SAMPLE EXAMPLE TO SHOW USE OF PROCEDURE WITH IN MODE
================================================== ===
-
CREATE OR REPLACE PROCEDURE REVNUM (NUM NUMBER)
-
IS
-
REV INTEGER;
-
NUM1 NUMBER;
-
BEGIN
-
NUM1:=NUM;
-
REV:=0;
-
WHILE NUM1>0
-
LOOP
-
REV:=REV * 10 + MOD(NUM1,10);
-
NUM1:=TRUNC(NUM1/10);
-
END LOOP;
-
DBMS_OUTPUT.PUT_LINE(REV);
-
END REVNUM;
-
SAMPLE PROGRAM TO SHOW USE OF EXECUTE IMMEDIATE WITH IN PROCEDURE & PROCEDURE WITH DEFAULT PARAMETER.
================================================== ===
-
CREATE OR REPLACE PROCEDURE delete_rows
-
(
-
table_name IN VARCHAR2,
-
condition IN VARCHAR2 DEFAULT NULL
-
)
-
AS
-
where_clause VARCHAR2(100) := ' WHERE ' || condition;
-
BEGIN
-
IF condition IS NULL THEN where_clause := NULL;
-
END IF;
-
--EXECUTE IMMEDIATE is used to dynamically execute any SQL statment at run time.
-
EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause;
-
END;
-
SAPMLE PROCEDURE WITH OUT PARAMETERS
=======================================
-
create or replace procedure display
-
(
-
eno in emp.empno%type,
-
name out emp.ename%type,
-
job out emp.job%type,
-
salary out emp.sal%type,
-
location out dept.loc%type
-
)
-
is
-
begin
-
--the values are selected into the out paramater.
-
select ename,job,sal,loc into name,job,salary,location from emp e,dept d
-
where e.deptno=d.deptno AND empno=eno;
-
end;
-
NOTE:----If a procedure contains any OUT or IN OUT parameter, it can't be executed from SQL prompt it must be called from with in an anonymous block.
To execute the above procedure.
-
declare
-
name emp.ename%type;
-
job emp.job%type;
-
salary emp.sal%type;
-
location dept.loc%type;
-
begin
-
--only the frist parameter accepts the value and the rest 4 returns after processing.
-
display(7839,name,job,salary,location);
-
dbms_output.put_line(name||' '||job||' '||salary||' '||location);
-
end;
-
Also check
PL/SQL-PROCEDURES - 3 
