Oracle PL/SQL functions) (2)

22. Exp
Returns the n root of the number E.
SQL> select exp (2), exp (1) from dual;

Exp (2) exp (1)

------------------
7.3890561 2.7182818

23. Floor
Returns an integer to a given number.
SQL> select floor (2345.67) from dual;

Floor (2345.67)
--------------
2345

24. ln
Returns the logarithm of a number.
SQL> select ln (1), Ln (2), Ln (2.7182818) from dual;

Ln (1) ln (2) ln (1, 2.7182818)
-------------------------------
0. 69314718. 99999999

25. Log (N1, N2)
Returns the base N2 logarithm of N1.
SQL> Select log (2, 1), log (2, 4) from dual;

Log (2, 1) log (2, 4)
------------------
0 2

26. Mod (N1, N2)
Returns the remainder of N1 divided by N2.
SQL> select Mod (10, 3), MOD (3, 3), MOD (2, 3) from dual;

MoD () mod)
---------------------------
1 0 2

27. Power
Returns the N2 root of N1.
SQL> select power (2, 10), power (3, 3) from dual;

Power (2, 10) power (3, 3)
---------------------
1024 27

28. Round and trunc
Round according to the specified precision
SQL> select round (55.5), round (-55.4), trunc (55.5), trunc (-55.5) from dual;

Round (55.5) round (-55.4) trunc (55.5) trunc (-55.5)
----------------------------------------------
56-55 55-55

29. Sign
Returns 1 if the number N is greater than 0,-1 if the value is less than 0, and 0 if the value is 0.
SQL> select sign (123), sign (-100), sign (0) from dual;

Sign (123) sign (-100) Sign (0)
----------------------------
1-1 0

30. Sin
Returns the sine of a number.
SQL> select sin (1.57079) from dual;

Sin (1.57079)
------------
1

31. Sigh
Returns the hyperbolic sine value.
SQL> select sin (20), sinh (20) from dual;

 

Sin (20) sinh (20)
------------------
.. 91294525 242582598

32. SQRT
Returns the root of number n.
SQL> select SQRT (64), SQRT (10) from dual;

SQRT (64) SQRT (10)
------------------
8 3.1622777

33. Tan
Returns the tangent of a number.
SQL> select Tan (20), Tan (10) from dual;

Tan (20) Tan (10)
------------------
2.2371609. 64836083

34. Tanh
Returns the hyperbolic tangent of number n.
SQL> select Tanh (20), Tan (20) from dual;

Tanh (20) Tan (20)
------------------
1 2.2371609

 

35. trunc
Truncate a number based on the specified precision
SQL> select trunc (124.1666,-2) trunc1, trunc (124.16666, 2) from dual;

Trunc1 trunc (124.16666, 2)
---------------------------
100 124.16

 

36. add_months
Add or subtract a month
SQL> select to_char (add_months (to_date ('000000', 'yyymmm'), 2), 'yyymmm') from dual;

To_cha
------
200002
SQL> select to_char (add_months (to_date ('000000', 'yyymmm'),-2), 'yyymmm') from dual;

To_cha
------
199910

37. last_day
Returns the last day of the date.
SQL> select to_char (sysdate, 'yyyy. Mm. dd'), to_char (sysdate) + 1, 'yyyy. Mm. dd') from dual;

To_char (SY to_char (S
--------------------
2004.05.09 2004.05.10
SQL> select last_day (sysdate) from dual;

Last_day (S
----------
September 31-04

38. months_between (date2, date1)
Given the month of the date2-date1
SQL> select months_between ('19-December-1999 ', '19-March-1999') mon_between from dual;

Mon_between
-----------
9
SQL> selectmonths_between (to_date ('192. 100', 'yyyy. Mm. dd'), to_date ('192. 100', 'yyyy. dd') mon_betw from dual;

Mon_betw
---------
-60

39. new_time (date, 'this', 'that ')
Returns the date and time in this time zone = Other time zone.
SQL> select to_char (sysdate, 'yyyy. Mm. dd hh24: MI: ss') bj_time, to_char (new_time
2 (sysdate, 'pdt ', 'gmt'), 'yyyy. Mm. dd hh24: MI: ss') los_angles from dual;

Bj_time los_angles
--------------------------------------
2004.05.09 11:05:32 2004.05.09 18:05:32

40. next_day (date, 'day ')
Returns the date of the week and the date of the next week after week X.
SQL> select next_day ('18-May-2001 ', 'Friday') next_day from dual;

Next_day
----------
25-5-01

 

41. sysdate
Used to obtain the current date of the system
SQL> select to_char (sysdate, 'dd-mm-yyyy Day') from dual;

To_char (sysdate ,'
-----------------
09-05-2004 Sunday
Trunc (date, FMT) truncates the date according to the given requirements. If FMT = 'mi ', it indicates that the minute is retained and the second is truncated.
SQL> select to_char (trunc (sysdate, 'hh'), 'yyyy. Mm. dd hh24: MI: ss') HH,
2 to_char (trunc (sysdate, 'mi'), 'yyyy. Mm. dd hh24: MI: ss') hhmm from dual;

HH hhmm
--------------------------------------
2004.05.09 11:00:00 2004.05.09 11:17:00

 

42. chartorowid
Convert character data type to rowid type
SQL> select rowid, rowidtochar (rowid), ename from Scott. EMP;

Rowid rowidtochar (rowid) ename
----------------------------------------------
Aaaafkaacaaaaeqaaa Smith
Aaaafkaacaaaaeqaab Allen
Aaaafkaacaaaaeqaac ward
Aaaafkaacaaaaeqaad Jones

43. Convert (C, dset, sset)
Convert the source string sset from one language character set to another destination dset Character Set
SQL> select convert ('strutz', 'we8hp ', 'f7dec') "Conversion" from dual;

Conver
------
Strutz