SQL Private work Area where the query get passed and executed.
Types
******
Implicit(SQL)
Explicit
-->-- advanced cursor types --<--
Parametrized Cursor
Ref Cursor
Implicit
********
ORACLE implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor.
PL/SQL lets you refer to the most recent implicit cursor as the SQL” cursor. So, although you cannot use the OPEN,
FETCH, and CLOSE statements to control an implicit cursor, you can still use cursor attributes to access information
about the most recently executed SQL statement.
Cursor Stages
**************
OPEN cursor_name
FETCH (with in loop)
CLOSE cursor_name
Explicit
The set of rows returned by a query can consist of zero, one, or multiple rows,
depending on how many rows meet your search criteria. When a query returns multiple rows,
you can explicitly define a cursor to process the rows. You use three commands to control the cursor
Cursor Attributes
*****************
%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT
%BULK_ROWCOUNT --<-- used to handle error while using bulk collection.
%BULK_EXCEPTIONS --<-- used to handle error while using bulk collection.
Cursor Declaration
DECLARE
CURSOR <cursor_name>
IS
<SELECT statement>
BEGIN
--> some stmt
END;
/
Cursor Loop
- Loop
- While Loop
- For Loop
- Return
- For update
- Where current of
- Bulk collect
-->-- creating table
create table product_details
(
p_id NUMBER,
p_name VARCHAR2(30),
p_order_dt DATE
);
-->-- Inserting data
BEGIN
FOR i IN 1 .. 75 LOOP
INSERT INTO product_details VALUES (i, 'prod_name_' || i, SYSDATE + i);
END LOOP;
commit;
END;
-->-- selecting data
SELECT * FROM product_details;
---------------------------------------------
p_id p_name p_order_dt
---------------------------------------------
1 prod_name_1 10/11/2014 3:48:32 PM
2 prod_name_2 10/12/2014 3:48:32 PM
3 prod_name_3 10/13/2014 3:48:32 PM
4 prod_name_4 10/14/2014 3:48:32 PM
5 prod_name_5 10/15/2014 3:48:32 PM
6 prod_name_6 10/16/2014 3:48:32 PM
7 prod_name_7 10/17/2014 3:48:32 PM
8 prod_name_8 10/18/2014 3:48:32 PM
9 prod_name_9 10/19/2014 3:48:32 PM
10 prod_name_10 10/20/2014 3:48:32 PM
11 prod_name_11 10/21/2014 3:48:32 PM
12 prod_name_12 10/22/2014 3:48:32 PM
13 prod_name_13 10/23/2014 3:48:32 PM
14 prod_name_14 10/24/2014 3:48:32 PM
15 prod_name_15 10/25/2014 3:48:32 PM
16 prod_name_16 10/26/2014 3:48:32 PM
17 prod_name_17 10/27/2014 3:48:32 PM
18 prod_name_18 10/28/2014 3:48:32 PM
19 prod_name_19 10/29/2014 3:48:32 PM
20 prod_name_20 10/30/2014 3:48:32 PM
---------------------------------------------
----------------------------------------------
Implicit Cursor
----------------------------------------------
--will update soon
----------------------------------------------
Explicit Cursor
----------------------------------------------
**********************************************
Processing cursor data by using LOOP
**********************************************
DECLARE
all_data product_details%ROWTYPE;
--cursor declaration
CURSOR prod_detail_cur IS
SELECT * FROM product_details
WHERE ROWNUM <=5;
BEGIN
OPEN prod_detail_cur;
LOOP
FETCH prod_detail_cur
INTO all_data;
EXIT WHEN prod_detail_cur%NOTFOUND; --<-- if next record not found means control will come out from the loop
dbms_output.put_line('Product id : ' || all_data.p_id ||
' Product Name : ' || all_data.p_name ||
' ( Orderd on : ' || TRIM(all_data.p_order_dt) || ' )');
END LOOP;
CLOSE prod_detail_cur;
END;
/*sample output*/
Product id : 1 Product Name : prod_name_1 ( Orderd on : 11-OCT-14 )
Product id : 2 Product Name : prod_name_2 ( Orderd on : 12-OCT-14 )
Product id : 3 Product Name : prod_name_3 ( Orderd on : 13-OCT-14 )
Product id : 4 Product Name : prod_name_4 ( Orderd on : 14-OCT-14 )
Product id : 5 Product Name : prod_name_5 ( Orderd on : 15-OCT-14 )
DECLARE
all_data product_details%ROWTYPE;
--cursor declaration
CURSOR prod_detail_cur IS
SELECT * FROM product_details
WHERE ROWNUM <=5;
BEGIN
OPEN prod_detail_cur;
LOOP
FETCH prod_detail_cur
INTO all_data;
dbms_output.put_line('Product id : ' || all_data.p_id ||
' Product Name : ' || all_data.p_name ||
' ( Orderd on : ' || TRIM(all_data.p_order_dt) || ' )');
EXIT WHEN prod_detail_cur%FOUND; --<-- if next record found means control will come out from the loop
END LOOP;
CLOSE prod_detail_cur;
END;
/*sample output*/
Product id : 1 Product Name : prod_name_1 ( Orderd on : 11-OCT-14 )
DECLARE
all_data product_details%ROWTYPE;
--cursor declaration
CURSOR prod_detail_cur IS
SELECT * FROM product_details
WHERE ROWNUM <=5;
BEGIN
OPEN prod_detail_cur;
LOOP
FETCH prod_detail_cur
INTO all_data;
EXIT WHEN prod_detail_cur%NOTFOUND;
dbms_output.put_line('Product id : ' || all_data.p_id ||
' Product Name : ' || all_data.p_name ||
' ( Orderd on : ' || TRIM(all_data.p_order_dt) || ' )');
END LOOP;
CLOSE prod_detail_cur;
IF prod_detail_cur%ISOPEN THEN
CLOSE prod_detail_cur;
dbms_output.put_line('prod_detail_cur cursor closed');
ELSE
dbms_output.put_line('prod_detail_cur cursor Already closed');
END IF;
END;
/*sample output*/
Product id : 1 Product Name : prod_name_1 ( Orderd on : 11-OCT-14 )
Product id : 2 Product Name : prod_name_2 ( Orderd on : 12-OCT-14 )
Product id : 3 Product Name : prod_name_3 ( Orderd on : 13-OCT-14 )
Product id : 4 Product Name : prod_name_4 ( Orderd on : 14-OCT-14 )
Product id : 5 Product Name : prod_name_5 ( Orderd on : 15-OCT-14 )
prod_detail_cur cursor Already closed
DECLARE
all_data product_details%ROWTYPE;
--cursor declaration
CURSOR prod_detail_cur IS
SELECT * FROM product_details
WHERE ROWNUM <=5;
BEGIN
OPEN prod_detail_cur;
LOOP
FETCH prod_detail_cur
INTO all_data;
EXIT WHEN prod_detail_cur%NOTFOUND;
dbms_output.put_line('Product id : ' || all_data.p_id ||
' Product Name : ' || all_data.p_name ||
' ( Orderd on : ' || TRIM(all_data.p_order_dt) || ' )');
END LOOP;
-->-- CLOSE prod_detail_cur;
IF prod_detail_cur%ISOPEN THEN
CLOSE prod_detail_cur;
dbms_output.put_line('prod_detail_cur cursor closed');
ELSE
dbms_output.put_line('prod_detail_cur cursor Already closed');
END IF;
END;
/*sample output*/
Product id : 1 Product Name : prod_name_1 ( Orderd on : 11-OCT-14 )
Product id : 2 Product Name : prod_name_2 ( Orderd on : 12-OCT-14 )
Product id : 3 Product Name : prod_name_3 ( Orderd on : 13-OCT-14 )
Product id : 4 Product Name : prod_name_4 ( Orderd on : 14-OCT-14 )
Product id : 5 Product Name : prod_name_5 ( Orderd on : 15-OCT-14 )
prod_detail_cur cursor closed
**********************************************
Processing cursor data by using WHILE LOOP
**********************************************
DECLARE
all_data product_details%ROWTYPE;
--cursor declaration
CURSOR prod_detail_cur IS
SELECT * FROM product_details WHERE ROWNUM <= 5;
BEGIN
OPEN prod_detail_cur;
FETCH prod_detail_cur
INTO all_data;
WHILE prod_detail_cur%FOUND LOOP
dbms_output.put_line('Product id : ' || all_data.p_id ||
' Product Name : ' || all_data.p_name ||
' ( Orderd on : ' || TRIM(all_data.p_order_dt) || ' )');
FETCH prod_detail_cur
INTO all_data;
END LOOP;
CLOSE prod_detail_cur;
END;
/*sample output*/
Product id : 1 Product Name : prod_name_1 ( Orderd on : 11-OCT-14 )
Product id : 2 Product Name : prod_name_2 ( Orderd on : 12-OCT-14 )
Product id : 3 Product Name : prod_name_3 ( Orderd on : 13-OCT-14 )
Product id : 4 Product Name : prod_name_4 ( Orderd on : 14-OCT-14 )
Product id : 5 Product Name : prod_name_5 ( Orderd on : 15-OCT-14 )
************************************************************
Processing cursor data by using FOR LOOP - CURSOR FOR LOOP
************************************************************
DECLARE
--cursor declaration
CURSOR prod_detail_cur IS
SELECT * FROM product_details WHERE ROWNUM <= 5;
BEGIN
FOR i IN prod_detail_cur
LOOP
dbms_output.put_line('Product id : ' || i.p_id ||
' Product Name : ' || i.p_name ||
' ( Orderd on : ' || TRIM(i.p_order_dt) || ' )');
END LOOP;
END;
/*sample output*/
Product id : 1 Product Name : prod_name_1 ( Orderd on : 11-OCT-14 )
Product id : 2 Product Name : prod_name_2 ( Orderd on : 12-OCT-14 )
Product id : 3 Product Name : prod_name_3 ( Orderd on : 13-OCT-14 )
Product id : 4 Product Name : prod_name_4 ( Orderd on : 14-OCT-14 )
Product id : 5 Product Name : prod_name_5 ( Orderd on : 15-OCT-14 )
Parameterized Cursor
*********************
This was used when you are going to use the cursor in more than one place with different values for the same where clause.
Cursor parameters must be in mode.
Cursor parameters may have default values.
The scope of cursor parameter is within the select statement.
CREATE TABLE dep_details(d_id NUMBER,d_name VARCHAR2(30), location_id NUMBER);
BEGIN
INSRT INTO dep_details VALUES(001,'Admin',1010);
INSRT INTO dep_details VALUES(002,'HR',1010);
INSRT INTO dep_details VALUES(003,'Sales',1020);
INSRT INTO dep_details VALUES(004,'Marketing',1020);
commit;
END;
SELECT * FROM dep_details;
----------------------------
d_id d_name location_id
----------------------------
1 Admin 1010
2 HR 1010
3 Sales 1020
4 Marketing 1020
----------------------------
DECLARE
-->--Declaring parameterized cursor
CURSOR dep_det_c(p_location_id NUMBER) IS
SELECT * FROM dep_details d WHERE d.location_id = p_location_id;
all_data dep_details%ROWTYPE;
BEGIN
dbms_output.put_line('-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');
OPEN dep_det_c(1010);
LOOP
FETCH dep_det_c
INTO all_data;
EXIT WHEN dep_det_c%NOTFOUND;
dbms_output.put_line('Department id : ' || all_data.d_id ||
' Department Name : ' || all_data.d_name);
END LOOP;
CLOSE dep_det_c;
dbms_output.put_line('-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');
-->-- opening same cursor with different input value
OPEN dep_det_c(1020);
LOOP
FETCH dep_det_c
INTO all_data;
EXIT WHEN dep_det_c%NOTFOUND;
dbms_output.put_line('Department id : ' || all_data.d_id ||
' Department Name : ' || all_data.d_name);
END LOOP;
CLOSE dep_det_c;
dbms_output.put_line('-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');
END;
/
/*sample output*/
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Department id : 1 Department Name : Admin
Department id : 2 Department Name : HR
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Department id : 3 Department Name : Sales
Department id : 4 Department Name : Marketing
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Super Document for Reference
ReplyDelete