PlSql存储过程基础

Oracle数据库封装了 PLSQL语言。因此若想畅游 Oracle，必须先学会 PLSql

1. 什么是PLSQL语言

PLSQL全称Procedure Language SQL，是Oracle对SQL的一种过程化拓展，在SQL命令语言中增加了过程处理语句（如分支，循环等），是SQL语言具有过程处理的能力。

把复杂的业务逻辑放到数据库中处理，能够减少数据库和服务之间的网络交互，提高执行效率。

PLSQL是一种不区分大小写的语言（大小写不敏感）

2. PLSQL程序结构

一个程序分为三个部分：

DECLARE
	--声明(变量,游标)
BEGIN
	-- 执行体(业务逻辑)
	-- [异常处理]
END;

其中DECLARE部分在不用声明变量、游标的情况下可以省略。

2.1 第一个程序 HelloWord:

DECLARE
	--Local variables here
	I INTEGER:
BEGIN
	--在`DBMS Output`窗口打印 "hello world"
	DBMS_OUTPUT.PUT_LINE ('hello world'); 
END;

DBMS_OUTPUT是Oracle内置的程序包，.PUT_LINE()是包内函数

2.2 执行程序

2.2.1 在工具中执行

在工具中，直接点击编辑窗口的执行按钮即可。

2.2.2 在sqlplus客户端中执行(命令行)

在命令行中，执行程序需要在程序最后输入/表示程序结束，这样回车才会执行程序，否则会认为程序还没结束而换行。

连接到：
Oracle Database 10g Express Edition Release 10.2.0.1.0-Production

SQL> begin
  2   DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
  3  END;
  4  /
HELLO WORLD
PL/SQL过程已成功完成。

默认情况下，sqlplus客户端默认关闭打印输出，因此没有显示打印结果，可以通过命令set serveroutput on开启：

SQL> set serveroutput on
SQL> begin
  2   DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
  3  END;
  4  /
HELLO WORLD

PL/SQL 过程已成功完成。

3. 变量

1. 普通数据类型：char、varchar2、date、number、boolean、long
2. 特殊变量类型：引用型变量、记录型变量

变量声明格式：

• 变量名 变量类型（变量长度） 例如：v_name varchar2(20);

变量赋值格式：

• 直接赋值：v_name := 'zhangsan'
• 语句赋值：select 值 into 变量

3.1 普通变量

【示例】打印人员个人信甚，包括：姓名、薪水、地址

DECLARE
    -- 姓名
    v_name VARCHAR2(20) := 'Haydon';
    -- 薪水
    v_sal NUMBER;
    -- 地址
    v_addr VARCHAR2(200);
BEGIN
    --直接赋值
    v_sal := 8888;
    --语句赋俏
    SELECT 'xxx' INTO v_addr FROM DUAL;
    --打印输出
    DBMS_OUTPUT.PUT_LINE('姓名：'|| v_name ||'薪水：'|| v_sal ||'地址：' || v_addr);
END;

执行结果：

3.2 引用型变量

变量的类型和长度取决于表中字段的类型和长度
通过表名.列名%TYPE指定变量的类型和长度，例如：v_name emp.ename%TYPE;

【示例】查询emp表中7839号员工的个人信息，打印姓名和薪水

DECLARE
    v_name emp.ename%TYPE;
    v_sal emp.sal%TYPE;
BEGIN
   SELECT ENAME,SAL INTO v_name,v_sal FROM EMP WHERE EMPNO = 7839;
    --打印输出
    DBMS_OUTPUT.PUT_LINE('姓名：'|| v_name ||'薪水：'|| v_sal ||'地址：' || v_addr);
END;

引用型变量的好处：
使用普通变量定义方式，需要知道表中列的类型，而使用弱引用类型，不需要考虑列的类型，使用%TYPE是非常好的编程风格，因为它使得PL/SQL更加灵活，更加适应于对数据库定义的更新。

3.3 记录型变量

接受表中的一整行记录，相当于Java中的一个对象
语法：变量名称 表名%ROWTYPE，例如：v_emp emp%ROWTYPE;

【示例】查询并特打印7839号员工的姓名和薪水

DECLARE
	-- 记录型变量
    v_emp EMP%ROWTYPE;
BEGIN
   SELECT * INTO v_emp FROM EMP WHERE EMPNO = 7839;
    --打印输出
    DBMS_OUTPUT.PUT_LINE('姓名：'|| v_emp.ename ||'薪水：'|| v_emp.sal ||'地址：' || v_emp.addr);
END;

1. 如果有一个表，有100个字段，那么你程序如果要使用这100字段话，如果你使用弱引用型变量一个个声明，会特别麻烦，记录型变量可以方便的解决这个问题
2. 因为记录型变量是存储一行数据的变量，因此不能指定查询字段，要用*，否则报错
3. 因为记录型变量是存储一行数据的变量，因为查询的结果不能>1，否则报错

4. 流程控制

4.1 条件分支

BEGIN
	IF 条件1 THEN 执行1
	ELSIF 条件2 THEN 执行2
	ELSE 执行3
	END IF;
END;

注意ELSIF是少一个e的。

【示例】

4.2 循环

loop循环：
【示例】打印1~10

DECLARE
	--声明循环变量
	V_NUM NUMBER :1;
BEGIN
	LOOP
		EXIT WHEN V_NUM > 10;
		DBMS_OUTPUT.PUT_LINE (V_NUM);
		--循环变量的自增
		V_NUM := V_NUM + 1;
	END LOOP;
END:

5. 游标

5.1 定义

用于临时存储一个查询返回的多行数据（结果集，类似于Java的Jdbc连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。
游标的使用方式：声明 一> 打开 一> 读取 一> 关闭

5.2 语法

声明游标：CURSOR 游标名[(参数列表)] IS 查询语句;
开启游标：OPEN 游标名;
游标取值：FETCH 游标名 NTO 变量列表;
关闭游标：CLOSE 游标名;

5.3 游标的属性

属性	返回值类型	说明
%ROWCOUNT	整形	获得 FETCH 语句返回的数据行数
%FOUND	布尔型	最近的 FETCH 语句能拿到数据则为真，否则为假
%NOTFOUND	布尔型	与 %FOUND 属性返回值相反
%ISOPEN	布尔型	游标已经打开时值为真，否则为假

其中%NOTFOUND是在游标中找不到元素的时候返回TRUE,通常用来判断退出循环，他的默认值是false，因此用%NOTFOUND作判断之前，应该先执行 FETCH 语句使%NOTFOUND带上真实的值。

5.4 创建和使用

【示例】使用游标查询 emp 表中所有员工的姓名和工资，并将其依次打印出来。

-- 使用游标查诡m表中所有员工的名和工资，并将其依次打印出来。
DECLARE
	-- 声明游标
	CURSOR c_emp IS SELECT ename,sal FROM emp;
	-- 声明变量接受游标中的数据
	v_ename emp.enames%TYPE;
	v_sal emp.sal%TYPE;
BEGIN
	-- 打开游标
	OPEN c_emp;
	-- 遍历游标
	LOOP
		--获取游标中的数据
		FETCH c_emp INTO v_ename,v_sal;
		--退出循环条件
		EXIT WHEN c_emp%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_ename || '-' || v_sal);
	END LOOP;
	--关闭游标
	CLOSE c_emp;
END;

游标（cursor）通常使用循环来遍历结果集，而循环通常使用 LOOP 语句来实现。但是，如果您只想检查或访问结果集的第一行或一组行，则无需使用循环。

DECLARE
  CURSOR c1 IS SELECT * FROM employees WHERE department_id = 50;
  emp_id NUMBER;
BEGIN
  OPEN c1;
  FETCH c1 INTO emp_id; --拿第一行数据
  IF c1%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No employees found in department 50');
  END IF;
  CLOSE c1;
END;

在这个例子中，游标中的第一行被获取并存储在 emp_id 变量中。
然后，使用 IF 语句来检查是否有结果，并根据结果输出不同的消息。
虽然这里没有使用循环，但这个代码段仍然使用了游标来查询员工表中的数据。

如果想要访问游标结果集中的第二行或后续行，则需要使用 LOOP 循环语句。

也可以使用for循环来遍历游标

DECLARE
    CURSOR employees_cur IS
        SELECT employee_id, first_name, last_name
        FROM employees;
        
    v_employee_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
BEGIN
    FOR emp IN employees_cur LOOP
        -- Fetch values from the cursor into variables
        v_employee_id := emp.employee_id;
        v_first_name := emp.first_name;
        v_last_name := emp.last_name;
        
        -- Perform operations on each row
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
        DBMS_OUTPUT.PUT_LINE('Name: ' || v_first_name || ' ' || v_last_name);
        DBMS_OUTPUT.PUT_LINE('-------------------');
    END LOOP;
END;

在这个例子中，没有显示的用OPEN语句打开游标
因为用for循环编译游标的时候，会隐式打开游标，不用自己手动打开
也没有显式地用FETCH语句来获取数据，而是直接用row.field的方式获取属性值

以上两种类型的游标循环的主要区别在于语法和它们处理从游标获取数据的方式。

1. 第一种类型的游标循环使用带有EXIT WHEN条件的loop语句来控制循环。
   它使用FETCH语句从游标中获取数据，并将获取的值分配给变量。
   然后，它检查游标的NOTFOUND属性，以确定是否有更多的行需要获取。
   当需要对循环进行更多控制并希望手动处理获取和退出条件时，这种类型的循环非常有用。
2. 第二种类型的游标循环使用FOR emp IN employees_cur loop语法。
   这种类型的循环自动从游标中获取数据并将其分配给循环变量(在本例中为emp)。
   它消除了显式获取和检查退出条件的需要。
   这种类型的循环更简单、更简洁，特别是当您不需要显式地处理获取和退出条件时。

在第二种情况下，使用循环是因为它允许您遍历游标返回的每一行，并分别对每一行执行操作。循环自动从游标中获取数据并将其分配给循环变量(emp)。这简化了代码并消除了显式获取和退出条件的需要,提供了一种更结构化和可读的方法来迭代游标结果。

5.5 带参游标

【示例】使用游标查询并打印某部门的员工的姓和薪资，部门编号为运行时手动输入。

-- 使用游标查诡m表中所有员工的名和工资，并将其依次打印出来。
DECLARE
	-- 声明含参游标
	CURSOR c_emp(v_deptno emp.deptno$TYPE) IS SELECT ename,sal FROM emp WHERE deptno = v_deptno;
	-- 声明变量接受游标中的数据
	v_ename emp.enames%TYPE;
	v_sal emp.sal%TYPE;
BEGIN
	-- 打开游标,同时传入参数
	OPEN c_emp(10);
	-- 遍历游标
	LOOP
		--获取游标中的数据
		FETCH c_emp INTO v_ename,v_sal;
		--退出循环条件
		EXIT WHEN c_emp%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_ename || '-' || v_sal);
	END LOOP;
	--关闭游标
	CLOSE c_emp;
END;

6. 存储过程⭐

6.1 概念

之前我们编写的PLSQL程序可以进行表的操作，判断，循环逻辑处理的工作，但无法重复调用。

可以理解之前的代码全都编写在了main方法中，是匿名程序。JAVA可以通过封装对像和方法来解决程序的复用问题。

PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用，这些被存储起来的PLSQL程序称之为 存储过程

存储过程作用：

1. 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭连接和关闭是很耗费资源)，需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务，可以大大提高效率。
2. ORACLE官方给的建议：能够让数据库操作的不要放在程序中 。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误.（如果在数据库中操作数据，可以有一定的日志恢复等功能.）

很奇怪，因为之前没有用过Oracle数据库，通常都是使用Mysql配合Mybatis/Mybatis puls做持久层操作，连接数据库就是做一些比较基础的增删改查，遍历封装什么的存储过程都放到应用程序的service里面做，说是减少数据库的负担云云。
·
而Oracle官方却提倡把存储过程复杂业务放到数据库中做，减少连接次数，提升效率，保障安全。
·
在使用Oracle的时候，Oracle是推荐尽可能将所有的操作都放在Plsql中的，但是在使用Mysql的时候是不让使用存储过程的，为什么？
·
下面这个回答感觉是这种分歧的主因：
·
因为在Mysql中，存储过程不是一次编译的，而是对每个会话在执行的时候编译一次，在会话结束的时候抛弃，而且在执行alter/procedure的时候是会将所有会话对这个存储过程持有的存储过程的版本抛弃掉。同时没有包的概念，没有类库，没有面向对象的概念，所以相对Oracle来说，Mysql额存储过程不是很好的选择。

6.2 语法

CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] IS
BEGIN
	-- 业务
END [过程名称];

根据参数的类型，我们将其分为3类：

• 不带参数的
• 带输入参数的
• 带输入输出参数(返回值)的

6.3 无参存储

创建存储过程（这里用的Oracle Sql Developer）

在过程文件夹右键 → 新建过程

选择方案，输入名称

编辑过程代码

CREATE OR REPLACE PROCEDURE PRO_HELLO IS 
	--声明变量
BEGIN
	dbms_output.put_line ('hello world');
END PRO_HELLO;

注意
第一个问题：is和as是可以互用的，用哪个都没关系
第二个问题：过程中可以不用declare关键字

保存即可

直接测试：

如果是在sqlplus上用 exec 指令调用：

C:\Users\82009816.GZCASCADE>sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on 星期三 5月 10 10:48:34 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

请输入用户名:  DMG
输入口令:
上次成功登录时间: 星期三 5月  10 2023 10:48:16 +08:00

连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> set serveroutput on;
SQL> exec pro_hello
hello world

PL/SQL 过程已成功完成。

6.4 带输入参数的存储过程

【示例】查询并用打印某个员工（如7839号员工）的姓名和薪水。要求，调用的时候传入员工编号，自动控制台打印，

CREATE OR REPLACE PROCEDURE P_QUERYNAMEANDSAL(I_EMPNO IN EMP.EMPNO%TYPE) IS
	--声明变量接授查询结果
	V_ENAME EMP.ENAME%TYPE;
	V_SAL   EMP.SAL%TYPE;
BEGIN
	--根据用户传递的员工号查询姓名和蕲水
	SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO = I_EMPNO;
	--打印结果
	DBMS_OUTPUT.PUT_LINE('姓名：' || V_ENAME || ',薪水：'|| V_SAL);
END P_QUERYNAMEANDSAL;

参数包括 IN OUT INOUT三种，对应单纯输入、单纯输出、输入输出作用。

6.5 带输出参数的存储过程

【示例】输入员工号查询某个员工(7839号员工)信息，要求，将薪水作为返回值输出，给调用的程序使用

--输入员工号查询某个员工(7839号员工)信息，要求，将薪水作为返回值输出，给调用的程序使用
CREATE OR REPLACE PROCEDURE P_QUERYSAL_OUT(
			I_EMPNO IN EMP.EMPNO%TYPE,
			O_SAL OUT EMP.SAL%TYPE) IS
BEGIN
	SELECT SAL INTO O_SAL FROM EMP WHERE EMPNO = I_EMPNO;
END P_QUERYSAL_OUT;

PLSQL工具调用：

--Created on 2018/3/22 by ADMINISTRATOR
declare
--明变量接受存储过军中的榆出参数
	v_sal emp.sal&TYPE;
BEGIN
	p_querysal_out(7839,v_sal)
	dbms_output.put_line(v_sal)：
END;

6.6 异常处理

在一个procedure中，通常都包含着异常处理部分

我们可以声明自己的异常，并在处理过程中手动抛出，然后在异常捕捉中做相应的处理。

【示例】

    PROCEDURE pro_myprocedure_test (v_status OUT VARCHAR2) AS
    	-- 声明自定义异常
        MYEXCEPTION_ONE EXCEPTION; 
        MYEXCEPTION_TWO EXCEPTION;	
        MYEXCEPTION_THREE EXCEPTION;	
    BEGIN
        IF 条件1
        THEN RAISE EXC_REQUIRED_PARAM_EMPTY; -- 抛出自定义异常
        END IF;
        IF 条件2 THEN RAISE EXC_DELIVERY_COMPLETED;   -- 抛出自定义异常
        ELSIF 条件3 THEN RAISE EXC_DELIVERY_CANCELED; -- 抛出自定义异常
        END IF;
        v_status := 0;
    -- 异常捕捉处理
    EXCEPTION
        WHEN MYEXCEPTION_ONE THEN   -- 处理 MYEXCEPTION_ONE 异常
            ROLLBACK;
            v_status := 1;
        WHEN MYEXCEPTION_TWO THEN   -- 处理 MYEXCEPTION_TWO 异常
            ROLLBACK;
            v_status := 2;
        WHEN MYEXCEPTION_THREE THEN	-- 处理 MYEXCEPTION_THREE 异常
            ROLLBACK;
            v_status := 3;
        WHEN OTHERS THEN        	-- 其他异常
            ROLLBACK;
            v_status := 4;
    END pro_myprocedure_test;

6.7 存储过程与函数的区别

存储过程Procedure和函数Function有许多差异，其中

• 参数方面：

  存储过程Procedure可以有出参，但是不能直接返回值，也就是说存储过程都是通过出参来传递结果的。

  PROCEDURE pro_test(	v_id			IN VARCHAR2,
  					v__batch		OUT CLOB,
  					v_number		OUT VARCHAR2,
  					v_status		OUT NUMBER) -- no return declaration
  AS
  BEGIN
  	-- no return
  END pro_test;
  

  FUNCTION fun_test(v__id IN VARCHAR2)
  RETURN SYS_REFCURSOR -- has return declaration
  IS
  	v_result SYS_REFCURSOR;
  BEGIN
  	-- has return value
  	RETURN v_result;
  END fun_test;
  

7 常用指令

7.1 sqlplus指令

sqlplus是Oracle专门为自己的数据库开发用来和数据库进行交互的客户端工具。

登录\连接命令

普通用户登录

sqlplus username/password

sys和system用户需要以sysdba身份连接

sqlplus sys/admin as sysdba

或者直接以sysdba身份连接

# cmd 打开 sqlplus 客户端
sqlplus /nolog
# 再登录连接
conn /as sysdba;

进入sqlplus之后，可以使用conn转换连接其他用户

8 常用函数

8.1 字符函数

UPPER() 转换大写字母

SELECT UPPER('smith') FROM DUAL; -- SMITH

LOWER() 转换小写字母

SELECT LOWER('SMITH') FROM DUAL; -- smith

INITCAP() 每个单词的首字母变成大写，其余字母小写

SELECT INITCAP('hello World')FROM DUAL; -- Hello World

CONCAT() 与 || 字符串拼接

SELECT CONCAT('HELLO','WORLD') FROM DUAL; -- HelloWorld

或

SELECT 'HELLO'||'WORLD' FROM DUAL; -- HelloWorld

结论: CONCAT函数不如||使用灵活

SUBSTR() 求字符串子串

第1个参数：字符串
第2个参数：起始位置
第3个参数：截取长度（可选）

SELECT SUBSTR('helloworld',1,3)FROM emp; -- hel

• 注意：
  • 截取子串时，从0截取和从1截取的效果是一样的
  • 如果不指定长度，则截取至末尾

replace() 字符串替换

把字符串中的T替换成空格：

replace(v_date_from,'T',' ')

8.2日期处理参数

to_date(）字符串转日期

把日期字符串按YYYY-MM-DD的格式解析成日期类型数据：

to_date(v_delivery_date,'YYYY-MM-DD');

把日期字符串按yyyy-mm-dd HH:mi:ss的格式解析成日期类型数据：

to_date(v_date_from,'yyyy-mm-dd HH:mi:ss')

• 在Oracle中貌似不区分大小写，也就是regexp正则表达式貌似可以不区分大小写
• 如果像yyyy-mm-dd HH:mi:ss同时包含了月份和分钟，要将分钟的表达式mm改成mi，因为不区分大小写，不能有两个mm

to_char() 日期转字符串

把日期类型数据转成DD-MM-YYYY格式的字符串：

TO_CHAR(do.DELIVERY_DATE, 'DD-MM-YYYY')

9 Oracle 中 JSON 的数据类型

在Oracle数据库中，JSON存储的数据类型有三种，VARCHAR2，CLOB，BLOB。其中VARCHAR2性能最好，但大小限制在32k，CLOB和BLOB不存在限制。

如果采用的是BLOB类型，Select语句直接查询出来是二进制，可以使用函数JSON_SERIALIZE进行转换。

如果采用的是CLOB类型，其本质就是一个Json对象，返回后台可以直接用Map<String,Object>来接收。

参考资料：
哔哩哔哩 丫丫5358 基于oracle数据库的PLSQL编程以及存储过程的创建和使用视频
https://www.bilibili.com/video/BV1Bb411E74d/?p=3&share_source=copy_web&vd_source=e22a75a6781804ee25ce46a2afc235ce