oracle and mysql procedure

//递归父子信息sql
select c.catalogid, c.lastmodifytime ,c.parentcatalogid
        from T_E_CATALOG c
      CONNECT BY PRIOR c.CATALOGID = c.PARENTCATALOGID
       START WITH C.PARENTCATALOGID = :CATALOGID


//递归父子信息sql
SELECT t.catalogid,t.parentcatalogId,t.type
  FROM T_E_CATALOG t
 --where t.type = '0' 
CONNECT BY prior t.parentcatalogId = t.catalogId
 START WITH t.CATALOGID = 100006






//merge operation
MERGE INTO t_us_rank_result a
      USING (select count(1) co
               from t_us_rank_result
              where contentId = :contentId) b
      ON (b.co <> 0)
      WHEN MATCHED THEN
        UPDATE
           SET a.rank           = :avgRank,
               a.count          = :count,
               a.lastModifyTime = sysdate
         where a.contentId = :contentId
      WHEN NOT MATCHED THEN
        INSERT
          (contentId, rank, count, lastModifyTime)
        VALUES
          (:contentId, :avgRank, :count, sysdate)





//添加字段
alter table egy_us_failbill add(SENDTIME TIMESTAMP(6) WITH TIME ZONE,PRODUCTINFO VARCHAR2(100));
//删除字段
alter table egy_us_failbill drop (sendtime1,productinfo2);
//改变字段
alter table egy_us_failbill rename column sendtime_temp to sendtime;


//diff between %type and %rowtype in oracle


-- %TYPE is used to declare a field with the same type as 
-- that of a specified table's column: 
 
DECLARE
   v_EmpName  emp.ename%TYPE;
BEGIN
   SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
   DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;

 
 
 
-- %ROWTYPE is used to declare a record with the same types as 
-- found in the specified database table, view or cursor: 
 
DECLARE
  v_emp emp%ROWTYPE;
BEGIN
  v_emp.empno := 10;
  v_emp.ename := 'XXXXXXX';

END;

//procedure

create or replace procedure P_RETURN_COMMON_CATALOGID(in_catalogId     in varchar2,
                                                      out_comCatalogId out varchar2) is


  v_type VARCHAR2(1);


  cursor v_cursor(i_catalogId varchar2) is
    select t.type from t_e_catalog t where t.catalogId = i_catalogId;
  --exception when no_data_found then return;


begin
  out_comCatalogId := null;
  open v_cursor(in_catalogId);
  loop
    fetch v_cursor
      into v_type;
  
    if v_cursor%found then
    
      if v_type = '3' then
      
        select t.catalogId
          into out_comCatalogId
          from t_e_catalog t
         where t.type = '0'
           and rownum = 1
        connect by prior t.parentCatalogId = catalogId
         start with t.catalogId = in_catalogId;
      
      else
        select t.catalogId
          into out_comCatalogId
          from t_e_catalog t
         where t.type in ('0', '1', '2', '4')
           and rownum = 1
        connect by prior t.parentCatalogId = catalogId
         start with t.catalogId = in_catalogId;
      end if;
    else
      exit;
    end if;
  
  end loop;


  close v_cursor;


end P_RETURN_COMMON_CATALOGID;

//trigger

CREATE OR REPLACE TRIGGER DUPLICATE_EMP
  AFTER UPDATE OR INSERT OR DELETE ON egy_us_balance
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO egy_us_balance_bak(ACCOUNT,BALANCE,LASTMODIFIED)
    VALUES
      (:new.account, :new.balance, sysdate);
  ELSIF DELETING THEN
    DELETE FROM egy_us_balance_bak t WHERE t.account = :old.account;
  ELSE
    UPDATE egy_us_balance_bak t
       SET t.account = :new.account, t.balance = :new.balance
    
     WHERE t.account = :old.account;
  END IF;
END;

//procedure

create or replace procedure P_XL_RANK_PURCHASEDCOMMEND as
  cursor curCatalog is(
  -- 查询专区ID不为9999，并且内容正常的专区、内容、产品
  
    select catalogId
      from t_e_catalog
     where catalogId != 9999
       and catalogId != -1
       and catalogId != 0);
begin
  -- 首先清空xl_rank_purchasedcommend


  EXECUTE IMMEDIATE 'truncate table xl_rank_purchasedcommend';


  for curCatalogTmp in curCatalog loop
  
    dbms_output.put_line(curCatalogTmp.catalogId);
  
    -- 单个专区 top10内容          
    insert into xl_rank_purchasedcommend
      (catalogid, contentid, count, offsetcount, rankdate)
      select curCatalogTmp.catalogId, e.*, '0', sysdate
        from (select *
                from (select c.contentId, count(pc.productId) count
                        from t_e_catalog2content cc,
                             t_e_content         c,
                             t_e_product2content pc,
                             t_e_productinfo     p,
                             t_us_subscribeinfo  s
                       where 1 = 1
                         and cc.contentid = c.contentid
                         and c.contentid = pc.contentid
                         and pc.productid = s.productid
                         and pc.productid = p.productid
                         and cc.catalogid = curCatalogTmp.catalogId
                         and cc.status = '0' -- 专区和内容的关系正常
                         and c.status = '1' -- 内容正常
                         and pc.status = 0 --内容和产品的关系正常
                         and p.status = 1 --对应的产品当前有效                            
                         and p.validfrom <= sysdate
                         and p.validto >= sysdate
                       group by c.contentId) d
               order by d.count desc) e
       where rownum <= 10;
  
    commit;
  end loop;


exception
  when others then
    dbms_output.put_line(Sqlerrm);
    ROLLBACK;
end P_XL_RANK_PURCHASEDCOMMEND;

//mysql procedure

proc:begin
declare vAppId   int unsigned;
declare vUserId  int unsigned;
declare vStatus  enum('nor', 'del','disable');

    if(pIdOnMarket is not null) then
   select id, userId, status into vAppId, vUserId, vStatus
     from t_apps
    where idOnMarket = pIdOnMarket
      and systemId = pSystemId;


        if(vAppId is not null) then
            if(vUserId <> pUserId) then -- 不同用户创建相同ID的应用，报内部错误
                select 205 into pRetCode;
                leave proc;
            else                 
           select 201 into pRetCode;
           leave proc;           
            end if;
            
   update t_apps set 
       name = ifnull(pName, name),
       platform = ifnull(pPlatform, platform),
       transition = ifnull(pTransition, transition),
       cycleTime = ifnull(pCycleTime, cycleTime),
       location = ifnull(pLocation, location),
       category = ifnull(pCategory, category),
       status = 'nor'
    where id = vAppId;


       /* 判断是否修改成功 */    
   if row_count() <= 0 then
       select 500 into pRetCode;
       leave proc;
   end if;    
            
   select 200 into pRetCode;
   leave proc;
        end if;
    else 
        select replace(uuid(), '-', '') into pIdOnMarket; 
    end if;
    
    insert into t_apps(
        name,
        systemId,
        userId,
        platform,
        transition,
        cycleTime,
        location,
        category,
        idOnMarket) 
    values(
        pName,
        pSystemId,
        pUserId,
        pPlatform,
        pTransition,
        pCycleTime,
        pLocation,
        pCategory,
        pIdOnMarket);            


    
    select @@Identity into vAppId;
    if vAppId is null or vAppId <= 0 then /* 判断用户是否增加成功 */
        select 500 into pRetCode;
        leave proc;
    end if;
    
    insert into t_updatedApps(appId, action)
    values(vAppId, 'refresh');
        
    select 200 into pRetCode;
    select vAppId as appId;
end

//mysql procedure about cursor ~~

-- Dumping structure for procedure hiad.prc_close_nw_task
DROP PROCEDURE IF EXISTS `prc_close_nw_task`;
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `prc_close_nw_task`(OUT `retcode` INT)
begin
declare  v_f0 int;




declare  flag boolean default true;
declare  curt1 cursor for select task_id from t_task  where status in('CREATIVE PENDING','RUN','AUDITED','AUDIT PENDING','REJECTED') and end_time<=date_sub(now(),interval 1 day);
declare  curt2 cursor for select task_id from t_task  where status in('CREATIVE PENDING','RUN','AUDITED','AUDIT PENDING','REJECTED') and delivery_model='GD' and impression_gain>=impression_goal;


declare continue handler for not found set flag = false;
  open curt1;


    fetch curt1 into  v_f0;   
    while flag do
    update t_task set status='DONE',modified_time=now() where task_id = v_f0;
    update t_task set settlement='Y' where task_id = v_f0 and delivery_model='CPT' ;
    commit;    
fetch curt1 into  v_f0;  
end while;
  
close curt1;
   set flag = true ;
  open curt2;
  
  
    fetch curt2 into  v_f0;
    while flag do
    update t_task set status='DONE',modified_time=now() where task_id = v_f0;
    commit;    
    fetch curt2 into  v_f0;
  end while;  
close curt2;
  call prc_close_nw_order();
  set retcode = 0;
end//
DELIMITER ;

declare 


val varchar2(80);
nolen int;
sublen int;
iter varchar(10);
begin
  
  --select getserialid(23) into val from dual;
  --dbms_output.put_line(val);


  nolen := length(12345678);
  sublen := nolen - 2;
  for iter in 1 .. sublen loop
      dbms_output.put_line(iter);
  end loop;


end;






declare
varStr varchar2(1000);
varStr1 varchar2(100);
useIng int(1);
begin
  
  useIng := 2;
  varStr := 'select trunc(sysdate + 1 ) from dual where 2 = :1';
  execute immediate varStr into varStr1 using useIng;
  dbms_output.put_line(varStr1);
end;












declare
  iter varchar2(20);
  TYPE cur_type is ref cursor;
  cur1 cur_type;
  queryStr varchar2(200);
  loopVal1 varchar2(20);
  loopVal2 varchar2(20);
  subStrs varchar2(100);
  finalVal varchar2(100);
begin
  
  queryStr := 'select 1, 2 from dual';
 
  open cur1 for queryStr;
 
       loop
            
           fetch cur1 into loopVal1, loopVal2;


            exit when cur1%notfound;
            
                 subStrs := 'select trunc(sysdate + 1 ) from dual where 1 = :1 and 2 = :2';
       
                 execute immediate subStrs into finalVal using loopVal1,loopVal2;
        
                 dbms_output.put_line(finalVal);
   
       end loop;
  
 
     if cur1%Isopen then 
        close cur1;
     end if;


end;

//page effecienty
select *
  from (select t.*, rownum rn
          from test t
         where rownum <= 3)
 where rn > 1;
 
 
 select * from test
 where rowid in (
 
   select rd
  from (select t.*, rownum rn ,rowid rd
          from test t
         where rownum <= 3)
 where rn > 1)

//找出重复数据用rowid

select e.rowid,e.*
  from test_table e
 where rowid not in (select min(rowid)
                  from test_table e2 
                  
                 where e.names = e2.names 
                 and e.id = e2.id and 
                 e.classes = e2.classes
                 )




//flashback
flashback table tablename to before drop [rename to tablename2];
select * from tab;
drop table test cascade constraints [purge];