子程序简单问题

Question

函数是否可以return 表对象？想做个函数输出的是表，直接赋值程序中的dataset对象显示报表输入参数是时间范围（起始时间、终止时间）等

Answer 1

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as scott
 
SQL> set serveroutput on
SQL> 
SQL> create or replace function func_return_cursor
  2  return sys_refcursor
  3  as
  4  cv_now sys_refcursor;
  5  begin
  6   open cv_now for select empno from emp;
  7   return cv_now;
  8  end;
  9  /
 
Function created
 
SQL> 
SQL> DECLARE
  2    cv_1    SYS_REFCURSOR;
  3    v_empno emp.empno%TYPE;
  4  BEGIN
  5    SELECT func_return_cursor INTO cv_1  FROM dual;
  6    LOOP
  7      FETCH cv_1
  8        INTO v_empno;
  9      EXIT WHEN cv_1%NOTFOUND;
 10      dbms_output.put_line(v_empno);
 11    END LOOP;
 12  END;
 13  /
 
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
 
PL/SQL procedure successfully completed
 
SQL> 

Answer 2

入参及exception可以自己添加,主题大概是这样.
当然也可以返回table类型的数据.
那样要先定义好table type,然后把数据取到table type的变量里.
比这个要麻烦些.

Answer 3

额,上面的匿名块忘记close cursor了.

Answer 4

有米有不用游标的方法？直接table的？

Answer 5

还可以这样,麻烦了些.
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as scott
 
SQL> create or replace type type_obj_emp as object(empno number(20),ename varchar2(10));
  2  /
 
Type created
 
SQL> create or replace type type_t_emp as table of type_obj_emp;
  2  /
 
Type created
 
SQL> 
SQL> CREATE OR REPLACE FUNCTION func_return_cursor RETURN type_t_emp AS
  2    cv_now  SYS_REFCURSOR;
  3    v_t_emp type_t_emp;
  4    v_empno NUMBER(10);
  5    v_ename VARCHAR2(10);
  6    v_num   NUMBER;
  7  BEGIN
  8    v_num := 1;
  9    OPEN cv_now FOR
 10      SELECT empno, ename FROM emp;
 11    LOOP
 12      FETCH cv_now
 13        INTO v_empno, v_ename;
 14      EXIT WHEN cv_now%NOTFOUND;
 15      IF v_num = 1 THEN
 16        v_t_emp := type_t_emp(type_obj_emp(v_empno, v_ename));
 17      ELSE
 18        v_t_emp.EXTEND;
 19        v_t_emp(v_num) := type_obj_emp(v_empno, v_ename);
 20      END IF;
 21      v_num := v_num + 1;
 22    END LOOP;
 23    RETURN v_t_emp;
 24  END;
 25  /
 
Function created
 
SQL> select * from table(func_return_cursor);
 
                EMPNO ENAME
--------------------- ----------
                 7369 SMITH
                 7499 ALLEN
                 7521 WARD
                 7566 JONES
                 7654 MARTIN
                 7698 BLAKE
                 7782 CLARK
                 7788 SCOTT
                 7839 KING
                 7844 TURNER
                 7876 ADAMS
                 7900 JAMES
                 7902 FORD
                 7934 MILLER
 
14 rows selected
 
SQL> 

Answer 6

CREATE OR REPLACE FUNCTION func_return_cursor RETURN type_t_emp AS
  cv_now  SYS_REFCURSOR;
  v_t_emp type_t_emp;
  v_empno NUMBER(10);
  v_ename VARCHAR2(10);
  v_num   NUMBER;
BEGIN
  v_num := 1;
  OPEN cv_now FOR
    SELECT empno, ename FROM emp;
  LOOP
    FETCH cv_now
      INTO v_empno, v_ename;
    EXIT WHEN cv_now%NOTFOUND;
    IF v_num = 1 THEN
      v_t_emp := type_t_emp(type_obj_emp(v_empno, v_ename));
    ELSE
      v_t_emp.EXTEND;
      v_t_emp(v_num) := type_obj_emp(v_empno, v_ename);
    END IF;
    v_num := v_num + 1;
  END LOOP;
  close cv_now;
  RETURN v_t_emp;
END;又忘记关闭cursor了...

Answer 7

还可以简单些.
SQL> CREATE OR REPLACE FUNCTION func_return_cursor RETURN type_t_emp AS
  2    v_t_emp type_t_emp;
  3  BEGIN
  4    SELECT type_obj_emp(empno, ename) BULK COLLECT INTO v_t_emp FROM emp;
  5    RETURN v_t_emp;
  6  END;
  7  /
 
Function created
 
SQL> select * from table(func_return_cursor);
 
                EMPNO ENAME
--------------------- ----------
                 7369 SMITH
                 7499 ALLEN
                 7521 WARD
                 7566 JONES
                 7654 MARTIN
                 7698 BLAKE
                 7782 CLARK
                 7788 SCOTT
                 7839 KING
                 7844 TURNER
                 7876 ADAMS
                 7900 JAMES
                 7902 FORD
                 7934 MILLER
 
14 rows selected
 
SQL> 

Answer 8

还有个问题顺便一块问了我做了个视图定义如下：
CREATE OR REPLACE FORCE VIEW v_userlog (vid, a, b)
AS
   SELECT ROW_NUMBER () OVER (ORDER BY l.time desc) ,
            l.user_id, u.name
     FROM right_log l,
          right_user u,
            WHERE l.user_id = u.userid这样VID就可以作为序号显示了但是我使用select * from v_userlog where a ='admin';去刷选视图里的记录时vid是不连续的，有啥米办法使每次都重新生成新的VID作为序号啊？是否有分析函数可以做到？还是要其它方法？     

Answer 9

试试CREATE OR REPLACE FORCE VIEW v_userlog (vid, a, b)
AS
  SELECT ROW_NUMBER () OVER (ORDER BY l.time desc) ,
  l.user_id, u.name
  FROM right_log l,
  right_user u,
  WHERE l.user_id = u.userid
  order by l.time desc

Answer 10

问题是建立视图的时候是没有where条件的，where是调用视图时加上去作为条件的，后加上去的所以vid就是不连续的，我需要的效果是vid是从1开始递增的

Answer 11

实在不行只能在调用的时候加上去了
select ROW_NUMBER () OVER (ORDER BY 1) ,a,b where userid =1;  

Answer 12

1 plsql中有返回整个表
2 ROW_NUMBER() over(order by CMPLT_ID ) 就是没有0 是从1开始