Use the Oracle Stored Procedure cursor

Example:

create or replace procedure test is     v_count number(4);       cursor v_c is    select * from "t2VoucherHandNo";    begin     select count(*) into v_count from "t2VoucherHandNo";    if v_count>1 then   raise too_many_rows;   else   for my_v in v_c   loop   dbms_output.put_line(my_v.FNO);   end loop;   end if; end; 

Basic Oracle Stored Procedure syntax

1. Basic Structure
Create or replace procedure
(
Parameter 1 in number,
Parameter 2 in number
) Is
Variable 1 integer: = 0;
Variable 2 date;
Begin

End stored procedure name

2. Select into statement
Save the result of the SELECT query to a variable. Multiple columns can be stored in multiple variables at the same time. One
Record; otherwise, an exception is thrown (if no record exists, no_data_found is thrown)
Example:
Begin
Select col1, col2 into variable 1, variable 2 from typestruct Where xxx;
Exception
When no_data_found then
Xxxx;
End;
...

3. If judgment
If v_test = 1 then
Begin
Do something
End;
End if;

4. While Loop
While v_test = 1 Loop
Begin
Xxxx
End;
End loop;

5. Variable assignment
V_test: = 123;

6. Use cursor with for in
...
Is
Cursor cur is select * from XXX;
Begin
For cur_result in cur Loop
Begin
V_sum: = cur_result. Column name 1 + cur_result. Column name 2
End;
End loop;
End;

7. cursor with Parameters
Cursor c_user (c_id number) is select name from user where typeid = c_id;
Open c_user (variable value );
Loop
Fetch c_user into v_name;
Exit fetch c_user % notfound;
Do something
End loop;
Close c_user;

8. Use PL/SQL developer debug
Create a test window after connecting to the database
Enter the SP call code in the window, F9 start debug, CTRL + n single-step debugging