ORACLE 经验两则
一、ORACLE 在存储过程使用Sys_Refcursor返回结果集
我们如何在procedure中完成对结果集的查询呢,从oracle7.3才被支持,在9i以后又有新的变化,在9i以前要define一个type才可以。而在9i以后oracle引入了一个新的类型为sys_refcursor,这样就不需要我们重新定义。我们来看一个例子吧。
Procedure Zkquery
(
p_Jyjh In Varchar2,
p_Wtdm In Varchar2,
p_Begindate In Date,
p_Enddate In Date,
Cur Out Sys_Refcursor
) As
Begin
Open Cur For
Select Fpclh 批处理号,
Fyyf 费用月份,
Zhs 托收户数,
Zje / 100 托收金额,
Cghs 成功户数,
Cgje 成功金额,
Case
When Bz = '0' Then
'未作返回'
Else
'已做返回'
End 返回标志,
Scph 上传批号,
Schs 上传户数,
Scje / 100 上传金额,
Zxrq 执行日期,
Ctpc 出托批次,
Sntfile 扣费文件,
Rtnfile 返回盘文件
From t_Zkzl
Where Wtdm = p_Wtdm And Jyjh = p_Jyjh And Zxrq Between p_Begindate And p_Enddate;
End Zkquery;
二、外部表的使用
ORACLE可以使用外部表关联外部EXCEL或文本文件,方便将数据导入数据库表,在邮储批扣系统开发过程中,主要解决两个关键问题:
1、 如何忽略文本的标题或总控行
使用SKIP 关键字
2、 如何识别DOS或UNIX格式文本
records delimited by newline
records delimited by 0x' 0 A '
举例如下:
DOS格式文件识别
create table DXPK
(
ZSXH VARCHAR2(20),
HM VARCHAR2(20),
A VARCHAR2(10),
JFHM VARCHAR2(30),
ZH VARCHAR2(20),
RQ VARCHAR2(15),
FYJE NUMBER,
B VARCHAR2(10),
C VARCHAR2(1),
D VARCHAR2(1),
PNXH VARCHAR2(10)
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY pkdata
ACCESS PARAMETERS (
records delimited by newline
skip 1
nologfile
nobadfile
nodiscardfile
fields terminated by '|'
missing field values are null
reject rows with all null fields
)
LOCATION ('Dxpk' )
)
PARALLEL 4 REJECT LIMIT UNLIMITED
UNIX格式识别:
create table RTN
(
XH VARCHAR2(8),
YDM VARCHAR2(2),
ZKBZ VARCHAR2(1),
ZH VARCHAR2(19),
KHJDM VARCHAR2(20),
HM VARCHAR2(30),
CKYE NUMBER,
KYYE NUMBER,
JYJE NUMBER,
ZJSXF NUMBER,
YWSXF NUMBER,
YLSXF NUMBER
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY pkdata
ACCESS PARAMETERS (
records delimited by 0x'
0
A
'
nologfile
nobadfile
nodiscardfile
skip 1
fields terminated by '|'
missing field values are null
reject rows with all null fields
)
LOCATION ('RTN' )
)
PARALLEL 4 REJECT LIMIT UNLIMITED