— 创建序列
CREATE SEQUENCE fgedu_emp_seq START WITH 1001 IN
CREMENT BY 1;
/

— 使用 FOR
ALL批量插入数据
SET SERVEROUTPUT ON;
DECLARE
TYPE
emp_name_tab IS
TABLE
OF VARCHAR2(100) IN
DEX BY PLS_IN
TEGER;
TYPE
emp_dept_tab IS
TABLE
OF VARCHAR2(50) IN
DEX BY PLS_IN
TEGER;
TYPE
emp_salary_tab IS
TABLE
OF NUMBER IN
DEX BY PLS_IN
TEGER;

emp_names emp_name_tab;
emp_depts emp_dept_tab;
emp_salaries emp_salary_tab;

v_start_time TIMESTAMP;
v_end_time TIMESTAMP;
BEGIN
— 准备测试数据
FOR
i IN
1 .. 10000 LOOP
emp_names(i) :=
‘员工’ || TO_CHAR(i);
emp_depts(i) :=
CAS
E MOD(i, 3)
WHEN 0 THEN ‘技术部’
WHEN 1 THEN ‘市场部’
ELSE
‘财务部’
END
;
emp_salaries(i) :=
5000 + MOD(i, 1000);
END LOOP
;

— 记录开始时间
v_start_time :=
SYSTIMESTAMP;

— 使用 FOR
ALL批量插入
FOR
ALL i IN
1 .. emp_names.COUNT
INSERT
IN
TO fgedu_employees (emp_id, emp_name, emp_dept, emp_salary)
VALUES (fgedu_emp_seq.NEXTVAL, emp_names(i), emp_depts(i), emp_salaries(i));

COMMIT
;

— 记录结束时间
v_end_time :=
SYSTIMESTAMP;

DBMS_OUTPUT.PUT_LINE(‘批量插入完成，共插入 ‘ || emp_names.COUNT || ‘ 条记录’);
DBMS_OUTPUT.PUT_LINE(‘耗时: ‘ || TO_CHAR(v_end_time – v_start_time, ‘MI:SS.FF3’));
END
;
/

— 验证插入结果
SELECT
COUNT(*)
FROM fgedu_employees;

— 使用 FOR
ALL批量更新数据
SET SERVEROUTPUT ON;
DECLARE
TYPE
emp_id_tab IS
TABLE
OF NUMBER IN
DEX BY PLS_IN
TEGER;
TYPE
emp_salary_tab IS
TABLE
OF NUMBER IN
DEX BY PLS_IN
TEGER;

emp_ids emp_id_tab;
emp_salaries emp_salary_tab;

v_start_time TIMESTAMP;
v_end_time TIMESTAMP;
BEGIN
— 获取员工ID
SELECT
emp_id BULK COLLECT IN
TO emp_ids
FROM fgedu_employees;

— 准备新工资
FOR
i IN
1 .. emp_ids.COUNT LOOP
emp_salaries(i) :=
6000 + MOD(i, 1000);
END LOOP
;

— 记录开始时间
v_start_time :=
SYSTIMESTAMP;

— 使用 FOR
ALL批量更新
FOR
ALL i IN
1 .. emp_ids.COUNT
UPDATE
fgedu_employees
SET emp_salary = emp_salaries(i)
WHERE emp_id = emp_ids(i);

COMMIT
;

— 记录结束时间
v_end_time :=
SYSTIMESTAMP;

DBMS_OUTPUT.PUT_LINE(‘批量更新完成，共更新 ‘ || emp_ids.COUNT || ‘ 条记录’);
DBMS_OUTPUT.PUT_LINE(‘耗时: ‘ || TO_CHAR(v_end_time – v_start_time, ‘MI:SS.FF3’));
END
;
/

— 验证更新结果
SELECT
AVG(emp_salary)
FROM fgedu_employees;