--1,建立包,包体,存储过程CREATE OR REPLACE PACKAGE PKG_RTN
AS
type RESULT_TYPE is ref cursor; END PKG_RTN; create or replace procedure returnT(rs_data in out PKG_RTN.RESULT_TYPE) is
begin
open rs_data for
select * from test;
end;
--2,CREATE OR REPLACE PACKAGE INTEL.packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(55)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(70)
INDEX BY BINARY_INTEGER;type DATASET is REF CURSOR;
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);PROCEDURE allperson2 ;PROCEDURE allperson3 ( p_rc OUT DATASET);END packperson;
PROCEDURE allperson3( p_rc OUT DATASET)
IS
VCURSOR DATASET;
BEGIN OPEN VCURSOR FOR
select * FROM person;
p_rc := VCURSOR;
END;
END;
AS
type RESULT_TYPE is ref cursor; END PKG_RTN; create or replace procedure returnT(rs_data in out PKG_RTN.RESULT_TYPE) is
begin
open rs_data for
select * from test;
end;
--2,CREATE OR REPLACE PACKAGE INTEL.packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(55)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(70)
INDEX BY BINARY_INTEGER;type DATASET is REF CURSOR;
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);PROCEDURE allperson2 ;PROCEDURE allperson3 ( p_rc OUT DATASET);END packperson;
PROCEDURE allperson3( p_rc OUT DATASET)
IS
VCURSOR DATASET;
BEGIN OPEN VCURSOR FOR
select * FROM person;
p_rc := VCURSOR;
END;
END;
解决方案 »
- 写一个select查询语句
- windows xp 安装oracle9i 出现“OracleOraHomePagingServer已经存在”错误?
- !!!郁闷了,又是一个连接QRACLE的问题!!!
- 数据库名问题
- 急救,在线等:用oracle 存储工程动态创建了张表,用desc查看表结构,系统提示找不到表
- 用触发器如何实现定时功能吗?
- 如何设置表格中的某列按物理降序排列? 急!
- 如何把字符串"6/20/01 4:56:16 PM"转换成为Oracle的日期型?jiezhi(Niu)大哥在么?
- 数据库连接问题,火急
- oracle怎么用存储过程计算年龄并且修改年龄
- 请大家推荐!!
- sql语句及exists的问题
1,为什么会在pl/sql developer的PACKAGEs那边有红色的x号啊?
2,create or replace procedure returnT(rs_data in out PKG_RTN.RESULT_TYPE) is ...
参数中为何有in out啊。我知道的不是要么in,要么out。
1,
create or replace procedure returnT(rs_data in out PKG_RTN.RESULT_TYPE) is这句还是出错啊,没有建立好procedure 啊,高亮度的一行黄颜色
TYPE RESULT_TYPE IS REF CURSOR;END PKG_RTN;
/
CREATE OR REPLACE PROCEDURE RETURNT(RS_DATA IN OUT PKG_RTN.RESULT_TYPE) IS
BEGIN
OPEN RS_DATA FOR
SELECT * FROM A;
END;
/
--2, CREATE OR REPLACE PACKAGE PACKPERSON AS
TYPE TSSN IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;
TYPE TFNAME IS TABLE OF VARCHAR2(55) INDEX BY BINARY_INTEGER;
TYPE TLNAME IS TABLE OF VARCHAR2(70) INDEX BY BINARY_INTEGER; TYPE DATASET IS REF CURSOR; --PROCEDURE ALLPERSON(SSN OUT TSSN, FNAME OUT TFNAME, LNAME OUT TLNAME); --PROCEDURE ALLPERSON2; PROCEDURE ALLPERSON3(P_RC OUT DATASET);END PACKPERSON;
/
CREATE OR REPLACE PACKAGE BODY PACKPERSON AS
PROCEDURE ALLPERSON3(P_RC OUT DATASET) IS
VCURSOR DATASET;
BEGIN
OPEN VCURSOR FOR
SELECT a FROM A;
P_RC := VCURSOR;
END;
END;
/DECLARE
l_c packperson.DATASET;
BEGIN
packperson.ALLPERSON3(l_C);
END;
/
AS
type RESULT_TYPE is ref cursor; END PKG_RTN; create or replace procedure returnT(rs_data in out PKG_RTN.RESULT_TYPE) is--此句错 begin
open rs_data for
select * from test;
end; --错误信息:
pls_00103 encountered the symbol '''create'高手们帮忙看看啊,语法是对的啊,错在那里了
2,CREATE OR REPLACE PACKAGE BODY PACKPERSON 都是语法错误啊,
--错误信息:
pls_00103 encountered the symbol "create"
我的用的是10g啊,不知道那里出错了,上面的在您的环境中运行是正常的吗?难道我的pl/sql developer有问题?
AS
type RESULT_TYPE is ref cursor;END PKG_RTN;create or replace procedure returnT(rs_data in out PKG_RTN.RESULT_TYPE) is
begin
open rs_data for
select * from test;
end; 这一部分错在存储过程returnT的参数rs_data没有声明,修改如下:SQL> edit
已写入 file afiedt.buf 1 create or replace package pkg_rtn as
2 type result_type is ref cursor;
3 procedure returnT(rs_data in out pkg_rtn.result_type); /* 存储过程需要在包头的定义中声明 */
4* end pkg_rtn;
SQL> /程序包已创建。SQL> edi
已写入 file afiedt.buf 1 create or replace procedure returnT(rs_data in out pkg_rtn.result_type) is
2 begin
3 open rs_data for
4 select * from dual; /* 楼主改成自己的语句吧,偶这里没有你的表 */
5* end;
SQL> /过程已创建。SQL> show err
没有错误。
SQL>第二部分的package修改类似第一部分的就可以了。
谢谢了,明白了,1,我是把这2块语句都放在建package 的语句执行的,应该是先建立包头的声明,然后在建立procedure 或者函数是吗?
2,如果我要在pl/sql developer调用procedure(returnT),如何看到此记录集的结果。我可以debug到此procedure(returnT),
begin
-- Call the procedure
returnt(rs_data => :rs_data);
end;类似您写的这个---------------
DECLARE
l_c packperson.DATASET;
BEGIN
packperson.ALLPERSON3(l_C);
END;
----------------
我要如何写啊?谢谢您的帮助。
to:oracledbalgtu还有一个问题如果我在returnT中传入一个参数
create or replace procedure returnT(depture varchar(20), rs_data in out pkg_rtn.result_type) is
begin
(
open rs_data for
select * from test where depname=depture;)--这句动态语句如何写呢?
end; 我就不查资料了,累了,请指点一下吧再次表示感谢!
OPEN RS_DATA FOR 'SELECT * FROM TEST WHERE DEPNAME = :X' USING DEPTURE;
..
END;
BEGIN
OPEN RS_DATA FOR 'SELECT * FROM TEST WHERE DEPNAME = :X and DESNAME=:Y' USING DEPTURE,DESTINATION;
..
END; 1,DEPTURE
2,DESTINATION
分别是输入参数