Q: | |
12-7. | Here is one possible implementation: DECLARE
TYPE emp_cvt IS REF CURSOR RETURN employee%ROWTYPE;
employees emp_cvt;
one_employee employees%ROWTYPE;
BEGIN
OPEN employees FOR SELECT * FROM employee;
LOOP
FETCH employees INTO one_employee;
EXIT WHEN employees%NOTFOUND;
double_salary (one_employee.employee_id, one_employee.salary);
END LOOP;
CLOSE employees;
END;
/ |
Q: | |
12-8. | Here are the rules to keep in mind in solving this problem: The statement must be valid. The number and datatypes of the record being declared must be compatible with the record structure in the RETURN clause of the REF CURSOR statement.
The declarations are: Valid. This statement (as well as (e) and (g)) declares records that can be used in FETCH statements against the_finest_cv cursor variable. This one declares a standard table-based record, exactly the same structure used in the REF CURSOR. Invalid. Doesn’t compile. You can’t use %ROWTYPE against a REF CURSOR, only against a cursor variable. Invalid. Doesn’t compile. You can’t declare a record of type REF CURSOR; you can only declare cursor variables based on REF CURSOR types. Invalid. Doesn’t compile. You need to add %ROWTYPE to this statement. Valid. This statement relies on a programmer-defined record that has the same structure as the table, so that works just fine. Invalid. Doesn’t compile. You can’t put a %ROWTYPE at the end of a record TYPE; it already is a type. Valid. This statement demonstrates that you can declare a record from a cursor variable with %ROWTYPE, just as you can with a table or cursor.
|
Q: | |
12-9. | Statements are: Correct. Correct. Incorrect (weakly). Incorrect (and downright silly). Correct. When you close a cursor variable, it closes the underlying cursor object.
You should not expect to see any significant difference in runtime performance between cursor variables based on strong and weak REF CURSORs, though the PL/SQL runtime engine will be doing a little more work at runtime with weak REF CURSORs. |
Q: | |
12-10. | Statements (a), (b), and (e) are all “poster children” for the cursor variable feature: Yes. Cursor variables allow you to “pass” result sets of queries between client and server programs, or between various server programs without actually passing the data. You just pass the pointer to the data. This reduces network traffic. No. This one requires the use of dynamic SQL (the SQL string is not complete at compile time). You need to use the DBMS_SQL package to implement this scenario, since Method 4 dynamic SQL is requested. Yes. This is a perfect example of why you might use cursor variables to “hide” minor differences between queries and write a single block of code that works for all of the queries. Yes. No. This one also requires dynamic SQL; it can be implemented with DBMS_SQL or native dynamic SQL (if you are running Oracle 8.1).
|
Q: | |
12-11. | The problems are: You cannot declare cursor variables in a package because they do not have persistent state. The SQL statement cannot be a literal string or a variable. It must be hardcoded or static at the time of program compilation. The OPEN FOR string syntax is used by native dynamic SQL. You cannot use FOR UPDATE inside a cursor variable’s SELECT statement. You cannot assign the NULL value to a cursor variable.
|
Q: | |
12-12. | The statement: Raises a ROWTYPE_MISMATCH error when the procedure is executed. You have requested that a record based on the department table receive a row from the employee table. When unhandled, this error displays as: ORA-06504: PL/SQL: Return types of Result Set variables or query do not match This error always rears its ugly head at runtime (a difficult point at which to debug one’s code) when you rely on weak REF CURSORs. Fails to compile with this error: PLS-00382: expression is of wrong type Once again, you have requested that a record based on the department table receive a row from the employee table. This time, however, the compiler can check for compatibility because you have used a strong REF CURSOR. Doesn’t cause a mismatch error. The airplane_food table has two numeric columns, and your query selects two numeric columns from the employee table.
|
Q: | |
12-13. | The function returns a cursor variable, so the RETURN clause of the function must reference a REF CURSOR type. The REF CURSOR type can be defined in the same package as the function (unless the function is to be “standalone”), or it can be declared in a separate package specification. Furthermore, since the REF CURSOR is based on a subset of the columns of the employee table, you also need to define a programmer-defined record type to reflect that two-field structure. Again, this needs to be defined in a package specification, either with the function or independent of it. |
Q: | |
12-14. | Here is one possible implementation of the package specification: 
/* Filename on web page: empinfo.pkg */
PACKAGE empinfo
IS
bysal CONSTANT INTEGER := 1;
bysaldesc CONSTANT INTEGER := 2;
bydept CONSTANT INTEGER := 3;
byname CONSTANT INTEGER := 4;
TYPE two_pieces_t IS RECORD (str VARCHAR2(100), num NUMBER);
TYPE emp_cvt IS REF CURSOR RETURN two_pieces_t;
FUNCTION open (query_number IN INTEGER) RETURN emp_cvt;
END;
Notice that you need to specify only that the record consists of one string and one number. You could have also relied on %TYPE declarations as follows: TYPE two_pieces_t IS RECORD (
str employee.last_name%TYPE, num employee.salary%TYPE); |
Q: | |
12-15. | Here is one possible implementation:
/* Filename on web page: empinfo.pkg */
CREATE OR REPLACE PACKAGE BODY empinfo
IS
FUNCTION open (query_number IN INTEGER) RETURN emp_cvt
IS
retval emp_cvt;
BEGIN
IF query_number = bysal
THEN
OPEN retval FOR
SELECT last_name, salary FROM employee ORDER BY salary;
ELSIF query_number = bysaldesc
THEN
OPEN retval FOR
SELECT last_name, salary
FROM employee ORDER BY salary DESC;
ELSIF query_number = bydept
THEN
OPEN retval FOR
SELECT last_name, department_id
FROM employee ORDER BY department_id;
ELSIF query_number = byname
THEN
OPEN retval FOR
SELECT first_name || ' ' || last_name, salary
FROM employee ORDER BY last_name;
END IF;
RETURN retval;
END;
END;
/ |
Q: | |
12-16. | Here is one possible implementation: PROCEDURE show (query_number IN INTEGER)
IS
cv emp_cvt;
rec cv%ROWTYPE;
BEGIN
cv := open (query_number);
LOOP
FETCH cv INTO rec;
EXIT WHEN cv%NOTFOUND;
IF cv%ROWCOUNT = 1
THEN
DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-'));
DBMS_OUTPUT.PUT_LINE ('Contents of Query ' || query_number);
DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-'));
END IF;
DBMS_OUTPUT.PUT_LINE (RPAD (rec.str, 30) || rec.num);
END LOOP;
CLOSE cv;
END; |
