1. Basic %ROWTYPE Declaration
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END;
2. Using %ROWTYPE with Cursors
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees;
v_employee emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_employee;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
CLOSE emp_cursor;
END;
3. Inserting Data into a Table Using %ROWTYPE
DECLARE
v_employee employees%ROWTYPE;
BEGIN
v_employee.employee_id := 101;
v_employee.first_name := 'John';
v_employee.last_name := 'Doe';
INSERT INTO employees VALUES v_employee;
END;
4. Updating Data with %ROWTYPE
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
v_employee.salary := v_employee.salary * 1.1;
UPDATE employees SET salary = v_employee.salary WHERE employee_id = 100;
END;
5. Deleting Data Using %ROWTYPE
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
DELETE FROM employees WHERE employee_id = v_employee.employee_id;
END;
6. Using %ROWTYPE in PL/SQL Procedures
CREATE OR REPLACE PROCEDURE get_employee (p_id IN employees.employee_id%TYPE) IS
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = p_id;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END;
7. Returning %ROWTYPE from a Function
CREATE OR REPLACE FUNCTION get_employee_info (p_id IN employees.employee_id%TYPE)
RETURN employees%ROWTYPE
IS
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = p_id;
RETURN v_employee;
END;
8. Handling %ROWTYPE in Exception Handling
DECLARE
v_employee employees%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
END;
9. Using %ROWTYPE with Dynamic SQL
DECLARE
v_employee employees%ROWTYPE;
v_sql VARCHAR2(200);
BEGIN
v_sql := 'SELECT * FROM employees WHERE employee_id = 100';
EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_employee;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END;
10. Inserting into Multiple Tables Using %ROWTYPE
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
INSERT INTO backup_employees VALUES v_employee;
INSERT INTO history_employees VALUES v_employee;
END;
11. Using %ROWTYPE in a Package
CREATE OR REPLACE PACKAGE employee_pkg AS
PROCEDURE process_employee(p_id IN employees.employee_id%TYPE);
END employee_pkg;
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
PROCEDURE process_employee(p_id IN employees.employee_id%TYPE) IS
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = p_id;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END process_employee;
END employee_pkg;
12. Cursor with %ROWTYPE and Exceptions
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees;
v_employee emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END LOOP;
CLOSE emp_cursor;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
13. Using %ROWTYPE in a Trigger
CREATE OR REPLACE TRIGGER emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = :NEW.employee_id;
IF v_employee.salary IS NOT NULL THEN
:NEW.salary := v_employee.salary * 1.1;
END IF;
END;
14. Iterating Over %ROWTYPE Results
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees;
v_employee emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FOR v_employee IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END LOOP;
CLOSE emp_cursor;
END;
15. Using %ROWTYPE with FORALL
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FORALL i IN v_employees.FIRST..v_employees.LAST
UPDATE employees SET salary = v_employees(i).salary * 1.1 WHERE employee_id = v_employees(i).employee_id;
END;
16. Passing %ROWTYPE to a Procedure
CREATE OR REPLACE PROCEDURE update_employee (p_employee IN employees%ROWTYPE) IS
BEGIN
UPDATE employees
SET first_name = p_employee.first_name,
last_name = p_employee.last_name
WHERE employee_id = p_employee.employee_id;
END;
17. Using %ROWTYPE in PL/SQL Block with SQL Cursor
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees;
v_employee emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_employee;
WHILE emp_cursor%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
FETCH emp_cursor INTO v_employee;
END LOOP;
CLOSE emp_cursor;
END;
18. Complex Query Using %ROWTYPE
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT e.*, d.department_name INTO v_employee
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id = 100;
DBMS_OUTPUT.PUT_LINE(v_employee.department_name);
END;
19. Dynamic Table Name with %ROWTYPE
DECLARE
v_employee employees%ROWTYPE;
v_table_name VARCHAR2(30) := 'employees';
v_sql VARCHAR2(200);
BEGIN
v_sql := 'SELECT * FROM ' || v_table_name || ' WHERE employee_id = 100';
EXECUTE IMMEDIATE v_sql INTO v_employee;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END;
20. Using %ROWTYPE in Bulk Operations
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FORALL i IN v_employees.FIRST..v_employees.LAST
DELETE FROM employees WHERE employee_id = v_employees(i).employee_id;
END;
21. Combining %ROWTYPE with Object Types
CREATE OR REPLACE TYPE employee_type AS OBJECT (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
DECLARE
v_employee employee_type;
v_record employees%ROWTYPE;
BEGIN
SELECT * INTO v_record FROM employees WHERE employee_id = 100;
v_employee := employee_type(v_record.employee_id, v_record.first_name, v_record.last_name);
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END;
22. Using %ROWTYPE with Multiple Tables
DECLARE
TYPE emp_dept_record IS RECORD (
emp_record employees%ROWTYPE,
dept_record departments%ROWTYPE
);
v_emp_dept emp_dept_record;
BEGIN
SELECT
e.*, d.*
INTO v_emp_dept.emp_record, v_emp_dept.dept_record
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id = 100;
DBMS_OUTPUT.PUT_LINE(v_emp_dept.emp_record.first_name || ' works in ' || v_emp_dept.dept_record.department_name);
END;
23. Using %ROWTYPE in a Function with Cursors
CREATE OR REPLACE FUNCTION get_employee_cursor (p_id IN employees.employee_id%TYPE)
RETURN SYS_REFCURSOR
IS
emp_cursor SYS_REFCURSOR;
BEGIN
OPEN emp_cursor FOR SELECT * FROM employees WHERE employee_id = p_id;
RETURN emp_cursor;
END;
24. Handling %ROWTYPE in Nested Blocks
DECLARE
v_employee employees%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
END;
25. Using %ROWTYPE with Ref Cursors
CREATE OR REPLACE PROCEDURE get_employee_ref_cursor (p_id IN employees.employee_id%TYPE, p_cursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_cursor FOR SELECT * FROM employees WHERE employee_id = p_id;
END;
26. Modifying %ROWTYPE Variables in Loops
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees;
v_employee emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee;
EXIT WHEN emp_cursor%NOTFOUND;
v_employee.salary := v_employee.salary * 1.1;
DBMS_OUTPUT.PUT_LINE(v_employee.salary);
END LOOP;
CLOSE emp_cursor;
END;
27. Using %ROWTYPE in Exception Logging
DECLARE
v_employee employees%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO error_log (error_message, error_time) VALUES ('Employee not found', SYSDATE);
END;
END;
28. Using %ROWTYPE for Multiple Records
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FOR i IN 1..v_employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_employees(i).first_name);
END LOOP;
END;
29. Performing Complex Calculations with %ROWTYPE
DECLARE
v_employee employees%ROWTYPE;
v_bonus NUMBER;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
v_bonus := v_employee.salary * 0.1;
DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
30. Creating a Record Array with %ROWTYPE
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
v_employees emp_table;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_employees(i).first_name);
END LOOP;
END;
31. Using %ROWTYPE with Exception Details
DECLARE
v_employee employees%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 999;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
END;
32. Processing %ROWTYPE in Parallel Execution
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees;
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO v_employees;
CLOSE emp_cursor;
FORALL i IN v_employees.FIRST..v_employees.LAST
UPDATE employees SET salary = v_employees(i).salary * 1.1 WHERE employee_id = v_employees(i).employee_id;
END;
33. Passing %ROWTYPE as an OUT Parameter
CREATE OR REPLACE PROCEDURE get_employee_by_id (
p_id IN employees.employee_id%TYPE,
p_employee OUT employees%ROWTYPE
) IS
BEGIN
SELECT * INTO p_employee FROM employees WHERE employee_id = p_id;
END;
34. Using %ROWTYPE in an Autonomous Transaction
CREATE OR REPLACE PROCEDURE log_employee_info (p_id IN employees.employee_id%TYPE) IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = p_id;
INSERT INTO employee_logs (employee_id, log_date) VALUES (v_employee.employee_id, SYSDATE);
COMMIT;
END;
35. Using %ROWTYPE in a Procedure with Cursors
CREATE OR REPLACE PROCEDURE process_employee (p_id IN employees.employee_id%TYPE) IS
CURSOR emp_cursor IS SELECT * FROM employees WHERE employee_id = p_id;
v_employee emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_employee;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
CLOSE emp_cursor;
END;
36. Handling %ROWTYPE in Nested Procedures
CREATE OR REPLACE PROCEDURE outer_procedure (p_id IN employees.employee_id%TYPE) IS
v_employee employees%ROWTYPE;
PROCEDURE inner_procedure IS
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = p_id;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END;
BEGIN
inner_procedure;
END;
37. Using %ROWTYPE for Dynamic SQL Statements
DECLARE
v_employee employees%ROWTYPE;
v_sql VARCHAR2(200);
BEGIN
v_sql := 'SELECT * FROM employees WHERE employee_id = 100';
EXECUTE IMMEDIATE v_sql INTO v_employee;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END;
38. Combining %ROWTYPE with Object Methods
CREATE OR REPLACE TYPE employee_obj AS OBJECT (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
MEMBER PROCEDURE display_info
);
CREATE OR REPLACE TYPE BODY employee_obj AS
MEMBER PROCEDURE display_info IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_id);
DBMS_OUTPUT.PUT_LINE('First Name: ' || first_name);
DBMS_OUTPUT.PUT_LINE('Last Name: ' || last_name);
END display_info;
END;
DECLARE
v_employee employee_obj;
BEGIN
SELECT employee_obj(employee_id, first_name, last_name)
INTO v_employee
FROM employees WHERE employee_id = 100;
v_employee.display_info;
END;
39. Using %ROWTYPE for Complex SQL Queries
DECLARE
v_employee employees%ROWTYPE;
v_department departments%ROWTYPE;
BEGIN
SELECT e.*, d.*
INTO v_employee, v_department
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id = 100;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name || ' is in ' || v_department.department_name);
END;
40. Using %ROWTYPE for Data Transformation
DECLARE
v_employee employees%ROWTYPE;
v_transformed_salary NUMBER;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
v_transformed_salary := v_employee.salary * 1.2;
DBMS_OUTPUT.PUT_LINE('Transformed Salary: ' || v_transformed_salary);
END;
41. Using %ROWTYPE in Trigger for Validation
CREATE OR REPLACE TRIGGER validate_employee_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
END IF;
END;
42. Passing %ROWTYPE to a Function
CREATE OR REPLACE FUNCTION get_employee_salary (p_employee IN employees%ROWTYPE)
RETURN NUMBER
IS
BEGIN
RETURN p_employee.salary;
END;
43. Processing Multiple Rows
with %ROWTYPE and PL/SQL Table
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_employees(i).first_name);
END LOOP;
END;
44. Using %ROWTYPE in Cursor for Loop
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees;
v_employee emp_cursor%ROWTYPE;
BEGIN
FOR v_employee IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END LOOP;
END;
45. Handling %ROWTYPE in Dynamic SQL with PL/SQL Table
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
v_sql VARCHAR2(200) := 'SELECT * FROM employees';
BEGIN
EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_employees;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_employees(i).first_name);
END LOOP;
END;
46. Using %ROWTYPE for Data Aggregation
DECLARE
v_employee employees%ROWTYPE;
v_total_salary NUMBER;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
v_total_salary := v_employee.salary + (SELECT SUM(salary) FROM employees);
DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_total_salary);
END;
47. Combining %ROWTYPE with Collections
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FOR i IN 1..v_employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_employees(i).first_name);
END LOOP;
END;
48. Using %ROWTYPE in Autonomous Transactions
CREATE OR REPLACE PROCEDURE log_employee (p_id IN employees.employee_id%TYPE) IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = p_id;
INSERT INTO employee_logs (employee_id, log_date) VALUES (v_employee.employee_id, SYSDATE);
COMMIT;
END;
49. Handling %ROWTYPE in PL/SQL Collections
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
v_employees emp_table;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_employees(i).first_name);
END LOOP;
END;
50. Using %ROWTYPE with Data Transformation
DECLARE
v_employee employees%ROWTYPE;
v_transformed_salary NUMBER;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
v_transformed_salary := v_employee.salary * 1.5;
DBMS_OUTPUT.PUT_LINE('Transformed Salary: ' || v_transformed_salary);
END;
51. Using %ROWTYPE with Nested Cursor
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees;
CURSOR dept_cursor IS SELECT * FROM departments;
v_employee emp_cursor%ROWTYPE;
v_department dept_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_employee;
OPEN dept_cursor;
FETCH dept_cursor INTO v_department;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name || ' works in ' || v_department.department_name);
CLOSE emp_cursor;
CLOSE dept_cursor;
END;
52. Using %ROWTYPE for Complex Aggregation
DECLARE
v_employee employees%ROWTYPE;
v_total_salary NUMBER;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
v_total_salary := (SELECT SUM(salary) FROM employees WHERE department_id = v_employee.department_id);
DBMS_OUTPUT.PUT_LINE('Total Salary for Department: ' || v_total_salary);
END;
53. Using %ROWTYPE with Exception Handling
DECLARE
v_employee employees%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found');
END;
END;
54. Using %ROWTYPE in PL/SQL Block
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END;
55. Using %ROWTYPE with Data Insertion
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
INSERT INTO archived_employees VALUES v_employee;
END;
56. Combining %ROWTYPE with Temporary Tables
DECLARE
v_employee employees%ROWTYPE;
BEGIN
CREATE GLOBAL TEMPORARY TABLE temp_employees AS SELECT * FROM employees WHERE 1=0;
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
INSERT INTO temp_employees VALUES v_employee;
END;
57. Handling %ROWTYPE with Cursors and Collections
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees;
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO v_employees;
CLOSE emp_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_employees(i).first_name);
END LOOP;
END;
58. Using %ROWTYPE in PL/SQL Procedures
CREATE OR REPLACE PROCEDURE process_employee_data (p_id IN employees.employee_id%TYPE) IS
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = p_id;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END;
59. Using %ROWTYPE in Nested Cursors
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees;
CURSOR dept_cursor IS SELECT * FROM departments;
v_employee emp_cursor%ROWTYPE;
v_department dept_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_employee;
OPEN dept_cursor;
FETCH dept_cursor INTO v_department;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name || ' belongs to ' || v_department.department_name);
CLOSE emp_cursor;
CLOSE dept_cursor;
END;
60. Using %ROWTYPE for Audit Logging
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
INSERT INTO audit_log (employee_id, action, log_date)
VALUES (v_employee.employee_id, 'Retrieved Employee Data', SYSDATE);
END;
61. Using %ROWTYPE in Conditional Statements
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
IF v_employee.salary > 50000 THEN
DBMS_OUTPUT.PUT_LINE('High Salary');
ELSE
DBMS_OUTPUT.PUT_LINE('Normal Salary');
END IF;
END;
62. Using %ROWTYPE with Exception Propagation
CREATE OR REPLACE PROCEDURE get_employee (p_id IN employees.employee_id%TYPE) IS
v_employee employees%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = p_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
END;
END;
63. Using %ROWTYPE in Complex PL/SQL Blocks
DECLARE
v_employee employees%ROWTYPE;
v_department departments%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
SELECT * INTO v_department FROM departments WHERE department_id = v_employee.department_id;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name || ' works in ' || v_department.department_name);
END;
64. Using %ROWTYPE in Recursive Procedures
CREATE OR REPLACE PROCEDURE recursive_employee_search (
p_id IN employees.employee_id%TYPE) IS
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = p_id;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
-- Recursive call or further processing
END;
65. Using %ROWTYPE with Dynamic Query Execution
DECLARE
v_employee employees%ROWTYPE;
v_sql VARCHAR2(200) := 'SELECT * FROM employees WHERE employee_id = 100';
BEGIN
EXECUTE IMMEDIATE v_sql INTO v_employee;
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END;
66. Using %ROWTYPE for Bulk Data Operations
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FORALL i IN v_employees.FIRST..v_employees.LAST
UPDATE employees SET salary = v_employees(i).salary * 1.1 WHERE employee_id = v_employees(i).employee_id;
END;
67. Using %ROWTYPE for Logging and Auditing
DECLARE
v_employee employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 100;
INSERT INTO audit_log (employee_id, action, log_date)
VALUES (v_employee.employee_id, 'Retrieved Employee Info', SYSDATE);
END;
68. Using %ROWTYPE in Conditional Cursor Processing
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees WHERE salary > 50000;
v_employee emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_employee;
IF emp_cursor%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_employee.first_name);
END IF;
CLOSE emp_cursor;
END;
69. Using %ROWTYPE for Error Handling and Recovery
DECLARE
v_employee employees%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
END;
70. Using %ROWTYPE in a Complex Data Processing Procedure
CREATE OR REPLACE PROCEDURE complex_data_processing (p_id IN employees.employee_id%TYPE) IS
v_employee employees%ROWTYPE;
v_salary NUMBER;
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = p_id;
v_salary := v_employee.salary * 1.1;
DBMS_OUTPUT.PUT_LINE('Processed Salary: ' || v_salary);
END;
No comments:
Post a Comment