<think>我们有一个Oracle Forms 6i的PL/SQL过程，它使用OLE2自动化来读取Excel文件并将数据导入到Oracle数据库中。然而，这段代码存在多个问题，包括潜在的资源泄漏、错误处理不足、日期转换问题以及一些逻辑问题。我将逐步分析并提供改进的代码。
### 主要问题分析
1. **资源管理**：
- 在循环中每次迭代都重新获取工作表（worksheet），这是不必要的，应该移到循环之外。
- 在异常处理部分和正常结束部分都重复写了资源释放代码，应该统一管理资源释放。
2. **错误处理**：
- 使用了`dbms_error_text`，但这不是标准函数，应使用`SQLERRM`。
- 异常处理不够全面，例如没有处理文件选择取消的情况。
3. **日期转换**：
- `v7`是日期类型，但使用`ole2.get_char_property`读取，然后直接赋给日期变量，这会导致错误。
- 在更新和插入语句中，尝试将`v7`（已经是日期）转换为字符再转回日期，这是多余的，而且如果`v7`不是有效的日期字符串，会出错。
4. **循环退出条件**：
- 退出条件是`v6 is null`，但在读取`v6`之后立即检查，而其他变量（如v2）可能已经读取，如果v6为空，那么这些变量可能无效，但循环内已经处理了这些变量。
5. **性能问题**：
- 每次循环都弹出两次`message_alert`，这在数据量大时会非常慢且烦人。
6. **变量初始化**：
- 变量`m`和`n`从表单的`basic`块中获取，但未处理可能为空的情况。
7. **OLE对象释放**：
- 在异常处理中释放资源时，没有检查对象是否已经释放，可能会重复释放或释放空对象。
8. **逻辑错误**：
- 在循环中每次迭代都重新获取工作表，这会导致效率低下。
### 改进的代码
以下是改进后的代码，包含详细的注释：
```plsql
PROCEDURE imp_excel_1 IS
-- 定义OLE对象
appli ole2.obj_type;
books ole2.obj_type;
book ole2.obj_type;
sheet ole2.obj_type;
cell ole2.obj_type;
args ole2.list_type;
filename VARCHAR2(1000);
v1 VARCHAR2(100); -- 注意：代码中未使用v1
v2 NUMBER;
v3 VARCHAR2(100);
v4 VARCHAR2(100);
v5 VARCHAR2(100);
v6 VARCHAR2(100);
v7 DATE;
v8 VARCHAR2(100);
v9 VARCHAR2(100);
v10 NUMBER;
m NUMBER(4); -- 行计数器
n NUMBER(2); -- 工作表索引
cnt NUMBER;
seq NUMBER;
v_seq NUMBER;
-- 用于临时存储日期字符串
v7_str VARCHAR2(20);
-- 用于记录处理的行数
row_count NUMBER := 0;
-- 子过程：释放所有OLE资源
PROCEDURE release_resources IS
BEGIN
IF sheet IS NOT NULL THEN
ole2.release_obj(sheet);
sheet := NULL;
END IF;
IF book IS NOT NULL THEN
ole2.invoke(book, 'Close');
ole2.release_obj(book);
book := NULL;
END IF;
IF books IS NOT NULL THEN
ole2.release_obj(books);
books := NULL;
END IF;
IF appli IS NOT NULL THEN
ole2.invoke(appli, 'Quit');
ole2.release_obj(appli);
appli := NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL; -- 避免在资源释放时出现异常导致主逻辑异常
END release_resources;
BEGIN
-- 初始化变量
n := :basic.n; -- 工作表索引
m := :basic.m; -- 起始行
-- 获取文件名
filename := get_file_name();
IF filename IS NULL OR filename = '' THEN
message_alert('请选择一个Excel文件');
RAISE FORM_TRIGGER_FAILURE;
END IF;
-- 创建Excel应用对象
appli := ole2.create_obj('Excel.Application');
books := ole2.get_obj_property(appli, 'Workbooks');
-- 打开工作簿
args := ole2.create_arglist;
ole2.add_arg(args, filename);
book := ole2.get_obj_property(books, 'Open', args);
ole2.destroy_arglist(args);
-- 激活工作簿（非必须）
ole2.invoke(book, 'Activate');
-- 获取指定的工作表，只获取一次
args := ole2.create_arglist;
ole2.add_arg(args, n); -- 工作表索引
sheet := ole2.get_obj_property(book, 'Worksheets', args);
ole2.destroy_arglist(args);
-- 主循环：从起始行m开始逐行读取，直到遇到空身份证号（v6）为止
LOOP
m := m + 1; -- 下一行
-- 读取第2列（B列）
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 2);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v2 := TO_NUMBER(ole2.get_char_property(cell, 'Value')); -- 确保转换为数字
ole2.release_obj(cell);
-- 读取第3列（C列）
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 3);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v3 := TRIM(ole2.get_char_property(cell, 'Value'));
ole2.release_obj(cell);
-- 读取第4列（D列）
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 4);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v4 := TRIM(ole2.get_char_property(cell, 'Value'));
ole2.release_obj(cell);
-- 读取第5列（E列）
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 5);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v5 := TRIM(ole2.get_char_property(cell, 'Value')); -- 注意：原本使用get_num_property，但这里可能是字符？
ole2.release_obj(cell);
-- 读取第6列（F列）: 身份证号
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 6);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v6 := TRIM(ole2.get_char_property(cell, 'Value'));
ole2.release_obj(cell);
-- 如果身份证号为空，则退出循环
EXIT WHEN v6 IS NULL;
-- 读取第7列（G列）: 出生日期
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 7);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v7_str := TRIM(ole2.get_char_property(cell, 'Value'));
ole2.release_obj(cell);
-- 将字符串转换为日期，假设格式为'YYYYMMDD'
BEGIN
v7 := TO_DATE(v7_str, 'YYYYMMDD');
EXCEPTION
WHEN OTHERS THEN
message_alert('第' || m || '行的出生日期格式错误: ' || v7_str);
RAISE FORM_TRIGGER_FAILURE;
END;
-- 读取第8列（H列）
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 8);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v8 := TRIM(ole2.get_char_property(cell, 'Value'));
ole2.release_obj(cell);
-- 读取第9列（I列）
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 9);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v9 := TRIM(ole2.get_char_property(cell, 'Value')); -- 原本使用get_num_property，但可能是字符？
ole2.release_obj(cell);
-- 读取第10列（J列）
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 10);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v10 := TO_NUMBER(ole2.get_char_property(cell, 'Value')); -- 转换为数字
ole2.release_obj(cell);
-- 调试信息（可以注释掉，避免频繁弹出）
-- message_alert('v2='||v2||';v3='||v3||';v4='||v4||';v5='||v5||';v6='||v6||';v7='||v7||';v8='||v8||';v9='||v9||';v10='||v10);
-- 更新或插入到表hr0c0
BEGIN
UPDATE hr0c0
SET hr0c0_sim_name = v3,
hr0c0_sex = v4,
hr0c0_nation = v5,
hr0c0_birthday = v7, -- 直接使用转换后的日期
hr0c0_address = v8,
hr0c0_organ = v9,
hr0c0_issue_date = TO_DATE('19000101','YYYYMMDD') + v2 - 2,
hr0c0_expire_date = TO_DATE('19000101','YYYYMMDD') + v10 - 2
WHERE hr0c0_id_no = v6;
IF SQL%NOTFOUND THEN
INSERT INTO hr0c0 (
hr0c0_sim_name, hr0c0_sex, hr0c0_nation, hr0c0_birthday,
hr0c0_address, hr0c0_id_no, hr0c0_organ,
hr0c0_issue_date, hr0c0_expire_date
) VALUES (
v3, v4, v5, v7,
v8, v6, v9,
TO_DATE('19000101','YYYYMMDD') + v2 - 2,
TO_DATE('19000101','YYYYMMDD') + v10 - 2
);
END IF;
row_count := row_count + 1;
EXCEPTION
WHEN OTHERS THEN
message_alert('第' || m || '行处理失败: ' || SQLERRM);
release_resources; -- 释放资源
RAISE FORM_TRIGGER_FAILURE;
END;
END LOOP;
-- 正常结束，释放资源
release_resources;
-- 提交事务
COMMIT;
message_alert('成功导入 ' || row_count || ' 笔身份证资料');
EXCEPTION
WHEN FORM_TRIGGER_FAILURE THEN
-- 触发失败，已经处理过资源释放，这里可以再次释放确保
release_resources;
ROLLBACK;
-- 错误信息已经在前面的异常处理中显示，这里不再显示
RAISE;
WHEN OTHERS THEN
message_alert('导入过程中发生错误: ' || SQLERRM);
release_resources;
ROLLBACK;
RAISE FORM_TRIGGER_FAILURE;
END;
```
### 改进点说明
1. **资源管理**：
- 创建了一个子过程`release_resources`来统一释放资源，并在异常处理和正常结束时调用。
- 在释放资源时，先检查对象是否为空，避免重复释放。
2. **错误处理**：
- 使用`SQLERRM`替换`dbms_error_text`。
- 在日期转换和数据库操作时，添加了异常处理，提供更具体的错误信息（如行号）。
3. **日期转换**：
- 将第7列（出生日期）作为字符串读取，然后使用`TO_DATE`明确转换，避免隐式转换错误。
4. **循环优化**：
- 将获取工作表移出循环，只获取一次。
- 使用`row_count`变量记录成功处理的行数。
5. **其他**：
- 移除了重复的`message_alert`，只保留一处（调试时可打开）。
- 在读取数字时，使用`TO_NUMBER`进行转换，避免类型不匹配。
- 在插入或更新时，直接使用转换后的日期`v7`，而不是先转字符再转日期。
### 注意事项
- 该过程假设Excel文件存在，并且用户有权限访问。
- 工作表索引（n）和起始行（m）由表单的`basic`块提供，确保在调用过程之前已设置。
- 日期列（第7列）必须是'YYYYMMDD'格式，否则转换会失败。
### 相关问题
§§相关问题§§:
1. 如何在Oracle Forms中处理Excel日期序列号（如Excel的日期数字）转换为Oracle日期？
2. 在OLE2自动化Excel时，如何避免Excel进程在异常退出后残留在内存中？
3. 如何提高从Excel导入大量数据到Oracle数据库的性能？
4. 在Oracle Forms中，除了使用OLE2，还有哪些方法可以实现Excel数据的导入？
</think>
以下是针对您提供的 Oracle Forms PL/SQL 过程的全面分析和改进方案。这个过程使用 OLE2 自动化 Excel 导入数据，但存在多个需要优化和修复的问题：
### 主要问题分析
1. **资源管理问题**：
- 未正确释放 OLE 对象（特别是 `appli` 对象）
- 没有处理文件选择取消的情况
- 异常处理中重复释放资源
2. **数据类型转换错误**：
- 日期字段 `v7` 直接赋值为字符串（但声明为 DATE 类型）
- 数字字段使用 `get_char_property` 读取（如 `v5`）
3. **逻辑缺陷**：
- 循环内每次迭代都重新获取工作表对象（低效）
- 未处理 Excel 启动失败的情况
- 未处理空行或无效数据的情况
4. **异常处理不完善**：
- 使用非标准的 `dbms_error_text` 函数
- 缺少详细错误信息
- 事务处理不完整
### 改进后的完整代码
```plsql
PROCEDURE imp_excel_1 IS
appli ole2.obj_type;
books ole2.obj_type;
book ole2.obj_type;
sheet ole2.obj_type;
cell ole2.obj_type;
args ole2.list_type;
filename VARCHAR2(1000);
v2 NUMBER;
v3 VARCHAR2(100);
v4 VARCHAR2(100);
v5 VARCHAR2(100);
v6 VARCHAR2(100);
v7 DATE;
v8 VARCHAR2(100);
v9 VARCHAR2(100);
v10 NUMBER;
m NUMBER := :basic.m;
n NUMBER := :basic.n;
row_count NUMBER := 0;
temp_str VARCHAR2(100);
-- 子过程：安全关闭Excel资源
PROCEDURE close_excel IS
BEGIN
IF sheet IS NOT NULL THEN
ole2.release_obj(sheet);
sheet := NULL;
END IF;
IF book IS NOT NULL THEN
ole2.invoke(book, 'Close');
ole2.release_obj(book);
book := NULL;
END IF;
IF books IS NOT NULL THEN
ole2.release_obj(books);
books := NULL;
END IF;
IF appli IS NOT NULL THEN
ole2.invoke(appli, 'Quit');
ole2.release_obj(appli);
appli := NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN NULL; -- 避免二次异常
END;
BEGIN
-- ========== 1. 初始化检查 ==========
IF NOT client_host('echo') THEN
message_alert('此功能仅能在客户端执行！');
RAISE FORM_TRIGGER_FAILURE;
END IF;
-- ========== 2. 获取Excel文件 ==========
filename := get_file_name();
IF filename IS NULL OR TRIM(filename) = '' THEN
message_alert('文件选择已取消');
RAISE FORM_TRIGGER_FAILURE;
END IF;
-- ========== 3. 启动Excel应用 ==========
appli := ole2.create_obj('Excel.Application');
IF appli IS NULL THEN
message_alert('无法启动Excel，请确认已安装Microsoft Office');
RAISE FORM_TRIGGER_FAILURE;
END IF;
ole2.set_property(appli, 'Visible', FALSE); -- 隐藏Excel窗口
-- ========== 4. 打开工作簿 ==========
books := ole2.get_obj_property(appli, 'Workbooks');
args := ole2.create_arglist;
ole2.add_arg(args, filename);
book := ole2.get_obj_property(books, 'Open', args);
ole2.destroy_arglist(args);
-- ========== 5. 获取工作表（只获取一次） ==========
args := ole2.create_arglist;
ole2.add_arg(args, n);
sheet := ole2.get_obj_property(book, 'Worksheets', args);
ole2.destroy_arglist(args);
-- ========== 6. 主循环处理数据 ==========
LOOP
m := m + 1; -- 移动到下一行
-- >> 读取B列 (v2)
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 2);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v2 := ole2.get_num_property(cell, 'Value'); -- 数字字段
ole2.release_obj(cell);
-- >> 读取C列 (v3)
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 3);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v3 := TRIM(ole2.get_char_property(cell, 'Value'));
ole2.release_obj(cell);
-- >> 读取D列 (v4)
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 4);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v4 := TRIM(ole2.get_char_property(cell, 'Value'));
ole2.release_obj(cell);
-- >> 读取E列 (v5) - 修正为字符读取
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 5);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v5 := TRIM(ole2.get_char_property(cell, 'Value')); -- 修正为字符
ole2.release_obj(cell);
-- >> 读取F列 (v6) - 退出条件
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 6);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v6 := TRIM(ole2.get_char_property(cell, 'Value'));
ole2.release_obj(cell);
-- 退出条件：身份证号为空
EXIT WHEN v6 IS NULL;
-- >> 读取G列 (v7) - 日期处理
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 7);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
temp_str := TRIM(ole2.get_char_property(cell, 'Value'));
ole2.release_obj(cell);
-- 日期转换（假设格式为YYYYMMDD）
BEGIN
v7 := TO_DATE(temp_str, 'YYYYMMDD');
EXCEPTION
WHEN OTHERS THEN
v7 := NULL; -- 或提供默认值
END;
-- >> 读取H列 (v8)
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 8);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v8 := TRIM(ole2.get_char_property(cell, 'Value'));
ole2.release_obj(cell);
-- >> 读取I列 (v9)
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 9);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v9 := TRIM(ole2.get_char_property(cell, 'Value')); -- 修正为字符
ole2.release_obj(cell);
-- >> 读取J列 (v10)
args := ole2.create_arglist;
ole2.add_arg(args, m);
ole2.add_arg(args, 10);
cell := ole2.get_obj_property(sheet, 'Cells', args);
ole2.destroy_arglist(args);
v10 := ole2.get_num_property(cell, 'Value'); -- 数字字段
ole2.release_obj(cell);
-- ========== 7. 数据库操作 ==========
BEGIN
UPDATE hr0c0
SET hr0c0_sim_name = v3,
hr0c0_sex = v4,
hr0c0_nation = v5,
hr0c0_birthday = v7,
hr0c0_address = v8,
hr0c0_organ = v9,
hr0c0_issue_date = TO_DATE('19000101','YYYYMMDD') + v2 - 2,
hr0c0_expire_date = TO_DATE('19000101','YYYYMMDD') + v10 - 2
WHERE hr0c0_id_no = v6;
IF SQL%NOTFOUND THEN
INSERT INTO hr0c0 (
hr0c0_sim_name, hr0c0_sex, hr0c0_nation, hr0c0_birthday,
hr0c0_address, hr0c0_id_no, hr0c0_organ,
hr0c0_issue_date, hr0c0_expire_date
) VALUES (
v3, v4, v5, v7, v8, v6, v9,
TO_DATE('19000101','YYYYMMDD') + v2 - 2,
TO_DATE('19000101','YYYYMMDD') + v10 - 2
);
END IF;
row_count := row_count + 1;
EXCEPTION
WHEN OTHERS THEN
message_alert('行号 ' || m || ' 错误: ' || SQLERRM);
close_excel;
ROLLBACK;
RAISE FORM_TRIGGER_FAILURE;
END;
END LOOP;
-- ========== 8. 成功完成处理 ==========
close_excel;
COMMIT;
message_alert('成功导入 ' || row_count || ' 笔身份证资料');
EXCEPTION
WHEN FORM_TRIGGER_FAILURE THEN
ROLLBACK;
-- 错误信息已在前面显示
WHEN OTHERS THEN
message_alert('未预期错误: ' || SQLERRM);
close_excel;
ROLLBACK;
RAISE FORM_TRIGGER_FAILURE;
END;
```
### 关键改进说明
1. **资源管理优化**：
- 创建了 `close_excel` 子过程统一处理资源释放
- 确保所有 OLE 对象都被正确释放
- 添加了文件选择取消的检查
2. **数据类型处理**：
- 使用 `get_num_property` 读取数字字段（v2, v10）
- 使用 `get_char_property` 读取文本字段（v3, v4, v5 等）
- 添加了日期转换逻辑（YYYYMMDD 格式）
3. **性能优化**：
- 工作表对象 `sheet` 在循环外只获取一次
- 移除了调试用的 `message_alert`（生产环境应移除）
- 添加了行计数器 `row_count`
4. **错误处理增强**：
- 使用标准 `SQLERRM` 替代 `dbms_error_text`
- 添加了详细的错误位置信息（行号）
- 添加了 Excel 启动失败的检查
5. **事务处理**：
- 使用显式 COMMIT 和 ROLLBACK
- 确保异常时回滚所有更改
- 添加了事务计数器
### 使用说明
1. **准备工作**：
- 确保客户端已安装 Microsoft Excel
- 设置 `basic.m` 为起始行号（通常为 2，跳过标题行）
- 设置 `basic.n` 为工作表索引（1 表示第一个工作表）
2. **日期格式**：
- 确保 Excel 中的日期列为 YYYYMMDD 格式文本
- 或修改代码适配其他日期格式
3. **字段映射**：
```plaintext
B列 → v2 → hr0c0_issue_date 相关
C列 → v3 → hr0c0_sim_name
D列 → v4 → hr0c0_sex
E列 → v5 → hr0c0_nation
F列 → v6 → hr0c0_id_no (主键)
G列 → v7 → hr0c0_birthday
H列 → v8 → hr0c0_address
I列 → v9 → hr0c0_organ
J列 → v10 → hr0c0_expire_date 相关
```
### 相关问题
本文介绍了解决从Windows或MacOS传输到Linux文本文件中出现的^M符号问题的方法。^M符号是由不同操作系统间的换行符差异导致的。文章提供了使用dos2unix和mac2unix工具进行转换的具体步骤。
扫一扫
1884
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
