Oracle PL/SQL

                                           TOPIC:-PL/SQL procedure

       Two type of Block:-

1. Named Block
2. Anonymous Block 

•  A PL/SQL procedure that accept zero or more parameter as input (IN),output (OUT) or both (INOUT) .
• PL/SQL does not return any value.
• PL/SQL can not used in SQL statement.
• They are invoked using Excute command or called inside a SQL block.

    CREATE OR REPLACE PROCEDURE  procedure name

    ( parameter ,parameter,parameter) 

    IS 

    local declaration ;

    BEGIN

    executable  statement

    [  EXCEPTION  

             exception handlers]

   END;

/

Let take an example we have table with name EMP.

    

          SELECT * FROM EMP;

 we created  a procedure-
create or replace procedure delete_emp(empid number) as
total_emp number;
begin
delete from emp where emp.empid=delete_emp.empid;
total_emp:=total_emp-1;
end;

/

execute delete_emp(2);

SELECT * FROM EMP;

             

                                                   

•    You can drop PL/SQL procedure using DROP PROCEDURE statement,

DROP PROCEDURE procedure_name;