Oracle学习 第28天 存储过程输出结果集

周末闲来练练Oracle的存储过程，还从来没写过，一写立马蒙逼。。。没有对比就没有伤害，SQL Server和SSMS真是方便啊

一、建立存储过程

CREATE OR REPLACE PROCEDURE upCustomStat (
    sAction VARCHAR2,
    dNewFromDate DATE,
    dNewToDate DATE,
    dOldFromDate DATE,
    dOldToDate DATE,
    sSQLText VARCHAR2
) AS
sSQL VARCHAR2(4000);
sIntRegServer VARCHAR2(50);
sIntRegDBName VARCHAR2(50);
sIntRegLinkServer VARCHAR2(100);
p_cur SYS_REFCURSOR;
CaseID VARCHAR2(50);
BEGIN
    OPEN p_cur FOR SELECT CASEID FROM Caseproject WHERE ROWNUM < 10;
    LOOP
        FETCH p_cur INTO CaseID;
        EXIT WHEN p_cur%NOTFOUND;
        dbms_output.put_line(CaseID);
    END LOOP;
    CLOSE p_cur;
END;

1、END末尾没有分号要报错，又提示的乱七八槽，找了半天才找到

错误：PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
        ; <an identifier>
          <a double-quoted delimited-identifier> current delete exists
          prior <a single-quoted SQL string>
       符号 ";" 被替换为 "end-of-file" 后继续。
行：1498

2、在存储过程里面的语法错误，执行后不会提示，但存储过程是编译不通过的，这样太不方便查原因了

 3、要想实时查看错误的话，要在存储过程上右键->编辑

4、编辑存储过程有个问题，平时我们都是用.sql文件存起来的，右键编辑调试完成后，还要手动复制到.sql文件中，太不方便。

直接保存的话是存成.prc文件，用这种存起来倒是也可以，但SQL Server过来习惯了存.sql文件，也比较通用。

二、调用存储过程

BEGIN 
    UPCUSTOMSTAT ('按开发项目统计预售情况数据',  TO_DATE('2016-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
        TO_DATE('2016-08-31 23:59:59','YYYY-MM-DD HH24:MI:SS'), NULL, NULL, NULL);
END;

 1、EXEC 存储过程名不行，看网上说好像要在命令行模式下？

2、在PL/SQL里的SQL窗口，只能用BEGIN END里直接执行

3、时间参数，不能直接传字符串，而是要转换。。。SQL Server都能自动识别

=================================

以上是使用dbms_output.put_line输出PRINT之类的文本，要输出结果集要用游标

=================================

三、输出结果集

1、存储过程使用游标

CREATE OR REPLACE PROCEDURE upCustomStat (
    sAction VARCHAR2,
    dNewFromDate DATE,
    dNewToDate DATE,
    dOldFromDate DATE,
    dOldToDate DATE,
    sSQLText VARCHAR2,
    p_cur OUT SYS_REFCURSOR
) AS
sSQL VARCHAR2(4000);
sIntRegServer VARCHAR2(50);
sIntRegDBName VARCHAR2(50);
sIntRegLinkServer VARCHAR2(100);
BEGIN
    OPEN p_cur FOR SELECT * FROM Caseproject WHERE ROWNUM < 10;
END upCustomStat;

2、调用时传入游标

DECLARE
    p_cur SYS_REFCURSOR;
BEGIN
    UPCUSTOMSTAT ('按开发项目统计预售情况数据',  TO_DATE('2016-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
        TO_DATE('2016-08-31 23:59:59','YYYY-MM-DD HH24:MI:SS'), NULL, NULL, NULL, p_cur);
END;

3、有了游标，按SQL Server惯性思维，一直想把结果集SELECT出来，搜了半天，看来是不行。。。只能手动输出

 

四、SQL Command

PL/SQL的Command和SQL Plus的Command还不一样。。。

SQL> var rset refcursor; 
SQL> exec getEmpByDept(10,:rset); 
SQL> print rset;

参照网上的这一段，在PL/SQL的命令行，会报【 REFCURSOR not supported】这个错，不识别，要SQL Plus才可以

使用SQL Plus后，确实能够根据游标把结果集查询出来

五、定义游标

1、使用【sys_refcursor】，即和上面用法一样，这个最方便

2、使用包来定义【ref cursor】，要多定义一个包头，再在包体中定义存储过程

StackOverFlow上有高手论述过，sys_refcursor其实就是事先定义好的ref cursor，专门为了方便的（少定义一个包头），可以放心使用