KEMBAR78
LA 5.2 - Functions | PDF | Subroutine | Pl/Sql
Scribd
Upload
110 views3 pages

LA 5.2 - Functions

This document contains instructions for a database administration lab activity involving the creation and use of functions. It asks students to create a function to concatenate an employee's first and last name, test it using anonymous blocks and a SELECT statement. It also asks students to identify which of several procedure and function calls would be valid and explains why some would fail. Finally, it provides instructions to create sample employee and department tables and create a function to calculate salary increases, testing it in a SELECT statement.

Uploaded by

qwert
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
110 views3 pages

LA 5.2 - Functions

This document contains instructions for a database administration lab activity involving the creation and use of functions. It asks students to create a function to concatenate an employee's first and last name, test it using anonymous blocks and a SELECT statement. It also asks students to identify which of several procedure and function calls would be valid and explains why some would fail. Finally, it provides instructions to create sample employee and department tables and create a function to calculate salary increases, testing it in a SELECT statement.

Uploaded by

qwert
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

COLLEGE OF COMPUTER STUDIES & MULTIMEDIA ARTS

CCS0029
(Database System with Administration)

LAB ACTIVITY

5.2
FUNCTIONS

Name:
Section:

Professor:

Database System with Administration Page 1 of 3


COLLEGE OF COMPUTER STUDIES & MULTIMEDIA ARTS
PART-B. FUNCTIONS.

5. 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).

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

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.

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.

6. 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;

Database System with Administration Page 2 of 3


COLLEGE OF COMPUTER STUDIES & MULTIMEDIA ARTS
7. The questions in this Practice use partial copies of the employees and departments tables.
Create these copies by executing the following SQL statements:

CREATE TABLE f_emps AS SELECT employee_id, last_name, salary, department_id


FROM employees;

CREATE TABLE f_depts AS SELECT department_id, department_name FROM departments;

A. Create and execute a function sal_increase using the following two code samples. The first creates a function
which returns an employee’s new salary if a percentage increase is granted. The second calls this function in a
SELECT statement, using an increase of 5 percent. Your code must display only those employees for whom the
increased salary would be greater than 10000.

CREATE OR REPLACE FUNCTION sal_increase


(p_salary f_emps.salary%TYPE, p_percent_incr NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (p_salary + (p_salary * p_percent_incr / 100));
END;

--------------------------------------------------------------------------------------------

SELECT last_name, salary, sal_increase(salary, 5) FROM f_emps;

B. Modify your anonymous block to ORDER the results by the increased salary from highest to lowest.

Database System with Administration Page 3 of 3

You might also like