CodesJava

Easy learning with example program codes

Oracle pl sql stored procedure tutorial


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;

 



Industrial Training

We offers Placement Oriented Training on Java, Spring, JSF, Hibernate, PHP, AngularJS, Angular 4, PLSQL, Oracle BI Publisher etc. We also provides Online training, please mail us at hr@codesjava.com.

Development

We also provides the Development services for Website Development , Java Development, PHP Development, Android App Development etc. You can contact us on hr@codesjava.com.

Copyright © 2019 CodesJava DMCA.com Protection Status SiteMap Reference: Java Wiki