Testology

Chapter 8:PL/SQL Procedure

PL/SQL Procedure

Like a PL/SQL function, a PL/SQL procedure is a named block that does a specific task. It is a database object used to perform a repeated task.Value can be supplied through parameter.It is just like procedures in other programming languages.

When you create a function or procedure, you have to define IN/OUT/INOUT parameters parameters.

  1. IN: IN parameter referring to the procedure or function and allow to overwritten the value of parameter.
  2. OUT: OUT parameter referring to the procedure or function and allow to overwritten the value of parameter.
  3. IN OUT: Both IN OUT parameter referring to the procedure or function to pass both IN OUT parameter, modify/update by the function or procedure and also get returned.

PL/SQL Create Procedure

Syntax for creating procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name  
    [(parameter_name [IN | OUT | IN OUT] type [,parameter])]  ]  
{IS  | AS}
    [declaration_section]  
BEGIN  
    [procedure_body]  
[EXCEPTION  
    exception_section]  
END [procedure_name];

Where,

procedure-name specifies the name of the procedure.

[OR REPLACE] option allows the modification of an existing procedure.

The optional parameter list contains name, mode and types of the parameters. INrepresents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.

procedure-body contains the executable part.

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

Example:

The following example creates a simple procedure that displays the string ‘Hello World!’ on the screen when executed.

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

When the above code is executed using the SQL prompt, it will produce the following result −

Procedure created.

Executing Procedure

A standalone procedure can be called in two ways −

  • Using the EXECUTE keyword
  • Calling the name of the procedure from a PL/SQL block

The above procedure named ‘greetings’ can be called with the EXECUTE keyword as −

EXECUTE greetings;

The above call will display −

Hello World
PL/SQL procedure successfully completed.

The procedure can also be called from another PL/SQL block −

BEGIN 
   greetings;  
END; /

The above call will display −

Hello World  

PL/SQL procedure successfully completed.

PL/SQL DROP Procedure

A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for deleting a procedure is −

DROP PROCEDURE procedure-name;

You can drop the greetings procedure by using the following statement −

DROP PROCEDURE greetings;