Section 8: Using and Managing Functions
8.01 Creating Functions
1. The following function has been created:
(p_emp_id IN employees.employee_id%TYPE)
RETURN NUMBER IS …
v_mynum NUMBER(6,2);
v_mydate DATE;
BEGIN
… Line A
END;
Which of the following would you include at Line A?
♦find_sal(100,v_mynum);
♦v_mynum := find_sal(100); (*)
♦v_mydate := find_sal(100);
♦find_sal(v_mynum,100);
2. You have created a function called GET_COUNTRY_NAME which accepts a country_id as an IN parameter and returns the name of the country. Which one of the following calls to the function will NOT work?
♦v_name := get_country_name(100);
♦DBMS_OUTPUT.PUT_LINE(get_country_name(100));
♦SELECT get_country_name(100) FROM dual;
♦BEGIN get_country_name(100, v_name); END; (*)
3. Which of the following is found in a function and not a procedure?
♦An exception section.
♦IN parameters.
♦Local variables in the IS/AS section.
♦Return statement in the header. (*)
♦False (*)
5. Procedure p1 has a single OUT parameter of type DATE. Function f1 returns a DATE. What is the difference between p1 and f1?
♦p1 can be invoked from an anonymous block but f1 cannot
♦f1 can be used within a SQL statement but p1 cannot (*)
♦p1 can have as many IN parameters as needed but f1 cannot have more than two IN parameters
♦There is no difference because they both return a single value of the same datatype
6. A stored function:
♦must have at least one IN parameter.
♦cannot be called in a SQL statement.
♦must return one and only one value. (*)
♦is called as a standalone executable statement.
7. What is wrong with the following code?
(sal employees.salary%TYPE,
comm_pct IN employees.commission%TYPE)
RETURN NUMBER(5,2)
IS
RETURN (sal*12) + NVL(comm_pct,0)*12*sal;
END annual_comp;
♦The RETURN NUMBER has a scale and precision. (*)
♦There should be parentheses (brackets) around NVL(comm_pct,0)*12*sal
♦The END; statement should not include the function name.
8. Based on the following function definition:
(sal employees.salary%type,
comm_pct In employees.commission%type)
…
Which one of the following is an incorrect call for annual_comp?
♦Execute dbms_output.put_line(annual_comp (1000,.2))
♦Select employee_id, annual_comp(salary, commission_pct) from employees;
♦Declare Ann_comp number (6,2); Begin … Ann_comp := annual_comp(1000,.2); … End;
♦Select employee_id, annual_comp(salary) from employees; (*)
9. CREATE FUNCTION get_sal
(p_id employees.employee_id%TYPE))
RETURN number
IS
v_sal employees.salary%TYPE := 0;
BEGIN
SELECT salary INTO v_sal
FROM employees
WHERE employee_id = p_id;
RETURN v_sal;
END get_sal;
Which variable is passed to the function and which variable is returned from the function?
♦GET_SAL is passed and V_SAL is returned.
♦SALARY is passed and P_ID is returned.
♦EMPLOYEE_ID is passed and SALARY is returned.
♦P_ID is passed and V_SAL is returned. (*)
10. Function MYFUNC1 has been created, but has failed to compile because it contains syntax errors. We now try to create procedure MYPROC1 which invokes this function. Which of the following statements is true?
♦MYPROC1 will compile correctly, but will fail when it is executed.
♦MYPROC1 will compile and execute succesfully.
♦MYPROC1 will fail to compile because the function is invalid. (*)
♦MYPROC1 will compile and execute successfully, except that the call to MYFUNC1 will be treated as a comment and ignored.
11. Function GET_JOB accepts an employee id as input and returns that employee’s job id. Which of the following calls to the function will NOT work?
♦DBMS_OUTPUT.PUT_LINE(get_job(100));
♦IF get_job(100) = ‘IT_PROG’ THEN …
♦get_job(100,v_job_id); (*)
♦v_job_id := get_job(100);
12. To create a function successfully, the following steps should be performed:
B. Write the code containing the CREATE or REPLACE FUNCTION followed by the function code
C. Test the function from a SQL statement or an anonymous block
D. If the function fails to compile, correct the errors
E. Load the code into Application Express
F. Execute the code in Application Express
G. What is the correct order to perform these steps?
♦D,B,E,F,A,C
♦B,C,E,F,D,A
♦A,B,E,F,D,C
8.02. Using Functions in SQL Statements
(p_dept_id IN departments.department_id%TYPE)
RETURN NUMBER IS
BEGIN
UPDATE departments SET department_name = ‘Accounting’
WHERE department_id = p_dept_id;
RETURN p_dept_id;
END;
Which of the following will execute successfully?
♦DELETE FROM departments
WHERE department_id = upd_dept(department_id);
FROM employees;
WHERE department_id = upd_dept(80);
(*)
♦SELECT upd_dept(80)
FROM dual;
♦They can be used in a WHERE clause to filter data.
♦They can do the same job as built-in system functions such as UPPER and ROUND. (*)
♦They can often be used inside SQL statements.
3. Function DOUBLE_SAL has been created as follows:
(p_salary IN employees.salary%TYPE)
RETURN NUMBER IS
BEGIN
RETURN(p_salary * 2);
END;
Which of the following calls to DOUBLE_SAL will NOT work?
♦SELECT *
FROM employees
WHERE double_sal(salary) > 20000;
FROM employees
ORDER BY double_sal(salary) DESC;
SET salary = double_sal(salary);
FROM employees;
♦None of the above; they will all work (*)
4. You want to create a function which can be used in a SQL statement. Which one of the following can be coded within your function?
♦RETURN BOOLEAN
♦One or more IN parameters (*)
♦An OUT parameter
♦COMMIT;
5. Which of the following is NOT a legal location for a function call in a SQL statement?
♦FROM clause of a SELECT statement (*)
♦WHERE clause in a DELETE statement
♦SET clause of an UPDATE statement
♦VALUES clause of an INSERT statement
6. User-defined functions can extend the power of SQL statements where Oracle does not provide ready-made functions such as UPPER and LOWER. True or False?
♦True (*)
♦False
8.03. Review of Data Dictionary
1. Which of the following is NOT a benefit of the Data Dictionary?
♦It allows us to remind ourselves of the names of our tables, in case we have fogotten them.
♦It allows us to check which system privileges have been granted to us.
♦It will speed up the execution of SELECT statements in which the WHERE clause column is not indexed. (*)
♦It allows the PL/SQL compiler to check for object existence; for example, when creating a procedure which references a table, the PL/SQL compiler can check that the table exists.
2. Which of the following will display how many objects of each type are in a user’s schema?
♦SELECT COUNT(*)
FROM user_objects;
♦SELECT object_type, COUNT(*)
FROM user_objects
GROUP BY object_type;
(*)
♦SELECT object_type, COUNT(*)
FROM all_objects
GROUP BY object_type;
♦DESCRIBE user_objects
GROUP BY object_type;
♦There are 15 views in Mary’s schema. (*)
♦Mary has created views on 15 of her tables.
♦There are 15 views in the database.
♦Other users have granted Mary SELECT privilege on 15 of their views.
4. Which of the following best describes the Data Dictionary?
♦It is a set of tables which can be updated by any user who has the necessary privileges.
♦It is an automatically managed master catalog of all the objects stored in the database. (*)
♦It contains a backup copy of all the data in the database.
♦It contains a list of all database tables which are not in any schema.
5. You have forgotten the name of the Dictionary view USER_TABLES. Which of the following statements is the best and quickest way to remind yourself?
♦SELECT * FROM dictionary
WHERE table_name LIKE ‘USER%’;
♦SELECT * FROM dict
WHERE table_name LIKE ‘USER%TAB%’;
WHERE table_name = ‘USER_TABLES’;
6. You have created a stored procedure called MYPROC2. Where is your detailed procedure code stored?
♦In the dictionary view USER_PROCEDURES
♦In a table named LOG_ERRORS
♦In the dictionary view USER_SOURCE (*)
♦In the dictionary view USER_CODE
7. Which of the following statements about the “super-view” DICTIONARY is true?
♦It lists all the dictionary views.
♦It can be thought of as a “catalog of the master catalog”.
♦We can use it like a Web search engine to remind ourselves of the names of dictionary views.
♦All of the above. (*)
♦None of the above.
8. User BOB is not a database administrator. BOB wants to see the names of all the tables in his schema, as well as all the tables in other users’ schemas which he has privileges to use. Which Data Dictionary view would BOB query to do this?
♦USER_TABLES
♦ALL_TABLES (*)
♦DBA_TABLES
♦USER_TAB_COLUMNS
♦None of the above.
9. You try to create a stored procedure called MYPROC1, but you make a number of syntax errors. Where are your errors stored?
♦In MYPROC1.ERRORS
♦In a table named ERROR.LOG
♦In the dictionary view USER_ERRORS (*)
♦In a subprogram called ERRORS.MYPROC1
10. A user executes the following statement:
FROM user_indexes
WHERE index_name LIKE ‘fn%’;
♦FN_INDEX
♦fn_index FN_INDEX
♦No output will be displayed (*)
8.04. Managing Procedures and Functions
♦DROP PROGRAM badproc;
♦ALTER PROCEDURE badproc DISABLE;
♦DROP PROCEDURE badproc (*)
2. Which view would you query to see the detailed code of a procedure?
♦user_source (*)
♦user_procedures
♦user_objects
♦user_dependencies
♦user_errors
3. Procedure ins_emp accepts an employee_id as an IN parameter and attempts to insert a row with that employee_id into the EMPLOYEES table. Ins_emp does not contain an exception section. A second procedure is created as follows:
BEGIN
ins_emp(99); — this employee does not exist
ins_emp(100); — this employee already exists
ins_emp(999); — this employee does not exist
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An exception occurred’);
END;
When call_ins_emp is executed, which rows will be inserted into the EMPLOYEES table?
♦99 only (*)
♦99 and 999
♦All three rows will be inserted
♦999 only
♦No rows will be inserted
4. Which dictionary view will list all the PL/SQL subprograms in your schema?
♦user_source
♦user_procedures
♦user_objects (*)
♦user_dependencies
♦user_subprograms
5. The database administrator has granted the DROP ANY PROCEDURE privilege to user KIM. This allows Kim to remove other users’ procedures and functions from the database. How would Kim now drop function GET_EMP, which is owned by user MEHMET?
♦DROP FUNCTION get_emp FROM mehmet
♦DROP FUNCTION mehmet.get_emp (*)
♦DROP PROCEDURE mehmet.get_emp
♦DROP PROGRAM mehmet.get_emp
♦None of the above
6. proc_a has been created as follows:
v_last_name employees.last_name%TYPE;
BEGIN
SELECT last_name INTO v_last_name FROM employees
WHERE employee_id = 999;
/* This SELECT will raise an exception because employee_id 999 does not exist */
DBMS_OUTPUT.PUT_LINE(‘This SELECT failed’);
END;
BEGIN
proc_a;
DBMS_OUTPUT.PUT_LINE(‘proc_a was invoked’);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An exception occurred’);
END;
♦This SELECT failed
proc_a was invoked
♦An exception occurred
proc_a was invoked
8.05. Review of Object Privileges
♦SELECT and EXECUTE on EMP_VIEW
♦SELECT on EMP_VIEW (*)
♦SELECT on EMP_VIEW and REFERENCES on EMPLOYEES
2. User COLLEEN owns an EMPLOYEES table and wants to allow user AYSE to create indexes on the table. Which object privilege must Colleen grant to Ayse?
♦SELECT on EMPLOYEES
♦INDEX on EMPLOYEES (*)
♦ALTER on EMPLOYEES
♦CREATE on EMPLOYEES
♦None of the above
3. User DIANE owns a DEPARTMENTS table. User JOEL needs to update the location_id column of Diane’s table, but no other columns. Which SQL statement should Diane execute to allow this?
♦GRANT UPDATE ON departments TO joel;
♦GRANT UPDATE ON departments(location_id) TO joel;
♦GRANT UPDATE ON departments.location_id TO joel;
♦GRANT UPDATE(location_id) ON departments TO joel; (*)
♦GRANT UPDATE ON location_id OF departments TO joel;
4. User FRED creates a procedure called DEL_DEPT using Definer’s Rights, which deletes a row from Fred’s DEPARTMENTS table. What privilege(s) will user BOB need to be able to execute Fred’s procedure?
♦EXECUTE on DEL_DEPT (*)
♦EXECUTE on DEL_DEPT and DELETE on DEPARTMENTS
♦EXECUTE on DEL_DEPT and DELETE on FRED.DEPARTMENTS
♦DELETE on FRED.DEPARTMENTS
5. User TOM needs to grant both SELECT and INSERT privileges on both his EMPLOYEES and DEPARTMENTS tables to both DICK and HARRY. What is the smallest number of GRANT statements needed to do this?
♦1
♦2 (*)
♦3
♦4
♦8
6. Assuming you are USER1, what is the PL/SQL syntax for invoking a stored procedure named “myproc” owned by a user named “USER2”?
♦EXECUTE USER2.myproc;
♦EXECUTE IMMEDIATE USER2.myproc;
♦call user2.myproc;
♦user2.myproc; (*)
7. USERB creates a function called SEL_PROC (using Definer’s Rights) which includes the statement:
♦UserB needs SELECT on userA.employees (*)
♦UserC needs SELECT on userA.employees
♦UserC needs EXECUTE on userB.sel_proc (*)
♦UserA needs EXECUTE on userB.sel_proc
♦UserC needs EXECUTE on Userb
8.06. Using Invoker’s Rights
SELECT … FROM NEWEMP … ;
Sally also grants EXECUTE privilege on the procedure to CURLY, but no other privileges. What will happen when Curly executes the procedure?
♦The procedure will execute successfully.
♦The procedure will fail because Curly does not have SELECT privilege on NEWEMP.
♦The procedure will fail because there is no NEWEMP table in Curly’s schema. (*)
♦The procedure will fail because Curly does not have the EXECUTE ANY PROCEDURE system privilege.
2. Procedure GET_EMPS includes a SELECT … FROM EMPLOYEES. The procedure was created using Invoker’s Rights. Which of the following statements are true? (Choose three.)
♦The user who executes the procedure needs EXECUTE privilege on the procedure. (*)
♦The creator of the procedure needs SELECT privilege on EMPLOYEES. (*)
♦The user who executes the procedure does not need any privileges.
♦The user who executes the procedure needs SELECT privilege on EMPLOYEES. (*)
3. When using Invoker’s rights, the invoker needs privileges on the database objects referenced within the subprogram, as well as GRANT privilege on the procedure. True or False?
♦True
♦False (*)
4. Which of the following is the correct syntax to create a procedure using Invoker’s Rights?
♦CREATE PROCEDURE myproc IS
AUTHID CURRENT_USER
BEGIN …
♦CREATE PROCEDURE myproc
AUTHID CURRENT_USER IS
BEGIN …
(*)
♦CREATE PROCEDURE AUTHID CURRENT_USER myproc IS
BEGIN …
♦CREATE PROCEDURE myproc IS
BEGIN
AUTHID CURRENT_USER …
5. What will happen when the following subprogram is compiled?
PROCEDURE at_proc IS
PRAGMA AUTONOMOUS_TRANSACTION;
dept_id NUMBER := 90;
BEGIN
UPDATE …
INSERT …
END at_proc;
♦The autonomous transaction subprogram will fail because it must include COMMIT or ROLLBACK. (*)
♦The compilation will fail because a semicolon after AUTONOMOUS_TRANSACTION is not needed.
♦The program will compile successfully.
6. Users SYS (the DBA), TOM, DICK and HARRY each have an EMPLOYEES table in their schemas. SYS creates a procedure DICK.SEL_EMP using Invoker’s Rights which contains the following code:
SELECT … FROM EMPLOYEES … ;
♦DICK.EMPLOYEES
♦HARRY.EMPLOYEES (*)
♦None of the above
♦False (*)
InfoCad Blog 