Oracle Stored Procedures Hello World Examples

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
Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

  • mounika

    Thanks for valuable post.

    Could you please tell me which command should we use to execute example no-3(hello world +Out parameter)

  • http://www.mkyong.com/oracle/oracle-stored-procedures-hello-world-examples/ Lakshmu Naidu

    Thank you. It is working

  • Nikash

    Thanks..

  • Diego Bernal

    bloque anĂ³nimo terminado……………………….

    CREATE OR REPLACE PROCEDURE SP_Fill_Municipios(p_Id_Departamento in MUNICIPIOS.ID_DEPARTAMENTO%TYPE,
    C_DBUSER OUT SYS_REFCURSOR)
    IS
    BEGIN
    OPEN C_DBUSER FOR
    SELECT ID_MUNICIPIO,ID_DEPARTAMENTO,NOMBRE_MUNICIPIO
    FROM MUNICIPIOS WHERE ID_DEPARTAMENTO = p_Id_Departamento;

    END SP_Fill_Municipios;

    DECLARE
    C_DBUSER SYS_REFCURSOR;
    temp_dbuser MUNICIPIOS%ROWTYPE;
    BEGIN

    SP_Fill_Municipios(1,C_DBUSER);

    LOOP

    –fetch cursor ‘c_dbuser’ into dbuser table type ‘temp_dbuser’
    FETCH C_DBUSER INTO temp_dbuser;

    –exit if no more records
    EXIT WHEN C_DBUSER%NOTFOUND;

    –print the matched username
    dbms_output.put_line(temp_dbuser.ID_DEPARTAMENTO);

    END LOOP;

    CLOSE C_DBUSER;

    END;

  • http://a jigs

    Easy learning

  • samantha

    no work’s :(

    PL/SQL procedure successfully completed.

  • http://gmail.com vijay

    A good post on procedures