create or replace function fr_other_emp (v_empno emp.empno%type) return sys_refcursor is cur sys_refcursor; v_hire_dt emp.hirdate%type; v_sql varchar2(100); begin select hirdate into v_hire_dt from emp where empno = v_empno; v_sql := 'select empno,empname,hirdate from emp where hirdate = to_date('||v_hire_dt||',''yyyy-mm-dd'')'; open cur for v_sql; return cur; end fr_other_emp;外边需要用一个cursor接收或者create or replace procedure prc_other_emp (v_empno in out emp.empno%type, v_empname out emp.empname%type, v_hirdate out emp.hirdate%type) is cur sys_refcursor; v_hire_dt emp.hirdate%type; v_sql varchar2(100); begin select hirdate into v_hire_dt from emp where empno = v_empno;
select empno,empname,hirdate into v_empno ,v_empname ,v_hirdate from emp where hirdate = v_hire_dt;
end prc_other_emp ; 传3个变量进去。
create type emptype as object (empno number(4), empname varchar2(10), hirdate date); /create type emptypes as table of emptype; /create or replace function sameempno(p_empno in SCOTT.EMP.EMPNO%type) return emptypes as v_resultset emptypes := emptypes(); cursor cur_sameemp(c_no number) is select empno,ename,trunc(hiredate) as hd from scott.emp where trunc(hiredate) = (select trunc(hiredate) from scott.emp where empno=p_empno); begin for v_rec in cur_sameemp(p_empno) loop v_resultset.extend; v_resultset(v_resultset.last) := emptype(v_rec.empno, v_rec.ename, v_rec.hd); end loop;
return v_resultset; end; /select empno,ename,hiredate from scott.emp; EMPNO ENAME HIREDATE ---------- --------------- --------- 7369 SMITH 17-DEC-80 7499 ALLEN 20-FEB-81 7521 WARD 22-FEB-81 7566 JONES 02-APR-81 7654 MARTIN 28-SEP-81 7698 BLAKE 01-MAY-81 7782 CLARK 09-JUN-81 7788 SCOTT 19-APR-87 7839 KING 17-NOV-81 7844 TURNER 08-SEP-81 7876 ADAMS 23-MAY-87 7900 JAMES 03-DEC-81 7902 FORD 03-DEC-81 7934 MILLER 23-JAN-82select sameempno(7900) from dual;SAMEEMPNO(7900)(EMPNO,EMPNAME,HIRDATE)
nGX20080110你写不错,经过测试了吧?前面这些我不知道是什么意思?望指点。 create type emptype as object (empno number(4), empname varchar2(10), hirdate date); /create type emptypes as table of emptype; /
create type emptype as object 创建对象来存放3个字段 create type emptypes as table of emptype 创建内嵌表来存放emptype对象这样会更容易理解:select * from table(sameempno(7900)); EMPNO EMPNAME HIRDATE ---------- --------------- --------- 7900 JAMES 03-DEC-81 7902 FORD 03-DEC-81
select * from table(sameempno(7900));这是什么语法呢?sameempno(7900)是上面写的带了参数的函数,正常测试的应该是:select sameempno(7900) from dual;这样写还有在创建函数的时候,要在函数写这些吗?: create type emptype as object (empno number(4), empname varchar2(10), hirdate date); / create type emptypes as table of emptype; /
没有必要写个函数吧,程序里面直接写sql数据,读取返回的结果
select * from table(sameempno(7900)); 返回的是一个结果集或者表 select sameempno(7900) from dual 返回的是一个字符串等等
那在pl/sql里下面这些东西写和函数sameempno写在一起吗?如果不是,写在哪里? create type emptype as object (empno number(4), empname varchar2(10), hirdate date); / create type emptypes as table of emptype; /
return sys_refcursor
is
cur sys_refcursor;
v_hire_dt emp.hirdate%type;
v_sql varchar2(100);
begin
select hirdate into v_hire_dt from emp where empno = v_empno;
v_sql := 'select empno,empname,hirdate from emp where hirdate = to_date('||v_hire_dt||',''yyyy-mm-dd'')';
open cur for v_sql;
return cur;
end fr_other_emp;外边需要用一个cursor接收或者create or replace procedure prc_other_emp (v_empno in out emp.empno%type,
v_empname out emp.empname%type,
v_hirdate out emp.hirdate%type)
is
cur sys_refcursor;
v_hire_dt emp.hirdate%type;
v_sql varchar2(100);
begin select hirdate
into v_hire_dt
from emp
where empno = v_empno;
select empno,empname,hirdate
into v_empno ,v_empname ,v_hirdate
from emp
where hirdate = v_hire_dt;
end prc_other_emp ; 传3个变量进去。
create type emptype as object
(empno number(4), empname varchar2(10), hirdate date);
/create type emptypes as table of emptype;
/create or replace function sameempno(p_empno in SCOTT.EMP.EMPNO%type)
return emptypes as
v_resultset emptypes := emptypes();
cursor cur_sameemp(c_no number) is
select empno,ename,trunc(hiredate) as hd from scott.emp where trunc(hiredate) = (select trunc(hiredate) from scott.emp where empno=p_empno);
begin
for v_rec in cur_sameemp(p_empno) loop
v_resultset.extend;
v_resultset(v_resultset.last) := emptype(v_rec.empno, v_rec.ename, v_rec.hd);
end loop;
return v_resultset;
end;
/select empno,ename,hiredate from scott.emp; EMPNO ENAME HIREDATE
---------- --------------- ---------
7369 SMITH 17-DEC-80
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7566 JONES 02-APR-81
7654 MARTIN 28-SEP-81
7698 BLAKE 01-MAY-81
7782 CLARK 09-JUN-81
7788 SCOTT 19-APR-87
7839 KING 17-NOV-81
7844 TURNER 08-SEP-81
7876 ADAMS 23-MAY-87
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
7934 MILLER 23-JAN-82select sameempno(7900) from dual;SAMEEMPNO(7900)(EMPNO,EMPNAME,HIRDATE)
----------------------------------------------------------------------
EMPTYPES(EMPTYPE(7900,JAMES,12/03/1981),EMPTYPE(7902,FORD,12/03/1981))
create type emptype as object
(empno number(4), empname varchar2(10), hirdate date);
/create type emptypes as table of emptype;
/
create type emptypes as table of emptype 创建内嵌表来存放emptype对象这样会更容易理解:select * from table(sameempno(7900)); EMPNO EMPNAME HIRDATE
---------- --------------- ---------
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
create type emptype as object
(empno number(4), empname varchar2(10), hirdate date);
/ create type emptypes as table of emptype;
/
select * from table(sameempno(7900)); 返回的是一个结果集或者表
select sameempno(7900) from dual 返回的是一个字符串等等
create type emptype as object
(empno number(4), empname varchar2(10), hirdate date);
/ create type emptypes as table of emptype;
/