2022年9月28日16:33:11
目录
🏆一、存储过程的创建及调用
⭐️1.1、PLSQL编程
⭐️1.2、程序结构
⭐️1.3、变量
1.3.1、普通变量
1.3.2、引用型变量
1.3.3、记录型变量
⭐️1.4、流程控制
1.4.1、条件分支
1.4.2、循环
🏆二、游标
⭐️2.1、什么是游标
⭐️2.2、语法
⭐️2.3、游标的属性
⭐️2.4、创建和使用
⭐️2.5、带参数的游标
🏆三、存储过程
⭐️3.1、概念作用
⭐️3.2、语法
⭐️3.3、输入带参数的存储过程
⭐️3.4、带输出参数的存储过程
⭐️3.5、Java程序调用存储过程
🏆一、存储过程的创建及调用
-
PLSQL编程:HelloWorld，程序结构、变量、流程控制、游标。
-
存储过程：概念、无参存储、有参存储(输入、输出)
-
JAVA调用存储过程
⭐️1.1、PLSQL编程
什么是PL/SQL?
-
PL/SQL (Procedure Language/SQL)
-
PLSQL是Oracle对sql语言的过程化扩展(类似于Basic)
-
指在sql命令语言中增加了过程处理语句(如分支、循环等)，使sql语言具有过程处理能力。
⭐️1.2、程序结构
通过PIsqI Developer工具的Test Window创建程序模版或者通过语句在SQL Window编写
提示:PLSQL语言的大小写是不区分的
PL/SQL可以分为三个部分:声明部分、可执行部分、异常处理部分。
-- Created on 2022/10/8 by ME666 declare -- 声明游标、变量i integer; begin-- 执行语句-- 异常处理end;
其中Declare部分用来声明变量或游标(结果类型变量)，如果程序中无变量声明可以省略掉
1.3、Hello World
BEGINDBMS_OUTPUT.PUT_LINE('hello world');
END;
其中 DBMS_OUTPUT为Oracle的内置程序包，相当于Java中的System.out，而PUT_LINE是调用的方法，相当于Java中的println()方法
在SQLplus中也可以编写运行PLSQL程序
SQL> BEGIN2 DBMS_OUTPUT.PUT_LINE('hello world');3 END;4 /
执行结束后并显示输出的结果，默认情况下，输出选项是关闭状态，我们需要开启一下set serveroutput on
⭐️1.3、变量
PLSQL编程中常见的变量分两大类
-
普通数据类型(char,varchar2,date,number,boolean,long)
-
特殊变量类型(引用型变量，记录型变量)
变量声明的方式为
变量名 变量类型(变量长度) eg: v_name varchar(20);
1.3.1、普通变量
变量赋值的方式有两种：
-
直接赋值语句 := 比如：v_name := '张三'
-
语句赋值，使用select...into...赋值：（语法 select 值 into 变量）
示例：打印人员个人信息，包括：姓名、薪水、地址
-- 打印人员个人信息，包括：姓名、薪水、地址
declare-- 姓名v_name varchar(20) := '张三';-- 薪水v_salary number;-- 地址v_address varchar2(200);
BEGIN-- 直接赋值v_salary := 7000;-- 语句赋值select '北凉州北凉府北凉人北凉王' into v_address from dual;DBMS_OUTPUT.PUT_LINE('姓名:' || v_name || '-' ||'薪水:' || v_salary || '-' ||'地址:' || v_address);
END;
1.3.2、引用型变量
变量的类型和长度取决于表中字段的类型和长度
通过表名.列名%TYPE指定变量的类型和长度，语法：变量名 表名.列名%TYPE。例如：v_name emp.name%Type
示例：查询emp表中7839员工的个人信息，打印姓名和薪水
-- Created on 2022/10/8 by ME666
DECLARE
V_NAME EMP.ENAME%TYPE; -- 姓名V_SALARY EMP.SAL%TYPE; -- 薪水BEGIN-- 查询表中的姓名和薪水并赋值给变量-- 注意查询的字段和赋值的变量的顺序、个数、类型要一致SELECT ENAME, SAL INTO V_NAME, V_SALARY FROM EMP WHERE EMPNO = 7839;-- 打印变量DBMS_OUTPUT.PUT_LINE('姓名为：' || V_NAME || '薪水为：' || V_SALARY);END;
1.3.3、记录型变量
接收表中的一整行记录，相当于Java中的一个对象
语法：变量名称 表名%ROWTYPE,列如：v_emp emp%ROWTYPE
示例：查询并打印7839号员工的姓名和薪水
-- 查询并打印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); END;
如果有一个表，有100个字段，那么你程序如果要使用这100字段的话，如果你使用引用型变量一个个声明，会特别麻烦，记录型便令可以方便的解决这个问题
错误的时候：
-
记录型变量只能存储一个完整行数据
⭐️1.4、流程控制
1.4.1、条件分支
语法：
beginif 条件1 then 执行1elsif 条件2 then 执行2else 执行3end if;end;
注意关键字：ELSIF
示例：判断emp表中记录是否超过20条，10~20之间，或者10条以下
-- 判断emp表中记录是否超过20条，10~20之间，或者10条以下 DECLARE -- 声明变量接收emp中的数量V_COUNT INTEGER;BEGINSELECT COUNT(*) INTO V_COUNT FROM EMP;IF V_COUNT > 20THENDBMS_OUTPUT.PUT_LINE(20);ELSIF V_COUNT >= 10THENDBMS_OUTPUT.PUT_LINE(10);ELSEDBMS_OUTPUT.PUT_LINE(30);END IF; END;
1.4.2、循环
在Oralce中有三种循环方式，以下介绍其中一种：loop循环。其他两种，源码放出来，大家仁者见仁智者见智。
语法：
exit中文意思出口
beginloopexit when 退出循环条件end loop; end;
示例：打印数字1-10
loop循环
-- 打印数字1-10 DECLARE -- 声明循环变量并赋初始值V_NUM NUMBER := 1; BEGINLOOPDBMS_OUTPUT.PUT_LINE(V_NUM);EXIT WHEN V_NUM = 10;-- 循环变量自增v_num := v_num + 1;END LOOP; END; --------------- 输出语句在后 判断条件是变量 > 10 -- 打印数字1-10 DECLARE -- 声明循环变量并赋初始值V_NUM NUMBER := 1; BEGINLOOPEXIT WHEN V_NUM > 10;DBMS_OUTPUT.PUT_LINE(V_NUM);-- 循环变量自增v_num := v_num + 1;END LOOP; END;
while循环
语法：
beginwhile 循环条件 loopend loop; end;
/*while循环*/ declarev_num number:=1; beginwhile v_num<10 loopdbms_output.put_line(v_num);v_num:=v_num+1;end loop; end;
for循环
declarev_num number:=1; beginfor v_num in 1..10 loopdbms_output.put_line(v_num);end loop; end;
🏆二、游标
⭐️2.1、什么是游标
用于临时存储一个查询返回的多行数据(结果集，类似于Java的jdbc连接返回的ResultSet集合)，通过遍历游标，可以逐行访问处理该结果集的数据。
游标的使用方式：声明——打开——获取——关闭
⭐️2.2、语法
游标声明：CURSOR 游标名[(参数列表)] is 查询语句; 游标的打开：open 游标名; 游标的取值:FETCH 游标名 into 变量列表; 游标的关闭：CLOSE 游标名;
⭐️2.3、游标的属性
| 游标的属性 | 返回值类型 | 说明 |
|---|---|---|
| %ROWCOUNT | 整型 | 获FETCH语句返回的数据行数 |
| %FOUND | 布尔型 | 最近的FETCH语句返回一行数据则为真，否则为假 |
| %NOTFOUND | 布尔型 | 与%FOUND属性返回值相反 |
| %ISOPEN | 布尔型 | 游标已经打开时值为真，否则为假 |
其中%NOTFOUND是在游标中找不到元素的时候返回TRUE，通常用来判断退出循环
⭐️2.4、创建和使用
示例：使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。
-- 使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。 DECLARECURSOR C_EMP ISSELECT ENAME, SAL FROM EMP; -- 声明变量用来接收游标中的元素V_ENAME EMP.ENAME%TYPE;V_SALARY EMP.SAL%TYPE; BEGIN-- 打开游标OPEN C_EMP;-- 游标的取值LOOPFETCH C_EMPINTO V_ENAME, V_SALARY;-- 通过%NOTFOUND判断是否有值，有值打印，没有则退出循环EXIT WHEN C_EMP%NOTFOUND;--#PLS-00225: 子程序或游标 'ELE' 引用超出范围--错误sql-dbms_output.put_line(c_emp.v_ename || c_emp.v_salary);DBMS_OUTPUT.PUT_LINE(V_ENAME || V_SALARY);END LOOP;-- 关闭游标CLOSE C_EMP; END;
⭐️2.5、带参数的游标
示例：使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时动手输入
-- 使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时动手输入 DECLARE-- 1、游标声明;CURSOR C_EMP(V_DEPTNO EMP.DEPTNO%TYPE) ISSELECT ENAME, SAL FROM EMP WHERE DEPTNO = V_DEPTNO; -- 1.1声变量接收游标的值V_ENAME EMP.ENAME%TYPE;V_SALARY EMP.SAL%TYPE; BEGIN-- 2、游标打开OPEN C_EMP(10);-- 4、游标取值-- 5、遍历游标LOOPFETCH C_EMPINTO V_ENAME, V_SALARY;EXIT WHEN C_EMP%NOTFOUND;DBMS_OUTPUT.PUT_LINE(V_ENAME);END LOOP;-- 3、游标关闭CLOSE C_EMP; END;
注意%NOTFOUND属性默认值为FALSE,所以在循环中要注意判断条件的位置，
-
如果先判断在FETCH就会导致最后一条记录的值被打印两次(默认多循环一次)
-
如果判断在输出后，也会导致最后一个记录的值被打印两次
如图
🏆三、存储过程
⭐️3.1、概念作用
-
之前我们编写的PLSQL程序可以进行表的操作，判断。循环逻辑处理的工作，但是无法重复调用。
-
可以理解之前的代码全部编写在了main方法中，是匿名程序，Java可以通过封装对象和方法对象来解决问题、PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用，这些被存储起来的PLSQL称之为存储过程。
存储过程作用：
-
在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭时很耗费资源的)，需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL只不过，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务，可以
-
Oracle官方给的建议：能够让数据库操作的不要放在程序中。在数据库张红实现基本上不会出现错误，在程序中操作可能会存在错误.(如果在数据库中操作数据，可以有一定的日志恢复功能)
⭐️3.2、语法
create or replace procedure 过程名称(参数列表) is beigin end[过程名称];
根据参数的类型，我们将其分为3类讲解：
-
不带参数的
-
带输入参数的
-
带输入输出参数(返回值)的
CREATE OR REPLACE PROCEDURE P_HELLO as
BEGINDBMS_OUTPUT.PUT_LINE('1234578 world');
END P_HELLO;
-- 创建或者替换过程 create or replace procedure procedure_demo1 as begin dbms_output.put_line('我是存储过程'); end procedure_demo1;
调用存储过程
-- Created on 2022/10/8 by ME666 DECLARE BEGINP_HELLO; END;
在SQLPLUS中显示结果的前提是需要set serveroutput on
注意：
-
is和as是可以互用的，用哪个都没有关系的
-
过程中没有declare关键字，declare用在语句块中
⭐️3.3、输入带参数的存储过程
示例：
查询并打印某个员工(如7839号员工)的姓名和薪水-存储过程：要求，调用的时候传入员工编号，自动控制台打印。
-- 查询并打印某个员工(如7839号员工)的姓名和薪水-- 要求，调用的时候传入员工编号，自动控制台打印。CREATE OR REPLACE PROCEDURE P_QUERYNAMEANDSAL(IN_EMPNO IN EMP.EMPNO%TYPE) IS
-- 声明变量接收查询结果V_ENAME EMP.ENAME%TYPE;V_SALARY EMP.SAL%TYPE;
BEGIN
-- 根据用户传递员工的编号 查询姓名和薪水SELECT ENAME, SAL INTO V_ENAME, V_SALARY FROM EMP WHERE EMPNO = IN_EMPNO;DBMS_OUTPUT.PUT_LINE('该员工的姓名是：' || V_ENAME || '薪资有：' || V_SALARY);
END P_QUERYNAMEANDSAL;
⭐️3.4、带输出参数的存储过程
示例：输入员工工号查询某个员工(7839)信息，要求，将薪水作为返回值输出，给调用的程序使用
-- 输入员工工号查询某个员工(7839)信息，-- 要求，将薪水作为返回值输出，给调用的程序使用 CREATE OR REPLACE PROCEDURE P_RETURNSALARY (IN_EMPNO IN EMP.EMPNO%TYPE,OUT_SALARY OUT EMP.SAL%TYPE ) IS -- 接收变量-- V_SALARY EMP.SAL%TYPE; BEGINSELECT SAL INTO OUT_SALARY FROM EMP WHERE EMPNO = IN_EMPNO;-- DBMS_OUTPUT.PUT_LINE(V_SALARY); END P_RETURNSALARY;
-- Created on 2022/10/9 by ME666 declare V_SALARY EMP.SAL%TYPE; beginP_ReturnSalary(7839,V_SALARY);DBMS_OUTPUT.PUT_LINE(V_SALARY);end;
⭐️3.5、Java程序调用存储过程
需求：如果一条语句无法实现结果集，比如需要多表查询，或者需要复杂逻辑查询，我们可以选择调用存储查询你的结果。
CallableStatement
通过Connect对象的prepareCall方法调用存储过程
得出结论：通过Connect对象调用prepareCall方法传递一个转义SQL语句调用存储过程，输入参数直接调用set方法传递，输出参数需要注册手，执行存储过程，通过get方法获取，参数列表的下标是从1开始的。
存储函数必须有返回值 存储过程可以没有返回值
public static void main(String[] args) throws ClassNotFoundException, SQLException {//1、加载驱动Class.forName("oracle.jdbc.driver.OracleDriver");//2、获取连接String url = "jdbc:oracle:thin:@localhost:1521:orcl";String username = "scott";String password = "tiger";
Connection conn = DriverManager.getConnection(url, username, password);
// System.out.println("conn = " + conn);//测试数据库是否连接成功
//3、获得语句String sql = "{call P_RETURNSALARY(?,?)}";CallableStatement call = conn.prepareCall(sql);//4、设置输出参数call.setInt(1, 7839);
//5、注册输出参数call.registerOutParameter(2, OracleTypes.DOUBLE);
//6、执行存储过程call.execute();
//7、获取输出参数double sal = call.getDouble(2);System.out.println("sal = " + sal);
//8、释放资源call.close();conn.close();
}
