To the point guide to PL/SQL

A program unit that performs a specific task is known as subprogram. The number of subprograms are grouped together to form an application. A subprogram is by calling it by another program/subprogram. Subprograms are called as procedure. A procedure contains a set of PL/SQL and SQL statements.

A subprogram can be created:

• Inside a package
• At schema level
• Inside a PL/SQL block

Inside a package: Inside a package is a packaged subprogram which is stored in the database. To deleted this only when the package is deleted by using DROP PACKAGE statement.

At schema level: is a standalone subprogram, to create it by using CREATE PROCEDURE statement and it can be delete by using DROP PROCEDURE statement.

PL/SQL blocks are invoked by setting the parameters.

To create a procedure:

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]

IS 
    [declaration_section] -- this section contains declarations of types, variables, constants,exceptions

BEGIN
    executable_section -- This part is mandatory and contains statements that perform the specific task.

[EXCEPTION
    exception_section] – this section to handle run-time errors.

END [procedure_name];

Let's now check some important parts of the above snippets

• Procedure-name: specifies the procedure name.
• [OR REPLACE]: option for modifying an already created procedure.
• Procedure-body or executable section: contains the executable part.

The AS keyword can be used instead of IS for standalone procedure creation.

Example:

Procedure to displays the message 'Welcome to PL/SQL'

CREATE OR REPLACE PROCEDURE message
AS
BEGIN
 dbms_output.put_line('Welcome to PL/SQL ');
END;
/

To execute procedure:

EXECUTE message;

Result:

Welcome to PL/SQL 
PL/SQL procedure successfully completed.

To call procedure from another PL/SQL function:

BEGIN
   message
END;
/

To drop/delete a procedure:

Syntax:

DROP PROCEDURE procedure-name;

Example:

DROP PROCEDURE message;
Procedure dropped.

To create a procedure/function, we need to define parameters IN/OUT/INOUT.

• IN: It referring to the procedure that allows to has overwritten the parameter value.
• OUT: It referring to the procedure that allows to has overwritten the parameter value.
• IN OUT: It referring to the procedure to pass both IN OUT parameters, update by the procedure and also returned.

Example

CREATE OR REPLACE PROCEDURE TEST_SWAPPING AS
      first NUMBER := 5;
      second NUMBER := 10;
 PROCEDURE SWAPPING(a IN OUT NUMBER,b IN OUT NUMBER) AS
       Temp NUMBER;
    BEGIN
       Temp := first;
       first := second;
       first:= Temp;
     END SWAPPING;
    BEGIN     
     SWAPPING(first,second);
     DBMS_OUTPUT.PUT_LINE('First  = ' || TO_CHAR(first));
     DBMS_OUTPUT.PUT_LINE('Second  = ' || TO_CHAR(second));
  END;
 /

Result

EXECUTE TEST_SWAPPING;
first  = 10
second  = 5

Methods for Passing Parameters

Types of parameter passing:

• Named notation
• Positional notation
• Mixed notation

NAMED:

In this notation, user use this symbol (=>) to associate actual parameter the formal parameter.

Example:

SWAPPING(x=>a, y=>b);

POSITIONAL:

In this notation, the first and second actual parameter is substituted for the first and second formal parameter respectively, and so on.

Example:

SWAPPING (a, b);

MIXED: 

This is a combination of both notations the positional notation should precede the named notation.

Example:

SWAPPING(a, b,x=>c);