Tema nr. 6
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_last_name employees.last_name%TYPE, p_first_name employees.first_name%TYPE)
RETURN VARCHAR2 IS
BEGIN
RETURN p_last_name || ', ' || p_first_name; END;
Function created
B. Test your function from an anonymous block which uses a local variable to store and display the returned value.
DECLARE nume VARCHAR2(30); BEGIN nume:=full_name('Popescu', 'Ion'); DBMS_OUTPUT.PUT_LINE(nume); END; Popescu, Ion
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(full_name('Popescu', 'Ion')); 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(last_name, first_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_sir VARCHAR2) RETURN VARCHAR2 IS i int; v_character CHAR; result VARCHAR2(50):= ‘’; BEGIN result:=NULL; i:=LENGTH(p_sir); LOOP result:=result ||SUBSTR(p_sir,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; LAST_NAME REVERSE_STRING(LAST_NAME) Abel lebA Davies seivaD De Haan naaH eD Ernst tsnrE Fay yaF Gietz zteiG Grant tnarG Hartstein nietstraH Higgins sniggiH Hunold dlonuH
More than 10 rows available. Increase rows selector to view more rows.
SELECT country_name, reverse_string(country_name) FROM wf_countries;
COUNTRY_NAME REVERSE_STRING(COUNTRY_NAME)
Canada adanaC
Republic of Costa Rica aciR atsoC fo cilbupeR Republic of Cape Verde edreV epaC fo cilbupeR
Greenland dnalneerG
Dominican Republic cilbupeR nacinimoD State of Eritrea aertirE fo etatS
Federal Democratic Republic of Ethiopia aipoihtE fo cilbupeR citarcomeD laredeF The Holy See (State of the Vatican City) )ytiC nacitaV eht fo etatS( eeS yloH ehT Kingdom of Spain niapS fo modgniK
Republic of San Marino oniraM naS fo cilbupeR More than 10 rows available. Increase rows selector to view more rows.
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 employees.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 employees;
LAST_NAME SALARY SAL_INCREASE(SALARY,5)
King 24000 25200 Kochhar 17000 17850 De Haan 17000 17850 Whalen 4400 4620 Higgins 12000 12600 Gietz 8300 8715 Zlotkey 10500 11025 Abel 11000 11550 Taylor 8600 9030 Grant 7000 7350
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, sal_increase(salary , 5) FROM employees;
SELECT last_name, salary, sal_increase(salary , 5) FROM employees
WHERE sal_increase(salary , 5)>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 object_name,owner 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 pintea;
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 (…..); NU VA MERGE
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;
NU VA MERGE
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?
VA MERGE DEOARECE DA DREPTUL DE INSERARE
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;
