CODEWITHSUNDEEP
sqldatabaseoracle-databasepackage
Richard C
Richard C

Reputation: 417

Calling a persistent package variable

Ive created a package and a package body that run but I'm unsure of how i can test the value held in my persistent package variable.

Here is the package code, its fairly simple just a delete procedure and create function which are in the package body.

CREATE OR REPLACE PACKAGE customers AS
FUNCTION create_customer (Country VARCHAR2, First_Name VARCHAR2, Last_name VARCHAR2,
                    Birth_date VARCHAR2, Customer_Type VARCHAR2, Address VARCHAR2)
RETURN VARCHAR2;
PROCEDURE remove_customer (customer_id VARCHAR2);
FUNCTION count_customer RETURN NUMBER;
END customers;

And now here is the package body

create or replace package body customers AS
total_customers NUMBER;
FUNCTION CREATE_CUSTOMER(        
       Country IN VARCHAR2 ,First_Name IN VARCHAR2 ,Last_Name IN VARCHAR2 ,Birth_Date IN VARCHAR2 ,Customer_Type IN VARCHAR2 ,Address IN VARCHAR2
) return VARCHAR2 IS
  new_customer_id VARCHAR2(8);
BEGIN
SELECT custid_seq.NEXTVAL
INTO new_customer_id
FROM DUAL;
INSERT INTO customer (Customer_id, Country, First_Name, Last_name, Birth_date, Customer_Type, Address)
VALUES (new_customer_id, Country, First_Name, Last_name, Birth_date, Customer_Type, Address);
total_customers := total_customers + 1;
RETURN (new_customer_id);
end;
PROCEDURE remove_customer (customer_id VARCHAR2) IS
BEGIN
DELETE FROM customer
WHERE customer.customer_id = remove_customer.customer_id;
total_customers := total_customers - 1;
END;
BEGIN
select count(*) into total_customers from customer;
END;

The total_customers is my persistent package variable and only local to this package I just want to know how i can test to see the value currently held in the variable.

Thanks for the help

Upvotes: 0

Views: 1154

Answers (2)

Alex Poole
Alex Poole

Reputation: 191315

In your package specification add:

function get_total_customers return number;

In your package body add:

function get_total_customers return number is
begin
    return total_customers;
end get_total_customers;

Then from SQL you can select customers.get_total_customers from dual.

The alternative is to have the package variable defined in the specification rather than the body, but then it could be changed externally as well as read.

Either way, the value will only be meaningful within a session; if you set it in one session then that won't be visible in another session. Both can calculate it, but then if you call create_customer() in one session, the other will still report the old total.

Upvotes: 1

Art
Art

Reputation: 5792

Put your variable to package spec, test it. Then put it back into the body or leave it in the spec. Keep it simple...

Upvotes: 0

Related Questions