Examples:
Let consider the package named sord display below. The package specification declares the subsequent subprograms:
- procedures ordproc, custchk and prodchk
After writing the package, applications which reference its types, call its subprograms, and use its cursor and raise its exception can be establish when the package is created. It is stored in an Oracle database for common use.
CREATE OR REPLACE PACKAGE SORD AS
PROCEDURE CUSTCHK(CNO NUMBER);
PROCEDURE PRODCHK(PNO NUMBER);
PROCEDURE ORDPROC(CNO NUMBER,PNO NUMBER,QTY NUMBER);
END;
CREATE OR REPLACE PACKAGE BODY SORD AS
FUNCTION CHKQTY(PNO NUMBER,QTY NUMBER) RETURN NUMBER IS
STOCK NUMBER:=0;
BEGIN
SELECT STOC INTO STOCK FROM PRODUCT WHERE PCODE=PNO;#for select stock field from product table where PCODE= PNO#
IF STOCK > QTY THEN
RETURN 1;
ELSE
RETURN -1;
END IF;
END;
PROCEDURE PRODUPD(PNO NUMBER,QT NUMBER) IS# declaration procedure#
BEGIN
UPDATE PRODUCT SET STOC=STOC-QT WHERE PCODE=PNO; # update product set to Stoc QT#
END;
PROCEDURE CUSTCHK(CNO NUMBER) IS C_ID NUMBER; #declaration procedure#
BEGIN
SELECT CCODE INTO C_ID FROM CUSTOMER WHERE CCODE=CNO;#for select C_ID field from Customer table where CCODE= CNO#
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,'CCODE NOT FOUND');
END;
PROCEDURE PRODCHK(PNO IN NUMBER) AS
P_ID NUMBER;
BEGIN
SELECT PCODE INTO P_ID FROM PRODUCT WHERE PCODE=PNO;#for select stock field from product table where PCODE= PNO#
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,'PCODE NOT FOUND');
END;
PROCEDURE ORDPROC(CNO IN NUMBER,PNO IN NUMBER,QTY IN NUMBER) AS
RES NUMBER:=0;
OC NUMBER:=0;
BEGIN CUSTCHK(CNO);
PRODCHK(PNO);
ELSE
RES:=CHKQTY(PNO,QTY);
IF RES=-1 THEN
RAISE_APPLICATION_ERROR(-20000,'SORRY NO STOCK');
SELECT MAX(OCODE)+1 INTO OC FROM ORDERS;
INSERT INTO ORDERS(OCODE,CCODE,ODAT) # insert Query#
VALUES(OC,CNO,SYSDATE);
INSERT INTO ORDPRD VALUES (OC, PNO, QTY);# insert Query#
PRODUPD(PNO,QTY);
END IF;
END;
END;
END;
The output of the program would be:
SQL> EXEC SORD.ORDPROC(1002,101,20);
begin SORD.ORDPROC(1002,101,20); end;
*
ERROR at line 1:
ORA-20000: SORRY NO STOCK
ORA-06512: at "HEMA.SORD", line 37
ORA-06512: at line 1
When a quantity which is more than the existing is entered, that raises an error. Seem at the subsequent:
SELECT STOC FROM PRODUCT WHERE PCODE=104
STOC
---------
EXEC SORD.ORDPROC(1002,104,20);
PL/SQL procedure successfully completed.
SELECT STOC FROM PRODUCT WHERE PCODE=104;
STOC
---------
696.1
the product stoc is updated.
Just Remember, the initialization part of a package is run just once, the first time you reference the package.
Each time the procedure ordproc is known as, a record in the table orders is inserted if the product code and the customer code exist and there is sufficient qty on hand.