Stored Procedure PL/SQL

PL/SQL Stored Procedure:

The PL/SQL stored procedure is a named PL/SQL block that performs one or more specific tasks. A PL/SQL stored procedure can be divided into two parts: Header and Body part.
Header: The header part contains the name of the procedure and the parameters passed to the procedure.
Body: The body part contains the declaration section, execution section, and exception section.
Note: A PL/SQL stored procedure does not return a value directly.

How to pass parameters in a procedure?

We can use the below modes to pass the parameters in a procedure:
IN-parameters: These parameters are the read-only parameters. The procedure cannot change the value of IN parameters.
OUT-parameters: These parameters are the write-only parameters and are used to return values to the calling program. The procedure can change the value of OUT parameters.
IN OUT parameters: These parameters are read and write parameters i.e. a procedure can read and change the IN OUT parameter value and return it to the calling program.

Syntax of PL/SQL stored procedure:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] 
IS | AS    
   //Declaration block 
BEGIN    
   //Execution block 
EXCEPTION    
  //Exception block 
END;

How to create a procedure?

Procedure example without parameters:

CREATE OR REPLACE PROCEDURE hello_world
AS
BEGIN
   dbms_output.put_line('Hello World!');
END;
/

Procedure example with parameters:

CREATE OR REPLACE PROCEDURE add_student(rollNo IN NUMBER, name IN VARCHAR2)
IS 
   BEGIN 
    insert into students values(rollNo,name);
  END;
/

How to execute stored procedure?

A procedure can be executed by using an EXEC or EXECUTE statement.

EXEC procedure_name();
EXEC procedure_name;

Note: Execute procedure with parameters:

EXEC procedure_name(param1,param2…paramN);

A procedure can also be invoked from other PL SQL blocks.

BEGIN
   procedure_name;
END;
/

How to drop stored procedure?

DROP PROCEDURE procedure_name;

Alex Martin