PL/SQL - Procedures

Procedure
  • is a PL/SQL sub program used to execute set of statements which takes parameters as input and returns output (optional).
  • is compiled and stored in the database as a schema object.
  • can be reused with other sub programs.
  • can be modified or deleted

Oracle Procedures has the following:
  • Declarative part
  • Executable part
  • Exception-handling part
Declarative part contains the following and these items are local and exist only till subprogram exists.
  • Declarations of types
  • Cursors ( Names Control Structures)
  • Constants
  • Variables
  • Exceptions
  • Nested subprograms
Executable part contains
  • Statements that assign values
  • Control execution
  • Process the data like Insert/update/delete
Exception-handling part
  • The exception-handling part contains exception handlers, which deal with exceptions raised during execution.
Procedure Syntax:
 
PROCEDURE name [ ( parameter [, parameter ... ] ) ]
IS
   [declaration statements]
BEGIN
   executable-statements
[ EXCEPTION
     exception handler statements]
END [ name ];

Component usage in Procedure:

  • Procedure name comes after standard keyword PROCEDURE
  • Procedure parameters are optional, takes parameters as input and return something as output. Return output is also optional.
  • Procedure variables will be declared between the IS and BEGIN statements.
  • There should be at least one executable statement before the END or EXCEPTION keywords.
  • Exception handling is optional
  • Procedure is called as an executable PL/SQL statement.
  • We must call procedure with out parentheses (brackets) when procedure does not have any parameters
Parameter Modes in Procedure:
  • IN
  • OUT
  • IN OUT
Sample procedure with input parameters:

CREATE PROCEDURE create_customer (
    p_cust_name   VARCHAR2,
    p_cust_id     customer_table.customer_id%TYPE)
AS
BEGIN
    INSERT INTO customer_table (customer_name, customer_id)
         VALUES (p_cust_name, p_cust_id);
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.PUT_LINE (
            'Exception while creating customer. Error: ' || SQLERRM);
END;

Above procedure takes customer name and customer id as input parameters and creates data into customer table.

Procedure execution:

create_customer('ASK HAREESH',10001);

Sample procedure without input parameters:

CREATE PROCEDURE validate_customers
AS
    v_cust_count   NUMBER;
BEGIN
    SELECT COUNT (1) INTO v_count FROM customer_table;

    IF v_count > 0
    THEN
        DBMS_OUTPUT.PUT_LINE ('Customer data exists');
    ELSE
        DBMS_OUTPUT.PUT_LINE ('Customers data not available');
    END IF;
        DBMS_OUTPUT.PUT_LINE (
            'Exception while validating customers. Error: ' || SQLERRM);
END;

Above procedure have no parameters and just execute set of statements.

Procedure execution:
validate_customers;

Sample procedure with input and output parameters:

CREATE PROCEDURE create_customer (
    p_cust_name       VARCHAR2,
    p_cust_id         customer_table.customer_id%TYPE,
    x_status      OUT VARCHAR2)
AS
BEGIN
    INSERT INTO customer_table (customer_name, customer_id)
         VALUES (p_cust_name, p_cust_id);

    x_status := 'S';
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.PUT_LINE (
            'Exception while creating customer. Error: ' || SQLERRM);
        x_status := 'E';
END;

Above procedure takes customer name and customer id as inputs and returns customer creation status as output.

Procedure execution:

DECLARE
    v_customer_status   VARCHAR2 (1);
BEGIN
    create_customer (p_cust_name   => 'ASK HAREESH PVT LTD',
                     p_cust_id     => 10001,
                     x_status      => v_customer_status);
    DBMS_OUTPUT.PUT_LINE ('Customer creation status: ' || v_customer_status);
END;

Sample procedure with output parameter:

CREATE PROCEDURE get_customer_count (x_cust_count OUT NUMBER)
AS
BEGIN
    SELECT COUNT (1) INTO x_cust_count FROM customer_table;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.PUT_LINE (
            'Exception while getting customer count. Error: ' || SQLERRM);
        x_cust_count := NULL;
END;

Procedure execution:

DECLARE
    v_customer_count   VARCHAR2 (1);
BEGIN
    get_customer_count (v_customer_count);
    DBMS_OUTPUT.PUT_LINE (
        'Total customers exists in system: ' || v_customer_count);
END;

Sample procedure with IN OUT parameter:

CREATE PROCEDURE update_customer (p_cust_name IN OUT VARCHAR2)
AS
    v_new_cust_name   v_new_cust_name.customer_name%TYPE;
BEGIN
    v_new_cust_name := 'New ' || p_cust_name;

    UPDATE customer_table
       SET customer_name = v_new_cust_name
     WHERE customer_name = p_cust_name;

    COMMIT;
    p_cust_name := v_new_cust_name;
END update_customer;

Procedure execution:

DECLARE
    v_customer_name   VARCHAR2 (100) := 'ASK HAREESH';
BEGIN
    DBMS_OUTPUT.PUT_LINE ('Old customer Name: ' || v_customer_name);
    update_customer (v_customer_name);
    DBMS_OUTPUT.PUT_LINE ('Modified customer Name: ' || v_customer_name);
END;



Procedure header is nothing but before IS keyword
Procedure Header contains
  • Procedure name
  • Parameter List if any
Procedure Body is nothing but after IS keyword
Procedure Body contains
  • Declaration
  • Execution
  • Exception handling

A procedure need not do anything  like below sample procedure

CREATE PROCEDURE nothing IS
BEGIN
   NULL;
END;

We can append the name of the procedure after the END keyword like below

CREATE PROCEDURE nothing IS
BEGIN
   NULL;
END nothing;


Recompile PROCEDURE:

We can recompile the procedure with below statement.

ALTER PROCEDURE update_customer compile;

Complete Tutorial

       Posted by Follow us on Facebook | Twitter | Youtube | Google+
*/

3 comments: