我在数据库中建了以下:
CREATE OR REPLACE PACKAGE queryData as
type myrctype is ref cursor;
end queryData;create or replace procedure getSH_EMS_INData(conditionStr in varchar2,myrc out querydata.myrctype) is
begin
dbms_output.put_line(conditionStr);
open myrc for 'select product_id from sh_ems_in :1' using ' where 1=1';
--open myrc for select product_id from sh_ems_in;
end getSH_EMS_INData;
在SQLPLUS中执行:
set serveroutput on
declare
myrc queryData.myrctype;
str varchar2(20);
begin
getSH_EMS_INData(' where 1 = 1',myrc);
loop
fetch myrc into str;
dbms_output.put_line(str);
exit when myrc%notfound;
end loop;
end;
/出现错误ORA-00933: SQL 命令未正确结束
ORA-06512: 在"TINGER.GETSH_EMS_INDATA", line 4
ORA-06512: 在line 5
能帮我看一下,错在那吗?
CREATE OR REPLACE PACKAGE queryData as
type myrctype is ref cursor;
end queryData;create or replace procedure getSH_EMS_INData(conditionStr in varchar2,myrc out querydata.myrctype) is
begin
dbms_output.put_line(conditionStr);
open myrc for 'select product_id from sh_ems_in :1' using ' where 1=1';
--open myrc for select product_id from sh_ems_in;
end getSH_EMS_INData;
在SQLPLUS中执行:
set serveroutput on
declare
myrc queryData.myrctype;
str varchar2(20);
begin
getSH_EMS_INData(' where 1 = 1',myrc);
loop
fetch myrc into str;
dbms_output.put_line(str);
exit when myrc%notfound;
end loop;
end;
/出现错误ORA-00933: SQL 命令未正确结束
ORA-06512: 在"TINGER.GETSH_EMS_INDATA", line 4
ORA-06512: 在line 5
能帮我看一下,错在那吗?
解决方案 »
- (求助)oracle表查询速度问题
- SQL 错误: ORA-04098: 触发器 'PMG001.OBJECTADMIN_SEQ_TRIGGER' 无效且未通过重新验证
- 紧急求救,ORACLE系统文件坏掉了,但数据文件都是好了,我该怎么恢复
- ORA-01002 违反读取顺序错误(在线等,高分答谢)
- 数据库资源被消耗,但是找不到原因!
- 谁有oracle 10g的jdbc驱动?
- 安装oracle客户端碰到的奇怪问题!
- 对于您是小问题,对于我大问题★★★
- 紧急SOS,恳请那位大虾不吝赐教!谢谢!
- 谢谢chooser(chooser)的指点,那么请问哪里有应用服务器和另外两个服务器连接的资料呢?或者请给介绍几本书给我!
- 高分求触发器程序,把变动的数据ID记录下来,然后通过其他方式把它们取出来
- 初次使用使用TOAD,要证明证明2等价1,如何设置字体 ... ...
type myrctype is ref cursor;
end queryData;create or replace procedure getSH_EMS_INData(conditionStr in varchar2,myrc out querydata.myrctype) is
vs_sql varchar2(100);
begin
vs_sql:='select product_id from sh_ems_in :1' using ' where 1=1';
dbms_output.put_line(conditionStr);
open myrc for vs_sql;
--open myrc for select product_id from sh_ems_in;
end getSH_EMS_INData;
试验试验!应该可以了
可以是open cursorname for 'select cols from tablename where col_name = :1 ' using '条件值' 的格式
但不能是open cursorname for 'select cols from tablename :1' using '1=1'的格式;
如果要实现这样的目的,只能这样做:
CREATE OR REPLACE PACKAGE queryData as
type myrctype is ref cursor;
end queryData;create or replace procedure getSH_EMS_INData(conditionStr in varchar2,myrc out querydata.myrctype) is
begin
var_sql varchar2(8000);
dbms_output.put_line(conditionStr);
var_sql := 'select product_id from sh_ems_in ' ;
var_sql := var_sql || conditionStr; --假设你想代入的字符串应该就是条件列表
open myrc for var_sql;
--open myrc for select product_id from sh_ems_in;
end getSH_EMS_INData;
这样就不会有任何错误的。
package中的prc执行要带package名