إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

introduction to PL/SQL cursors


3 رد (ردود) على هذا الموضوع

#1 skyway

skyway

    عضو

  • الأعضــاء
  • 10 مشاركة

تاريخ المشاركة 04 April 2005 - 12:38 AM

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

#2 Osama Soliman

Osama Soliman

    مشرف سابق وعضو مميز

  • المجموعة الماسية
  • 1,611 مشاركة
  • الاسم الأول:Osama
  • اسم العائلة:Soliman
  • البـلـد: Country Flag
  • المنصب الحالي:Oracle Technical Consultant - Asfour Crystal International

تاريخ المشاركة 02 May 2005 - 06:14 AM

اضافة الى مشاركة الاخ الكريم
مرفق ملف به ملخص شرح موضوع الـ Cursor ، ملخص من كتاب Oracle University

ارجو ان يستفيد به السادة اعضاء المنتدى الاعزاء

ملفات مرفقة

  • ملف مرفق  Cursors.doc   189كيلو   220 عدد مرات التحميل

وقل رب زدنى علماً
Osama M. Soliman
Oracle Certified Professional
Oracle Technical Consultant

 Asfour Crystal International
Cairo - Egypt
Osama.Soliman@hotmail.com
Osama.Soliman@asfourcystal.com


#3 moh

moh

    مشترك

  • الأعضــاء
  • 158 مشاركة
  • الاسم الأول:moh
  • اسم العائلة:moh
  • البـلـد: Country Flag

تاريخ المشاركة 02 May 2005 - 09:40 AM

جزاكم الله خيرا

#4 vipwithyou

vipwithyou

    مشترك

  • الأعضــاء
  • 100 مشاركة

تاريخ المشاركة 08 May 2005 - 11:04 PM

جزاكم الله خيراً
وإلى الأمام
لا إله إلا الله ... محمد رسول الله