Quick Search for:  in language:    
PLSQL,introduces,dimension,power,programming,
   Code/Articles ╗ |  Newest/Best ╗ |  Community ╗ |  Jobs ╗ |  Other ╗ |  Goto ╗ | 
CategoriesSearch Newest CodeCoding ContestCode of the DayAsk A ProJobsUpload
SQL Stats

 Code: 44,501. lines
 Jobs: 115. postings

 How to support the site

 
Sponsored by:

 
You are in:
 

Does your code think in ink?
Login





Latest Code Ticker for SQL.
Group Data Values
By Thivya Prabakaran on 11/29


Grouping Data
By Thivya Prabakaran on 11/29


Separating Function
By Jayanthi Venugopal on 11/27


Filter duplicate records from a table
By Geoff Kazzi on 11/25


qStats_Ex
By Ewald Hofman on 11/19


find depended objects
By chandra sekhar on 11/19


Click here to put this ticker on your site!


Add this ticker to your desktop!


Daily Code Email
To join the 'Code of the Day' Mailing List click here!

Affiliate Sites



 
 
   

Introduction to PL/SQL (Series 3) Cursors

Print
Email
 

Submitted on: 10/14/2002 8:22:31 AM
By: David Nishimoto 
Level: Beginner
User Rating: By 7 Users
Compatibility:Oracle

Users have accessed this article 9641 times.
 
(About the author)
 
     PL/SQL introduces a new dimension of power programming capability for the developer. The goal of reusable code measurely improves quality verse vast amounts of non-reusable SQL. PL/SQL packages describe function and procedure specification and body. The specification describes the package interface to the calling application. The package specification approach is part of good design. The package specification exposes procedures and their parameter properties to external applications. Each specification has a package body. Within the package body various data structures, such as arrays and tables can be referenced, functions called, and procedures executed. Developers new to PL/SQL often understand insert, update, and delete data manipulations; but are confused about selection. This is because they don't know about cursors.

 
 
Terms of Agreement:   
By using this article, you agree to the following terms...   
1) You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.   
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
Author: David Nishimoto
davepamn@relia.net
Introduction to PL/SQL cursors (series 3)
PLSQL Haven
Buy Oracle PLSQL

PL/SQL introduces a new dimension of power programming capability for the developer. The goal of reusable code measurely improves quality verse vast amounts of non-reusable SQL. PL/SQL packages describe function and procedure specification and body. The specification describes the package interface to the calling application. The package specification approach is part of good design. The package specification exposes procedures and their parameter properties to external applications. Each specification has a package body. Within the package body various data structures, such as arrays and tables can be referenced, functions called, and procedures executed. Developers new to PL/SQL often understand insert, update, and delete data manipulations; but are confused about selection. This is because they don't know about cursors.

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 
	/*Procedures*/
	PROCEDURE myProcedure (param1 IN NUMBER, param2 in NUMBER); 
	/*Function*/
	FUNCTION myFunction (param1 IN varchar2, param2 IN varchar2) RETURN NUMBER IS retparam NUMBER; 
	/*variable declaration */
	bFlag Number(1);
	/*cursor variables*/
	TYPE type_C1 IS REF CURSOR RETURN myTable%ROWTYPE;
END {package_name}; 

Step 2 CREATE OR REPLACE PACKAGE BODY {package_name} AS /*Procedures*/ PROCEDURE myProcedure ( param1 IN NUMBER, param2 in Number) IS BEGIN /*Add your Code*/ END myProcedure; /*Functions*/ FUNCTION myFunction (param1 IN varchar2, param2 IN varchar2) RETURN NUMBER IS retparam NUMBER; BEGIN /*Add your Code*/ END myFunction; END {package_name};

PL/SQL uses the concept of a cursor to return data records. A cursor defines the records to be returned based on a SQL statement. A cursor returns one record at a time. Cursors are very efficient not requiring large amounts of memory to operate. SQL criteria can be defined by binding field name values to bind variables. Bind variables can be passed into a procedure or function and create cursor selection criteria.

Once the cursor has been declared and bind variable associated with the cursor variable, the cursor variable can be opened. There are two ways to access a cursor fetched row. The first method is through field variables inwhich the cursor row is fetched. The bind variable is type casted to a specific field name and its cast type.

	FETCH C1 INTO v_field1,field2;

The second method is through a table row variable, inwhich the cursor is fetched into. Retrieve values from a row variable fetch is easy. Reference the row variable proceded by a "." and annotate the field name.

	FETCH C1 INTO v_row;

Once the data row has be fetched various data manipulation can be applied, such as, update, delete, and insert. A cursor can be defined as select SQL for update.

		CURSOR C1 IS
			SELECT * FROM mytable
			where field1 = v_field1
			and field2 = v_field2
			FOR UPDATE OF field1;
		So as the data row is being fetch it can be updated.
		update mytable
			set field1 = field1 * 1.01;
			where current of C1;

Parameters can be passed to a Cursor declaration. The cursor can be open with parameters.

	CURSOR C1 (pField1 myTable.field1%TYPE, pField2 myTable.field2.%TYPE) IS
		Select * from mytable
		where field1=pField1
		and field2=pField2;

Defining and using Cursors inside a function or procedure

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


Other 9 submission(s) by this author

 

 
Report Bad Submission
Use this form to notify us if this entry should be deleted (i.e contains no code, is a virus, etc.).
Reason:
 
Your Vote!

What do you think of this article(in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor See Voting Log
 
Other User Comments
10/18/2002 8:55:06 PM:Joseph Gama
Very clear article with simple and very explicit samples. 5 globes! Peace, Joseph Gama
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
12/16/2002 7:40:51 PM:
thanks, very helpful article.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
Add Your Feedback!
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you.  If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.
 
Name:
Comment:

 

Categories | Articles and Tutorials | Advanced Search | Recommended Reading | Upload | Newest Code | Code of the Month | Code of the Day | All Time Hall of Fame | Coding Contest | Search for a job | Post a Job | Ask a Pro Discussion Forum | Live Chat | Feedback | Customize | SQL Home | Site Home | Other Sites | About the Site | Feedback | Link to the Site | Awards | Advertising | Privacy

Copyrightę 1997 by Exhedra Solutions, Inc. All Rights Reserved.  By using this site you agree to its Terms and Conditions.  Planet Source Code (tm) and the phrase "Dream It. Code It" (tm) are trademarks of Exhedra Solutions, Inc.