— 使用动态SQL执行DDL语句
SET SERVEROUTPUT ON;
DECLARE
v_sql VARCHAR2(1000);
v_table_name VARCHAR2(50) :=
‘fgedu_employees_backup’;
BEGIN
— 构建动态SQL语句
v_sql :=
‘CREATE TABLE
‘ || v_table_name || ‘ AS
SELECT
*
FROM fgedu_employees’;

— 执行动态SQL语句
EXECUTE IMMEDIATE
v_sql;

DBMS_OUTPUT.PUT_LINE(‘备份表已创建’);
END
;
/

— 验证备份表
SELECT
*
FROM fgedu_employees_backup;

— 使用动态SQL处理动态表名和列名
SET SERVEROUTPUT ON;
DECLARE
v_sql VARCHAR2(1000);
v_table_name VARCHAR2(50) :=
‘fgedu_employees’;
v_column_name VARCHAR2(50) :=
’emp_salary’;
v_dept VARCHAR2(50) :=
‘市场部’;
v_avg_salary NUMBER;
BEGIN
— 构建动态SQL语句
v_sql :=
‘SELECT
AVG(‘ || v_column_name || ‘)
FROM ‘ || v_table_name || ‘
WHERE emp_dept = :1′;

— 执行动态SQL语句
EXECUTE IMMEDIATE
v_sql IN
TO v_avg_salary USIN
G v_dept;

DBMS_OUTPUT.PUT_LINE(‘市场部平均工资: ‘ || v_avg_salary);
END
;
/

— 使用动态SQL处理动态条件
SET SERVEROUTPUT ON;
DECLARE
v_sql VARCHAR2(1000);
v_where_clause VARCHAR2(500) :=
’emp_dept = ”技术部” AND emp_salary > 5000′;
v_emp_id NUMBER;
v_emp_name VARCHAR2(100);
v_emp_salary NUMBER;
— 定义游标
TYPE
emp_cursor IS
REF CURSOR;
c_emp emp_cursor;
BEGIN
— 构建动态SQL语句
v_sql :=
‘SELECT
emp_id, emp_name, emp_salary
FROM fgedu_employees
WHERE ‘ || v_where_clause;

— 打开游标
OPEN c_emp FOR
v_sql;

— 遍历游标
LOOP
FETCH c_emp IN
TO v_emp_id, v_emp_name, v_emp_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || v_emp_id);
DBMS_OUTPUT.PUT_LINE(‘员工姓名: ‘ || v_emp_name);
DBMS_OUTPUT.PUT_LINE(‘员工工资: ‘ || v_emp_salary);
DBMS_OUTPUT.PUT_LINE(‘—‘);
END LOOP
;

— 关闭游标
CLOSE c_emp;
END
;
/