stored procedure plsql

Pl sql stored procedure:

The pl sql stored procedure is a named PL/SQL block which 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 declaration section, execution section and exception section. Note: A pl sql stored procedure do not return a value directly.

How to pass parameter 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. Procedure cannot change the value of IN parameters. OUT-parameters: These parameters are the write-only parameters and used to return values back to the calling program. Procedure can change the value of OUT parameters. IN OUT-parameters: These parameters are read and write parameters i.e. a procedure can reads and change the IN OUT parameter value and return it back 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 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 block.

BEGIN
   procedure_name;
END;
/

How to drop stored procedure?

DROP PROCEDURE procedure_name;

Please follow and like us: