[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

Oracle Cursor

Dear all,

Any cursor usage in this script :

set serveroutput on -- TO BE ABLE TO USE THE DBMS_OUTPUT PACKAGE
DECLARE
	vMEASUREDESC     VARCHAR(4000);
	vTYPEDESC    	 VARCHAR(4000);
	vALARMDESC   	 VARCHAR(4000);
BEGIN

	DBMS_OUTPUT.ENABLE( 1000000 ) ; 

	FOR CurCONTRACTS IN (                 
		SELECT SCHEMA_ID FROM EXT_ADMIN.EXT_CONTRACTS
	) LOOP 
		FOR CurINNER IN (   
            SELECT COALESCE(A.RT_HOUR, B.RT_HOUR) "RT_HOUR", COALESCE(A.CONTRACT_ID,B.CONTRACT_ID) "RT_CONTRACT_ID", A.RT_1_IC_ASR "RT_1_IC_ASR", A.RT_1_OG_ASR "RT_1_OG_ASR", A.RT_1_IC_ALOC "RT_1_IC_ALOC", A.RT_1_OG_ALOC "RT_1_OG_ALOC", A.RT_1_IC_DURATION "RT_1_IC_DURATION", A.RT_1_OG_DURATION "RT_1_OG_DURATION", A.RT_1_IC_CALLATT "RT_1_IC_CALLATT", A.RT_1_OG_CALLATT "RT_1_OG_CALLATT", B.RT_2_IC_ASR "RT_2_IC_ASR", B.RT_2_OG_ASR "RT_2_OG_ASR", B.RT_2_IC_ALOC "RT_2_IC_ALOC", B.RT_2_OG_ALOC "RT_2_OG_ALOC", B.RT_2_IC_DURATION "RT_2_IC_DURATION", B.RT_2_OG_DURATION "RT_2_OG_DURATION", B.RT_2_IC_CALLATT "RT_2_IC_CALLATT", B.RT_2_OG_CALLATT "RT_2_OG_CALLATT"  FROM
            (
            SELECT T1.HOUR "RT_HOUR", T1.CONTRACT_ID "CONTRACT_ID", ROUND(T1.RT_1_IC_ASR,2) "RT_1_IC_ASR", ROUND(T2.RT_1_OG_ASR,2) "RT_1_OG_ASR", ROUND(T1.RT_1_IC_ALOC,2) "RT_1_IC_ALOC", ROUND(T2.RT_1_OG_ALOC,2) "RT_1_OG_ALOC", ROUND(T1.RT_1_IC_DURATION,2) "RT_1_IC_DURATION", ROUND(T2.RT_1_OG_DURATION,2) "RT_1_OG_DURATION", T1.RT_1_IC_CALLATT "RT_1_IC_CALLATT", T2.RT_1_OG_CALLATT "RT_1_OG_CALLATT" FROM
            (SELECT RT_HOUR "HOUR", RT_IC_CONTRACT_ID "CONTRACT_ID", DECODE (SUM (RT_NBTOT), 0, 0, (SUM (RT_NBOK) / SUM (RT_NBTOT)) * 100) "RT_1_IC_ASR", DECODE (SUM (RT_NBOK), 0, 0, (SUM (RT_DURATION) / SUM (RT_NBOK)) / 60) "RT_1_IC_ALOC", SUM (RT_DURATION)/60 "RT_1_IC_DURATION", SUM (RT_NBTOT) "RT_1_IC_CALLATT" FROM DEN_REALTIME_DM WHERE RT_IC_CONTRACT_ID = CurCONTRACTS.SCHEMA_ID AND TRUNC(RT_TENMINPERIOD,'DD') = TRUNC(SYSDATE-1,'DD') GROUP BY RT_HOUR, RT_IC_CONTRACT_ID ORDER BY RT_HOUR ASC) T1,
            (SELECT RT_HOUR "HOUR", RT_OG_CONTRACT_ID "CONTRACT_ID", DECODE (SUM (RT_NBTOT), 0, 0, (SUM (RT_NBOK) / SUM (RT_NBTOT)) * 100) "RT_1_OG_ASR", DECODE (SUM (RT_NBOK), 0, 0, (SUM (RT_DURATION) / SUM (RT_NBOK)) / 60) "RT_1_OG_ALOC", SUM (RT_DURATION)/60 "RT_1_OG_DURATION", SUM (RT_NBTOT) "RT_1_OG_CALLATT" FROM DEN_REALTIME_DM WHERE RT_OG_CONTRACT_ID = CurCONTRACTS.SCHEMA_ID AND TRUNC(RT_TENMINPERIOD,'DD') = TRUNC(SYSDATE-1,'DD') GROUP BY RT_HOUR, RT_OG_CONTRACT_ID ORDER BY RT_HOUR ASC) T2
            WHERE T1.HOUR = T2.HOUR
            ) A,
            (
            SELECT T1.HOUR "RT_HOUR", T1.CONTRACT_ID "CONTRACT_ID", ROUND(T1.RT_2_IC_ASR,2) "RT_2_IC_ASR", ROUND(T2.RT_2_OG_ASR,2) "RT_2_OG_ASR", ROUND(T1.RT_2_IC_ALOC,2) "RT_2_IC_ALOC", ROUND(T2.RT_2_OG_ALOC,2) "RT_2_OG_ALOC", ROUND(T1.RT_2_IC_DURATION,2) "RT_2_IC_DURATION", ROUND(T2.RT_2_OG_DURATION,2) "RT_2_OG_DURATION", T1.RT_2_IC_CALLATT "RT_2_IC_CALLATT", T2.RT_2_OG_CALLATT "RT_2_OG_CALLATT" FROM
            (SELECT RT_HOUR "HOUR", RT_IC_CONTRACT_ID "CONTRACT_ID", DECODE (SUM (RT_NBTOT), 0, 0, (SUM (RT_NBOK) / SUM (RT_NBTOT)) * 100) "RT_2_IC_ASR", DECODE (SUM (RT_NBOK), 0, 0, (SUM (RT_DURATION) / SUM (RT_NBOK)) / 60) "RT_2_IC_ALOC", SUM (RT_DURATION)/60 "RT_2_IC_DURATION", SUM (RT_NBTOT) "RT_2_IC_CALLATT" FROM DEN_REALTIME_DM WHERE RT_IC_CONTRACT_ID = CurCONTRACTS.SCHEMA_ID AND TRUNC(RT_TENMINPERIOD,'DD') = TRUNC(SYSDATE,'DD') GROUP BY RT_HOUR, RT_IC_CONTRACT_ID ORDER BY RT_HOUR ASC) T1,
            (SELECT RT_HOUR "HOUR", RT_OG_CONTRACT_ID "CONTRACT_ID", DECODE (SUM (RT_NBTOT), 0, 0, (SUM (RT_NBOK) / SUM (RT_NBTOT)) * 100) "RT_2_OG_ASR", DECODE (SUM (RT_NBOK), 0, 0, (SUM (RT_DURATION) / SUM (RT_NBOK)) / 60) "RT_2_OG_ALOC", SUM (RT_DURATION)/60 "RT_2_OG_DURATION", SUM (RT_NBTOT) "RT_2_OG_CALLATT" FROM DEN_REALTIME_DM WHERE RT_OG_CONTRACT_ID = CurCONTRACTS.SCHEMA_ID AND TRUNC(RT_TENMINPERIOD,'DD') = TRUNC(SYSDATE,'DD') GROUP BY RT_HOUR, RT_OG_CONTRACT_ID ORDER BY RT_HOUR ASC) T2
            WHERE T1.HOUR = T2.HOUR
            AND T1.HOUR <= (SELECT TO_CHAR(MAX(RT_TENMINPERIOD),'HH24')-4 FROM DEN_REALTIME_DM)
            ) B
            WHERE A.RT_HOUR = B.RT_HOUR (+)      
		) LOOP
		    INSERT INTO DEN_REALTIME_EXT (RT_HOUR, RT_CONTRACT_ID, RT_1_IC_ASR, RT_1_OG_ASR, RT_1_IC_ALOC, RT_1_OG_ALOC, RT_1_IC_DURATION, RT_1_OG_DURATION, RT_1_IC_CALLATT, RT_1_OG_CALLATT, RT_2_IC_ASR, RT_2_OG_ASR, RT_2_IC_ALOC, RT_2_OG_ALOC, RT_2_IC_DURATION, RT_2_OG_DURATION, RT_2_IC_CALLATT, RT_2_OG_CALLATT) VALUES (CurINNER.RT_HOUR, CurINNER.RT_CONTRACT_ID, CurINNER.RT_1_IC_ASR, CurINNER.RT_1_OG_ASR, CurINNER.RT_1_IC_ALOC, CurINNER.RT_1_OG_ALOC, CurINNER.RT_1_IC_DURATION, CurINNER.RT_1_OG_DURATION, CurINNER.RT_1_IC_CALLATT, CurINNER.RT_1_OG_CALLATT, CurINNER.RT_2_IC_ASR, CurINNER.RT_2_OG_ASR, CurINNER.RT_2_IC_ALOC, CurINNER.RT_2_OG_ALOC, CurINNER.RT_2_IC_DURATION, CurINNER.RT_2_OG_DURATION, CurINNER.RT_2_IC_CALLATT, CurINNER.RT_2_OG_CALLATT);  
		END LOOP ;              
	END LOOP ; 


    DBMS_OUTPUT.PUT_LINE( vALARMDESC ) ;
    
END;

Open in new window

0
marrowyung
Asked:
marrowyung
  • 10
  • 9
  • 2
2 Solutions
 
Steve WalesSenior Database AdministratorCommented:
The FOR CurCONTRACTS IN is an implicit "for loop cursor" as well as "For CurINNER".

Here's the doc on Cursors from the the Online documentation:

http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/cursor_for_loop_statement.htm#LNPLS1155
0
 
HuaMinChenBusiness AnalystCommented:
It is having a loop among the recordset, to do the insert.
0
 
marrowyungAuthor Commented:
one thing, is that mean Oracle has more than one type of cursor?

1)Explicit Cursor
2) Implicit Cursor
?

So this mean there are 2 x Implicit Cursors?

1) FOR CurCONTRACTS IN (                 
		SELECT SCHEMA_ID FROM EXT_ADMIN.EXT_CONTRACTS
	) LOOP 
		
2) FOR CurINNER IN (   
            SELECT COALESCE(A.RT_HOUR, B.RT_HOUR) "RT_HOUR", COALESCE(A.CONTRACT_ID,B.CONTRACT_ID) "RT_CONTRACT_ID", A.RT_1_IC_ASR "RT_1_IC_ASR", A.RT_1_OG_ASR "RT_1_OG_ASR", A.RT_1_IC_ALOC "RT_1_IC_ALOC", A.RT_1_OG_ALOC "RT_1_OG_ALOC", A.RT_1_IC_DURATION "RT_1_IC_DURATION", A.RT_1_OG_DURATION "RT_1_OG_DURATION", A.RT_1_IC_CALLATT "RT_1_IC_CALLATT", A.RT_1_OG_CALLATT "RT_1_OG_CALLATT", B.RT_2_IC_ASR "RT_2_IC_ASR", B.RT_2_OG_ASR "RT_2_OG_ASR", B.RT_2_IC_ALOC "RT_2_IC_ALOC", B.RT_2_OG_ALOC "RT_2_OG_ALOC", B.RT_2_IC_DURATION "RT_2_IC_DURATION", B.RT_2_OG_DURATION "RT_2_OG_DURATION", B.RT_2_IC_CALLATT "RT_2_IC_CALLATT", B.RT_2_OG_CALLATT "RT_2_OG_CALLATT"  FROM
            (

Open in new window


?

then what is the condition to keep the cursor loop until the end  ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HuaMinChenBusiness AnalystCommented:
Yes, there are 2 levels of 2 looping cursors above. Both will exit till no record can be further fetched.
0
 
Steve WalesSenior Database AdministratorCommented:
Yes, looks like 2 implicit cursors.

You can also explicitly declare cursors.

Off the top of my head, the syntax is

declare cursor cur1  as select x,y,z from tab1 where col1 = val1;

In the code you open it, fetch from it and close it when done.
0
 
marrowyungAuthor Commented:
ok, by that example, I don't see the condition of keep looping, where is it?

in mySQL and MS SQL, it seems it is much easelier to read .

what is the exist condition ?
0
 
marrowyungAuthor Commented:
what I found from the link is :

ANOTHER example:

DECLARE
  CURSOR c1 IS
    SELECT t1.department_id, department_name, staff
    FROM departments t1,
         ( SELECT department_id, COUNT(*) AS staff
           FROM employees
           GROUP BY department_id
         ) t2
    WHERE (t1.department_id = t2.department_id) AND staff >= 5
    ORDER BY staff;

BEGIN
   FOR dept IN c1
   LOOP
     DBMS_OUTPUT.PUT_LINE ('Department = '
       || dept.department_name || ', staff = ' || dept.staff);
   END LOOP;
END;
/

Open in new window


This is the keep looping conditon :

   FOR dept IN c1

Open in new window

?

What is this means:

     DBMS_OUTPUT.PUT_LINE ('Department = '
       || dept.department_name || ', staff = ' || dept.staff);

Open in new window

?
0
 
Steve WalesSenior Database AdministratorCommented:
The For .. in controls the loop through the rows in the cursor, yes.

DBMS_OUTPUT.PUT_LINE will just output the text on your screen in a sqlplus session (if set serveroutput on is run first).
0
 
marrowyungAuthor Commented:
so this

FOR dept IN c1 

Open in new window


means, as long as dept still in the result set of c1, then keep going ?

then I don't see statement to fetch the NEXT cursor result. Any ?
0
 
Steve WalesSenior Database AdministratorCommented:
It's handled as a part of the construct.

for variable in cursor
LOOP
  Do things referencing variable.column_name
END

Will process all rows until end of cursor fetch.
0
 
marrowyungAuthor Commented:
"Will process all rows until end of cursor fetch. "

So thie means no need to close it or fetch next in oracle and it will drop out of the loop once it can see that it is an end ?
0
 
Steve WalesSenior Database AdministratorCommented:
To the best of my knowledge (I am a DBA, not a developer, I don't get down to detailed code very often), when using an implicit cursor construct, you don't need to close anything or fetch next.

The easy way is to test it.  Write a small code block and see what happens.  You can download a copy of Oracle XE for free from www.oracle.com.  Install it on your computer and play with things.  It is a great way to learn and try things out.

A small test snippet shows that it works just like that:
SQL> create table t1 (col1 char(1));

Table created.

SQL> insert into t1 values ('A');

1 row created.

SQL> insert into t1 values ('B');

1 row created.

SQL> insert into t1 values ('C');

1 row created.

SQL> set serveroutput on
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT t1.col1
  4      from t1
  5      ORDER BY col1;
  6
  7  BEGIN
  8     FOR letter IN c1
  9     LOOP
 10       DBMS_OUTPUT.PUT_LINE ('letter = ' || letter.col1);
 11     END LOOP;
 12  END;
 13  /
letter = A
letter = B
letter = C

PL/SQL procedure successfully completed.

Open in new window

0
 
marrowyungAuthor Commented:
in here, something I don't understand":

FOR letter IN c1

Open in new window


we don't need to letter to be declare?
0
 
Steve WalesSenior Database AdministratorCommented:
Does not need to be specifically declared.  Go back to the reference to the documentation I posted in my first reply.

"Letter" is referred to there as record.  The docs state:

record

Name for the loop index that the cursor FOR LOOP statement implicitly declares as a %ROWTYPE record variable of the type that cursor or select_statement returns.

record is local to the cursor FOR LOOP statement. Statements inside the loop can reference record and its fields. They can reference virtual columns only by aliases. Statements outside the loop cannot reference record. After the cursor FOR LOOP statement runs, record is undefined.


Seems to be a specific local variable used by this particular construct.  Can't tell you the why - it just is that way.
0
 
marrowyungAuthor Commented:
"Can't tell you the why - it just is that way. "

yeah, I think so.

"Name for the loop index that the cursor FOR LOOP statement implicitly declares as a %ROWTYPE record variable of the type that cursor or select_statement returns.

record is local to the cursor FOR LOOP statement. Statements inside the loop can reference record and its fields. They can reference virtual columns only by aliases. Statements outside the loop cannot reference record. After the cursor FOR LOOP statement runs, record is undefined."

very hard to understand.

any simplified answer ?
0
 
Steve WalesSenior Database AdministratorCommented:
It's saying that the name is a local variable that has a scope of the loop only.

Any statements between LOOP and END LOOP can reference that variable as a reference to the implicit cursor.

Once you get outside the LOOP, the variable is no longer valid.

So if the cursor is a select that selects COL1 which is defined as CHAR(5) and COL2 which is defined as number you can reference record.COL1 and it knows it is a CHAR(5) and record.COL2 and it knows that it's a number.

The %ROWTYPE reference says that it will automatically detect the data type.
0
 
marrowyungAuthor Commented:
"Any statements between LOOP and END LOOP can reference that variable as a reference to the implicit cursor."

I think that Letter is a point ! all cursor has a point pointing to the current one and the next one.

"Once you get outside the LOOP, the variable is no longer valid."

yeah, it should be!!

"
So if the cursor is a select that selects COL1 which is defined as CHAR(5) and COL2 which is defined as number you can reference record.COL1 and it knows it is a CHAR(5) and record.COL2 and it knows that it's a number."

I understand.

"The %ROWTYPE reference says that it will automatically detect the data type. "

where is the %ROWTYPE  you are talking about ?
0
 
Steve WalesSenior Database AdministratorCommented:
%ROWTYPE was mentioned in the documentation explaining the explicit cursor.

You'd be better off reading the documentation on that particular part of it and what it means.

http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_packages.htm#ADFNS009
0
 
marrowyungAuthor Commented:
sorry,

it just said "Use the %ROWTYPE attribute to create a record that contains all the columns of the specified table" which can be all field type!

I don't see other like what you say, can you copy the statement you read here ?
0
 
Steve WalesSenior Database AdministratorCommented:
In response #39900724 I quoted from the Oracle documentation that said

"Name for the loop index that the cursor FOR LOOP statement implicitly declares as a %ROWTYPE record variable of the type that cursor or select_statement returns."


The only reason I brought it up at all is because it was mentioned there.

I believe that the whole point of %ROWTYPE is that you can reference a column of any data type that is returned from the cursor without having to explicitly declare variables - however we may be getting a little too deep into the how exactly it works - you're very much better off consulting the documentation linked above for the exact specifics of what it does and how it works - as mentioned above, I'm a DBA, not a developer :)
0
 
marrowyungAuthor Commented:
I think I understand !
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 10
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now