十、Oracle 存储过程

• 一、Oracle 操作集
• 二、Oracle 数据类型
• 三、Oracle 函数
• 四、Oracle 视图
• 五、Oracle 序列
• 六、Oracle 同义词
• 七、Oracle 索引
• 八、Oracle PL/SQL
• 九、Oracle 存储函数
• 十、Oracle 存储过程
• 十一、Oracle 触发器
• 十二、Oracle 查询

---

十、存储过程

• 存储过程 是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。
• 应用程序可以调用 存储过程，执行相应的逻辑。

---

• 存储过程 和 存储函数 都可以封装一定的业务逻辑，并返回结果。
• 存在区别如下：

1. 存储函数 中有返回值，且必须返回。
   存储过程 没有返回值，可以通过传出参数返回多个值。
2. 存储函数 可以在 select语句 中直接使用。
   存储过程 不能，多数是被应用程序所调用。
3. 存储函数 一般都是封装一个查询结果。
   存储过程 一般都封装一段事务代码。

---

• 存储过程 的优点：

1. 可增强SQL语言的 功能 和 灵活性。
2. 良好的封装性。
3. 高性能。
4. 可减少网络流量。
5. 可作为一种安全机制，来确保 数据库的安全性 和 数据的完整性。

---

1. 自定义结束符

• 用户自定义结束符（默认;分号）。

-- 用户自定义结束符（默认`;`分号）。
DELIMTER;

DELIMTER $$

DELIMTER !! 

---

2. 创建存储过程

• 创建 或 修改 存储过程 的语法。

CREATE [OR REPLACE] PROCEDURE '存储过程名' ([IN|OUT|INOUT] '参数名1' '类型1', [..., [IN|OUT|INOUT] '参数名n' '类型n'])
IS|AS
	'变量声明部分';
BEGIN
 	'逻辑部分'
[EXCEPTION '异常处理部分']
END;

• 参数只指定类型，不指定长度。
• 过程参数的三种模式。

过程参数	说明
IN	传入参数【默认】
OUT	传出参数 ，主要用于返回程序运行结果
IN OUT	传入传出参数

-- 创建存储过程`sp_update_sex`，用于实现给一个`客户Id`修改`客户性别`。
CREATE PROCEDURE `sp_update_sex`(IN cid INT, csex CHAR(1))
BEGIN
	UPDATE `customers` SET cust_sex = csex WHERE cust_id = cid;
END $$

---

3. 声明局部变量

• DECLARE 声明局部变量。

1. 只能在 存储过程 体的 BEGIN ... END 语句块中声明。
2. 必须在存储过程的开头声明。
3. 作用范围 仅限于声明它的 BEGIN ... END 语句块。
4. 不同于用户变量。

---

• 局部变量 和 用户变量 的区别。

1. 局部变量声明时，在其前面没有 @ 符号，并且只能被声明它的 BEGIN ... END 语句块中的语句使用。
2. 用户变量声明时，会在其名称前面使用 @ 符号，同时以声明的 用户变量 存在于整个会话之中。

-- 声明变量。
DECLARE 
	'变量名1' type [DEFAULT '值1'];
	['变量名n' type]; 
	
-- 变量赋值。
SET '变量名1' = '值1'[, '变量名n' = '值n'];

DECLARE `cid` INT(10);
SET `cid` = 1001;

---

• SELECT ... INTO 语句，把选定列的值直接存储到局部变量中。

SELECT '列名1'[, ... '列名n'] INTO '变量名1'[, ... '变量名n'] '表名';

---

4. 流程控制语句

-- 条件判断语句。
IF ... THEN ... ELSE

-- CASE语句。
IF '条件' 
THEN '表达式1' 
ELSE '表达式2' 
END IF;

-- 循环语句。
WHILE '条件' '表达式'
END WHILE;

REPEAT '表达式'
END REPEAT;

LOOP '表达式'
END LOOP;

-- 用于退出当前循环，且重新开始一个循环。 
ITERATE 

---

5. CURSOR 游标

• 在 MySQL 中，一条 SELECT ... INTO 语句成功执行后，会返回带有值的一行数据，这行数据可以被读取到 存储过程 中进行处理。
• 然而，在使用 SELECT 语句进行数据检索时，若该语句成功被执行，则会返回一组称为 结果集 的数据行，该 结果集 中可能拥有多行数据，这些数据无法直接被一行一行地进行处理，此时就需要使用 游标。

---

• 游标 是一个被 SELECT 语句检索出来地结果集。在存储了游标后，应用程序 或 用户 就可以根据需要 滚动 或 浏览 其中地数据。

---

-- 声明游标。
DECLARE 
	'游标名' CURSOR FOR 'SELECT语句';
-- 游标名：与表名语法相同。
-- SELECT语句：返回一行 或 多行的数据。 

-- 打开游标。
# 游标声明好，必须打开才能使用，实际是将游标连接到`SELECT`语句返回的结果集中。
# 游标可以被多次打开，由于其他用户 或 应用程序 可能随时更新了数据表，因此每次打开游标的结果集可能会不同。
OPEN '游标名'

-- 读取数据。
FETCH '游标名' INTO '变量名'

-- 关闭游标。
CLOSE '游标';

DELIMITER $$

CREATE PROCEDURE `sp_sumofrow` (OUT ROWS INT)
BEGIN
	DECLARE `cid` INT
	DECLARE `FOUND` BOOLEAN DEFAULT true
	DECLARE `cur_cid` CURSOR FOR SELECT `cus_id` FROM `customers`;
	DECLARE CONTINUE HANDLER FOR NOT FOUND

	SET FOUND = FALSE
	SET ROWS = 0

	OPEN `cur_cid`
	FETCH `cur_id` INTO cid
	WHILE FOUND DO
		SET ROWS = ROWS + 1
	FETCH `cur_cid` INTO cid 
	END WHILE
	CLOSE `cur_cid`
END $$

-- `CONTINUE HANDLER`：  

---

二、案例

---

1. 创建 不带传出参数 的存储过程

• 需求：添加业主信息。

-- 增加业主信息序列
create sequence `seq_owners` start with 11;

-- 增加业主信息的存储过程
create or replace procedure 'pro_owners_add' (
	v_name varchar2,
	v_addressid number,
	v_housenumber varchar2,
	v_watermeter varchar2,
	v_type number
)
is
begin
	insert into 'T_OWNERS'
	values (seq_owners.nextval, v_name, v_addressid, v_housenumber, v_watermeter, sysdate, v_type);
	commit;
end;

---

1.1 PL/SQL 调用 存储过程

call pro_owners_add('王岩', 1, '999', '888', 1);

---

1.2 JDBC 调用 存储过程

public static void add(Owners owners) {
    java.sql.Connection conn = null;
    java.sql.CallableStatement stmt = null;
    
    try {
        conn = BaseDao.getConnection();
        stmt = conn.prepareCall("{call pro_owners_add(?,?,?,?,?)}");
        stmt.setString(1, owners.getName());
        stmt.setLong(2, owners.getAddressid());
        stmt.setString(3, owners.getHousenumber());
        stmt.setString(4, owners.getWatermeter());
        stmt.setLong(5, owners.getOwnertypeid());
        stmt.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        BaseDao.closeAll(null, stmt, conn);
    }
}

---

2. 创建 带传出参数 的存储过程

• 需求：添加业主信息，传出参数为 新增业主的ID。

-- 增加业主信息的存储过程
create or replace procedure 'pro_owners_add_2' (
	v_name in varchar2,
	v_addressid in number,
	v_housenumber in varchar2,
	v_watermeter in varchar2,
	v_type in number,
	v_id out number
)
is
begin
	select seq_owners.nextval into v_id from dual;
	insert into 'T_OWNERS' 
	values (v_id, v_name, v_addressid, v_housenumbr, v_watermeter, sysdate, v_type);
	commit;
end;

---

2.1 PL/SQL 调用 存储过程

declare
	-- 定义传出参数的变量
	v_id number;
begin
	pro_owners_add_2( '骑士梦', 1, '001', '001', 1, v_id );
	-- 输出
	DBMS_OUTPUT.put_line('增加成功，新增ID：'||v_id);
end;

---

2.2 JDBC 调用 存储过程

public static long add(Owners owners) {
    long id = 0;
    java.sql.Connection conn = null;
    java.sql.CallableStatement stmt = null;
    try {
        conn = BaseDao.getConnection();
        stmt = conn.prepareCall("{call pro_owners_add_2(?,?,?,?,?,?)}");
        stmt.setString(1, owners.getName());
        stmt.setLong(2, owners.getAddressid());
        stmt.setString(3, owners.getHousenumber());
        stmt.setString(4, owners.getWatermeter());
        stmt.setLong(5, owners.getOwnertypeid());
        // 注册传出参数类型
        stmt.registerOutParameter(6, OracleTypes.NUMBER);
        stmt.execute();
        // 提取传出参数
        id = stmt.getLong(6);
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        BaseDao.closeAll(null, stmt, conn);
    }
    return id;
}

---