一、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) )

 

4、来源SqlServer的DBLink的表，查询中文乱码（补充3）

SqlServer字符集为Latin1_General_CI_AS

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation;

Oracle字符集为AL32UTF8

SELECT value FROM nls_database_parameters  WHERE parameter = 'NLS_CHARACTERSET';

在Oracle查询SqlServer的语句，中文乱码的解决方式：

SELECT
UTL_I18N.RAW_TO_CHAR(
UTL_RAW.CAST_TO_RAW("VendorName"), -- 字符串转二进制
'AL16UTF16' -- 按 UTF-16 解码
) AS VendorName ,"VENDOR","UserDefine2"
FROM Vendor@Zns;