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'
Thanks..
Thanks
in sql cmd prompt after creating the basic procedure, when we run it with “exec procedure_name” getting output as “PL/SQL procedure successfully completed” not displaying the message given inside.
Add “Set ServerOutput on” before executing the proc
we should use below syntax:-
set serveroutput on;
Thanks for valuable post.
Could you please tell me which command should we use to execute example no-3(hello world +Out parameter)
It is described above already, but if you are not able to understand well check this post Run Oracle Procedure with IN and OUT parameter.
Thank you. It is working
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;
You should execute ‘set serveroutput on’ command so that DBMS_OUTPUT.PUT_LINE can actually print the output.
Easy learning
no work’s 🙁
PL/SQL procedure successfully completed.
A good post on procedures
Thank you
Thank you.