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

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 ifdeclare --当没有变量的定义时，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循环declarebegin  --此处的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 游标名称;--案例：--无参数的游标declarecursor 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;

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

--定义带参数的游标declarecursor 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循环提取游标值:隐式的打开游标和关闭游标declarecursor 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 varchar2is    --变量声明    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代表变化后行记录

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

案例：

--定义前置触发器--自动计算每月用水量create trigger tri_account_usenumbefore --创建前置触发器update of num1 --触发器作用在某列的值改变on t_accountfor each rowdeclare--声明变量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_updateafterupdate of Name --在业主表的name列上创建触发器on t_ownersfor each rowdeclare--声明变量begin  --触发器中的dml语句会自动提交事务，不用手动提交事务  insert into t_owners_log values (sysdate,:old.name,:new.name,:old.id);end;