Solved

Oracle Cursor

Posted on 2014-03-02
21
472 Views
Last Modified: 2014-03-05
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
Comment
Question by:marrowyung
  • 10
  • 9
  • 2
21 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39898712
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
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39899381
It is having a loop among the recordset, to do the insert.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899547
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
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 150 total points
ID: 39899553
Yes, there are 2 levels of 2 looping cursors above. Both will exit till no record can be further fetched.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 350 total points
ID: 39899556
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39899578
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39899582
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39899585
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39899615
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39899641
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:marrowyung
ID: 39899699
"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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39900570
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39900709
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39900724
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39900730
"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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39900767
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39902313
"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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39902351
%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
 
LVL 1

Author Comment

by:marrowyung
ID: 39906699
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39906762
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
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39908466
I think I understand !
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now