Basic Cursor Queries
1. 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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;
/
No comments:
Post a Comment