PLSQL--Oracle的存储过程

爱洗澡的喜羊羊

已于 2022-11-25 09:01:39 修改

阅读量2.5k

点赞数

文章标签： oracle 数据库 sql

于 2022-11-25 08:57:32 首次发布

本文链接：https://blog.csdn.net/m0_66717003/article/details/128031596

版权

一.普通变量
-- 打印人员个人信息，包括：姓名，薪水，地址
declare
--姓名
v_name varchar2(20) := 'XXX'; --声明变量直接赋值
--薪水
v_sal number;
--地址
v_addr varchar2(200);
begin
-- 在程序中直接赋值
v_sal := 2300;
--语句赋值
select 'XXX公司' into v_addr from dual;

--打印输出
dbms_output.put_line('姓名:'||v_name|| ',薪水:' ||v_sal|| ',地址:' ||v_addr);

end;
=========================================================================

二.引用型变量
1.变量的类型和长度取决于表中字段的类型和长度
2.通过表名.列名%TYPE指定变量的类型和长度, v_name emp.ename%TYPE

-- 查询工号为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);

end;

========================================================================
三.
1.接收表中的一整行记录，相当于Java中的一个对象
语法：变量名称表名%ROWTYPE, 例如：v_emp emp%rowtype;

--查询 emp 表中 7839 号员工的个人信息，打印姓名和薪水
declare
--记录型变量
v_emp emp%ROWTYPE; --记录变量就相当于一条记录

begin
--错误使用： 1.SELECT ename INTO V_EMP FROM EMP WHERE EID = 7839;(报错：值过多)
---错误使用： 2.SELECT * INTO V_EMP FROM EMP（报错：超出请求行数）

SELECT * INTO V_EMP FROM EMP WHERE EID = 7839;

--打印输出
DBMS_OUTPUT.PUT_LINE('姓名:' || v_emp.v_ename || ',薪水:' || v_emp.v_sal);

end;
--记录型变量慎用会增加数据库的负担

--使用场景：如果有一个表，有100个字段，如果你使用引用型变量一个个声明会特别麻烦，记录型变量可以解决这个问题

=========================================================================

四.流程控制（条件分支）
1.if elsif else 多条件判断

例：
--判断emp表中记录是否超过20条，10-20之间，或者10条一下
declare
--声明变量接受emp中的数量
v_count number;

begin

SELECT count(1) INTO v_count FROM EMP;

if v_count > 20 then
dbms_output.put_line('emp表中记录数量超过20条为：' || v_count);
elsif v_count >= 10 then
dbms_output.put_line('emp表中记录数量超过10-20条为：' || v_count);
else
dbms_output.put_line('emp表中记录数量超过10条以下为：' || v_count);

end if;

end;

=========================================================================

五.循环
1.oracle循环有三种第一种：loop循环第二种：for 第三种：while
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;

=========================================================================

六.游标
一.
1.什么是游标：用于临时存储一个查询返回的多行数据(结果集，类似于Java的JDBC连接返回ResultSet集合),通过遍历游标，可以
逐行访问处理该结果集的数据
2.游标的使用方式：声明-->打开-->读取-->关闭
二.语法 :
游标声明 :
CURSOR 游标名{(参数列表)} IS 查询语句;

游标的打开：

OPEN游标名；
游标的取值；
FETCH 游标名 INTO 变量列表；
游标的关闭：
CLOSE 游标名；

3.游标的属性

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

4.游标的创建和使用

（1）无参游标

示例：
-- 使用游标查询emp表中所有的员工姓名和工资。并且将其依次打印出来
declare
--声明游标
cursor c_emp is
select ename, sal from emp;

--声明变量接收游标中的数据
v_ename emp.ename%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;

（2）.带参数的游标

-- 使用游标查询emp表中所有的员工姓名和工资。并且将其依次打印出来
declare
--声明游标
cursor c_emp(v_deptno emp.deptno%TYPE) is
select ename, sal from emp where deptno = v_deptno ;

--声明变量接收游标中的数据
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;

begin

--打开游标
open c_emp(10088);

--循环遍历游标
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;

注意： fetch 不能放错位置否则默认有值

=========================================================================

七.存储过程

1.概念和作用

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

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

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

存储过程作用：

1.在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很消耗资源)，需要对数据库
进行多次的IO读写，性能比较低，如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭
一次数据库就可以实现我们的业务，可以大大提高效率

2.ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能
会存在错误（如果在数据库中操作数据，可以有一定的日志恢复功能）

二.无参存储过程

新建一个程序窗口在程序窗口中选择过程

在过程中写

示例：
create or replace procedure p_hello as
--在这里直接声明变量

begin

dbms_output.put_line('hello word');

end p_hello;

然后在测试窗口，调用存储过程
示例：

begin
--PLSQL调用存储过程
p_hello;

end;

三.带参存储过程

新建一个程序窗口在程序窗口中选择过程

在存储过程中写

示例：
-- 查询并打印某个员工(如7839号员工)的姓名和薪水--存储过程：要求调用的时候传入员工编号，自动控制台打印
create or replace procedure p_querynameandsal(i_empno in emp.empno%TYPE) as
--声明变量
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin

--查询emp表中某个员工的姓名和薪水并赋值给变量
select ename,sal into v_name,v_sal from emp where empno = i_empno;
dbms_output.put_line(v_name||'_'||v_sal);

end p_querynameandsal;

然后在测试窗口，调用存储过程
示例：

-- Created on 2022/11/24 by 王宇超
declare
-- Local variables here 或者在这里声明一个参数
i integer;
begin
-- Test statements here
p_querynameandsal(7839);--直接赋值

end;

三.带输出参数的存储过程

新建一个程序窗口在程序窗口中选择过程

在过程中写

示例：

-- 输入工号查询某个员工（7839号员工）信息，要求，将薪水作为返回值输出，给调用的程序使用
create or replace procedure p_querysal_out(i_empno in emp.empno%TYPE,
o_sal out emp.sal%TYPE) as

begin

--查询emp表中某个员工的姓名和薪水并赋值给变量
select sal into o_sal from emp where empno = i_empno;

end;

然后在测试窗口，调用存储过程
示例：

-- Created on 2022/11/24 by 王宇超
declare
-- 声明变量接收存储过程中的输出参数
v_sal emp.sal%TYPE;

begin

p_querysal_out(7839,v_sal);
dbms_output.put_line(v_sal);

end;

=========================================================================

八.JAVA程序调用存储过程

jar包使用Ojdbc8

在测试类测试一下

代码：

public static void main(String[] args) throws Exception {

// OracleDriver

//1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");

//2.获取链接对象
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "wyc";
String password = "123";
//返回对象
Connection conn = DriverManager.getConnection(url, user, password);

//3.获得语句对象
//call 调取存储过程名
String sql ="{call p_querysal_out(?,?)}";
CallableStatement call = conn.prepareCall(sql);

//4.设置输出参数
//下标从1开始
call.setInt(1,7839);

//5.注册输出参数
call.registerOutParameter(2, OracleTypes.DOUBLE);

//6.执行存储过程
call.execute();

//7.获取输出参数
double sal = call.getDouble(2);
System.out.println(sal);

//8.释放资源
call.close();
conn.close();

}