Basic Cursor Queries
1. Simple Cursor: Retrieve Employee Names
Description: This example covers simple Cursor: Retrieve Employee Names.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/2. Cursor with WHERE Clause: Employees in a Specific Department
Description: This example covers cursor with WHERE Clause: Employees in a Specific Department.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/3. Parameterized Cursor: Fetch Employees from a Specific Department
Description: This example covers parameterized Cursor: Fetch Employees from a Specific Department.
DECLARE
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT first_name, last_name FROM employees WHERE department_id = p_dept_id;
BEGIN
FOR rec IN emp_cursor(10) LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/4. Cursor with Multiple Columns
Description: This example covers cursor with Multiple Columns.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name, salary FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name || ' earns ' || rec.salary);
END LOOP;
END;
/5. Cursor with ORDER BY Clause
Description: This example covers cursor with ORDER BY Clause.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name || ' earns ' || rec.salary);
END LOOP;
END;
/6. Cursor with FOR UPDATE
Description: This example covers cursor with FOR UPDATE.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees WHERE department_id = 10 FOR UPDATE;
BEGIN
FOR rec IN emp_cursor LOOP
UPDATE employees SET salary = salary + 1000 WHERE employee_id = rec.employee_id;
DBMS_OUTPUT.PUT_LINE('Employee ID ' || rec.employee_id || ' updated.');
END LOOP;
END;
/7. Cursor with JOIN
Description: This example covers cursor with JOIN.
DECLARE
CURSOR emp_dept_cursor IS
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
BEGIN
FOR rec IN emp_dept_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name || ' works in ' || rec.department_name);
END LOOP;
END;
/8. Cursor with GROUP BY
Description: This example covers cursor with GROUP BY.
DECLARE
CURSOR dept_salary_cursor IS
SELECT department_id, SUM(salary) AS total_salary
FROM employees GROUP BY department_id;
BEGIN
FOR rec IN dept_salary_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Department ' || rec.department_id || ' total salary: ' || rec.total_salary);
END LOOP;
END;
/9. Cursor with DISTINCT
Description: This example covers cursor with DISTINCT.
DECLARE
CURSOR job_cursor IS
SELECT DISTINCT job_id FROM employees;
BEGIN
FOR rec IN job_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Job ID: ' || rec.job_id);
END LOOP;
END;
/10. Cursor with UNION
Description: This example covers cursor with UNION.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees WHERE department_id = 10
UNION
SELECT first_name, last_name FROM employees WHERE department_id = 20;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/Intermediate Cursor Queries
11. Cursor with MINUS
Description: This example covers cursor with MINUS.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees WHERE department_id = 10
MINUS
SELECT first_name, last_name FROM employees WHERE department_id = 20;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/12. Cursor with CASE Statement
Description: This example covers cursor with CASE Statement.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name,
CASE WHEN salary > 5000 THEN 'High' ELSE 'Low' END AS salary_status
FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name || ' has ' || rec.salary_status || ' salary');
END LOOP;
END;
/13. Cursor with IN Clause
Description: This example covers cursor with IN Clause.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees WHERE department_id IN (10, 20);
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/14. Cursor with BULK COLLECT
Description: This example covers cursor with BULK COLLECT.
DECLARE
TYPE t_emp_names IS TABLE OF employees.first_name%TYPE;
v_names t_emp_names;
BEGIN
SELECT first_name BULK COLLECT INTO v_names FROM employees WHERE department_id = 10;
FOR i IN v_names.FIRST .. v_names.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_names(i));
END LOOP;
END;
/15. Cursor with Dynamic SQL
Description: This example covers cursor with Dynamic SQL.
DECLARE
TYPE t_emp IS TABLE OF employees%ROWTYPE;
v_employees t_emp;
v_query VARCHAR2(1000);
BEGIN
v_query := 'SELECT * FROM employees WHERE department_id = :dept_id';
EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_employees USING 10;
FOR i IN v_employees.FIRST .. v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_employees(i).first_name || ' ' || v_employees(i).last_name);
END LOOP;
END;
/16. Cursor with Analytical Functions
Description: This example covers cursor with Analytical Functions.
DECLARE
CURSOR rank_cursor IS
SELECT first_name, last_name, department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
BEGIN
FOR rec IN rank_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name || ' rank in department ' || rec.department_id || ': ' || rec.salary_rank);
END LOOP;
END;
/17. Cursor with Aggregate Functions
Description: This example covers cursor with Aggregate Functions.
DECLARE
CURSOR emp_avg_salary_cursor IS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees GROUP BY department_id;
BEGIN
FOR rec IN emp_avg_salary_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Department ' || rec.department_id || ' average salary: ' || rec.avg_salary);
END LOOP;
END;
/18. Cursor with EXISTS Clause
Description: This example covers cursor with EXISTS Clause.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/19. Cursor with Hierarchical Query
Description: This example covers cursor with Hierarchical Query.
DECLARE
CURSOR emp_hierarchy_cursor IS
SELECT employee_id, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;
BEGIN
FOR rec IN emp_hierarchy_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || rec.employee_id || ', Manager ID: ' || rec.manager_id || ', Level: ' || rec.LEVEL);
END LOOP;
END;
/20. Cursor with Self Join
Description: This example covers cursor with Self Join.
DECLARE
CURSOR emp_self_join_cursor IS
SELECT e1.first_name || ' ' || e1.last_name AS emp_name,
e2.first_name || ' ' || e2.last_name AS mgr_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;
BEGIN
FOR rec IN emp_self_join_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.emp_name || ', Manager: '
|| rec.mgr_name);
END LOOP;
END;
/Advanced Cursor Queries
21. Cursor with FULL OUTER JOIN
Description: This example covers cursor with FULL OUTER JOIN.
DECLARE
CURSOR emp_dept_cursor IS
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
BEGIN
FOR rec IN emp_dept_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name || ' works in ' || rec.department_name);
END LOOP;
END;
/22. Cursor with Updateable View
Description: This example covers cursor with Updateable View.
CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, salary FROM employees
WHERE department_id = 10 WITH CHECK OPTION;
DECLARE
CURSOR emp_cursor IS
SELECT * FROM emp_view FOR UPDATE;
BEGIN
FOR rec IN emp_cursor LOOP
UPDATE employees SET salary = salary + 500 WHERE employee_id = rec.employee_id;
DBMS_OUTPUT.PUT_LINE('Updated salary for ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/23. Cursor with Materialized View
Description: This example covers cursor with Materialized View.
CREATE MATERIALIZED VIEW emp_dept_mv AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
DECLARE
CURSOR mv_cursor IS
SELECT * FROM emp_dept_mv;
BEGIN
FOR rec IN mv_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name || ' works in ' || rec.department_name);
END LOOP;
END;
/24. Cursor with PL/SQL Table
Description: This example covers cursor with PL/SQL Table.
DECLARE
TYPE emp_table_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
emp_table emp_table_type;
v_index PLS_INTEGER := 0;
BEGIN
FOR rec IN (SELECT * FROM employees WHERE department_id = 10) LOOP
v_index := v_index + 1;
emp_table(v_index) := rec;
END LOOP;
FOR i IN 1..emp_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_table(i).first_name || ' ' || emp_table(i).last_name);
END LOOP;
END;
/25. Cursor with PL/SQL Collection
Description: This example covers cursor with PL/SQL Collection.
DECLARE
TYPE salary_collection IS TABLE OF employees.salary%TYPE;
salaries salary_collection;
BEGIN
SELECT salary BULK COLLECT INTO salaries FROM employees WHERE department_id = 10;
FOR i IN 1..salaries.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Salary: ' || salaries(i));
END LOOP;
END;
/26. Cursor with PL/SQL Records
Description: This example covers cursor with PL/SQL Records.
DECLARE
TYPE emp_record_type IS RECORD (
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
salary employees.salary%TYPE
);
emp_rec emp_record_type;
BEGIN
FOR rec IN (SELECT first_name, last_name, salary FROM employees WHERE department_id = 10) LOOP
emp_rec.first_name := rec.first_name;
emp_rec.last_name := rec.last_name;
emp_rec.salary := rec.salary;
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name || ' earns ' || emp_rec.salary);
END LOOP;
END;
/27. Cursor with Custom PL/SQL Record
Description: This example covers cursor with Custom PL/SQL Record.
DECLARE
TYPE custom_emp_rec IS RECORD (
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
hire_date employees.hire_date%TYPE
);
emp_rec custom_emp_rec;
BEGIN
FOR rec IN (SELECT first_name, last_name, hire_date FROM employees WHERE department_id = 20) LOOP
emp_rec := rec;
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name || ' hired on ' || emp_rec.hire_date);
END LOOP;
END;
/28. Cursor with %ROWTYPE
Description: This example covers cursor with %ROWTYPE.
DECLARE
emp_rec employees%ROWTYPE;
BEGIN
FOR rec IN (SELECT * FROM employees WHERE department_id = 30) LOOP
emp_rec := rec;
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name || ', Salary: ' || emp_rec.salary);
END LOOP;
END;
/29. Cursor with Cursor FOR Loop
Description: This example covers cursor with Cursor FOR Loop.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name, salary FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name || ', Salary: ' || rec.salary);
END LOOP;
END;
/30. Cursor with BULK COLLECT and FORALL
Description: This example covers cursor with BULK COLLECT and FORALL.
DECLARE
TYPE t_salary IS TABLE OF employees.salary%TYPE;
v_salaries t_salary;
BEGIN
SELECT salary BULK COLLECT INTO v_salaries FROM employees WHERE department_id = 10;
FORALL i IN v_salaries.FIRST .. v_salaries.LAST
UPDATE employees SET salary = salary + 100 WHERE salary = v_salaries(i);
DBMS_OUTPUT.PUT_LINE('Salaries updated');
END;
/31. Cursor with INSTEAD OF Trigger
Description: This example covers cursor with INSTEAD OF Trigger.
CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, salary FROM employees;
CREATE TRIGGER emp_view_instead_of_trg
INSTEAD OF UPDATE ON emp_view
FOR EACH ROW
BEGIN
UPDATE employees SET salary = :NEW.salary WHERE employee_id = :OLD.employee_id;
END;
/
DECLARE
CURSOR emp_cursor IS
SELECT * FROM emp_view;
BEGIN
FOR rec IN emp_cursor LOOP
UPDATE emp_view SET salary = rec.salary + 500 WHERE employee_id = rec.employee_id;
END LOOP;
END;
/32. Cursor with Autonomous Transaction
Description: This example covers cursor with Autonomous Transaction.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO emp_log (log_message) VALUES (:1)' USING rec.first_name || ' ' || rec.last_name;
COMMIT;
END;
END LOOP;
END;
/33. Cursor with Compound Trigger
Description: This example covers cursor with Compound Trigger.
CREATE OR REPLACE TRIGGER emp_compound_trg
FOR UPDATE ON employees
COMPOUND TRIGGER
TYPE t_emp_bulk IS TABLE OF employees%ROWTYPE;
v_emp_bulk t_emp_bulk;
BEFORE EACH ROW IS
BEGIN
v_emp_bulk.EXTEND;
v_emp_bulk(v_emp_bulk.COUNT) := :NEW;
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
FORALL i IN v_emp_bulk.FIRST .. v_emp_bulk.LAST
INSERT INTO emp_log (log_message) VALUES ('Updated employee: ' || v_emp_bulk(i).first_name || ' ' || v_emp_bulk(i).last_name);
END AFTER STATEMENT;
END;
/
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees WHERE department_id = 10 FOR UPDATE;
BEGIN
FOR rec IN emp_cursor LOOP
UPDATE employees SET salary = salary + 1000 WHERE employee_id = rec.employee_id;
END LOOP;
END;
/34. Cursor with Conditional Compilation
Description: This example covers cursor with Conditional Compilation.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name, salary FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name || ', Salary: ' || rec.salary);
$ELSE
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
$END
END LOOP;
END;
/35. Cursor with Pipelined Function
Description: This example covers cursor with Pipelined Function.
CREATE OR REPLACE FUNCTION emp_pipeline RETURN employees%ROWTYPE PIPELINED IS
CURSOR emp_cursor IS SELECT * FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
PIPE ROW(rec);
END LOOP;
RETURN;
END;
/
DECLARE
CURSOR emp_cursor IS SELECT * FROM TABLE(emp_pipeline);
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/36. Cursor with Dynamic Partition Pruning
Description: This example covers cursor with Dynamic Partition Pruning.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ dynamic_sampling(emp 2) */ * FROM employees PARTITION (p1);
BEGIN
FOR
rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/37. Cursor with Virtual Columns
Description: This example covers cursor with Virtual Columns.
CREATE TABLE emp_virtual (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
full_name GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL
);
DECLARE
CURSOR emp_cursor IS
SELECT full_name FROM emp_virtual;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Full Name: ' || rec.full_name);
END LOOP;
END;
/38. Cursor with Flashback Query
Description: This example covers cursor with Flashback Query.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/39. Cursor with Result Cache
Description: This example covers cursor with Result Cache.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ result_cache */ first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/40. Cursor with Index Hint
Description: This example covers cursor with Index Hint.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ index(employees emp_dept_idx) */ first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/Data Handling and ETL Cursor Queries
41. Cursor for Data Migration
Description: This example covers cursor for Data Migration.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
INSERT INTO employees_backup VALUES rec;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Data migration completed');
END;
/42. Cursor for Data Transformation
Description: This example covers cursor for Data Transformation.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name, salary FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
INSERT INTO transformed_employees (full_name, salary)
VALUES (rec.first_name || ' ' || rec.last_name, rec.salary * 1.10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Data transformation completed');
END;
/43. Cursor for Data Cleansing
Description: This example covers cursor for Data Cleansing.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees WHERE salary < 0;
BEGIN
FOR rec IN emp_cursor LOOP
UPDATE employees SET salary = 0 WHERE employee_id = rec.employee_id;
DBMS_OUTPUT.PUT_LINE('Cleansed employee ID ' || rec.employee_id);
END LOOP;
END;
/44. Cursor for Data Aggregation
Description: This example covers cursor for Data Aggregation.
DECLARE
CURSOR dept_salary_cursor IS
SELECT department_id, SUM(salary) AS total_salary
FROM employees GROUP BY department_id;
BEGIN
FOR rec IN dept_salary_cursor LOOP
INSERT INTO dept_salary_agg (department_id, total_salary)
VALUES (rec.department_id, rec.total_salary);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Data aggregation completed');
END;
/45. Cursor for Data Deduplication
Description: This example covers cursor for Data Deduplication.
DECLARE
CURSOR dup_cursor IS
SELECT first_name, last_name, COUNT(*)
FROM employees GROUP BY first_name, last_name HAVING COUNT(*) > 1;
BEGIN
FOR rec IN dup_cursor LOOP
DELETE FROM employees WHERE ROWID IN (
SELECT ROWID FROM employees
WHERE first_name = rec.first_name
AND last_name = rec.last_name
AND ROWNUM > 1);
DBMS_OUTPUT.PUT_LINE('Duplicates removed for ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/46. Cursor for Data Archiving
Description: This example covers cursor for Data Archiving.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees WHERE hire_date < ADD_MONTHS(SYSDATE, -60);
BEGIN
FOR rec IN emp_cursor LOOP
INSERT INTO archived_employees VALUES rec;
DELETE FROM employees WHERE employee_id = rec.employee_id;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Data archiving completed');
END;
/47. Cursor for Data Purging
Description: This example covers cursor for Data Purging.
DECLARE
CURSOR purge_cursor IS
SELECT employee_id FROM employees WHERE termination_date < ADD_MONTHS(SYSDATE, -12);
BEGIN
FOR rec IN purge_cursor LOOP
DELETE FROM employees WHERE employee_id = rec.employee_id;
DBMS_OUTPUT.PUT_LINE('Purged employee ID ' || rec.employee_id);
END LOOP;
END;
/48. Cursor for ETL Process
Description: This example covers cursor for ETL Process.
DECLARE
CURSOR etl_cursor IS
SELECT * FROM staging_employees;
BEGIN
FOR rec IN etl_cursor LOOP
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (rec.employee_id, rec.first_name, rec.last_name, rec.salary);
DELETE FROM staging_employees WHERE employee_id = rec.employee_id;
END LOOP;
DBMS_OUTPUT.PUT_LINE('ETL process completed');
END;
/49. Cursor for Data Validation
Description: This example covers cursor for Data Validation.
DECLARE
CURSOR validation_cursor IS
SELECT employee_id, salary FROM employees WHERE salary < 3000;
BEGIN
FOR rec IN validation_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID ' || rec.employee_id || ' has a low salary: ' || rec.salary);
END LOOP;
END;
/50. Cursor for Data Reconciliation
Description: This example covers cursor for Data Reconciliation.
DECLARE
CURSOR emp_cursor IS
SELECT e.employee_id, e.first_name, e.last_name, s.salary
FROM employees e
LEFT JOIN salary_table s ON e.employee_id = s.employee_id
WHERE e.salary != s.salary;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Reconciliation needed for Employee ID ' || rec.employee_id);
END LOOP;
END;
/Security and Auditing Cursor Queries
51. Cursor with Data Masking
Description: This example covers cursor with Data Masking.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || rec.employee_id || ', Name: ' || DBMS_CRYPTO.HASH(SYS.UTL_I18N.STRING_TO_RAW(rec.first_name || rec.last_name, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH1));
END LOOP;
END;
/52. Cursor with Audit Trail
Description: This example covers cursor with Audit Trail.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees WHERE salary > 5000;
BEGIN
FOR rec IN emp_cursor LOOP
INSERT INTO audit_log (log_message) VALUES ('Checked salary for ' || rec.first_name || ' ' || rec.last_name);
DBMS_OUTPUT.PUT_LINE('Logged audit for ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/53. Cursor with Fine-Grained Auditing
Description: This example covers cursor with Fine-Grained Auditing.
BEGIN
DBMS_FGA.ADD_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'SALARY_AUDIT',
audit_column => 'SALARY',
audit_condition => 'SALARY > 5000',
audit_trail => DBMS_FGA.DB_EXTENDED);
END;
/
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name, salary FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name || ', Salary: ' || rec.salary);
END LOOP;
END;
/54. Cursor with Virtual Private Database
Description: This example covers cursor with Virtual Private Database.
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'VPD_POLICY',
function_schema => 'HR',
policy_function => 'hr.vpd_function');
END;
/
CREATE OR REPLACE FUNCTION hr.vpd_function RETURN VARCHAR2 IS
BEGIN
RETURN 'department_id = 10';
END;
/
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP
;
END;
/55. Cursor with Data Redaction
Description: This example covers cursor with Data Redaction.
BEGIN
DBMS_REDACT.ADD_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'REDACT_POLICY',
column_name => 'SALARY',
expression => '1=1',
action => DBMS_REDACT.FULL);
END;
/
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name, salary FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name || ', Salary: ' || rec.salary);
END LOOP;
END;
/56. Cursor with SecureFile LOBs
Description: This example covers cursor with SecureFile LOBs.
DECLARE
CURSOR lob_cursor IS
SELECT file_id, file_data FROM files WHERE file_type = 'SECURE';
BEGIN
FOR rec IN lob_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Processing file ID ' || rec.file_id);
END LOOP;
END;
/57. Cursor with Data Encryption
Description: This example covers cursor with Data Encryption.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
UPDATE employees SET last_name = DBMS_CRYPTO.ENCRYPT(UTL_RAW.CAST_TO_RAW(rec.last_name), DBMS_CRYPTO.DES_CBC_PKCS5, '12345678') WHERE employee_id = rec.employee_id;
DBMS_OUTPUT.PUT_LINE('Encrypted last name for Employee ID ' || rec.employee_id);
END LOOP;
END;
/58. Cursor with Access Control
Description: This example covers cursor with Access Control.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Accessing record for: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/59. Cursor with Role-Based Access Control
Description: This example covers cursor with Role-Based Access Control.
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'RBAC_POLICY',
function_schema => 'HR',
policy_function => 'hr.rbac_function');
END;
/
CREATE OR REPLACE FUNCTION hr.rbac_function RETURN VARCHAR2 IS
BEGIN
IF DBMS_SESSION.IS_ROLE_ENABLED('HR_MANAGER') THEN
RETURN '1=1';
ELSE
RETURN '1=0';
END IF;
END;
/
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Accessing record for: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/60. Cursor with Label Security
Description: This example covers cursor with Label Security.
BEGIN
DBMS_LABEL_SECURITY.CREATE_POLICY('HR_LABEL_POLICY', TRUE);
DBMS_LABEL_SECURITY.ADD_POLICY_COLUMN('HR_LABEL_POLICY', 'HR', 'EMPLOYEES', 'SENSITIVE_DATA');
DBMS_LABEL_SECURITY.APPLY_LABEL('HR_LABEL_POLICY', 'HR_EMPLOYEE_LABEL', 'HR', 'EMPLOYEES', 10);
END;
/
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees WHERE SENSITIVE_DATA = 'HR_EMPLOYEE_LABEL';
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Accessing record for: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/61. Cursor with Flashback Data Archive
Description: This example covers cursor with Flashback Data Archive.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' HOUR);
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/62. Cursor with Data Vault
Description: This example covers cursor with Data Vault.
BEGIN
DBMS_MACADM.CREATE_REALM('HR_REALM', 'HR Realm', 'HR', 'EMPLOYEES', DBMS_MACUTL.G_OBJECT_TYPE_TABLE);
DBMS_MACADM.ADD_AUTH_TO_REALM('HR_REALM', 'HR Manager Auth', 'HR_MANAGER', DBMS_MACADM.G_REALM_AUTH);
END;
/
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Accessing record for: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/63. Cursor with Asynchronous Processing
Description: This example covers cursor with Asynchronous Processing.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'ASYNC_EMP_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN FOR rec IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(rec.first_name || '' '' || rec.last_name); END LOOP; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=1',
enabled => TRUE);
DBMS_OUTPUT.PUT_LINE('Asynchronous processing job created');
END;
/64. Cursor with Parallel Execution
Description: This example covers cursor with Parallel Execution.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ PARALLEL(employees, 4) */ first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/65. Cursor with Data Compression
Description: This example covers cursor with Data Compression.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
UPDATE employees SET salary = rec.salary * 1.05 WHERE employee_id = rec.employee_id COMPRESS;
DBMS_OUTPUT.PUT_LINE('Compressed and updated salary for employee ID ' || rec.employee_id);
END LOOP;
END;
/66. Cursor with Data Replication
Description: This example covers cursor with Data Replication.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
INSERT INTO employees_replica VALUES rec;
DBMS_OUTPUT.PUT_LINE('Replicated employee ID ' || rec.employee_id);
END LOOP;
END;
/67. Cursor with Data Synchronization
Description: This example covers cursor with Data Synchronization.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees WHERE last_update > SYSDATE - 1;
BEGIN
FOR rec IN emp_cursor LOOP
MERGE INTO employees_sync s USING DUAL ON (s.employee_id = rec.employee_id)
WHEN MATCHED THEN UPDATE SET s.salary = rec.salary, s.last_update = rec.last_update
WHEN NOT MATCHED THEN INSERT VALUES rec;
DBMS_OUTPUT.PUT_LINE('Synchronized employee ID ' || rec.employee_id);
END LOOP;
END;
/68. Cursor with Database Links
Description: This example covers cursor with Database Links.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees@remote_db;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/69. Cursor with Data Guard
Description: This example covers cursor with Data Guard.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees@standby_db;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/70. Cursor with Database Clustering
Description: This example covers cursor with Database Clustering.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ CLUSTER(employees_cluster) */ * FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/Miscellaneous Cursor Queries
71. Cursor with Distributed Transactions
Description: This example covers cursor with Distributed Transactions.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees@remote_db;
BEGIN
FOR rec IN emp_cursor LOOP
INSERT INTO employees_local VALUES rec;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Distributed transaction completed for employee ID ' || rec.employee_id);
END LOOP;
END;
/72. Cursor with Result Set Caching
Description: This example covers cursor with Result Set Caching.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ RESULT_CACHE */ first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT
.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/73. Cursor with Dynamic Partition Pruning
Description: This example covers cursor with Dynamic Partition Pruning.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees PARTITION (p1);
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/74. Cursor with Versioning
Description: This example covers cursor with Versioning.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/75. Cursor with INMEMORY Clause
Description: This example covers cursor with INMEMORY Clause.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ INMEMORY */ first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/76. Cursor with SQL Plan Management
Description: This example covers cursor with SQL Plan Management.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ USE_PLAN BASELINE 'baseline_name' */ first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/77. Cursor with Automatic Workload Repository
Description: This example covers cursor with Automatic Workload Repository.
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees WHERE employee_id = (SELECT MAX(employee_id) FROM employees);
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/78. Cursor with Real Application Testing
Description: This example covers cursor with Real Application Testing.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/79. Cursor with SQL Tuning Advisor
Description: This example covers cursor with SQL Tuning Advisor.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('19.1.0') */ first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/80. Cursor with SQL Profile
Description: This example covers cursor with SQL Profile.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ USE_PROFILE */ first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/81. Cursor with Real-Time Statistics
Description: This example covers cursor with Real-Time Statistics.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ GATHER_PLAN_STATISTICS */ first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/82. Cursor with Time Travel
Description: This example covers cursor with Time Travel.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY);
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/83. Cursor with Multiple Columns in FOR UPDATE
Description: This example covers cursor with Multiple Columns in FOR UPDATE.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 10 FOR UPDATE;
BEGIN
FOR rec IN emp_cursor LOOP
UPDATE employees SET salary = salary + 1000 WHERE employee_id = rec.employee_id;
DBMS_OUTPUT.PUT_LINE('Updated salary for ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/84. Cursor with Optimizer Hint
Description: This example covers cursor with Optimizer Hint.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ FIRST_ROWS */ first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/85. Cursor with Analytical Function
Description: This example covers cursor with Analytical Function.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name || ' is ranked ' || rec.salary_rank || ' in salary');
END LOOP;
END;
/86. Cursor with Nested Loops
Description: This example covers cursor with Nested Loops.
DECLARE
CURSOR dept_cursor IS SELECT department_id FROM departments;
CURSOR emp_cursor IS SELECT first_name, last_name FROM employees WHERE department_id = dept_cursor.department_id;
BEGIN
FOR dept_rec IN dept_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Department ID: ' || dept_rec.department_id);
FOR emp_rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(' Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
END LOOP;
END LOOP;
END;
/87. Cursor with SYS_REFCURSOR
Description: This example covers cursor with SYS_REFCURSOR.
DECLARE
TYPE emp_refcursor IS REF CURSOR;
emp_cursor emp_refcursor;
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_cursor FOR SELECT * FROM employees WHERE department_id = 10;
LOOP
FETCH emp_cursor INTO emp_rec;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
END LOOP;
CLOSE emp_cursor;
END;
/88. Cursor with Parallel Hint
Description: This example covers cursor with Parallel Hint.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ PARALLEL(employees, 4) */ first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/89. Cursor with Compound Query
Description: This example covers cursor with Compound Query.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees WHERE department_id = 10
UNION
SELECT first_name, last_name FROM employees WHERE department_id = 20;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/90. Cursor with RETURNING Clause
Description: This example covers cursor with RETURNING Clause.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
UPDATE employees SET salary = salary + 1000 WHERE employee_id = rec.employee_id
RETURNING employee_id, first_name, last_name INTO rec.employee_id, rec.first_name, rec.last_name;
DBMS_OUTPUT.PUT_LINE('Updated salary for Employee ID ' || rec.employee_id);
END LOOP;
END;
/91. Cursor with Row Locking
Description: This example covers cursor with Row Locking.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10 FOR UPDATE;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Locked Employee ID ' || rec.employee_id);
END LOOP;
END;
/92. Cursor with Read Consistency
Description: This example covers cursor with Read Consistency.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Read Consistent Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/93. Cursor with SERIALIZABLE Isolation Level
Description: This example covers cursor with SERIALIZABLE Isolation Level.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
COMMIT;
/94. Cursor with Snapshot Isolation
Description: This example covers cursor with Snapshot Isolation.
SET TRANSACTION ISOLATION
LEVEL READ ONLY;
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
COMMIT;
/95. Cursor with Flashback Transaction Query
Description: This example covers cursor with Flashback Transaction Query.
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' HOUR);
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/96. Cursor with Table Partitioning
Description: This example covers cursor with Table Partitioning.
DECLARE
CURSOR emp_cursor IS
SELECT /*+ FULL(employees) */ * FROM employees PARTITION (P1);
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/97. Cursor with Function Result Cache
Description: This example covers cursor with Function Result Cache.
CREATE OR REPLACE FUNCTION get_emp_name(p_emp_id NUMBER) RETURN VARCHAR2 RESULT_CACHE IS
v_name VARCHAR2(100);
BEGIN
SELECT first_name || ' ' || last_name INTO v_name FROM employees WHERE employee_id = p_emp_id;
RETURN v_name;
END;
/
DECLARE
CURSOR emp_cursor IS
SELECT employee_id FROM employees WHERE department_id = 10;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || get_emp_name(rec.employee_id));
END LOOP;
END;
/98. Cursor with Materialized View Refresh
Description: This example covers cursor with Materialized View Refresh.
DECLARE
CURSOR mv_cursor IS
SELECT first_name, last_name FROM employees_mv;
BEGIN
FOR rec IN mv_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/
EXEC DBMS_MVIEW.REFRESH('employees_mv', 'C');99. Cursor with XML Data
Description: This example covers cursor with XML Data.
DECLARE
CURSOR emp_cursor IS
SELECT XMLTYPE('<Employee><FirstName>' || first_name || '</FirstName><LastName>' || last_name || '</LastName></Employee>') AS emp_xml FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_xml.EXTRACT('//FirstName').getStringVal());
DBMS_OUTPUT.PUT_LINE(rec.emp_xml.EXTRACT('//LastName').getStringVal());
END LOOP;
END;
/100. Cursor with JSON Data
Description: This example covers cursor with JSON Data.
DECLARE
CURSOR emp_cursor IS
SELECT JSON_OBJECT('FirstName' VALUE first_name, 'LastName' VALUE last_name) AS emp_json FROM employees;
BEGIN
FOR rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('JSON: ' || rec.emp_json);
END LOOP;
END;
/Note: Some examples are illustrative and may need small adjustments before use in a real Oracle environment.
No comments:
Post a Comment