Oracle学习笔记（二）

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

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;

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;

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;

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;  

　　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;

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;

    <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>