Oracle Dynamic SQL & Cursor Guide

1 year ago
Ava Mitchell
2 minutes

In Oracle databases, dynamic SQL and cursor processing can be achieved using PL/SQL. Here is a simple example code demonstrating how to use dynamic SQL and cursor processing.

DECLARE
    sql_stmt VARCHAR2(200);
    emp_id employees.employee_id%TYPE;
    emp_name employees.first_name%TYPE;
    CURSOR emp_cur IS
        SELECT employee_id, first_name
        FROM employees;
BEGIN
    sql_stmt := 'SELECT employee_id, first_name FROM employees';
    
    OPEN emp_cur;
    LOOP
        FETCH emp_cur INTO emp_id, emp_name;
        EXIT WHEN emp_cur%NOTFOUND;
        
        EXECUTE IMMEDIATE sql_stmt INTO emp_id, emp_name;
        
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Employee Name: ' || emp_name);
    END LOOP;
    
    CLOSE emp_cur;
END;
/

In the example above, a variable named sql_stmt for dynamic SQL statement is declared first, and two variables, emp_id and emp_name, are defined to store the query results. Then a cursor named emp_cur is defined to query the data in the employees table.

Within the BEGIN block, assign the dynamic SQL statement to the variable sql_stmt and open the cursor emp_cur. Then iterate through the cursor using a loop, execute the dynamic SQL query using the EXECUTE IMMEDIATE statement, store the results in the variables emp_id and emp_name, and finally output the query results.

It is worth noting that in practical applications, dynamic SQL and cursor processing may be more complex and flexible, allowing developers to adjust and extend based on specific requirements.

bannerAds