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 procPrintHelloWorld
IS
BEGIN
  
  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)
IS
BEGIN
  
  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)
IS
BEGIN
  
  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)
IS
BEGIN
  
  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)
IS
BEGIN

  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

  1. http://www.oradev.com/ref_cursor.jsp
  2. http://psoug.org/reference/procedures.html
  3. http://www.devshed.com/c/a/Oracle/Working-with-REF-CURSOR-in-PL-SQL/
  4. http://www.codeproject.com/KB/database/Oracle_RefCursor_ADO_C__.aspx