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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


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











×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.