Home » SQL & PL/SQL » SQL & PL/SQL » Need a bit of help with function basics..
Need a bit of help with function basics.. [message #357531] Wed, 05 November 2008 15:43 Go to next message
Meeples
Messages: 2
Registered: November 2008
Junior Member
I'm quite new to working with PL/SQL, and am having issues crafting up a certain function here.

This function's intent is to compare rows in one table with a couple of others, checking for rows that match certain criteria. It is then meant to return the number of these rows found.

As noted, I'm quite new, as in 'this is my first function'. I had figured on using a SELECT INTO to gather up the criteria-fitting rows into a new table, and then COUNT that, but as SELECT INTO with PL/SQL functions can only take a single row, I cannot see how to get that to work.

Can someone offer suggestions to me here as to how I should go about properly doing this? Thank you!
Re: Need a bit of help with function basics.. [message #357534 is a reply to message #357531] Wed, 05 November 2008 16:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
you can use a cursor. There are several choices, the easiest of which is to use the FOR LOOP cursor. Here is an example.

set serveroutput on

begin
   for r1 in (
               select table_name,tablespace_name,logging
               from user_tables
               where rownum <= 3
             ) loop
      dbms_output.put_line('table_name='||r1.table_name);
      dbms_output.put_line('tablespace_name='||r1.tablespace_name);
      dbms_output.put_line('logging='||r1.logging);
      dbms_output.put_line('---------------------------');
   end loop;
end;
/


SQL> set serveroutput on
SQL> 
SQL> begin
  2     for r1 in (
  3                 select table_name,tablespace_name,logging
  4                 from user_tables
  5                 where rownum <= 3
  6               ) loop
  7        dbms_output.put_line('table_name='||r1.table_name);
  8        dbms_output.put_line('tablespace_name='||r1.tablespace_name);
  9        dbms_output.put_line('logging='||r1.logging);
 10        dbms_output.put_line('---------------------------');
 11     end loop;
 12  end;
 13  /
table_name=MV_UW_PROD_ASSIGNMENTS
tablespace_name=USER_SPACE
logging=YES
---------------------------
table_name=MV_CALL_MEMO_REPORT
tablespace_name=USER_SPACE
logging=YES
---------------------------
table_name=MV_SALES_TEAMS
tablespace_name=USER_SPACE
logging=NO
---------------------------

PL/SQL procedure successfully completed.

Notice how you can put whatever sql statement you want as the rows you bring back.
Notice how values are referenced in the loop code using record_name.column_name syntax.
Notice that you do not need to open/close your query as it is done automatically.

It is also possible to nest cursors like this:

begin
   for r1 in (
               select table_name
               from user_tables
               where rownum = 1
             ) loop
      dbms_output.put_line('table_name='||r1.table_name);
      for r2 in (
                  select column_name
                  from user_tab_columns
                  where user_tab_columns.table_name = r1.table_name
                ) loop
         dbms_output.put_line('......'||r2.column_name);
      end loop;
      dbms_output.put_line('--------------------------');
   end loop;
end;
/


SQL> begin
  2     for r1 in (
  3                 select table_name
  4                 from user_tables
  5                 where rownum = 1
  6               ) loop
  7        dbms_output.put_line('table_name='||r1.table_name);
  8        for r2 in (
  9                    select column_name
 10                    from user_tab_columns
 11                    where user_tab_columns.table_name = r1.table_name
 12                  ) loop
 13           dbms_output.put_line('......'||r2.column_name);
 14        end loop;
 15        dbms_output.put_line('--------------------------');
 16     end loop;
 17  end;
 18  /
table_name=MV_UW_PROD_ASSIGNMENTS
......DIV_NAME
......MASTER_CD
......TEAM_NAME
......PRODUCER_CD
......UW_NAME
......AGENCY_NAME
......LAST_NAME
......FIRST_NAME
......SUPP_EFF_DT
......SUPP_EXP_DT
......POS_EXP_DT
......POS_EFF_DT
......EMP_EFF_DT
......EMP_EXP_DT
......EFF_DT
......EXP_DT
--------------------------

PL/SQL procedure successfully completed.


Notice how the second for loop is correlated to the first by use of its where clause and a reference back to data comming off the first for loop.

Hope this help, good luck, Kevin
Re: Need a bit of help with function basics.. [message #357535 is a reply to message #357534] Wed, 05 November 2008 16:37 Go to previous messageGo to next message
Meeples
Messages: 2
Registered: November 2008
Junior Member
Thank you! 'twas of much help, I must say. =>
Re: Need a bit of help with function basics.. [message #357744 is a reply to message #357531] Thu, 06 November 2008 07:49 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Glad to help. Now that you see how PL/SQL can do what you want I would add the following:

PL/SQL cursors of any form process the data one row at a time. TOM KYTE of ASKTOMHOME fame coined the phrase to desrbie row by row processing as "SLOW BY SLOW" processing. And it is. You are always better trying to find an all sql solution to your problem.

I would suggest that you first write the PL/SQL solution to get what you want. This will give you a working product that lets you learn plsql, and from which results can be compared to the ALL SQL alternative.

Then you should think hard about it and craft an all sql solution to your problem. This does several things:

Quote:
1) if both alternatives give the same answer then they are likely both correct.
2) you will see that it is no more difficult to write a sql only solution as compared to a pl/sql solution.
3) if the datasets are large enought, you will see how much faster a sql only solution can run.

Good luck, Kevin
Previous Topic: Fetching values from tables
Next Topic: PL/SQL Package compilation
Goto Forum:
  


Current Time: Mon Dec 05 21:29:42 CST 2016

Total time taken to generate the page: 0.13498 seconds