总结一下这两天存储过程踩的坑 参考https://blog.csdn.net/u013057786/article/details/17165623
一、 存储过程
1、定义
所谓存储过程(Stored Procedure),就是一组用于完成特定数据库功能的SQL语句集,该SQL语句集经过
编译后存储在数据库系统中。在使用时候,用户通过指定已经定义的存储过程名字并给出相应的存储过程参数
来调用并执行它,从而完成一个或一系列的数据库操作。
2、存储过程的创建
Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常。
(1)无参存储过程语法
create or replace procedure NoParPro
as //声明
;
begin // 执行
;
exception//存储过程异常
;
end;
(2)带参存储过程实例
create or replace procedure queryempname(sfindno emp.empno%type)
as
sName emp.ename%type;
sjob emp.job%type;
begin
....
exception
....
end;
create or replace procedure queryempname(param1 in varchar2,param2 in number)
as
v_price number(12, 4);
v_advicep_id varchar2(20);
begin
....
exception
....
end;
参数定义的几种方式:
- 声明并使用变量
变量声明基本规则: PL/SQL是强类型语言,变量都必须在它声明之后才可以使用; 变量不区分大小写; 变量都要在DECLARE部分声明; 变量名称可以由字母,数字,_(下划线),$,#组成; 所有的变量名称要求以字母开头,不能是Oracle保留字(关键字); 可以通过sys登录,使用"v$reserved_words"数据字典查看全部关键字 变量的长度最多30个字符; 所有的变量均为局部作用域,它们只在给定的PL/SQL块中有效; 变量的默认值为NULL; 变量名称 [constant] 类型 [not null] [:=value]; 语句解析: constant:定义常量,必须在声明时为其赋予默认值; not null:表示此变量不允许为null; :=value:在变量声明示,设置初始数据; 示例:定义变量 DECLARE v_resultA NUMBER := 100 ; -- 定义一个变量同时赋值 v_resultB NUMBER ; -- 定义一个变量没有设置内容 BEGIN v_resultb := 30 ; -- 没有区分大小写 DBMS_OUTPUT.put_line('计算的结果是:' || (v_resultA + v_resultB) ) ; END ;
- 使用%TYPE声明变量类型;
该声明方式,可以使某一个变量与指定数据表中某一列的类型一样: 语法:变量定义表名称.字段名称%TYPE 示例: DECLARE v_eno emp.empno%TYPE ; -- 与empno类型相同 v_ename emp.ename%TYPE ; -- 与ename类型相同 BEGIN DBMS_OUTPUT.put_line('请输入雇员编号:') ; v_eno := &empno ; -- 由键盘输入雇员编号 SELECT ename INTO v_ename FROM emp WHERE empno= v_eno ; DBMS_OUTPUT.put_line('编号为:' || v_eno || '雇员的名字为:'|| v_ename) ; END ;
使用%ROWTYPE声明变量类型
此标记可以定义表中一行记录的类型: 使用"select…..into…."将表中的一行记录设置到了ROWTYPE类型的变量中时,可以利用"rowtype变量.表字段"的方式取得表中每行的对应列数据 示例:使用ROWTYPE装载一行记录 DECLARE v_deptRow dept%ROWTYPE ; -- 装载一行dept记录 BEGIN SELECT * INTO v_deptRow FROM dept WHERE deptno=10 ; DBMS_OUTPUT.put_line('部门编号:'|| v_deptRow.deptno || ',名称:' || v_deptRow.dname || ',位置:' || v_deptRow.loc) ; END ; 补充:通过定义专门的集合类型也可以完成与ROWTYPE类型的功能 通过自定义类型接收一行记录: DECLARE TYPE dept_type IS RECORD ( dno dept.deptno%TYPE , dna dept.dname%TYPE , dlo dept.loc%TYPE) ; -- 定义一个新的类型 v_deptRow dept_type ; -- 装载一行dept记录 BEGIN SELECT * INTO v_deptRow FROM dept WHERE deptno=10 ; DBMS_OUTPUT.put_line('部门编号:'|| v_deptRow.dno || ',名称:' || v_deptRow.dna || ',位置:' || v_deptRow.dlo) ; END ;
其中参数IN表示输入参数,是参数的默认模式。
OUT表示返回值参数,类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值,表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值,也可以将某个值传出去。
(3)游标的定义
--显示cursor的处理
declare
---声明cursor,创建和命名一个sql工作区
cursor cursor_name is
select real_name from account_hcz;
v_realname varchar2(20);
begin
open cursor_name;---打开cursor,执行sql语句产生的结果集
fetch cursor_name into v_realname;--提取cursor,提取结果集中的记录
dbms_output.put_line(v_realname);
close cursor_name;--关闭cursor
end;
二、 项目实战
使用存储过程实现优惠金额更新的小算法
create or replace procedure p_inhos_update_actual_price(hos_patient_id in varchar2,
rest_price in number) as
v_discount_amount number(12, 4);
v_advicep_id varchar2(20);
v_tmp number(12, 4) := rest_price;
v_sql varchar2(2000);
cursor hos_doctor_advicep(v_hos_patient_id varchar2) is
select advicep_id, hop.preferential_actual_price
from hos_doctor_advicep hop
where hop.hos_patient_id = v_hos_patient_id
order by to_number(hop.advicep_id) asc;
begin
---init preferential_actual_price
v_sql := 'update hos_doctor_advicep hop set hop.preferential_actual_price =
hop.quantity * hop.discount_amount where hop.hos_patient_id = :1';
--dbms_output.put_line(v_sql);
execute immediate v_sql using hos_patient_id;
--update hos_doctor_advicep hop set hop.preferential_actual_price = ( select to_char(hop.quantity * hop.discount_amount,'fm9999990.0099') from dual) where hop.hos_patient_id=hos_patient_id;
commit;
--执行语句
open hos_doctor_advicep(hos_patient_id);
if hos_doctor_advicep%isopen then
--取出游标指向的每行数据,用循环语句
loop
fetch hos_doctor_advicep
into v_advicep_id, v_discount_amount;
--判断当前游标是否到达最后
exit when hos_doctor_advicep%notfound;
if v_tmp != 0 and (to_number(v_tmp) - v_discount_amount) < 0 then
--最后一笔费用
v_sql := 'update hos_doctor_advicep hop set hop.preferential_actual_price = ( select to_char(:1,''fm9999990.0099'') from dual) where hop.advicep_id =:2';
--dbms_output.put_line(v_sql);
execute immediate v_sql
using v_tmp, v_advicep_id;
commit;
v_tmp := 0;
elsif v_tmp = 0 then
v_sql := 'update hos_doctor_advicep hop set hop.preferential_actual_price = ''0'' where hop.advicep_id = :1';
--dbms_output.put_line(v_sql);
execute immediate v_sql
using v_advicep_id;
commit;
else
v_tmp := to_number(v_tmp - v_discount_amount);
end if;
end loop;
else
dbms_output.put_line('用户信息:游标没有打开!');
end if;
--关闭游标
close hos_doctor_advicep;
-- return to_number(v_tmp);
end;
遇到的问题:
1.Oracle 字段名与变量同名赋值问题,会被解析为1=1
update hos_doctor_advicep hop set hop.preferential_actual_price = ( select to_char(hop.quantity * hop.discount_amount,'fm9999990.0099') from dual) where hop.hos_patient_id=hos_patient_id
where hop.hos_patient_id=hos_patient_id; 会被解析为 where 1=1 导致更新全表
解决办法:
1.修改变量名称,与字段名不一致where hop.hos_patient_id=v_hos_patient_id;
2.换以下赋值的方式 execute immediate的简单用法
create or replace procedure proc_test( --参数区域 ) is --变量区域 --sql脚本 v_sql varchar2(2000) :=''; --记录学生数量 v_num number; begin --执行区域 -- execute immediate用法1:立刻执行sql语句 v_sql := 'create or replace view myview as select id,name from student'; execute immediate v_sql; --- execute immediate用法2:立刻执行sql语句,并赋值给某个变量 v_sql := 'select count(1) from student'; execute immediate v_sql into v_num; -- execute immediate用法3:带参数的sql v_sql:='select * from student t where t.name=:1 and t.age=:2'; execute immediate v_sql using 'ZhangSan',23; end proc_test;
2.Oracle中会将小数点前的0隐去也会将小数点后的0隐去(例如:0.10会变为.1)
to_char(0.10,'fm99990.0099')
0的位置,如果有数字就显示数字,没数字就用0显示
9的位置,有值就显示,没有就不显示(0也显示)
FM :除空格 9999999.0099:允许小数点左边最大正数为7位,小数点右边最少2位,最多4位,且在第5位进行四舍五入