ORACLE 11G traverses the cursor in the stored procedure, calls the scheduled execution of job tasks, and 11 gjob

ORACLE 11G traverses the cursor in the stored procedure, calls the scheduled execution of job tasks, and 11 gjob

Cursor traversal and scheduled job execution in ORACLE stored procedures

 

1. First, use the For Loop

For Loop is a simple and practical method.

First, it automatically opens and closes the cursor. It solves the problem of forgetting to open or close the cursor.

Second, automatically declare a record type and define a variable of this type, and automatically fetch the data to this variable.

Note that the C_ROW variable does not need to be declared outside the loop, and you do not need to specify the data type for it. It is a record type, and the specific structure is determined by the cursor.

The scope of this variable is only in the loop body.

Finally, after all records associated with the cursor have been retrieved, the cycle ends unconditionally, and the % NOTFOUND attribute of the cursor does not have to be determined to be TRUE.

For Loop is the best way to loop cursors. Efficient, concise, and secure.

 

Create or replace procedure PRC_LJ IS

CURSOR C_EMP IS -- acoustic explicit CURSOR

Select empno, ename from emp;

C_ROW C_EMP % ROWTYPE; -- defines the cursor variable. The variable type is a record based on the cursor C_EMP.

BEGIN

-- For Loop

FORC_ROW IN C_EMP LOOP

DBMS_OUTPUT.PUT_LINE (C_ROW.EMPNO | '--' | C_ROW.ENAME );

ENDLOOP;

 

-- Fetch Loop

OPEN C_EMP; -- the cursor must be opened and closed explicitly.

LOOP

FETCH C_EMP

INTO C_ROW;

Exit when C_EMP % NOTFOUND;

DBMS_OUTPUT.PUT_LINE (C_ROW.EMPNO | '+ +' | C_ROW.ENAME );

ENDLOOP;

CLOSE C_EMP;

 

-- While loop

OPEN C_EMP; -- the cursor must be opened and closed explicitly.

FETCH C_EMP INTO C_ROW;

WHILE C_EMP % FOUND LOOP

DBMS_OUTPUT.PUT_LINE (C_ROW.EMPNO | '**' | C_ROW.ENAME );

FETCH C_EMP INTO C_ROW;

End loop;

CLOSE C_EMP;

END PRC_LJ;

 

 

2. The second type uses the Fetch loop.

Note that the exit when statement must be followed after fetch to avoid unnecessary data processing.

The processing logic must follow exit when.

Remember to close the cursor after the loop ends.

 

Create or replace procedure PRC_LJ IS

CURSOR C_EMP IS -- acoustic explicit CURSOR

Select empno, ename from emp;

C_ROW C_EMP % ROWTYPE; -- defines the cursor variable. The variable type is a record based on the cursor C_EMP.

BEGIN

 

-- Fetch Loop

OPEN C_EMP; -- the cursor must be opened and closed explicitly.

LOOP

FETCH C_EMP

INTO C_ROW;

Exit when C_EMP % NOTFOUND;

DBMS_OUTPUT.PUT_LINE (C_ROW.EMPNO | '+ +' | C_ROW.ENAME );

ENDLOOP;

CLOSE C_EMP;

 

 

END PRC_LJ;

3. Use the While LOOP

When a while loop is used, a fetch action must be performed before the loop to make the cursor attribute take effect.

In addition, the data processing action must be placed before the fetch method in the circular body, and the fetch method in the circular body must be placed at the end; otherwise, the processing will be performed once more.

Create or replace procedure PRC_LJ IS

CURSOR C_EMP IS -- acoustic explicit CURSOR

Select empno, ename from emp;

C_ROW C_EMP % ROWTYPE; -- defines the cursor variable. The variable type is a record based on the cursor C_EMP.

BEGIN

-- While loop

OPEN C_EMP; -- the cursor must be opened and closed explicitly.

FETCH C_EMP INTO C_ROW;

WHILE C_EMP % FOUND LOOP

DBMS_OUTPUT.PUT_LINE (C_ROW.EMPNO | '**' | C_ROW.ENAME );

FETCH C_EMP INTO C_ROW;

End loop;

CLOSE C_EMP;

END PRC_LJ;

 

4. The FOR loop cursor is used FOR the stored procedure and scheduled job invocation is made.

-- The stored procedure is as follows:

Createor replace procedure BIS_QUIC_REPORT is

-- Declare a cursor

Cursorcur_proids is select t. bis_project_id from bis_project t;

-- Define the cursor variable

Cur_pidscur_proids % rowtype;

V_monthvarchar2 (2 );

V_yearvarchar2 (4 );

Begin

/** Forjack. liuon 20150331 */

Select to_char (sysdate, 'yyyy') into v_yearfrom dual;

Select case whensubstr (to_char (sysdate, 'mm'), 1, 1) = '0' then substr (to_char (sysdate, 'mm'), 2, 1) else to_char (sysdate, 'mm') end into v_month from dual;

-- Start Traversal

For cur_pids in cur_proids loop

Insertinto zzz_test (id, name, create_time) values (v_month, 'pkp _ BIS_REPORT.buildQuickReport: '| cur_pids.bis_project_id, sysdate );

Commit;

PKP_BIS_REPORT.buildQuickReport (cur_pids.bis_project_id, v_year, v_month, null );

End loop;

EndBIS_QUIC_REPORT;

 

-- The job is as follows:

Begin

Sys. dbms_scheduler.create_job (job_name => 'Jack. buildquickreport ',

Job_type => 'stored _ PROCEDURE ',

Job_action => 'bis _ QUIC_REPORT ',

Start_date => to_date ('01-04-2015 03:00:00 ', 'dd-mm-yyyy hh24: mi: ss '),

Repeat_interval => 'freq = Daily ',

End_date => to_date (null ),

Job_class => 'dbms _ JOB $ ',

Enabled => true,

Auto_drop => false,

Comments => '');

End;

/

 

5. Execute SQL in plsqland the job interface is shown as 1.png:

Bytes ----------------------------------------------------------------------------------------------------------------
<All Rights Reserved. This document can be reprinted, but the source address must be indicated by link. Otherwise, we will be held legally responsible.>
Original blog address: http://blog.itpub.net/26230597/viewspace-1479382/
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------