Select statement in oracle Stored Procedure

Guidance:InOracle DatabaseIfSelect statementYou can either use the "select into variable" statement or use a cursor. oracle does not support separate select statements.

Let's take a look at this stored procedure:

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), 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;

The above is all the content about the select statement in the oracle database stored procedure that I want to introduce to you.