Intermediate
Q: | |||||||||||
6-14. | The only complication comes into play when you assign a value to a field in a record:
| ||||||||||
Q: | |||||||||||
6-15. | Here are the methods:
| ||||||||||
Q: | |||||||||||
6-16. | You can specify NOT NULL for the collection type: SQL> DECLARE 2 TYPE a_table_type IS TABLE OF NUMBER(10) NOT NULL 3 INDEX BY BINARY_INTEGER; 4 a_table a_table_type; 5 BEGIN 6 a_table(-100) := -9876; 7 a_table(-100) := NULL; -- will fail! 8 END; 9 / DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error TipYou can also use IS NULL to check values before assigning them to rows. This approach involves writing more code, but Oracle claims that it is more efficient than the declarative constraint NOT NULL. | ||||||||||
Q: | |||||||||||
6-17. | First, declare a cursor to fetch the employee numbers: DECLARE
CURSOR curs_get_empnos IS
SELECT employee_id
FROM employee;Then define a table TYPE and index-by table based on the employee ID number: TYPE v_employee_table_type IS TABLE OF employee.employee_id%TYPE INDEX ... |