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 0 3856 Sign in to post your reply or Sign up for a free account.
Similar topics |
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 Server*, do i keep my current Access 2000 database and
ASP pages?
|
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
|
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 "Specified partition does not
exist".
Does anybody know if it is a database problem or a problem of
|
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
to generate create script, I'm told that I need client tools.
|
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:
| |
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
|
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 not seem to be
necessary.
<quote>
In the default implementation of threaded applications against...
|
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..
|
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
combining the two
Querys but all that does is create a Query that returns nothing after a
long period...
|
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 problem.(Like should I install any new OS etc).
Any help would be appreciated.
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |