declare
cursor c1 is select ename,sal from emp where rownum<11;
my_ename emp.ename%type;
my_sal emp.sal%type;
begin
open c1;
loop
fetch c1 into my_ename,my_sal;
if c1%found then
dbms_output.put_LIne('name = '||my_ename|| ', sal ='||my_sal);
else
exit;
end if;
end loop;
close c1;
end;
---------------------------
declare
cursor c1 is select ename,sal from emp where rownum<11;
my_ename emp.ename%type;
my_sal emp.sal%type;
vc_message varchar2(4000);
begin
open c1;
loop
fetch c1 into my_ename,my_sal;
if c1%found then
dbms_output.put_LIne('name = '||my_ename|| ', sal ='||my_sal);
else
exit;
end if;
end loop;
close c1;
exception
when invalid_cursor then
dbms_output.put_Line('invaild_cursor');
return;
when others then
vc_message:=sqlcode||'_'||sqlerrm;
return;
end;
cursorname%isipen
exception
when others then
if c1%isopen =true then
close c1;
end if;
return;
end;
cursorname%rowcount
if c1%found then
dbms_output.put_LIne('name = '||my_ename|| ', sal ='||my_sal);
dbms_output.put_LIne(c1%rowcount ||'name = '||my_ename);
else
exit;
end if;
name = SMITH, sal =800
1name = SMITH
name = ALLEN, sal =1600
2name = ALLEN
name = WARD, sal =1250
3name = WARD
当一个显式游标还没有被打开时，使用found，notfound，rowcount都会报错
当首次fecth为null时，found为false，notfount为true，rowcount=0
参考游标 ref cursor
可以作为procedure的输入参数和function的输出参数
type typ_cur_emp is ref cursor return emp%rowtype;
cur_emp typ_cur_emp;
type typ_result is record(ename emp.ename%type, sal emp.sal%type);
type typ_cur_strong is ref cursor return typ_result;
cur_emp type_cur_strong;
type typ_cur_weak is ref cursor
cur_emp typ_cur_weak;
cur_emp sys_refcursor;
四种方式 分别定义同你一个参考游戏cur_emp
declare
type typ_cur_emp is ref cursor return emp%rowtype;
cur_emp typ_cur_emp;
procedure process_emp_cv(emp_cv in typ_cur_emp) is
person emp%rowtype;
begin
DBMS_OUTPUT.PUT_LINE('------');
loop
fetch emp_cv into person;
exit when emp_cv%notfound;
DBMS_OUTPUT.PUT_LINE('name = '||person.ename);
end loop;
end;
begin
open cur_emp for select * from emp where rownum<11;
process_emp_cv(cur_emp);
close cur_emp;
open cur_emp for select * from emp where ename like'C%';
process_emp_cv(cur_emp);
close cur_emp;
end;
不能直接在一个package或者package body的定义部分定义一个参考游标类型的cursor变量
create package pck_refcursor_open_dmep as
type gentype is ref cursor;
genri_cv gentype;---defalut 1 不正确
procedure open_cv(genri_cv in out gentype ----defult 3 ,choice int);
and pck_refcursor_open_dmep;
create package body pck_refcursor_open_dmep as
genri_cv gentype;--defalut 2 不正确
procedure open_cv(genri_cv in out gentype ,choice int);
genri_cv gentype; ---default 4
begin
null
end ;
end pck_refcursor_open_dmep;
批量fetch数据
declare
type empcurtype is ref cursor return emp%rowtype;
emp_cv empcurtype;
emp_rec emp%rowtype;
begin
open emp_cv for select * from emp where rownum<11;
loop
fetch emp_cv into emp_rec;
exit when emp_cv%notfound ;
dbms_output.put_Line('name = '||emp_rec.ename);
end loop;
close emp_cv;
end;
-----
declare
type empcurtype is ref cursor;
type namelist is table of emp.ename%type;
emp_cv empcurtype;
names namelist;
begin
open emp_cv for select ename from emp where rownum<11;
fetch emp_cv bulk collect into names;
close emp_cv;
for i in names.first .. names.last
loop
dbms_output.put_Line('name = '||names(i));
end loop;
end;
Oracle里的绑定变量
占位符
绑定变量的典型用法
SQL> var x number;
SQL> var 1 number;
SP2-0553: Illegal variable name "1".
SQL> var xyz number;
SQL> exec :x :=7369;
PL/SQL procedure successfully completed.
SQL> select ename from emp where empno=:x;
ENAME
--------------------
SMITH
SQL> select ename from emp where empno=:xyz;
ENAME
--------------------
SMITH
1 在plsql中select语句的绑定变量的典型用法
declare
vc_name varchar2(20);
begin
execute immediate 'select ename from emp where empno=:1' into vc_name using 7369;
DBMS_OUTPUT.PUT_LINE('name = '||vc_name);
end;
2 plsql中dml语句
declare
v_sql1 varchar2(4000);
v_sql2 varchar2(4000);
v_temp1 number;
v_temp2 number;
begin
v_sql1:='insert into emp(empno,ename) values(:1,:2)';
execute immediate v_sql1 using 8000,'hongquan';
v_temp1:=sql%rowcount;
v_sql2:='insert into emp(empno,ename) values(:1,:1)';
execute immediate v_sql2 using 8001,'hongquan2';
v_temp2:=sql%rowcount;
DBMS_OUTPUT.PUT_LINE(to_char(v_temp1+v_temp2));
end;