Hi
I'm not sure if I'm posting this in the right place but here goes.
I have two procedures and one function that I'm trying to put into a
Package.
Having had no experience with Packages, I have written the following but it
is not working.
Could someone pls have a look and tell me what I'm doing wrong (assuming the
whole thing isn't wrong.
The first part of the code is the Package and the second part is the
original procedures and function - these were working prior to creating the
package.
Thanks in advance
Sandy
Package I'm trying to create
- Create a package to contain the procedures and functions
CREATE OR REPLACE PACKAGE
acme
AS
PROCEDURE delete_cust(custid in number);
PROCEDURE cust_list(suburb in varchar2);
FUNCTION average_order RETURN number;
END acme;
CREATE OR REPLACE PACKAGE BODY
acme
AS
-- Number 3
- Procedure to delete customers
PROCEDURE delete_cust(custid in number)
IS
BEGIN
- Select the record
SELECT name INTO custname
FROM customer
WHERE id = custid;
- Delete the record
DELETE FROM customer
WHERE id = custid;
dbms_output.put_line('Customer '||custname||' deleted.');
EXCEPTION
- If record is not found
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20010, 'Cannot find employee '||custid);
END delete_cust;
- Procedure to list customer names and phone numbers in a certain suburb
PROCEDURE cust_list(suburb in varchar2)
IS
BEGIN
OPEN custcurs;
FETCH custcurs INTO custrec;
IF custcurs%found THEN
while custcurs%found
loop
dbms_output.put_line(RPAD(CUSTREC.NAME, 30, '.')||' '||CUSTREC.PHONE);
fetch custcurs into custrec;
end loop;
CLOSE custcurs;
ELSE
dbms_output.put_line('-------------------------------------------');
dbms_output.put_line('There are no customers in '||UPPER(suburb));
dbms_output.put_line('-------------------------------------------');
END IF;
END cust_list;
- Function to return the average number of days it takes to fill an order
FUNCTION average_order RETURN NUMBER
IS
BEGIN
SELECT TRUNC(avg(datesupplied - dateordered))
INTO average_days
FROM orders
WHERE datesupplied is not null;
RETURN average_days;
END average_order;
END acme;
/
SHOW ERRORS PACKAGE acme;
- Original Procedures and Functions
CREATE OR REPLACE PROCEDURE
delete_cust(custid in number)
AS
custname customer.name%type;
BEGIN
- Select the record
SELECT name INTO custname
FROM customer
WHERE id = custid;
- Delete the record
DELETE FROM customer
WHERE id = custid;
dbms_output.put_line('Customer '||custname||' deleted.');
EXCEPTION
- If record is not found
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20010, 'Cannot find employee '||custid);
END;
/
SHOW ERRORS PROCEDURE delete_cust;
- Number 4
- Procedure to display customer name and phone number
CREATE OR REPLACE PROCEDURE
cust_list (suburb in varchar2)
AS
CURSOR custcurs IS
SELECT name,
phone
FROM customer
WHERE address like '%'||UPPER(suburb);
custrec custcurs%rowtype;
BEGIN
OPEN custcurs;
FETCH custcurs INTO custrec;
IF custcurs%found THEN
while custcurs%found
loop
dbms_output.put_line(RPAD(CUSTREC.NAME, 30, '.')||' '||CUSTREC.PHONE);
fetch custcurs into custrec;
end loop;
CLOSE custcurs;
ELSE
dbms_output.put_line('-------------------------------------------');
dbms_output.put_line('There are no customers in '||UPPER(suburb));
dbms_output.put_line('-------------------------------------------');
END IF;
END;
/
SHOW ERRORS PROCEDURE cust_list;
- Mumber 5
- Function to calculate the avg no of days to fill an order
CREATE or REPLACE FUNCTION
average_order
RETURN number IS Average_Days NUMBER(3,0);
BEGIN
SELECT TRUNC(avg(datesupplied - dateordered))
INTO average_days
FROM orders
WHERE datesupplied is not null;
RETURN average_days;
END;
/
SHOW ERRORS FUNCTION average_order;
Received on Tue Jun 04 2002 - 09:03:04 CDT
