Oracle 数据库高级特性深度解析PL/SQL 高级开发完全指南5. 如何使用 Oracle 的 PL/SQL 进行开发5.1 PL/SQL 语言体系架构PL/SQL(Procedural Language extensions to SQL)是 Oracle 对标准 SQL 的过程化扩展它将 SQL 的数据操作能力与过程化语言的编程结构完美融合形成了一门功能强大的数据库编程语言。PL/SQL 核心架构层次PL/SQL引擎架构PL/SQL 程序单元程序结构匿名块Anonymous Block命名块存储过程Procedure函数Function包Package触发器Trigger类型Type执行引擎过程语句执行器Procedural Statement ExecutorSQL语句执行器SQL Statement Executor变量赋值/条件判断循环控制/异常处理SQL解析与执行Oracle 内核PL/SQL 与其他语言对比特性| 特性 | PL/SQL | Java/JDBC | Python ||------|--------|-----------|--------|| 数据访问效率 | 极高引擎内执行 | 中网络往返 | 中网络往返 || 事务控制 | 原生支持 | 需显式管理 | 需显式管理 || 代码位置 | 数据库服务器端 | 应用服务器端 | 应用服务器端 || 可移植性 | 仅Oracle | 跨平台 | 跨平台 || 学习曲线 | 中等 | 较高 | 较低 || 适合场景 | 数据密集型逻辑 | 业务逻辑处理 | 数据分析处理 |5.2 匿名块与基础语法5.2.1 PL/SQL 块结构一个完整的 PL/SQL 块由三部分组成声明部分、执行部分和异常处理部分。-- PL/SQL 块的基本结构 DECLARE -- 声明部分定义变量、常量、游标、类型 v_variable VARCHAR2(100); v_constant CONSTANT NUMBER : 100; v_date DATE : SYSDATE; BEGIN -- 执行部分核心业务逻辑必须存在 v_variable : Hello PL/SQL; DBMS_OUTPUT.PUT_LINE(v_variable); DBMS_OUTPUT.PUT_LINE(Current Date: || TO_CHAR(v_date, YYYY-MM-DD)); EXCEPTION -- 异常处理部分捕获和处理错误 WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(Error: || SQLERRM); RAISE; END; /5.2.2 变量与数据类型DECLARE -- 标量类型 v_number NUMBER(10,2) : 100.50; v_integer INTEGER : 100; v_varchar VARCHAR2(200) : Oracle Database; v_date DATE : SYSDATE; v_boolean BOOLEAN : TRUE; v_clob CLOB; -- 引用类型锚定数据类型 v_emp_name employees.first_name%TYPE; v_emp_row employees%ROWTYPE; -- 记录类型自定义复合类型 TYPE t_employee IS RECORD ( emp_id employees.employee_id%TYPE, emp_name VARCHAR2(100), salary employees.salary%TYPE, hire_date DATE ); v_emp_record t_employee; -- 集合类型 TYPE t_number_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER; v_numbers t_number_list; TYPE t_emp_list IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER; v_employees t_emp_list; BEGIN -- 使用 %TYPE 和 %ROWTYPE SELECT first_name INTO v_emp_name FROM employees WHERE employee_id 100; SELECT * INTO v_emp_row FROM employees WHERE employee_id 100; -- 使用记录类型 SELECT employee_id, first_name || || last_name, salary, hire_date INTO v_emp_record FROM employees WHERE employee_id 100; DBMS_OUTPUT.PUT_LINE(Employee: || v_emp_record.emp_name); DBMS_OUTPUT.PUT_LINE(Salary: $ || v_emp_record.salary); -- 使用集合类型 v_numbers(1) : 100; v_numbers(2) : 200; v_numbers(3) : 300; DBMS_OUTPUT.PUT_LINE(Sum: || (v_numbers(1) v_numbers(2) v_numbers(3))); END; /5.2.3 控制流语句DECLARE v_score NUMBER : 85; v_grade VARCHAR2(10); v_count NUMBER : 1; BEGIN -- IF-THEN-ELSIF-ELSE 条件语句 IF v_score 90 THEN v_grade : A; ELSIF v_score 80 THEN v_grade : B; ELSIF v_score 70 THEN v_grade : C; ELSIF v_score 60 THEN v_grade : D; ELSE v_grade : F; END IF; DBMS_OUTPUT.PUT_LINE(Grade: || v_grade); -- CASE 表达式 v_grade : CASE WHEN v_score 90 THEN A WHEN v_score 80 THEN B WHEN v_score 70 THEN C WHEN v_score 60 THEN D ELSE F END; -- 基本 LOOP 循环 v_count : 1; LOOP DBMS_OUTPUT.PUT_LINE(LOOP iteration: || v_count); v_count : v_count 1; EXIT WHEN v_count 5; END LOOP; -- WHILE 循环 v_count : 1; WHILE v_count 5 LOOP DBMS_OUTPUT.PUT_LINE(WHILE iteration: || v_count); v_count : v_count 1; END LOOP; -- FOR 循环 FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE(FOR iteration: || i); END LOOP; -- FOR REVERSE 循环 FOR i IN REVERSE 1..5 LOOP DBMS_OUTPUT.PUT_LINE(REVERSE iteration: || i); END LOOP; -- CONTINUE 和 GOTO FOR i IN 1..10 LOOP IF MOD(i, 2) 0 THEN CONTINUE; -- 跳过偶数 END IF; DBMS_OUTPUT.PUT_LINE(Odd number: || i); END LOOP; END; /5.3 存储过程与函数5.3.1 存储过程开发-- 基础存储过程 CREATE OR REPLACE PROCEDURE update_employee_salary( p_employee_id IN employees.employee_id%TYPE, p_new_salary IN employees.salary%TYPE, p_update_reason IN VARCHAR2 DEFAULT NULL, p_old_salary OUT employees.salary%TYPE, p_status OUT VARCHAR2 ) IS v_current_salary employees.salary%TYPE; v_max_salary employees.salary%TYPE; BEGIN -- 初始化输出参数 p_status : SUCCESS; -- 获取当前薪资 SELECT salary INTO v_current_salary FROM employees WHERE employee_id p_employee_id FOR UPDATE; p_old_salary : v_current_salary; -- 验证薪资范围 SELECT MAX(salary) INTO v_max_salary FROM employees; IF p_new_salary v_max_salary * 1.5 THEN RAISE_APPLICATION_ERROR(-20001, New salary exceeds maximum allowed: || v_max_salary * 1.5); END IF; -- 更新薪资 UPDATE employees SET salary p_new_salary, last_update_date SYSDATE WHERE employee_id p_employee_id; -- 记录审计日志 INSERT INTO salary_audit_log ( employee_id, old_salary, new_salary, change_reason, change_date ) VALUES ( p_employee_id, v_current_salary, p_new_salary, p_update_reason, SYSDATE ); COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN p_status : FAILED; RAISE_APPLICATION_ERROR(-20002, Employee not found: || p_employee_id); WHEN OTHERS THEN p_status : FAILED; ROLLBACK; RAISE; END update_employee_salary; / -- 调用存储过程 DECLARE v_old_salary NUMBER; v_status VARCHAR2(20); BEGIN update_employee_salary( p_employee_id 100, p_new_salary 15000, p_update_reason Annual performance increase, p_old_salary v_old_salary, p_status v_status ); DBMS_OUTPUT.PUT_LINE(Status: || v_status); DBMS_OUTPUT.PUT_LINE(Old Salary: $ || v_old_salary); END; /5.3.2 函数开发-- 标量函数 CREATE OR REPLACE FUNCTION calculate_bonus( p_employee_id IN employees.employee_id%TYPE, p_performance IN VARCHAR2 DEFAULT B ) RETURN NUMBER IS v_salary employees.salary%TYPE; v_hire_years NUMBER; v_bonus_rate NUMBER : 0; v_bonus NUMBER; BEGIN -- 获取员工信息 SELECT salary, TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) INTO v_salary, v_hire_years FROM employees WHERE employee_id p_employee_id; -- 根据绩效等级确定基础奖金比例 v_bonus_rate : CASE UPPER(p_performance) WHEN A THEN 0.20 WHEN B THEN 0.15 WHEN C THEN 0.10 WHEN D THEN 0.05 ELSE 0 END; -- 根据工龄增加额外比例 IF v_hire_years 10 THEN v_bonus_rate : v_bonus_rate 0.05; ELSIF v_hire_years 5 THEN v_bonus_rate : v_bonus_rate 0.03; END IF; -- 计算奖金 v_bonus : ROUND(v_salary * v_bonus_rate, 2); RETURN v_bonus; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN -1; WHEN OTHERS THEN RETURN -2; END calculate_bonus; / -- 管道函数返回多行数据 CREATE OR REPLACE FUNCTION get_top_employees( p_department_id IN NUMBER, p_top_n IN NUMBER DEFAULT 10 ) RETURN SYS_REFCURSOR IS v_cursor SYS_REFCURSOR; BEGIN OPEN v_cursor FOR SELECT employee_id, first_name || || last_name AS full_name, salary, hire_date FROM employees WHERE department_id p_department_id ORDER BY salary DESC FETCH FIRST p_top_n ROWS ONLY; RETURN v_cursor; END get_top_employees; / -- 调用函数 -- 标量函数调用 SELECT employee_id, first_name, salary, calculate_bonus(employee_id, A) AS bonus FROM employees WHERE department_id 30; -- 管道函数调用 SELECT get_top_employees(30, 5) FROM dual; -- 在PL/SQL中调用 DECLARE v_bonus NUMBER; v_cursor SYS_REFCURSOR; v_emp_record employees%ROWTYPE; BEGIN v_bonus : calculate_bonus(100, A); DBMS_OUTPUT.PUT_LINE(Bonus: $ || v_bonus); v_cursor : get_top_employees(30, 3); LOOP FETCH v_cursor INTO v_emp_record; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || : || v_emp_record.first_name || - $ || v_emp_record.salary); END LOOP; CLOSE v_cursor; END; /5.4 包(Package)的完整实现包是 PL/SQL 中最重要的代码组织单元它将相关的类型、变量、过程、函数封装在一起。5.4.1 包规范定义-- 创建包规范 CREATE OR REPLACE PACKAGE employee_mgmt AS -- 公共常量 gc_max_salary_increase CONSTANT NUMBER : 0.50; gc_company_name CONSTANT VARCHAR2(50) : Oracle Corp; -- 公共类型定义 TYPE t_employee_record IS RECORD ( emp_id NUMBER, emp_name VARCHAR2(200), job_title VARCHAR2(50), salary NUMBER(10,2), hire_date DATE, years_of_service NUMBER ); TYPE t_employee_table IS TABLE OF t_employee_record INDEX BY PLS_INTEGER; TYPE t_salary_range IS RECORD ( min_salary NUMBER, max_salary NUMBER, avg_salary NUMBER, median_salary NUMBER ); -- 公共异常定义 e_employee_not_found EXCEPTION; e_invalid_salary EXCEPTION; e_exceed_max_increase EXCEPTION; PRAGMA EXCEPTION_INIT(e_employee_not_found, -20001); PRAGMA EXCEPTION_INIT(e_invalid_salary, -20002); PRAGMA EXCEPTION_INIT(e_exceed_max_increase, -20003); -- 公共过程和函数声明 PROCEDURE hire_employee( p_first_name IN VARCHAR2, p_last_name IN VARCHAR2, p_email IN VARCHAR2, p_job_id IN VARCHAR2, p_salary IN NUMBER, p_department_id IN NUMBER, p_employee_id OUT NUMBER ); PROCEDURE terminate_employee( p_employee_id IN NUMBER, p_termination_date IN DATE DEFAULT SYSDATE, p_reason IN VARCHAR2 DEFAULT NULL ); PROCEDURE batch_salary_increase( p_department_id IN NUMBER, p_increase_pct IN NUMBER, p_updated_count OUT NUMBER ); FUNCTION get_employee_detail( p_employee_id IN NUMBER ) RETURN t_employee_record; FUNCTION get_department_salary_stats( p_department_id IN NUMBER ) RETURN t_salary_range; FUNCTION search_employees( p_search_term IN VARCHAR2 ) RETURN SYS_REFCURSOR; -- 公共变量会话级别 g_last_error_message VARCHAR2(4000); g_last_operation VARCHAR2(100); END employee_mgmt; /5.4.2 包体实现-- 创建包体 CREATE OR REPLACE PACKAGE BODY employee_mgmt AS -- 私有常量 pc_max_name_length CONSTANT NUMBER : 100; -- 私有函数生成员工邮箱 FUNCTION generate_email( p_first_name VARCHAR2, p_last_name VARCHAR2 ) RETURN VARCHAR2 IS v_email VARCHAR2(100); v_suffix NUMBER : 1; BEGIN v_email : UPPER(SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7)); -- 检查邮箱是否已存在 LOOP BEGIN SELECT X INTO v_email FROM employees WHERE email v_email; v_suffix : v_suffix 1; v_email : UPPER(SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || v_suffix); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; END LOOP; RETURN v_email; END generate_email; -- 私有过程验证薪资范围 PROCEDURE validate_salary( p_job_id VARCHAR2, p_salary NUMBER ) IS v_min_salary NUMBER; v_max_salary NUMBER; BEGIN SELECT min_salary, max_salary INTO v_min_salary, v_max_salary FROM jobs WHERE job_id p_job_id; IF p_salary v_min_salary OR p_salary v_max_salary THEN g_last_error_message : Salary || p_salary || is out of range [ || v_min_salary || , || v_max_salary || ]; RAISE e_invalid_salary; END IF; END validate_salary; -- 实现雇佣员工 PROCEDURE hire_employee( p_first_name IN VARCHAR2, p_last_name IN VARCHAR2, p_email IN VARCHAR2, p_job_id IN VARCHAR2, p_salary IN NUMBER, p_department_id IN NUMBER, p_employee_id OUT NUMBER ) IS v_email VARCHAR2(100); BEGIN g_last_operation : HIRE_EMPLOYEE; -- 验证薪资 validate_salary(p_job_id, p_salary); -- 生成邮箱如果未提供 IF p_email IS NULL THEN v_email : generate_email(p_first_name, p_last_name); ELSE v_email : p_email; END IF; -- 生成员工ID SELECT employees_seq.NEXTVAL INTO p_employee_id FROM dual; -- 插入新员工 INSERT INTO employees ( employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id ) VALUES ( p_employee_id, p_first_name, p_last_name, v_email, SYSDATE, p_job_id, p_salary, p_department_id ); COMMIT; DBMS_OUTPUT.PUT_LINE(Employee hired: || p_employee_id || - || v_email); EXCEPTION WHEN OTHERS THEN g_last_error_message : SQLERRM; ROLLBACK; RAISE; END hire_employee; -- 实现解雇员工 PROCEDURE terminate_employee( p_employee_id IN NUMBER, p_termination_date IN DATE DEFAULT SYSDATE, p_reason IN VARCHAR2 DEFAULT NULL ) IS v_emp_record t_employee_record; BEGIN g_last_operation : TERMINATE_EMPLOYEE; -- 获取员工信息用于审计 v_emp_record : get_employee_detail(p_employee_id); -- 记录到离职表 INSERT INTO employee_terminations ( employee_id, first_name, last_name, job_title, termination_date, reason ) VALUES ( p_employee_id, SUBSTR(v_emp_record.emp_name, 1, INSTR(v_emp_record.emp_name, ) - 1), SUBSTR(v_emp_record.emp_name, INSTR(v_emp_record.emp_name, ) 1), v_emp_record.job_title, p_termination_date, p_reason ); -- 删除员工记录 DELETE FROM employees WHERE employee_id p_employee_id; COMMIT; DBMS_OUTPUT.PUT_LINE(Employee terminated: || p_employee_id); EXCEPTION WHEN NO_DATA_FOUND THEN g_last_error_message : Employee not found: || p_employee_id; RAISE e_employee_not_found; WHEN OTHERS THEN g_last_error_message : SQLERRM; ROLLBACK; RAISE; END terminate_employee; -- 实现批量调薪 PROCEDURE batch_salary_increase( p_department_id IN NUMBER, p_increase_pct IN NUMBER, p_updated_count OUT NUMBER ) IS v_max_increase NUMBER : gc_max_salary_increase * 100; BEGIN g_last_operation : BATCH_SALARY_INCREASE; p_updated_count : 0; -- 验证增长比例 IF p_increase_pct v_max_increase THEN g_last_error_message : Increase percentage || p_increase_pct || % exceeds maximum || v_max_increase || %; RAISE e_exceed_max_increase; END IF; -- 批量更新 UPDATE employees SET salary salary * (1 p_increase_pct / 100), last_update_date SYSDATE WHERE department_id p_department_id; p_updated_count : SQL%ROWCOUNT; COMMIT; DBMS_OUTPUT.PUT_LINE(Updated || p_updated_count || employees); EXCEPTION WHEN OTHERS THEN g_last_error_message : SQLERRM; ROLLBACK; RAISE; END batch_salary_increase; -- 实现获取员工详情 FUNCTION get_employee_detail( p_employee_id IN NUMBER ) RETURN t_employee_record IS v_record t_employee_record; BEGIN SELECT e.employee_id, e.first_name || || e.last_name, j.job_title, e.salary, e.hire_date, TRUNC(MONTHS_BETWEEN(SYSDATE, e.hire_date) / 12) INTO v_record FROM employees e JOIN jobs j ON e.job_id j.job_id WHERE e.employee_id p_employee_id; RETURN v_record; EXCEPTION WHEN NO_DATA_FOUND THEN g_last_error_message : Employee not found: || p_employee_id; RAISE e_employee_not_found; END get_employee_detail; -- 实现获取部门薪资统计 FUNCTION get_department_salary_stats( p_department_id IN NUMBER ) RETURN t_salary_range IS v_range t_salary_range; BEGIN SELECT MIN(salary), MAX(salary), AVG(salary), MEDIAN(salary) INTO v_range FROM employees WHERE department_id p_department_id; RETURN v_range; END get_department_salary_stats; -- 实现搜索员工 FUNCTION search_employees( p_search_term IN VARCHAR2 ) RETURN SYS_REFCURSOR IS v_cursor SYS_REFCURSOR; BEGIN OPEN v_cursor FOR SELECT employee_id, first_name, last_name, email, salary FROM employees WHERE UPPER(first_name || || last_name) LIKE % || UPPER(p_search_term) || % OR UPPER(email) LIKE % || UPPER(p_search_term) || % ORDER BY employee_id; RETURN v_cursor; END search_employees; END employee_mgmt; / -- 使用包 DECLARE v_emp_id NUMBER; v_count NUMBER; v_record employee_mgmt.t_employee_record; v_range employee_mgmt.t_salary_range; v_cursor SYS_REFCURSOR; v_emp employees%ROWTYPE; BEGIN -- 测试雇佣功能 employee_mgmt.hire_employee( p_first_name John, p_last_name Developer, p_email NULL, p_job_id IT_PROG, p_salary 8000, p_department_id 60, p_employee_id v_emp_id ); -- 测试查询功能 v_record : employee_mgmt.get_employee_detail(v_emp_id); DBMS_OUTPUT.PUT_LINE(Name: || v_record.emp_name); DBMS_OUTPUT.PUT_LINE(Salary: $ || v_record.salary); -- 测试批量调薪 employee_mgmt.batch_salary_increase(60, 10, v_count); DBMS_OUTPUT.PUT_LINE(Updated: || v_count || employees); -- 测试薪资统计 v_range : employee_mgmt.get_department_salary_stats(60); DBMS_OUTPUT.PUT_LINE(Avg Salary: $ || v_range.avg_salary); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(Error: || employee_mgmt.g_last_error_message); END; /5.5 游标高级应用-- 游标变量与动态游标 CREATE OR REPLACE PROCEDURE process_employees_by_dept( p_department_id IN NUMBER ) IS -- 显式游标声明 CURSOR c_emp_cursor IS SELECT employee_id, first_name, last_name, salary, hire_date FROM employees WHERE department_id p_department_id ORDER BY salary DESC; -- 带参数的游标 CURSOR c_emp_by_job(p_job_id VARCHAR2) IS SELECT * FROM employees WHERE job_id p_job_id ORDER BY employee_id; -- 游标变量REF CURSOR TYPE t_ref_cursor IS REF CURSOR; v_ref_cursor t_ref_cursor; v_emp_record employees%ROWTYPE; v_processed_count NUMBER : 0; BEGIN -- 使用显式游标 DBMS_OUTPUT.PUT_LINE( Processing Department || p_department_id || ); OPEN c_emp_cursor; LOOP FETCH c_emp_cursor INTO v_emp_record; EXIT WHEN c_emp_cursor%NOTFOUND; v_processed_count : v_processed_count 1; DBMS_OUTPUT.PUT_LINE( v_processed_count || . || v_emp_record.first_name || || v_emp_record.last_name || - $ || v_emp_record.salary ); END LOOP; CLOSE c_emp_cursor; DBMS_OUTPUT.PUT_LINE(Total processed: || v_processed_count); -- 使用带参数的游标 DBMS_OUTPUT.PUT_LINE( IT Programmers ); FOR rec IN c_emp_by_job(IT_PROG) LOOP DBMS_OUTPUT.PUT_LINE(rec.first_name || || rec.last_name); END LOOP; -- 使用游标变量动态SQL OPEN v_ref_cursor FOR SELECT * FROM employees WHERE department_id :dept_id USING p_department_id; LOOP FETCH v_ref_cursor INTO v_emp_record; EXIT WHEN v_ref_cursor%NOTFOUND; -- 处理逻辑 END LOOP; CLOSE v_ref_cursor; END process_employees_by_dept; /5.6 触发器开发-- DML 触发器 CREATE OR REPLACE TRIGGER trg_employees_audit BEFORE INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW DECLARE v_operation VARCHAR2(20); BEGIN -- 确定操作类型 IF INSERTING THEN v_operation : INSERT; :NEW.create_date : SYSDATE; :NEW.last_update_date : SYSDATE; ELSIF UPDATING THEN v_operation : UPDATE; :NEW.last_update_date : SYSDATE; ELSIF DELETING THEN v_operation : DELETE; END IF; -- 记录审计信息 INSERT INTO employees_audit ( audit_id, operation_type, employee_id, old_salary, new_salary, change_date, changed_by ) VALUES ( audit_seq.NEXTVAL, v_operation, NVL(:NEW.employee_id, :OLD.employee_id), :OLD.salary, :NEW.salary, SYSDATE, USER ); EXCEPTION WHEN OTHERS THEN -- 触发器中的异常不会阻止DML操作 DBMS_OUTPUT.PUT_LINE(Audit error: || SQLERRM); END; / -- 系统事件触发器 CREATE OR REPLACE TRIGGER trg_logon_audit AFTER LOGON ON DATABASE BEGIN INSERT INTO user_logon_log ( username, logon_time, ip_address, program ) VALUES ( USER, SYSDATE, SYS_CONTEXT(USERENV, IP_ADDRESS), SYS_CONTEXT(USERENV, MODULE) ); COMMIT; END; /5.7 动态 SQL 实现-- 动态SQL工具包 CREATE OR REPLACE PACKAGE dynamic_sql_utils AS -- 执行DDL语句 PROCEDURE execute_ddl(p_ddl_statement IN VARCHAR2); -- 动态查询返回游标 FUNCTION dynamic_query( p_select_clause IN VARCHAR2, p_from_clause IN VARCHAR2, p_where_clause IN VARCHAR2 DEFAULT NULL, p_order_by IN VARCHAR2 DEFAULT NULL ) RETURN SYS_REFCURSOR; -- 动态UPDATE/INSERT/DELETE PROCEDURE execute_dml( p_dml_statement IN VARCHAR2, p_rows_affected OUT NUMBER ); END dynamic_sql_utils; / CREATE OR REPLACE PACKAGE BODY dynamic_sql_utils AS PROCEDURE execute_ddl(p_ddl_statement IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE p_ddl_statement; DBMS_OUTPUT.PUT_LINE(DDL executed successfully); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DDL Error: || SQLERRM); RAISE; END execute_ddl; FUNCTION dynamic_query( p_select_clause IN VARCHAR2, p_from_clause IN VARCHAR2, p_where_clause IN VARCHAR2 DEFAULT NULL, p_order_by IN VARCHAR2 DEFAULT NULL ) RETURN SYS_REFCURSOR IS v_cursor SYS_REFCURSOR; v_sql VARCHAR2(4000); BEGIN v_sql : SELECT || p_select_clause || FROM || p_from_clause; IF p_where_clause IS NOT NULL THEN v_sql : v_sql || WHERE || p_where_clause; END IF; IF p_order_by IS NOT NULL THEN v_sql : v_sql || ORDER BY || p_order_by; END IF; OPEN v_cursor FOR v_sql; RETURN v_cursor; END dynamic_query; PROCEDURE execute_dml( p_dml_statement IN VARCHAR2, p_rows_affected OUT NUMBER ) IS BEGIN EXECUTE IMMEDIATE p_dml_statement; p_rows_affected : SQL%ROWCOUNT; END execute_dml; END dynamic_sql_utils; /5.8 性能优化最佳实践-- BULK COLLECT 和 FORALL 优化 CREATE OR REPLACE PROCEDURE batch_process_large_data IS -- 定义集合类型 TYPE t_id_array IS TABLE OF employees.employee_id%TYPE; TYPE t_salary_array IS TABLE OF employees.salary%TYPE; v_ids t_id_array; v_salaries t_salary_array; CURSOR c_employees IS SELECT employee_id, salary FROM employees WHERE department_id 50; v_batch_size CONSTANT NUMBER : 1000; BEGIN OPEN c_employees; LOOP -- 批量获取数据 FETCH c_employees BULK COLLECT INTO v_ids, v_salaries LIMIT v_batch_size; EXIT WHEN v_ids.COUNT 0; -- 业务处理 FOR i IN 1..v_ids.COUNT LOOP v_salaries(i) : v_salaries(i) * 1.1; END LOOP; -- 批量更新 FORALL i IN 1..v_ids.COUNT UPDATE employees SET salary v_salaries(i) WHERE employee_id v_ids(i); COMMIT; DBMS_OUTPUT.PUT_LINE(Processed batch: || v_ids.COUNT || rows); END LOOP; CLOSE c_employees; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END batch_process_large_data; /---通过本文的全面讲解我们深入掌握了 Oracle PL/SQL 从基础语法到高级开发的所有核心内容。PL/SQL 作为 Oracle 数据库的原生编程语言在数据处理、业务逻辑封装和性能优化方面具有不可替代的优势掌握好 PL/SQL 是成为一名优秀 Oracle 开发人员的必备技能。