有关ORACLE根据动态表名查询返回结果集

有一个orcle过程
PROCEDURE PROC_AVG_F(cur_OUT OUT MYCURSOR,TABLE_NAME VARCHAR2,START_TIME VARCHAR2,END_TIME VARCHAR2 );
过程的目的是想查询出 TABLE_NAME 表中开始时间和结束时间段的所有信息
添加到结果集游标cur_OUT里面，我想问你的就是此查询语句该怎么和输入参数进行结合
下面是我已经完成的部分过程：
CREATE OR REPLACE PACKAGE SCOTT.PKG_AVG_F IS
  TYPE MYCURSOR IS REF CURSOR;
  PROCEDURE PROC_AVG_F(cur_OUT OUT MYCURSOR,TABLE_NAME VARCHAR2,START_TIME VARCHAR2,END_TIME VARCHAR2 );
END;
/

CREATE OR REPLACE PACKAGE BODY SCOTT.PKG_AVG_F
IS
PROCEDURE PROC_AVG_F(cur_OUT OUT MYCURSOR,TABLE_NAME VARCHAR2,START_TIME VARCHAR2,END_TIME VARCHAR2 ) AS
BEGIN
OPEN cur_OUT FOR
SELECT TO_CHAR(TRUNC(TO_DATE(D_TIME,'YYYY-MM-DD HH24:MI:SS'),'MI'),'YYYY-MM-DD HH24:MI:SS') D_TIME,
                TO_CHAR(AVG(F_COMPONENTCODE),'fm9999999990.00') F_COMPONENTCODE
                FROM || TABLE_NAME ||
                WHERE TO_CHAR(TRUNC(TO_DATE(D_TIME,'YYYY-MM-DD HH24:MI:SS'),'MI'),'YYYY-MM-DD HH24:MI:SS')
                BETWEEN '2010-04-04 02:01' AND '2010-04-04 02:06'
                GROUP BY TO_CHAR(TRUNC(TO_DATE(D_TIME,'YYYY-MM-DD HH24:MI:SS'),'MI'),'YYYY-MM-DD HH24:MI:SS')
                ORDER BY TO_CHAR(TRUNC(TO_DATE(D_TIME,'YYYY-MM-DD HH24:MI:SS'),'MI'),'YYYY-MM-DD HH24:MI:SS') ;
END;
END;
/
出现问题：
SQL> CREATE OR REPLACE PACKAGE BODY SCOTT.PKG_AVG_F
  2     IS
  3     PROCEDURE PROC_AVG_F(cur_OUT OUT MYCURSOR,TABLE_NAME VARCHAR2,START_TIME
VARCHAR2,END_TIME VARCHAR2 ) AS
  4     BEGIN
  5             OPEN cur_OUT FOR
  6             SELECT TO_CHAR(TRUNC(TO_DATE(D_TIME,'YYYY-MM-DD HH24:MI:SS'),'MI
'),'YYYY-MM-DD HH24:MI:SS') D_TIME,
  7                  TO_CHAR(AVG(F_COMPONENTCODE),'fm9999999990.00') F_COMPONENT
CODE
  8                  FROM || TABLE_NAME ||
  9                  WHERE TO_CHAR(TRUNC(TO_DATE(D_TIME,'YYYY-MM-DD HH24:MI:SS')
,'MI'),'YYYY-MM-DD HH24:MI:SS')
10                  BETWEEN '2010-04-04 02:01' AND '2010-04-04 02:06'
11                  GROUP BY TO_CHAR(TRUNC(TO_DATE(D_TIME,'YYYY-MM-DD HH24:MI:S
S'),'MI'),'YYYY-MM-DD HH24:MI:SS')
12                  ORDER BY TO_CHAR(TRUNC(TO_DATE(D_TIME,'YYYY-MM-DD HH24:MI:S
S'),'MI'),'YYYY-MM-DD HH24:MI:SS') ;
13     END;
14  END;
15  /

警告: 创建的包体带有编译错误。

SQL> show error;
PACKAGE BODY SCOTT.PKG_AVG_F 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3      PL/SQL: SQL Statement ignored
8/22     PL/SQL: ORA-00903: 表名无效
SQL>

9 个解决方案

#1

||两连接的是字符串
CREATE OR REPLACE PACKAGE BODY SCOTT.PKG_AVG_F IS
  PROCEDURE PROC_AVG_F(cur_OUT    OUT MYCURSOR,
                       TABLE_NAME VARCHAR2,
                       START_TIME VARCHAR2,
                       END_TIME   VARCHAR2) AS
  BEGIN
    OPEN cur_OUT FOR
      SELECT 'TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'') D_TIME,
              TO_CHAR(AVG(F_COMPONENTCODE),''fm9999999990.00'') F_COMPONENTCODE
              FROM ' || TABLE_NAME ||'WHERE TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')
              BETWEEN ''2010-04-04 02:01'' AND ''2010-04-04 02:06''
              GROUP BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')
              ORDER BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')';
  END;
END;

#2

如果字符串里有'号，需要使用两个'来转义
如:select 'It''s a pen' from dual;

CREATE OR REPLACE PACKAGE BODY SCOTT.PKG_AVG_F IS
  PROCEDURE PROC_AVG_F(cur_OUT    OUT MYCURSOR,
                       TABLE_NAME VARCHAR2,
                       START_TIME VARCHAR2,
                       END_TIME   VARCHAR2) AS
  BEGIN
    OPEN cur_OUT FOR
      SELECT 'TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'') D_TIME,
              TO_CHAR(AVG(F_COMPONENTCODE),''fm9999999990.00'') F_COMPONENTCODE  FROM ' || TABLE_NAME 
              ||' WHERE TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')  
              BETWEEN ''2010-04-04 02:01'' AND ''2010-04-04 02:06''  
              GROUP BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')  
              ORDER BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')';
  END;
END;

#3

警告: 创建的包体带有编译错误。

SQL> show error;
PACKAGE BODY SCOTT.PKG_AVG_F 出现错误:

LINE/COL ERROR
-------- -------------------------------------------------
5/7 PL/SQL: SQL Statement ignored
10/115 PL/SQL: ORA-00923: 未找到要求的 FROM 关键字
刚才老师给的答案很好，到学生这出现了以上错误，还请老师看看

#4

我根据刚才提示的错误信息，发现from也是关键字，所以把from关键像select一样提了出来：语句如下：
CREATE OR REPLACE PACKAGE BODY SCOTT.PKG_AVG_F
IS
PROCEDURE PROC_AVG_F(cur_OUT OUT MYCURSOR,TABLE_NAME VARCHAR2,START_TIME VARCHAR2,END_TIME VARCHAR2 ) AS
BEGIN
OPEN cur_OUT FOR
SELECT 'TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'') D_TIME,
                TO_CHAR(AVG(F_COMPONENTCODE),''fm9999999990.00'') F_COMPONENTCODE
                ' FROM || TABLE_NAME ||
                ' WHERE TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')
                BETWEEN ''2010-04-04 02:01'' AND ''2010-04-04 02:06''
                GROUP BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')
                ORDER BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')' ;
END;
END;
/
可是出现了和之前一样的问题：
SQL> CREATE OR REPLACE PACKAGE BODY SCOTT.PKG_AVG_F
  2     IS
  3     PROCEDURE PROC_AVG_F(cur_OUT OUT MYCURSOR,TABLE_NAME VARCHAR2,START_TIME
VARCHAR2,END_TIME VARCHAR2 ) AS
  4     BEGIN
  5             OPEN cur_OUT FOR
  6             SELECT 'TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),
''MI''),''YYYY-MM-DD HH24:MI:SS'') D_TIME,
  7                  TO_CHAR(AVG(F_COMPONENTCODE),''fm9999999990.00'') F_COMPONE
NTCODE
  8                  ' FROM || TABLE_NAME ||
  9                  ' WHERE TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:S
S''),''MI''),''YYYY-MM-DD HH24:MI:SS'')
10                  BETWEEN ''2010-04-04 02:01'' AND ''2010-04-04 02:06''
11                  GROUP BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:
SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')
12                  ORDER BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:
SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')' ;
13     END;
14  END;
15  /

警告: 创建的包体带有编译错误。

SQL> show error;
PACKAGE BODY SCOTT.PKG_AVG_F 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3      PL/SQL: SQL Statement ignored
8/24     PL/SQL: ORA-00903: 表名无效
SQL>

#5

8 ' FROM || TABLE_NAME ||

8 ' FROM ' || TABLE_NAME ||

#6

8 ' FROM ' || TABLE_NAME || FROM加上'之后会提示找不到FROM关键字

#7

OPEN cur_OUT FOR
'
SELECT TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'') D_TIME,
  TO_CHAR(AVG(F_COMPONENTCODE),''fm9999999990.00'') F_COMPONENTCODE
  FROM ' || TABLE_NAME || '
  WHERE TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')
  BETWEEN ''2010-04-04 02:01'' AND ''2010-04-04 02:06''
  GROUP BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')
  ORDER BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')
';

#8

谢谢你，问题解决了。

#9

经过大家的帮助，问题最终解决了，以下是测试通过并且程序调用执行返回结果ok：

		try{
			XmlReader xml = new XmlReader();
			con = db.getConnection();
			CallableStatement proc = con.prepareCall("call SCOTT.PKG_AVG_F.PROC_AVG_F(?,?,?,?)");

			proc.registerOutParameter(1, OracleTypes.CURSOR);
			proc.setString(2, "ZSZ_OFM01_201004");
			proc.setString(3, "2010-04-04 02:01");
			proc.setString(4, "2010-04-04 02:03");
			proc.execute();
			ResultSet rs = (ResultSet)proc.getObject(1);
			int count = 0;
			while(rs.next()){
				System.out.println(count++ +":"+rs.getString(1));
			}
		}catch(Exception e)
		{System.out.println(e.getMessage());}

===============================下面是ORACLE过程=====================================

CREATE OR REPLACE PACKAGE SCOTT.PKG_AVG_F IS
  TYPE MYCURSOR IS REF CURSOR;
  PROCEDURE PROC_AVG_F(cur_OUT OUT MYCURSOR,TABLE_NAME VARCHAR2,START_TIME VARCHAR2,END_TIME VARCHAR2 );  
END;
/

CREATE OR REPLACE PACKAGE BODY SCOTT.PKG_AVG_F
	IS
	PROCEDURE PROC_AVG_F(cur_OUT OUT MYCURSOR,TABLE_NAME VARCHAR2,START_TIME VARCHAR2,END_TIME VARCHAR2 ) AS
	BEGIN
OPEN cur_OUT FOR   
'
  SELECT TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'') D_TIME,
  TO_CHAR(AVG(F_COMPONENTCODE),''fm9999999990.00'') F_COMPONENTCODE   
  FROM ' || TABLE_NAME || '  
  WHERE TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')   
  BETWEEN ' || '''' || START_TIME || '''' || ' AND ' || '''' ||  END_TIME || '''' ||
  '   
  GROUP BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')   
  ORDER BY TO_CHAR(TRUNC(TO_DATE(D_TIME,''YYYY-MM-DD HH24:MI:SS''),''MI''),''YYYY-MM-DD HH24:MI:SS'')
';
	END;
END;
/

#1

#2