個人主頁:Guiat
歸屬專欄:Oracle
文章目錄
- 1. 游標基礎概述
- 1.1 游標的概念與作用
- 1.2 游標的生命周期
- 1.3 游標的分類
- 2. 顯式游標
- 2.1 顯式游標的基本語法
- 2.1.1 聲明游標
- 2.1.2 帶參數的游標
- 2.2 游標的基本操作
- 2.2.1 完整的游標操作示例
- 2.3 游標屬性
- 2.3.1 游標屬性應用示例
- 2.4 游標FOR循環
- 2.4.1 基本游標FOR循環
- 2.4.2 內聯游標FOR循環
- 2.4.3 帶參數的游標FOR循環
- 3. 隱式游標
- 3.1 隱式游標的特點
- 3.2 隱式游標應用示例
- 3.2.1 DML操作中的隱式游標
- 3.2.2 SELECT INTO語句中的隱式游標
- 3.3 隱式游標與異常處理
- 4. REF游標
- 4.1 REF游標類型
- 4.2 強類型REF游標
- 4.2.1 聲明和使用強類型REF游標
- 4.2.2 自定義記錄類型的REF游標
- 4.3 弱類型REF游標
- 4.3.1 使用SYS_REFCURSOR
- 4.3.2 動態查詢處理
- 4.4 REF游標作為參數傳遞
- 4.4.1 函數返回REF游標
- 4.4.2 存儲過程的OUT參數REF游標
- 5. 游標高級特性
- 5.1 可更新游標
- 5.1.1 FOR UPDATE子句
- 5.1.2 選擇性鎖定
- 5.2 批量操作(BULK COLLECT)
- 5.2.1 基本BULK COLLECT
- 5.2.2 帶LIMIT的BULK COLLECT
- 5.2.3 FORALL批量DML操作
- 6. 游標性能優化
- 6.1 游標性能考慮因素
- 6.2 性能對比示例
- 6.2.1 傳統處理 vs BULK COLLECT
正文
1. 游標基礎概述
游標是Oracle PL/SQL中用于處理查詢結果集的重要機制,它允許我們逐行處理SQL查詢返回的數據,為復雜的數據處理提供了強大的控制能力。
1.1 游標的概念與作用
游標本質上是指向查詢結果集中某一行的指針,通過移動指針來逐行訪問和處理數據。
1.2 游標的生命周期
游標的完整生命周期包含四個關鍵階段:
1.3 游標的分類
Oracle提供了多種類型的游標來滿足不同的需求:
2. 顯式游標
顯式游標是程序員顯式聲明、打開、讀取和關閉的游標,提供了對查詢結果集的完全控制。
2.1 顯式游標的基本語法
2.1.1 聲明游標
-- 基本游標聲明
DECLARECURSOR emp_cursor ISSELECT employee_id, first_name, last_name, salaryFROM employeesWHERE department_id = 10;v_emp_id employees.employee_id%TYPE;v_first_name employees.first_name%TYPE;v_last_name employees.last_name%TYPE;v_salary employees.salary%TYPE;
BEGIN-- 游標操作NULL;
END;
/
2.1.2 帶參數的游標
DECLARE-- 帶參數的游標聲明CURSOR emp_dept_cursor(p_dept_id NUMBER) ISSELECT employee_id, first_name, last_name, salaryFROM employeesWHERE department_id = p_dept_idORDER BY salary DESC;-- 使用%ROWTYPE簡化變量聲明emp_record emp_dept_cursor%ROWTYPE;
BEGIN-- 打開游標時傳遞參數OPEN emp_dept_cursor(20);LOOPFETCH emp_dept_cursor INTO emp_record;EXIT WHEN emp_dept_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE('員工ID: ' || emp_record.employee_id || ', 姓名: ' || emp_record.first_name || ' ' || emp_record.last_name ||', 工資: ' || emp_record.salary);END LOOP;CLOSE emp_dept_cursor;
END;
/
2.2 游標的基本操作
2.2.1 完整的游標操作示例
DECLARE-- 聲明游標CURSOR salary_cursor ISSELECT employee_id, first_name, last_name, salary, department_idFROM employeesWHERE salary > 5000ORDER BY salary DESC;-- 聲明記錄類型變量emp_rec salary_cursor%ROWTYPE;v_counter NUMBER := 0;v_total_salary NUMBER := 0;BEGINDBMS_OUTPUT.PUT_LINE('=== 高薪員工報告 ===');-- 打開游標OPEN salary_cursor;-- 讀取數據LOOPFETCH salary_cursor INTO emp_rec;-- 檢查是否還有數據EXIT WHEN salary_cursor%NOTFOUND;v_counter := v_counter + 1;v_total_salary := v_total_salary + emp_rec.salary;DBMS_OUTPUT.PUT_LINE(v_counter || '. ' || emp_rec.first_name || ' ' || emp_rec.last_name ||' (ID: ' || emp_rec.employee_id || ')' ||' - 工資: $' || emp_rec.salary ||' - 部門: ' || emp_rec.department_id);END LOOP;-- 關閉游標CLOSE salary_cursor;-- 統計信息DBMS_OUTPUT.PUT_LINE('====================');DBMS_OUTPUT.PUT_LINE('總計: ' || v_counter || ' 名高薪員工');DBMS_OUTPUT.PUT_LINE('平均工資: $' || ROUND(v_total_salary / v_counter, 2));EXCEPTIONWHEN OTHERS THEN-- 確保游標關閉IF salary_cursor%ISOPEN THENCLOSE salary_cursor;END IF;RAISE;
END;
/
2.3 游標屬性
Oracle提供了多個游標屬性來檢查游標狀態:
2.3.1 游標屬性應用示例
DECLARECURSOR dept_cursor ISSELECT department_id, department_name, manager_idFROM departmentsWHERE department_id BETWEEN 10 AND 50;dept_rec dept_cursor%ROWTYPE;BEGIN-- 檢查游標是否已打開IF NOT dept_cursor%ISOPEN THENOPEN dept_cursor;DBMS_OUTPUT.PUT_LINE('游標已打開');END IF;LOOPFETCH dept_cursor INTO dept_rec;-- 使用%FOUND屬性IF dept_cursor%FOUND THENDBMS_OUTPUT.PUT_LINE('第 ' || dept_cursor%ROWCOUNT || ' 行: ' ||dept_rec.department_name || ' (ID: ' || dept_rec.department_id || ')');END IF;-- 使用%NOTFOUND屬性退出循環EXIT WHEN dept_cursor%NOTFOUND;END LOOP;DBMS_OUTPUT.PUT_LINE('總共處理了 ' || dept_cursor%ROWCOUNT || ' 個部門');-- 關閉游標CLOSE dept_cursor;-- 驗證游標已關閉IF NOT dept_cursor%ISOPEN THENDBMS_OUTPUT.PUT_LINE('游標已關閉');END IF;END;
/
2.4 游標FOR循環
游標FOR循環是處理游標的簡化語法,自動處理游標的打開、讀取和關閉:
2.4.1 基本游標FOR循環
DECLARECURSOR emp_cursor ISSELECT employee_id, first_name, last_name, hire_date, salaryFROM employeesWHERE department_id = 20ORDER BY hire_date;BEGINDBMS_OUTPUT.PUT_LINE('=== 部門20員工信息 ===');-- 游標FOR循環 - 自動管理游標生命周期FOR emp_rec IN emp_cursor LOOPDBMS_OUTPUT.PUT_LINE('員工: ' || emp_rec.first_name || ' ' || emp_rec.last_name ||', 入職日期: ' || TO_CHAR(emp_rec.hire_date, 'YYYY-MM-DD') ||', 工資: $' || emp_rec.salary);END LOOP;END;
/
2.4.2 內聯游標FOR循環
BEGINDBMS_OUTPUT.PUT_LINE('=== 各部門平均工資統計 ===');-- 內聯游標FOR循環 - 無需顯式聲明游標FOR dept_rec IN (SELECT d.department_name, ROUND(AVG(e.salary), 2) as avg_salary,COUNT(e.employee_id) as emp_countFROM departments dJOIN employees e ON d.department_id = e.department_idGROUP BY d.department_nameORDER BY avg_salary DESC) LOOPDBMS_OUTPUT.PUT_LINE('部門: ' || dept_rec.department_name ||', 平均工資: $' || dept_rec.avg_salary ||', 員工數: ' || dept_rec.emp_count);END LOOP;END;
/
2.4.3 帶參數的游標FOR循環
DECLARECURSOR salary_range_cursor(p_min_sal NUMBER, p_max_sal NUMBER) ISSELECT employee_id, first_name, last_name, salary, department_idFROM employeesWHERE salary BETWEEN p_min_sal AND p_max_salORDER BY salary;BEGINDBMS_OUTPUT.PUT_LINE('=== 工資范圍 $5000-$10000 的員工 ===');FOR emp_rec IN salary_range_cursor(5000, 10000) LOOPDBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id ||', 姓名: ' || emp_rec.first_name || ' ' || emp_rec.last_name ||', 工資: $' || emp_rec.salary ||', 部門: ' || emp_rec.department_id);END LOOP;END;
/
3. 隱式游標
隱式游標是Oracle自動為每個DML語句和單行SELECT語句創建的游標,由系統自動管理。
3.1 隱式游標的特點
3.2 隱式游標應用示例
3.2.1 DML操作中的隱式游標
DECLAREv_dept_id NUMBER := 90;v_location_id NUMBER := 1700;v_affected_rows NUMBER;BEGIN-- 插入操作INSERT INTO departments (department_id, department_name, location_id)VALUES (v_dept_id, 'New Department', v_location_id);-- 檢查插入是否成功IF SQL%FOUND THENDBMS_OUTPUT.PUT_LINE('部門插入成功,影響行數: ' || SQL%ROWCOUNT);ELSEDBMS_OUTPUT.PUT_LINE('部門插入失敗');END IF;-- 更新操作UPDATE employeesSET salary = salary * 1.05WHERE department_id = 20 AND salary < 8000;v_affected_rows := SQL%ROWCOUNT;IF v_affected_rows > 0 THENDBMS_OUTPUT.PUT_LINE('成功給 ' || v_affected_rows || ' 名員工加薪5%');ELSEDBMS_OUTPUT.PUT_LINE('沒有符合條件的員工需要加薪');END IF;-- 刪除操作DELETE FROM departments WHERE department_id = v_dept_id;IF SQL%FOUND THENDBMS_OUTPUT.PUT_LINE('部門刪除成功');END IF;-- 注意:隱式游標的%ISOPEN始終返回FALSE-- 因為它在語句執行后立即關閉DBMS_OUTPUT.PUT_LINE('隱式游標是否打開: ' || CASE WHEN SQL%ISOPEN THEN 'TRUE' ELSE 'FALSE' END);END;
/
3.2.2 SELECT INTO語句中的隱式游標
DECLAREv_emp_name VARCHAR2(100);v_emp_salary NUMBER;v_emp_id NUMBER := 100;BEGIN-- 單行SELECT INTO使用隱式游標BEGINSELECT first_name || ' ' || last_name, salaryINTO v_emp_name, v_emp_salaryFROM employeesWHERE employee_id = v_emp_id;-- 檢查是否找到記錄IF SQL%FOUND THENDBMS_OUTPUT.PUT_LINE('找到員工: ' || v_emp_name || ', 工資: $' || v_emp_salary);END IF;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('沒有找到員工ID為 ' || v_emp_id || ' 的記錄');WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE('查詢返回了多行記錄');END;END;
/
3.3 隱式游標與異常處理
隱式游標的使用需要特別注意異常處理:
CREATE OR REPLACE PROCEDURE process_employee_bonus(p_emp_id NUMBER, p_bonus_pct NUMBER)
ASv_current_salary NUMBER;v_new_bonus NUMBER;v_emp_name VARCHAR2(100);BEGIN-- 獲取員工信息BEGINSELECT salary, first_name || ' ' || last_nameINTO v_current_salary, v_emp_nameFROM employeesWHERE employee_id = p_emp_id;-- 計算獎金v_new_bonus := v_current_salary * p_bonus_pct / 100;DBMS_OUTPUT.PUT_LINE('員工 ' || v_emp_name || ' 當前工資: $' || v_current_salary);DBMS_OUTPUT.PUT_LINE('計算獎金 ' || p_bonus_pct || '%: $' || v_new_bonus);-- 更新獎金(假設有bonus列)-- UPDATE employees SET bonus = v_new_bonus WHERE employee_id = p_emp_id;IF SQL%ROWCOUNT > 0 THENDBMS_OUTPUT.PUT_LINE('獎金更新成功');END IF;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('錯誤: 員工ID ' || p_emp_id || ' 不存在');WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE('錯誤: 查詢返回多個員工記錄');END;END;
/-- 調用存儲過程
BEGINprocess_employee_bonus(100, 10); -- 給員工100發放10%獎金process_employee_bonus(999, 5); -- 不存在的員工ID
END;
/
4. REF游標
REF游標(游標變量)是一種特殊的游標類型,支持動態SQL和在子程序之間傳遞游標。
4.1 REF游標類型
4.2 強類型REF游標
4.2.1 聲明和使用強類型REF游標
DECLARE-- 定義強類型REF游標TYPE emp_cursor_type IS REF CURSOR RETURN employees%ROWTYPE;-- 聲明游標變量emp_cursor emp_cursor_type;emp_record employees%ROWTYPE;v_dept_id NUMBER := 20;BEGIN-- 打開游標OPEN emp_cursor FORSELECT * FROM employees WHERE department_id = v_dept_idORDER BY salary DESC;DBMS_OUTPUT.PUT_LINE('=== 部門 ' || v_dept_id || ' 員工列表 ===');LOOPFETCH emp_cursor INTO emp_record;EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE('ID: ' || emp_record.employee_id ||', 姓名: ' || emp_record.first_name || ' ' || emp_record.last_name ||', 工資: $' || emp_record.salary);END LOOP;CLOSE emp_cursor;DBMS_OUTPUT.PUT_LINE('總共處理了 ' || emp_cursor%ROWCOUNT || ' 名員工');END;
/
4.2.2 自定義記錄類型的REF游標
DECLARE-- 定義自定義記錄類型TYPE emp_summary_rec IS RECORD (emp_id NUMBER,full_name VARCHAR2(100),department VARCHAR2(50),salary NUMBER,hire_year NUMBER);-- 定義基于記錄類型的REF游標TYPE emp_summary_cursor_type IS REF CURSOR RETURN emp_summary_rec;emp_cursor emp_summary_cursor_type;emp_rec emp_summary_rec;BEGIN-- 打開游標OPEN emp_cursor FORSELECT e.employee_id,e.first_name || ' ' || e.last_name,d.department_name,e.salary,EXTRACT(YEAR FROM e.hire_date)FROM employees eJOIN departments d ON e.department_id = d.department_idWHERE e.salary > 8000ORDER BY e.salary DESC;DBMS_OUTPUT.PUT_LINE('=== 高薪員工摘要報告 ===');LOOPFETCH emp_cursor INTO emp_rec;EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE('員工: ' || emp_rec.full_name ||', 部門: ' || emp_rec.department ||', 工資: $' || emp_rec.salary ||', 入職年份: ' || emp_rec.hire_year);END LOOP;CLOSE emp_cursor;END;
/
4.3 弱類型REF游標
4.3.1 使用SYS_REFCURSOR
DECLARE-- 聲明弱類型REF游標my_cursor SYS_REFCURSOR;v_sql VARCHAR2(1000);v_table_name VARCHAR2(30) := 'employees';v_condition VARCHAR2(100) := 'department_id = 10';-- 動態處理不同的查詢結果v_employee_id NUMBER;v_first_name VARCHAR2(50);v_last_name VARCHAR2(50);v_department_id NUMBER;v_salary NUMBER;BEGIN-- 構建動態SQLv_sql := 'SELECT employee_id, first_name, last_name, department_id, salary FROM ' || v_table_name || ' WHERE ' || v_condition || 'ORDER BY salary DESC';DBMS_OUTPUT.PUT_LINE('執行SQL: ' || v_sql);DBMS_OUTPUT.PUT_LINE('======================');-- 打開游標OPEN my_cursor FOR v_sql;LOOPFETCH my_cursor INTO v_employee_id, v_first_name, v_last_name, v_department_id, v_salary;EXIT WHEN my_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id ||', 姓名: ' || v_first_name || ' ' || v_last_name ||', 部門: ' || v_department_id ||', 工資: $' || v_salary);END LOOP;CLOSE my_cursor;END;
/
4.3.2 動態查詢處理
CREATE OR REPLACE PROCEDURE dynamic_query_processor(p_table_name IN VARCHAR2,p_where_clause IN VARCHAR2 DEFAULT NULL,p_order_clause IN VARCHAR2 DEFAULT NULL
)
ASquery_cursor SYS_REFCURSOR;v_sql VARCHAR2(4000);-- 使用DBMS_SQL.DESCRIBE_COLUMNS來處理不同的列類型v_desc_tab DBMS_SQL.DESC_TAB;v_col_cnt NUMBER;v_cursor_id NUMBER;BEGIN-- 構建基本SQLv_sql := 'SELECT * FROM ' || p_table_name;IF p_where_clause IS NOT NULL THENv_sql := v_sql || ' WHERE ' || p_where_clause;END IF;IF p_order_clause IS NOT NULL THENv_sql := v_sql || ' ORDER BY ' || p_order_clause;END IF;DBMS_OUTPUT.PUT_LINE('執行動態查詢: ' || v_sql);DBMS_OUTPUT.PUT_LINE('===========================================');-- 打開游標OPEN query_cursor FOR v_sql;-- 這里簡化處理,實際應用中可能需要更復雜的元數據處理DBMS_OUTPUT.PUT_LINE('查詢執行成功,結果集已準備就緒');CLOSE query_cursor;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('查詢執行出錯: ' || SQLERRM);IF query_cursor%ISOPEN THENCLOSE query_cursor;END IF;
END;
/-- 調用動態查詢處理器
BEGINdynamic_query_processor('employees', 'salary > 5000', 'salary DESC');dynamic_query_processor('departments', NULL, 'department_name');
END;
/
4.4 REF游標作為參數傳遞
4.4.1 函數返回REF游標
CREATE OR REPLACE FUNCTION get_employees_by_dept(p_dept_id NUMBER)
RETURN SYS_REFCURSOR
ASemp_cursor SYS_REFCURSOR;
BEGINOPEN emp_cursor FORSELECT employee_id, first_name, last_name, email, salary, hire_dateFROM employeesWHERE department_id = p_dept_idORDER BY hire_date;RETURN emp_cursor;
END;
/-- 使用返回的REF游標
DECLAREemp_cursor SYS_REFCURSOR;v_emp_id NUMBER;v_first_name VARCHAR2(50);v_last_name VARCHAR2(50);v_email VARCHAR2(100);v_salary NUMBER;v_hire_date DATE;BEGIN-- 獲取游標emp_cursor := get_employees_by_dept(20);DBMS_OUTPUT.PUT_LINE('=== 部門20員工列表 ===');LOOPFETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name, v_email, v_salary, v_hire_date;EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id ||', 姓名: ' || v_first_name || ' ' || v_last_name ||', 郵箱: ' || v_email ||', 工資: $' || v_salary ||', 入職: ' || TO_CHAR(v_hire_date, 'YYYY-MM-DD'));END LOOP;CLOSE emp_cursor;END;
/
4.4.2 存儲過程的OUT參數REF游標
CREATE OR REPLACE PROCEDURE get_salary_statistics(p_dept_id IN NUMBER,p_emp_cursor OUT SYS_REFCURSOR,p_total_employees OUT NUMBER,p_avg_salary OUT NUMBER,p_min_salary OUT NUMBER,p_max_salary OUT NUMBER
)
AS
BEGIN-- 獲取統計信息SELECT COUNT(*), ROUND(AVG(salary), 2),MIN(salary),MAX(salary)INTO p_total_employees, p_avg_salary, p_min_salary, p_max_salaryFROM employeesWHERE department_id = p_dept_id;-- 打開游標返回詳細信息OPEN p_emp_cursor FORSELECT employee_id, first_name || ' ' || last_name as full_name,salary,ROUND((salary - p_avg_salary), 2) as salary_diff,CASE WHEN salary > p_avg_salary THEN '高于平均'WHEN salary < p_avg_salary THEN '低于平均'ELSE '等于平均'END as salary_levelFROM employeesWHERE department_id = p_dept_idORDER BY salary DESC;END;
/-- 使用OUT參數REF游標
DECLAREemp_cursor SYS_REFCURSOR;v_total_count NUMBER;v_avg_sal NUMBER;v_min_sal NUMBER;v_max_sal NUMBER;v_emp_id NUMBER;v_full_name VARCHAR2(100);v_salary NUMBER;v_salary_diff NUMBER;v_salary_level VARCHAR2(20);BEGIN-- 調用存儲過程get_salary_statistics(20, emp_cursor, v_total_count, v_avg_sal, v_min_sal, v_max_sal);-- 顯示統計信息DBMS_OUTPUT.PUT_LINE('=== 部門20工資統計 ===');DBMS_OUTPUT.PUT_LINE('員工總數: ' || v_total_count);DBMS_OUTPUT.PUT_LINE('平均工資: $' || v_avg_sal);DBMS_OUTPUT.PUT_LINE('最低工資: $' || v_min_sal);DBMS_OUTPUT.PUT_LINE('最高工資: $' || v_max_sal);DBMS_OUTPUT.PUT_LINE('========================');-- 顯示詳細信息LOOPFETCH emp_cursor INTO v_emp_id, v_full_name, v_salary, v_salary_diff, v_salary_level;EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE('員工: ' || v_full_name ||', 工資: $' || v_salary ||', 與平均差: $' || v_salary_diff ||' (' || v_salary_level || ')');END LOOP;CLOSE emp_cursor;END;
/
5. 游標高級特性
5.1 可更新游標
可更新游標允許通過游標直接更新或刪除當前行。
5.1.1 FOR UPDATE子句
DECLARECURSOR emp_cursor ISSELECT employee_id, first_name, last_name, salary, department_idFROM employeesWHERE department_id = 20FOR UPDATE; -- 鎖定查詢的行emp_rec emp_cursor%ROWTYPE;v_new_salary NUMBER;BEGINDBMS_OUTPUT.PUT_LINE('=== 部門20員工工資調整 ===');OPEN emp_cursor;LOOPFETCH emp_cursor INTO emp_rec;EXIT WHEN emp_cursor%NOTFOUND;-- 根據當前工資計算新工資IF emp_rec.salary < 5000 THENv_new_salary := emp_rec.salary * 1.15; -- 加薪15%ELSIF emp_rec.salary < 8000 THENv_new_salary := emp_rec.salary * 1.10; -- 加薪10%ELSEv_new_salary := emp_rec.salary * 1.05; -- 加薪5%END IF;-- 使用WHERE CURRENT OF更新當前行UPDATE employees SET salary = v_new_salaryWHERE CURRENT OF emp_cursor;DBMS_OUTPUT.PUT_LINE('員工 ' || emp_rec.first_name || ' ' || emp_rec.last_name ||': $' || emp_rec.salary || ' -> $' || v_new_salary ||' (漲幅: ' || ROUND(((v_new_salary - emp_rec.salary) / emp_rec.salary * 100), 1) || '%)');END LOOP;CLOSE emp_cursor;COMMIT;DBMS_OUTPUT.PUT_LINE('所有工資調整已提交');END;
/
5.1.2 選擇性鎖定
DECLARECURSOR emp_cursor ISSELECT employee_id, first_name, last_name, salary, commission_pctFROM employeesWHERE department_id IN (80, 90)FOR UPDATE OF salary NOWAIT; -- 只鎖定salary列,不等待emp_rec emp_cursor%ROWTYPE;v_bonus NUMBER;BEGINDBMS_OUTPUT.PUT_LINE('=== 銷售和管理部門績效獎金計算 ===');OPEN emp_cursor;LOOPFETCH emp_cursor INTO emp_rec;EXIT WHEN emp_cursor%NOTFOUND;-- 計算績效獎金IF emp_rec.commission_pct IS NOT NULL THENv_bonus := emp_rec.salary * emp_rec.commission_pct; -- 有提成的員工ELSEv_bonus := emp_rec.salary * 0.05; -- 無提成員工給5%獎金END IF;DBMS_OUTPUT.PUT_LINE('員工 ' || emp_rec.first_name || ' ' || emp_rec.last_name ||', 基本工資: $' || emp_rec.salary ||', 績效獎金: $' || ROUND(v_bonus, 2));-- 可以在這里更新獎金字段-- UPDATE employees SET bonus = v_bonus WHERE CURRENT OF emp_cursor;END LOOP;CLOSE emp_cursor;EXCEPTIONWHEN OTHERS THENIF emp_cursor%ISOPEN THENCLOSE emp_cursor;END IF;IF SQLCODE = -54 THEN -- Resource busyDBMS_OUTPUT.PUT_LINE('錯誤: 記錄正被其他會話使用');ELSEDBMS_OUTPUT.PUT_LINE('錯誤: ' || SQLERRM);END IF;
END;
/
5.2 批量操作(BULK COLLECT)
BULK COLLECT允許一次獲取多行數據,提高性能。
5.2.1 基本BULK COLLECT
DECLARETYPE emp_id_array IS TABLE OF employees.employee_id%TYPE;TYPE emp_name_array IS TABLE OF VARCHAR2(100);TYPE emp_salary_array IS TABLE OF employees.salary%TYPE;v_emp_ids emp_id_array;v_emp_names emp_name_array;v_emp_salaries emp_salary_array;v_total_salary NUMBER := 0;BEGIN-- 使用BULK COLLECT一次獲取所有數據SELECT employee_id, first_name || ' ' || last_name,salaryBULK COLLECT INTO v_emp_ids, v_emp_names, v_emp_salariesFROM employeesWHERE department_id = 50ORDER BY salary DESC;DBMS_OUTPUT.PUT_LINE('=== 部門50員工信息(共' || v_emp_ids.COUNT || '人)===');-- 處理批量數據FOR i IN 1..v_emp_ids.COUNT LOOPv_total_salary := v_total_salary + v_emp_salaries(i);DBMS_OUTPUT.PUT_LINE(i || '. ID: ' || v_emp_ids(i) ||', 姓名: ' || v_emp_names(i) ||', 工資: $' || v_emp_salaries(i));END LOOP;DBMS_OUTPUT.PUT_LINE('==============================');DBMS_OUTPUT.PUT_LINE('工資總額: $' || v_total_salary);DBMS_OUTPUT.PUT_LINE('平均工資: $' || ROUND(v_total_salary / v_emp_ids.COUNT, 2));END;
/
5.2.2 帶LIMIT的BULK COLLECT
DECLARECURSOR large_table_cursor ISSELECT employee_id, first_name, last_name, salaryFROM employees;TYPE emp_record_array IS TABLE OF large_table_cursor%ROWTYPE;v_emp_batch emp_record_array;v_batch_size CONSTANT PLS_INTEGER := 100; -- 每批處理100行v_total_processed NUMBER := 0;BEGINDBMS_OUTPUT.PUT_LINE('=== 批量處理員工數據 ===');OPEN large_table_cursor;LOOP-- 使用LIMIT控制每次獲取的行數FETCH large_table_cursor BULK COLLECT INTO v_emp_batch LIMIT v_batch_size;-- 處理當前批次的數據FOR i IN 1..v_emp_batch.COUNT LOOPv_total_processed := v_total_processed + 1;-- 這里可以進行復雜的業務處理-- 例如:數據轉換、驗證、插入到其他表等IF MOD(v_total_processed, 50) = 0 THENDBMS_OUTPUT.PUT_LINE('已處理 ' || v_total_processed || ' 條記錄...');END IF;END LOOP;-- 可以在這里提交事務,避免長事務-- COMMIT;-- 如果這批數據少于批次大小,說明已到末尾EXIT WHEN v_emp_batch.COUNT < v_batch_size;END LOOP;CLOSE large_table_cursor;DBMS_OUTPUT.PUT_LINE('批量處理完成,總共處理 ' || v_total_processed || ' 條記錄');END;
/
5.2.3 FORALL批量DML操作
DECLARETYPE emp_id_array IS TABLE OF employees.employee_id%TYPE;TYPE salary_array IS TABLE OF employees.salary%TYPE;v_emp_ids emp_id_array;v_old_salaries salary_array;v_new_salaries salary_array;BEGIN-- 獲取需要調薪的員工信息SELECT employee_id, salaryBULK COLLECT INTO v_emp_ids, v_old_salariesFROM employeesWHERE department_id = 30AND salary < 6000;-- 計算新工資v_new_salaries := salary_array();v_new_salaries.EXTEND(v_emp_ids.COUNT);FOR i IN 1..v_emp_ids.COUNT LOOPv_new_salaries(i) := v_old_salaries(i) * 1.12; -- 加薪12%END LOOP;DBMS_OUTPUT.PUT_LINE('=== 批量工資調整 ===');DBMS_OUTPUT.PUT_LINE('準備調整 ' || v_emp_ids.COUNT || ' 名員工的工資');-- 使用FORALL進行批量更新FORALL i IN 1..v_emp_ids.COUNTUPDATE employeesSET salary = v_new_salaries(i)WHERE employee_id = v_emp_ids(i);DBMS_OUTPUT.PUT_LINE('批量更新完成,影響行數: ' || SQL%ROWCOUNT);-- 顯示調整詳情FOR i IN 1..v_emp_ids.COUNT LOOPDBMS_OUTPUT.PUT_LINE('員工ID ' || v_emp_ids(i) ||': $' || v_old_salaries(i) ||' -> $' || v_new_salaries(i));END LOOP;COMMIT;END;
/
6. 游標性能優化
6.1 游標性能考慮因素
6.2 性能對比示例
6.2.1 傳統處理 vs BULK COLLECT
-- 傳統逐行處理方式
CREATE OR REPLACE PROCEDURE process_employees_traditional
ASCURSOR emp_cursor ISSELECT employee_id, salaryFROM employees;emp_rec emp_cursor%ROWTYPE;v_start_time NUMBER;v_end_time NUMBER;v_count NUMBER := 0;BEGINv_start_time := DBMS_UTILITY.GET_TIME;OPEN emp_cursor;LOOPFETCH emp_cursor INTO emp_rec;EXIT WHEN emp_cursor%NOTFOUND;-- 模擬處理操作v_count := v_count + 1;-- 可以在這里進行具體的業務處理NULL;END LOOP;CLOSE emp_cursor;v_end_time := DBMS_UTILITY.GET_TIME;DBMS_OUTPUT.PUT_LINE('傳統方式處理 ' || v_count || ' 條記錄');DBMS_OUTPUT.PUT_LINE('耗時: ' || (v_end_time - v_start_time) / 100 || ' 秒');END;
/-- BULK COLLECT批量處理方式
CREATE OR REPLACE PROCEDURE process_employees_bulk
ASTYPE emp_record_array IS TABLE OF employees%ROWTYPE;v_employees emp_record_array;v_start_time NUMBER;v_end_time NUMBER;v_count NUMBER := 0;BEGINv_start_time := DBMS_UTILITY.GET_TIME;SELECT * BULK COLLECT INTO v_employees FROM employees;FOR i IN 1..v_employees.COUNT LOOPv_count := v_count + 1;-- 處理每條記錄NULL;END LOOP;v_end_time := DBMS_UTILITY.GET_TIME;DBMS_OUTPUT.PUT_LINE('BULK COLLECT方式處理 ' || v_count || ' 條記錄');DBMS_OUTPUT.PUT_LINE('耗時: ' || (v_end_time - v_start_time) / 100 || ' 秒');END;
/-- 性能測試
BEGINDBMS_OUTPUT.PUT_LINE('=== 游標性能對比測試 ===');process_employees_traditional;DBMS_OUTPUT.PUT_LINE('---');process_employees_bulk;
END;
/
結語
感謝您的閱讀!期待您的一鍵三連!歡迎指正!