一、Oracle手册

1.递归参考写法

--建立测试表数据

with temp as(
select 0 as flevel, 'a' fparent,'b' fchild,3 fqty from dual
union all select 0,'a','c',1 from dual
union all select 0,'c','d',4 from dual
union all select 0,'b','e',1 from dual
union all select 0,'b','f',4 from dual
union all select 0,'e','h',2 from dual
union all select 0,'h','k',2 from dual)

--递归运用
select length(SYS_CONNECT_BY_PATH('', '1')) AS flevel,SYS_CONNECT_BY_PATH(fparent,'\') as fparent,fchild,fqty from temp
start with fchild='e' or fchild='c'
CONNECT BY PRIOR fchild=fparent ;

--运行结果：

 

2、存储过程执行语句写法：

begin
p_an_oa_supplier; --这是存储过程
end;

 

3、查询属于SqlServer来源的DBLINK的表或视图，字段查不出内容

以视图为例，在SqlServer那边创建的视图，把字段转换为varchar，就能在Oracle查的出字段内容了。

select
convert(varchar(100),"IntlASNNum") intlasnnum,
convert(varchar(100),"warehouse") warehouse,
convert(varchar(100),"COMPANY") company,
convert(varchar(100),"ReceiptId") receiptid,
convert(varchar(100),"ReceiptIdType") receiptidtype
from ASNHeader
where DateDiff(dd,ReceiptDate,getdate())<=8
and (( "LeadingSts">=900
and "TrailingSts">=900 ) or (closedate is not null and "TrailingSts"<900) )