Procedures in PL/SQL

If a program or query that is stored as a procedure permanently in the database is called "Stored Procedure". It is saved in database as Pre-compiled object, once its compiled it will not be compiled again and again like a program until the logic changes. Procedures are executed explicitly, to execute it we have to use execute command. 

A Procedure is a subprogram unit that consists of a group of PL/SQL statements. Each procedure in Oracle has its own unique name by which it can be referred. 
It contains declaration part (optional), execution part, exception handling part (optional).
The values can be passed into the procedure or fetched from the procedure through parameters.
Keyword 'IS' will be used, when the procedure is nested into some other blocks. If the procedure is standalone then 'AS' will be used. Other than this coding standard, both have the same meaning.


SYNTAX :-
----------
CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 

END procedure_name; 


THERE ARE 3 TYPES OF PARAMETERS :-
-------------------------------------------
- IN parameter lets you pass a value to the subprogram
- OUT represents the parameter that will be used to return a value outside of       the procedure
- IN OUT parameter passes an initial value to a subprogram and returns an     updated value to the caller.


PROCEDURES ARE OF TWO TYPES :-
---------------------------------------
- Dynamic Procedure
- Static Procedure


STATIC PROCEDURE :-
-----------------
CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM DEPTNO 10.

CREATE OR REPLACE PROCEDURE PRO_DEPT_10
IS
O_EMPCOUNT INT;
BEGIN
SELECT COUNT(*) INTO O_EMPCOUNT FROM EMP WHERE DEPTNO=10;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES FROM DEPARTMENT NO 10 IS' || O_EMPCOUNT);
END PRO_DEPT_10;
/



CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM DEPTNO 20

CREATE OR REPLACE PROCEDURE PROC_DEPT_20
IS
O_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO O_VARIABLE FROM EMP WHERE DEPTNO=20;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES FROM DEPTNO 20 IS'|| O_VARIABLE);
END PROC_DEPT_20;
/


CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM DEPTNO 30

CREATE OR REPLACE PROCEDURE PROC_DEPTNO_30
IS
O_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO O_VARIABLE FROM EMP WHERE DEPTNO = 30;
DBMS_OUTPUT.PUT_LINE ('NUMBER OF EMPLOYEES FROM DEPTNO 30 IS ' || O_VARIABLE);
END PROC_DEPTNO_30;
/


WRITE A PROCEDURE TO DISPLAY 'MY NAME IS PRASAN KUMAR '

CREATE PROCEDURE PROC_NAME
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('MY  NAME IS PRASAN KUMAR');
END PROC_NAME;
/

SET SERVEROUTPUT ON 

EXECUTE PROC_NAME



DYNAMIC PROCEDURE :-
---------------------
CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM VARIABLE DEPTNO'S.

CREATE OR REPLACE PROCEDURE PROC_EMP_COUNT (V_DEPTNO INT)
IS
EMP_COUNT INT;
BEGIN
SELECT COUNT(*) INTO EMP_COUNT FROM EMP WHERE DEPTNO = V_DEPTNO;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEES FROM V_DEPTNO IS ' || EMP_COUNT );
END PROC_EMP_COUNT;
/

SET SERVEROUTPUT ON
EXECUTE PROC_EMP_COUNT(10)

 
CREATE OR REPLACE PROCEDURE PROC_VARIABLE_DEPT (IN_VARIABLE INT)
IS
O_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO O_VARIABLE FROM EMP WHERE IN_VARIABLE=DEPTNO;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES FROM VARIABLE DEPT IS' || O_VARIABLE);
END PROC_VARIABLE_DEPT;
/
SET SERVEROUTPUT ON
EXECUTE PROC_VARIABLE_DEPT(20)
 
 
CREATE OR REPLACE PROCEDURE PROC_VAR_JOB (IN_VARIABLE VARCHAR)
IS
OUT_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO OUT_VARIABLE FROM EMP WHERE JOB=IN_VARIABLE;
DBMS_OUTPUT.PUT_LINE('COUNT FROM VARIABLE JOBS IS' || OUT_VARIABLE );
END PROC_VAR_JOB;

/
SET SERVEROUTPUT ON
EXECUTE PROC_VAR_JOB (CLERK)

CREATE A PROCEDURE TO POPULATE TOTAL SALARY OF AN EMPLOYEE.

CREATE OR REPLACE PROCEDURE PROC_SAL_COMM (EMP_ID IN NUMBER, TOTAL OUT NUMBER)
IS 
BEGIN
SELECT SAL+(SAL*10/100) INTO TOTAL FROM EMP
WHERE EMP_ID = EMPNO;
END PROC_SAL_COMM;
/
SET SERVEROUTPUT ON
EXECUTE PROC_SALHIKE(7934)


CREATE A PROCEDURE TO GET MIN, MAX, AVG, SUM OF SALARIES OF EMPLOYEES.

CREATE OR REPLACE PROCEDURE PROC_SAL_CAL (
IN_DEPTNO IN NUMBER,
MAX_SAL OUT NUMBER,
MIN_SAL OUT NUMBER,
SUM_SAL OUT NUMBER,
AVG_SAL OUT NUMBER )
IS 
BEGIN
SELECT MAX(SAL), MIN(SAL), SUM(SAL), AVG(SAL) 
INTO MAX_SAL, MIN_SAL, SUM_SAL, AVG_SAL
FROM EMP
WHERE DEPTNO = IN_DEPTNO GROUP BY DEPTNO;
END PROC_SAL_CAL;
/

CREATE A PROCEDURE TO INSERT ROWS INTO THE DEPT TABLE.

CREATE OR REPLACE PROCEDURE ADD_DEPT (
P_DNO IN DEPT.DEPTNO%TYPE,
P_DNM IN DEPT.DNAME%TYPE,
P_LOC IN DEPT.LOC%TYPE
)
IS 
BEGIN
INSERT INTO DEPT VALUES (P_DNO, P_DNM, P_LOC);
DBMS_OUTPUT.PUT_LINE('DEPARTMENT ADDED');
END ADD_DEPT;
/

SET SERVEROUTPUT ON
EXECUTE ADD_DEPT (50,'BISCUIT','OUR LOCATION');


WRITE A PROCEDURE TO FIND MINIMUM VALUE.

CREATE OR REPLACE PROCEDURE FINDMIN 
( X IN NUMBER, Y IN NUMBER, Z OUT NUMBER )
IS
BEGIN
IF X < Y THEN
Z:= X;
ELSE
Z:=Y;
END IF;
END;
/
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
A:=23;
B:=45;
FINDMIN (A,B,C);
DBMS_OUTPUT.PUT_LINE ('MINIMUM OF (23,45):' || C);
END;

/



WRITE A PROCEDURE TO DISPLAY DEPARTMENT NAME FROM DEPT TABLE.

CREATE OR REPLACE PROCEDURE ADD_DEPT
(P_DNO IN DEPT.DEPTNO%TYPE,
 P_DN OUT DEPT.DNAME%TYPE)
IS
BEGIN
SELECT DNAME INTO P_DN
FROM DEPT
WHERE DEPTNO = P_DNO;
END;
/
DECLARE
P_DN VARCHAR2(20);
BEGIN
ADD_DEPT(30,P_DN);
DBMS_OUTPUT.PUT_LINE(P_DN);
END;

/

* Write a procedure to get min, max, avg, sum of salaries of employees.

Create procedure Proc_Get_Salaries

(

in_deptno in number,

min_sal out number, 

max_sal out number,

avg_sal out number 

sum_sal out number)

AS

begin

Select  min(sal), max(sal), avg(sal), sum(sal) into 

min_sal, max_sal, avg_sal, sum_sal from emp where deptno=in_deptno group by deptno;

End;