в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:55:10Z

1 версия

Новая страница

==select sign( 1 2 ) ) "Using expressions"==



<source lang="sql">

SQL>
SQL>
SQL> select sign( 100 - ( 50 * 2 ) ) "Using expressions"
2 from dual
3 /
Using expressions
-----------------
0
1 row selected.
SQL> --
</source>

==select sign( 123 * "Using expressions"==



<source lang="sql">

SQL>
SQL> select sign( 123 * -1 + 122) "Using expressions"
2 from dual
3 /
Using expressions
-----------------
-1
1 row selected.
SQL>
SQL> --
</source>

==SIGN(0)==



<source lang="sql">

SQL> select SIGN(0) from dual;
SIGN(0)
----------
0
SQL>
SQL>

</source>

==SIGN(5)==



<source lang="sql">

SQL> select SIGN(5) from dual;
SIGN(5)
----------
1
SQL>

</source>

==SIGN: Returns 1 if the argument is positive, 0 if the argument is negative==



<source lang="sql">

SQL>
SQL> -- create demo table
SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 MyName VARCHAR2(10 BYTE),
4 MyDate DATE,
5 MyNumber Number(8,2)
6 )
7 /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("2","Jason",to_date("19970622","YYYYMMDD"),-12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("3","Smith",to_date("19980513","YYYYMMDD"),22.1);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("4","Tailor",to_date("19990624","YYYYMMDD"),-2.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("5","Darlene",to_date("20000415","YYYYMMDD"),2.1);
1 row created.
SQL>
SQL>
SQL> select * from TestTable;
ID MYNAME MYDATE MYNUMBER
---- ---------- --------- ----------
1 Alison 11-JUL-96 12.12
2 Jason 22-JUN-97 -12.12
3 Smith 13-MAY-98 22.1
4 Tailor 24-JUN-99 -2.12
5 Darlene 15-APR-00 2.1
SQL>
SQL>
SQL>
SQL> -- SIGN: Returns 1 if the argument is positive, 0 if the argument is negative.
SQL>
SQL>
SQL> select MyNumber, SIGN(MyNumber) from TestTable;
MYNUMBER SIGN(MYNUMBER)
---------- --------------
12.12 1
-12.12 -1
22.1 1
-2.12 -1
2.1 1
SQL>
SQL>
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>

</source>

==-SIGN(x): Returns -1 if x is negative, 1 if x is positive, or 0 if x is zero==



<source lang="sql">

SQL> --SIGN(x): Returns -1 if x is negative, 1 if x is positive, or 0 if x is zero.
SQL>
SQL> select SIGN(-5) from dual;
SIGN(-5)
----------
-1
SQL>

</source>

==Use sign function in user-defined function==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE FUNCTION get_num (
2 p_highval NUMBER,
3 p_lowval NUMBER := 0,
4 p_negatives_allowed BOOLEAN := FALSE,
5 p_scale PLS_INTEGER := 0
6 )
7 RETURN NUMBER
8 IS
9 l_ret NUMBER;
10 l_sign NUMBER := 1;
11 BEGIN
12 IF (p_negatives_allowed) THEN
13 l_sign := SIGN (DBMS_RANDOM.random);
14 END IF;
15
16 l_ret := l_sign * ROUND (DBMS_RANDOM.VALUE (p_lowval, p_highval), p_scale);
17 RETURN l_ret;
18 END;
19 /
Function created.
</source>

==Use SIGN in PL/SQL statement==



<source lang="sql">

SQL>
SQL> set serveroutput on
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(SIGN(3.5));
3 DBMS_OUTPUT.PUT_LINE(SIGN(-3.5));
4 DBMS_OUTPUT.PUT_LINE(SIGN(0));
5 END;
6 /
1
-1
0
PL/SQL procedure successfully completed.
SQL>
SQL>
</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Numeric Math Functions/SIGN - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия