Listen Software Solutions' "How To" Series:
Cursors, Functions, Procedures, and More
By David Nishimoto
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
Back to the LSS "How To" Series Main Page
|
