UBD08

Tema nr.
8
Observație!
Scrieți rezolvarea direct în acest document!
Creating Functions
1. Function full_name:
A. 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 and space,
first name (for example: Smith, Joe). Save your code.
CREATE OR REPLACE FUNCTION full_name(p_lname IN employees.last_name%TYPE,

p_fname IN employees.first_name%TYPE)
RETURN VARCHAR2 IS
BEGIN
RETURN (p_lname||', '||p_fname);
END;
B. Test your function from an anonymous block which uses a local variable to store and display the
returned value.
DECLARE
v_full_name VARCHAR2(50);
BEGIN
v_full_name := full_name('Smith', 'Joe');
DBMS_OUTPUT.PUT_LINE('The full name is ' || v_full_name);
END;
C. Modify your anonymous block from step b 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('The full name is ' || full_name('Smith', 'Joe'));
END;
D. Now call the function from within a SQL SELECT statement. Execute a SQL statement (not a
PL/SQL block) which displays the first_name, last_name and full name (using the function) of all
employees in department 50.
SELECT first_name, last_name, full_name(first_name, last_name) AS "FULL NAME"

FROM EMPLOYEES
WHERE department_id = 50;
2. Function reverse_string:
A. 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
i int;
result VARCHAR2(50):='';
BEGIN
i := LENGTH(p_string);
LOOP
result := result || SUBSTR(p_string, i, 1);
i := i-1;
EXIT WHEN i = 0;
END LOOP;
RETURN result;
END;
B. Test your function using the following SQL statements:
SELECT last_name, reverse_string(last_name)

FROM employees;
Using Functions in SQL Statements

1. Function sal_increase
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.
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. Now, suppose you want to see the same information in your SELECT statement, but only for
those employees for whom the increased salary would be greater than 10000. Write and test two
SELECT statements to do this. In the first, do NOT use your function. In the second, use your
function. Use an increase of 5 percent.
SELECT last_name, salary

FROM employees
WHERE salary>10000;
SELECT last_name, salary, sal_increase(salary, 5)

FROM employees
WHERE salary>10000;
Review of the Data Dictionary
1. Write and execute a SELECT statement that lists all the stored objects you have created in
your account so far. The query should return the object name and type and its status. Order the output
by type of object.
SELECT object_name, object_type, status

FROM user_objects
ORDER BY object_type;
2. Change the query from question 3 to show all functions and procedures to which you have
access. Include the owner of the object as well.
SELECT owner, procedure_name FROM ALL_PROCEDURES;

Review of Object Privileges
1. If you wanted user SUSAN to be able to execute SELECT and all DML statements on your
wf_countries table, what SQL statement would you execute to give her the required privileges?
GRANT SELECT, UPDATE, DELETE, INSERT ON wf_countries TO SUSAN;
2. Another user TOM creates a table called tomtab, and does not grant you any privileges on it.
A. If you try to execute the following statement, will it work?
INSERT INTO tom.tomtab (….) VALUES (…..);
No, because if I don’t have any privileges granted, it means I can’t insert rows into the table.
B. Examine the following code. Now the INSERT statement has been included in a procedure which
you have created. Will it work now?
CREATE OR REPLACE PROCEDURE my_ins_proc

IS
BEGIN
INSERT into tom.tomtab (….)
VALUES (……);
END;
Even though I try to insert rows through a procedure, because I don’t have any privileges the insert
won’t work and I also need the EXECUTE privilege.
C. TOM now executes the following statement:
GRANT INSERT ON tomtab TO <your user name>;

Will your my_ins_proc procedure work now? Why or why not?
It will work, because Tom gave me INSERT privileges.
D. TOM now REVOKEs your INSERT privilege on tomtab. TOM then writes the following
procedure. Which privilege must TOM grant to you to allow you to execute the procedure? With this
privilege, will your INSERT work now when you invoke TOM’s procedure?
CREATE OR REPLACE PROCEDURE tom_ins_proc

IS
BEGIN
INSERT into tom.tomtab (….)
VALUES (……);
END;
Tom needs to give me the EXECUTE privilege so I can execute the procedure. In this case the
INSERT will work.

UBD08

Uploaded by

Copyright:

Available Formats

UBD08

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

UBD08

Uploaded by

Copyright:

Available Formats

Tema nr.

CREATE OR REPLACE FUNCTION full_name(p_lname IN employees.last_name%TYPE,

SELECT first_name, last_name, full_name(first_name, last_name) AS "FULL NAME"

CREATE OR REPLACE FUNCTION reverse_string(p_string VARCHAR2)

B. Test your function using the following SQL statements:

SELECT last_name, reverse_string(last_name)

Using Functions in SQL Statements

CREATE OR REPLACE FUNCTION sal_increase

SELECT last_name, salary, sal_increase(salary , 5)

SELECT last_name, salary

SELECT last_name, salary, sal_increase(salary, 5)

SELECT object_name, object_type, status

SELECT owner, procedure_name FROM ALL_PROCEDURES;

GRANT SELECT, UPDATE, DELETE, INSERT ON wf_countries TO SUSAN;

A. If you try to execute the following statement, will it work?

INSERT INTO tom.tomtab (….) VALUES (…..);

CREATE OR REPLACE PROCEDURE my_ins_proc

C. TOM now executes the following statement:

GRANT INSERT ON tomtab TO <your user name>;

CREATE OR REPLACE PROCEDURE tom_ins_proc

You might also like