【Oracle】游標

在這里插入圖片描述

個人主頁: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行
第3行
第n行
游標指針

1.2 游標的生命周期

游標的完整生命周期包含四個關鍵階段:

聲明游標
打開游標
讀取數據
還有數據?
關閉游標

1.3 游標的分類

Oracle提供了多種類型的游標來滿足不同的需求:

Oracle游標類型
顯式游標
隱式游標
REF游標
游標變量
用戶定義和控制
手動管理生命周期
Oracle自動管理
單行SELECT或DML
強類型REF游標
弱類型REF游標
動態查詢支持
基于游標的變量
可傳遞參數

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提供了多個游標屬性來檢查游標狀態:

游標屬性
%FOUND
%NOTFOUND
%ROWCOUNT
%ISOPEN
返回TRUE如果上次FETCH成功
返回TRUE如果上次FETCH失敗
返回已讀取的行數
返回TRUE如果游標已打開

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 隱式游標的特點

隱式游標特點
系統自動管理
SQL%屬性訪問
單行操作優化
無需顯式聲明
自動打開和關閉
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN
INSERT/UPDATE/DELETE
單行SELECT INTO

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游標類型

REF游標類型
強類型REF游標
弱類型REF游標
指定返回類型
編譯時類型檢查
更好的性能
SYS_REFCURSOR
運行時確定類型
最大靈活性

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 游標性能考慮因素

游標性能優化
選擇合適的游標類型
合理使用BULK COLLECT
優化SQL查詢
控制游標作用域
避免頻繁開關游標
顯式 vs 隱式
強類型 vs 弱類型
批量處理
內存使用控制
索引優化
執行計劃分析
及時關閉游標
游標變量傳遞
游標緩存
連接池使用

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;
/

結語
感謝您的閱讀!期待您的一鍵三連!歡迎指正!

在這里插入圖片描述

本文來自互聯網用戶投稿,該文觀點僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。
如若轉載,請注明出處:http://www.pswp.cn/bicheng/83552.shtml
繁體地址,請注明出處:http://hk.pswp.cn/bicheng/83552.shtml
英文地址,請注明出處:http://en.pswp.cn/bicheng/83552.shtml

如若內容造成侵權/違法違規/事實不符,請聯系多彩編程網進行投訴反饋email:809451989@qq.com,一經查實,立即刪除!

相關文章

pikachu靶場通關筆記11 XSS關卡07-XSS之關鍵字過濾繞過(三種方法滲透)

目錄 一、源碼分析 1、進入靶場 2、代碼審計 3、攻擊思路 二、滲透實戰 1、探測過濾信息 2、注入Payload1 3、注入Payload2 4、注入Payload3 本系列為通過《pikachu靶場通關筆記》的XSS關卡(共10關&#xff09;滲透集合&#xff0c;通過對XSS關卡源碼的代碼審計找到安…

XML 元素:基礎、應用與優化

XML 元素:基礎、應用與優化 引言 XML(可擴展標記語言)作為一種數據交換的標準格式,廣泛應用于互聯網數據交換、數據存儲等領域。XML 元素是 XML 文檔的核心組成部分,本文將深入探討 XML 元素的概念、特性、應用以及優化方法。 一、XML 元素概述 1.1 XML 元素的定義 X…

【Axure高保真原型】交通事故大屏可視化分析案例

今天和大家分享交通事故大屏可視化分析案例的原型模板&#xff0c;包括餅圖分類分析、動態顯示發生數、柱狀圖趨勢分析、中部地圖展示最新事故發現地點和其他信息、右側列表記錄發生事故的信息…… 通過多種可視化圖表展示分析結果&#xff0c;具體效果可以點擊下方視頻觀看或…

HCIP(BGP基礎)

一、BGP 基礎概念 1. 網絡分類與協議定位 IGP&#xff08;內部網關協議&#xff09;&#xff1a;用于自治系統&#xff08;AS&#xff09;內部路由&#xff0c;如 RIP、OSPF、EIGRP&#xff0c;關注選路效率、收斂速度和資源占用。EGP&#xff08;外部網關協議&#xff09;&a…

【HarmonyOS 5】 ArkUI-X開發中的常見問題及解決方案

一、跨平臺編譯與適配問題 1. 平臺特定API不兼容 ?問題現象?&#xff1a;使用Router模塊的replaceUrl或startAbility等鴻蒙專屬API時&#xff0c;編譯跨平臺工程報錯cant support crossplatform application。 ?解決方案?&#xff1a; 改用ohos.router的跨平臺封裝API&a…

Matlab2018a---安裝教程

目錄 壹 | 引 言 貳 | 安裝環境 叁 | 安 裝 肆 | 結 語 壹 | 引 言 大家好&#xff0c;我是子正。 最近想學習一下DSP數字信號處理有關的知識&#xff0c;要用到Matlab進行數據處理&#xff0c;于是又重新把Matlab撿了回來; 記得上學那會兒用的還是Matlab2012a&#xff…

分布式流處理與消息傳遞——Kafka ISR(In-Sync Replicas)算法深度解析

Java Kafka ISR&#xff08;In-Sync Replicas&#xff09;算法深度解析 一、ISR核心原理 #mermaid-svg-OQtnaUGNQ9PMgbW0 {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-OQtnaUGNQ9PMgbW0 .error-icon{fill:#55222…

ARM GIC V3概述

中斷類型 locality- specific peripheral interrupt&#xff08;LPI&#xff09;&#xff1a;LPI是一個有針對性的外設中斷&#xff0c;通過affinity路由到特定的PE。 為非安全group1中斷邊沿觸發可以通過its進行路由沒有active狀態&#xff0c;所以不需要明確的停用操作LPI總…

藍橋杯國賽訓練 day1

目錄 k倍區間 舞獅 交換瓶子 k倍區間 取模后算組合數就行 import java.util.HashMap; import java.util.Map; import java.util.Scanner;public class Main {static Scanner sc new Scanner(System.in);public static void main(String[] args) {solve();}public static vo…

安裝和配置 Nginx 和 Mysql —— 一步一步配置 Ubuntu Server 的 NodeJS 服務器詳細實錄6

前言 昨天更新了四篇博客&#xff0c;我們順利的 安裝了 ubuntu server 服務器&#xff0c;并且配置好了 ssh 免密登錄服務器&#xff0c;安裝好了 服務器常用軟件安裝, 配置好了 zsh 和 vim 以及 通過 NVM 安裝好Nodejs&#xff0c;還有PNPM包管理工具 。 作為服務器的運行…

鴻蒙版Taro 搭建開發環境

鴻蒙版Taro 搭建開發環境 一、配置鴻蒙環境 下載安裝 DevEco 建議使用最新版本的 IDE&#xff0c;當前為 5.0.5Release 版本。 二、創建鴻蒙項目 打開 DevEco&#xff0c;點擊右上角的 Create Project&#xff0c;在 Application 處選擇 Empty Ability&#xff0c;點擊 Ne…

Could not get unknown property ‘mUser‘ for Credentials [username: null]

最近遇到jekins打包報錯&#xff1a; Could not get unknown property mUser for Credentials [username: null] of type org.gradle.internal.credentials.DefaultPasswordCredentials_Decorated。 項目使用的是gradle&#xff0c;通過pipeline打docker包&#xff1b;因為ma…

Spring Boot + MyBatis-Plus 讀寫分離與多 Slave 負載均衡示例

Spring Boot + MyBatis-Plus 讀寫分離與多 Slave 負載均衡示例 一、項目結構 src/main/java/com/example/demo/ ├── config/ │ ├── DataSourceConfig.java # 數據源配置 │ ├── MyBatisPlusConfig.java # MyBatis-Plus配置 ├── constant/ │…

android binder(1)基本原理

一、IPC 進程間通信&#xff08;IPC&#xff0c;Inter-Process Communication&#xff09;機制&#xff0c;用于解決不同進程間的數據交互問題。 不同進程之間用戶地址空間的變量和函數是不能相互訪問的&#xff0c;但是不同進程的內核地址空間是相同和共享的&#xff0c;我們可…

高密爆炸警鐘長鳴:AI為化工安全戴上“智能護盾”

一、高密爆炸&#xff1a;一聲巨響&#xff0c;撕開化工安全“傷疤” 2025年5月27日&#xff0c;山東高密友道化學有限公司的車間爆炸聲&#xff0c;像一把利刃劃破了化工行業的平靜。劇烈的沖擊波將車間夷為平地&#xff0c;黑色蘑菇云騰空而起&#xff0c;刺鼻的化學氣味彌漫…

雙擎驅動:華為云數字人與DeepSeek大模型的智能交互升級方案

一、技術融合概述 華為云數字人 華為云數字人&#xff0c;全稱&#xff1a;數字內容生產線 MetaStudio。數字內容生產線&#xff0c;提供數字人視頻制作、視頻直播、智能交互、企業代言等多種服務能力&#xff0c;使能千行百業降本增效。另外&#xff0c;數字內容生產線&#…

Linux運維筆記:1010實驗室電腦資源規范使用指南

文章目錄 一. 檢查資源使用情況&#xff0c;避免沖突1. 檢查在線用戶2. 檢查 CPU 使用情況3. 檢查 GPU 使用情況4. 協作建議 二. 備份重要文件和數據三. 定期清理硬盤空間四. 退出 ThinLinc 時注銷&#xff0c;釋放內存五. 校外使用時配置 VPN注意事項 總結 實驗室的電腦配備了…

手機郵箱APP操作

收發電子郵件方式 郵箱可以在網絡段登錄&#xff0c;也可以在手機端登錄。 大學網絡服務 收發電子郵件有三種方式&#xff1a; 1、Web方式&#xff1a; 1&#xff09;登錄“網絡服務”&#xff08;https://its.pku.edu.cn&#xff09;&#xff0c;點頁面頂端“郵箱”。 2&…

Dockerfile 使用多階段構建(build 階段 → release 階段)后端配置

錯誤Dockerfile配置示例&#xff1a; FROM python:3.11 as buildENV http_proxyhttp://172.17.0.1:7890 ENV https_proxyhttp://172.17.0.1:7890WORKDIR /appENV PYTHONPATH/app# Install Poetry # RUN curl -sSL https://install.python-poetry.org | POETRY_HOME/opt/poetry…

webstrom中git插件勾選提交部分文件時卻出現提交全部問題怎么解決

原因是我有個.husky的文件制定了執行提交的時候就是提交所有的文件 修改.husky/pre-commit文件就可以啦 #!/usr/bin/env sh . "$(dirname -- "$0")/_/husky.sh"# 獲取通過 WebStorm 提交的暫存文件&#xff08;僅勾選的部分&#xff09; STAGED_FILES$(gi…