PL/SQL 9-1: Creating Functions

 Database Programming with PL/SQL

9-1: Creating Functions

Practice Activities

Vocabulary

A named PL/SQL block that can accept optional IN parameters and must return a single output. - Stored function

Try It / Solve It

1. Name the characteristics of a stored function.

Accepts IN parameters

Returns exactly one value

Must have at least one return

2. Create a function called full_name. Pass two parameters to the function, an employee’s last name

and first name. The function should return the full name in the format, last name, comma, space,

first name (for example: Smith, Joe). Save your code.

CREATE OR REPLACE FUNCTION full_name

(p_last_name employees.last_name%TYPE,p_first_name employees.first_name%TYPE) RETURN VARCHAR2 IS

v_full_name VARCHAR2(30);

BEGIN

 v_full_name := p_last_name || ', ' || p_first_name;

 RETURN v_full_name;

END full_name;

A. Test your function from an anonymous block which uses a local variable to store and display

the returned value.

DECLARE

 v_name VARCHAR2(40);

BEGIN

 v_name := full_name('Petre', 'Florin');

 DBMS_OUTPUT.PUT_LINE(v_name);

END;

B. Modify your anonymous block from the previous step to remove the local variable declaration

and call the function directly from within the DBMS_OUTPUT.PUT_LINE call. Test the block

again.

BEGIN

 DBMS_OUTPUT.PUT_LINE(full_name('Petre', 'Florin'));

END;

C. Now call the function from within a SELECT statement, not a PL/SQL block. Your SELECT

statement should display the first_name, last_name, and full name (using the function) of all

employees in department 50. Your output should look like this:

SELECT first_name, last_name, full_name(last_name, first_name)

FROM employees

WHERE department_id = 50;

3. Create a function called divide that accepts two numbers as input and returns the result of dividing

the first number by the second number, rounded to two decimal places. Save your code.

CREATE OR REPLACE FUNCTION divide

(p_a NUMBER, p_b NUMBER) RETURN NUMBER IS

v_res NUMBER(30,2);

BEGIN

 v_res := ROUND(p_a/p_b,2);

 RETURN v_res;

END divide;

A. Test your function twice from an anonymous block using input values (50, 2)

and (25, 3).

BEGIN

 DBMS_OUTPUT.PUT_LINE(divide(50,2));

END;

BEGIN

 DBMS_OUTPUT.PUT_LINE(divide(25,3));

END;

B. Test your function a third time using input values (16, 0). What happens?

BEGIN

 DBMS_OUTPUT.PUT_LINE(divide(16, 0));

END;

ORA-01476: divisor is equal to zero

C. Modify the function code to trap the ZERO_DIVIDE exception. The exception handler should

return a value of zero from the function if ZERO_DIVIDE is raised.

CREATE OR REPLACE FUNCTION divide

(p_a NUMBER, p_b NUMBER) RETURN NUMBER IS

v_res NUMBER(30,2);

BEGIN

 v_res := ROUND(p_a/p_b,2);

 RETURN v_res;

EXCEPTION

 WHEN ZERO_DIVIDE THEN

 RETURN 0;

END divide;

D. Test your function again using input values (16,0) as before. Now what happens?

BEGIN

 DBMS_OUTPUT.PUT_LINE(divide(16, 0));

END;

the output is 0

4. List four major differences between a procedure and a function.

Procedure is executed as a PL/SQL statement, function is invoked as part of an expression.

Procedures do not contain RETURN clause in the header, functions MUST contain return clause.

Porcedures may return values in output parameters (not required), functions MUST return exactly one value.

Procedures may contain a return statement without a value, functions MUST contain at least one return statement.

5. Look at the following two subprograms. The first is a procedure; the second is a function. Answer

the following questions.

CREATE OR REPLACE PROCEDURE get_country_name_proc

 (p_country_id IN countries.country_id%TYPE,

 p_country_name OUT countries.country_name%TYPE)

IS

BEGIN

 SELECT country_name INTO p_country_name

 FROM countries

 WHERE country_id = p_country_id;

END;

CREATE OR REPLACE FUNCTION get_country_name_func

 (p_country_id IN countries.country_id%TYPE)

 RETURN VARCHAR2

IS

 v_country_name countries.country_name%TYPE;

BEGIN

 SELECT country_name INTO v_country_name

 FROM countries

 WHERE country_id = p_country_id;

 RETURN v_country_name;

END;

A. For a given country id, will both of these subprograms return the same results?

Yes, both subprograms will return the name of the country with that id.

B. What is the advantage of creating this subprogram as a function rather than as a procedure?

You can use it directly in an SQL statement, for example, if it is a function, not a procedure.

C. Which of the following procedure and function calls are valid and which are not? Explain why

the invalid ones will fail.

DECLARE

 v_country_id countries.country_id%TYPE := 2;

 v_country_name countries.country_name%TYPE;

BEGIN

 get_country_name_proc(v_country_id, v_country_name); -- Call 1

 v_country_name := get_country_name_func(v_country_id); -- Call 2

 v_country_name := get_country_name_proc(v_country_id); -- Call 3

END;

SELECT get_country_name_proc(country_id) -- Call 4

 FROM countries;

SELECT get_country_name_func(country_id) -- Call 5

 FROM countries;

Call 1 and Call 2 are valid

Call 3 is not valid because you need two parameters for the procedure. Also, procedure doesn't return anything, you must put v_country_name as the OUT parameter.

Call 4 is not valid because you can't use procedures in sql statements.

Call 5 is valid.

6. List the ways you can invoke (i.e., call) a function.

Functions can be invoked:

-As part of PL/SQL expressions

-As a parameter to another subprogram

-As an expression in a SQL statement

7. Create a function which accepts a character string as input and returns the same character string but with the order of the letters reversed. For example, "Smith" would be returned as "htimS." Save your code. Hint: you will need to declare a local variable to store the reversed string, and build its contents by reading the input one character at a time (using SUBSTR) in a loop structure, starting from the last character. Each execution of the loop reads the preceding character and concatenates it to the reversed string.

CREATE OR REPLACE FUNCTION reverse_string

(p_string VARCHAR2) RETURN VARCHAR2 IS

v_string VARCHAR2(30);

len NUMBER;

BEGIN

len := Length(p_string);

 FOR i IN REVERSE 1..len LOOP

 v_string := v_string || SUBSTR(p_string, i, 1);

 END LOOP;

RETURN v_string;

END reverse_string;

8. Test your function using the following SQL statements:

SELECT last_name, reverse_string(last_name)

 FROM employees;

SELECT country_name, reverse_string(country_name)

 FROM countries;