471,305 Members | 1,301 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 471,305 developers and data experts.

PL/SQL-PACKAGE - 2

debasisdas
8,127 Expert 4TB
SAMPLE PACKAGE EX#3
====================
PACKAGE SPECIFICATION
--------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE MYPACK AS
  2. PROCEDURE SHOWENAME(EMPID IN NUMBER);
  3. FUNCTION SHOWSAL(EMPID IN NUMBER) RETURN NUMBER;
  4. END MYPACK;
  5.  
-------------------
package body
----------------------
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE BODY MYPACK AS
  2. PROCEDURE SHOWENAME(EMPID IN NUMBER) IS
  3. MYNAME EMP.ENAME%TYPE;
  4. BEGIN
  5. SELECT ENAME INTO MYNAME FROM EMP WHERE EMPNO=EMPID;
  6. DBMS_OUTPUT.PUT_LINE(MYNAME);
  7. END SHOWENAME;
  8. FUNCTION SHOWSAL(EMPID IN NUMBER) RETURN NUMBER IS
  9. MYSAL EMP.SAL%TYPE;
  10. BEGIN
  11. SELECT SAL INTO MYSAL FROM EMP WHERE EMPNO=EMPID;
  12. RETURN MYSAL;
  13. END SHOWSAL;
  14. END MYPACK;
SAMPLE PACKAGE EX#4
====================
PACKAGE SPECIFICATION
--------------------------------------------

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE  PACKAGE EMP_ACTIONS AS
  2. PROCEDURE HIRE_EMPLOYEE (EMPNO NUMBER,ENAME VARCHAR2,JOB  VARCHAR2,
  3. MGR NUMBER,HIREDATE DATE,SAL NUMBER,COMM         NUMBER,DEPTNO NUMBER);
  4. PROCEDURE FIRE_EMPLOYEE (EMP_ID NUMBER);
  5. PROCEDURE TRANSFER_EMPLOYEE(EMP_ID NUMBER,DEPT_NO NUMBER);
  6. PROCEDURE PROMOT_EMPLOYEE(EMP_ID NUMBER,EMP_JOB VARCHAR2);
  7. PROCEDURE INCREMENT(EMP_ID NUMBER,INCR NUMBER);
  8. FUNCTION ANSAL(NUM IN OUT NUMBER)RETURN NUMBER;
  9. END EMP_ACTIONS;
-------------------
package body
---------------------
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE BODY emp_actions AS
  2. PROCEDURE hire_employee (EMPNO NUMBER,ENAME VARCHAR2,JOB  VARCHAR2,
  3. MGR NUMBER,HIREDATE DATE,SAL NUMBER,COMM         NUMBER,DEPTNO NUMBER)
  4. IS
  5. BEGIN
  6. INSERT INTO EMP VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
  7. EXCEPTION
  8. WHEN DUP_VAL_ON_INDEX THEN
  9. RAISE_APPLICATION_ERROR(-20001,'DUPLICATE ENTRY...!');
  10. WHEN OTHERS THEN
  11. RAISE_APPLICATION_ERROR(-20002,'SOME OTHER ERROR...!');
  12. END hire_employee;
  13. PROCEDURE fire_employee (emp_id NUMBER) IS
  14. I INT;
  15. BEGIN
  16. SELECT COUNT(*)  INTO I FROM EMP    WHERE EMPNO=EMP_ID;
  17. IF I=1 THEN
  18. DELETE FROM emp WHERE empno = emp_id;
  19. ELSE
  20. DBMS_OUTPUT.PUT_LINE('EMPLOYEE DOES NOT EXIST...!');
  21. END IF;
  22. END fire_employee;
  23. PROCEDURE TRANSFER_EMPLOYEE(EMP_ID NUMBER,DEPT_NO NUMBER)
  24. IS
  25. NUM EMP.EMPNO%TYPE;
  26. BEGIN
  27. SELECT COUNT(EMP_ID) INTO NUM FROM EMP WHERE EMPNO=EMP_ID;
  28. IF NUM=1 THEN
  29. UPDATE EMP SET DEPTNO=DEPT_NO WHERE EMPNO=EMP_ID;
  30. END IF;
  31. END TRANSFER_EMPLOYEE;
  32. PROCEDURE PROMOT_EMPLOYEE(EMP_ID NUMBER,EMP_JOB VARCHAR2)
  33. IS
  34. I INT;
  35. BEGIN
  36. SELECT EMPNO INTO I FROM EMP WHERE EMPNO=EMP_ID;
  37. IF I=1 THEN
  38. UPDATE EMP SET JOB=EMP_JOB WHERE EMPNO=EMP_ID;
  39. END IF;
  40. END PROMOT_EMPLOYEE;
  41. PROCEDURE INCREMENT(EMP_ID NUMBER,INCR NUMBER)
  42. IS
  43. NUM EMP.EMPNO%TYPE;
  44. BEGIN
  45. SELECT COUNT(*) INTO NUM FROM EMP WHERE EMPNO=EMP_ID;
  46. IF NUM=1 THEN
  47. UPDATE EMP SET SAL=SAL+INCR WHERE EMPNO=EMP_ID;
  48. END IF;
  49. END INCREMENT;
  50. FUNCTION ANSAL(NUM IN OUT NUMBER)RETURN NUMBER
  51. IS
  52. II NUMBER(4);
  53. BEGIN
  54. SELECT COUNT(*) INTO II FROM EMP WHERE EMPNO=NUM;
  55. IF II=1 THEN
  56. SELECT (SAL+NVL(COMM,0))*12 INTO NUM FROM EMP WHERE EMPNO=NUM;
  57. END IF;
  58. RETURN NUM;
  59. END ANSAL;
  60. FUNCTION SHOWMANAGER(ID IN NUMBER)RETURN VARCHAR2
  61. IS
  62. MG EMP.MGR%TYPE;
  63. II NUMBER(4);
  64. BEGIN
  65. SELECT COUNT(*) INTO II FROM EMP WHERE EMPNO=ID;
  66. IF II=1 THEN
  67. SELECT MGR INTO MG FROM EMP WHERE EMPNO=ID;
  68. END IF;
  69. RETURN MG;
  70. END SHOWMANAGER;
  71. END EMP_ACTIONS;

SAMPLE PACKAGE EX#5
====================
PACKAGE SPECIFICATION
------------------------------------------

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE  PACKAGE PACK1 IS 
  2. PROCEDURE PROC1(DEPTN NUMBER);
  3. PROCEDURE PROC2(NO NUMBER);
  4. PROCEDURE PROC3(NUM NUMBER);
  5. END PACK1;
package body
----------------------
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE BODY PACK1 AS
  2.  
  3. PROCEDURE PROC1(DEPTN NUMBER) IS
  4. salary emp.sal%type;
  5. ex exception;
  6. begin
  7. if (deptn=10 or deptn=20 or deptn=30) then
  8. select max(sal) into salary from emp where deptno=deptn;
  9. dbms_output.put_line('The max salary for deptno '||deptn||' is '||salary);
  10. else 
  11. raise ex;
  12. end if;
  13. exception
  14. when ex then
  15. raise_application_error(-20001,'Please enter a valid department number');
  16. end proc1;
  17.  
  18. procedure proc2(no number) is
  19. a number;
  20. bb number;
  21. c number;
  22.  
  23. begin
  24. c:=1;
  25. bb:=no;
  26. loop
  27. c:=c*bb;
  28. bb:=bb-1;
  29. exit when bb<1;
  30. end loop;
  31. dbms_output.put_line('The factorial of  is '||c);
  32.  
  33. END PROC2;
  34.  
  35. PROCEDURE PROC3(NUM NUMBER) IS
  36. FIRS number;
  37. SECON NUMBER;
  38. SMM NUMBER(4);
  39. B NUMBER;
  40. BEGIN
  41.   B:=0;
  42.   FIRS:=0;
  43.   SECON:=1;
  44.    SMM:=0;
  45. DBMS_OUTPUT.PUT_LINE(FIRS);
  46. DBMS_OUTPUT.PUT_LINE(SECON);
  47. WHILE B<NUM-2 LOOP
  48. SMM:=FIRS+SECON;
  49. FIRS:=SECON;
  50. SECON:=SMM;
  51. B:=B+1;
  52. DBMS_OUTPUT.PUT_LINE(SMM);
  53. END LOOP;
  54. END proc3;
  55.  
  56. END PACK1;
To execute the members of the package from anomymous block.
-----------------------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. declare
  2. a number;
  3. b number;
  4. wrong exception;
  5. begin
  6. a:=&a;
  7. b:=&b;
  8. if a=1 then
  9. goto hello1;
  10. elsif a=2 then
  11. goto hello2;
  12. elsif a=3 then
  13. goto hello3;
  14. else
  15. raise wrong;
  16. end if;
  17. <<hello1>>
  18. pack1.proc1(b);
  19. return;
  20. <<hello2>>
  21. pack1.proc2(b);
  22. return;
  23. <<hello3>>
  24. pack1.proc3(b);
  25. return;
  26. exception
  27. when wrong then
  28. raise_application_error(-20001,'Please enter the numbers in range 1(for dept),2(for factorial), or 3(for febonacci)');
  29. end;
Also check PL/SQL-PACKAGE - 3
Jun 8 '07 #1
0 3575

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Peter | last post: by
10 posts views Thread by Dagwood | last post: by
6 posts views Thread by Fuzzydave | last post: by
14 posts views Thread by Developer | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.