Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Apr 29, 2002

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

By David Nishimoto

PL/SQL Commands and Syntax--Contents

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date