 | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,569
# 1
Jun 8 '07
| |
SAMPLE PACKAGE EX#3
====================
PACKAGE SPECIFICATION
-------------------------------------------- -
CREATE OR REPLACE PACKAGE MYPACK AS
-
PROCEDURE SHOWENAME(EMPID IN NUMBER);
-
FUNCTION SHOWSAL(EMPID IN NUMBER) RETURN NUMBER;
-
END MYPACK;
-
-------------------
package body
---------------------- - CREATE OR REPLACE PACKAGE BODY MYPACK AS
-
PROCEDURE SHOWENAME(EMPID IN NUMBER) IS
-
MYNAME EMP.ENAME%TYPE;
-
BEGIN
-
SELECT ENAME INTO MYNAME FROM EMP WHERE EMPNO=EMPID;
-
DBMS_OUTPUT.PUT_LINE(MYNAME);
-
END SHOWENAME;
-
FUNCTION SHOWSAL(EMPID IN NUMBER) RETURN NUMBER IS
-
MYSAL EMP.SAL%TYPE;
-
BEGIN
-
SELECT SAL INTO MYSAL FROM EMP WHERE EMPNO=EMPID;
-
RETURN MYSAL;
-
END SHOWSAL;
-
END MYPACK;
SAMPLE PACKAGE EX#4
====================
PACKAGE SPECIFICATION
-------------------------------------------- - CREATE OR REPLACE PACKAGE EMP_ACTIONS AS
-
PROCEDURE HIRE_EMPLOYEE (EMPNO NUMBER,ENAME VARCHAR2,JOB VARCHAR2,
-
MGR NUMBER,HIREDATE DATE,SAL NUMBER,COMM NUMBER,DEPTNO NUMBER);
-
PROCEDURE FIRE_EMPLOYEE (EMP_ID NUMBER);
-
PROCEDURE TRANSFER_EMPLOYEE(EMP_ID NUMBER,DEPT_NO NUMBER);
-
PROCEDURE PROMOT_EMPLOYEE(EMP_ID NUMBER,EMP_JOB VARCHAR2);
-
PROCEDURE INCREMENT(EMP_ID NUMBER,INCR NUMBER);
-
FUNCTION ANSAL(NUM IN OUT NUMBER)RETURN NUMBER;
-
END EMP_ACTIONS;
-------------------
package body
--------------------- -
CREATE OR REPLACE PACKAGE BODY emp_actions AS
-
PROCEDURE hire_employee (EMPNO NUMBER,ENAME VARCHAR2,JOB VARCHAR2,
-
MGR NUMBER,HIREDATE DATE,SAL NUMBER,COMM NUMBER,DEPTNO NUMBER)
-
IS
-
BEGIN
-
INSERT INTO EMP VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
-
EXCEPTION
-
WHEN DUP_VAL_ON_INDEX THEN
-
RAISE_APPLICATION_ERROR(-20001,'DUPLICATE ENTRY...!');
-
WHEN OTHERS THEN
-
RAISE_APPLICATION_ERROR(-20002,'SOME OTHER ERROR...!');
-
END hire_employee;
-
PROCEDURE fire_employee (emp_id NUMBER) IS
-
I INT;
-
BEGIN
-
SELECT COUNT(*) INTO I FROM EMP WHERE EMPNO=EMP_ID;
-
IF I=1 THEN
-
DELETE FROM emp WHERE empno = emp_id;
-
ELSE
-
DBMS_OUTPUT.PUT_LINE('EMPLOYEE DOES NOT EXIST...!');
-
END IF;
-
END fire_employee;
-
PROCEDURE TRANSFER_EMPLOYEE(EMP_ID NUMBER,DEPT_NO NUMBER)
-
IS
-
NUM EMP.EMPNO%TYPE;
-
BEGIN
-
SELECT COUNT(EMP_ID) INTO NUM FROM EMP WHERE EMPNO=EMP_ID;
-
IF NUM=1 THEN
-
UPDATE EMP SET DEPTNO=DEPT_NO WHERE EMPNO=EMP_ID;
-
END IF;
-
END TRANSFER_EMPLOYEE;
-
PROCEDURE PROMOT_EMPLOYEE(EMP_ID NUMBER,EMP_JOB VARCHAR2)
-
IS
-
I INT;
-
BEGIN
-
SELECT EMPNO INTO I FROM EMP WHERE EMPNO=EMP_ID;
-
IF I=1 THEN
-
UPDATE EMP SET JOB=EMP_JOB WHERE EMPNO=EMP_ID;
-
END IF;
-
END PROMOT_EMPLOYEE;
-
PROCEDURE INCREMENT(EMP_ID NUMBER,INCR NUMBER)
-
IS
-
NUM EMP.EMPNO%TYPE;
-
BEGIN
-
SELECT COUNT(*) INTO NUM FROM EMP WHERE EMPNO=EMP_ID;
-
IF NUM=1 THEN
-
UPDATE EMP SET SAL=SAL+INCR WHERE EMPNO=EMP_ID;
-
END IF;
-
END INCREMENT;
-
FUNCTION ANSAL(NUM IN OUT NUMBER)RETURN NUMBER
-
IS
-
II NUMBER(4);
-
BEGIN
-
SELECT COUNT(*) INTO II FROM EMP WHERE EMPNO=NUM;
-
IF II=1 THEN
-
SELECT (SAL+NVL(COMM,0))*12 INTO NUM FROM EMP WHERE EMPNO=NUM;
-
END IF;
-
RETURN NUM;
-
END ANSAL;
-
FUNCTION SHOWMANAGER(ID IN NUMBER)RETURN VARCHAR2
-
IS
-
MG EMP.MGR%TYPE;
-
II NUMBER(4);
-
BEGIN
-
SELECT COUNT(*) INTO II FROM EMP WHERE EMPNO=ID;
-
IF II=1 THEN
-
SELECT MGR INTO MG FROM EMP WHERE EMPNO=ID;
-
END IF;
-
RETURN MG;
-
END SHOWMANAGER;
-
END EMP_ACTIONS;
SAMPLE PACKAGE EX#5
====================
PACKAGE SPECIFICATION
------------------------------------------ - CREATE OR REPLACE PACKAGE PACK1 IS
-
PROCEDURE PROC1(DEPTN NUMBER);
-
PROCEDURE PROC2(NO NUMBER);
-
PROCEDURE PROC3(NUM NUMBER);
-
END PACK1;
package body
---------------------- - CREATE OR REPLACE PACKAGE BODY PACK1 AS
-
-
PROCEDURE PROC1(DEPTN NUMBER) IS
-
salary emp.sal%type;
-
ex exception;
-
begin
-
if (deptn=10 or deptn=20 or deptn=30) then
-
select max(sal) into salary from emp where deptno=deptn;
-
dbms_output.put_line('The max salary for deptno '||deptn||' is '||salary);
-
else
-
raise ex;
-
end if;
-
exception
-
when ex then
-
raise_application_error(-20001,'Please enter a valid department number');
-
end proc1;
-
-
procedure proc2(no number) is
-
a number;
-
bb number;
-
c number;
-
-
begin
-
c:=1;
-
bb:=no;
-
loop
-
c:=c*bb;
-
bb:=bb-1;
-
exit when bb<1;
-
end loop;
-
dbms_output.put_line('The factorial of is '||c);
-
-
END PROC2;
-
-
PROCEDURE PROC3(NUM NUMBER) IS
-
FIRS number;
-
SECON NUMBER;
-
SMM NUMBER(4);
-
B NUMBER;
-
BEGIN
-
B:=0;
-
FIRS:=0;
-
SECON:=1;
-
SMM:=0;
-
DBMS_OUTPUT.PUT_LINE(FIRS);
-
DBMS_OUTPUT.PUT_LINE(SECON);
-
WHILE B<NUM-2 LOOP
-
SMM:=FIRS+SECON;
-
FIRS:=SECON;
-
SECON:=SMM;
-
B:=B+1;
-
DBMS_OUTPUT.PUT_LINE(SMM);
-
END LOOP;
-
END proc3;
-
-
END PACK1;
To execute the members of the package from anomymous block.
----------------------------------------------------------------------------------------------- - declare
-
a number;
-
b number;
-
wrong exception;
-
begin
-
a:=&a;
-
b:=&b;
-
if a=1 then
-
goto hello1;
-
elsif a=2 then
-
goto hello2;
-
elsif a=3 then
-
goto hello3;
-
else
-
raise wrong;
-
end if;
-
<<hello1>>
-
pack1.proc1(b);
-
return;
-
<<hello2>>
-
pack1.proc2(b);
-
return;
-
<<hello3>>
-
pack1.proc3(b);
-
return;
-
exception
-
when wrong then
-
raise_application_error(-20001,'Please enter the numbers in range 1(for dept),2(for factorial), or 3(for febonacci)');
-
end;
Also check PL/SQL-PACKAGE - 3 |
