PL/SQL Exception

PL/SQL Exception

PL/SQL Interview Questions

PL/SQL Interview Questions and Answers


PL/SQL Create Procedure

The PL/SQL Procedure is a subprogram or module which perform specific task. A subprogram can be invoked by another subprogram or module which is called the calling program. A subprogram can be created at: the schema level, inside a package, inside a PL/SQL block.

Difference between Procedure and Function

Procedures

  • Procedures can have input/output parameters.
  • Procedure can return zero or n values.
  • Procedures cannot be called from Function.

Functions

  • Functions can have only input parameters.
  • Function must return a value.
  • Functions can be called from Procedure.

A procedure contains a header and a body.

Header: The header part consist of name of the procedure and the parameters passed to the procedure.

Body: The body part consist of a declaration section, execution section and exception section similar to a general PL/SQL block.

Pass parameters in procedure

There are three ways to pass parameters in procedure:

  • IN parameters: Indicate that you must supply a value for the argument when calling the procedure.
  • OUT parameters: Indicate that the procedure passes a value for this argument back to its calling environment after execution.
  • INOUT parameters: Indicate that you must supply a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.
Note: If you omit IN, OUT, and IN OUT, then the argument defaults to IN.

PL/SQL Create Procedure Syntax

To create a procedure use CREATE OR REPLACE PROCEDURE statement, use the following syntax:

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;   

Where:

procedure-name: The name of the procedure_name.

[OR REPLACE]: This option used to modify of an existing procedure.

parameter_name: The optional parameter list contains name, mode and types of the parameters.

procedure-body: This part contains executable block.

IS | AS: The AS keyword is used instead of the IS keyword for creating a standalone procedure.

PL/SQL Create Procedure example

The following PL/SQL show how to jump to perform specific tast in the same sub section of PL/SQL section:

Example

CREATE OR REPLACE PROCEDURE CreateUser    
(id IN NUMBER, firstName IN VARCHAR2, lastName IN VARCHAR2)    
AS
BEGIN    
	INSERT INTO tblUser values(id, firstName, lastName);    
END;      

PL/SQL Call Procedure example

The following PL/SQL show how to call above created procedure using EXECUTE and in PL/SQL block:

Example

EXECUTE CreateUser;

OR

BEGIN    
   CreateUser(10001, 'Ramesh', 'Malhotra');  
   DBMS_OUTPUT.PUT_LINE('User created successfully');    
END;