Oracle 存储过程

总结一下这两天存储过程踩的坑 参考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位进行四舍五入