ORACLE 的游标是不能动态定义的
解决方案 »
- 如何用SQL Fri Nov 11 00:00:00 CST 2011 转换为2011-11-11
- 关于触发器请教
- sum查询结果中,如何去除空记录,请大虾朋友们指点
- 怎么把这五条SQL语句合并成一条SQL语句?
- 如何判断在数据库中某个表的存在,如果存在就不创建,否则就创建
- oracle中有个列的是这样描述的: column1 (7,2),怎么解释?
- 请教 oracle的数据类型
- pga是sga的一部分吗?
- 树型结构的查询和输出问题!!
- 我在建立过程的时候,出现这样的错误,请大家帮我解决一下,先谢谢了!
- 请问各位高手用SQL如何实现一个日期是否在给定日期的范围
- 通过jdbc,执行insert语句,每插入50条记录就开始报错?
field2='||para2||' and field3='||para3; 改为
sqlstr:='select * from mytalbe where field1='||para1||' and '||
' field2='||para2||' and field3='||para3;
sqlstr varchar2;
改为:
sqlstr varchar2(100);
但是当我在Oracle Schema manager里编辑这个存储过程时,
create procedure test_1(table_cursor out hxapkg.hxatypequery
,mem_id in number)
AS
sqlstr varchar2(100);
begin
sqlstr:='select * from entity_members where card_id='||mem_id;
Open table_cursor for sqlstr;
end;
给出如下错误:
PLS-00103:出现符号"SQLSTR"在需要下列之一时
select
不知何故,请各位大虾明示,我用的Oralce是8.0.5
谢谢了
create procedure test_1(table_cursor in out hxapkg.hxatypequery%TYPE
,mem_id in number)
c number;
d number;
col_cnt integer;
f boolean;
rec_tab dbms_sql.desc_tab;
col_num number;
procedure print_rec(rec in dbms_sql.desc_rec) is
begin
dbms_output.new_line;
dbms_output.put_line('col_type = '
|| rec.col_type);
dbms_output.put_line('col_maxlen = '
|| rec.col_max_len);
dbms_output.put_line('col_name = '
|| rec.col_name);
dbms_output.put_line('col_name_len = '
|| rec.col_name_len);
dbms_output.put_line('col_schema_name = '
|| rec.col_schema_name);
dbms_output.put_line('col_schema_name_len = '
|| rec.col_schema_name_len);
dbms_output.put_line('col_precision = '
|| rec.col_precision);
dbms_output.put_line('col_scale = '
|| rec.col_scale);
dbms_output.put('col_null_ok = ');
if (rec.col_null_ok) then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end;
begin
c := dbms_sql.open_cursor; dbms_sql.parse(c, 'select * from scott.bonus', dbms_sql);
d := dbms_sql.execute(c);
dbms_sql.describe_columns(c, col_cnt, rec_tab);/*
* Following loop could simply be for j in 1..col_cnt loop.
* Here we are simply illustrating some of the PL/SQL table
* features.
*/
col_num := rec_tab.first;
if (col_num is not null) then
loop
print_rec(rec_tab(col_num));
col_num := rec_tab.next(col_num);
exit when (col_num is null);
end loop;
end if;
dbms_sql.close_cursor(c);
end;
/
只有游标变量才支持动态
用ref来定义
你可以查一下pl/sql书立刻可知