1 2 Previous Next 18 Replies Latest reply: Oct 8, 2009 5:04 AM by Dom Brooks RSS

    Multiple select statements in PL/SQL

    720040
      Hi All

      I am new to PL/SQL and my experience is in writing TSQL. There we can write a SQL statement like this to return 3 result set

      SELECT empname FROM Employee
      SELECT authname FROM Author
      SELECT athname FROM sport

      how can we write the same 3 statements in PL/SQL and attain the 3 resultsets.
      I tried to implement the same using PL/SQL anonymous blocks. But it didn't worked.


      DECLARE
      P_RECORDSET OUT SYS_REFCURSOR
      BEGIN
      OPEN P_RECORDSET FOR
      SELECT empname FROM Employee;
      SELECT authname FROM Author;
      SELECT athname FROM sport;
      END;

      can anybody show how it can be done.

      Thanks in advance
      George

      Edited by: user6290570 on Sep 16, 2009 11:23 PM
        • 1. Re: Multiple select statements in PL/SQL
          ravikumar.sv
          SELECT empname FROM Employee union
          SELECT authname FROM Author union 
          SELECT athname FROM sport 
          Are you asking for finding union or union all of the data...??


          Ravi Kumar
          • 2. Re: Multiple select statements in PL/SQL
            720040
            No i just want to select 3 result sets from 3 select statements, so that it is helpful to compare the resultsets.
            This is mostly useful when we are trying to write JOIN select statements from multiple tables.
            It can be done with SQL server query analyser, just write 3 independent select statements
            then run. UNION leads to return as a single resultset.

            Edited by: george2009 on Sep 18, 2009 2:01 AM
            • 3. Re: Multiple select statements in PL/SQL
              465815
              you need 3 cursors:
              DECLARE
              P_RECORDSET_1  SYS_REFCURSOR;
              P_RECORDSET_2  SYS_REFCURSOR;
              P_RECORDSET_3  SYS_REFCURSOR;
              BEGIN
              OPEN P_RECORDSET_1 FOR
              SELECT empname FROM Employee;
              OPEN P_RECORDSET_2 FOR
              SELECT authname FROM Author;
              OPEN P_RECORDSET_3 FOR
              SELECT athname FROM sport;
              END;
              untested!!
              Amiel
              • 4. Re: Multiple select statements in PL/SQL
                ravikumar.sv
                george2009 wrote:
                No i just want to select 3 result sets from 3 select statements, so that it is helpful to compare the resultsets.
                This is mostly useful when we are trying to write JOIN select statements from multiple tables.
                It can be done with SQL server query analyser, just write 3 independent select statements
                then run. UNION leads to return as a single resultset.

                Edited by: george2009 on Sep 18, 2009 2:01 AM
                where do you want to have or see the multiple query results. Inside the pl/sql program or just from editor to compare results??

                Ravi Kumar

                Edited by: ravikumar.sv on Sep 18, 2009 2:52 PM
                • 5. Re: Multiple select statements in PL/SQL
                  Billy~Verreynne
                  Amiel D. wrote:
                  you need 3 cursors:
                  Yes.. but why ref cursors? A ref cursor's primary purpose is to be given to an external client (like Java/VB/.Net/etc). It is very seldom that a ref cursor is needed specifically for PL processing.

                  PL code most times will use implicit or explicit cursors.

                  Also, the whole idea of opening a cursor for an entire table as the OP showed, without using any filtering is utterly flawed 99.9% of the time.

                  The code is going to fetch a row at a time and the do "+something+" to that row, before fetching the next row.. until all the rows in the table have been fetched.

                  That "+something+" can very likely be done using SQL. Be that filtering, ordering, data transformation, joining, inserting, updating.. whatever.

                  Maximize SQL. Minimize PL/Java/.Net/etc.+
                  • 6. Re: Multiple select statements in PL/SQL
                    _Karthick_
                    What client are you using? What Query Analyzer does SQL Developer or Toad does not do.

                    If you are using SQL Developer better to open three work sheet and run the query independently.
                    • 7. Re: Multiple select statements in PL/SQL
                      Billy~Verreynne
                      george2009 wrote:
                      No i just want to select 3 result sets from 3 select statements, so that it is helpful to compare the resultsets.
                      Compare? How? This is done using the SQL language. Not PL/SQL. Not Java. Not VB. Not anything else.

                      You would use these other language for flow control and certain forms of conditional logic - but the actual comparison of data sets is done in SQL.

                      Of course, that is if you do want to do it the most optimal way, that will perform well, and scale well.

                      SQL is not an I/O API layer - to be used to read() a record and write() a record as if the RDBMS is an ISAM file. That form of row-by-row and slow-by-slow processing dates back to the 80's when we used Cobol.. (or at least for those old farts like me that can actually remember coding in Cobol in the 80's ;-) ).

                      You want to design and code database applications that are fast, robust, and can scale? Then learn how to use SQL correctly.
                      • 8. Re: Multiple select statements in PL/SQL
                        _Karthick_
                        I think you overlooked the OP's statement. By "Compare the resultsets" I gues he means comparing visually (with his eye ;) ) He want to see the table data with his eye and see how to join them.

                        I guess he does not have a proper design specification with him which gives the relation between tables :-)
                        • 9. Re: Multiple select statements in PL/SQL
                          ravikumar.sv
                          Actually
                          In Query Analyzer..

                          If you write
                          select * from table1
                          select * from table2

                          and now execute..
                          It gives two sets of data..in two visually distinguishable tables

                          May be this is want the OP wants..

                          @OP
                          Sql developer or sql plus doesn't support executing multiple statements in a single go..

                          Ravi Kumar
                          • 10. Re: Multiple select statements in PL/SQL
                            _Karthick_
                            Yes i know and thats why i said this
                            What Query Analyzer does SQL Developer or Toad does not do.
                            and this
                            If you are using SQL Developer better to open three work sheet and run the query independently.
                            • 11. Re: Multiple select statements in PL/SQL
                              ravikumar.sv
                              I miscued!!
                              Apologize for that..
                              ;-)

                              Ravi Kumar
                              • 12. Re: Multiple select statements in PL/SQL
                                Billy~Verreynne
                                Karthick_Arp wrote:
                                I think you overlooked the OP's statement. By "Compare the resultsets" I gues he means comparing visually (with his eye ;) ) He want to see the table data with his eye and see how to join them.
                                Eish... the Mk. 1 Eyeball cannot compare to native SQL in that regard. :-)
                                • 13. Re: Multiple select statements in PL/SQL
                                  465815
                                  "Maximize SQL. Minimize PL/Java/.Net/etc."
                                  I couldn't agree more.
                                  • 14. Re: Multiple select statements in PL/SQL
                                    720040
                                    I am using TOAD as my query processor.
                                    1 2 Previous Next