Oracle 简单存储过程! 就一条select语句！

[hanqing_liu]

CREATE OR REPLACE PROCEDURE a_test

AS
strsql VARCHAR2(500);
BEGIN
strsql:='SELECT * FROM REFERENCE_TABLE ';
//怎么写
END;

如果再加一个输出参数（返回总条数） 又该怎么写！

[qq2013]

p_RecordCount out int,--返回总记录数

as
v_SqlCount varchar2(3000);


v_SqlSelect := 'SELECT Count(*) FROM REFERENCE_TABLE ';
execute immediate v_SqlCount into v_count;
p_RecordCount := v_count;

[hanqing_liu]

这个问题 是 有些2
也不至于没人回个话啊


谢谢！

[wuyisky84]

不需要用动态sql
p_RecordCount out int,--返回总记录数

as
v_SqlCount varchar2(3000);

SELECT Count(*) into v_count FROM REFERENCE_TABLE;
p_RecordCount := v_count;

[minitoy]

CREATE OR REPLACE PROCEDURE a_test(o_rn out number)

AS
strsql VARCHAR2(500);
BEGIN
  SELECT count(*) into o_rn FROM REFERENCE_TABLE ;

END;

直接select也可以.像1楼那样使用动态sql也可以.

[hanqing_liu]

CREATE OR REPLACE PROCEDURE a_test

AS
strsql VARCHAR2(500);
BEGIN
strsql:='SELECT * FROM REFERENCE_TABLE ';
//怎么写
END;
我怎么 返回查询数据

[wuyisky84]

[Quote=引用 5 楼 hanqing_liu 的回复:]
CREATE OR REPLACE PROCEDURE a_test

AS
strsql VARCHAR2(500);
BEGIN
strsql:='SELECT * FROM REFERENCE_TABLE ';
//怎么写
END;
我怎么 返回查询数据
[/Quote]
-------存储过程中不能直接写 SELECT * FROM REFERENCE_TABLE

[不动如岳]

加参数啊

[minitoy]

CREATE OR REPLACE PROCEDURE a_test(o_rn out sys_refcursor)

AS
BEGIN

  open o_rn for  'SELECT * FROM emp' ;

END;

[minitoy]

SQL> var o_cur ref cursor

SQL> exec a_test(:o_cur);

PL/SQL procedure successfully completed.

SQL> print :o_cur

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           1000 17-12月-80          800.1
        20

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
        30

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
        10

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000
        20

      7839 KING       PRESIDENT            17-11月-81           5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
        30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100
        20

      7900 JAMES      CLERK           7698 03-12月-81            950
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-12月-81           3000
        20

      7934 MILLER     CLERK           7782 23-1月 -82           1300
        10


14 rows selected.

SQL>

[WXHDSWH]

studying and marking

[hanqing_liu]

3 Q

[zhone]

像minitoy学习，mark一下下.