oracle sql编程笔记

--decode
decode(uu.status,'10','1','11','3','12','2')
--case
case when uu.status='10' and partCodeMain.Status='10' then '1' when uu.status<>'10' or partCodeMain.Status<>'10' then '0' end as transStatus    
--left join
from uc_company_info uci
inner join uc_users uu on uci.root_user_id=uu.id
inner join (select uci.company_code partCodeMain,uci.company_cn partFullNameMain,uci.company_cn_short partShortNameMain,uci.ext1 partCategories1Main,uur.user_id,uu.status from uc_user_relate uur,uc_company_info uci,uc_users uu where uur.is_deleted = 'N' and uur.relate_user_id = uci.root_user_id and uci.root_user_id=uu.id) partCodeMain on partCodeMain.User_Id=uu.id
inner join uc_type t on t.id=partCodeMain.partCategories1Main
left join (select ufav.attr_value trade,ufav.object_id from uc_form_attr_values ufav where ufav.attr_key='trade' and ufav.is_deleted = 'N')trade on trade.object_id =uu.id
left join (select ufav.attr_value contEmail,ufav.object_id from uc_form_attr_values ufav where ufav.attr_key='contEmail' and ufav.is_deleted = 'N')contEmail on contEmail.object_id =uu.id
left join (select ufav.attr_value registerOrg,ufav.object_id from uc_form_attr_values ufav where ufav.attr_key='registerOrg' and ufav.is_deleted = 'N')registerOrg on registerOrg.object_id =uu.id
left join (select ufav.attr_value holderName,ufav.object_id from uc_form_attr_values ufav where ufav.attr_key='holderName' and ufav.is_deleted = 'N')holderName on holderName.object_id =uu.id
left join (select ufav.attr_value holderAccount,ufav.object_id from uc_form_attr_values ufav where ufav.attr_key='holderAccount' and ufav.is_deleted = 'N')holderAccount on holderAccount.object_id =uu.id
left join (select ufav.attr_value capitalAccountName,ufav.object_id from uc_form_attr_values ufav where ufav.attr_key='capitalAccountName' and ufav.is_deleted = 'N')capitalAccountName on capitalAccountName.object_id =uu.id
left join (select ufav.attr_value capitalAccount,ufav.object_id from uc_form_attr_values ufav where ufav.attr_key='capitalAccount' and ufav.is_deleted = 'N')capitalAccount on capitalAccount.object_id =uu.id
left join (select ufav.attr_value capitalAccountBank,ufav.object_id from uc_form_attr_values ufav where ufav.attr_key='capitalAccountBank' and ufav.is_deleted = 'N')capitalAccountBank on capitalAccountBank.object_id =uu.id
left join (select ufav.attr_value paymentSystemLine,ufav.object_id from uc_form_attr_values ufav where ufav.attr_key='paymentSystemLine' and ufav.is_deleted = 'N')paymentSystemLine on paymentSystemLine.object_id =uu.id
where uci.company_code='13000027';
--存储过程
create or replace procedure P_YHT_MEMBER_CONNECTOR
(
orgCode in varchar2,orgName in varchar2,rescur out sys_refcursor
)
as
tmp_str varchar2(4000);
rowRecord CM_MEMBER_BASE_INFO%ROWTYPE;
BEGIN
  if orgCode is not null  and orgName is null then
     tmp_str :='select orgcode as org_code,name as org_name,decode(mstate,'0','2','2') as mem_status,case when mstate='1' and mlevel='12' then '1' when mlevel<>'1' and mlevel='12' then '0' end as mem_filing from CM_MEMBER_BASE_INFO where orgcode=to_char( '|| chr(39)||orgCode|| chr(39)|| ')';
  elsif  orgName is not null  and orgCode is null then
    tmp_str :='select orgcode as org_code,case when mstate='1' and mlevel='12' then '1' when mlevel<>'1' and mlevel='12' then '0' end as mem_filing from CM_MEMBER_BASE_INFO where name= to_char('|| chr(39) ||orgName|| chr(39)|| ')';
  elsif orgCode is not null and orgName is not null then
     tmp_str :='select orgcode as org_code,case when mstate='1' and mlevel='12' then '1' when mlevel<>'1' and mlevel='12' then '0' end as mem_filing from CM_MEMBER_BASE_INFO where orgcode=to_char( '|| chr(39) ||orgCode || chr(39)|| ') and name=to_char( '|| chr(39) ||orgName || chr(39) || ')';
  elsif orgCode is null and orgName is null then
     tmp_str :='select orgcode as org_code,case when mstate='1' and mlevel='12' then '1' when mlevel<>'1' and mlevel='12' then '0' end as mem_filing from CM_MEMBER_BASE_INFO';
  end if;
  open rescur for tmp_str ;
  FETCH rescur INTO rowRecord; 
  if rescur%notfound  then    
    if orgCode is not null  and orgName is null then
     tmp_str :='select orgcode as org_code,case when mstate='1' and mlevel='12' then '1' when mlevel<>'1' and mlevel='12' then '0' end as mem_filing from CM_MEMBER_BASE_INFO_HIS where orgcode=to_char( '|| chr(39)||orgCode|| chr(39)|| ')';
    elsif  orgName is not null  and orgCode is null then
      tmp_str :='select orgcode as org_code,case when mstate='1' and mlevel='12' then '1' when mlevel<>'1' and mlevel='12' then '0' end as mem_filing from CM_MEMBER_BASE_INFO_HIS where name= to_char('|| chr(39) ||orgName|| chr(39)|| ')';
    elsif orgCode is not null and orgName is not null then
       tmp_str :='select orgcode as org_code,case when mstate='1' and mlevel='12' then '1' when mlevel<>'1' and mlevel='12' then '0' end as mem_filing from CM_MEMBER_BASE_INFO_HIS where orgcode=to_char( '|| chr(39) ||orgCode || chr(39)|| ') and name=to_char( '|| chr(39) ||orgName || chr(39) || ')';
    elsif orgCode is null and orgName is null then
       tmp_str :='select orgcode as org_code,case when mstate='1' and mlevel='12' then '1' when mlevel<>'1' and mlevel='12' then '0' end as mem_filing from CM_MEMBER_BASE_INFO_HIS';
    end if;
    open rescur for tmp_str ;
  end if;
END P_YHT_MEMBER_CONNECTOR;
--游标
--1.声明游标时写好SELECT语句，如
CURSOR r_cur1 IS select *** from tableName where 条件;
   OPEN r_cur1;
   LOOP
     FETCH *** INTO variable;
     EXIT WHEN r_cur1%NOTFOUND OR r_cur1%NOTFOUND IS NULL;
--2.声明游标
ccc sys_refcursor;
       open ccc for select dept_code,dept_name from comm.dept_dict; 
       loop
       fetch ccc into aa,bb;
       exit when ccc%notfound;
       。。。
       end loop;
       close ccc;
       
 --EXECUTE IMMEDIATE执行的是SQL,或者PL/SQL块。
--OPEN ... FOR 是 PL/SQL语句,所以你必须把它变成PLSQL块,即加上BEGIN ... END，还要把C_RES当作绑定变量传递。

CREATE OR REPLACE PROCEDURE SP_TEST(C_RES OUT SYS_REFCURSOR) AS
V_SQL VARCHAR2(1000);
BEGIN
  V_SQL:='BEGIN OPEN :C_RES FOR SELECT * FROM DUAL; END;';
  EXECUTE IMMEDIATE V_SQL  USING C_RES;
END SP_TEST; 
------------
create or replace   
function get_first_unlocked_row   
return t%rowtype  
as  
    resource_busy exception;  
    pragma exception_init( resource_busy,-54 );  
    l_rec t%rowtype;  
begin  
    for x in ( select rowid rid   
                 from t   
                 where decode(processed_flag,'N','N') = 'N')  
    loop  
    begin  
        select * into l_rec   
          from t   
         where rowid = x.rid and processed_flag='N'  
           for update nowait;  
        return l_rec;  
    exception  
        when resource_busy then null;  
    end;  
    end loop;  
    return null;  
end;  
-----------
declare  
    l_rec t%rowtype;  
    cursor c   
    is  
    select *  
      from t   
     where decode(processed_flag,'N') = 'N'  
       FOR UPDATE  
      SKIP LOCKED;  
begin  
    open c;  
    fetch c into l_rec;  
    if ( c%found )  
    then  
        dbms_output.put_line( 'I got row ' || l_rec.id || ',' || l_rec.payload );  
    end if;  
    close c;  
end; 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!