有关存储过程的问题

Question

1、没看出过程中嵌套有什么错，应该不是这个原因。编译失败的原因是什么？2、http://www.vbip.com/books/1861001789/chapter_1789_10.asp
http://www.vbip.com/books/1861001789/chapter_1789_11.asp
http://www.csdn.net/Expert/TopicView2.asp?id=338638&datebasetype=now

Answer 1

在sqlplus中执行报
输入被截为1个字符警告: 创建的过程带有编译错误

Answer 2

伙计你写错了
EXIT WITH c_tdylf%NOTFOUND;
-〉EXIT WHEN c_tdylf%NOTFOUND;

Answer 3

你可以在程序的结尾加上/
show errors;
就可以把错误打出来。

Answer 4

测试过程：
1、建立测试表
CREATE TABLE student
 (
  id                         NUMBER,
  name                       VARCHAR2(30),
  sex                        VARCHAR2(10),
  address                    VARCHAR2(100),
  postcode                   VARCHAR2(10),
  birthday                   DATE,
  photo                      LONG RAW
 )
/2、建立带ref cursor定义的包和包体及函数：
CREATE OR REPLACE
package pkg_test as
/* 定义ref cursor类型
   不加return类型，为弱类型，允许动态sql查询，
   否则为强类型，无法使用动态sql查询;
*/
  type myrctype is ref cursor; --函数申明
  function get(intID number) return myrctype;
end pkg_test;
/CREATE OR REPLACE
package body pkg_test as
--函数体
   function get(intID number) return myrctype is
     rc myrctype;  --定义ref cursor变量
     sqlstr varchar2(500);
   begin
     if intID=0 then
        --静态测试，直接用select语句直接返回结果
        open rc for select id,name,sex,address,postcode,birthday from student;
     else
        --动态sql赋值，用:w_id来申明该变量从外部获得
        sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
        --动态测试，用sqlstr字符串返回结果，用using关键词传递参数
        open rc for sqlstr using intid;
     end if;     return rc;
   end get;end pkg_test;
/3、用pl/sql块进行测试：
declare
  w_rc       pkg_test.myrctype; --定义ref cursor型变量  --定义临时变量，用于显示结果
  w_id       student.id%type;
  w_name     student.name%type;
  w_sex      student.sex%type;
  w_address  student.address%type;
  w_postcode student.postcode%type;
  w_birthday student.birthday%type;begin
  --调用函数，获得记录集
  w_rc := pkg_test.get(1);  --fetch结果并显示
  fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
  dbms_output.put_line(w_name);
end;4、测试结果：
通过。请问高手：
我已经按上面的程序做了一遍，可以运行。
   有2个问题：
   1。测试后，未在屏幕是显示。是否在最后加
  dbms_output.get_line();
   2.我如何获取这个结果集，我用java 写程序，只知道有一个out输出参数。

Answer 5

在sql/plus中要看到dbms_output的结果，需要先set serveroutput on

Answer 6

感谢kingsunsha:现在可以在屏幕上显示了；
问题：1，如果符合条件的记录有多条，为什么只显示1条，
      2，返回的结果集怎么取？
                                  谢！

Answer 7

因为你没有做LOOP  进行FETCH,所以只会返回第一条.返回的结果集怎么取?得看你前台的程序支不支持结果集.我用PB是无法实现的.可能没有找是方法.
只好变通实现.在包中申明一个全局游标,在过程中不断FETCH值,用PB循环取这个过程的返回值,只到遇到自己定义的结束标识.

Answer 8

jdbc中可以用一个CallableStatement来做，大致如下
  ...
  String sql = "{ call get_myRS(?, ?) }"; //函数
  stmt = connection.prepareCall(sql);
  stmt.setInt(...);  //加入参数
  stmt.registerOutParameter(1,Types.REF); //注册返回参数
  rs = stmt.execute(); //返回结果集大致是这样的，我没测试过，你自己试一下

Answer 9

这是msdn中有关返回记录集的办法(标准方法）
DROP TABLE person;CREATE TABLE person
 (ssn     NUMBER(9) PRIMARY KEY,
  fname   VARCHAR2(15),
  lname   VARCHAR2(20));INSERT INTO person VALUES(555662222,'Sam','Goodwin');INSERT INTO person VALUES(555882222,'Kent','Clark');INSERT INTO person VALUES(666223333,'Jane','Doe');COMMIT;
 /  
Create the following package on your Oracle server:CREATE OR REPLACE PACKAGE packperson
  AS
    TYPE tssn is TABLE of  NUMBER(10)
    INDEX BY BINARY_INTEGER;
    TYPE tfname is TABLE of VARCHAR2(15)
    INDEX BY BINARY_INTEGER;
    TYPE tlname is TABLE of VARCHAR2(20)
    INDEX BY BINARY_INTEGER;    PROCEDURE allperson
            (ssn    OUT     tssn,
             fname  OUT     tfname,
             lname  OUT     tlname);
    PROCEDURE oneperson
        (onessn IN      NUMBER,
         ssn    OUT     tssn,
             fname  OUT     tfname,
             lname  OUT     tlname);
END packperson;
/  
Create the following package body on your Oracle server:CREATE OR REPLACE PACKAGE BODY packperson
ASPROCEDURE allperson
            (ssn    OUT     tssn,
             fname  OUT     tfname,
             lname  OUT     tlname)
IS
    CURSOR person_cur IS
            SELECT ssn, fname, lname
            FROM person;    percount NUMBER DEFAULT 1;BEGIN
    FOR singleperson IN person_cur
    LOOP
            ssn(percount) := singleperson.ssn;
            fname(percount) := singleperson.fname;
            lname(percount) := singleperson.lname;
            percount := percount + 1;
    END LOOP;
END;PROCEDURE oneperson
      (onessn  IN    NUMBER,
             ssn     OUT   tssn,
             fname   OUT   tfname,
             lname   OUT   tlname)
IS
 CURSOR person_cur IS
           SELECT ssn, fname, lname
           FROM person
           WHERE ssn = onessn;    percount NUMBER DEFAULT 1;BEGIN
    FOR singleperson IN person_cur
    LOOP
            ssn(percount) := singleperson.ssn;
            fname(percount) := singleperson.fname;
            lname(percount) := singleperson.lname;
            percount := percount + 1;
    END LOOP;
END;
END;
/