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