Oracle PL/SQL Programming: A Developer's Workbook

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