Procedure

Procedure

/*A SIMPLE PROCEDURE*/
CREATE OR REPLACE PROCEDURE showname
IS
BEGIN
DELETE FROM STUD
WHERE ROLL=6;
END;
-------------------------------------------
CALLING PROCEDURE FROM SQL* PLUS
EXECUTE SHOWNAME;

CALLING PROCEDURE FROM PL/SQL BLOCK

BEGIN
SHOWNAME;
END;

---------------------------------------------
/*NORMAL PARAMETERIZE PROCEDURE(USING IN PARAMETER)*/
CREATE OR REPLACE PROCEDURE INS
(A IN STUD.ROLL%TYPE)
IS
BEGIN
INSERT INTO STUD(ROLL) VALUES(A);
END INS;
---------------------------------------------
/*PARAMETERIZE PROCEDURE 2 (USING OUT PARAMETER)*/
CREATE OR REPLACE PROCEDURE SHOWTAB
(A IN EMP.ENAME%TYPE,B OUT EMP.JOB%TYPE,C OUT EMP.SAL%TYPE)
IS
BEGIN
SELECT JOB,SAL INTO B,C FROM EMP WHERE ENAME=A;
DBMS_OUTPUT.PUT_LINE(B||' '||C);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('sORRY THE PERSON YOU HAVE ENTERED IS NOT AN EMPLOYEE');
END SHOWTAB;

/*show output*/
declare
x varchar2(30):='&Name';
y varchar2(10);
z number(5);
begin
showtab(x,y,z);
end;

---------------------------------------------
/*PARAMETERIZE PROCEDURE 2 (USING IN OUT PARAMETER)*/
CREATE OR REPLACE PROCEDURE PHONE
(PHNO IN OUT VARCHAR2)
IS
BEGIN
PHNO:='('||SUBSTR(PHNO,1,3)||')'||SUBSTR(PHNO,4,4)||'-'||SUBSTR(PHNO,8);
END PHONE;

/*SHOW OUTPUT*/
DECLARE
PH_NO varchar2(25):='&PHONE_NUMBER';
BEGIN
PHONE(PH_NO);
DBMS_OUTPUT.PUT_LINE(PH_NO);
END;
/

---------------------------------------------
/*PROCEDURE USING DEFAULT PARAMETER*/
CREATE OR REPLACE PROCEDURE ADD_STUD
(SNAME IN STUD.NAME%TYPE DEFAULT 'NOONE',SROLL IN STUD.ROLL%TYPE DEFAULT 99,SDOB IN STUD.DOB%TYPE)
IS
BEGIN
INSERT INTO STUD(NAME,ROLL,DOB) VALUES(SNAME,SROLL,SDOB);
END ADD_STUD;
/

/*SHOW OUTPUT*/
DECLARE
A STUD.NAME%TYPE:='&NAME';
B STUD.ROLL%TYPE:=&ROLL;
C STUD.DOB%TYPE:='&DATE';
BEGIN
ADD_STUD(A,B,C);
END;

---------------------------------------------
/*CALLING A PROCEDURE FROM ANOTHER PROCEDURE*/
CREATE OR REPLACE PROCEDURE PROC1
IS
CURSOR EMP_CUR IS
SELECT EMPNO
FROM EMP;
BEGIN
FOR EMP_REC IN EMP_CUR
LOOP
INS(EMP_REC.EMPNO);
END LOOP;
COMMIT;
END PROC1;
/
---------------------------------------------

/*REMOVING PROCEDURE*/
DROP PROCEDURE PROC1;