欢迎投稿

今日深度：

> > 正文

一文详解Oracle存储过程，

来源:未知 阅读:7144 评论 118 热度5

标签：oracle 存储过程

一文详解Oracle存储过程，


目录
  • 简介
  • 1、基本语法 
    • 1.1 新建测试窗口
    • 1.2 程序结构
    • 1.3打印输出  
    • 1.4 变量
      • 1.4.1 普通变量
      • 1.4.2 引用型变量
      • 1.4.3 记录型变量  
    • 1.5 流程控制
      • 1.5.1 条件分支
      • 1.5.2 循环
  • 2、游标
    • 2.1 游标说明
      • 2.3 游标属性
        • 2.4 无参数游标
          • 2.5 带参数的游标
          • 3、存储过程
            • 3.1 概念 
              • 3.2 语法
                • 3.3 无参存储
                  • 3.3.1 创建存储
                  • 3.3.2 调用存储过程 
                • 3.4 带输入参数的存储过程 IN
                  • 3.4.1 创建带参数存储过程
                  • 3.4.2 调用带参数存储过程
                • 3.5 带输入输出参数的存储过程  OUT
                  • 3.5.1 创建带输入输出参数的存储过程
                  • 3.5.2 调用带输入输出参数存储过程
                • 3.6 带输入输出参数的存储过程 IN OUT
                  • 3.6.1 创建带输入输出参数的存储过程
                  • 3.6.2 调用带输入输出参数存储过程

              简介

              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 < 10) LOOP
        DBMS_OUTPUT.put_line('值为: ' || V_NUM);
        V_NUM := V_NUM + 1;
    END LOOP;
END;
 
--此循环会先判断再执行语句
              • 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;

              执行结果:

              以上就是一文详解Oracle存储过程的详细内容，更多关于Oracle存储过程的资料请关注PHP之友其它相关文章！

              您可能感兴趣的文章:
              • Oracle数据库存储过程的调试过程
              • Oracle在PL/SQL中使用存储过程
              • C#调用Oracle存储过程方法介绍(附源码)
              • Oracle存储过程案例详解
              • 解决PL/SQL修改Oracle存储过程编译就卡死的问题
              • Spring boot调用Oracle存储过程的两种方式及完整代码

              www.htsjk.Com true http://www.htsjk.com/oracle/46743.html NewsArticle 一文详解Oracle存储过程， 目录 简介 1、基本语法 1.1 新建测试窗口 1.2程序结构 1.3打印输出 1.4 变量 1.4.1普通变量 1.4.2引用型变量 1.4.3 记录型变量 1.5 流程控制 1.5.1 条件分支 1.5.2 循环...
              本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
              同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。
              返回首页
              相关文章
              评论暂时关闭