ORACLE 经验两则

来源：互联网发布：福建师范大学校园网络编辑：程序博客网时间：2024/04/29 22:19

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'0A'

举例如下：

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'0A'
                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