Detailed description of select and parameters in oracle stored procedures

 

Create or replace procedure pro_test

Is

Begin

Select * from t_test;

End pro_test;

Is this stored procedure correct?

Yesterday, this was a long delay (the select statement was used in a stored procedure, but neither the cursor nor the into statement was used ).

If a select statement is used in the Stored Procedure (oracle Database tutorial), either the "select into variable" statement or the cursor is used, oracle does not support separate select statements (if the statement is incorrect, please note ).

Select into is relatively simple, but if a result set is returned, it cannot meet the requirements.

Cursor-type cursor and sys_refcursor-type cursor

Cursor type cursor -- cannot be used for parameter transfer

Create or replace procedure pro_test () is

Cusor_1 cursor is select field name from table name where condition;

(Or

Select class_name into cursor_2 from class where ...;

Another usage of cursor, which must be written between begin and end)

Begin

Select class_name into cursor_2 from class where ...;

Available

For xxx in cursor

Loop

....

End loop; -- traverses cursor

End pro_test;

Sys_refcursor type cursor

Create or replace procedure pro_test (rscursor out sys_refcursor) is

Cursor sys_refcursor;

Name varhcar (20 );

Begin

Open cursor

Select name from student where...; -- use open to open and assign values

-- Traversal

Loop

Fetch cursor into name -- fetch

Exit when cursor % notfound; -- record information not found

Dbms_output.putline (xxxx );

End loop;

Rscursor: = cursor;

End pro_test;

How to call other stored procedures in Stored Procedures (these are all with parameters)

 

A stored procedure with parameters.
SQL> create or replace procedure helloworld1 (
2 p_user_name varchar2
3)
4 begin
5 dbms_output.put_line ('hello' | p_user_name | '! ');
6 end helloworld1;
7/

Procedure created.

SQL> create or replace procedure callhelloworld1 (
2 p_user varchar2
3)
4 begin
5 -- call the Stored Procedure
6 helloworld1 (p_user );
7 end callhelloworld1;
8/

Procedure created.

Run
SQL> set serveroutput on
SQL> exec callhelloworld1 ('Tom ');
Hello tom!

Pl/SQL procedure successfully completed.