pl/sql

555555555

评论

5 楼 luogen33 2012-05-02  

create function monthBetween("DAY1" varchar(10),"DAY2" varchar(10))  
            Returns INTEGER  
            LANGUAGE SQL  
  begin ATOMIC Declare dMonth INTEGER;  
            Declare date1 Date;  
            Declare date2 Date;  
                Declare a INTEGER;  
                --我们这里将传入的day1，day2进行比较，将日期大的赋值给date1，反之赋值给date2  
                if day1>day2 then   
                    set date1=Date(day1);  
                    set date2=Date(day2);  
                else   
                    set date1=Date(day2);  
                    set date2=Date(day1);  
                end if;  
                set a = (date1-date2);  
                if  mod(a,100)>0 then --如果有零星天，即天数大于零则月数加上1 
                    set dMonth=(date1-date2)/10000*12+ mod((a/100),100)+1;  
                else 
                    set dMonth=(date1-date2)/10000*12+mod((a/100),100);  
                end if;  
                return dMonth;  
  end    

4 楼 luogen33 2012-05-02  

create or replace function split
(
   p_list varchar2,

   p_sep varchar2 := ','

)return type_split pipelined

is

   l_idx  pls_integer;

   v_list  varchar2(4000) := p_list;

begin

   loop

      l_idx := instr(v_list,p_sep);

      if l_idx > 0 then

          pipe row(substr(v_list,1,l_idx-1));

          v_list := substr(v_list,l_idx+length(p_sep));

      else

          pipe row(v_list);

          exit;

      end if;

   end loop;

   return;

end split;

3 楼 luogen33 2012-05-02  

    set v_str = 'insert into mmscsvclog_tmp select * from '||v_tname||' where logtype > 1 and messageid = '||''''||v_mid||'''';
       --dbms_output.put_line(v_str);
       set  vvv_tmp03=v_tname;
       EXECUTE IMMEDIATE v_str;
       commit;
       end while;
       close v_tables;
       set v_i = v_i + 1;
  set vvv_tmp02= v_mid;
  fetch v_mids into v_mid;
  end while;
  close v_mids;
  --dbms_output.put_line('end...  '||to_char(SYSDATE , 'yyyy-MM-dd HH24:mi:ss')); 
  --返回结果集
  open pCur_Res;
END

2 楼 luogen33 2012-05-02  

CREATE PROCEDURE "CAOJJ"."PRC_PORTAL_DETAIL_LOGQUERY"
(
  in  p_mids   varchar(21),
  in  p_times varchar(24)
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
SPECIFIC SQL120502151408200
BEGIN
  declare v_mid  varchar(21);
  declare v_time varchar(24);
  declare vvv_tmp01 varchar(24) default '0';
  declare vvv_tmp02 varchar(24) default '0';
  declare vvv_tmp03 varchar(24) default '0';
  --type v_array is table of varchar(24) index by binary_integer;
  --TODO
  --declare CREATE TYPE v_array as  ARRAY;      
  --declare v_arrayTime v_array;
  --v_mids sys_refcursor;
  --v_times sys_refcursor;
  --v_tables sys_refcursor;
  declare v_startday varchar(8);
  declare v_endday varchar(8);
  declare v_clean_sql varchar(29) default 'truncate table mmscsvclog_tmp';
  declare v_i integer default 0; 
  declare v_str varchar(200);
  declare  v_prefix varchar(10) default 'MMSCSVCLOG';
  declare v_tname varchar(22); 
  declare sqlcode integer default 0 ; --SQL返回的结果编码 
    -- 放入数组
     --open v_times for select column_value from table(split(p_times,','));
      declare  v_times cursor for select column_value from column_value_tmp_table;
      --open v_mids for select column_value from table (split(p_mids, ','));
      declare  v_mids  cursor for select column_value from column_value_tmp_table;
   -- open v_tables for
     declare  v_tables  cursor for
     select   tabname   from   syscat.tables where SUBSTR(tabname,1,18) >= v_prefix||v_startday
            and SUBSTR(tabname,1,18) <= v_prefix||v_endday and tabname <> 'MMSCSVCLOG_TMP';
    --定义返回结果集
  --open rs_cursor for
  declare pCur_Res cursor with return to client for
       select TIMESTAMP,LOGTYPE,AFFAIRORIGIN,SENDER,RECEIVER,FWDADDR,SEQUENCENUM,SESSIONID,MESSAGEID,TRANSACTIONID,EXMESSAGEID,MESSAGETYPE,AFFAIRATTRIBUTE,AFFAIRSTATUS,FLOWCLASS,ADDONSINFO
              from mmscsvclog_tmp; 
 
  -- 为空直接返回
  if(p_mids is null)
  then
     --dbms_output.put_line('p_mids is null!');
     return;
  end if;
  if(p_times is null)
  then
     --dbms_output.put_line('p_times is null!');
     return;
  else
  --遍历v_times
  open v_times;
  fetch v_times into v_time;
  while ((sqlcode=0) and (v_time!=vvv_tmp01))
  do
  --TODO
  --set v_arrayTime(v_i) = v_time;
  --set v_i = v_i + 1;
  set vvv_tmp01= v_time;
  fetch v_times into v_time;
  end while;
  close v_times;
  end if; 
  set v_i = 0;
  --dbms_output.put_line('begin...  '||to_char(SYSDATE , 'yyyy-MM-dd HH24:mi:ss'));
  -- 释放水位线
  EXECUTE IMMEDIATE v_clean_sql;
  --dbms_output.put_line('clean ...  '||to_char(SYSDATE , 'yyyy-MM-dd HH24:mi:ss'));
  --打开游标
  open v_mids;
  fetch v_mids into v_mid;
  while ((sqlcode=0) and (v_mid != vvv_tmp02))
  do
   -- 判断一下mid是否符合规则  
  --TODO
   --set v_startday = replace(substr(v_arrayTime(v_i),1,10),'-','');  
  -- set v_endday  = to_char((to_date(substr(v_arrayTime(v_i),1,19),'yyyy-MM-dd HH24:mi:ss')+3),'yyyymmdd'); 
   --dbms_output.put_line(v_startday||'--'||v_endday||'--');
       fetch v_tables into v_tname;
       while  ((sqlcode=0) and (v_tname != vvv_tmp03))
       do
  

1 楼 luogen33 2012-04-23  

1. BASE64编码 ?

2.如何获得数据库字符集编码 （ORACLE /DB2）？

3.server端系统参数，如何，实时生效？

4.增加支持JDK1.4的RSA解密第三方jar包bcprov-jdk14-138.jar?