Home » SQL & PL/SQL » SQL & PL/SQL » How to use list of selected values on the body of procedure (Oracle 10g, PL/SQL)
How to use list of selected values on the body of procedure [message #602160] Sat, 30 November 2013 11:30 Go to next message
lemune
Messages: 5
Registered: November 2013
Junior Member
Hello, I need help with the structure of the procedure.
First of all, I should retrieve list of table names and owners.
At the body of the procedure I want to use this list for testing and comparisons.
I made some example that used cursor, I know it is not true.
Please advise how to implement this.
-------------------------------------
create or replace
PROCEDURE TEST_PROCEDURE (P_CODE IN NUMBER) IS

CURSOR C01 IS
(SELECT TABLE_NAME, OWNER
FROM TABLE1
UNION
SELECT TABLE_NAME, OWNER
FROM TABLE2);

BEGIN

SELECT MAX(SCORE)
INTO V_SCORE
FROM TABLE4 Q
WHERE EXISTS (SELECT 'Y'
FROM C01 T --- ?????????
WHERE Q.TABLE_NAME = T.TAB_NAME
AND Q.OWNER = T.OWNER);

END TEST_PROCEDURE;
Re: How to use list of selected values on the body of procedure [message #602164 is a reply to message #602160] Sat, 30 November 2013 12:52 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A cursor FOR loop might be one option; have a look at the following example:
declare
  v_score number;
begin
  for cur_r in (select table_name, owner from table1 union
                select table_name, owner from table2)
  loop
    select max(score) 
      into v_score
      from table4 q
      where q.table_name = cur_r.table_name
        and q.owner = cur_r.owner;
  end loop;
end;
Re: How to use list of selected values on the body of procedure [message #602165 is a reply to message #602164] Sat, 30 November 2013 13:15 Go to previous messageGo to next message
lemune
Messages: 5
Registered: November 2013
Junior Member
Thanks, but I need to use this list several times during the procedure body.
Therefore, in my opinion should be another solution, without using cursor.
Re: How to use list of selected values on the body of procedure [message #602168 is a reply to message #602165] Sat, 30 November 2013 14:34 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What prevents you from using it throughout the procedure (as long as you keep it within the loop)?

Besides, it is you who created a cursor, but did it in a wrong manner - I just showed you how to use it properly. Reading your question, I still think that there's no problem with cursor whatsoever.
Re: How to use list of selected values on the body of procedure [message #602170 is a reply to message #602165] Sat, 30 November 2013 14:46 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
lemune wrote on Sat, 30 November 2013 19:15
Thanks, but I need to use this list several times during the procedure body.
Therefore, in my opinion should be another solution, without using cursor.

Here's an idea, why don't you tell us exactly what it is that you're trying to do instead of making vague allusions to your requirements.
Re: How to use list of selected values on the body of procedure [message #602200 is a reply to message #602160] Sun, 01 December 2013 07:05 Go to previous messageGo to next message
lemune
Messages: 5
Registered: November 2013
Junior Member
Sorry for the delay. Apparently I'm not being clear enough.
Below is the full code of my procedure.
I'm trying to find a way to implement the sub select only once (select from ri_relationships with union).

----------------------------------------------------------
create or replace
procedure test_procedure (p_owner          in varchar2,
                       p_table_name     in varchar2) is

  v_owner varchar2(50) := upper(p_owner);
  v_table_name varchar2(50) := upper(p_table_name);
  v_score number := 0;
  v_table_score number := 0;

  begin

      while v_table_score <> 99
      loop
         select nvl(max(score),0)
           into v_score
           from ds.ri_mng_queue q
          where exists (select 'Y'
                          from (select t.dim_table_name tab_name, 
                                       t.dim_owner owner
                                  from ds.ri_relationships t
                                 where t.fact_table_name = v_table_name
                                   and t.fact_owner = v_owner
                                 union
                                select t.fact_table_name tab_name, 
                                       t.fact_owner owner
                                  from ds.ri_relationships t
                                 where t.dim_table_name = v_table_name
                                   and t.dim_owner = v_owner) t
                                 where q.table_name = t.tab_name
                                   and q.owner = t.owner);

         if v_score = 0 and v_table_score = 0 then
         insert into ds.ri_mng_queue values (1, p_table_name, p_owner, sysdate);
         v_table_score := 99;

         else if v_table_score = 0 then
         v_table_score := v_score + 1;
         insert into ds.ri_mng_queue values (v_table_score, p_table_name, p_owner, sysdate);
         dbms_lock.sleep(10);

         else if v_table_score <> 0 and v_score = 0 then
         v_table_score := 99;

         else
         dbms_lock.sleep(10);
         end if; 
         end if; 
         end if;

    commit;
    end loop;

end test_procedure;
Re: How to use list of selected values on the body of procedure [message #602220 is a reply to message #602170] Sun, 01 December 2013 17:23 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
As Pabolee said earlier:
[quote title=pablolee wrote on Sat, 30 November 2013 20:46]lemune wrote on Sat, 30 November 2013 19:15

Here's an idea, why don't you tell us exactly what it is that you're trying to do instead of making vague allusions to your requirements.


I very much doubt anyone reading this has a clue what it is you're trying to do.
I suggest you explain your requirement using words, rather than code that presumably doesn't do what you want.
Re: How to use list of selected values on the body of procedure [message #602287 is a reply to message #602220] Mon, 02 December 2013 14:44 Go to previous messageGo to next message
lemune
Messages: 5
Registered: November 2013
Junior Member
This is system for management locks. According to the relationships table the process will manage queues of the locks.
The code (second) does exactly what I need, but the way it was written the sub query will be repeated again and again (select from ri_relationships).
I have to write it in such a way that the sub select will be executed only once, before the loop.
The questions is how can I save the result of the select for using it in the loop body.
Re: How to use list of selected values on the body of procedure [message #602290 is a reply to message #602287] Mon, 02 December 2013 14:53 Go to previous messageGo to next message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
>According to the relationships table the process will manage queues of the locks.
This single sentence REEKS of multiple design flaws.
I would like to bet that if you ever get the software meet the requirements, that it will scale as well as my goat can fly.
I hope that you plan on doing load testing before letting users access it; otherwise you could end up a something like healthcare.gov results.
Re: How to use list of selected values on the body of procedure [message #602353 is a reply to message #602290] Tue, 03 December 2013 05:41 Go to previous message
lemune
Messages: 5
Registered: November 2013
Junior Member
I read the news about healthcare.gov, thanks for your help.
Previous Topic: split long string
Next Topic: Reading Particular Line in UTL_FILE
Goto Forum:
  


Current Time: Sun Apr 20 19:08:34 CDT 2014

Total time taken to generate the page: 0.10653 seconds