Oracle学习笔记（二）-阿里云开发者社区

Oracle学习笔记（二）

2022-12-10 169

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： Oracle学习笔记（二）

while循环

案例：

declare
age int := 1;
begin
while age<=10 then
          dbms_output.put_line('age is'||age);
          age := age+1;
 end loop;
 end;

for循环

案例：

DECLARE
   x NUMBER := 100;
BEGIN
   FOR i IN 1..10 LOOP  --noted here 
      IF MOD(i,2) = 0 THEN     -- i is even
          dbms_output.put_line( 'i: '||i||' is even ' );
      ELSE
          dbms_output.put_line('i: '|| i||' is odd' );
      END IF;
      x := x + 100;
      dbms_output.put_line('x value: '|| x);
   END LOOP;
   COMMIT;
END;

loop循环

declare
    i number :=0;
    total number :=0;
begin
    loop
        i := i+1;
        total := total + i;
        if i=100 then
            exit;
        end if;
    end loop;
    dbms_output.put_line('总和'||total);
end;

触发器

触发器语法

CREATE [OR REPLACE] TIGGER 触发器名 触发时间 触发事件
ON 表名
[FOR EACH ROW]
BEGIN
PL/SQL 语句;
END;

案例

该触发器是在插入之前，如果INDEXS_ACCOUNT_TABLE表中的id为null就生成一个新的id

CREATE OR REPLACE TRIGGER tri_INDEXS_ACCOUNT_TABLE
before insert on INDEXS_ACCOUNT_TABLE
for each row
declare
begin
if :new.id is null then
    :new.id:=lower(sys_guid());
  end if;
end tri_INDEXS_ACCOUNT_TABLE;

for each row 的意思是：这个触发器是行触发器。

函数

参数说明

其中参数IN表示输入参数，是参数的默认模式。

OUT表示返回值参数，也就是输出参数。类型可以使用任意Oracle中的合法类型。

OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程

IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去

create or replace function func  (  
        -- 入参、出参列表， 逗号分隔。  
        uid in varchar2,                          -- 不能带长度信息  
        startDate in date,                        -- 第二个输入参数  
        defaultVar in varchar2 default "",        -- 默认参数，如果不传，要注意参数的顺序  
        isok out number,                          -- 输出参数  
        result out varchar2                       -- 第二个输出参数  
)  
return number      -- 定义返回类型  
as  
-- 变量声明，每个声明用分号结束。可以在声明的同时初始化  
var1 varchar2(11);  
var2 number(2) := 123;  
begin  
        -- 字符串拼接用 ||  
        dbms_output.put_line('isok:' || 'abc');  
        return ret_val;  
end;

存储过程

优点

存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而
一般SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速
度。
当对数据库进行复杂操作时(如对多个表进行
Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来
与数据库提供的事务处理结合一起使用。
存储过程可以重复使用,可减少数据库开发人员的工作量
安全性高,可设定只有某此用户才具有对指定存储过程的使用权

缺点

可移植性差
出bug不好查找

基本语法

　　CREATE OR REPLACE PROCEDURE 存储过程名(param1 in type,param2 out type)
　　IS
　　变量1 类型(值范围);
　　变量2 类型(值范围);　　
　　BEGIN
　　　　select count(*) into 变量1 from 表名 where 列名=param1;
　　　　if (判断条件) then
　　　　　　select 列名 into 变量2 from 表名 where 列名=param1;
　　　　　　DBMS_OUTPUT.put_line('打印信息');
　　　　Elsif (判断条件) then
　　　　　　dbms_output.put_line('打印信息');
　　　　Else
　　　　　　Raise 异常名 (NO_DATA_FOUND);
　　　　End if;
　　Exception
　　　　　　When others then
　　　　　　　　Rollback; 　　
　　END;

案例

create or replace procedure Test(x in out number)
is
begin
     if x<0 then
         begin
             x:= 0 - x;
        end;
     elsif x > 0 then     --noted here elsif 
         begin
             x:= x ;
        end;
     else
        x:=    0;
     end if;
end Test;

plsql中调用

一个比较复杂的案例

create or replace procedure update_times_sort(var_index      in varchar2,
                                              var_reportTime in varchar2) as
  x number;
  y number;
  f number;
  z varchar2(100);
begin
 select nvl(max(a.times_sort), 0) into x
  from user_index_data_table a
 where a.index_id in
       (select b.mreform_id
          from medical_reform_index_table b
         where b.indexs_id =
               (select c.indexs_id
                  from medical_reform_index_table c
                 where c.mreform_id = var_index))
   and a.report_time = var_reportTime
   and a.times_sort is not null;
  y := 1;
  loop
   select times_uuid into z
      from (select times_uuid, rownum rn
              from (select DISTINCT a.times_uuid, min(a.create_time) tim
                      from user_index_data_table a
                     where a.index_id in
                           (select b.mreform_id
                              from medical_reform_index_table b
                             where b.indexs_id =
                                   (select c.indexs_id
                                      from medical_reform_index_table c
                                     where c.mreform_id = var_index))
                       and a.report_time = var_reportTime
                       and a.times_sort is null
                     group by a.times_uuid
                     order by tim asc))
     where rn =1;
    update user_index_data_table a
       set a.times_sort = x + y
     where a.times_uuid = z;
    update factor_data_table a
       set a.times_sort = x + y
     where a.times_uuid = z;
    commit;
    y := y + 1;
     select count(times_uuid)
      into f
      from (select times_uuid, rownum rn
              from (select DISTINCT a.times_uuid, min(a.create_time) tim
                      from user_index_data_table a
                     where a.index_id in
                           (select b.mreform_id
                              from medical_reform_index_table b
                             where b.indexs_id =
                                   (select c.indexs_id
                                      from medical_reform_index_table c
                                     where c.mreform_id = var_index))
                       and a.report_time = var_reportTime
                       and a.times_sort is null
                     group by a.times_uuid
                     order by tim asc));
    exit when f=0;
  end loop;
end;

mybatis中调用存储过程

dss为数据库的服务名称

    <update id="setTimesSortForUserIndexAndFactor" parameterType="Map">
          declare
            p2 varchar(50);
            p3 varchar(50);
          begin
            p2:= #{indexId,jdbcType=VARCHAR};
            p3:= #{reportTime,jdbcType=VARCHAR};
            dss.update_times_sort(p2, p3);
          end;
    </update>

存储过程与函数异同

1、两者定义类似，都可以带输入输出参数。

2、函数有返回值，存储过程没有。

3、函数的调用要在select语句里；而存储过程不用，可以独立调用。