Oracle PL/SQL Programming: A Developer's Workbook

Chapter 17. Calling Functions in SQL

Beginner

Q:
17-1.	Generally, the answer is “Yes! You can call your own PL/SQL functions from within a SQL statement.” There are, however, a number of restrictions and requirements governing how and when you can do this. The following exercises test your knowledge of these conditions.
Q:
17-2.	No. When executed inside a SQL statement, your functions can operate only on individual column values within a single row.
Q:
17-3.	First, here is the definition of the function: CREATE OR REPLACE FUNCTION totcomp (sal_in IN PLS_INTEGER, comm_in IN NUMBER := NULL) RETURN NUMBER IS BEGIN RETURN (sal_in + NVL (comm_in, 0)); END; / Now you can apply that function back into the statement: SELECT ename, totcomp (sal, comm) total FROM emp WHERE totcomp (sal, comm) > 1000;
Q:
17-4.	The statements are: True. False. The datatypes of the parameters and the RETURN clause of the function can only be valid SQL datatypes. You cannot, for example, return an index-by table (a.k.a., PL/SQL table). You also cannot pass in a Boolean value. The Boolean datatype is not currently supported in SQL. False. That’s downright silly. You can name your function whatever you want; it certainly cannot be the name of a table. False. You can only call one of your own functions if it is stored in the database; client-side functions are not accessible from within SQL. False. You can call a function in any non-DDL SQL statement, whether a query or a DML statement.
Q:
17-5.	No, you cannot directly access a procedure ...