JavaWeb开发知识总结(四)-(oracle_编程)

今天药忘吃喽~ 2022-06-14 10:28 160阅读 0赞

# JavaWeb开发知识总结(Oracle-编程) #

## **1. PL/SQL：Oracle特有** ##

### **1.1 PL/SQL概述** ###

PL/SQL（Procedure Language/SQL）是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

**基本的语法结构：**

--语法：变量声明和异常处理可以没有,当时begin..end块必须存在
    [declare
        声明变量;
    ]
    begin
        逻辑代码;
        [exception
            异常处理逻辑;
        ]
    end;

### **1.2 变量** ###

**变量声明：**

--语法：在declare中声明变量
    --1.只声明变量，不赋值
    变量名称  变量类型(长度); --如：v_name varchar2(30);
    --2.声明时就给初始值
    变量名称  变量类型(长度):=值; --如：v_name varchar2(30):='somnus';

**变量的赋值：**

--变量赋值方式：
    --1.声明时就给初始值
    变量名称  变量类型(长度):=值; --如：v_name varchar2(30):='somnus';
    --2.先声明，在begin..end块中进行赋值
    变量名:=值; --如：v_name:='somnus';
    --3.先声明，在begin..end块中通过select 字段名 into 变量名 from 表名 条件;方式进行赋值
    select 字段名 into 变量名 from 表名 条件;

**案例：**

--1.变量声明和赋值
    declare
      v_price number(10,2); -- 水费单价
      v_usenum number;--水表数字
      v_usenum2 number(10,2);--水的吨数
      v_money number(10,2); --水费金额
    begin
      --赋值
      v_price:=2.45;
      v_usenum:=9527;
      v_usenum2:=round(v_usenum/1000);
      v_money:=v_price*v_usenum2;
      --输出值
      dbms_output.put_line('用水量/吨:'||v_usenum2);
      dbms_output.put_line('金额/元:'||v_money);
    end; --要有分号
    --2.从表中查询数据到变量中 select 列名 into 变量名from 表名 [条件]
    declare
      v_price number(10,2); -- 水费单价
      v_usenum number;--水表数字
      v_usenum2 number(10,2);--水的吨数
      v_money number(10,2); --水费金额
    begin
      --赋值
      v_price:=2.45;
      --从水费台帐表中查询数据到变量中
      select usenum into v_usenum from t_account where year='2016' and month='01' and id = 1;
      v_usenum2:=round(v_usenum/1000);
      v_money:=v_price*v_usenum2;
      --输出值
      dbms_output.put_line('用水量/吨:'||v_usenum2);
      dbms_output.put_line('金额/元:'||v_money);
    end; --要有分号

### **1.3 属性类型** ###

如果定义的变量的类型依赖于具体表中的某列(引用型属性)或查询表结果集的中行(记录类型)，则可以通过定义的方式的使得变量依赖于具体的列类型或行类型。

**引用类型：`变量名称 表名.列名%type;`**

--案例：
    declare
      v_price number(10,2); -- 水费单价
      v_usenum t_account.usenum%type;--水表数字
      v_usenum2 number(10,2);--水的吨数
      v_money number(10,2); --水费金额
    begin
      --赋值
      v_price:=2.45;
      --从表中查询数据到变量中
      select usenum into v_usenum from t_account where year='2016' and month='01' and id = 1;
      v_usenum2:=round(v_usenum/1000);
      v_money:=v_price*v_usenum2;
      --输出值
      dbms_output.put_line('用水量/吨:'||v_usenum2);
      dbms_output.put_line('金额/元:'||v_money);
    end; --要有分号

**记录类型：`变量名称 表名%rowtype;` 将结果集中一行记录封装到变量中**

declare
      v_price number(10,2); -- 水费单价
      v_account t_account%rowtype;--将查询的一行记录封装到一个变量中
      v_usenum2 number(10,2);--水的吨数
      v_money number(10,2); --水费金额
    begin
      --为变量赋值
      v_price:=2.45;
      --从表中查询一行数据封装到一个变量中
      select * into v_account from t_account where year='2016' and month='01' and id = 1;
      --通过记录类型变量.列名获取一行记录中某列的值
      v_usenum2:=round(v_account.usenum/1000); -- 通过变量.列名调用某列的值
      v_money:=v_price*v_usenum2;
      --输出值
      dbms_output.put_line('用水量/吨:'||v_usenum2);
      dbms_output.put_line('金额/元:'||v_money);
    end; --要有分号

### **1.4 异常** ###

在运行程序时出现的错误叫做异常/例外，发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分  
异常有两种类型：  
1. 预定义异常：当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发，一共有21种异常；  
2. 用户定义异常：用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发。

**异常处理的语法：**

--语法：在begin..end块中定义
    begin
        处理逻辑;
        exception
            when 异常类型 then
                异常处理逻辑
    end;

**案例：**

--一般使用系统预定义异常：
    --可能多个异常时，需要有多个when..then语句
    --异常/例外  预定义异常21种和其他用户自定义异常
    declare
      v_num number;
    begin
      v_num:=1/0; --除0异常
      exception
        when ZERO_DIVIDE --除数不能为0的异常关键字
          then
            dbms_output.put_line('除数不能为0');
        when NO_DATA_FOUND --使用 select into 未返回行
          then
            dbms_output.put_line('没有返回数据');
    end;

### **1.5 条件判断：if语法** ###

**判断语句if的语法：**

--在begin..end块中使用判断语句：注意elsif中不是else if
    declare --当没有变量的定义时，declare可以省略
    begin
      --if的格式1：if语句
      if 1<2 then
        dbms_output.put_line('1小于2');
      end if;
      --if的格式2：if..else语句
      if 1>2 then
        dbms_output.put_line('1大于2');
      else
         dbms_output.put_line('1小于2');
      end if;
      --if的格式3：if..elsif..elsif..else语句
      if 1>2 then
        dbms_output.put_line('1大于2');
      elsif 1=2 then
         dbms_output.put_line('1等于2');
      else
        dbms_output.put_line('1小于2');
      end if;
    end;

### **1.6 循环：** ###

**无条件循环：**

--语法：循环语句定义在begin..end块中
    loop
        循环体;
    end loop;
    --案例：
    declare
      v_num number:=1;
    begin
      loop
        dbms_output.put_line(v_num);
        v_num:=v_num+1;
        --退出循环方式1
        if v_num >= 100 then
          exit;
        end if;
        --退出循环方式2
        exit when v_num >= 100;
      end loop;
    end;

**有条件循环：**

--语法：循环语句定义在begin..end块中
    while 条件;--条件是指循环能够执行的条件
    loop
        循环体;
    end loop;
    --案例：
    declare
      v_num number:=1;
    begin
      while v_num < 100 --循环的条件
      loop
        dbms_output.put_line(v_num);--输出变量的值
        v_num:=v_num+1; -- 变量加1
      end loop;
    end;

**for循环：**

--语法：循环语句定义在begin..end块中
    for 变量 in 起始值..终止值
    loop
        循环体;
    end loop;
    --案例：
    --使用for循环
    declare
    begin
      --此处的x变量相当于是局部变量，不用再定义，并且x只能在loop循环体内使用
      for x in 1..100
      loop
        dbms_output.put_line(x);
      end loop;
    end;
    --for循环，可以使用变量控制循环的数据范围
    declare
     v_num number:=50;
    begin
      --此处的x变量相当于是局部变量，不用再定义，并且x只能在loop循环体内使用
      for x in 1..v_num
      loop
        dbms_output.put_line(x);
      end loop;
    end;

### **1.7 游标** ###

游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。我们可以把游标理解为 PL/SQL 中的结果集。查询的数据存放在内存中缓冲区中，降低了服务器性能。游标打开时默认指向的是结果集中第一行的上面，当通过fetch抓取数据时才使得游标指向第一条数据。

**定义无参数游标语法：**

--语法：
    cursor 游标名称 is SQL 语句;

**无参数游标使用语法：**

--语法：
    open 游标名称;
    loop
      fetch 游标名称 into 变量;
      exit when 游标名称%notfound;
    end loop;
    close 游标名称;
    --案例：
    --无参数的游标
    declare
    cursor cur_pricetable is 
    select * from t_pricetable where ownertypeid=1;
    --获取游标中存储的值
    v_price t_pricetable%rowtype;
    begin
      open cur_pricetable;
      loop
        fetch cur_pricetable into v_price; --获取游标中的值到行变量中
        exit when cur_pricetable%notfound; --游标的值取完后退出循环
        dbms_output.put_line(v_price.price||'--'||v_price.minnum||'--'||v_price.maxnum);
        --退出语句放在输出语句后面，会将最后一个值输出两次
        --原因是：只有fetch语句才会抓取下一个值，当是最后一个值，输出语句进行输出，
        --判断条件判断的是当前的结果是最后一个值，下次循环，fetch抓取的是空值，但
        --输出语句还是会继续输出最后一个值，到退出语句才会结束循环
        --exit when cur_pricetable%notfound; --游标的值取完后退出循环
      end loop;
      close cur_pricetable;
    end;

**有参数游标的定义与使用：**

--定义带参数的游标
    declare
    cursor cur_pricetable2(v_ownertypeid number) is  --定义游标的参数
    select * from t_pricetable where ownertypeid=v_ownertypeid;
    --获取游标中存储的值
    v_price t_pricetable%rowtype;
    begin
      open cur_pricetable2(3); --调用带参数的游标需要传入参数
      loop
        fetch cur_pricetable2 into v_price; --获取游标中的值到行变量中
        exit when cur_pricetable2%notfound; --游标的值取完后退出循环
        dbms_output.put_line(v_price.price||'--'||v_price.minnum||'--'||v_price.maxnum);
      end loop;
      close cur_pricetable2;
    end;

**通过for循环使用游标：**

--for循环提取游标值:隐式的打开游标和关闭游标
    declare
    cursor cur_pricetable2(v_ownertypeid number) is  --定义游标的参数
    select * from t_pricetable where ownertypeid=v_ownertypeid;
    --获取游标中存储的值
    v_price t_pricetable%rowtype;
    begin
      for v_price in cur_pricetable2(1)
      loop
        dbms_output.put_line(v_price.price||'--'||v_price.minnum||'--'||v_price.maxnum);
      end loop;
    end;

## **2. 存储函数：Oracle和mysql通用** ##

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用 P/SQL 进行逻辑的处理。

**创建存储过程语法：**

--语法：
    CREATE [ OR REPLACE ] FUNCTION 函数名称
    （参数名称 参数类型, 参数名称 参数类型, ...）
        RETURN 结果变量数据类型
    IS
        变量声明部分;
    BEGIN
        逻辑部分;
        RETURN 结果变量;
        [EXCEPTION
        异常处理部分]
    END;
    --注意事项：
    函数的参数声明时不能加参数长度;
    函数的返回值不能添加类型的长度;

**案例：**

--存储函数可以嵌入SQL语句中，返回的是一个值或对象
    create or replace function fn_getAddress
    (v_id number)
    return varchar2
    is
        --变量声明
        v_name varchar2(20);
    begin
      select name into v_name from t_address where id = v_id;
      return v_name;
    end;
    --调用存储函数
    select fn_getAddress(1) from dual; --简单的调用存储函数
    --调用存储函数作为select语句字段
    select id,name,fn_getaddress(addressid) from t_owners where id=1;

## **3. 存储过程（重点）：Oracle和mysql通用** ##

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。存储过程的缺点：数据库中表之间耦合度较高，不利于功能需求的更改。

存储过程：能够将开发中的业务逻辑进行封装，其思想和hibernate框架思想相反。

**存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：**

1.  存储函数中有返回值，且必须返回；存储过程没有返回值，但可以通过传出参数返回多个值。
2.  存储函数可以在 select 语句中直接使用；存储过程不能直接在select语句中使用，存储过程多是被应用程序(java)调用。
3.  存储函数一般都是封装一个查询结果；存储过程一般都封装一段事务代码，进行事务操作的控制。

**存储过程语法结构：**

--语法：
    CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
    （参数名 [in|out|in out] 类型, 参数名 [in|out|in out] 类型, 参数名 [in|out|in out] 类型...）
    IS|AS --两者都可以，但必须有一个
        变量声明部分;
    BEGIN
        逻辑部分;
        [EXCEPTION
        异常处理部分]
    END;
    --注意事项：
    参数声明时不能指定类型的长度;
    参数的in|out说明：in代表该参数是传入参数，out代表该参数是传出参数
        声明参数为in：声明该参数是传入参数
        声明参数为out：声明该参数是传出参数
        声明参数为in out：声明该参数是传入传出参数
    无传出参数的存储过程调用：
        call方式调用；
        begin...end方式调用；
        JDBC中调用；
    有传出参数的存储过程调用：不能使用call调用，无法获取传出参数
        begin...end方式调用；
        JDBC中调用；

**案例1：无参数的存储过程创建**

--不带传出参数的存储过程
    create sequence seq_owners start with 18;--创建自动增长序列,用于设置表的id列主键
    --创建存储过程
    create or replace procedure pro_owners_add
    (
    v_name varchar2, --传入参数默认省略in关键字
    v_addressid number,
    v_housenumber varchar2,
    v_watermeter varchar2,
    v_ownertypeid number
    )
    is
    --声明变量
    begin
      insert into t_owners values (seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter
                                  ,sysdate,v_ownertypeid);
    end;

**案例1：无参数的存储过程调用–call和begin..end方式调用**

--调用不带传出参数的存储过程
    --调用方式1：
    call pro_owners_add('sunmos',1,'2-1','0729-0809',1);commit;
    --调用方式2：
    begin
      pro_owners_add('sunmos',1,'2-1','0729-0809',1);
      commit;
    end;

**案例1：无参数的存储过程调用–jdbc方式调用**

/**
     * 注意事项：调用方式：{call pro_owners_add(?,?,?,?,?)}，必须要加{},传入参数
     * 调用数据库的无传出参数的存储过程
     */
    public void pro_add(Owners owners) {
        // 声明数据库连接对象
        Connection conn = null;
        // 声明执行存储过程的对象
        CallableStatement stmt = null; // 存储过程使用的CallableStatement对象
    
        try {
            // 获取连接
            conn = BaseDao.getConnection();
            // 预编译存储过程
            stmt = conn.prepareCall("{call pro_owners_add(?,?,?,?,?)}");
            // 设置参数
            stmt.setString(1, owners.getName());
            stmt.setLong(2, owners.getAddressid());
            stmt.setString(3, owners.getHousenumber());
            stmt.setString(4, owners.getWatermeter());
            stmt.setLong(5, owners.getOwnertypeid());
            // 执行sql语句
            stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            // 释放资源
            BaseDao.closeAll(conn, stmt, null);
        }
    }

**案例2：有传出参数存储过程的创建**

--创建带传出参数的存储过程
    create or replace procedure pro_owners_add2
    (
    v_name varchar2,
    v_addressid number,
    v_housenumber varchar2,
    v_watermeter varchar2,
    v_ownertypeid number,
    v_id out number --传出参数需要定义out关键字
    )
    is -- is和as均可以
    begin
      --将添加的业主的id值存储到传出变量中
      select seq_owners.nextval into v_id from dual;
      insert into t_owners values (v_id,v_name,v_addressid,v_housenumber,v_watermeter
                                  ,sysdate,v_ownertypeid);
      commit; --dml操作需要使用commit提交事务
    end;

**案例2：有传出参数存储过程的调用–begin..end方式调用**

--调用带传出参数的存储过程
    --只能通过begin...end语句进行调用，不能通过call调用
    declare
      v_id number; --声明接收传出参数的变量
    begin
      --将传出参数作为存储过程的对应的参数
      pro_owners_add2('sunmos',1,'2-1','0729-0809',1,v_id);
      dbms_output.put_line(v_id);--输出传出参数值
    end;

**案例2：有传出参数存储过程的调用–JDBC方式调用**

/**
     * 注意事项：
     *  调用方式：{call pro_owners_add(?,?,?,?,?)}，必须要加{},传入参数；
     *  获取传出参数必须在执行SQL语句后才能获取传出参数值；
     * 调用数据库的有传出参数的存储过程
     */
    public Long pro_add2(Owners owners) {
        // 定义传出的数据的值
        Long v_id = 0L;
        // 声明数据库连接对象
        Connection conn = null;
        // 声明执行存储过程的对象
        CallableStatement stmt = null; // 存储过程使用的CallableStatement对象
    
        try {
            // 获取连接
            conn = BaseDao.getConnection();
            // 预编译存储过程
            stmt = conn.prepareCall("{call pro_owners_add2(?,?,?,?,?,?)}");
            // 设置参数
            stmt.setString(1, owners.getName());
            stmt.setLong(2, owners.getAddressid());
            stmt.setString(3, owners.getHousenumber());
            stmt.setString(4, owners.getWatermeter());
            stmt.setLong(5, owners.getOwnertypeid());
            // 设置传出参数的类型,最后一个参数是传出参数,类型时number类型
            stmt.registerOutParameter(6, OracleTypes.NUMBER);//OracleTypes是Oracle驱动包中的类
            // 执行SQL语句
            stmt.execute();
            // 在存储过程执行完毕后获取传出参数值
            v_id = stmt.getLong(6);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            BaseDao.closeAll(conn, stmt, null);
        }
        return v_id;
    }

## **4. 触发器：Oracle和mysql通用** ##

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。触发器是作用表上的监听DML操作并执行预定义的逻辑代码。触发器分为前置触发器和后置触发器。当表被删除时，作用在表上的触发器会被自动删除。

**触发器的作用：** 数据确认；实施复杂的安全性检查；做审计，跟踪表上所做的数据操作等； 数据的备份和同步。

**触发器的定义：**

--语法：
    CREATE [or REPLACE] TRIGGER 触发器名
        BEFORE | AFTER
        [DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
        ON 表名
        [FOR EACH ROW ] [WHEN(条件) ]
    declare
        变量声明;
    begin
        逻辑代码块;
    End;
    --注意事项：
    before：代表是前置触发器；执行语句会先被挂起，如果触发器执行过程抛出异常，挂起的sql语句不能执行；
    after：代表是后置触发器；执行语句执行后，再执行触发器；
    [DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]：表示该表发生那种DML操作时触发该触发器，如果需要监听多个DML操作，则使用or关键字连接；如果是监听update操作，还可以指定要监听该表的具体列，监听多列时，列名用逗号隔开；
    [FOR EACH ROW ]：表示该触发器的类别是行级触发器，缺省时代表是语句级触发器；
    [WHEN(条件) ]：表示该触发器执行的条件，默认是true，执行;
    触发器中的dml语句会自动commit提交，不用手动提交.

**行级触发器和语句触发器的区别：**

行级触发器：for each row,一次语句执行，执行一次触发器中的逻辑;
    语句级触发器：没有伪记录变量  多次的语句执行，才执行一次触发器中的逻辑;

**触发器中的伪记录变量：每一个触发器都有默认的old和new变量，old代表变化之前的行记录，new代表变化后行记录**

<table> 
 <thead> 
  <tr> 
   <th>触发语句</th> 
   <th>:old变量</th> 
   <th>:new变量</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>insert语句</td> 
   <td>所有的字段均为null</td> 
   <td>将要插入的行数据</td> 
  </tr> 
  <tr> 
   <td>update语句</td> 
   <td>更新该行记录之前的行记录值</td> 
   <td>更该行后的行记录值</td> 
  </tr> 
  <tr> 
   <td>delete语句</td> 
   <td>删除该行之前的行记录值</td> 
   <td>所有的字段均为null</td> 
  </tr> 
 </tbody> 
</table>

**案例：**

--定义前置触发器
    --自动计算每月用水量
    create trigger tri_account_usenum
    before --创建前置触发器
    update of num1 --触发器作用在某列的值改变
    on t_account
    for each row
    declare
    --声明变量
    begin
      :new.usenum:=:new.num1-:new.num0;
    end;
    
    --定义后置触发器
    --当业主名称修改时，将修改之前的信息保存到信息记录表中
    --创建存储用户信息修改的日志表
    create table t_owners_log(
    v_updatetime date,
    v_oldname varchar2(30),
    v_newname varchar2(30),
    v_ownersid number);
    --创建后置触发器
    create trigger tri_owners_update
    after
    update of Name --在业主表的name列上创建触发器
    on t_owners
    for each row
    declare
    --声明变量
    begin
      --触发器中的dml语句会自动提交事务，不用手动提交事务
      insert into t_owners_log values (sysdate,:old.name,:new.name,:old.id);
    end;