oracle sql脚本

set define off;

declare

   function detect_config(str_in_configname varchar2) return integer is
    i_l_number integer;
   begin
     select count(*)
       into i_l_number
       from t_config
      where configname = str_in_configname;
  
     return i_l_number;
   end;
   
    --detect whether the table exist
    function detect_table(str_in_tablename varchar2) return integer is
        i_l_number integer;
    begin
        select count(*)
          into i_l_number
          from user_tables
         where table_name = upper(str_in_tablename);

        return i_l_number;
    end;


begin

    -----------------------------------------------------------
    --------------- version : ---------------
    -----------------------------------------------------------
if(detect_table('t_ussd') = 0) then
            execute immediate 'create table t_ussd
                              (  
                                   streamID         NUMBER(10,0) not null,
                                   transUniqueID    VARCHAR2(100) not null,
                                   phoneNumber      VARCHAR2(128) not null,
                                   sendNum          NUMBER(2,0) default 0 not null,
                                   createTime       TIMESTAMP(6) not null,
                                   updateTime       TIMESTAMP(6) not null,
                                   sessionID        VARCHAR2(100) not null,
                                   fee              NUMBER,
                              )
                              tablespace ptl';
            execute immediate 'alter table t_ussd add constraint PK_PO_USSD primary key(streamID) using index tablespace ptlindx';
            execute immediate 'create index IDX_USSD_SENDNUM on t_ussd (sendNum) tablespace ptlindx';
            execute immediate 'create index IDX_USSD_UPDATETIME on t_ussd (updateTime) tablespace ptlindx';
    end if;
    if (detect_config('supportsms_or_not') = 0) then
        insert into t_config (configid, configname, configmodule, configvalue, configrule, defaultvalue, description,) 
        values (s_po_configid.nextval, 'supportsms_or_not', 'SYS', '1', '0|1', '1', 'Whether support sms or not');
    end if;

    commit;
end;
/
create or replace function f_recordua
(    
     str_userAgent    varchar2, 
     str_statValue    number
) 
return integer 
is
    i_exists      integer;
    i_userAgent   varchar2(2000);
    i_statmonth        date;
begin
    i_userAgent := substr(str_userAgent, 1, 1024);
    i_statmonth := trunc(sysdate, 'month');
    
    select count(*) into i_exists from t_useragent a
    where a.useragent = i_userAgent
      and a.statmonth = i_statmonth;
      
    if (i_exists = 0) then
       insert into t_useragent (userAgent, statValue, statMonth) values (i_userAgent, str_statValue, i_statmonth);
    else
       update t_useragent set statValue = statValue + str_statValue where useragent = i_userAgent
       and statmonth = i_statmonth;
    end if;
     
    commit;
    
    return 1;
end f_recordua;
/

-----compile
begin
    dbms_utility.compile_schema(user, false);
end;
/