Question

The following question is based on this table named Customer

Column Name     Type             

CustId          Integer Primary Key,

CustName        Varchar2(40),

Balance         Number

Gender          Varchar2(1)

Write the Stored Function named GetCustDetails.      

The function:

• Has one parameter named pCustName.
• Must return a text value
• Must search for a customer row that has a CustName value equal to pCustName
  • If a match is found return the customer's name and balance in the following format:

Customer Name: Fred   Gender M   Balance 20000

• If a match is not found return the string "Customer not Found"
• If more that one match is found return the string "Count greater than 1"

• Must use Exceptions and Late Error Raising

You may only use a maximum of one SELECT statement in the function.

Answer 1

Creating a PL/SQL function

PL/SQL function is a reusable program unit stored as a schema object in the Oracle Database. The following illustrates the syntax for creating a function:

CREATE [OR REPLACE] FUNCTION function_name (parameter_list)

    RETURN return_type

IS

Calling a Function

While creating a function, you give a definition of what the function has to do. To use a function, you will have to call that function to perform the defined task. When a program calls a function, the program control is transferred to the called function.

A called function performs the defined task and when its return statement is executed or when the last end statement is reached, it returns the program control back to the main program.

Eg.)

To call a function, you simply need to pass the required parameters along with the function name and if the function returns a value, then you can store the returned value. Following program calls the function totalCustomers from an anonymous block −

DECLARE 

   c number(2); 

BEGIN 

   c := totalCustomers(); 

   dbms_output.put_line('Total no. of Customers: ' || c); 

END; 

/

PL/SQL Exceptions

PL/SQL treats all errors that occur in an anonymous block, procedure, or function as exceptions. The exceptions can have different causes such as coding mistakes, bugs, even hardware failures.

Here is the basic syntax of the exception-handling section:

BEGIN

    -- executable section

    ...

    -- exception-handling section

    EXCEPTION

        WHEN e1 THEN

            -- exception_handler1

        WHEN e2 THEN

            -- exception_handler1

        WHEN OTHERS THEN

            -- other_exception_handler

END;

In this syntax, e1, e2 are exceptions.

Customer Table

Column Name     Type             

CustId Integer Primary Key,

CustName Varchar2(40),

Balance Number

Gender Varchar2(1)

Write the Stored Function named GetCustDetails.      

The function:

• Has one parameter named pCustName.
• Must return a text value

Answer:

CREATE OR REPLACE FUNCTION GetCustDetails(pCustName IN VARCHAR2(40))

RETURN VARCHAR2(40)

IS

BEGIN

RETURN(‘Welcome’| pCustName);

END

/

O/P Function created.

Calling function:

DECLARE

msg VARCHAR2(40);

BEGIN

msg:= GetCustDetails(‘Raj’)

dbms_output.put_line(msg);

END

O/P Welcome Raj

• Must search for a customer row that has a CustName value equal to pCustName
  • If a match is found return the customer's name and balance in the following format:

Customer Name: Fred   Gender M   Balance 20000

Answer:

SELECT

customername,

balance

FROM

Customer

WHERE

CustName =pCustName

• If a match is not found return the string "Customer not Found"

DECLARE

   CustId Customers.id%type := 8;

   CustName CustomerS.Name%type;

   Balance Customers.Balance%type :=8
   Gender Customers.Gender%type;

BEGIN

   SELECT name, id ,gender,balance INTO CustName,CustId,Gender,Balance

   FROM customers

   WHERE CustName = pCustname;
    DBMS_OUTPUT.PUT_LINE ('Customer Name: ' || CustName||'\t'||'Gender'||Gender||'\t'||'Balance'||Balance);

EXCEPTION

   WHEN no_data_found THEN

      dbms_output.put_line('Customer not found!');

   WHEN others THEN

      dbms_output.put_line('Error!');

END;

/