我在sql plus中创建了一个存储过程,可是报下面错误,请问我哪里错了??SQL> CREATE OR REPLACE PROCEDURE ObtainPartNumberProc( leftobid in VARCHAR2 ) as
2 V_SQLString VARCHAR2( 100 ) ;
3 BEGIN
4 V_SQLString :='SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='||leftobid||' ;
5 EXECUTE IMMEDIATE V_SQLString;
6 END ObtainPartNumberProc
7 /警告: 创建的过程带有编译错误。
2 V_SQLString VARCHAR2( 100 ) ;
3 BEGIN
4 V_SQLString :='SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='||leftobid||' ;
5 EXECUTE IMMEDIATE V_SQLString;
6 END ObtainPartNumberProc
7 /警告: 创建的过程带有编译错误。
修改为
V_SQLString :='SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='''||leftobid || '''';
定义一个动态游标作为返回类型,这样才有意义CREATE OR REPLACE PROCEDURE ObtainPartNumberProc( leftobid in VARCHAR2,rst out sys_refcursor)
is
V_SQLString VARCHAR2( 1000 ) ;
BEGIN
V_SQLString :='SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='||leftobid;
open rst for V_SQLString;
END ObtainPartNumberProc;
sqlplus 下,直接用show error,然后就看清楚了。何必要我们帮你调试。
或者在诸如plp/sql developer中进行编译也行
我不是初学者吗,对oracle刚刚接触,如果我会的话也不用问啊!
CREATE OR REPLACE PROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT sys_refcursor
)
IS
v_sqlstring VARCHAR2 (1000);
BEGIN
v_sqlstring :=
'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='
|| leftobid; OPEN rst FOR v_sqlstring;
END obtainpartnumberproc;PLS-00201:必须说明标识符'SYS_REFCURSOR'请朋友帮我分析一下原因啊,我先谢谢了!!
如果没有可以这么修改:
定义一个包,然后在包中定义类型
CREATE OR REPLACE PACKAGE "TYPES" as
type refcursor is ref cursor;
end;
CREATE OR REPLACE PROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT refcursor
)
或者也可以通过其它方式。
CREATE OR REPLACE PROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT types.refcursor
)
这个错是说没有SYS_REFCURSOR 这个类型;
http://www.cnblogs.com/attraction/archive/2008/01/23/13489.html
http://www.chinaitpower.com/A200507/2005-07-27/171528.html
楼主去看看,就应该没问题了,创建包的时候,包头,包体分开创建
SQL> CREATE OR REPLACE PACKAGE "TYPES"
2 AS
3 TYPE refcursor IS REF CURSOR;
4 END;
5 /程序包已创建。SQL> CREATE OR REPLACE PROCEDURE obtainpartnumberproc (
2 leftobid IN VARCHAR2,
3 rst IN OUT TYPES.refcursor
4 )
5 IS
6 v_sqlstring VARCHAR2 (1000);
7 BEGIN
8 v_sqlstring :=
9 'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='
10 || leftobid;
11
12 OPEN rst FOR v_sqlstring;
13 END obtainpartnumberproc;
14 /过程已创建。SQL> EXECUTE TYPES.obtainpartnumberproc('sieczKcpdmtstpdmm31a-bJ1')
BEGIN TYPES.obtainpartnumberproc('sieczKcpdmtstpdmm31a-bJ1'); END; *
ERROR 位于第 1 行:
ORA-06550: 第 1 行, 第 13 列:
PLS-00302: 必须说明 'OBTAINPARTNUMBERPROC' 组件
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored真的惭愧啊,我以前没接触过oracle,是个十足的菜鸟,请各位多帮帮忙啊,十分感谢!
SQL> EXECobtainpartnumberproc('sieczKcpdmtstpdmm31a-bJ1')ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'OBTAINPARTNUMBERPROC' 时参数数量或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
应该是调用时参数的个数不对,我创建的过程有两个参数即:leftid和rst那我调用时应该传递几个参数,rst应该传什么值啊?
Type rst Is ref Cursor;end PKG_ObtainPartNumberProc ;
/
create or replace package body PKG_ObtainPartNumberProc is
PROCEDURE ObtainPartNumberProc( leftobid in VARCHAR2,
GetList out rst) as
V_SQLString VARCHAR2( 100 ) ;
BEGIN
V_SQLString :='SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='||leftobid||' ;
open GetList for V_SQLString ;
END ObtainPartNumberProc ;
end PKG_ObtainPartNumberProc ;
/
Type rst Is ref Cursor;end PKG_ObtainPartNumberProc ;
/
create or replace package body PKG_ObtainPartNumberProc is
PROCEDURE ObtainPartNumberProc( leftobid in VARCHAR2,
GetList out rst) as
V_SQLString VARCHAR2( 100 ) ;
BEGIN
V_SQLString :='SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='||leftobid;
open GetList for V_SQLString ;
END ObtainPartNumberProc ;
end PKG_ObtainPartNumberProc ;
/
SQL> CREATE OR REPLACE PROCEDURE obtainpartnumberproc (
2 leftobid IN VARCHAR2,
3 rst IN OUT refcursor
4 )
5 IS
6 v_sqlstring VARCHAR2 (1000);
7 BEGIN
8 v_sqlstring :=
9 'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT=
10 :leftobid';
11
12 OPEN rst FOR v_sqlstring using lefttobid;
13 END obtainpartnumberproc;
SQL> CREATE OR REPLACE PACKAGE "TYPES"
2 AS
3 TYPE refcursor IS REF CURSOR;
4 END;
5 /程序包已创建。SQL> CREATE OR REPLACE PROCEDURE obtainpartnumberproc (
2 leftobid IN VARCHAR2,
3 rst IN OUT TYPES.refcursor
4 )
5 IS
6 v_sqlstring VARCHAR2 (1000);
7 BEGIN
8 v_sqlstring :=
9 'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='
10 || leftobid;
11
12 OPEN rst FOR v_sqlstring;
13 END obtainpartnumberproc;
14 /过程已创建。SQL> EXEC obtainpartnumberproc('sieczKcpdmtstpdmm31a-bJ1')ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'OBTAINPARTNUMBERPROC' 时参数数量或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored请问我该怎样调用obtainpartnumberproc这个存储过程呢?