Before executing a procedure in "Run SQL Command Line" we must execute the below query to display the server result in screen.SQL> set serveroutput on;
Example 1:
Stored Procedure
create procedure addn(a in number,b in number,c in out number)
is
begin
c:=a+b;
end;
/
Calls from PL/SQL
declare
a integer:=10;
b integer:=10;
c integer;
begin
addn(a,b,c);
dbms_output.put_line(c);
end;
/
Example 2:
Stored Procedure
create or replace procedure findmax(a in number,b in number,c in out number)
is
begin
if a>b then
c:=a;
else
c:=b;
end if;
end;
/
Calls from PL/SQL
declare
a integer:=10;
b integer:=21;
c integer;
begin
findmax(a,b,c);
dbms_output.put_line(c);
end;
/
Example 3:
Table SQL
CREATE TABLE DBUSER (
USER_ID NUMBER (5) NOT NULL,
USERNAME VARCHAR2 (20) NOT NULL,
CREATED_BY VARCHAR2 (20) NOT NULL,
CREATED_DATE DATE NOT NULL,
PRIMARY KEY ( USER_ID )
)
Insert Query
INSERT INTO DBUSER VALUES(11145,'Manoj','Admin',sysdate);
Stored Procedure
CREATE OR REPLACE PROCEDURE getDBUSERByUserId(
p_userid IN DBUSER.USER_ID%TYPE,
o_username OUT DBUSER.USERNAME%TYPE,
o_createdby OUT DBUSER.CREATED_BY%TYPE,
o_date OUT DBUSER.CREATED_DATE%TYPE)
IS
BEGIN
SELECT USERNAME , CREATED_BY, CREATED_DATE
INTO o_username, o_createdby, o_date
FROM DBUSER WHERE USER_ID = p_userid;
END;
/
Calls from PL/SQL
DECLARE
o_username DBUSER.USERNAME%TYPE;
o_createdby DBUSER.CREATED_BY%TYPE;
o_date DBUSER.CREATED_DATE%TYPE;
BEGIN
getDBUSERByUserId(11144,o_username,o_createdby,o_date);
DBMS_OUTPUT.PUT_LINE('username : ' || o_username);
DBMS_OUTPUT.PUT_LINE('createdby : ' || o_createdby);
DBMS_OUTPUT.PUT_LINE('createddate : ' || o_date);
END;
/