Example of calling Oracle stored procedure with a cursor in plus

In the previous post, I answered some questions from some netizens about how to wear a stored procedure that returns a set of records. I think many netizens already understand this. I will not talk about it more here.

How to call a stored procedure with a cursor in sqlplus

How Does Oracle execute the process with a cursor?

Here is an example.

-- Use the cursor (the cursor is actually a temporary table in the memory)
Declare
Money cms3_simcard.card_1_% type: = 0; -- Define the same type as the table Field
Cursor mycursor is -- defines the cursor
Select * from cms3_simcard
Where return_flag = 1 and msisdn like '000000 ';
My_record mycursor % rowtype; -- defines the cursor record type
Counter int: = 0;

Begin
Open mycursor; -- open the cursor
If mycursor % isopen then -- determines whether the open is successful
Loop -- Obtain Record Sets cyclically
Fetch mycursor into my_record; -- get records in the cursor
If mycursor % found then -- the cursor's found attribute determines whether a record exists
Dbms_output.put_line (my_record.card_logs );
Else
Exit;
End if;
End loop;
Else
Dbms_output.put_line ('cursor not opened ');
End if;
Close mycursor;
End;

If you want to ask me how to call the program, you should not ask because there are too many people who know it. No more nonsense.

First, let's look at the table structure of t1.

1. SQL>DescT1
2. Is the name empty? Type
3.  ----------------------------------------------------------------------
5. DNOT NULL DATE
6. A number (38)
7. B NUMBER (38)
8. C number (38)

Check the data in Table T1.

1. SQL>Select*FromT1;
3. D A B C
4. --------------------------------------------
5. 12-3 months-11 102 21 15
6. 14-3 month-11 100 58 73
7. 15-3-11 105 87

As with the previous post, create a package first

1. SQL>Create Or ReplacePackage pkg_package
2. 2As
3. 3 type type_cursorIsRefCursor;
4. 4 type type_recordIsRecord
5. 5 (
6. 6 test01DATE,
7. 7 test02 NUMBER (38 ),
8. 8 test03 NUMBER (38 ),
9. 9 test04 NUMBER (38)
10. 10 );
11. 11End;
12. 12/
14. The package has been created.

Creating a stored procedure with a cursor is a stored procedure that returns a set of records.

1. SQL>Create Or Replace ProcedureP_temp_procedure
2. 2 (
3. 3 cur_out_argOutPkg_package.type_cursor
4. 4)
5. 5Is
6. 6Begin
7. 7OpenCur_out_argFor Select*FromT1;
8. 8End;
9. 9/
11. The process has been created.

All the data is available, and the focus is on it. A collection of records returned when a stored procedure is called

1. SQL>Declare
2. 2 cur_out_arg pkg_package.type_cursor;
3. 3 rec_arg pkg_package.type_record;
4. 4Begin
5. 5 dbms_output.put_line ('------------------------');
6. 6 p_temp_procedure (cur_out_arg );
7. 7 loop
8. 8FetchCur_out_argIntoRec_arg;
9. 9 exitWhenCur_out_arg % notfound;
10. 10 dbms_output.put_line (rec_arg.test01 |''| Rec_arg.test02 |''| Rec_a
11. Rg. test03 |''| Rec_arg.test04 );
12. 11EndLoop;
13. 12End;
14. 13/
15. ------------------------
16. 12-3 months-11 102 2115
17. 14-3 months-11 100 5873
18. 15-3-11 105 87
20. The PL/SQL process is successfully completed.
22. SQL>