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

CURSOR traversal in Oracle stored procedure, call job timed execution

1, first use for loop

The For loop is a relatively simple and practical method.

First, it will automatically open and close cursors. Resolves the annoyance that you forgot to turn the cursor on or off.

Next, automatically declares a record type and defines the type of variable, and automatically fetch the data to that variable.

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 specific structure is determined by the cursor.

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

Finally, after all the records associated with the cursor have been fetched, the loop unconditionally ends without having to determine that the cursor's%notfound property 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; --Define a cursor variable with the type of the base cursor c_emp record

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;--must explicitly turn cursors on and off

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;--must explicitly turn cursors on and off

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 use of the fetch loop

Note that the exit when statement must immediately follow the fetch to 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;--must explicitly turn cursors on and off

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 in the loop in the body of the fetch method, the loop in the body of the fetch method to 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; --Define a cursor variable with the type of the base cursor c_emp record

BEGIN

--while Cycle

Open c_emp;--must explicitly turn cursors on and off

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 chooses the FOR loop traversal cursor, and makes the timer job call execution

--The stored procedure is as follows:

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

End Loop;

Endbis_quic_report;

The--job tasks are 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, in Plsql the above Sql,job interface shows the following 1.png:

 ----------------------------------------------------------------------------------------------------------- -----
< Copyright, the article allows 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 execute periodically