473,569 Members | 2,747 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

PL/SQL-PACKAGE - 2

debasisdas
8,127 Recognized Expert Expert
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 3832

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

Similar topics

3
3341
by: cooldv | last post by:
i am running a website on Windows 2000 server with ASP 3 webpages and Access 2000 database. (with a hosting company) traffic is slow at this time but expect to grow. lately i have been reading about sql database and sql server, specially this article: http://www.aspfaq.com/show.asp?id=2195 will someone help me understand: 1. with *SQL...
2
12646
by: Peter | last post by:
I run most of my SQL scripts via kornshell on AIX. I use the "here-document" to run some of the smaller ones. Example: #!/bin/ksh # Analyze the table. sqlplus ${SCHEMA_NM}/${SCHEMA_PASSWD}@${DB_NM} <<-ANALYZE_TABLE SET TERMOUT ON
0
10179
by: Jan | last post by:
I store sql-commands in a database table. In the first step I get the sql command out of the database table with embedded sql. In the second step I try to execute the command, which i got from the database table, using dynamic sql. Executing 'EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;' the error code -2149 is returned That means...
10
6722
by: Dagwood | last post by:
Good morning: At least it's morning where I am. :) I have a rather newbie question I'm afraid. I have VisualStudio.NET, and have installed it along with SQL server. However I can't seem to find an administration front-end to SQL, other than the VisualStudio, which is ok for creating databases and adding tables/columns. However when I go...
6
6758
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
0
4110
by: AlessanBar | last post by:
Hello Friends !! I have a strange problem, and I need to know what would be the source of this. I have a laptop computer with the following configuration: Pentium III Brand : Toshiba Speed : 500 Mhz RAM : 192 MB Hard Disk : 6 GB
11
4234
by: Mark Yudkin | last post by:
The documentation is unclear (at least to me) on the permissibility of accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003, with separate transactions scope, from separate threads of a multithreaded program using embedded SQL. Since the threads do not need to share transaction scopes, the sqleAttachToCtx family of APIs do...
1
8037
by: annie | last post by:
Hi all, I have recently ported my Access 2000 app to SQL Server, keeping the Access client as the front end using linked tables. I am also using triggers on my SQL tables to trap orphan records and validate added data. My question is..
6
2482
by: Fuzzydave | last post by:
I am back developing futher our Python/CGI based web application run by a Postgres DB and as per usual I am having some issues. It Involves a lot of Legacy code. All the actual SQL Querys are stored in the .py files and run in the .cgi files. I have the problem that I need to construct a row from two seprate SQL Querys, I have tried...
14
3007
by: Developer | last post by:
Hello All, i have recently installed VS2005 and was trying to install SQL sever 2000. I have Win XP' SP2. But when I tried installing, it only installed client tools and not the database. Can anyone please help me with this as I want to install SQL server and also wouold be grateful, if you can suggest me any workaround to dealwith this...
0
7694
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7609
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7921
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7964
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5504
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5217
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2107
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1208
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.