ORACLE 11G iterates through the cursor inside the stored procedure, calling the job task to run periodically

CURSOR traversal in Oracle stored procedure, call job timed run

1, first use for loop

The For loop is a simpler and more useful method.

First of all. It will take its own initiative open and close cursors. Overcomes the annoyance of forgetting to open or close a cursor.

Second, you declare a record type and define variables of that type, and fetch the data to this variable by yourself.

Note that the C_row variable does not need to be declared outside of the loop, and it does not require a data type to be specified. It is a record type, and the detailed structure is determined by the cursor.

This variable is scoped to the body of the loop.

Finally, all the records associated with the cursor have been retrieved. The loop unconditionally ends without having to determine that the%notfound property of the cursor is true.

The For loop is the best way to loop a cursor. efficient, concise and safe.

CREATE OR REPLACE PROCEDURE Prc_lj is

Cursor C_emp is--sound-clear cursor

SELECT empno,ename from EMP;

C_row C_emp%rowtype; --Defines the cursor variable. The type of the variable is a record of the base cursor c_emp

BEGIN

--for Cycle

Forc_row in C_emp LOOP

Dbms_output. Put_Line (C_row. EMPNO | | '---' | | C_row. ENAME);

Endloop;

--fetch Cycle

Open c_emp;--necessary to understand the opening and closing of cursors

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 Cycle

Open c_emp;--necessary to understand the opening and closing of cursors

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, another use of a fetch loop

Note that the exit when statement must immediately follow the fetch. Avoid unnecessary data processing.

The processing logic needs to follow the exit when.

Remember to close the cursor when the loop is finished.

CREATE OR REPLACE PROCEDURE Prc_lj is

Cursor C_emp is--sound-clear cursor

SELECT empno,ename from EMP;

C_row C_emp%rowtype; --Define a cursor variable with the type of the base cursor c_emp record

BEGIN

--fetch Cycle

Open c_emp;--necessary to understand the opening and closing of cursors

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, the third uses the while loop

When using a while loop, a fetch action is required before the loop, and the properties of the cursor do not work.

and the data processing action must be placed before the Fetch method in the loop body. The Fetch method in the loop body should be placed at the end, otherwise it will be processed more than once.

CREATE OR REPLACE PROCEDURE Prc_lj is

Cursor C_emp is--sound-clear cursor

SELECT empno,ename from EMP;

C_row C_emp%rowtype; --Defines the cursor variable. The type of the variable is a record of the base cursor c_emp

BEGIN

--while Cycle

Open c_emp;--necessary to understand the opening and closing of cursors

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 stored procedure uses a for loop to traverse the cursor. And make a timed job call to run

--Storage steps such as the following:

Createor Replace procedure Bis_quic_report is

--Declaring cursors

Cursorcur_proids is the select t.bis_project_id from Bis_project t;

--Define cursor variables

Cur_pidscur_proids%rowtype;

V_MONTHVARCHAR2 (2);

V_YEARVARCHAR2 (4);

Begin

/**forjack.liu on 20150331*/

Select To_char (sysdate, ' yyyy ') into V_yearfrom dual;

Select Case Whensubstr (To_char (sysdate, ' mm '), "0" then substr (To_char (sysdate, ' mm '), 2,1) Else To_char (Sysdate, ' MM ') end into v_month from dual;

--Start traversing

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,null,null,null);

End Loop;

Endbis_quic_report;

--job tasks such as the following:

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, in the Plsql inside the Sql,job interface display demo sample such as the following 1.png:

 ----------------------------------------------------------------------------------------------------------- -----
< All rights reserved. The article agreed to reprint, but must be linked to the source address, otherwise investigate legal liability!>
Original Blog Address:http://blog.itpub.net/26230597/viewspace-1479382/
Hara Douglas Fir (MCHDBA)
----------------------------------------------------------------------------------------------------------- -----

ORACLE 11G iterates through the cursor inside the stored procedure, calling the job task to run periodically