基于oracle数据库的PLSQL编程以及存储过程的创建和使用

深藏阁楼爱情的钟 2022-05-10 22:00 643阅读 0赞

### 前言 ###

本文简单记录下oracle数据库中，如何用plsql编程、以及存储过程的创建和使用。  
相关的概念我就不列举了，大家不清楚的可以自行搜索，本篇主要列举实际的sql语法。  
那plsql编程，大家可以使用windows的命令行，也可以使用工具进行编写调试，我这边主要使用Oracle Sql Developer来举例。

### 1 plsql编程 ###

#### 1.1 程序结构 ####

首先说说plsql的程序，可以分为3个部分：声明部分、可执行部分、异常处理部分。其中declare部分用来声明变量或者游标（结果集类型变量），如果程序中没有变量声明则可以省略。

DECLARE
      --声明变量、游标
      -- 相当于java中的 public class A{}
    BEGIN
      --执行部分，相当于java中的main方法
      --可以进行异常处理
      dbms_output.put_line('hello world');
    END;

这段程序中，dbms\_output是oracle的内置程序包，相当于java中的System.out，而put\_line()是调用的方法，相当于println()方法。执行结果为：  
![在这里插入图片描述][70]  
那如果是在命令行执行这段程序，则需要在执行程序之前，打开控制台的输出，并在结尾加“/”标识程序已结束，然后回车即可看到打印的结果。  
![在这里插入图片描述][70 1]

#### 1.2 变量 ####

在plsql中，常见的变量分为两大类：  
1、普通数据类型（char,varchar2,date,number,boolean,long）；  
2、特殊变量类型（引用型变量、记录型变量）；  
变量的声明方式为：变量名 变量类型（变量长度） 例如：v\_name varchar2(20)

##### 1.2.1 普通变量 #####

普通变量的赋值方式有两种：  
1）使用“:=”直接赋值，比如：v\_name :=‘zhangsan’  
2）语句赋值，语法为：select 值 into 变量名

-- 声明一个人的信息，姓名、薪水、地址
    DECLARE
      -- 姓名
      v_name VARCHAR2(50) := 'zhangsan';
      -- 薪水
      v_sal NUMBER;
      -- 地址
      v_addr VARCHAR2(200);
    begin
      -- 直接赋值
      v_sal := 10000;
      -- 语句赋值
      select '中国' into v_addr from dual;
      -- 打印变量
      DBMS_OUTPUT.PUT_LINE('姓名：' || v_name || '薪水：' || v_sal || '地址：' || v_addr);
    end;

执行结果为：  
![在这里插入图片描述][70 2]

##### 1.2.2 引用型变量 #####

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

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

CREATE TABLE "GD_JIANGUAN"."EMP" 
       (	"ENAME" VARCHAR2(20 BYTE), 
    	"ESAL" NUMBER, 
    	"EADDR" VARCHAR2(20 BYTE), 
    	"ENO" VARCHAR2(20 BYTE)
       ) ;
       COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ENAME" IS '用户名';
       COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ESAL" IS '薪水';
       COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."EADDR" IS '地址';
       COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ENO" IS '编号';
    Insert into emp (ENAME,ESAL,EADDR,ENO) values ('张三',10000,'中国','1');
    Insert into emp (ENAME,ESAL,EADDR,ENO) values ('李四',8000,'美国','2');
    Insert into emp (ENAME,ESAL,EADDR,ENO) values ('王五',12000,'广州','3');
    Insert into emp (ENAME,ESAL,EADDR,ENO) values ('赵六',7800,'深圳','4');
    Insert into emp (ENAME,ESAL,EADDR,ENO) values ('钱七',9000,'上海','5');

举例

--查询emp表中赵六的个人信息，打印姓名、薪水和地址
    DECLARE
      --姓名 声明变量并直接赋值
      v_name   emp.ename%TYPE;
      --薪水
      v_sal    emp.esal%TYPE;
      --地址
      v_addr   emp.eaddr%TYPE;
    BEGIN
      --查询表中的姓名和薪水并赋值给变量
      --注意查询的字段和赋值的变量的顺序、个数、类型需要一致
      SELECT ename,esal,eaddr INTO v_name,v_sal,v_addr FROM emp WHERE ename = '赵六';
      --打印结果
      dbms_output.put_line('姓名：' || v_name || ' 薪水：' || v_sal || ' 地址：' || v_addr);
    END;

结果为：  
![在这里插入图片描述][70 3]

##### 1.2.3 记录型变量 #####

接收表中的一整行记录，相当于java中的一个对象  
语法：变量名称 表名%rowtype，例如：v\_emp emp%type;  
如果一张表有几十个字段，程序中需要使用这些字段，那定义一个记录型变量则可以方便的解决这个问题。  
举例：

--查询emp表中赵六的个人信息，打印姓名、薪水和地址
    DECLARE
      --定义记录型变量
      v_emp emp%ROWTYPE;
    BEGIN
      --记录型变量默认接收表中的一行数据，不能指定字段
      SELECT * INTO v_emp FROM emp WHERE ename = '赵六';
      --打印结果，通过变量名.属性的方式获取变量中的值
      dbms_output.put_line('姓名：' || v_emp.ename || ' 薪水：' || v_emp.esal || ' 地址：' || v_emp.eaddr);
    END;

结果为：  
![在这里插入图片描述][70 4]  
**使用记录型变量常见的问题及注意点：**  
1）记录型变量只能存储一条完整的行数据，不支持存储部分字段  
![在这里插入图片描述][70 5]  
2）返回的行太多，记录型变量也接收不了  
![在这里插入图片描述][70 6]

#### 1.3 流程控制 ####

##### 1.3.1 条件分支 #####

条件分支的语法一般为

begin
        if 条件1 then 执行1        
    	elsif 条件2 then 执行2
    	else 执行3
        end if;
    end;

举例：

-- 判断emp表中记录数是否超过5条，3-5条，小于3条
    DECLARE
      --声明变量接收emp表中的记录数
      v_count NUMBER;
    BEGIN
      SELECT COUNT(1) INTO v_count FROM emp;
      IF v_count > 5 THEN
        dbms_output.put_line('记录数超过5条，具体数量为：' || v_count);
      ELSIF v_count >= 3 THEN
        dbms_output.put_line('记录数为3~5条，具体数量为：' || v_count);
      ELSE
        dbms_output.put_line('记录数小于3条，具体数量为：' || v_count);
      END IF;
    END;

结果为：  
![在这里插入图片描述][70 7]  
**注意：oracle中所有的字符拼接符都是“||”，而不是“+”。如果报错“数字或值错误”，则检查程序是否使用了“+”号作为拼接符使用。**

##### 1.3.2 循环 #####

oracle中，循环的方式有3钟，for、while、loop，当然，使用goto语法，也能进行循环下面分别列举这4种循环的用法。

-- goto循环
    DECLARE
      x number;
    BEGIN
      x := 0;
      <<repeat_loop>> --循环点
      x := x + 1;
      DBMS_OUTPUT.PUT_LINE(X);
      IF X < 5 THEN
        GOTO repeat_loop; --当x的值小于9时,就goto到repeat_loop
      END IF;
    END;
    /
    --for循环
    DECLARE
      X number; --声明变量
    BEGIN
      x := 1; --给初值
      FOR X IN REVERSE 1 .. 10 LOOP
        --reverse由大到小
        DBMS_OUTPUT.PUT_LINE('内:x=' || x);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('end loop:x=' || X); --x=1
    END;
    /
    --while循环
    DECLARE
      x number;
    BEGIN
      x := 0;
      WHILE x < 9 LOOP
        x := x + 1;
        DBMS_OUTPUT.PUT_LINE('内:x=' || x);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('外:x=' || x);
    END;
    --loop循环
    DECLARE
      x number;
    BEGIN
      x := 0;
      LOOP
        x := x + 1;
        EXIT WHEN x > 9;
        DBMS_OUTPUT.PUT_LINE('内:x=' || x);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('外:x=' || x);
    END;

### 2 游标 ###

#### 2.1 什么是游标 ####

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

#### 2.2 语法 ####

游标的声明：cursor 游标名\[\{参数列表\}\] is 查询语句;  
游标打开： open 游标名;  
游标读取：fetch 游标名 into 变量列表;  
游标关闭：close 游标名;

#### 2.3 游标的属性 ####

<table> 
 <thead> 
  <tr> 
   <th>属性</th> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%rowcount</td> 
   <td>整型</td> 
   <td>获得fetch语句返回的数据行数</td> 
  </tr> 
  <tr> 
   <td>%found</td> 
   <td>布尔型</td> 
   <td>最近的fetch语句返回一行数据则为真，否则为假</td> 
  </tr> 
  <tr> 
   <td>%notfound</td> 
   <td>布尔型</td> 
   <td>与%found返回值相反</td> 
  </tr> 
  <tr> 
   <td>%isopen</td> 
   <td>布尔型</td> 
   <td>游标已经开启时值为真，否则为假</td> 
  </tr> 
 </tbody> 
</table>

其中%notfound是在游标中找不到元素的时候返回true，通常用来判断退出循环。

#### 2.4 创建和使用 ####

##### 2.4.1 不带参数 #####

-- 查询EMP表中所有员工姓名和工资，并依次打印出来
    DECLARE
      -- 声明游标
      CURSOR C_EMP IS SELECT ENAME, ESAL FROM EMP;
      --申明变量接收游标中的数据
      V_NAME EMP.ENAME%TYPE;
      V_SAL  EMP.ESAL%TYPE;
    BEGIN
      --打开游标
      OPEN C_EMP;
      --遍历游标
      LOOP
        -- 获取游标中的数据，如果有的话就赋值给变量
        FETCH C_EMP INTO V_NAME, V_SAL;
        EXIT WHEN C_EMP%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ' 薪水：' || V_SAL);
      END LOOP;
    END;

结果为：  
![在这里插入图片描述][70 8]

##### 2.4.2 带参数 #####

-- 查询EMP表中编号为5的员工姓名和工资，并依次打印出来
    DECLARE
      -- 声明游标
      CURSOR C_EMP(V_NO EMP.ENO%TYPE) IS
        SELECT ENAME, ESAL FROM EMP WHERE ENO = V_NO;
      --申明变量接收游标中的数据
      V_NAME EMP.ENAME%TYPE;
      V_SAL  EMP.ESAL%TYPE;
    BEGIN
      --打开游标
      OPEN C_EMP(5);
      --遍历游标
      LOOP
        -- 获取游标中的数据，如果有的话就赋值给变量
        FETCH C_EMP INTO V_NAME, V_SAL;
        EXIT WHEN C_EMP%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ' 薪水：' || V_SAL);
      END LOOP;
    END;

结果为：  
![在这里插入图片描述][70 9]

### 3 存储过程 ###

#### 3.1 概念 ####

之前编写的plsql程序可以进行表的操作、判断、循环逻辑处理的工作，但是没有办法重复调用。这可以理解为之前的代码全部编写在了main方法中，是匿名的程序，那在java中可以通过封装对象和方法来解决复用的问题。那plsql是将一个个的程序处理过程存储起来进行复用，那这些被存储起来的plsql程序称之为存储过程，相当于java中的方法。

**存储过程的作用：**  
1）在开发程序的过程中，为了一个特定的业务功能，会向数据库进行多次请求查询，需要对数据库进行多次I/O读写，性能比较低，而且数据库的连接关闭是非常耗资源的。如果把这些业务处理放到plsql中，在应用程序中调用plsql就可以做到连接关闭一次数据库就可以实现我们的业务，那将可以大大提升效率。  
2）oracle官方建议：能够让数据库操作的，就不要放到程序中，在数据库中实现基本不会出现错误，而在程序中可能会出错（如果在数据库中操作数据，有一定的日志恢复等功能）。

#### 3.2 语法 ####

create or replace procedure 过程名称[{参数列表}] is
    begin
    
    end [过程名称];

根据参数的类型，可以分为以下3种：  
1）不带参数的

create or REPLACE PROCEDURE p_hello is
      --这里声明变量，declare被省略
    begin
      DBMS_OUTPUT.PUT_LINE('hello');
    end p_hello;
    /
    exec p_hello;

2）带输入参数的

--创建一个查询并打印某个员工的姓名和薪水的存储过程，要求调用的时候传入员工的编号，自动在控制台打印
    CREATE OR REPLACE PROCEDURE QUERYNAMEANDSAL(I_NO EMP.ENO%TYPE) AS
      -- 声明变量
      V_ENAME EMP.ENAME%TYPE;
      V_ESAL  EMP.ESAL%TYPE;
    BEGIN
      SELECT ENAME, ESAL INTO V_ENAME, V_ESAL FROM EMP WHERE ENO = I_NO;
      DBMS_OUTPUT.PUT_LINE(V_ENAME || ' ' || V_ESAL);
    END QUERYNAMEANDSAL;
    /
    exec QUERYNAMEANDSAL(5);

3）带输入参数和输出参数（返回值）的

--创建一个查询并打印某个员工薪水的存储过程，要求调用的时候传入员工的编号,程序中获取输出
    CREATE OR REPLACE PROCEDURE QUERYNAMEANDSAL02(I_NO in EMP.ENO%TYPE ,O_SAL out EMP.ESAL%TYPE) AS
    BEGIN
      SELECT ESAL INTO O_SAL FROM EMP WHERE ENO = I_NO;
    END QUERYNAMEANDSAL02;
    /
    -- 以下程序在java中编写
    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
            String userName = "gd_jianguan";
            String userpwd = "gd_jianguan";
            Connection conn = DriverManager.getConnection(url, userName, userpwd);
            //获取语句对象
            String sql = "{call QUERYNAMEANDSAL02(?,?)}";
            CallableStatement call = conn.prepareCall(sql);
            //设置输入参数
            call.setInt(1, 5);
            //注册输出参数
            call.registerOutParameter(2, OracleTypes.DOUBLE);
            //执行存储过程
            call.execute();
            //获取输出参数
            double sal = call.getDouble(2);
            System.out.println("薪水：" + sal);
            //释放资源
            call.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

[70]: /images/20220511/ef2b93db14554f1da53d5b83c61ef147.png
[70 1]: /images/20220511/5026e179e67642498b895a1a80e6eea6.png
[70 2]: /images/20220511/eaa0e0cc0708468085cb8da01a7608a5.png
[70 3]: /images/20220511/9e0f0a08e316488d865ac44384d6bb90.png
[70 4]: /images/20220511/e7c11afd23b74d4ba28bd09108ac2038.png
[70 5]: /images/20220511/3e9854769066469994067b745e0c73e0.png
[70 6]: /images/20220511/8de4e3e56e914ccdbce02ca9484960e8.png
[70 7]: /images/20220511/ed76a1294f80420985a8978a796a1e26.png
[70 8]: /images/20220511/03e671fc640c4f8d821f322c9ffc0aee.png
[70 9]: /images/20220511/27a1983df631478eaf264d068bf88145.png