How to write Oracle stored procedure cursors for loops

First, write the overall structure of the stored procedure, as follows:

Create or replace procedure test_proc is

V_date date; -Variable definition

begin

Select sysdate to v _ date from dual.

end test _ proc

2

Define cursor:

Create or replace procedure test_proc is

V_date date; -Define variables

Cursor cur is select * from ldcode- defines the cursor.

begin

Select sysdate to v _ date from dual.

end test _ proc

three

Write a for loop:

Create or replace procedure test_proc is

V_date date; -Define variables

Cursor Cur is select * from ldcode, where rownum <10; -Define cursor

begin

Select sysdate to v _ date from dual.

-Cursor at the beginning of the loop

For temp in the cur loop-temp is a temporary variable name, you can choose it yourself.

Output of database management system. Code); -Use Variable Name to output a field. Column name ".

End the cycle;

-Cursor at the end of the loop

end test _ proc

four

Test run, click the DBMS output tab to view the results, as shown below:

end

Second, the cursor is used for loops with parameters.

1

Define a cursor with parameters:

cursor cur(v_codetype ldcode。 Codetype%TYPE) is

Select * from ldcode where codetype = v _ codetype- defines the cursor.

Define cursor format:

Cursor cursor name (variable definition) is a query statement;

note:

The variable name v_codetype in the where condition should be consistent with the variable name in the cursor definition cur (v _ codetype ldcode). codetype% type)。

2

Write cycle part:

-Cursor at the beginning of the loop

For temp in cur('llmedfeetype') loop.

-temp is a temporary variable name, you can choose it yourself.

-cur('llmedfeetype') is "cursor name (passed variable)"

Output of database management system. Code); -Use Variable Name to output a field. Column name ".

End the cycle;

-Cursor at the end of the loop

three

Test run, click the DBMS output tab to view the results, as shown below: