How To Execute Stored Procedure from Front and End in Oracle PL/SQL  


Member Admin
Joined: 6 years ago
Posts: 36
29/04/2018 12:37 am  

Executing Stored Procedure from Front and End
PLSQL stored procedure:  
The plsql 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 read and change the IN OUT parameter value and return it back to the calling program.

Syntax of PLSQL stored procedure: 

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]

IS | AS   

   //Declaration block


   //Execution block


  //Exception block


How to create a procedure?

CREATE OR REPLACE PROCEDURE hello_worldASBEGIN   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;/

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;/