データベースsql関数

1 sql関数構文
1.1条件文if else

--if-then
if condition then
  sequece_of statements
end if;

--if-then-else
if condition then
  sequece_of statements1
else
  sequece_of statements1
end if;

--if-then-elseif-else
if condition1 then
  sequece_of statements1
elseif condition1 then
  sequece_of statements2
else
  sequece_of statements3
end if;
--      
create or replace function testifelse(idd in varchar) return number is
  Result number;
  v_para1 number;
  v_para2 number;
  message varchar2(200);
begin
  select count(1) into v_para1 from dual;
  if v_para1 <> 0 then v_para1 := '111';
  end if;
  if v_para2 is null then message := 'number      null,  0';
  end if;
  dbms_output.put_line('v_para1='||v_para1||'---'||'v_para2='||message);
  return(Result);
end testifelse;

1.2 case文

case selector
 when expression1 then sequence_of_statemts1;
end case;
--  1：
select case 
         when 1 = 1 and 2 = 2 then
          '  '
       end case
  from dual
--  2   
create or replace function testCase(condition in varchar2) return varchar2 is
  Result varchar2(20);
  v_param dual.dummy%type;
begin
  case condition
    when ''  then Result:='    ';
    when '1' then Result:='   1';
    when '2' then Result:='   2';
    dbms_output.put_line(v_param);
    end case;
  return(Result);
end testCase;
--  2     
create or replace procedure testcase(para_in  in varchar2,
                                     para_out out varchar2) is
  v_param varchar2(20);
begin
  dbms_output.put_line('    ');
  case para_in
    when '1' then
      para_out := para_in || 'out  ';
    when '2' then
      para_out := para_in || 'out  2';
  end case;
end testcase;

1.3 for loop配列および配列タイプ

declare
  i number;
  type list_type is table of binary_integer index by binary_integer; --     
  list list_type;
begin
  for i in reverse 1 .. 5 loop
    list(i) := i * 1;
    dbms_output.put_line(list(i));
  end loop;
  dbms_output.put_line(list.first());
end;

Declare
  type my_text_table_type is table of varchar2(200) index by binary_integer;
  l_text_table my_text_table_type;
  l_index      number;
begin
  for l_index in 1 .. 5 loop
    l_text_table(l_index) := l_index * l_index;
  end loop;
  l_index := l_text_table.first; --  first  
  dbms_output.put_line('000'||l_text_table.first);
  loop
    exit when l_index is null;
    dbms_output.put_line(l_index || ':' || l_text_table(l_index));
    l_index := l_text_table.next(l_index);
  end loop;
end;

次の表にoracleのコレクションを示す方法は、COUNTがコレクション内の要素に戻る個数を制限するDELETEを使用して、コレクション内のすべての要素DELETE()削除要素の下にxと表示されている要素を削除し、xがnullの場合、コレクションはVARAY不正DELETE()削除要素の下にXからYと表示されている要素をそのまま保持します.X>Yコレクションが一定のままVARAYに対して不正なEXIST()コレクション要素xが初期化されている場合はTRUEに戻り、そうでない場合はFALSEEXTENDに戻りコレクションの最後にIndex_by不正EXTEND()コレクションの最後にx個の要素対Index_を追加by不正EXTEND(,)集合の末尾に要素nのx個のコピー対Index_を追加by不正FIRSTは、集合の最初の要素の下の番号を返し、VARAY集合については常に1を返します.LASTは、集合の最後の要素の下の符号を返し、VARAYに対する戻り値は常にCOUNTに等しい.LIMITは、ネストされたテーブルとネストされたテーブルとIndex_についてVARRYセットの最大要素数を返します.byはnull Index_by集合無用NEXT()は、エレメントxの直後にあるエレメントの値を返し、エレメントが最後のエレメントであるnullを返す.PRIOR()は、集合内の要素xの直前の要素の値を返し、その要素が最初の要素である場合nullを返します.TRI Mはindex_に対して集合の末端から要素を削除します.by不正TRIM()集合末端からx個の要素対index_を削除するby非合法
 
1.4実戦例

create or replace function getArrearage2(yuangongID in varchar2)
  return number is
  Result number;
  money1 number;
  money2 number;
  money3 number;
begin
  select count(1)
    into money1
    from (SELECT b.id_worker, SUM(B.ALLOW_MONEY) as totalMoney
            FROM T_MONEY1 B
           WHERE B.STATUS = '1'
             AND B.FLAG = '1'
             AND B.BILL_STAUTUS = '4'
             AND B.ID_WORKER = yuangongID
           group by b.id_worker);
  if (money1 0) then
    select totalMoney
      into money1
      from (SELECT b.id_worker, SUM(B.ALLOW_MONEY) as totalMoney
              FROM T_MONEY1 B
             WHERE B.STATUS = '1'
               AND B.FLAG = '1'
               AND B.BILL_STAUTUS = '4'
               AND B.ID_WORKER = yuangongID
             group by b.id_worker);
  end if;
  select count(1)
    into money2
    from (SELECT b.id_worker, sum(b.back_money) as back_money
            FROM T_MONEY2 B
           WHERE B.STATUS = '1'
             AND B.FLAG = '1'
             AND B.ID_WORKER = yuangongID
           group by b.id_worker) tt;
  if (money2 0) then
    select tt.back_money
      into money2
      from (SELECT b.id_worker, sum(b.back_money) as back_money
              FROM T_MONEY2 B
             WHERE B.STATUS = '1'
               AND B.FLAG = '1'
               AND B.ID_WORKER = yuangongID
             group by b.id_worker) tt;
  end if;
  select count(1)
    into money3
    from (SELECT p.id_worker, sum(c.money) as money
            FROM T_MONEY3 C
            LEFT JOIN MB_PAY_ASK P
              ON P.ID = C.ID_PAYBILL
           WHERE C.MONEY IS NOT NULL
             AND C.MONEY '0'
             AND C.STATUS = '1'
             AND C.FLAG = '1'
             AND P.STATUS = '1'
             AND P.FLAG = '1'
             AND P.ID_WORKER = yuangongID
             AND P.PROPOSAL_STATUS = '4'
           group by p.id_worker) xx;
  if (money3 0) then
    select xx.money
      into money3
      from (SELECT p.id_worker, sum(c.money) as money
              FROM T_MONEY3 P
             WHERE C.MONEY IS NOT NULL
               AND C.MONEY '0'
               AND C.STATUS = '1'
               AND C.FLAG = '1'
             group by p.id_worker) xx;
  end if;
  Result := money1 - money2 - money3;
  return(Result);
end getArrearage2;