达梦游标的简单使用
create table T1(sex varchar2(10),name varchar2(20));
insert into t1 values('男','小刘');
insert into t1 values('女','小陈');
insert into t1 values('女','晓燕');
insert into t1 values('女','小红');
commit;
select * from t1;
DECLARE
CURSOR c_t1_cursor is select sex,name from t1 where sex='女';
v_sex t1.sex%type;
v_name t1.name%type;
begin
open c_t1_cursor;
loop
fetch c_t1_cursor into v_sex,v_name;
exit when c_t1_cursor%notfound;
print(v_name||' is '||v_sex);
end loop;
close c_t1_cursor;
end;
注：游标的定义要在匿名块的定义部分定义，游标打开、提取数据、关闭都在执行部分。
2 参数游标语法：
CURSOR cursor_name
[(parameter_name datatype, ...)]
IS
select_statement;
......
OPEN cursor_name(parameter_value,.....) ;
DECLARE
CURSOR c_t1_cursor(c_sex varchar2(10)) is select sex,name from t1 where sex=c_sex;
v_sex t1.sex%type;
v_name t1.name%type;
begin
open c_t1_cursor('男');
loop
fetch c_t1_cursor into v_sex,v_name;
exit when c_t1_cursor%notfound;
print(v_name||' is '||v_sex);
end loop;
close c_t1_cursor;
end;
注：open c_t1_cursor('男')也可以改为open c_t1_cursor(&sex);
3 游标for循环语法：
FOR record_name IN cursor_name|select_statement LOOP
statement1;
statement2;
......
END LOOP;
begin
for t1_record in (select sex,name from t1 where sex='女') loop
print(t1_record.name||' is '||t1_record.sex);
end loop;
end;
4 游标表达式语法：
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
cursor_variable ref_type_name;
ref_type_name：用于指定自定义类型名
RETURN：用于指定返回结果的数据类型
cursor_variable：用于指定游标变量名
DECLARE
TYPE t1_cursor IS REF CURSOR;
my_cursor t1_cursor;
v_sex t1.sex%type;
v_name t1.name%type;
begin
OPEN my_cursor FOR select sex,name from t1 where sex='女';
LOOP
FETCH my_cursor INTO v_sex,v_name;
EXIT WHEN my_cursor%NOTFOUND;
print(v_name||' is '||v_sex);
end loop;
close my_cursor;
end;
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
