PLSQL Procedure

Procedure

PL/SQL procedures:-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];      
    /

   
Example1:-

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


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.

SQL>DECLARE
    detail emp1%rowtype;
    eno number :=&no;
BEGIN
    proc_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
SQL>DROP PROCEDURE proc_emp_detail;

Procedure dropped.