PL/SQL-PROCEDURES - 2

[debasisdas]

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