================================================== ===
Expand|Select|Wrap|Line Numbers
- 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;
================================================== ===
Expand|Select|Wrap|Line Numbers
- 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
=======================================
Expand|Select|Wrap|Line Numbers
- 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;
To execute the above procedure.
Expand|Select|Wrap|Line Numbers
- 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