Arrays in PLSQL

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
lopes_andre
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 49
Joined: Mon, 13 Oct 2008 9:01 am
Location: Lisboa

Viva,

I am doing a program in plsql that will make a draw. This draw besides being random also has a weighting.

I am based on this program in Excel: http://www.dailydoseofexcel.com/archive ... dom-sorts/
I wanted to know what the best way to deal with the following situation:

In my case, assigns the categories of the company. The employee will have a weighting value equal to that of its category.

If the weighting of the XPTO employee category has the value 6 I would have to create an array with 6 values, each of these values ??would be generated according to the formula ->

Select all

=(RAND()^LOG(1-1/(6+1); 0,5))*1000
This formula is still a formula in Excel. Of these 6 values ??generated by the formula in the array I would choose the highest.

Is this possible to do in PLSQL or will I have to take another way?


Thanks.

Greetings,
André.
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

I do not think it's okay.
To make number randoms, you can use dbms_rand. [[0] http://en.glufke.net/oracle/viewtopic.php?t=243

Select all

SQL> select dbms_random.random from dual; 
 
    RANDOM 
---------- 
-1.262E+09 
 
SQL> / 
 
    RANDOM 
---------- 
 619010116 
 
SQL> / 
 
    RANDOM 
---------- 
 406715840 
 
SQL> / 
 
    RANDOM 
---------- 
-615930667 
 
SQL>  

To log, it is also no problem: http://www.techonthenet.com/oracle/functions/log.php

Select all

SQL> select log(10, 100) from dual; 
 
LOG(10,100) 
----------- 
          2 
 
SQL> 
]
Now, just set up something that looks for these results and get the biggest of them ...
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Ball show, dr_gori.

only supplementing, to pick up the highest value, can still use Greatest (if I am not mistaken 9i and higher):

Select all

 
SELECT GREATEST (2, 5, 12, 3) maior_valor 
  FROM DUAL; 
Returns: 12.

Anything, Tamos aí.
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Nice job!

But I think maybe he will have to use another way to get the biggest. Maybe insert into a global temporary table (if it is not fixed the maximum number of his "weighting" ...

This is because it is impossible to do greatest of an array. Example:

Select all

SQL> declare 
  2   TYPE ARRAY IS TABLE OF number INDEX BY BINARY_INTEGER; 
  3    
  4   t ARRAY; 
  5    
  6  begin 
  7   
  8    t(1) :=10; 
  9    t(2) :=20; 
 10    t(3) :=30; 
 11     
 12    dbms_output.put_line ( greatest(t) ); 
 13   
 14   
 15  end; 
 16  / 
  dbms_output.put_line ( greatest(t) ); 
                         * 
ERROR at line 12: 
ORA-06550: line 12, column 26: 
PLS-00306: wrong number or types of arguments in call to 'GREATEST' 
ORA-06550: line 12, column 3: 
PL/SQL: Statement ignored 
 
 
SQL>  
I think it's best to insert the values ??in a gtt and make max simply.
lopes_andre
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 49
Joined: Mon, 13 Oct 2008 9:01 am
Location: Lisboa

Live,

Thanks for the help!

I am already building the program, however I am having difficulty with dbms_random. I will not work with arrays, I'm going to send the values ??generated to a Temporary table, it will simplify things ... now the obstacle ..

in Excel I am using this formula:

Select all

=RAND()^LOG(1-1/(N+1);0,5)
The closer N of 10 which will be the most likely maximum weight / weighting will be generated values ??of 1.

Do random from a log in excel works well. In Oracle I can not do random from a log. Does it have a way to do random from a log?


Thanks.

best compliments
André.
ricardorauber
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 96
Joined: Wed, 21 Jun 2006 11:33 pm
Location: Canoas RS

I would do so:

Select all

 
DECLARE 
 
  TYPE nums IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 
  vetor nums; 
   
  FUNCTION maior RETURN NUMBER IS 
    cont NUMBER := 0; 
  BEGIN 
    FOR i IN vetor.FIRST .. vetor.LAST LOOP 
      IF vetor(i) > cont THEN 
        cont := vetor(i); 
      END IF; 
    END LOOP; 
    RETURN cont; 
  END maior; 
   
BEGIN 
 
  vetor(1) := 3; 
  vetor(2) := 5; 
  vetor(3) := 4; 
   
  dbms_output.put_line(maior); 
   
END; 
ricardorauber
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 96
Joined: Wed, 21 Jun 2006 11:33 pm
Location: Canoas RS

To calculate exponential, random and log

Select all

 
DECLARE 
 
  TYPE nums IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 
  vetor nums; 
   
BEGIN 
 
  -- LOG(1-1/(1+A1);0,5) 
 
  -- Insere valores 
  FOR i IN 1 .. 10 LOOP 
    vetor(i) := i; 
    dbms_output.put_line(vetor(i)); 
  END LOOP; 
   
  -- Separador 
  dbms_output.put_line(rpad('-',100,'-')); 
   
  -- Adiciona o log 
  FOR i IN vetor.FIRST .. vetor.LAST LOOP 
    vetor(i) := LOG(0.5,1-1/(1+vetor(i))); 
    dbms_output.put_line(vetor(i)); 
  END LOOP; 
   
  -- Separador 
  dbms_output.put_line(rpad('-',100,'-')); 
   
  -- Adiciona o random e calcula a potencia (exponencial) 
  FOR i IN vetor.FIRST .. vetor.LAST LOOP 
    vetor(i) := power(trunc(dbms_random.value(1,10)), vetor(i)); 
    dbms_output.put_line(vetor(i)); 
  END LOOP; 
   
END; 
Output:

Select all

 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
---------------------------------------------------------------------------------------------------- 
1 
,5849625007211561814537389439478165087624 
,41503749927884381854626105605218349124 
,3219280948873623478703194294893901758618 
,2630344058337938335834195144584263329081 
,22239242133644792598823037328401429991 
,1926450779423958925580306827681691913527 
,1699250014423123629074778878956330175169 
,1520030934450499849628415415937571583599 
,1375035237499349083290436172364027828474 
---------------------------------------------------------------------------------------------------- 
2 
1,90150749823037254511681818840789155874 
1,77777777777777777777777777777777777777 
2,02860491380619980243290499234874746619 
1,78237306890983628007321950484593599572 
1,1666666666666666666666666666666666667 
1,45480229597148250562035976049055480278 
1,45261210788256229152408722218618633821 
1,37174211248285322359396433470507544584 
1,09999999999999999999999999999999999999 
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 13 guests