栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

oracle(四)

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

oracle(四)

一、PL/SQL 1.1什么是 PL/SQL

pl/sql说白了就是数据库中的一段脚本代码。

PL/SQL ( Procedure Language/SQL )是 Oracle 对 sql 语言的过程化扩展,指 在 SQL 命令语言中增加了过程处理语句(如分支、循环等),使 SQL 语言具有 过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起 来,使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。 基本语法结构 [ declare -- 声明变量 ] begin -- 代码逻辑 [ exception -- 异常处理 ] end ; 1.2变量

变量是存储数据的,数据库的声明变量和java的声明变量是相反的。赋值是:=的。

声明变量的语法: 变量名 类型(长度); 变量赋值的语法: 变量名 := 变量值 变量的声明 需求: 声明变量水费单价、水费字数、吨数、金额。 对水费单价、字数、进行赋值 。吨数根据水费字数换算,规则为水费字数除以 1000 ,并且四舍五入,保留两位小数。计算金额,金额 = 单价 * 吨数。 输出单价 、数量和金额。 -- 变量的用法 -- 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:= 8012 ; -- 字数 -- 字数换算为吨数 v_usenum2:= round ( v_usenum/ 1000 , 2 ); -- 计算金额 v_money:= round (v_price*v_usenum2, 2 ); dbms_output.put_line( ' 单价: ' ||v_price|| ' 吨 数 :' ||v_usenum2|| ' 金额: ' ||v_money); end ;

Select into 方式 赋值 刚才的赋值是直接赋值的,有时候我们要用到赋值是从数据库里面查到的结果,这时候用到select into了。 语法结构: select 列名 into 变量名 from 表名 where 条件 注意:结果必须是一条记录 ,有多条记录和没有记录都会报错 declare v_price number ( 10 , 2 ); -- 单价 v_usenum number ; -- 水费字数 v_num0 number ; -- 上月字数 v_num1 number ; -- 本月字数 v_usenum2 number ( 10 , 2 ); -- 使用吨数 v_money number ( 10 , 2 ); -- 水费金额 begin -- 对单价进行赋值 v_price:= 3.45 ; -- 变量赋值 select usenum,num0,num1 into v_usenum,V_num0,V_num1 from T_ACCOUNT where year = '2012' and month = '01' and owneruuid= 1 ; v_usenum2:= round (v_usenum/ 1000 , 2 ); v_money:=v_price*v_usenum2; DBMS_OUTPUT.put_line( ' 单价: ' ||v_price|| ' 吨数: ' ||v_usenum2|| ' 金额: ' ||v_money|| ' 上月字数: ' ||v_num0|| ' 本月 字数 ' ||v_num1); end ;

1.3属性类型--2种(推荐记录型)

属性类型有2种,引用型和记录型。

%TYPE 引用型

作用:引用某表某列的字段类型 declare v_price number ( 10 , 2 ); -- 单价 v_usenum T_ACCOUNT.USENUM% TYPE ; -- 水费字数 v_num0 T_ACCOUNT.NUM0% TYPE ; -- 上月字数 v_num1 T_ACCOUNT.NUM1% TYPE ; -- 本月字数 v_usenum2 number ( 10 , 2 ); -- 使用吨数 v_money number ( 10 , 2 ); -- 水费金额 begin -- 对单价进行赋值 v_price:= 3.45 ; --v_usenum:=8090; select usenum,num0,num1 into v_usenum,V_num0,V_num1 from T_ACCOUNT where year = '2012' and month = '01' and owneruuid= 1 ; -- 使用吨数 v_usenum2:= round (v_usenum/ 1000 , 2 ); -- 计算金额 v_money:=v_price*v_usenum2; DBMS_OUTPUT.put_line( ' 单价: ' ||v_price|| ' 吨数: ' ||v_usenum2|| ' 金额: ' ||v_money|| ' 上月字数: ' ||v_num0|| ' 本月 字数 ' ||v_num1); 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:= 3.45 ; -- 赋值 select * into v_account from T_ACCOUNT where year = '2012' and month = '01' and owneruuid= 1 ; -- 使用吨数 v_usenum2:= round (v_account.usenum/ 1000 , 2 ); -- 计算金额 v_money:=v_price*v_usenum2; DBMS_OUTPUT.put_line( ' 单价: ' ||v_price|| ' 吨数: ' ||v_usenum2|| ' 金额: ' ||v_money|| ' 上月字数: ' ||v_account.num0|| ' 本月字数 ' ||v_account.num1); end ;

1.4异常

在运行程序时出现的错误叫做异常/例外。 发生异常后,语句将停止执行,控制权转移到 PL/SQL 块的异常处理部分 异常有两种类型: 预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式 引发 用户(自)定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常,自定义的 异常通过 RAISE 语句显式引发 预定义异常 Oracle 预定义异常 21 个 NO_DATA_FOUND:就是执行select into 的时候,没有符合条件的记录,没有结果返回,就不能给变量赋值,所以会报错。 TOO_MANY_ROWS:就是执行select into 的时候,就是没有返回行,就会报 NO_DATA_FOUND的错误,如果返回的行多处一行,就会报 TOO_MANY_ROWS的错误。你要把结果集某一列给变量,返回很多行的你就不知道哪一行了。 select into要求你的结果集只有一条记录,多行记录就会报错(多一行或者少了都会报错) 语法结构: exception when 异常类型 then 异常处理逻辑 根据上例中的代码,添加异常处理部分 -- 变量的用法 -- declare v_price number ( 10 , 2 ); -- 水费单价 v_usenum T_ACCOUNT.USENUM% type ; -- 水费字数 v_usenum2 number ( 10 , 3 ); -- 吨数 v_money number ( 10 , 2 ); -- 金额 begin v_price:= 2.45 ; -- 水费单价 select usenum into v_usenum from T_ACCOUNT where owneruuid= 1 and year = '2012' and month = '01' ; -- 字数换算为吨数 v_usenum2:= round ( v_usenum/ 1000 , 3 ); -- 计算金额 v_money:= round (v_price*v_usenum2, 2 ); dbms_output.put_line( ' 单价: ' ||v_price|| ' 吨 数 :' ||v_usenum2|| ' 金额: ' ||v_money); exception when NO_DATA_FOUND then dbms_output.put_line( ' 未找到数据,请核实 ' ); when TOO_MANY_ROWS then dbms_output.put_line( ' 查询条件有误,返回多条信息,请核实 ' ); end ;

1.5条件判断--3种写法

基本语法 1 if 条件 then 业务逻辑 end if ; 基本语法 2 if 条件 then 业务逻辑 else 业务逻辑 end if ; 基本语法 3 if 条件 then 业务逻辑 elsif 条件 then 业务逻辑 else 业务逻辑 end if ;

需求:设置三个等级的水费 5 吨以下 2.45 元 / 吨 5 吨到 10 吨部分 3.45 元 / 吨 , 超过 10 吨部分 4.45 ,根据使用水费的量来计算阶梯水费。 declare v_price1 number ( 10 , 2 ); -- 不足 5 吨的单价 v_price2 number ( 10 , 2 ); -- 超过 5 吨不足 10 吨单价 v_price3 number ( 10 , 2 ); -- 超过 10 吨单价 v_account T_ACCOUNT% ROWTYPE ; -- 记录型 v_usenum2 number ( 10 , 2 ); -- 使用吨数 v_money number ( 10 , 2 ); -- 水费金额 begin -- 对单价进行赋值 v_price1:= 2.45 ; v_price2:= 3.45 ; v_price3:= 4.45 ; -- 赋值 select * into v_account from T_ACCOUNT where year = '2012' and month = '01' and owneruuid= 1 ; -- 使用吨数 v_usenum2:= round (v_account.usenum/ 1000 , 2 ); -- 计算金额 ( 阶梯水费 ) if v_usenum2<= 5 then -- 第一个阶梯 v_money:=v_price1*v_usenum2; elsif v_usenum2> 5 and v_usenum2<= 10 then -- 第二个阶梯 v_money:=v_price1* 5 + v_price2*( v_usenum2- 5 ); else -- 第三个阶梯 v_money:=v_price1* 5 +v_price2* 5 + v_price3*( v_usenum2- 10 ); end if ; DBMS_OUTPUT.put_line( ' 吨数: ' ||v_usenum2|| ' 金额: ' ||v_money|| ' 上月字数: ' ||v_account.num0|| ' 本月字数 ' ||v_account.num1); exception when NO_DATA_FOUND then DBMS_OUTPUT.put_line( ' 没有找到数据 ' ); when TOO_MANY_ROWS then DBMS_OUTPUT.put_line( ' 返回的数据有多行 ' ); end ;

1.6循环--3种写法

1 .无条件循环 语法结构 loop -- 循环语句 end loop ; 范例:输出从 1 开始的 100 个数 declare v_num number := 1 ; begin loop dbms_output.put_line(v_num); v_num:=v_num+ 1 ; exit when v_num> 100 ; end loop ; end ;

2 、条件循环 有条件循环就是多了一个while条件,其他的都是一样的。就是这个while条件成立的时候才循环。跟无条件循环刚好相反,它是条件不成立才循环。

语法结构 while 条件 loop end loop ; 范例:输出从 1 开始的 100 个数 declare v_num number := 1 ; begin while v_num<= 100 loop dbms_output.put_line(v_num); v_num:=v_num+ 1 ; end loop ; end ; 3 、 for 循环 for循环很简单,变量都省了,我们一般用for循环和无条件loop循环较多。 基本语法 for 变量 in 起始值 .. 终止值 loop end loop ; 范例:输出从 1 开始的 100 个数 begin for v_num in 1 ..100 loop dbms_output.put_line(v_num); end loop ; end ;

 

1.7游标 1.什么是游标 游标就是一个结果集,用来展示你的查询结果的,这个结果集可以用来循环,游标相当于jdbc中的结果集,这个结果集通过next就可以读取下一行的数据,读取一行就取出一行。有时候我们需要把查询的结果作为一个变量,变量的时候我们讲过select。。into要求你的每一行必须有一条记录,如果查询的多条记录的话则需要游标了。 游标是系统为用户开设的一个数据缓冲区 , 存放 SQL 语句的执行结果。我们 可以把游标理解为 PL/SQL 中的结果集。

打开游标,逐行提取每一条记录,打开游标的时候有个小指针指向第一条记录的上面,提取的时候就会指向第一条,每提一行就往下走,最后需要关闭游标。

2.语法结构及示例 在声明区声明游标,语法如下: cursor 游标名称 is SQL 语句; 使用游标语法 open 游标名称 loop fetch 游标名称 into 变量 exit when 游标名称%notfound end loop; close 游标名称 先用cursor声明游标,然后再使用open使用游标,然后进行循环,通过fetch提取游标into给变量。然后游标走到(游标名称%notfound)就退出。 还不如用for循环,都不用打开游标。 需求:打印业主类型为 1 的价格表 代码: declare v_pricetable T_PRICETABLE% rowtype ; -- 价格行对象 cursor cur_pricetable is select * from T_PRICETABLE where ownertypeid= 1 ; -- 定义游标 begin open cur_pricetable; -- 打开游标 loop fetch cur_pricetable into v_pricetable; -- 提取游标到变量 exit when cur_pricetable% notfound ; -- 当游标到最后一行下面退 出循环 dbms_output.put_line( ' 价格 :' ||v_pricetable.price || ' 吨位: ' ||v_pricetable.minnum|| '-' ||v_pricetable.maxnum ); end loop ; close cur_pricetable; -- 关闭游标 end ; 运行结果如下:

3.带参数的游标---就是形参和实参 我们的查询语句的条件值有可能是在运行时才能决定的,比如性业主类型, 可能是运行时才可以决定,那如何实现呢?我们接下来学习带参数的游标,修改 上述案例 declare v_pricetable T_PRICETABLE% rowtype ; -- 价格行对象 cursor cur_pricetable(v_ownertypeid number ) is select * from T_PRICETABLE where ownertypeid=v_ownertypeid; -- 定义游 标 begin open cur_pricetable( 2 ); -- 打开游标 loop fetch cur_pricetable into v_pricetable; -- 提取游标到变量 exit when cur_pricetable% notfound ; -- 当游标到最后一行下面退 出循环 dbms_output.put_line( ' 价格 :' ||v_pricetable.price || ' 吨 位: ' ||v_pricetable.minnum|| '-' ||v_pricetable.maxnum ); end loop ; close cur_pricetable; -- 关闭游标 end ;

 

4. for 循环提取游标值 我们每次提取游标,需要打开游标 关闭游标 循环游标 提取游标 控制循环的 退出等等,好麻烦!有没有更简单的写法呢?有!用 for 循环一切都那么简单, 上例的代码可以改造为下列形式 declare cursor cur_pricetable(v_ownertypeid number ) is select * from T_PRICETABLE where ownertypeid=v_ownertypeid; -- 定义游 标 begin for v_pricetable in cur_pricetable( 3 ) loop dbms_output.put_line( ' 价格 :' ||v_pricetable.price || ' 吨 位: ' ||v_pricetable.minnum|| '-' ||v_pricetable.maxnum ); end loop ; end ;

二、存储函数 2.1什么是存储函数

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

创建或修改存储过程的语法如下 : CREATE [ OR REPLACe ] FUNCTION 函数名称 (参数名称 参数类型, 参数名称 参数类型, ...) RETURN 结果变量数据类型 IS 变量声明部分; BEGIN 逻辑部分; RETURN 结果变量; [EXCEPTION 异常处理部分] END; 注意:参数类型只写类型,不写长度的。 下面is就是类似于pl/sql,其实后面的存储函数,储存过程,触发器都是以pl/sql为基础的。 2.3案例

需求: 创建存储函数,根据地址 ID 查询地址名称。 语句 : create function fn_getaddress(v_id number ) return varchar2 is v_name varchar2 ( 30 ); begin select name into v_name from t_address where id =v_id; return v_name; end ; 测试此函数: select fn_getaddress( 3 ) from dual 输出内容

需求:查询业主 ID ,业主名称,业主地址,业主地址使用刚才我们创建的函数 来实现。 select id 编号 , name 业主名称 ,fn_getaddress(addressid) 地址 from t_owners 查询结果如下:

三、存储过程 3.1什么是存储过程

存储函数类似于java中的方法,接收参数,返回结果。存储函数一般都用在查询中,存储函数一般返回一个值,存储过程可以返回多个值,它是通过传出参数返回的,存储过程本身是没有返回值的,没有return的写法,它可以通过传出参数像调用端传出多个参数。一般返回多个值的时候就用存储过程。有个局限性,存储函数可以直接在select语句中直接使用,存储过程通常是被应用程序(比如php,java,.net之类的)所调用。存储过程其实是对一段业务的封装,存储过程和我们三大框架是互斥的,他们是不能一起使用的,三大框架数据持久层hibernate已经帮我们解决了,我们根本不需要写存储过程。它的逻辑是要写到业务逻辑层中的,它是分层的。存储过程是吧逻辑写到数据库里,你的编程语言不写逻辑,直接去调用存储过程得到结果。

存储过程执行效率比三大框架的高,不需要打开链接,关闭链接啥的,当要求你的执行效率很高的时候,我们就用存储过程。

存储过程是被命名的 PL/SQL 块,存储于数据库中,是数据库对象的一种。 应用程序可以调用存储过程,执行相应的逻辑。 存储过程与存储函数都可以封装一定的业务逻辑并返回结果,存在区别如 下: 1 、存储函数中有返回值,且必须返回;而存储过程没有返回值,可以通过 传出参数返回多个值。 2 、存储函数可以在 select 语句 中直接使用,而存储过程不能。过程多数是 被应用程序所调用。 3 、存储函数一般都是封装一个查询结果,而存储过程一般都封装一段事务 代码。 3.2存储过程语法结构

存储过程语法结构和存储函数语法结构很相似,它里面没有return,声明部分is或者as,注意参数部分它可以传入和传出,传入参数的话,就在类型前面加上in,传出参数的话,就在类型前面加上out,加上in out的话就可以传入传出了。

创建或修改存储过程的语法如下 : CREATE [ OR REPLACE ] PROCEDURE 存储过程名称 (参数名 类型, 参数名 类型, 参数名 类型) IS|AS 变量声明部分; BEGIN 逻辑部分 [EXCEPTION 异常处理部分] END; 参数只指定类型,不指定长度 过程参数的三种模式: IN 传入参数(默认) OUT 传出参数 ,主要用于返回程序运行结果 IN OUT 传入传出参数 3.3案例

1. 创建不带传出参数的存储过程 :添加业主信息 -- 增加业主信息序列 create sequence seq_owners start with 11 ; -- 增加业主信息存储过程 create or replace procedure pro_owners_add ( v_name varchar2 , v_addressid number , v_housenumber varchar2 , v_watermeter varchar2 , v_type number ) is begin insert into T_OWNERS values ( seq_owners.nextval,v_name,v_addressid,v_housenumb er,v_watermeter, sysdate ,v_type ); commit ; end ; PL/SQL 中调用存储过程 call pro_owners_add( ' 赵伟 ' , 1 , '999-3' , '132-7' , 1 );

 

 

 

 

JDBC 调用存储过程 存储过程一般在程序中调用的较多。单独调用的比较少。 public static void add(Owners owners ){ java.sql.Connection conn = null ; java.sql.CallableStatement stmt = null ; 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()); stmt .execute(); } catch (SQLException e ) { e .printStackTrace(); } finally { BaseDao. closeAll ( null , stmt , conn ); } }

 

 

2 创建带传出参数的存储过程 带传出参数的存储过程调用的时候只能用begin ...end,不能用call。 需求:添加业主信息,传出参数为新增业主的 ID -- 增加业主信息存储过程 create or replace procedure pro_owners_add ( v_name varchar2 , v_addressid number , v_housenumber varchar2 , v_watermeter varchar2 , v_type number , v_id out number ) is begin select seq_owners.nextval into v_id from dual; insert into T_OWNERS values ( v_id,v_name,v_addressid,v_housenumber,v_watermete r, sysdate ,v_type ); commit ; end ; PL/SQL 调用该存储过程 declare v_id number ; -- 定义传出参数的变量 begin pro_owners_add( ' 王旺旺 ' , 1 , '922-3' , '133-7' , 1 ,v_id); DBMS_OUTPUT.put_line( ' 增加成功 ,ID:' ||v_id); end ; 执行成功后输出结果:

 

 

 

JDBC 调用存储过程 public static long add(Owners owners ){ long id =0; java.sql.Connection conn = null ; java.sql.CallableStatement stmt = null ; 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()); stmt .registerOutParameter(6, OracleTypes. NUMBER ); // 注 册传出参数类型 stmt .execute(); id = stmt .getLong(6); // 提取传出参数 } catch (SQLException e ) { e .printStackTrace(); } finally { BaseDao. closeAll ( null , stmt , conn ); } return id ; }

 

 

 

四、触发器 4.1什么是触发器

现实中的触发器,空调开关,遥控器,灯的开关,枪,特点就是触发某一操作,会被动触发另外操作。数据库的触发器是自动执行的存储过程,之前的存储过程需要通过应用程序去调用它。

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的 数据操作语句 (Insert,update,delete) 在指定的表上发出时, Oracle 自动地执行触发 器中定义的语句序列。 触发器可用于
  •  数据确认
  • 实施复杂的安全性检查
  • 做审计,跟踪表上所做的数据操作等
  • 数据的备份和同步
触发器分类
  •  前置触发器(BEFORE)
  •  后置触发器(AFTER)
前置触发器就是触发器执行在你语句执行之前执行的。触发器是基于表的增删改除的。前置触发器是可以修改值。 后置触发器则相反,就是你的语句执行完了它才能执行。 区别:前置触发器是可以修改值的,后置触发器则不能。 打拳击,别人打我,要快打到我的时候,我反勾拳先打了他。这就是前置触发器。就是他操作,我先触发。他倒地之后再去执行操作。 后置触发器就是他先到你,你在回击他。 4.2创建触发器的语法

语法: CREATE [ or REPLACE ] TRIGGER 触发器名 BEFORE | AFTER [ DELETE ][[or] INSERT ] [[or] UPDATE [ OF 列名 ]] ON 表名 [ FOR EACH ROW ][ WHEN ( 条件 ) ] declare …… begin PLSQL 块 End ; FOR EACH ROW 作用是标注此触发器是行级触发器 语句级触发器 注意:行级触发器就是加了这个 FOR EACH ROW这个关键字,语句及触发器就是没有加。 在触发器中触发语句与 伪记录变量 的值 伪记录变量主要有2种:old和:new,:old就是你修改记录之前的数据,它是代表一行数据,修改之前的数据可以通过:old.列名获取修改之前的数据,:new就是你修改之后的数据。

 

 

4.3案例 1. 前置触发器 需求:当用户输入本月累计表数后,自动计算出本月使用数 。 代码: create or replace trigger tri_account_updatenum1 before update of num1 on t_account for each row declare begin :new.usenum:=:new.num1-:new.num0; end ;

 

 

2. 后置触发器 后置触发器你可以读那个值,查询那个值,就是不能改那个值。

 

需求:当用户修改了业主信息表的数据时记录修改前与修改后的值,这就是触发器审计的功能。 -- 创建业主名称修改日志表 : 用于记录业主更改前后的名称,这个类似于物化视图日志的功能。 create table t_owners_log ( updatetime date , ownerid number , oldname varchar2 ( 30 ), newname varchar2 ( 30 ) ); -- 创建后置触发器,自动记录业主更改前后日志 create trigger tri_owners_log after update of name on t_owners for each row declare begin insert into t_owners_log values ( sysdate ,:old.id,:old.name,:new.name); end ; 测试: -- 更新数据 update t_owners set name = ' 杨小花 ' where id = 3 ; commit ; -- 查询日志表 select * from t_owners_log;

 

 

 

五、综合案例 5.1编写 PL/SQL ,用水吨数 12 吨,业主类型为 1,计算阶梯水费。 思路分析: 水费是实行阶梯计算的,我们查询价格表中业主类型为 1 的水费价格记录 minnum 为下限值 , maxnum 为上限值。上边的记录的含义是 5 吨以下的价格为 2.45 超过 5 吨不足 10 吨的价格为 3.45 超过 10 吨以上的价格为 4.45 如果吨数为 12 。计算如下: 考虑到阶梯的层次可能是不确定的,所以我们需要通过游标查询出阶梯价格记 录,然后计算每一阶梯的水费,然后相加。伪代码如下: 金额 =0 循环价格表 { if( 上限值为空 或者 总吨数 < 上限值 ) -- 最高阶梯 { // 此为最后阶梯 ,数量为超过上限值部分的吨数 金额 = 金额 + 价格 * (总吨数 - 上限值) 退出循环 } else { // 此为非最后阶梯 ,数量为区间内的吨数 金额 = 金额 + 价格 * (上限值 - 下限值) } } 语句: declare v_ownertypeid number ; -- 业主类型 ID v_usenum2 number ( 10 , 2 ); -- 总吨数 v_money number ( 10 , 2 ); -- 总金额 cursor cur_pricetable(v_type number ) is select * from t_pricetable where ownertypeid=v_type; -- 价格游标 v_pricetable t_pricetable% rowtype ; -- 每阶梯价格对象 begin v_ownertypeid:= 1 ; v_usenum2:= 12 ; v_money:= 0 ; for v_pricetable in cur_pricetable(v_ownertypeid) loop if v_pricetable.maxnum is null or v_usenum2<=v_pricetable.maxnum then -- 最后阶梯 (总吨数 - 下限值) * 价格 v_money:=v_money+ v_pricetable.price*(v_usenum2-v_pricetable.minnum); exit ; else -- 非最后阶梯 ( 上限值 - 下限值 )* 价格 v_money:=v_money+ v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minn um); end if ; end loop ; DBMS_OUTPUT.put_line( ' 阶梯水费金额: ' ||v_money); end ; 5.2存储函数综合案例:创建计算阶梯水费的函数,参数为业主类型、吨数。 create or replace function fn_calmoney(v_ownertypeid number ,v_usenum2 number ) return number is v_pricetable t_pricetable% rowtype ; -- 价格行对象 v_money number ( 10 , 2 ); -- 金额 cursor cur_pricetable(v_type number ) is select * from t_pricetable where ownertypeid=v_type order by minnum; -- 定 义游标 begin v_money:= 0 ; -- 金额 for v_pricetable in cur_pricetable(v_ownertypeid) loop -- 计算阶梯水费 -- 如果水费小于最大值,或最大值为 null 表示此阶梯为最后一个阶梯, -- 价格 * (总吨数 - 此阶梯下限值) if v_usenum2<= v_pricetable.maxnum or v_pricetable.maxnum is null then v_money:=v_money+ v_pricetable.price* ( v_usenum2 - v_pricetable.minnum); exit ; else -- 价格 * (此阶梯上限值 - 此阶梯下限值) v_money:=v_money+ v_pricetable.price* (v_pricetable.maxnum-v_pricetable.minnum ); end if ; end loop ; return v_money; end ; 测试此函数: select fn_calmoney( 1 , 12 ) from dual; 5.3触发器综合案例:当用户输入本月累计数后,自动计算阶梯水费。

create or replace trigger tri_account_updatenum1 before update of num1 on t_account for each row declare v_usenum2 number ( 10 , 2 ); -- 吨数 begin -- 使用数赋值 :new.usenum:=:new.num1-:new.num0; v_usenum2:= round ( :new.usenum/ 1000 , 3 ); -- 计算吨数 :new.money:=fn_calmoney(:new.ownertype,v_usenum2); -- 对金 额列赋值 end ; 修改某记录,观察结果。 5.4存储过程综合案例。

需求:增加业主信息时,同时在账务表(account)增加一条记录,年份与月份 为当前日期的年月,初始值(num0)为 0,其它字段信息(区域)与 t_owners 表一致 难点分析: 1. 如何取得年和月 用 to_char()函数 2. 如何取得区域 ID 参数中没有直接提供区域 ID,我们可以通过 addressid 到 address 表查询 创建存储过程语句: create or replace procedure pro_owners_add ( v_name varchar2 , v_addressid number , v_housenumber varchar2 , v_watermeter varchar2 , v_type number , v_ownersuuid out number ) is v_area number ; -- 区域编号 v_year char ( 4 ); -- 年份 v_month char ( 2 ); -- 月份 begin -- 提取序列值到变量 select seq_owners.nextval into v_ownersuuid from dual; -- 根据地址编号查询区域编号 select areaid into v_area from t_address where id=v_addressid; -- 年份 v_year:=to_char( sysdate , 'yyyy' ); -- 月份 v_month:=to_char( sysdate , 'mm' ); -- 增加业主信息 insert into t_owners values ( v_ownersuuid,v_name,v_addressid,v_housenumber,v_w atermeter, sysdate ,v_type ); -- 增加账务表信息 insert into t_account (id,owneruuid,ownertype,areaid, year , month ,num0) values (seq_account.nextval,v_ownersuuid,v_type,v_area,v_year, v_month, 0 ); commit ; exception when NO_DATA_FOUND then v_ownersuuid:=- 1 ; rollback ; end ;

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/936758.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号