前端之家收集整理的这篇文章主要介绍了
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;