Listen Software’s How To: Cursors, Functions, Procedures, and More

PL/SQL Commands and Syntax–Contents

• Cursors
• Functions
• Procedures
• Packages
• Triggers

Defining and Using Functions

CREATE OR REPLACE FUNCTION {function name}
( {argument} in {datatype} )
return {datatype} IS
    
v_variable {datatype};
    
BEGIN
    
    /* Insert PL/SQL code here */
    
    return v_variable;
    
END {function name};

Back to the Top

Defining and Using Procedures

See Also: Defining and Using Stored Procedures

CREATE OR REPLACE PROCEDURE {procedure name}
( {argument} IN {datatype} )
IS
v_variable {datatype};
BEGIN
    /*Insert your code here*/
END {procedure name};

Back to the Top

Defining and Using Packages

Two steps to creating a PL/SQL Package

1. Create the specifications
   Contains the declarative descriptions of the function, procedures, and gobal variables.

2. Create the body

Step 1

CREATE OR REPLACE PACKAGE {package_name} AS
   procedure specification
   function specification
   variable declaration
   cursor declaration
   type definition
END {package_name};

Step 2

CREATE OR REPLACE PACKAGE BODY {package_name} AS
    /*code in the specifications*/
END {package_name};

Back to the Top

Defining and Using Triggers

CREATE OR REPLACE TRIGGER {trigger name}
AFTER|BEFORE INSERT OR DELETE OR UPDATE ON {table name}
    
DECLARE
    /*You insert your code*/
BEGIN
END {trigger name};

Restrictions on Triggers

• A trigger may not issue any transaction control:
  - COMMIT, ROLLBACK, or SAVEPOINT.
• Any functions or Procedures called by the trigger can not issue any transaction control.
• No LONG or LONG RAW types can be declared

Back to the Top

Defining and Using Cursors

Using Bind Variables:

v_field1 mytable.field1%TYPE;
v_field2 mytable.field2%TYPE;

v_row mytable%ROWTYPE;

-- DECLARE THE CURSOR

CURSOR C1 IS
    SELECT * FROM mytable
    where field1 = v_field1
    and field2 = v_field2;

BEGIN
    -- OPEN A PREVIOUSLY DECLARED CURSOR
    OPEN C1;

    LOOP
        FETCH C1 INTO v_row;

        EXIT WHEN C1%NOTFOUND;
    END LOOP;

    CLOSE C1;
    -- CLOSE THE CURSOR
END;

Using a While Structure to Fetch Rows

v_field1 mytable.field1%TYPE;
v_field2 mytable.field2%TYPE;

v_row mytable%ROWTYPE;

-- DECLARE THE CURSOR

CURSOR C1 IS
    SELECT * FROM mytable
    where field1 = v_field1
    and field2 = v_field2;

BEGIN
    -- OPEN A PREVIOUSLY DECLARED CURSOR
    OPEN C1;

    FETCH C1 INTO v_row;

    WHILE C1%FOUND LOOP
        FETCH C1 INTO v_row;
    END LOOP;

    CLOSE C1;
    -- CLOSE THE CURSOR
END;

Using a For Structure to Fetch Rows

v_field1 mytable.field1%TYPE;
v_field2 mytable.field2%TYPE;

v_row mytable%ROWTYPE;

-- DECLARE THE CURSOR

CURSOR C1 IS
    SELECT * FROM mytable
    where field1 = v_field1
    and field2 = v_field2;

BEGIN
    -- OPEN A PREVIOUSLY DECLARED CURSOR
    OPEN C1;

    FOR v_row IN C1
        -- AN IMPLICIT FETCH 
        -- IS EXECUTED
        -- AN IMPLICIT CHECK 
        FOR C1%NOTFOUND is performed
    END LOOP
    -- AN IMPLICIT CLOSE OF 
    -- THE CURSOR IS PERFORMED
    -- CLOSE THE CURSOR
END;

Where Current Of

v_field1 mytable.field1%TYPE;
v_field2 mytable.field2%TYPE;

v_row mytable%ROWTYPE;

-- DECLARE THE CURSOR

CURSOR C1 IS
    SELECT * FROM mytable
    where field1 = v_field1
    and field2 = v_field2
    FOR UPDATE OF field1;

BEGIN
    -- OPEN A PREVIOUSLY DECLARED CURSOR
    OPEN C1;

    FOR v_row IN C1
        -- Update on the column list 
        --in the FOR UPDATE clause
        update mytable
        set field1 = field1 * 1.01;
        where current of C1;
    END LOOP
    -- AN IMPLICIT CLOSE OF THE 
    -- CURSOR IS PERFORMED
    -- CLOSE THE CURSOR
END;

Cursor Variables

TYPE type_C1 IS REF CURSOR
    RETURN myTable%ROWTYPE;

v_C1 type_C1;

v_row mytable%ROWTYPE;

BEGIN
    OPEN v_C1 FOR
    SELECT * FROM mytable
    where field1 = v_field1
    and field2 = v_field2
    FOR UPDATE OF field1;

    FETCH C1 INTO v_row;

    WHILE C1%FOUND LOOP
        FETCH C1 INTO v_row;
    END LOOP;

    CLOSE C1;

END

Back to the Top