Oracle PL/SQL/Numeric Math Functions/least

Материал из SQL эксперт
Перейти к: навигация, поиск

select least( "A", "B", "C" ) from dual;

   <source lang="sql">
  

SQL> SQL> select least( "A", "B", "C" ) from dual; L - A 1 row selected. SQL> SQL> --


 </source>
   
  


select least( "X", "x" ) from dual;

   <source lang="sql">
  

SQL> SQL> SQL> select least( "X", "x" ) from dual; L - X 1 row selected. SQL> SQL> --


 </source>
   
  


This script demonstrates the Least functions

   <source lang="sql">
 

SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2     v_char VARCHAR2(10);
 3     v_number NUMBER(10);
 4  BEGIN
 5
 6
 7     v_char := LEAST("A", "B", "C");
 8     v_number := LEAST(1,2,3);
 9
10     DBMS_OUTPUT.PUT_LINE("Least Character: "||v_char);
11     DBMS_OUTPUT.PUT_LINE("Least Number: "||v_number);
12
13  END;
14  /

Least Character: A Least Number: 1 PL/SQL procedure successfully completed. SQL> SQL>


 </source>
   
  


Use least() to limit the value

   <source lang="sql">
 

SQL> create table salary

 2  ( grade      NUMBER(2)   constraint S_PK primary key
 3  , lowerlimit NUMBER(6,2)
 4  , upperlimit NUMBER(6,2)
 5  , bonus      NUMBER(6,2)
 6  , constraint S_LO_UP_CHK check(lowerlimit <= upperlimit)
 7  ) ;

Table created. SQL> SQL> insert into salary values (1, 700,1200, 0); 1 row created. SQL> insert into salary values (2, 1201,1400, 50); 1 row created. SQL> insert into salary values (3, 1401,2000, 100); 1 row created. SQL> insert into salary values (4, 2001,3000, 200); 1 row created. SQL> insert into salary values (5, 3001,9999, 500); 1 row created. SQL> SQL> select grade + 5

 2  ,      lowerlimit + 2300
 3  ,      least(9999,upperlimit + 2300)
 4  ,      500
 5  from   salary;
  GRADE+5 LOWERLIMIT+2300 LEAST(9999,UPPERLIMIT+2300)        500

--------------- --------------------------- ----------
        6            3000                        3500        500
        7            3501                        3700        500
        8            3701                        4300        500
        9            4301                        5300        500
       10            5301                        9999        500

SQL> SQL> SQL> drop table salary; Table dropped.


 </source>