CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Richard C
Richard C

Reputation: 417

PL/SQL Package and Package Body

I've created a function inside my package body that counts the number of current customers in the customer database and I've created a function that creates a customer. But I am unsure to what i need to put inside the package itself. Here is the code that is inside the package body.

 FUNCTION count_customer
 RETURN NUMBER is
 total NUMBER;
 BEGIN
 SELECT COUNT(*) into total FROM customer;
 RETURN (total);
 END;


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;

And then later in the package body I have this to calculate the new count.

BEGIN
total_customers := count_customer();
END;

The problem I am having is what to put in the actual package. I've got the create customer working inside the package it is just the customer count im having problems with.

Any ideas anyone?

Upvotes: 0

Views: 219

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

You need to declare any procedures or functions that you want to be accessible form outside the package. They are private by default, meaning that they can only be called within the same package.

To expose both of those functions:

CREATE PACKAGE my_package AS
    FUNCTION count_customer
     RETURN 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;
END my_package;

CREATE PACKAGE BODY ...

There is much more in the documentation.

From what you have, though, I suspect you don't really want count_customers() to be exposed; you want a separate public function that returns the current value of total_customers. Although that assumes that all inserts and deletions always go through the package, otherwise the count will get out of step.

Upvotes: 2

igr
igr

Reputation: 3499

Put declarative part of any function or procedure that you want to accessed from "outside" like:

FUNCTION count_customer RETURN NUMBER;

Upvotes: 1

Related Questions