Oracle存储过程怎么写

Oracle 存储过程（Stored Procedure）是存储在数据库中的一组预编译 SQL 和 PL/SQL 代码块，用于封装复杂的业务逻辑。

以下是完整的编写指南，包含基本结构、参数类型、常用逻辑以及完整示例。

1. 基本语法结构

一个标准的存储过程由三部分组成：声明部分、执行部分、异常处理部分。

CREATEORREPLACEPROCEDURE过程名(参数1IN数据类型,-- 输入参数参数2OUT数据类型,-- 输出参数参数3INOUT数据类型-- 输入输出参数)IS-- 【声明部分】定义局部变量、游标、常量等v_variable_name VARCHAR2(100);v_count NUMBER :=0;BEGIN-- 【执行部分】编写具体的 SQL 和业务逻辑SELECTcount(*)INTOv_countFROM表名WHERE条件;IFv_count>0THEN-- 业务逻辑...参数2:='成功';-- 给输出参数赋值ELSE参数2:='失败';ENDIF;EXCEPTION-- 【异常处理部分】捕获并处理错误WHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('未找到数据');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生错误: '||SQLERRM);END过程名;

2. 三种参数模式

3. 实战示例：用户信息处理

假设有一个表USERS(ID, NAME, AGE, CITY)。我们要写一个存储过程：

1. 根据 ID 查询用户。
2. 如果用户年龄大于 18 岁且城市包含“市”，则去掉城市后缀（类似你之前的 JS 逻辑）。
3. 返回处理后的城市和状态消息。

CREATEORREPLACEPROCEDUREPROC_UPDATE_USER_CITY(p_user_idINNUMBER,-- 输入：用户IDp_city_outOUTVARCHAR2,-- 输出：处理后的城市p_msgOUTVARCHAR2-- 输出：执行消息)ISv_age NUMBER;v_city VARCHAR2(100);v_name VARCHAR2(100);BEGIN-- 1. 查询数据SELECTage,city,nameINTOv_age,v_city,v_nameFROMUSERSWHEREid=p_user_id;-- 2. 业务逻辑判断IFv_age>18THEN-- 模拟去除城市后缀逻辑IFINSTR(v_city,'市')>0ANDv_cityNOTLIKE'%自治州%'THENv_city :=REPLACE(v_city,'市','');ENDIF;p_msg :='用户 '||v_name||' 处理成功，新城市为：'||v_city;ELSEp_msg :='用户未成年，不处理城市信息。';ENDIF;-- 3. 赋值给输出参数p_city_out :=v_city;EXCEPTIONWHENNO_DATA_FOUNDTHENp_msg :='错误：未找到 ID 为 '||p_user_id||' 的用户';p_city_out :=NULL;WHENOTHERSTHENp_msg :='系统异常：'||SQLERRM;p_city_out :=NULL;-- 可选：记录日志到日志表-- INSERT INTO error_log VALUES (SYSDATE, SQLERRM);ENDPROC_UPDATE_USER_CITY;

4. 如何调用存储过程

方法 A：在 SQL Developer / PLSQL Developer 中测试

DECLAREv_result_city VARCHAR2(100);v_message VARCHAR2(200);BEGIN-- 调用过程PROC_UPDATE_USER_CITY(p_user_id=>1001,-- 传入具体值p_city_out=>v_result_city,-- 传入变量接收p_msg=>v_message);-- 打印结果DBMS_OUTPUT.PUT_LINE(v_message);DBMS_OUTPUT.PUT_LINE('处理后城市：'||v_result_city);END;

注意：使用前需开启输出显示SET SERVEROUTPUT ON;

方法 B：在 Java / Python / Kettle 中调用

• Java (JDBC):使用CallableStatement。
• Kettle:使用 “Call DB Procedure” 组件，选择刚才创建的过程名，映射输入输出参数即可。

5. 常用技巧与注意事项

1. CREATE OR REPLACE:
   • 始终加上OR REPLACE，这样如果过程已存在，会直接覆盖更新，而不会报错。
2. 变量赋值:
   • 使用:=进行赋值（如v_count := 1;）。
   • 使用SELECT ... INTO ...将查询结果赋给变量。
3. 动态 SQL:
   • 如果表名或列名需要动态变化，使用EXECUTE IMMEDIATE。

   EXECUTEIMMEDIATE'UPDATE '||v_table_name||' SET status=1 WHERE id=:1'USINGp_id;

4. 事务控制:
   • 存储过程内部通常不写COMMIT或ROLLBACK，除非明确设计为独立事务。最好由调用者（外部程序）决定何时提交事务，以保持灵活性。
5. 调试:
   • 使用DBMS_OUTPUT.PUT_LINE('变量值：' || v_var);打印调试信息。

6. 常见错误排查

• ORA-06550 / PLS-00201: 标识符必须声明（检查变量名拼写，或是否有权限访问该表）。
• ORA-01403: 未找到数据（SELECT INTO没查到数据，需在EXCEPTION中捕获NO_DATA_FOUND）。
• ORA-01422: 返回多于请求的行数（SELECT INTO查出了多行，但变量只能存一行，需改用游标CURSOR）。

如果你需要将之前 Kettle 中的 JavaScript 清洗逻辑迁移到 Oracle 存储过程中，我可以帮你直接转换那段代码。