8,127
====================
PACKAGE SPECIFICATION
--------------------------------------------
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE PACKAGE MYPACK AS
- PROCEDURE SHOWENAME(EMPID IN NUMBER);
- FUNCTION SHOWSAL(EMPID IN NUMBER) RETURN NUMBER;
- END MYPACK;
package body
----------------------
Expand|Select|Wrap|Line Numbers
- 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;
====================
PACKAGE SPECIFICATION
--------------------------------------------
Expand|Select|Wrap|Line Numbers
- 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
---------------------
Expand|Select|Wrap|Line Numbers
- 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
------------------------------------------
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE PACKAGE PACK1 IS
- PROCEDURE PROC1(DEPTN NUMBER);
- PROCEDURE PROC2(NO NUMBER);
- PROCEDURE PROC3(NUM NUMBER);
- END PACK1;
----------------------
Expand|Select|Wrap|Line Numbers
- 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;
-----------------------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- 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;
