Create a PL/SQL procedure object in your schema. Using a Cursor the procedure code should be able to retrieve records of all customers and check their credit limit. If a customer credit limit is greater than 100000 the procedure should insert a record in the table HighCredits.

Question

(a) What is a named block in PL/SQL and how many types does PL/SQL support?
(b) Specify the general structure of an anonymous block.
(c) Assume that the following tables are part of a Company database schema
(d) Invoke the procedure from SQL*Plus.

Answer 1

(a) Named blocks are PL/SQL subprograms. PL/SQL has two types of subprograms:
procedures and functions.
(b) Named block general structure
[DECLARE
Declaration statement]
BEGIN
Executable statements
[EXCEPTION
Exception handler statements]
END;
(c) Customer(custNo, custName, address, sex, DOB, creditLimit)
HighCredit(hcCustNo, hcCustName, hcCreditLimit)
-- Using Cursor FOR Loop
CREATE OR REPLACE PROCEDURE Check_Credit
IS
-- Declare a Cursor
CURSOR C1 IS
SELECT custNo, custName, creditLimit FROM Customer;
BEGIN
FOR Customer_rec IN C1 LOOP
IF (Customer_rec.creditLimit > 100000) THEN
INSERT INTO HighCredit VALUES (Customer_rec.custNo,
Customer_rec.custName, Customer_rec.creditLimit);

END IF;
END LOOP;
END;
-- Students can also use OPEN, FETCH, CLOSE Cursor.
CREATE OR REPLACE PROCEDURE Check_Credit
IS
-- Declare a Cursor
CURSOR C1 IS
SELECT custNo, custName, creditLimit FROM Customer;
Customer_rec C1%ROWTYPE ; -- declare a record of cursor type

BEGIN
OPEN C1;
LOOP
FETCH C1 INTO Customer_rec;
-- exit loop when no more records

EXIT WHEN C1%NOTFOUND;
IF (Customer_rec.creditLimit > 100000) THEN
INSERT INTO HighCredit values (Customer_rec.custNo,
Customer_rec.custName, Customer_rec.creditLimit);
END IF;
END LOOP;
CLOSE C1;
END;