📄 ch04.htm
字号:
<H3><FONT COLOR="#000077">SYSDATE</FONT></H3>
<P><TT>SYSDATE</TT> returns the system time and date:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT DISTINCT SYSDATE
</B> 2<B> FROM PROJECT;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SYSDATE
----------------
18-JUN-95 1020PM
</FONT></PRE>
<P>If you wanted to see where you stood today in a certain project, you could type</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
</B> 2<B> FROM PROJECT
</B> 3<B> WHERE STARTDATE > SYSDATE;</B>
TASK STARTDATE ENDDATE
-------------- --------- ---------
CODE WIDGET 01-JUL-95 02-SEP-95
TESTING 03-SEP-95 17-JAN-96
</FONT></PRE>
<P>Now you can see what parts of the project start after today.
<H2><FONT COLOR="#000077">Arithmetic Functions</FONT></H2>
<P>Many of the uses you have for the data you retrieve involve mathematics. Most
implementations of SQL provide arithmetic functions similar to the functions covered
here. The examples in this section use the <TT>NUMBERS</TT> table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
</B> 2 <B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> A B
--------- ---------
3.1415 4
-45 .707
5 9
-57.667 42
15 55
-7.2 5.3
6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">ABS</FONT></H3>
<P>The <TT>ABS</TT> function returns the absolute value of the number you point to.
For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT ABS(A) ABSOLUTE_VALUE
</B> 2<B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ABSOLUTE_VALUE
--------------
3.1415
45
5
57.667
15
7.2
6 rows selected.
</FONT></PRE>
<P><TT>ABS</TT> changes all the negative numbers to positive and leaves positive
numbers alone.
<H3><FONT COLOR="#000077">CEIL and FLOOR</FONT></H3>
<P><TT>CEIL</TT> returns the smallest integer greater than or equal to its argument.
<TT>FLOOR</TT> does just the reverse, returning the largest integer equal to or less
than its argument. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT B, CEIL(B) CEILING
</B> 2 <B>FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> B CEILING
--------- ---------
4 4
.707 1
9 9
42 42
55 55
5.3 6
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>And</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, FLOOR(A) FLOOR
</B> 2 <B> FROM NUMBERS;</B>
A FLOOR
--------- ---------
3.1415 3
-45 -45
5 5
-57.667 -58
15 15
-7.2 -8
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">COS, COSH, SIN, SINH, TAN, and TANH</FONT></H3>
<P>The <TT>COS</TT>, <TT>SIN</TT>, and <TT>TAN</TT> functions provide support for
various trigonometric concepts. They all work on the assumption that n is in radians.
The following statement returns some unexpected values if you don't realize <TT>COS</TT>
expects <TT>A</TT> to be in radians.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, COS(A)
</B> 2<B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> A COS(A)
--------- ---------
3.1415 -1
-45 .52532199
5 .28366219
-57.667 .437183
15 -.7596879
-7.2 .60835131
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You would expect the <TT>COS</TT> of <TT>45</TT> degrees to be in the neighborhood
of <TT>.707</TT>, not <TT>.525</TT>. To make this function work the way you would
expect it to in a degree-oriented world, you need to convert degrees to radians.
(When was the last time you heard a news broadcast report that a politician had done
a pi-radian turn? You hear about a 180-degree turn.) Because 360 degrees = 2 pi radians,
you can write</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, COS(A* 0.01745329251994)
</B> 2<B> FROM NUMBERS;
</B></FONT></PRE>
<PRE><FONT COLOR="#0066FF"> A COS(A*0.01745329251994)
--------- -----------------------
3.1415 .99849724
-45 .70710678
5 .9961947
-57.667 .5348391
15 .96592583
-7.2 .9921147
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Note that the number <TT>0.01745329251994</TT> is radians divided by degrees.
The trigonometric functions work as follows:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, COS(A*0.017453), COSH(A*0.017453)
</B> 2 <B> FROM NUMBERS;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF"> A COS(A*0.017453) COSH(A*0.017453)
--------- --------------- ----------------
3.1415 .99849729 1.0015035
-45 .70711609 1.3245977
5 .99619483 1.00381
-57.667 .53485335 1.5507072
15 .96592696 1.0344645
-7.2 .99211497 1.0079058
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>And</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, SIN(A*0.017453), SINH(A*0.017453)
</B> 2<B> FROM NUMBERS;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF"> A SIN(A*0.017453) SINH(A*0.017453)
--------- --------------- ----------------
3.1415 .05480113 .05485607
-45 -.7070975 -.8686535
5 .08715429 .0873758
-57.667 -.8449449 -1.185197
15 .25881481 .26479569
-7.2 -.1253311 -.1259926
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>And</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, TAN(A*0.017453), TANH(A*0.017453)
</B> 2<B> FROM NUMBERS;
</B>
A TAN(A*0.017453) TANH(A*0.017453)
--------- --------------- ----------------
3.1415 .05488361 .05477372
-45 -.9999737 -.6557867
5 .08748719 .08704416
-57.667 -1.579769 -.7642948
15 .26794449 .25597369
-7.2 -.1263272 -.1250043
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">EXP</FONT></H3>
<P><TT>EXP</TT> enables you to raise <I>e</I> (<I>e</I> is a mathematical constant
used in various formulas) to a power. Here's how <TT>EXP</TT> raises <I>e</I> by
the values in column A:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, EXP(A)
</B> 2 <B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF"> A EXP(A)
--------- ---------
3.1415 23.138549
-45 2.863E-20
5 148.41316
-57.667 9.027E-26
15 3269017.4
-7.2 .00074659
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">LN and LOG</FONT></H3>
<P>These two functions center on logarithms. <TT>LN</TT> returns the natural logarithm
of its argument. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> SELECT A, LN(A)
</B> 2<B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">ERROR:
ORA-01428: argument '-45' is out of range
</FONT></PRE>
<P>Did we neglect to mention that the argument had to be positive? Write</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, LN(ABS(A))
</B> 2 <B> FROM NUMBERS;</B>
A LN(ABS(A))
--------- ----------
3.1415 1.1447004
-45 3.8066625
5 1.6094379
-57.667 4.0546851
15 2.7080502
-7.2 1.974081
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice how you can embed the function <TT>ABS</TT> inside the <TT>LN</TT> call.
The other logarith-mic function, <TT>LOG</TT>, takes two arguments, returning the
logarithm of the first argument in the base of the second. The following query returns
the logarithms of column B in base 10.</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT B, LOG(B, 10)
</B> 2<B> FROM NUMBERS;</B>
B LOG(B,10)
----------- ---------
4 1.660964
.707 -6.640962
9 1.0479516
42 .61604832
55 .57459287
5.3 1.3806894
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">MOD</FONT></H3>
<P>You have encountered <TT>MOD</TT> before. On Day 3, "Expressions, Conditions,
and Operators," you saw that the ANSI standard for the modulo operator <TT>%</TT>
is sometimes implemented as the function <TT>MOD</TT>. Here's a query that returns
a table showing the remainder of A divided by B:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> SELECT A, B, MOD(A,B)
</B> 2 <B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> A B MOD(A,B)
--------- --------- ---------
3.1415 4 3.1415
-45 .707 -.459
5 9 5
-57.667 42 -15.667
15 55 15
-7.2 5.3 -1.9
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">POWER</FONT></H3>
<P>To raise one number to the power of another, use <TT>POWER</TT>. In this function
the first argument is raised to the power of the second:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, B, POWER(A,B)
</B> 2<B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">ERROR:
ORA-01428: argument '-45' is out of range
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>At first glance you are likely to think that the first argument can't be negative.
But that impression can't be true, because a number like -4 can be raised to a power.
Therefore, if the first number in the <TT>POWER</TT> function is negative, the second
must be an integer. You can work around this problem by using <TT>CEIL</TT> (or <TT>FLOOR</TT>):</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, CEIL(B), POWER(A,CEIL(B))
</B>2<B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> A CEIL(B) POWER(A,CEIL(B))
--------- --------- ----------------
3.1415 4 97.3976
-45 1 -45
5 9 1953125
-57.667 42 9.098E+73
15 55 4.842E+64
-7.2 6 139314.07
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>That's better!
<H3><FONT COLOR="#000077">SIGN</FONT></H3>
<P><TT>SIGN</TT> returns <TT>-1</TT> if its argument is less than <TT>0</TT>, <TT>0</TT>
if its argument is equal to <TT>0</TT>, and <TT>1</TT> if its argument is greater
than <TT>0</TT>, as shown in the following example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, SIGN(A)
</B> 2<B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF"> A SIGN(A)
--------- ---------
3.1415 1
-45 -1
5 1
-57.667 -1
15 1
-7.2 -1
0 0
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -