用于从Oracle中的表中选择所有行的存储过程

Please Note: There are several post regarding this, but I am not getting those due to some key words and why used? How to create a simple stored procedure in Oracle which select all rows from table. I exactly want to create same as below script written in SQL. Also How to execute the procedure in Oracle and place in ASP.NET OracleCommand object.

请注意:关于这一点有几个帖子,但由于一些关键词并且为什么使用,我没有得到那些?如何在Oracle中创建一个简单的存储过程,从表中选择所有行。我想要创建与SQL下面编写的脚本相同的内容。另外如何在Oracle中执行该过程并放在ASP.NET OracleCommand对象中。

How to re-write this SQL Stored Procedure in Oracle

如何在Oracle中重写此SQL存储过程

CREATE PROCEDURE spGetData
AS
Begin
SELECT * from My_Table;
End

Execute is sql

执行是sql

execute spGetData

3 个解决方案

#1

Don't have much reputation so instead of comment adding answer.

没有太多的声誉,所以而不是评论添加答案。

To Write Store Procedure in Oracle with select all rows you need to include refcursor,as cursor it will work as pointer and point to the oracle result sets one by one and give result.

要在Oracle中编写存储过程,选择所有需要包含refcursor的行,作为游标,它将作为指针,逐个指向oracle结果集并给出结果。

Store Procedure For Oracle

适用于Oracle的存储过程

CREATE OR REPLACE PROCEDURE spGetData(cursorParam OUT SYS_REFCURSOR)
 IS
  BEGIN
   OPEN cursorParam FOR
    SELECT * from My_Table;
END ;

And to execute it oracle you need

并执行它需要的oracle

   var c refcursor;
    execute spGetData(:c)
   print c;

Don't have much knowledge about ASP.NET but may be yo need to add cursor parameter

没有太多关于ASP.NET的知识,但可能需要添加游标参数

  OracleCommand.Parameters.Add("cursorParam ", OracleType.Cursor).Direction =
ParameterDirection.Output;

Thanks.

#2

Oracle 12c has a new feature Implicit Statement Results that only requires a few changes to the procedure:

Oracle 12c有一个新功能隐式语句结果,只需要对该过程进行一些更改:

SQL> create table My_Table as select 1 a from dual;

Table created.

SQL> CREATE PROCEDURE spGetData
  2  AS
  3     c1 sys_refcursor;
  4  Begin
  5     open c1 for
  6     SELECT * from My_Table;
  7
  8     dbms_sql.return_result(c1);
  9  End;
 10  /

Procedure created.

SQL> execute spGetData

PL/SQL procedure successfully completed.

ResultSet #1

         A
----------
         1

SQL>

#3

you must want to declare some variable according to your appropriate columns, declare only interested columns you want

你必须根据适当的列声明一些变量,只声明你想要的感兴趣的列

CREATE PROCEDURE spGetData
AS
v_emp_id number(20);
v_ename varchar2(20);
Begin
SELECT emp_id,ename into v_emp_id,v_ename from My_Table;
End;

I think that your error must be expecting into statement ? so try this one

我认为你的错误一定是期待声明?试试这个吧

DECLARE
TYPE EMP_USER_TYPE IS RECORD (
 EMP_ID        VARCHAR2(10),
emp_name        VARCHAR2(10)
);
TYPE emp_user_tab IS TABLE OF emp_user_type;
emp_user_rec emp_user_tab;
BEGIN
SELECT emp_id,emp_name
BULK COLLECT INTO emp_user_rec
FROM test_employee;
FOR i IN emp_user_rec.FIRST .. emp_user_rec.LAST LOOP
dbms_output.put_line( emp_user_rec(i).emp_id || ', ' || 
emp_user_rec(i).emp_name );
END LOOP;
END;

#1