如题,如何实现游标的动态使用,举个例子吧,谢谢!
解决方案 »
- 求救,怎样高效地将INFORMIX数据导入到ORACLE数据库,谢谢
- 大家帮忙看看:oracle 授权 权限设置 。错误:表或视图不存在
- 帮帮忙!递归如何优化啊
- 都是Oracler惹的祸!!!!
- 嵌套查询的问题
- clob字段能用like查询吗?
- 请问oracle.jdbc.driver.OracleConnection怎样才能新建一个OracleConnection?
- 我是新手,不知怎么搞的, OracleOraHome92ManagementServer 服务启动不起了.
- oracle Intelligent Agent 在linux上启动不了
- 编写一个存储过程,怎么添加一个返回值供程序判断是否添加成功?
- 记录一个表的数据更改除了使用触发外,还有什么其他方法没
- 100分,请专家帮忙解决。oracle 的 CLOB字段写入问题
for v_cursor in (select student_id from students where student_name=v_student_name) loop或者:open v_cursor for 'select student_id
from students
where student_name='||v_student_name;
loop fetch v_cursor into v_student_id;
exit when v_event_cur%notfound;以上是两种不同的动态游标
其中v_student_name是外部传进来的变量,v_student_id是自己定义的内部变量
//创建包,在包中创建refcursor
CREATE OR REPLACE PACKAGE "TYPES"
AS
TYPE refcursor IS REF CURSOR;
END;//创建存储过程obtainPartnumberProc
CREATE OR REPLACE PROCEDURE obtainPartnumberProc (
tablename IN VARCHAR2,
obid IN VARCHAR2,
rst IN OUT TYPES.refcursor
)
IS
v_sqlstring VARCHAR2 (1000);
BEGIN
v_sqlstring := 'SELECT PARTNUMBER FROM PDMM31A.'||tablename||' WHERE OBID=''' || obid || ''''; OPEN rst FOR v_sqlstring;
END obtainPartnumberProc;
(in_var varchar2)
is
begin
for cur in (select * from mytest where name=in_var) loop
dbms_output.put_line(cur.name);
end loop;
end;
如果查询的结果只有一个,则没有必要用游标:
如:
create or replace procedure proc (in_var in varchar2,out_var out varchar2) //out_var即位输出参数
is
begin
select name into out_var from mytest where id = in_var;
end;
如果查询的结果是一个结果集,则有必要用游标:
如:
CREATE OR REPLACE PACKAGE pac AS
TYPE cur IS REF CURSOR;
end ;CREATE OR REPLACE PROCEDURE proc(in_var in varchar2, p_CURSOR out pac.cur) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM mytest where id = in_var;
END;
在程序调用时:
如下:
CallableStatement proc = null;
proc = conn.prepareCall("{ call proc(?,?) }");
proc.setString(1, "100");
// proc.registerOutParameter(2, Types.VARCHAR); //根据类型选择
proc.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
不知我的回答是否让你满意!
CREATE OR REPLACE PROCEDURE P_T_DELETE_TESTER
(
T_IN_TESTER IN VARCHAR2
)
AS
T_CNT INT;
T_IN_FIED VARCHAR2(25);
type refCur is ref cursor;
cur refCur ;
BEGIN
SELECT COUNT(*) INTO T_CNT FROM W WHERE T_TESTER=T_IN_TESTER;
IF(T_CNT=0) THEN
RETURN;
ELSE
open cur for 'SELECT T_FIED FROM W WHERE T_TESTER='||T_IN_TESTER;
LOOP
FETCH cur into T_IN_FIED;
EXIT WHEN cur%NOTFOUND;
SELECT COUNT(*) INTO T_CNT FROM F WHERE T_FIED=T_IN_FIED;
if(T_CNT=0) THEN
DELETE FROM W WHERE T_TESTER=T_IN_TESTER AND T_FIED=T_IN_FIED;
END IF;
END LOOP;
CLOSE cur;
END IF;
END;