想象SQLserver那样,直接用select语句就能把select出来的字段下的所有记录给select 出来,同样的语句用在Oracle上说缺少into语句,随便定义了一两个变量给它into也是不能实现,请问有没有这种经历的老大们,给解决方法!!!
感谢万分!!!
-----------------------
--包头
create or replace package dinya_pkg_test
as
? type myCursor is ref cursor;
? function get(p_id number) return myCursor;
end dinya_pkg_test;
--包体
create or replace package body dinya_pkg_test
as
? --*********************************************************************************
? --输入ID 返回记录集的函数
? function get(p_id number) return myCursor is
???? rc myCursor;
???? strsql varchar2(200);
? begin
???? if p_id=0 then
??????? open rc for select a.user_name from fnd_user a ;??????
???? else
??????? strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
??????? open rc for strsql;
???? end if;
???? return rc;?
???? end get;
end dinya_pkg_test;
--调用:
set serverout on
declare
? w_rc dinya_pkg_test.myCursor;
? w_name varchar2(100);
begin
? w_rc:=dinya_pkg_test.get(0);? --这里输入要的参数
? loop
? fetch w_rc into w_name;
??????? exit when w_rc%notfound;
??????? dbms_output.put_line(w_name);
? end loop;
end;
/
--------------------
一般在匿名块或包(函数,存储过程)里面必须加上into
eg:
conn scott/tiger@你的实例名
set serveroutput on
declare
??? v_emp emp%rowtype;
begin
??? select * into v_emp from emp where empno=7499;
??? dbms_output.put_line('Salary?? commision');
??? dbms_output.put_line(v_emp.sal || '??????? ' || v_emp.comm);
end;
/
?