# 存储过程

参考 https://blog.csdn.net/weixin_41968788/article/details/83659164/

## 创建

> 注意：一定不要漏掉了语句末尾的分号
> * DBMS_OUTPUT.PUT_LINE() 的输出结果可在PLSQL的【输出栏】中查看

```sql
-- 创建并替换存储过程
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;

```

## 删除
```sql
-- 删除存储过程
drop procedure simple_procedure;
```

## 调用存储过程

> 调用存储过程的方式有两种：call 和 exec
> exec是sqlplus的命令，只能在sqlplus中使用。和 set serveroutput on 一起用。
> call是sql命令，任何工具都可以使用。

```sql
-- 调用存储过程
call simple_procedure();
```

## 条件语句

> 注意：多个条件分支时，关键词就是 elsif ，而不是 elseif 

```sql
-- 1个条件分支
if ... then
	...
end if;

-- 2个条件分支
if ... then
	...
else 
	...
end if;

-- 多个条件分支
if ... then
	...
elsif ... then
	...
else
	...
end if;
```

## 循环语句

1. Oracle中的for循环用法：

   ```sql
   declare
   begin
      for i in 1..10 Loop
        dbms_output.put_line('i='||i);
     end loop;
   end;
   ```

2. Oracle中while的循环用法：

   ```sql
   declare
   x number;
   begin
    x := 5 ;
    while x>0 loop
      x := x-1; -- 循环的每次处理
    if x = 3 then
      return; -- 跳出循环
    else
       dbms_output.put_line('x的值是'||x);
      end if;
    end loop; 
   end;
   ```


3. Oracle 中的loop循环用法：

   ```sql
   declare
   x number;
   begin
     x:=0;
     loop
       x := x+1;
       exit when x >5 ; -- x大于5是终止
        dbms_output.put_line('结果x是two：'||x);
     end loop;
     end;
   ```

   

## 异常捕获

> 参考：https://www.cnblogs.com/windy2008/p/5329164.html

Oracle提供了三种异常处理方式：
1. 预定义异常    用于处理常见的Oracle错误
2. 非预定义异常   用于处理预定义异常所不能处理的Oracle错误
3. 自定义异常    用于处理于Oracle错误无关的其他情况

**语法**

```sql
-- 捕获异常
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  | 由于发生死锁事务被撤消                             |



### 非预定义异常

使用非预定义异常包括三步：
1. 在定义部分定义异常名，
2. 在异常和Oracle错误之间建立关联，
3. 在异常处理部分捕捉并处理异常。 

当定义Oracle错误和异常之间的关联关系时，需要使用伪过程EXCEPTION_INIT。
1. 首先的定义部分定义异常，
2. 使用 progma exception_init(exception_name, exception_number) 在异常和oracle错误之间建立关联，这时要求用户知道可能出现的错误号（异常函数sqlcode、sqlerrm和 raise_application_error）；[参考oracle错误号表](https://www.cnblogs.com/haicheng92/p/16055940.html)
3. 最终在异常处理部分捕捉并处理异常。

以ORA-02291错误（违反完整约束条件 (.) - 未找到父项关键字）为例，为此先创建两张表

```sql
-- 部门表
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 错误

```sql
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: 违反完整约束条件 (.) - 已找到子记录日志

```sql
-- 首先插入两个数据
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错误没有任何关联，它是由开发人员为特定情况所定义的异常。 

我的理解：处理非预定义异常，就是给已有的错误号起个名；自定义异常则是自定义错误号并起名。

当使用自定义异常时，
1. 需要在定义部分（DECLARE）定义异常，
2. 再执行部分（BEGIN）触发异常（使用RAISE语句），
3. 在异常处理部分（EXCEPTION）捕捉并处理异常。 

以 simple_procedure 为例

```sql
-- 创建并替换存储过程
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);  函数的作用是抛出异常

## 示例

```sql
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;

```



# 包 package
参考 https://www.cnblogs.com/zmztya/p/8534522.html


# 匿名代码快
参考 https://blog.csdn.net/weixin_45848211/article/details/121020159

```sql
DECLARE
	ORDER_STATUS varchar(50);
	ORDER_ID NUMBER;
BEGIN
	ORDER_STATUS := 'FINISHED';
	ORDER_ID := 10001;
	-- 修改订单状态
	update T_ORDER o
	set o.status=ORDER_NUM,
		o.last_update_date=sysdate
	where o.id=ORDER_ID;

	COMMIT;
END;
```