cur带个参数什么意思? create or replace package T002 is type cur is ref cursor; procedure sss( Rst out cur ); end T002; create or replace package body T002 is begin procedure sss( Rst out cur ) is begin open Rst for select * from aaaaaa; end sss; end T002;
就像这个样子,是写在存储过程里面的 cursor cur(no VARCHAR2) is select * from dbcs.tab_pdtlists where order_no=no ; /*带参数的游标*/
在存储过程参数里定义成这样似乎没有什么意义啊type cur is ref cursor;是一个动态游标包括你要求的那种情况,你需要在存储过程传入参数里加入你的no VARCHAR2 ........ procedure sss( no in varchar2,Rst out cur ) is begin open cur for select * from dbcs.tab_pdtlists where order_no=no end;
cursor cur(no VARCHAR2) is select * from dbcs.tab_pdtlists where order_no=no ;
带参数的游标与存储过程和函数相似,可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下:cursor cursor_name[(parameter[,parameter],...)] is select_statement;定义参数的语法如下:parameter_name [in] data_type[{:=|default} value]与存储过程不同的是,游标只能接受传递的值,而不能返回值。参数只定义数据类型,没有大小。另外可以给参数设定一个缺省值,当没有参数值传递给游标时,就使用缺省值。游标中定义的参数只是一个占位符,在别处引用该参数不一定可靠。在打开游标时给参数赋值,语法如下:open cursor_name[value[,value]....];参数值可以是文字或变量。例:decalrecursor c_dept is select * from dept order by deptno; cursor c_emp (p_dept varachar2) is select ename,salary from emp where deptno=p_dept order by ename r_dept dept%rowtype; v_ename emp.ename%type; v_salary emp.salary%type; v_tot_salary emp.salary%type;beginopen c_dept; loop fetch c_dept into r_dept; exit when c_dept%notfound; dbms_output.put_line(''department:''|| r_dept.deptno||''-''||r_dept.dname); v_tot_salary:=0; open c_emp(r_dept.deptno); loop fetch c_emp into v_ename,v_salary; exit when c_emp%notfound; dbms_output.put_line(''name:''|| v_ename||'' salary:''||v_salary); v_tot_salary:=v_tot_salary+v_salary; end loop; close c_emp; dbms_output.put_line(''toltal salary for dept:''|| v_tot_salary); end loop; close c_dept; end;
create or replace package T002
is
type cur is ref cursor;
procedure sss( Rst out cur );
end T002;
create or replace package body T002
is
begin
procedure sss( Rst out cur )
is
begin
open Rst for select * from aaaaaa;
end sss;
end T002;
cursor cur(no VARCHAR2) is select * from dbcs.tab_pdtlists
where order_no=no ; /*带参数的游标*/
........
procedure sss( no in varchar2,Rst out cur )
is
begin
open cur for select * from dbcs.tab_pdtlists
where order_no=no
end;
where order_no=no ;
cursor c_emp (p_dept varachar2) is
select ename,salary
from emp
where deptno=p_dept
order by ename
r_dept dept%rowtype;
v_ename emp.ename%type;
v_salary emp.salary%type;
v_tot_salary emp.salary%type;beginopen c_dept;
loop
fetch c_dept into r_dept;
exit when c_dept%notfound;
dbms_output.put_line(''department:''|| r_dept.deptno||''-''||r_dept.dname);
v_tot_salary:=0;
open c_emp(r_dept.deptno);
loop
fetch c_emp into v_ename,v_salary;
exit when c_emp%notfound;
dbms_output.put_line(''name:''|| v_ename||'' salary:''||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
end loop;
close c_emp;
dbms_output.put_line(''toltal salary for dept:''|| v_tot_salary);
end loop;
close c_dept;
end;