17-1.

Can you call a PL/SQL function from within a SQL statement?

17-2.

Can your own function act like a SQL group function (SUM, MIN, MAX, etc.) in a SQL statement?

17-3.

The following query contains a redundant formula to compute the total compensation of an employee (salary + commission):

SELECT ename, sal + NVL (comm, 0)
  FROM emp
 WHERE sal + NVL (comm, 0) > 1000;

Rewrite this query so that it instead calls a function to calculate the total compensation.

17-4.

Which of the following statements regarding the execution of functions in SQL are true, and which are false?

17-5.

Can you call a PL/SQL procedure from within a SQL statement?

17-6.

When you want to call a packaged function (a function defined in the specification of a package) from within an SQL statement, what statement do you need to add to that specification? What is it for?

17-7.

What are the four different purity levels you can attempt to assert about a function?

17-8.

Add the necessary code to this package specification to assert all four purity levels for the total function:

CREATE OR REPLACE PACKAGE comp
IS
   FUNCTION total
      (sal_in IN NUMBER,
       comm_in IN NUMBER := NULL)
      RETURN NUMBER;
END;
/

17-9.

What is the minimum required purity level for a packaged function to be callable inside SQL?

17-10.

Under what circumstances do you need to assert the WNPS purity level for a packaged function or procedure?

17-11.

Which purity levels can you assert about the following function if you define it inside a package?

FUNCTION betwnstr
   (str IN VARCHAR2, startat IN INTEGER, endat IN INTEGER := NULL)
   RETURN VARCHAR2
IS
BEGIN
   RETURN SUBSTR (str, startat, endat-startat+1);
END;

17-12.

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
   SELECT ename INTO retval
     FROM emp WHERE empno = empno_in;
   RETURN retval;
END;

17-13.

Suppose that you define the following package in the database:

PACKAGE who_is_paying_whom
IS
    too_low CONSTANT NUMBER := 15000; /* This is above minimum wage! */
    my_salary CONSTANT NUMBER := too_low;
    ceo_salary NUMBER := too_low * 250;
END;

Which purity levels can you assert about the following functions?

17-14.

Suppose you define a package specification as follows:

CREATE OR REPLACE PACKAGE comp
IS
   FUNCTION total
      (sal_in IN NUMBER, comm_in IN NUMBER := NULL)
      RETURN NUMBER;

   FUNCTION total (emp_in IN emp.empno%TYPE)
      RETURN NUMBER;

   PRAGMA RESTRICT_REFERENCES (total, WNDS, WNPS, RNDS, RNPS);
END;
/

You also create the body and get no compile errors. But when you try to use your function in a SELECT statement (prior to Oracle 8.1) as shown here, you get this error:

SQL> SELECT ename, comp.total (sal, comm) total
  2    FROM emp
  3   WHERE comp.total (sal, comm) > 0;
SELECT ename, comp.total (sal, comm) total
              *
ERROR at line 1:
ORA-06571: Function TOTAL does not guarantee not to update database

Why does this error occur?

17-15.

Suppose you wrote a query to implement this requirement:

“If a person has been hired within the last 30 days, display “JUST HIRED”. Otherwise, if that person’s last evaluation took place at least 10 days ago, display “NEEDS REVIEW”; otherwise, display “UP TO DATE”.

You used DECODE as follows:

SELECT ename,
       DECODE (
          GREATEST (
             SYSDATE - 30,
             hire_date
          ),
          SYSDATE - 30,
             DECODE (
                 LEAST (
                    SYSDATE - 10,
                    eval_date
                 ),
                 eval_date, 'NEEDS REVIEW',
                 'UP TO DATE'
              ),
          'JUST HIRED'
       ) status
  FROM emp;

Rewrite this query so that it does not use DECODE.

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 (col1)
  FROM table1
 WHERE my_function (col1) > 3;

If table1 has 100 rows in it and the WHERE clause filters out 50 of those rows, my_function is called 150 times.

Rewrite this statement using new SQL features available in Oracle8 to reduce the number of executions of my_function.