Oracle 存储过程使用总结
2022/3/25 19:22:43
本文主要是介绍Oracle 存储过程使用总结,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
参考 https://blog.csdn.net/weixin_41968788/article/details/83659164/
创建
注意:一定不要漏掉了语句末尾的分号
- DBMS_OUTPUT.PUT_LINE() 的输出结果可在PLSQL的【输出栏】中查看
-- 创建并替换存储过程 CREATE OR REPLACE PROCEDURE simple_procedure -- 定义参数 ( name IN VARCHAR, birth_year IN NUMBER ) AS -- 声明变量 current_year NUMBER(4) := 2022; age NUMBER(2); remark VARCHAR(255); BEGIN current_year := to_number(to_char(sysdate, 'yyyy')); age := current_year - birth_year; remark := name || '今年' || age || '岁'; -- 条件语句 IF age < 18 THEN remark := remark || ':未成年人'; ELSIF age < 60 THEN remark := remark || ':青年人'; ELSE remark := remark || ':老年人'; END IF; DBMS_OUTPUT.PUT_LINE(remark); -- 捕获异常 EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END;
调用存储过程
调用存储过程的方式有两种:call 和 exec
exec是sqlplus的命令,只能在sqlplus中使用。和 set serveroutput on 一起用。
call是sql命令,任何工具都可以使用。
-- 调用存储过程 call simple_procedure();
条件语句
注意:多个条件分支时,关键词就是 elsif ,而不是 elseif
-- 1个条件分支 if ... then ... end if; -- 2个条件分支 if ... then ... else ... end if; -- 多个条件分支 if ... then ... elsif ... then ... else ... end if;
异常捕获
参考:https://www.cnblogs.com/windy2008/p/5329164.html
Oracle提供了三种异常处理方式:
- 预定义异常 用于处理常见的Oracle错误
- 非预定义异常 用于处理预定义异常所不能处理的Oracle错误
- 自定义异常 用于处理于Oracle错误无关的其他情况
语法
-- 捕获异常 EXCEPTION WHEN EXCEPTION_NAME THEN ... WHEN OTHERS THEN ...
预定的21种异常类型
对预定义异常情况的处理,只需在PL/SQL块的异常处理部分,直接引用相应的异常情况名,并对其完成相应的异常错误处理即可。
错误号 | 命名的系统异常 | 产生原因 |
---|---|---|
ORA-6530 | access_into_null | 试图为null 对象的属性赋值 |
case_not_found | case中若未包含相应的when,并且没有设置 | |
ORA-6531 | collection_is_null | 集合元素未初始化 |
ORA-6511 | curser_already_open | 试图打开一个已处于打开状态的游标 |
ORA-0001 | dup_val_on_index | 违反了唯一性限制 |
ORA-1001 | invalid_cursor | 试图使用一个无效的游标 |
ORA-1722 | invalid_number | 转换一个数字失败 |
ORA-1403 | no_data_found | 使用 select into 未返回行,或应用索引表未初始化的 |
ORA-1422 | too_many_rows | 执行 select into 时,结果集超过一行 |
ORA-1476 | zero_divide | 试图被零除 |
ORA-6533 | subscript_beyond_count | 元素下标超过嵌套表或varray的最大值 |
ORA-6533 | subscript_outside_limit | 使用嵌套表或 varray 时,将下标指定为负数 |
ORA-6502 | value_error | 赋值时,变量长度不足以容纳实际数据 |
ORA-1017 | login_denied | 无效的用户名/口令 |
ORA-1012 | not_logged_on | 没有连接到ORACLE |
ORA-6501 | program_error | 内部错误 |
ORA-6504 | rowtype_mismatch | 主游标变量与 pl/sql 游标变量的返回类型不兼容 |
self_is_null | 使用对象类型时,在 null 对象上调用对象方法 | |
ORA-6500 | storage_error | 内存不够引发的内部错误 |
sys_invalid_id | 无效的 rowid 字符串 | |
ORA-0051 | timeout_on_resource | 在等待资源时发生超时 |
ORA-0061 | Transaction-backed-out | 由于发生死锁事务被撤消 |
非预定义异常
使用非预定义异常包括三步:
- 在定义部分定义异常名,
- 在异常和Oracle错误之间建立关联,
- 在异常处理部分捕捉并处理异常。
当定义Oracle错误和异常之间的关联关系时,需要使用伪过程EXCEPTION_INIT。
- 首先的定义部分定义异常,
- 使用 progma exception_init(exception_name, exception_number) 在异常和oracle错误之间建立关联,这时要求用户知道可能出现的错误号(异常函数sqlcode、sqlerrm和 raise_application_error);参考oracle错误号表
- 最终在异常处理部分捕捉并处理异常。
以ORA-02291错误(违反完整约束条件 (.) - 未找到父项关键字)为例,为此先创建两张表
-- 部门表 CREATE TABLE DEPT ( dept_no VARCHAR2(5) NOT NULL, dept_name VARCHAR2(255) NOT NULL, PRIMARY KEY(dept_no) ); -- 员工表 CREATE TABLE EMP ( emp_no VARCHAR2(8) NOT NULL, emp_name VARCHAR2(20) NOT NULL, dept_no VARCHAR2(5) NOT NULL, PRIMARY KEY(emp_no), FOREIGN KEY(dept_no) REFERENCES DEPT(dept_no) );
来吧,来捕获 ORA-02291 错误
DECLARE e_integrity EXCEPTION; -- 1、定义部分 PRAGMA EXCEPTION_INIT (e_integrity, -2291); -- 2、建立关联关系 BEGIN INSERT INTO emp (emp_no, emp_name, dept_no) values(&empno, &empname, &deptno); EXCEPTION WHEN e_integrity THEN -- 3、捕捉处理 DBMS_OUTPUT.PUT_LINE('该部门不存在'); END;
再来捕获 ORA-02292: 违反完整约束条件 (.) - 已找到子记录日志
-- 首先插入两个数据 insert into DEPT(dept_no, dept_name) values ('D0001', '开发部'); insert into EMP(emp_no, emp_name, dept_no) values('E0001', '张三', 'D0001'); DECLARE e_integrity EXCEPTION; -- 1、定义部分 PRAGMA EXCEPTION_INIT (e_integrity, -2292); -- 2、建立关联关系 BEGIN DELETE FROM DEPT WHERE dept_no='D0001'; EXCEPTION WHEN e_integrity THEN -- 3、捕捉处理 DBMS_OUTPUT.PUT_LINE('该部门下还有员工'); END;
自定义异常
预定义异常和非预定义异常都与Oracle错误有关,并且当出现Oracle错误时会隐含触发相应异常;
而自定义异常与Oracle错误没有任何关联,它是由开发人员为特定情况所定义的异常。
我的理解:处理非预定义异常,就是给已有的错误号起个名;自定义异常则是自定义错误号并起名。
当使用自定义异常时,
- 需要在定义部分(DECLARE)定义异常,
- 再执行部分(BEGIN)触发异常(使用RAISE语句),
- 在异常处理部分(EXCEPTION)捕捉并处理异常。
以 simple_procedure 为例
-- 创建并替换存储过程 CREATE OR REPLACE PROCEDURE simple_procedure -- 定义参数 ( name IN VARCHAR, birth_year IN NUMBER ) AS -- 声明变量 current_year NUMBER(4) := 2022; age NUMBER(2); remark VARCHAR(255); -- 定义变量:错误号 V_sqlcode NUMBER; V_sqlerr VARCHAR(512); -- 定义异常 Null_name EXCEPTION; Null_birth_year EXCEPTION; Invalid_birth_year EXCEPTION; PRAGMA EXCEPTION_INIT(Null_name,-20991); PRAGMA EXCEPTION_INIT(Null_birth_year,-20992); PRAGMA EXCEPTION_INIT(Invalid_birth_year, -20993); BEGIN IF name IS NULL THEN RAISE_APPLICATION_ERROR(-20991, '姓名不能为空'); END IF; IF birth_year IS NULL THEN RAISE_APPLICATION_ERROR(-20992, '出生年份不能为空'); ELSIF birth_year < 1900 THEN RAISE_APPLICATION_ERROR(-20993, '出生年份不能早于1900年'); END IF; current_year := to_number(to_char(sysdate, 'yyyy')); age := current_year - birth_year; remark := name || '今年' || age || '岁'; -- 条件语句 IF age < 18 THEN remark := remark || ':未成年人'; ELSIF age < 60 THEN remark := remark || ':青年人'; ELSE remark := remark || ':老年人'; END IF; DBMS_OUTPUT.PUT_LINE(remark); -- 捕获异常 EXCEPTION WHEN Null_name THEN V_sqlcode :=SQLCODE; V_sqlerr :=SQLERRM; DBMS_OUTPUT.PUT_LINE('错误号:' || V_sqlcode || ',错误信息:' || V_sqlerr); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END;
RAISE_APPLICATION_ERROR(errCode, errMsg); 函数的作用是抛出异常
示例
create or replace procedure P_DATABASE_USED_DISK_SPACE_RECORD AS /****************************************************************************** * 目的:统计数据库表和索引所用磁盘空间大小 ******************************************************************************/ COUNT_ NUMBER(19); V_ERRORCODE VARCHAR2(20); V_ERRORMESSAGE VARCHAR2(900); V_ERRORLINE VARCHAR2(1000); STEP NUMBER(19); BEGIN -- 记录数据库表所占磁盘空间大小 INSERT INTO T_DATABASE_RECORD SELECT SEGMENT_NAME AS OBJECT_NAME, BYTES / 1024 / 1024/ 1024 AS DISK_SPACE, TO_CHAR(SYSDATE,'YYYY-MM-DD') AS RECORD_DATE, 'TABLE' AS TYPE FROM USER_SEGMENTS V WHERE V.SEGMENT_TYPE='TABLE' ORDER BY V.BYTES DESC; -- 记录数据库表索引所占磁盘空间大小 INSERT INTO T_DATABASE_RECORD SELECT SEGMENT_NAME AS OBJECT_NAME, SUM(BYTES) /1024/1024/1024 AS DISK_SPACE, TO_CHAR(SYSDATE,'YYYY-MM-DD') AS RECORD_DATE, 'INDEX' AS TYPE FROM USER_SEGMENTS WHERE SEGMENT_TYPE ='INDEX' GROUP BY SEGMENT_NAME; COUNT_ := SQL%ROWCOUNT; INSERT INTO I_JOB_LOG SELECT INTERFACE_SEQUENCES.NEXTVAL, 'P_DATABASE_RECORD', 1, COUNT_, 'Y', 'success', SYSDATE FROM DUAL; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; V_ERRORCODE := SQLCODE; V_ERRORMESSAGE := SUBSTR(SQLERRM, 1, 160); V_ERRORLINE := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE; --记录错误信息 INSERT INTO I_JOB_LOG SELECT INTERFACE_SEQUENCES.NEXTVAL, 'P_DATABASE_RECORD', STEP, 0, 'N', '失败ErrorCode' || V_ERRORCODE || 'ErrorMessage:' || V_ERRORMESSAGE || ' ' V_ERRORLINE, SYSDATE FROM DUAL; COMMIT; end P_DATABASE_RECORD;
这篇关于Oracle 存储过程使用总结的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-15PingCAP 黄东旭参与 CCF 秀湖会议,共探开源教育未来
- 2024-05-13PingCAP 戴涛:构建面向未来的金融核心系统
- 2024-05-09flutter3.x_macos桌面os实战
- 2024-05-09Rust中的并发性:Sync 和 Send Traits
- 2024-05-08使用Ollama和OpenWebUI在CPU上玩转Meta Llama3-8B
- 2024-05-08完工标准(DoD)与验收条件(AC)究竟有什么不同?
- 2024-05-084万 star 的 NocoDB 在 sealos 上一键起,轻松把数据库编程智能表格
- 2024-05-08Mac 版Stable Diffusion WebUI的安装
- 2024-05-08解锁CodeGeeX智能问答中3项独有的隐藏技能
- 2024-05-08RAG算法优化+新增代码仓库支持,CodeGeeX的@repo功能效果提升