本文转自：

List of quick examples to create stored procedures (IN, OUT, IN OUT and Cursor parameter) in Oracle database. PL/SQL code is self-explanatory.

1. Hello World

A stored procedure to print out a “Hello World” via DBMS_OUTPUT.

CREATE OR REPLACE PROCEDURE procPrintHelloWorldISBEGIN   DBMS_OUTPUT.PUT_LINE('Hello World!'); END;/

Run it

EXEC procPrintHelloWorld;

Output

Hello World!

2. Hello World + IN Parameter

A stored procedure to accept a single parameter and print out the “Hello World IN parameter” + parameter value via DBMS_OUTPUT.

CREATE OR REPLACE PROCEDURE procOneINParameter(param1 IN VARCHAR2)ISBEGIN   DBMS_OUTPUT.PUT_LINE('Hello World IN parameter ' || param1); END;/

Run it

EXEC procOneINParameter('mkyong');

Output

Hello World IN parameter mkyong

3. Hello World + OUT Parameter

A stored procedure to output/assign the “Hello World OUT parameter” value to OUT parameter.

CREATE OR REPLACE PROCEDURE procOneOUTParameter(outParam1 OUT VARCHAR2)ISBEGIN   outParam1 := 'Hello World OUT parameter'; END;/

Run it

DECLARE  outParam1 VARCHAR2(100);BEGIN  procOneOUTParameter(outParam1);  DBMS_OUTPUT.PUT_LINE(outParam1);END;/

Output

Hello World OUT parameter

4. Hello World + INOUT Parameter

A stored procedure to accept a INOUT parameter (genericParam), construct the output message and assign back to the same parameter name(genericParam) again.

CREATE OR REPLACE PROCEDURE procOneINOUTParameter(genericParam IN OUT VARCHAR2)ISBEGIN   genericParam := 'Hello World INOUT parameter ' || genericParam; END;/

Run it

DECLARE  genericParam VARCHAR2(100) := 'mkyong';BEGIN  procOneINOUTParameter(genericParam);  DBMS_OUTPUT.PUT_LINE(genericParam);END;/

Output

Hello World INOUT parameter mkyong

5. Hello World + Cursor

A stored procedure, return a ref cursor and accept a IN parameter.

CREATE OR REPLACE PROCEDURE procCursorExample(cursorParam OUT SYS_REFCURSOR, userNameParam IN VARCHAR2)ISBEGIN   OPEN cursorParam FOR  SELECT * FROM DBUSER WHERE USERNAME = userNameParam; END;/

Run it

DECLARE   dbUserCursor SYS_REFCURSOR;  dbUserTable DBUSER%ROWTYPE;BEGIN   procCursorExample(dbUserCursor,'mkyong');   LOOP 	FETCH dbUserCursor INTO dbUserTable;     EXIT WHEN dbUserCursor%NOTFOUND;    dbms_output.put_line(dbUserTable.user_id);   END LOOP;   CLOSE dbUserCursor; END;/

Output

List OF the user_id which matched username='mkyong'

Reference