create or replace procedure p_getrsnamebytableid( tableid in number,
out_sy out Arraylist,
result out number)
is
v_sql varchar2(1000);
tablename varchar2(50);
TYPE MYRCTYPE IS REF CURSOR;
rc myrctype;
id number;
name1 varchar2(100);
begin
savepoint p_getrsnamebytableid;
select f_tablename into tablename from t_edu_sys_table where id =tableid;
v_sql := 'select id f_name from ' ||tablename;
out_sy :=Arraylist();
OPEN rc FOR v_sql;
loop
fetch rc into id,name1;
dbms_output.put_line(id);
OUT_SY.extend;
OUT_SY(OUT_SY.last) := id;
OUT_SY.extend;
OUT_SY(OUT_SY.last) := name1;
exit when(rc%NOTFOUND);
end loop;
CLOSE rc;
result :=100;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO p_getrsnamebytableid;
result :=101;
end p_getrsnamebytableid;
out_sy out Arraylist,
result out number)
is
v_sql varchar2(1000);
tablename varchar2(50);
TYPE MYRCTYPE IS REF CURSOR;
rc myrctype;
id number;
name1 varchar2(100);
begin
savepoint p_getrsnamebytableid;
select f_tablename into tablename from t_edu_sys_table where id =tableid;
v_sql := 'select id f_name from ' ||tablename;
out_sy :=Arraylist();
OPEN rc FOR v_sql;
loop
fetch rc into id,name1;
dbms_output.put_line(id);
OUT_SY.extend;
OUT_SY(OUT_SY.last) := id;
OUT_SY.extend;
OUT_SY(OUT_SY.last) := name1;
exit when(rc%NOTFOUND);
end loop;
CLOSE rc;
result :=100;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO p_getrsnamebytableid;
result :=101;
end p_getrsnamebytableid;
解决方案 »
- 急急急!!Oracle9i2OleDBDriver驱动出错
- 删除oracle数据库时报错,提示有连接连在上面,应该怎么kill这个连接?
- 帮帮我理解oracle层次化查询
- 80ORA-12571:TNS:包写入程序失败如何解决
- 怎么进入自己用DBCA建的数据库,而非全局数据库?
- 请问关于Java执行Oracle存储过程时参数为BLOB的问题!
- SQL Plus中输入的主机字符串应该输入什么啊,在线等待
- oralce9 for hp-ux 64bit 能不能安装到redhat2.1企业版上,急
- 100分请教:如何统计查询,需要嵌套吗?
- oracle使用sql*loader导入数据出错,但是sql*plus可以正常使用。
- oracle存储过程中游标问题
- window下tnsname文件传到UNIX下多了“^M”字符
fetch rc into id,name1;
loop
dbms_output.put_line(id);
OUT_SY.extend;
OUT_SY(OUT_SY.last) := id;
OUT_SY.extend;
OUT_SY(OUT_SY.last) := name1;
fetch rc into id,name1;
exit when(rc%NOTFOUND);
end loop;