| packages [message #370033] |
Thu, 30 November 2000 17:49  |
christopher
Messages: 25
Registered: September 2000
|
Junior Member |
|
|
Hi I have a procedure and a function and I was wondering if someone could show mw how to set this up into a package. I know that there are two parts a package "specification and a body". I have been getting an error when I put in the specification part. Thanks!
CREATE FUNCTION average_salary
(v_empno IN NUMBER)
RETURN NUMBER IS
v_deptno NUMBER;
v_deptavg NUMBER;
BEGIN
SELECT DEPTNO
INTO v_deptno
FROM emp
WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE ('in FUNCTION Avg_sal, v_deptno='||v_deptno);
SELECT AVG(sal)
INTO v_deptavg
FROM emp
WHERE deptno = v_deptno;
RETURN v_deptavg;
END average_salary;
/
CREATE PROCEDURE pay_grade
(v_sal IN NUMBER,v_paygrade OUT NUMBER)
IS
--v_sal NUMBER;
--v_paygrade NUMBER;
BEGIN
--SELECT sal INTO v_sal
--FROM emp
--WHERE empno = v_empno;
IF v_sal >= 3000 THEN
v_paygrade := 6;
ELSIF v_sal >= 2500 THEN
v_paygrade := 5;
ELSIF v_sal >= 2000 THEN
v_paygrade := 4;
ELSIF v_sal >= 1500 THEN
v_paygrade := 3;
ELSIF v_sal >= 1000 THEN
v_paygrade := 2;
ELSIF v_sal < 1000 THEN
v_paygrade := 1;
ELSIF v_sal < 0 THEN
v_paygrade:= 0;
END IF;
END Pay_Grade;
/
|
|
|
|
| Re: packages [message #370034 is a reply to message #370033] |
Fri, 01 December 2000 03:32  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
The syntax you're looking for is:
Create or replace package package_name as
Function average_salary (v_empno IN number) Return number;
Procedure pay_grade (v_sal IN NUMBER,v_paygrade OUT NUMBER);
End package_name;
/
Create or replace package body package_name as
Function average_salary (v_empno IN number) Return number IS
{code for function}
End average_salary;
Procedure pay_grade (v_sal IN NUMBER,v_paygrade OUT NUMBER) IS
{code for procedure{
End pay_grade;
end package_name;
Hope this helps
|
|
|
|
| Re: packages [message #370039 is a reply to message #370033] |
Mon, 04 December 2000 09:35 |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
create or replace package package_name is
FUNCTION average_salary (v_empno IN NUMBER) return number;
PROCEDURE pay_grade (v_sal IN NUMBER,v_paygrade OUT NUMBER);
end;
/
Create or replace package body package_name is
FUNCTION average_salary
(v_empno IN NUMBER)
RETURN NUMBER IS
v_deptno NUMBER;
v_deptavg NUMBER;
BEGIN
SELECT DEPTNO
INTO v_deptno
FROM emp
WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE ('in FUNCTION Avg_sal, v_deptno='||v_deptno);
SELECT AVG(sal)
INTO v_deptavg
FROM emp
WHERE deptno = v_deptno;
RETURN v_deptavg;
END average_salary;
PROCEDURE pay_grade
(v_sal IN NUMBER,v_paygrade OUT NUMBER)
IS
--v_sal NUMBER;
--v_paygrade NUMBER;
BEGIN
--SELECT sal INTO v_sal
--FROM emp
--WHERE empno = v_empno;
IF v_sal >= 3000 THEN
v_paygrade := 6;
ELSIF v_sal >= 2500 THEN
v_paygrade := 5;
ELSIF v_sal >= 2000 THEN
v_paygrade := 4;
ELSIF v_sal >= 1500 THEN
v_paygrade := 3;
ELSIF v_sal >= 1000 THEN
v_paygrade := 2;
ELSIF v_sal < 1000 THEN
v_paygrade := 1;
ELSIF v_sal < 0 THEN
v_paygrade:= 0;
END IF;
END Pay_Grade;
END;
/
Suresh
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
