Expert
17-16. | Which purity levels can you assert about the following function? FUNCTION ename_from (empno_in IN emp.empno%TYPE) RETURN emp.ename%TYPE IS retval emp.ename%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE ('Getting name for ' || empno_in); SELECT ename INTO retval FROM emp WHERE empno = empno_in; RETURN retval; END; |
17-17. | Build a function that can be called from within a SQL statement that provides a DBMS_OUTPUT trace of each row returned by a query, showing the table name and the ROWID. |
17-18. | Build a function that can be called from within a SQL statement that provides a DBMS_PIPE trace of each row returned by a query, showing the table name and the ROWID. Why might you use DBMS_PIPE, rather than DBMS_OUTPUT? |
17-19. | In Oracle 8.0 and earlier, suppose you define your total compensation as follows: CREATE OR REPLACE FUNCTION totcomp (sal_in IN PLS_INTEGER, comm_in IN NUMBER := NULL) RETURN NUMBER IS BEGIN DELETE FROM emp; RETURN (sal_in + NVL (comm_in, 0)); END; / What happens when you execute this INSERT statement? insert into emp (empno) values (totcomp(100,200)); And what behavior do you see when you take these same steps in Oracle 8.1? |
17-20. | Implement the following request in “straight SQL” and then using PL/SQL functions: “Show me the name and salary of the employee with the highest salary in each department, along with the total salary for each department.” |
17-21. | Suppose you want to call your PL/SQL function in both the SELECT list and the WHERE clause. Here is an example: SELECT my_function ... |
Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.