oracle Oracle中的多选语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27854732/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple select statement in Oracle
提问by Nitin Singh
I want to write a simple stored procedure in Oracle with multiple select statements in that. I can do the same in the SQL server like
我想在 Oracle 中编写一个简单的存储过程，其中包含多个选择语句。我可以在 SQL 服务器中做同样的事情
Create proc spc_name
as
begin
select * from tab1
Select * from tab2
......
end
I want to know how to write the same equivalent stored procedure in Oracle as I have done in SQL Server
我想知道如何在 Oracle 中编写与我在 SQL Server 中所做的相同的等效存储过程
回答by Lalit Kumar B
select * from tab1
Select * from tab2
从tab1中选择*
从 tab2 中选择 *
You cannot simply have a selectquery in PL/SQL. It will throw PLS-00428: an INTO clause is expected in this SELECT statementerror.
您不能简单地select在PL/SQL. 它会抛出PLS-00428: an INTO clause is expected in this SELECT statement错误。
Either you use a SELECT..INTOclause or use CURSORS. The SELECT INTO clause is used to retrieve one row or set of columns. It is used to store the returned data into predefined variables. For multiple SELECTs you can have multiple SELECT INTOclause, each clause would store the result of respective SQL.
要么使用SELECT..INTO子句，要么使用CURSORS. SELECT INTO 子句用于检索一行或一组列。它用于将返回的数据存储到预定义的变量中。对于多个 SELECT 可以有多个SELECT INTO子句，每个子句将存储各自 SQL 的结果。
For example,
例如，
SQL> DECLARE
2 v_ename emp.ename%TYPE;
3 v_empno emp.empno%TYPE;
4 BEGIN
5 SELECT ename INTO v_ename FROM emp WHERE empno = 7369;
6 SELECT empno INTO v_empno FROM emp WHERE ename = 'SCOTT';
7 dbms_output.put_line('SELECT statement 1 returns name =: '||v_ename);
8 dbms_output.put_line('SELECT statement 2 returns emp number =: '||v_empno)
9 END;
10 /
SELECT statement 1 returns name =: SMITH
SELECT statement 2 returns emp number =: 7788
PL/SQL procedure successfully completed.
SQL>
To return multiple rows, you could use CURSOR. In your case, with multiple statements, you can have two REFCURSOR.
要返回多行，您可以使用CURSOR. 在您的情况下，使用多个语句，您可以有两个REFCURSOR.
For example,
例如，
SQL> variable v_ref1 refcursor
SQL> variable v_ref2 refcursor
SQL>
SQL> DECLARE
2 v_ref1 sys_refcursor;
3 v_ref2 sys_refcursor;
4 BEGIN
5 OPEN :v_ref1 FOR SELECT empno, ename
6 FROM emp ORDER BY empno
7 FETCH FIRST 5 ROWS ONLY;
8 OPEN :v_ref2 FOR SELECT empno, ename
9 FROM emp ORDER BY empno DESC
10 FETCH FIRST 5 ROWS ONLY;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> print v_ref1
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
SQL> print v_ref2
EMPNO ENAME
---------- ----------
7934 MILLER
7902 FORD
7900 JAMES
7876 ADAMS
7844 TURNER
SQL>
If you want to combine the resultset of your multiple SELECTstatements, you could use UNIONoperator and have it in single REFCURSOR. Given that the column data types match and are in proper order. It is just an example,
如果要组合多个SELECT语句的结果集，可以使用UNIONoperator 并将其放在 single 中REFCURSOR。鉴于列数据类型匹配且顺序正确。这只是一个例子，
SQL> variable v_ref refcursor
SQL>
SQL> DECLARE
2 v_ref sys_refcursor;
3 BEGIN
4 OPEN :v_ref FOR
5 SELECT empno, DEPTNO FROM emp WHERE ROWNUM <=5
6 UNION ALL
7 SELECT empno, DEPTNO FROM EMP WHERE ROWNUM <=5;
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> print v_ref
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7369 20
7499 30
7521 30
7566 20
7654 30
10 rows selected.
SQL>
回答by Shirishkumar Bari
Alternatively you can use cursors for this purpose. Please check thisor thisfor sample Example.
You can declare multiple Cursors in a Stored procedures.
您可以在存储过程中声明多个游标。
If you want to fetch result from Oracle Stroed PRoc to Java probably you can refer this URLor this Stack Overflow answer
如果您想从 Oracle Stroed PROc 获取结果到 Java，您可能可以参考此 URL或此Stack Overflow 答案
