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 because of this, delayed for a long time (a SELECT statement is used in a stored procedure, but there is neither a cursor nor an into).

Oracle does not support a separate SELECT statement (if stated incorrectly, if a SELECT statement is used in the stored procedure (Oracle Database Tutorial), or if you use a SELECT INTO variable statement or a cursor.

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

Cursors are divided into cursor-type and sys_refcursor-type cursors

Cursor cursors--cannot be used for parameter passing

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 use of cursor, which needs to be written between Begin and end.

Begin

Select Class_name into cursor_2 from class where ...;

can use

for XXX in cursor

Loop

....

End Loop; --Traversal of cursor

End Pro_test;

Sys_refcursor-Type Cursors

Create or Replace procedure Pro_test (Rscursor out Sys_refcursor) is

Cursor sys_refcursor;

Name Varhcar (20);

Begin

Open cursor for

Select name from student where ...; --Open for assignment using open

--Traversal

Loop

Fetch cursor into name--fetch into to open each data traversed

Exit when Cursor%notfound; --No record information found

Dbms_output.putline (xxxx);

End Loop;

Rscursor: = cursor;

End Pro_test;

How to invoke other stored procedures in a stored procedure (these are all with parameters)

A stored procedure with parameters.
sql> Create or replace procedure HelloWorld1 (
  2    p_user_name  varchar2
  ; 3 ) as
  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   as
  4  begin
  5   --invokes stored procedure
  6    Hellowo Rld1 (P_user);
  7  End callhelloworld1;
  8 /

Procedure created.

Execute
sql> set serveroutput on
sql> exec callhelloworld1 (' Tom ');
Hello tom!

Pl/sql procedure successfully completed.