PL/SQL Procedure

 PL/SQL Procedure 

     PL/SQL Procedure are PL/SQL Block,used for implement the business logic. PL/SQL Procedure create using "CREATE PROCEDURE" statement.

 PL/SQL Procedure Syntax:-  

CREATE [OR REPLACE] PROCEDURE [SCHEMA.] procedure_name
        [ (parameter [,parameter]) ]
IS
    [declaration_section
        variable declarations;
        constant declarations;
    ]
BEGIN
    [executable_section
        PL/SQL execute/subprogram body
    ]
[EXCEPTION]
        [exception_section
        PL/SQL Exception block
        ]
END [procedure_name];

 PL/SQL Procedure Example:-  

   Example1:- Create Procedure that take employee number and return their name.

CREATE or REPLACE 
PROCEDURE proc_get_emp_detail(
	in_empno in number,
	detail out emp1%rowtype
)
IS
BEGIN
    SELECT * INTO detail 
    FROM emp 
    WHERE empno = in_empno;
END proc_get_emp_detail;
/

New Procedure created.

 Procedure Calling:-  

  Procedure calling is different from function calling . Function always return a value so we can call function in "Select Statement" but we can't call Procedure in "Select Statement". Returning value of Function can be assigned to variable but we can't do the same with procedure.

DECLARE
    detail emp1%rowtype;
    eno number :=&no;
BEGIN
    proc_get_emp_detail(eno,detail);
    dbms_output.put_line(
    detail.empno ||'<-->'||
    detail.ename ||'<-->'||
    detail.deptno ||<-->'||
    detail.sal);
END;
/

 Drop Procedure:-  

  We can drop PL/SQL procedure using DROP PROCEDURE statement.

  Syntax:

 DROP PROCEDURE procedure_name;

  Example:

 DROP PROCEDURE proc_get_emp_detail; 

  After running above statement the pl/sql procedure proc_get_emp_detail will get dropped from database.