简介
Oracle 存储过程是 Oracle 数据库中的一种数据处理对象,它可以在数据库中定义一组预定义的 SQL 语句,用于完成特定的数据库操作。存储过程可以被授权的用户调用,并且可以执行多个语句,这些语句可以被视为一个单独的操作,也可以被视为一系列的操作。
使用存储过程可以大大提高数据库的性能和安全性。存储过程可以减少网络流量和请求,同时也可以减少与应用程序之间的接口调用,从而提高了数据库的性能和可靠性。
目录
基本语法, 游标,存储过程。
1、基本语法
1.1 新建测试窗口
- New Test Window
1.2 程序结构
- 在Java编程中是区分大小写,这里不用区分大小写。
- DECLARE部分声明变量或游标(结果集类型变量),程序没有变量声明的可以省略或删除。
- PLSQL可分为三个部分: 变量声明部分,执行部分,异常处理部分。
-- Created on 2023/5/10 by 肖
declare
--声明变量 游标
begin
-- 执行语句
-- 异常处理
end;
1.3打印输出
- Dbms_Output 为oracle内置程序包,类似Java中的System.out,而put_line() 是调用的方法,相当于println()方法。
- 需要注意的是: put_line(‘hello mr.xiao’); 中 一定是 ‘ ‘ 号,否则会报错的。
begin
--打印 hello mr.xiao
Dbms_Output.put_line('hello mr.xiao');
end;
执行结果
- 如果你不能打印输出,需要开启 set serveroutput on 因为 默认情况下,输出选项是关闭状态。
1.4 变量
变量分两大类如:
- 普通数据类型(char,varchar2, date, number, boolean, long)
- 特殊变量类型(引用型变量、记录型变量)
声明变量的方式如:
- 变量名 变量类型(变量长度) 例如: v_name varchar2(30);
1.4.1 普通变量
变量赋值的方式有两种如:
- 直接赋值语句 := 比如: v_name := ‘你才是臭弟弟’
- 语句赋值,使用select …into … 赋值:(语法 select 值 into 变量)
-- 打印个人信息,包括: 姓名、薪水、地址
DECLARE
-- 姓名
V_NAME VARCHAR2(30) := '你才是臭弟弟'; -- 声明变量直接赋值
--薪水
V_SAL NUMBER;
--地址
V_ADDR VARCHAR2(200);
BEGIN
--在程序中直接赋值
V_SAL := 1800; --工资每月1800 每天笑哈哈
--语句赋值
SELECT 'CSDN你才是臭弟弟' INTO V_ADDR FROM DUAL; --不会有人不知道DUAL吧,DUAL 是一个用于描述 Oracle 数据库中的虚拟表的关键字
--打印变量 注意 || 是拼接
DBMS_OUTPUT.PUT_LINE('姓名:' || V_NAME || ',薪水:' || V_SAL || ',地址:' ||V_ADDR);
END;
执行结果:
1.4.2 引用型变量
- 变量的类型和长度取决于表中字段的类型和长度
- 通过 表名.列名%TYPE 指定变量的类型和长度,例如: v_name emp.ename%TYPE
-- 查询emp表中1001号员工的个人信息,打印姓名和薪水
DECLARE
-- 姓名
V_NAME EMP.ENAME%TYPE; -- 声明变量直接赋值
--薪水
V_SAL EMP.ESALARY%TYPE;
BEGIN
--查询表中的姓名和薪水并赋值给变量
--注意查询的字段和赋值的变量的顺序、个数、类型要一致
SELECT ENAME, ESALARY INTO V_NAME, V_SAL FROM EMP WHERE EMPLOYEEID = 1001;
--打印变量
DBMS_OUTPUT.PUT_LINE('姓名:' || V_NAME || ',薪水:' || V_SAL);
END;
执行结果:
推荐大家使用引用型变量区别:
- 普通型变量: V_NAME VARCHAR2(30); 你怎么知道一定VARCHAR2类型,你又怎么知道长度一定是30呢,假设V_NAME VARCHAR2(1); 就对应不上SELECT ENAME, ESALARY INTO V_NAME, V_SAL FROM EMP 这条语句中的 ENAME 的长度了,也就接收不到值了,就会报错。如果要使用普通变量前提是 ,了解查询表中对应字段的 类型 及 长度, 才能基于他们来确定类型长度 这比较繁琐。
- 引用型变量: 声明一个变量 不再定义类型长度,而是基于接收表字段的类型及长度 来定义。如:V_NAME EMP.ENAME%TYPE;
总结:
使用普通变量定义方式,需要知道表中列的类型,而使用引用类型,不需要考虑列的类型,使用%TYPE是非常好的编程风格,因为引用型变量更加灵活。
1.4.3 记录型变量
- 记录型变量 接受表中的一整行记录,相当于Java中的一个对象
- 语法: 变量名称 表名%ROWTYPE, 例如:v_emp emp%rowtype;
-- 查询emp表中1001号员工的个人信息,打印姓名和薪水
DECLARE
-- 记录型变量接受一行
V_EMP EMP%ROWTYPE;
BEGIN
--记录型变量默认接受表中的一行数据,不能指定字段。
SELECT * INTO V_EMP FROM EMP WHERE EMPLOYEEID = 1001;
--打印变量,通过变量名.属性的方式获取变量中的值
DBMS_OUTPUT.PUT_LINE('姓名:' || V_EMP.ENAME || ',薪水:' || V_EMP.ESALARY);
END;
总结:
- 如果有一张表,有50个字段,那么你程序如果要使用这50字段话,如果你使用引用型变量一个个声明,会特别繁琐,记录型变量可以方便的解决这个问题。
注意错误的使用案例如下:
- 记录型变量只能存储一个完整的行数据
我把 * 换成 单个字段执行报错,因为上面的变量定义的是一行,而现在只给一个是不行的。
- 返回的行太多了,记录型变量也接收不了
现在这条sql 为什么报错,因为现在是全表查询 返回的行数超出了 一个变量只能接收一行,这就和JAVA 类似了,应该用集合去装才可以 ,装进集合在取出来 是不是就跟JAVA 中循环取值差不多。
1.5 流程控制
1.5.1 条件分支
-
IF条件判断~语法
BEGIN
IF 条件 THEN
执行语句
END IF;
END;
- IF…ELSE 条件判断~语法
BEGIN
IF 条件 THEN
执行语句
ELSE
执行语句
END IF;
END;
- IF…ELSIF…ELSE条件判断~语法,注意关键字:ELSIF。
BEGIN
IF 条件1 THEN 执行1
ELSIF 条件2 THEN 执行2
ELSE 执行3
END IF;
END;
案例:
--判断emp表中记录是否超过20条,10-20之间,或者10条以下
DECLARE
--声明变量接受emp表中的记录数
V_COUNT NUMBER;
BEGIN
--查询emp表中的记录数赋值给变量
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.5.2 循环
- Loop 语法
BEGIN
LOOP
EXIT WHEN 退出循环条件
END LOOP;
END;
Loop语法 案例:
--循环打印 1-5
DECLARE
--声明循环变量并赋初值
V_NUM NUMBER := 1;
BEGIN
LOOP
EXIT WHEN V_NUM > 5;
DBMS_OUTPUT.PUT_LINE(V_NUM);
--循环变量自增
V_NUM := V_NUM + 1;
END LOOP;
END;
- While 语法
while(判断循环的条件) loop
循环的语句;
END loop;
While语法 案例:
DECLARE
--声明循环变量
V_NUM NUMBER;
BEGIN
-- 必须给一个初始值
V_NUM := 1;
WHILE(V_NUM
- FOR循环 语法
FOR 变量名 in 变量的初始值..结束值 lOOP
循环语句;
END loop;
FOR循环语法 案例:
--for循环打印 1-10
DECLARE
--声明循环变量并赋初值
V_NUM NUMBER ;
BEGIN
--此语句会自动将1到10赋值给V_NUM
FOR V_NUM in 1..10 loop
DBMS_OUTPUT.put_line('值为: ' || V_NUM);
END LOOP;
END;
2、游标
2.1 游标说明
- 用于临时存储一个查询返回的多行数据,通过遍历游标,可以逐行访问处理该结果集的数据。
- 游标的使用方式:声明→打开→读取→关闭
2.2 语法
游标声明:
CURSOR 游标名[(参数列表)] IS 查询语句;
游标的打开:
OPEN 游标名;
游标的取值:
FETCH 游标名 INTO 变量列表;
游标的关闭:
CLOSE 游标名;
注意: 游标名自身是可以带参数的,如果有参数、参数会带入到查询语句中进行查询,游标本质 就是 一个 is 查询语句,也就是说查询结果被放置到游标中。
2.3 游标属性
属性 | 说明 |
%FOUND | 变量最后从游标中获取记录的时候,在结果集中找到了记录。 |
%NOTFOUND | 变量最后从游标中获取记录的时候,在结果集中没有找到记录。 |
%ROWCOUNT | 当前时刻已经从游标中获取的记录数量。 |
%ISOPEN | 是否打开。 |
%ROW | 游标指向的行数。 |
%COLUMN | 游标指向的列数。 |
%ATTEMPTS | 尝试获取记录的次数。 |
%ERROR | 发生错误的次数。 |
%FETCH_STATUS FETCH | 语句的执行状态,包括成功、失败和出错标志。 |
%SIZE | 当前游标指向的记录大小。 |
%LINE_NUMBER | 当前行号。 |
%ERROR_STRING | 错误信息字符串。 |
%PROCID | 当前执行的SQL语句的ID |
2.4 无参数游标
- 使用游标查询emp表中所有员工的姓名和工资,Loop循环依次打印结果集。
--使用游标查询emp表中所有员工的姓名和工资,依次打印结果集。
DECLARE
--声明游标
CURSOR C_EMP IS
SELECT ENAME, ESALARY FROM EMP;
--声明变量用来接受游标中的元素
V_ENAME EMP.ENAME%TYPE;
V_SAL EMP.ESALARY%TYPE;
BEGIN
--打开游标
OPEN C_EMP;
--遍历游标中的值
LOOP
--通过FETCH语句获取游标中的值并赋值给变量
FETCH C_EMP
INTO V_ENAME, V_SAL;
--通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
END LOOP;
--关闭游标
CLOSE C_EMP;
END;
执行结果:
2.5 带参数的游标
- 使用游标查询并打印某部门的员工的姓名和薪资,部门编号为运行时手动输入。
--使用游标查询并打印某部门的员工的姓名和薪资,部门编号为运行时手动输入。
DECLARE
--声明游标传递参数
CURSOR C_EMP(V_EMPLOID EMP.EMPLOYEEID%TYPE) IS
SELECT ENAME, ESALARY FROM EMP WHERE EMPLOYEEID = V_EMPLOID;
--声明变量用来接受游标中的元素
V_ENAME EMP.ENAME%TYPE;
V_SAL EMP.ESALARY%TYPE;
BEGIN
--打开游标并传递参数
OPEN C_EMP(1001);
--遍历游标中的值
LOOP
--通过FETCH语句获取游标中的值并赋值给变量
FETCH C_EMP
INTO V_ENAME, V_SAL;
--通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
END LOOP;
--关闭游标
CLOSE C_EMP;
END;
执行结果:
注意:%NOTFOUND属性默认值为FLASE,所以在循环中要注意判断条件的位置.如果先判断在FETCH会导致最后一条记录的值被打印两次(多循环一次默认);
错误反例演示:
反例执行结果:
原因: %NOTFOUND 默认值是 false,false意味着游标里面默认是有值,到底有值还是没值 需要fetch 好之后才知道有没有值,%NOTFOUND 默认做了一个有值的假设 , 看下面代码:
LOOP
–通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
EXIT WHEN C_EMP%NOTFOUND;–通过FETCH语句获取游标中的值并赋值给变量
FETCH C_EMP
INTO V_ENAME, V_SAL;
DBMS_OUTPUT.PUT_LINE(‘姓名:’ || V_ENAME || ‘,薪水:’ || V_SAL);
END LOOP;
EXIT WHEN C_EMP%NOTFOUND; 判断有值打印,出去之后 又带着有值的进入到循环中 EXIT WHEN C_EMP%NOTFOUND; 判断没值打印, 所以这次打印的是上次值的。注意存放的位置。
3、存储过程
3.1 概念
- 之前编写的PLSQL程序可以进行表的操作、判断、循环等逻辑处理的工作,但无法重复调用。可以理解为代码编写在了JAVA的main方法中,JAVA可以通过封装对象和方法来解决复用问题
- PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用,这些被存储起来的PLSQL程序称之为存储过程
3.2 语法
参数的类型分为:
- 不带参数的。
- 带输入参数的。
- 带输入输出参数(返回值)的。
CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] IS
BEGIN
END 过程名称;
3.3 无参存储
3.3.1 创建存储
- 第一种方式: New → Program Window → Procedure
- 第二种方式: New → SQL Window
- 创建存储过程语法
--通过调用存储过程打印hello 臭弟弟
CREATE OR REPLACE PROCEDURE P_XIAO IS
--声明变量
BEGIN
DBMS_OUTPUT.PUT_LINE('hello 臭弟弟');
END P_XIAO ;
1、is和as都可以用。
2、存储过程中没有declare关键字,declare用在语句块中。也就是说匿名程序才需要, 存储过程没有可以直接带上方–声明变量。
- 注意点击△执行后 会进行存储 ,Procedures 中会以P_XIAO 这个名称进行存储。
- 通过PLSQL工具查看创建好的存储过程
3.3.2 调用存储过程
- 通过PLSQL程序调用 New → Text Window
begin
--输入调用存储过程的名称
P_XIAO;
end;
查看结果:
3.4 带输入参数的存储过程 IN
说明:
- 带参数的存储过程跟我们在Java中的方法就可以对应上,比如查询并打印某个员工姓名薪水 ,在调用存储过程的时候自己指定传参, 比如我传一个员工编号,基于传的编号 将结果返回。
- 实现查询并打印某个员工(如:编号1001)的姓名和薪水, 调用存储过程的时候传入员工编号,自动控制台打印。
3.4.1 创建带参数存储过程
- 第一种方式: 重新编辑存储过程 右击 → Edit(这是基于之前创建的,还可以进行编辑)
- 需要注意的是如果有OR REPLACE当存储过程名字被更改时,如果PLSQL中存在此存储过程名称会被删除替换创建当前的,果不存在则创建一个新的存储过程。
执行结果:
- 如果没有OR REPLACE语句 PLSQL也不存相同的名字在则会新创建。如果存在则会报错。
- 第二种方式: New → SQL Window
- 查询并打印某个员工(如:编号1001)的姓名和薪水, 要调用存储过程的时候传入员工编号,自动控制台打印。
- 注意:参数要与定义的参数的顺序和类型一致
--查询并打印某个员工(如:编号1001)的姓名和薪水, 要调用存储过程的时候传入员工编号,自动控制台打印。
CREATE OR REPLACE PROCEDURE P_xiao_jian(IN_EMPLOYEEID IN EMP.EMPLOYEEID%TYPE) as
--声明变量接受查询结果
V_ENAME EMP.ENAME%TYPE;
V_SAL EMP.ESALARY%TYPE;
BEGIN
--根据用户传递的员工号查询姓名和薪水
--注意:参数要与定义的参数的顺序和类型一致 如: ENAME INTO V_ENAME
SELECT ENAME, ESALARY INTO V_ENAME, V_SAL FROM EMP WHERE EMPLOYEEID = IN_EMPLOYEEID;
--打印结果
DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
END P_xiao_jian;
3.4.2 调用带参数存储过程
- New → Text window 直接赋值
-- Created on 2023/5/13 by 肖
declare
-- Local variables here
i integer;
begin
-- Test statements here
P_XIAO_JIAN(1001);--直接赋值 做一个值的传递
end;
- 也可以 声明变量 赋值
-- Created on 2023/5/13 by 肖
declare
-- Local variables here
V_PARAM number :=1001; --声明变量 赋值
begin
-- Test statements here
P_XIAO_JIAN(V_PARAM);
end;
查看执行结果:
3.5 带输入输出参数的存储过程 OUT
说明:
- 带输入输出的这种存储过程通常是给第三方程序调用的,就比如Java或其他编程语言,也就是说把这个存储过程的 计算结果进行返回不是在数据库打印打印就完事了
- 举例说明比如输入员工编号查询某个员工信息,要求将薪水作为返回值输出,给调用的程序使用。这个调用的程序可以是PLSQ自身程序,也可以是第三方比如Java 程序。
3.5.1 创建带输入输出参数的存储过程
- 参数传递方式分三类: IN,OUT,IN OUT
- IN 表示输入参数
- OUT 表示输出参数
- IN OUT 即可作输入参数,也可作输出参数。
--输入员工号查询某个员工(如:编号1001)信息,要求将薪水作为返回值输出,给调用的程序使用。
CREATE OR REPLACE PROCEDURE P_XIAO_JIAN(IN_EMPLOYEEID IN EMP.EMPLOYEEID%TYPE,OUT_ESALARY OUT EMP.ESALARY%TYPE) as
BEGIN
--查询 ESALARY into 给 OUT_ESALARY 输出变量
SELECT ESALARY INTO OUT_ESALARY FROM EMP WHERE EMPLOYEEID = IN_EMPLOYEEID;
END P_XIAO_JIAN;
3.5.2 调用带输入输出参数存储过程
DECLARE
--声明一个变量接受存储过程的输出参数
V_ESALARY EMP.ESALARY%TYPE;
BEGIN
P_XIAO_JIAN(1001, V_ESALARY); --注意参数的顺序
DBMS_OUTPUT.PUT_LINE('工资:'||V_ESALARY);
END;
执行结果:
3.6 带输入输出参数的存储过程 IN OUT
- IN OUT 即可作输入参数,也可作输出参数。
3.6.1 创建带输入输出参数的存储过程
--输入员工号查询某个员工(如:编号1001)信息,要求将薪水作为返回值输出,给调用的程序使用。
CREATE OR REPLACE PROCEDURE P_xiao_jian(IN_EMPLOYEEID IN EMP.EMPLOYEEID%TYPE,OUT_ESALARY OUT EMP.ESALARY%TYPE,IN_OUT_PARAM in out number) as
BEGIN
--查询 ESALARY into 给 OUT_ESALARY 输出变量
SELECT ESALARY INTO OUT_ESALARY FROM EMP WHERE EMPLOYEEID = IN_EMPLOYEEID;
--打印被传入的值
dbms_output.put_line('我是被传入的值'||IN_OUT_PARAM);
--IN_OUT_PARAM赋值默认值为10
IN_OUT_PARAM:=10;
END P_xiao_jian;
3.6.2 调用带输入输出参数存储过程
-- Created on 2023/5/16 by 肖
declare
-- Local variables here
V_ESALARY EMP.ESALARY%TYPE;
V_IN_OUT_PARAM number:=6;--传入的值
begin
-- Test statements here
P_xiao_jian(1001,V_ESALARY,V_IN_OUT_PARAM);
DBMS_OUTPUT.PUT_LINE('薪水:'||V_ESALARY||'原始默认值: '||V_IN_OUT_PARAM);
end;
执行结果:
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net