1. Basic Usage with a Single Variable
DECLARE
v_salary employees.salary%TYPE;
BEGIN
v_salary := 5000;
END;
2. Using %TYPE in a Procedure
CREATE OR REPLACE PROCEDURE get_employee_name (
p_emp_id IN employees.employee_id%TYPE,
p_name OUT employees.last_name%TYPE
) IS
BEGIN
SELECT last_name INTO p_name FROM employees WHERE employee_id = p_emp_id;
END;
3. Using %TYPE in a Function
CREATE OR REPLACE FUNCTION get_employee_salary (
p_emp_id IN employees.employee_id%TYPE
) RETURN employees.salary%TYPE IS
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
RETURN v_salary;
END;
4. Using %TYPE with Record Types
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE
);
v_emp emp_rec;
BEGIN
SELECT employee_id, last_name INTO v_emp FROM employees WHERE employee_id = 100;
END;
5. Using %TYPE in a Dynamic SQL Statement
DECLARE
v_column_name VARCHAR2(30) := 'salary';
v_value employees.salary%TYPE := 5000;
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET ' || v_column_name || ' = :1' USING v_value;
END;
6. Using %TYPE with Cursors
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees;
v_salary emp_cursor%ROWTYPE.salary%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_salary;
CLOSE emp_cursor;
END;
7. Using %TYPE in an Update Statement
DECLARE
v_emp_id employees.employee_id%TYPE := 100;
v_salary employees.salary%TYPE := 6000;
BEGIN
UPDATE employees
SET salary = v_salary
WHERE employee_id = v_emp_id;
END;
8. Using %TYPE in a PL/SQL Package
CREATE OR REPLACE PACKAGE emp_pkg IS
PROCEDURE set_salary (p_emp_id IN employees.employee_id%TYPE, p_salary IN employees.salary%TYPE);
END emp_pkg;
9. Using %TYPE for Input Parameters in Procedures
CREATE OR REPLACE PROCEDURE process_employee (
p_emp_id IN employees.employee_id%TYPE,
p_emp_name IN employees.last_name%TYPE
) IS
BEGIN
-- Procedure logic
END;
10. Using %TYPE with Nested Tables
DECLARE
TYPE emp_table IS TABLE OF employees.employee_id%TYPE;
v_emp_ids emp_table := emp_table();
BEGIN
v_emp_ids.EXTEND(3);
v_emp_ids(1) := 100;
v_emp_ids(2) := 101;
v_emp_ids(3) := 102;
END;
11. Using %TYPE with VARRAYs
DECLARE
TYPE emp_array_type IS VARRAY(10) OF employees.employee_id%TYPE;
emp_array emp_array_type;
BEGIN
emp_array := emp_array_type(100, 101, 102);
END;
12. Using %TYPE for Constants
DECLARE
v_constant employees.salary%TYPE := 5000;
BEGIN
-- Use v_constant
END;
13. Using %TYPE with PL/SQL Collections
DECLARE
TYPE emp_list IS TABLE OF employees.last_name%TYPE;
v_emp_list emp_list;
BEGIN
v_emp_list := emp_list();
END;
14. Using %TYPE for Record Variables
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE
);
v_emp emp_rec;
BEGIN
v_emp.emp_id := 100;
v_emp.emp_name := 'Smith';
END;
15. Using %TYPE in SQL Queries
DECLARE
v_emp_id employees.employee_id%TYPE;
BEGIN
SELECT employee_id INTO v_emp_id FROM employees WHERE last_name = 'Smith';
END;
16. Using %TYPE in SQL*Plus Scripts
DECLARE
v_dept_id departments.department_id%TYPE := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('Department ID: ' || v_dept_id);
END;
17. Using %TYPE with Exception Handling
DECLARE
v_salary employees.salary%TYPE;
BEGIN
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_salary := 0;
END;
END;
18. Using %TYPE in Complex Queries
DECLARE
v_emp_id employees.employee_id%TYPE;
v_last_name employees.last_name%TYPE;
BEGIN
SELECT employee_id, last_name INTO v_emp_id, v_last_name FROM employees WHERE employee_id = 100;
END;
19. Using %TYPE for PL/SQL Tables
DECLARE
TYPE emp_table_type IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;
emp_table emp_table_type;
BEGIN
emp_table(1) := 100;
END;
20. Using %TYPE with Procedures and Functions
CREATE OR REPLACE PROCEDURE update_salary (
p_emp_id IN employees.employee_id%TYPE,
p_new_salary IN employees.salary%TYPE
) IS
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET salary = :1 WHERE employee_id = :2' USING p_new_salary, p_emp_id;
END;
21. Using %TYPE for Procedure Return Types
CREATE OR REPLACE FUNCTION calculate_bonus (
p_salary IN employees.salary%TYPE
) RETURN NUMBER IS
BEGIN
RETURN p_salary * 0.10;
END;
22. Using %TYPE with Multiple Variables
DECLARE
v_emp_id employees.employee_id%TYPE;
v_emp_name employees.last_name%TYPE;
BEGIN
v_emp_id := 101;
v_emp_name := 'Doe';
END;
23. Using %TYPE for Dynamic Data Types
DECLARE
v_column_name VARCHAR2(30) := 'salary';
v_value employees.salary%TYPE := 5000;
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET ' || v_column_name || ' = :1' USING v_value;
END;
24. Using %TYPE in PL/SQL Packages
CREATE OR REPLACE PACKAGE emp_pkg IS
PROCEDURE set_salary (p_emp_id IN employees.employee_id%TYPE, p_salary IN employees.salary%TYPE);
END emp_pkg;
25. Using %TYPE for Nested PL/SQL Blocks
DECLARE
v_salary employees.salary%TYPE;
BEGIN
BEGIN
v_salary := 6000;
EXCEPTION
WHEN OTHERS THEN
v_salary := 0;
END;
END;
26. Using %TYPE in Triggers
CREATE OR REPLACE TRIGGER emp_salary_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < :OLD.salary THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased.');
END IF;
END;
27. Using %TYPE in Dynamic PL/SQL
DECLARE
v_column_name VARCHAR2(30) := 'salary';
v_value employees.salary%TYPE := 5000;
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET ' || v_column_name || ' = :1' USING v_value;
END;
28. Using %TYPE for Multiple Cursors
DECLARE
CURSOR emp_cursor IS
SELECT employee_id FROM employees;
CURSOR dept_cursor IS
SELECT department_id FROM departments;
v_emp_id emp_cursor%ROWTYPE.employee_id%TYPE;
v_dept_id dept_cursor%ROWTYPE.department_id%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_emp_id;
CLOSE emp_cursor;
OPEN dept_cursor;
FETCH dept_cursor INTO v_dept_id;
CLOSE dept_cursor;
END;
29. Using %TYPE in Complex Data Structures
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%
TYPE
);
TYPE emp_table IS TABLE OF emp_rec;
emp_data emp_table;
BEGIN
-- Logic to populate and use emp_data
END;
30. Using %TYPE for Loop Variables
DECLARE
v_emp_id employees.employee_id%TYPE;
BEGIN
FOR emp_rec IN (SELECT employee_id FROM employees) LOOP
v_emp_id := emp_rec.employee_id;
-- Process v_emp_id
END LOOP;
END;
31. Using %TYPE in Complex PL/SQL Procedures
CREATE OR REPLACE PROCEDURE update_employee_details (
p_emp_id IN employees.employee_id%TYPE,
p_salary IN employees.salary%TYPE,
p_dept_id IN departments.department_id%TYPE
) IS
BEGIN
UPDATE employees
SET salary = p_salary, department_id = p_dept_id
WHERE employee_id = p_emp_id;
END;
32. Using %TYPE for Table Column Values
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;
END;
33. Using %TYPE with PL/SQL Table Indexes
DECLARE
TYPE emp_table IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;
emp_ids emp_table;
BEGIN
emp_ids(1) := 100;
emp_ids(2) := 101;
END;
34. Using %TYPE for PL/SQL Variables in Scripts
DECLARE
v_emp_name employees.last_name%TYPE;
BEGIN
v_emp_name := 'Smith';
END;
35. Using %TYPE for PL/SQL Variables in Triggers
CREATE OR REPLACE TRIGGER emp_salary_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < :OLD.salary THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased.');
END IF;
END;
36. Using %TYPE in SQL Queries
DECLARE
v_emp_id employees.employee_id%TYPE;
BEGIN
SELECT employee_id INTO v_emp_id FROM employees WHERE last_name = 'Smith';
END;
37. Using %TYPE for Complex Procedures
CREATE OR REPLACE PROCEDURE process_employee (
p_emp_id IN employees.employee_id%TYPE,
p_salary IN employees.salary%TYPE,
p_department_id IN departments.department_id%TYPE
) IS
BEGIN
-- Procedure logic
END;
38. Using %TYPE in a PL/SQL Function
CREATE OR REPLACE FUNCTION get_department_name (
p_dept_id IN departments.department_id%TYPE
) RETURN departments.department_name%TYPE IS
v_dept_name departments.department_name%TYPE;
BEGIN
SELECT department_name INTO v_dept_name FROM departments WHERE department_id = p_dept_id;
RETURN v_dept_name;
END;
39. Using %TYPE with Temporary Variables
DECLARE
v_temp employees.salary%TYPE;
BEGIN
v_temp := 7000;
-- Use v_temp
END;
40. Using %TYPE in Procedures with Cursors
CREATE OR REPLACE PROCEDURE list_employees IS
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees;
v_emp_id emp_cursor%ROWTYPE.employee_id%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_emp_id;
CLOSE emp_cursor;
END;
41. Using %TYPE for Record Variables in Packages
CREATE OR REPLACE PACKAGE emp_pkg IS
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE
);
v_emp emp_rec;
END emp_pkg;
42. Using %TYPE in SQL*Plus Scripts
DECLARE
v_dept_id departments.department_id%TYPE := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('Department ID: ' || v_dept_id);
END;
43. Using %TYPE with Index-by Tables
DECLARE
TYPE emp_table IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;
emp_ids emp_table;
BEGIN
emp_ids(1) := 100;
emp_ids(2) := 101;
END;
44. Using %TYPE for Record Variables with Multiple Attributes
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE,
emp_salary employees.salary%TYPE
);
v_emp emp_rec;
BEGIN
v_emp.emp_id := 100;
v_emp.emp_name := 'Smith';
v_emp.emp_salary := 6000;
END;
45. Using %TYPE with Multiple Cursors
DECLARE
CURSOR emp_cursor IS
SELECT employee_id FROM employees;
CURSOR dept_cursor IS
SELECT department_id FROM departments;
v_emp_id emp_cursor%ROWTYPE.employee_id%TYPE;
v_dept_id dept_cursor%ROWTYPE.department_id%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_emp_id;
CLOSE emp_cursor;
OPEN dept_cursor;
FETCH dept_cursor INTO v_dept_id;
CLOSE dept_cursor;
END;
46. Using %TYPE in PL/SQL Triggers
CREATE OR REPLACE TRIGGER emp_salary_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < :OLD.salary THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased.');
END IF;
END;
47. Using %TYPE for Complex PL/SQL Variables
DECLARE
v_salary employees.salary%TYPE;
v_emp_name employees.last_name%TYPE;
BEGIN
v_salary := 6000;
v_emp_name := 'Smith';
END;
48. Using %TYPE with Nested Tables
DECLARE
TYPE emp_table IS TABLE OF employees.employee_id%TYPE;
v_emp_ids emp_table := emp_table();
BEGIN
v_emp_ids.EXTEND(3);
v_emp_ids(1) := 100;
v_emp_ids(2) := 101;
v_emp_ids(3) := 102;
END;
49. Using %TYPE for Update and Insert Statements
DECLARE
v_emp_id employees.employee_id%TYPE := 100;
v_salary employees.salary%TYPE := 6000;
BEGIN
UPDATE employees
SET salary = v_salary
WHERE employee_id = v_emp_id;
INSERT INTO audit_log (employee_id, salary)
VALUES (v_emp_id, v_salary);
END;
50. Using %TYPE for Dynamic SQL Statements
DECLARE
v_column_name VARCHAR2(30) := 'salary';
v_value employees.salary%TYPE := 5000;
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET ' || v_column_name || ' = :1' USING v_value;
END;
51. Using %TYPE for PL/SQL Package Variables
CREATE OR REPLACE PACKAGE emp_pkg IS
v_emp_id employees.employee_id%TYPE;
END emp_pkg;
52. Using %TYPE in PL/SQL Functions with Records
CREATE OR REPLACE FUNCTION get_employee_details (
p_emp_id IN employees.employee_id%TYPE
) RETURN SYS_REFCURSOR IS
v_cursor SYS_REFCURSOR;
BEGIN
OPEN v_cursor FOR
SELECT employee_id, last_name, salary FROM employees WHERE employee_id = p_emp_id;
RETURN v_cursor;
END;
53. Using %TYPE for Dynamic Data Types in Procedures
CREATE OR REPLACE PROCEDURE update_salary (
p_emp_id IN employees.employee_id%TYPE,
p_new_salary IN employees.salary%TYPE
) IS
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET salary = :1 WHERE employee_id = :2' USING p_new_salary, p_emp_id;
END;
54. Using %TYPE for Temporary Variables in Cursors
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees;
v_salary emp_cursor%ROWTYPE.salary%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_salary;
CLOSE emp_cursor;
END;
55. Using %TYPE for Complex Data Structures in Packages
CREATE OR REPLACE PACKAGE emp_pkg IS
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE,
emp_salary employees.salary%TYPE
);
TYPE emp_table IS TABLE OF emp_rec;
v_emp_data emp_table;
END emp_pkg;
56. Using %TYPE in SQL Queries with Cursors
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees;
v_emp_id emp_cursor%ROWTYPE.employee_id%TYPE;
v_salary emp_cursor%ROWTYPE.salary%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_emp_id, v_salary;
CLOSE emp_cursor;
END;
57. Using %TYPE for Multiple Record Variables
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE
);
v_emp1 emp_rec;
v_emp2 emp_rec;
BEGIN
v_emp1.emp_id := 100;
v_emp1.emp_name := 'Smith';
v_emp2.emp_id := 101;
v_emp2.emp_name := 'Johnson';
END;
58. Using %TYPE in PL/SQL Collections
DECLARE
TYPE emp_list IS TABLE OF employees.salary%TYPE;
v_salaries emp_list;
BEGIN
v_salaries := emp_list();
END;
59. Using %TYPE for Record Variables in Procedures
CREATE OR REPLACE PROCEDURE process_employee (
p_emp_id IN employees.employee_id%TYPE
) IS
TYPE emp_rec IS RECORD (
emp_name employees.last_name%TYPE,
emp_salary employees.salary%TYPE
);
v_emp emp_rec;
BEGIN
SELECT last_name, salary INTO v_emp.emp_name, v_emp.emp_salary
FROM employees WHERE employee_id = p_emp_id;
END;
60. Using %TYPE in PL/SQL Triggers with Cursors
CREATE OR REPLACE TRIGGER emp_salary_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be negative.');
END IF;
END;
61. Using %TYPE in PL/SQL Packages with Cursors
CREATE OR REPLACE PACKAGE emp_pkg IS
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees;
TYPE emp_rec IS RECORD (
emp_id emp_cursor%ROWTYPE.employee_id%TYPE,
emp_name emp_cursor%ROWTYPE.last_name%TYPE
);
v_emp emp_rec;
END emp_pkg;
62. Using %TYPE for Dynamic SQL Queries
DECLARE
v_column_name VARCHAR2(30) := 'salary';
v_value employees.salary%TYPE := 5000;
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET ' || v_column_name || ' = :1' USING v_value;
END;
63. Using %TYPE with PL/SQL Functions and Records
CREATE OR REPLACE FUNCTION get_employee_record (
p_emp_id IN employees.employee_id%TYPE
) RETURN SYS_REFCURSOR IS
v_cursor SYS_REFCURSOR;
BEGIN
OPEN v_cursor FOR
SELECT employee_id, last_name, salary FROM employees WHERE employee_id = p_emp_id;
RETURN v_cursor;
END;
64. Using %TYPE for Multiple Cursors in a Package
CREATE OR REPLACE PACKAGE emp_pkg IS
CURSOR emp_cursor IS
SELECT employee_id FROM employees;
CURSOR dept_cursor IS
SELECT department_id FROM departments;
TYPE emp_rec IS RECORD (
emp_id emp_cursor%ROWTYPE.employee_id%TYPE
);
v_emp emp_rec;
v_dept_id dept_cursor%ROWTYPE.department_id%TYPE;
END emp_pkg;
65. Using %TYPE in Complex PL/SQL Procedures with Records
CREATE OR REPLACE PROCEDURE process_employee (
p_emp_id IN employees.employee_id%TYPE
) IS
TYPE emp_rec IS RECORD (
emp_name employees.last_name%TYPE,
emp_salary employees.salary%TYPE
);
v_emp emp_rec;
BEGIN
SELECT last_name, salary INTO v_emp.emp_name, v_emp.emp_salary
FROM employees WHERE employee_id = p_emp_id;
END;
66. Using %TYPE for Temporary Variables in Procedures
CREATE OR REPLACE PROCEDURE update_salary (
p_emp_id IN employees.employee_id%TYPE,
p_new_salary IN employees.salary%TYPE
) IS
v_current_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_current_salary FROM employees WHERE employee_id = p_emp_id;
UPDATE employees SET salary = p_new_salary WHERE employee_id = p_emp_id;
END;
67. Using %TYPE with Nested Tables and Records
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE
);
TYPE emp_table IS TABLE OF emp_rec;
v_emp_data emp_table;
BEGIN
-- Populate and use v_emp_data
END;
68. Using %TYPE in PL/SQL Scripts for Debugging
DECLARE
v_salary employees.salary%TYPE := 6000;
BEGIN
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
69. Using %TYPE for Dynamic Queries with Cursors
DECLARE
v_column_name VARCHAR2(30) := 'salary';
v_value employees.salary%TYPE := 5000;
CURSOR emp_cursor IS
SELECT employee_id FROM employees;
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET ' || v_column_name || ' = :1' USING v_value;
FOR emp_rec IN emp_cursor LOOP
-- Process emp_rec
END LOOP;
END;
70. Using %TYPE for PL/SQL Package Variables and Procedures
CREATE OR REPLACE PACKAGE emp_pkg IS
v_emp_name employees.last_name%TYPE;
PROCEDURE set_employee_name (p_emp_id IN employees.employee_id%TYPE, p_name IN employees.last_name%TYPE);
END emp_pkg;
71. Using %TYPE for Complex PL/SQL Variables and Collections
DECLARE
TYPE emp_list IS TABLE OF employees.salary%TYPE;
v_salaries emp_list;
BEGIN
v_salaries := emp_list();
END;
72. Using %TYPE with PL/SQL Collections and Records
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE
);
TYPE emp_table IS TABLE OF emp_rec;
v_emp_data emp_table;
BEGIN
-- Populate and use v_emp_data
END;
73. Using %TYPE for Record Variables in SQL Queries
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE
);
v_emp emp_rec;
BEGIN
SELECT employee_id, last_name INTO v_emp.emp_id, v_emp.emp_name FROM employees WHERE employee_id = 100;
END;
74. Using %TYPE in SQL Queries with Dynamic Data Types
DECLARE
v_column_name VARCHAR2(30) := 'salary';
v_value employees.salary%TYPE := 5000;
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET ' || v_column_name || ' = :1' USING v_value;
END;
75. Using %TYPE with PL/SQL Packages for Cursors
CREATE OR REPLACE PACKAGE emp_pkg IS
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees;
TYPE emp_rec IS RECORD (
emp_id emp_cursor%ROWTYPE.employee_id%TYPE,
emp_salary emp_cursor%ROWTYPE.salary%TYPE
);
v_emp emp_rec;
END emp_pkg;
76. Using %TYPE in SQL*Plus Scripts for Output
DECLARE
v_salary employees.salary%TYPE := 5000;
BEGIN
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
77. Using %TYPE for Dynamic SQL in Procedures
CREATE OR REPLACE PROCEDURE update_employee_salary (
p_emp_id IN employees.employee_id%TYPE,
p_new_salary IN employees.salary%TYPE
) IS
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET salary = :1 WHERE employee_id = :2' USING p_new_salary, p_emp_id;
END;
78. Using %TYPE for Record Variables with Nested Tables
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE
);
TYPE emp_table IS TABLE OF emp_rec;
v_emp_data emp_table;
BEGIN
-- Populate and use v_emp_data
END;
79. Using %TYPE for Cursors in PL/SQL Packages
CREATE OR REPLACE PACKAGE emp_pkg IS
CURSOR emp_cursor IS
SELECT employee_id FROM employees
;
TYPE emp_rec IS RECORD (
emp_id emp_cursor%ROWTYPE.employee_id%TYPE
);
v_emp emp_rec;
END emp_pkg;
80. Using %TYPE with Nested Tables and Index-by Tables
DECLARE
TYPE emp_table IS TABLE OF employees.salary%TYPE INDEX BY PLS_INTEGER;
v_salaries emp_table;
BEGIN
v_salaries(1) := 5000;
v_salaries(2) := 6000;
END;
81. Using %TYPE in Complex PL/SQL Procedures with Collections
CREATE OR REPLACE PROCEDURE process_salaries (
p_salaries IN SYS.ODCINUMBERLIST
) IS
BEGIN
-- Procedure logic
END;
82. Using %TYPE for Record Variables and Cursors
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_salary employees.salary%TYPE
);
v_emp emp_rec;
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_emp.emp_id, v_emp.emp_salary;
CLOSE emp_cursor;
END;
83. Using %TYPE with Multiple Records in PL/SQL Procedures
CREATE OR REPLACE PROCEDURE process_multiple_employees (
p_emp_id1 IN employees.employee_id%TYPE,
p_emp_id2 IN employees.employee_id%TYPE
) IS
TYPE emp_rec IS RECORD (
emp_name employees.last_name%TYPE,
emp_salary employees.salary%TYPE
);
v_emp1 emp_rec;
v_emp2 emp_rec;
BEGIN
SELECT last_name, salary INTO v_emp1.emp_name, v_emp1.emp_salary
FROM employees WHERE employee_id = p_emp_id1;
SELECT last_name, salary INTO v_emp2.emp_name, v_emp2.emp_salary
FROM employees WHERE employee_id = p_emp_id2;
END;
84. Using %TYPE for Complex Data Structures and Procedures
CREATE OR REPLACE PROCEDURE process_employee_data (
p_emp_id IN employees.employee_id%TYPE
) IS
TYPE emp_rec IS RECORD (
emp_name employees.last_name%TYPE,
emp_salary employees.salary%TYPE
);
TYPE emp_table IS TABLE OF emp_rec;
v_emp_data emp_table;
BEGIN
-- Populate and use v_emp_data
END;
85. Using %TYPE in PL/SQL Packages for Complex Variables
CREATE OR REPLACE PACKAGE emp_pkg IS
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_salary employees.salary%TYPE
);
TYPE emp_table IS TABLE OF emp_rec;
v_emp_data emp_table;
END emp_pkg;
86. Using %TYPE for SQL Queries with PL/SQL Variables
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
87. Using %TYPE for PL/SQL Functions with Records and Cursors
CREATE OR REPLACE FUNCTION get_employee_data (
p_emp_id IN employees.employee_id%TYPE
) RETURN SYS_REFCURSOR IS
v_cursor SYS_REFCURSOR;
BEGIN
OPEN v_cursor FOR
SELECT employee_id, last_name, salary FROM employees WHERE employee_id = p_emp_id;
RETURN v_cursor;
END;
88. Using %TYPE with Nested Tables and PL/SQL Procedures
DECLARE
TYPE emp_table IS TABLE OF employees.salary%TYPE;
v_salaries emp_table := emp_table();
BEGIN
v_salaries.EXTEND(2);
v_salaries(1) := 5000;
v_salaries(2) := 6000;
END;
89. Using %TYPE in PL/SQL Packages with Multiple Cursors
CREATE OR REPLACE PACKAGE emp_pkg IS
CURSOR emp_cursor IS
SELECT employee_id FROM employees;
CURSOR dept_cursor IS
SELECT department_id FROM departments;
TYPE emp_rec IS RECORD (
emp_id emp_cursor%ROWTYPE.employee_id%TYPE
);
TYPE dept_rec IS RECORD (
dept_id dept_cursor%ROWTYPE.department_id%TYPE
);
v_emp emp_rec;
v_dept dept_rec;
END emp_pkg;
90. Using %TYPE with SQL Queries and PL/SQL Variables
DECLARE
v_employee_id employees.employee_id%TYPE;
v_salary employees.salary%TYPE;
BEGIN
SELECT employee_id, salary INTO v_employee_id, v_salary
FROM employees WHERE last_name = 'Smith';
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
91. Using %TYPE in PL/SQL Packages for Collections
CREATE OR REPLACE PACKAGE emp_pkg IS
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_salary employees.salary%TYPE
);
TYPE emp_list IS TABLE OF emp_rec;
v_emp_data emp_list;
END emp_pkg;
92. Using %TYPE for Dynamic SQL in PL/SQL Procedures
CREATE OR REPLACE PROCEDURE update_employee (
p_emp_id IN employees.employee_id%TYPE,
p_salary IN employees.salary%TYPE
) IS
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET salary = :1 WHERE employee_id = :2' USING p_salary, p_emp_id;
END;
93. Using %TYPE for Cursors and Record Variables
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees;
TYPE emp_rec IS RECORD (
emp_id emp_cursor%ROWTYPE.employee_id%TYPE,
emp_salary emp_cursor%ROWTYPE.salary%TYPE
);
v_emp emp_rec;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_emp;
CLOSE emp_cursor;
END;
94. Using %TYPE with PL/SQL Packages for Complex Data
CREATE OR REPLACE PACKAGE emp_pkg IS
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE,
emp_salary employees.salary%TYPE
);
TYPE emp_table IS TABLE OF emp_rec;
v_emp_data emp_table;
END emp_pkg;
95. Using %TYPE for Nested Tables and SQL Queries
DECLARE
TYPE emp_table IS TABLE OF employees.salary%TYPE;
v_salaries emp_table;
BEGIN
v_salaries.EXTEND(2);
v_salaries(1) := 5000;
v_salaries(2) := 6000;
END;
96. Using %TYPE in PL/SQL Triggers with Cursors
CREATE OR REPLACE TRIGGER emp_salary_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be negative.');
END IF;
END;
97. Using %TYPE with Multiple PL/SQL Variables in Procedures
CREATE OR REPLACE PROCEDURE process_employee_data (
p_emp_id IN employees.employee_id%TYPE
) IS
v_emp_name employees.last_name%TYPE;
v_emp_salary employees.salary%TYPE;
BEGIN
SELECT last_name, salary INTO v_emp_name, v_emp_salary
FROM employees WHERE employee_id = p_emp_id;
END;
98. Using %TYPE with Complex Data Types in Packages
CREATE OR REPLACE PACKAGE emp_pkg IS
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.last_name%TYPE,
emp_salary employees.salary%TYPE
);
TYPE emp_list IS TABLE OF emp_rec;
v_emp_data emp_list;
END emp_pkg;
99. Using %TYPE in PL/SQL Packages with Multiple Cursors
CREATE OR REPLACE PACKAGE emp_pkg IS
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees;
CURSOR dept_cursor IS
SELECT department_id FROM departments;
TYPE emp_rec IS RECORD (
emp_id emp_cursor%ROWTYPE.employee_id%TYPE,
emp_salary emp_cursor%ROWTYPE.salary%TYPE
);
TYPE dept_rec IS RECORD (
dept_id dept_cursor%ROWTYPE.department_id%TYPE
);
v_emp emp_rec;
v_dept dept_rec;
END emp_pkg;
100. Using %TYPE for Dynamic SQL in PL/SQL Packages
CREATE OR REPLACE PACKAGE emp_pkg IS
PROCEDURE update_salary (
p_emp_id IN employees.employee_id%TYPE,
p_new_salary IN employees.salary%TYPE
);
END emp_pkg;
CREATE OR REPLACE PACKAGE
BODY emp_pkg IS
PROCEDURE update_salary (
p_emp_id IN employees.employee_id%TYPE,
p_new_salary IN employees.salary%TYPE
) IS
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees SET salary = :1 WHERE employee_id = :2' USING p_new_salary, p_emp_id;
END;
END emp_pkg;
No comments:
Post a Comment