The Oracle tutorials being looked at are: Oraclepl/sql single-line functions and group functions. A 1 function is a program that has 0 or more parameters and has a return value. In SQL, Oracle builds a series of functions that can be called SQL or PL/SQL statements, and functions fall into two main categories:
2
3 single-line functions
4
5 Group functions
6
7 This article will discuss how to take advantage of single-line functions and use rules.
8
9 single-line functions in SQL
10
SQL and Pl/sql have many types of functions, there are characters, numbers, dates, transformations, and mixed types of functions for processing single row of data, so these can be collectively referred to as Single-line functions. These functions can be used in select,where, order BY, and other clauses, such as the following example contains the To_char,upper,soundex and other single-line functions.
12
13SELECT Ename,to_char (hiredate, ' day,dd-mon-yyyy ')
14FROM EMP
15Where UPPER (ename) like ' al% '
16ORDER by SOUNDEX (ename)
17
18 Single-line functions can also be used in other statements, such as the SET clause of UPDATE, the values clause of INSERT, the WHERE clause of the delet, and the authentication exams pay special attention to using these functions in the SELECT statement, so our attention is focused on the SELECT statement.
19
NULL and Single-line functions
21st
22 It is difficult to begin with how to understand null, even a very experienced person is still puzzled by it. A null value indicates an unknown or null value, and any operand of an arithmetic operator is a null value, and the result is a null value, and the rule is suitable for many functions, only CONCAT,DECODE,DUMP,NVL, Replace can return a non-null value when a null parameter is invoked. The most important thing in these NVL functions is that he can handle null values directly, NVL has two parameters: Nvl (x1,x2), X1 and X2 are all expressions, return x1 When X2 is null, or return x1.
23
24 Let's take a look at the EMP datasheet It contains the salary, bonus two items that need to calculate the total compensation
25
26column Name emp_id Salary Bonus
27
28key Type PK
29nulls/unique Nn,u nn
30FK table
31datatype Number Number number
32length 11.2 11.2
33
34 is not a simple combination of salary and bonuses, if a row is a null value then the result will be null, such as the following example:
35
36update EMP
37set salary= (Salary+bonus) *1.1
38
39 In this statement, the employee's wages and bonuses will be updated to a new value, but if there is no bonus, i.e. salary + NULL, then the wrong conclusion will be drawn, and the NVL function should be used to exclude the effect of the null value.
40 so the correct statement is:
41
42update EMP
43set salary= (SALARY+NVL (bonus,0) *1.1
44
45 Single line String function
46
47 Single-line String functions are used to manipulate string data, most of them have one or more parameters, most of which return a string
48
ASCII ()
C1 is a string that returns the ASCII code of the first letter of C1, and his inverse function is Chr ()
51
52SELECT ASCII (' A ') big_a,ascii (' z ') big_z from EMP
53
54big_a big_z
5565 122
56
CHR (<i>) [Nchar_cs]
The i is a number, the function returns the decimal representation of the character
59
60select CHR (), CHR (122), CHR (223) from EMP
61
62chr65 CHR122 CHR223
63A Z B
64
CONCAT (,)
The C1,C2 is a string, the function connects C2 to the C1, and if C1 is null, returns C2. If C2 is null, returns C1, or null if c1, c2 are null. He and operator | | Returned the same result
67
68select concat (' Slobo ', ' Svoboda ') Username from dual
69
70username
71
72slobo Syoboda
73
74
Initcap ()
The C1 is a string. The function returns the first letter of each word to uppercase in the other letter lowercase. Words are restricted by spaces, control characters, and punctuation marks.
77
78select initcap (' Veni,vedi,vici ') Ceasar from dual
79
80Ceasar
81
82veni,vedi,vici
83
84
INSTR (, [, <i> [,]])
The C1,C2 is a string and I,j is an integer. The function returns the position of the C2 in the C1, and the search begins with the first character of C1. When you don't find the word you need characters return 0, if I is negative, then the search will go from right to left, but the position is calculated from left to right, and I and J have the default value of 1.
87
88select INSTR (' Mississippi ', ' I ', 3,3) from dual
89
90INSTR (' Mississippi ', ' I ', 3, 3)
91
9211
93
94select INSTR (' Mississippi ', ' I ', -2,3) from dual
95
96INSTR (' Mississippi ', ' I ', 3, 3)
97
982
99
100
INSTRB (, [, I[,j])
102 is the same as the InStr () function, except that he returns a byte, for a single byte InStrB () equals InStr ()
103
LENGTH ()
The C1 is a string that returns the length of the C1 and, if C1 is null, returns a null value.
106
107select LENGTH (' Ipso Facto ') ergo from dual
108
109ergo
110
11110
112
113 LENGTHB ()
114 returns the byte, as in length ().
115
116 Lower ()
117 returns lowercase characters of C, often appearing in the Where substring
118
119select LOWER (colorname) from Itemdetail WHERE LOWER (colorname) like '%white% '
120
121COLORNAME
122
123Winterwhite
124
125
126 Lpad (, <i> [,])
127 c1,c2 are all strings, I is an integer. On the left side of the C1, a C2 string is used to make up the length I, which can be repeated several times, if I is less than the length of the C1, then only return I so long C1 characters, others will be truncated. The default value for C2 is a single space, see Rpad.
128
129select Lpad (answer,7, ') Padded,answer unpadded from question;
130
131PADDED unpadded
132
133Yes Yes
134NO NO
135Maybe maybe
136
137
138 LTRIM (,)
13
[1] [2] [3] [4] Next page
The Oracle tutorials being looked at are: Oraclepl/sql single-line functions and group functions. 9 Remove the leftmost character in the C1, so that the first character is not in the C2, if there is no C2, then C1 will not change.
140
141select LTRIM (' Mississippi ', ' Mis ') from dual
142
143LTR
144
145ppi
146
147 Rpad (, <i> [,])
148 on the right side of the C1 with a C2 string to complement the length I, can repeat, if I is less than the length of C1, then only return I so long C1 characters, the others will be truncated. The default value for C2 is single space, others are similar to Lpad
149
RTRIM (,)
151 Remove the rightmost character in the C1, so that after the first character is not in the C2, if there is no C2, then C1 will not change.
152
153 REPLACE (, [,])
154 c1,c2,c3 are strings, and functions are returned with C3 instead of C2 appearing in C1.
155
156select REPLACE (' Uptown ', ' Up ', ' down ') from dual
157
158REPLACE
159
160downtown
161
162 Stbstr (, <i> [,])
163 C1 is a string, I,j is an integer, and returns a substring of J at the beginning of C1, if J is empty, until the end of the string.
164
165select SUBSTR (' message ', 1,4) from dual
166
167SUBS
168
169Mess
170
171
172 SUBSTRB (, <i> [,])
173 is roughly the same as substr, except that the i,j is calculated in bytes.
174
175 SOUNDEX ()
176 return words similar to C1 pronunciation
177
178select SOUNDEX (' Dawes ') Dawes SOUNDEX (' Daws ') Daws, SOUNDEX (' Dawson ') from dual
179
180Dawes Daws Dawson
181
182d200 D200 D250
183
184 TRANSLATE (,,)
185 replaces the same characters in the C1 with the C2 C3
186
187select TRANSLATE (' fumble ', ' uf ', ' ar ') test from dual
188
189TEXT
190
191ramble
192
193 TRIM ([[]] from C3)
194 will c3 the first and last of the strings, or both.
195
196select trim (' space padded ') trim from dual
197
198TRIM
199
200space Padded
201
UPPER ()
203 returns the upper case of C1, which often appears in the Where substring
204
205select name from dual where UPPER (name) like ' ki% '
206
207NAME
208
209KING
210
211 single-line numeric functions
212
213 single-line numeric functions manipulate digital data and perform mathematical and arithmetic operations. All functions have numeric parameters and return numeric values. The operands and values of all trigonometric functions are radians instead of angles, and Oracle does not provide a built-in radian and angle conversion function.
214
215 ABS ()
216 returns the absolute value of n
217
218 ACOS ()
219 inverse of the function, return 1 to 1 of the number. n indicates radians
220
221select ACOS ( -1) Pi,acos (1) ZERO from dual
222
223PI ZERO
224
2253.14159265 0
226
ASIN ()
228 the Black function, return-1 to 1,n to express radians
229
230 Atan ()
231 Inverse tangent function, returns the tangent value of N, N represents radians.
232
233 Ceil ()
234 returns the smallest integer greater than or equal to N.
235
236 COS ()
237 Returns the remaining black value of N, N is radians
238
239 COSH ()
240 returns the hyperbolic residual value of N, N is a number.
241
242select COSH (<1.4>) from dual
243
244COSH (1.4)
245
2462.15089847
247
248 EXP ()
249 returns the n power of E, e=2.71828183.
250
251 FLOOR ()
252 returns the largest integer less than or equal to N.
253
254 LN ()
255 returns the natural logarithm of n, and N must be greater than 0
256
257 LOG (,)
258 returns the logarithm of the N1 as the base N2
259
The MOD ()
261 returns n1 divided by the remainder of the N2,
262
263 Power (,)
264 return to N1 n2
265
266 ROUND (,)
267 returns the N1 value of the N2 bit to the right of the decimal point, N2 the default value of 0, which rounds the nearest integer to the decimal point, if N2 is a negative number, to the corresponding bit to the left of the decimal point, and the N2 must be an integer.
[NextPage]
268
269select ROUND (12345,-2), ROUND (12345.54321,2) from dual
270
271ROUND (12345,-2) ROUND (12345.54321,2)
272
27312300 12345.54
274
275 SIGN ()
276 if n is negative, returns-1, if n is positive, returns 1 if N=0 returns 0.
277
278 SIN ()
279 returns the positive value of N, and N is radians.
280
281 SINH ()
282 returns the hyperbolic positive value of n, and N is radians.
283
284 SQRT ()
285 returns the square root of N, N is radians
286
287 TAN ()
288 returns the tangent of N, N is radians
289
290 TANH ()
291 returns the hyperbolic tangent of N, and N is radians
292
293 TRUNC (,)
294 returns the N1 value of the truncated to N2 decimal digits, n2 the default setting of 0, and when N2 is the default, the N1 is truncated to an integer, and if the N2 is negative, it is truncated to the corresponding bit to the left of the decimal point.
295
296 single-line date function
297
298 single-line date functions manipulate data data types, most of which have data type parameters, and the vast majority of returns are also values of data types.
299
Add_months (, <i>)
301 returns the Date D plus I after the result of the month. I can make any integer. If I is a decimal, then the database converts the implicit form to an integer, which will intercept the part following the decimal point.
302
303 Last_day ()
304 function returns the last day of the month containing date D
305
306 Months_between (,)
307 returns the number of months between D1 and D2, if the date of the D1 and D2 is the same, or all make the last day of the month, then an integer is returned, otherwise the result returned will contain a fraction.
308
309 New_time (,,)
310 D1 is a date data type that returns the date and time in the time zone tz2 when the date and time in the zone tz1 are d. TZ1 and TZ2 strings.
311
312 Next_day (,)
313 Returns the first day of the condition given by Dow after date D, Dow uses the language given in the current session to specify a day of the week that returns the same time component as D.
314
315select next_day (' 01-jan-2000 ', ' Monday ') "1st Monday", Next_day (' 01-nov-2004 ', ' Tuesday ') +7 "2nd Tuesday") from dual;
316
3171st Monday 2nd Tuesday
318
31903-jan-2000 09-nov-2004
320
321 ROUND ([,])
322 rounds the Date d in the format specified by FMT, FMT as a string.
323
324 Syadate
The 325 function has no arguments and returns the current date and time.
326
327 TRUNC ([,])
328 returns the date d of the unit specified by FMT.
329
330 single-line conversion functions
331
332
prev [1] [2] [3] [4] Next page
The Oracle tutorials being looked at are: Oraclepl/sql single-line functions and group functions. Single-line conversion functions are used to manipulate multiple data types and to convert between data types.
333
334 Chartorwid ()
335 C makes a string that functions to convert C to Rwid data type.
336
337SELECT test_id from Test_case where Rowid=chartorwid (' aaaa0saacaaaaliaaa ')
338
339 CONVERT (, [,])
The C tail string, Dset, Sset is two character sets, the function converts string C from the Sset character set to the Dset character set, and the Sset defaults to the database's character set.
341
342 Hextoraw ()
343 X is a 16-binary string that converts a 16-in X to a raw data type.
344
345 Rawtohex ()
346 x is a raw data type string that converts the raw data class to a 16-binary data type.
347
348 Rowidtochar ()
The 349 function converts the ROWID data type to the char data type.
350
351 To_char ([[,)
352 x is a data or number datatype that converts x to the char data type FMT The specified format, if x is the date Nlsparm=nls_date_language control the language of the month and day that is returned. If x is the numeric nlsparm=nls_numeric_characters used to specify the decimal and thousand separator characters, and the currency symbol.
353
354nls_numeric_characters = "DG", nls_currency= "string"
355
356 To_date ([, [,)
357 C represents a string, FMT represents a special form of string. Returns the language used by the C,nlsparm represented in FMT format. function converts the string C to a date data type.
358
359 To_multi_byte ()
360 C represents a string that converts a burden-truncated character of C into multibyte characters.
361
362 To_number ([, [,)
363 C Represents a string, FMT represents a special format string, and the function return value is displayed in the format specified by FMT. Nlsparm represents the language, and the function returns the number represented by C.
364
365 To_single_byte ()
366 converts a multibyte character in string C to an equivalent single-byte character. This function is used only if the database character set contains both Single-byte and multibyte characters.
367
368 Other Single-line functions
369
370 Bfilename (
371,)
372 dir is a directory-type object with file name. function returns an empty bfile positional value indicator, which is used to initialize bfile variables or bfile columns.
373
374 DECODE (,, [,,, [])
375 x is an expression, M1 is a matching expression, X is compared to M1, if M1 equals X, then return r1, otherwise, X is compared with M2, and so on M3,M4,M5. Until there is a return result.
376
377 DUMP (, [, [, [,]]]
378 x is an expression or character that FMT represents 8, 10, 16, or single characters. function returns a value that contains the VARCHAR2 type of internal representation information about X. If N1,N2 is specified, the byte with a length of N2 starting from N1 will be returned.
379
380 Empty_blob ()
381 the function has no arguments, and the function returns an empty BLOB position indicator. function to initialize a BLOB variable or BLOB column.
382
383 Empty_clob ()
384 the function has no arguments, and the function returns an empty CLOB position indicator. function to initialize a CLOB variable or CLOB column.
385
386 Greatest ()
387 exp_list is a column expression that returns the largest expression in which each expression is implicitly converted to the data type of the first expression, and if the first expression is any of the string data type, the result returned is the VARCHAR2 data type. Comparisons that are used at the same time are non padded space types.
388
389 least ()
390 Exp_list is a column expression that returns the smallest expression in which each expression is implicitly converted to the data type of the first expression, and if the first expression is any of the string data types, the result returned is the VARCHAR2 data type. Comparisons that are used at the same time are non padded space types.
391
392 UID
393 the function has no arguments and returns an integer that uniquely identifies the current database user.
394
395 USER
396 returns the user name of the current user
397
398 USERENV ()
399 based on OPT return contains the current session information. Optional values for opt are:
400
401 a sysdba-foot-color response in a ISDBA session, returns True
402 SESSIONID return Audit session identifier
403 ENTRYID returns the available audit entry identifiers
404 INSTANCE Returns the instance identifier after the session is connected. This value is used only when running the parallel server and with multiple instances.
405 LANGUAGE Returns the character set for language, geography, and database settings.
406 LANG returns the ISO abbreviation for the language name.
407 TERMINAL Returns the operating system identifier for the terminal or computer used for the current session.
408
409 vsize ()
410 x is an expression. Returns the number of bytes represented within X.
411
Group functions in 412 SQL
413
The 414 group functions, also called aggregate functions, return a single result based on multiple rows, and the exact number of rows cannot be determined unless the query is executed and all the results are included. Unlike a single-line function, all rows are known at parse time. Because of this difference, there is a slight difference in the requirements and behavior between the group function and the Single-line function.
415
416 groups (multiple lines) function
417
418 Oracle provides a rich array-based, multiline function compared to a single-line function. These functions can be used in the HAVING clause of a select or select and are often used with group by when used for select substrings.
419
420 AVG ([{disyinct| All}])
421 returns the average of the numeric value. The default setting is all.
422
423SELECT avg (SAL), avg (all Sal), avg (DISTINCT sal) from Scott.emp
424
425AVG (SAL) AVG (all sal) avg (DISTINCT sal)
426
4271877.94118 1877.94118 1916.071413
428
429
430 COUNT ({*| Distinct| All})
431 returns the number of rows in the query, the default setting is all,* to return all rows.
432
433 MAX ([{distinct| All}])
434 returns the maximum value of the select list item, if x is a string data type, he returns a VARCHAR2 data type, if x is a data type, returns a date, if X is a numeric data type, returns a number. Note that distinct and all do not work, and the maximum value should be the same as the two settings.
435
436 MIN ([{distinct| All}])
437 returns the minimum value for the SELECT list item.
438
439 StdDev ([{distinct| All}])
440 returns the standard deviation of the list item of the selected person, the so-called standard deviation is the square root of the variance.
441
442 SUM ([{distinct| All}])
443 returns the sum of the values for the select list item.
444
445 variance ([{distinct| All}])
446 returns the statistical variance of the selected list item.
447
448 grouping data with GROUP by
449
450 as the topic implies, the group function is to manipulate the data that has been grouped together, and we tell the database how to group or categorize the data using group by, and when we use the groups function in the SELECT clause of the SELECT statement, we must place the grouping or the very number of columns on the group By clause, the default classification is to set the entire result to a class if it is not specifically handled with group by.
451
452select stat,counter (*) Zip_count from Zip_codes GROUP by state;
453
454ST Zip_count
455-----------
456AK 360
457AL 1212
458AR 1309
459AZ 768
460CA 3982
461
462 in this example, we classify the state field, and if we want to sort the results by zip_codes, we can use the order BY statement, and the ORDER BY clause can work with the column or group function.
463
464sel
prev [1] [2] [3] [4] Next page
The Oracle tutorials being looked at are: Oraclepl/sql single-line functions and group functions. The ECT stat,counter (*) Zip_count from the Zip_codes GROUP by (*) DESC;
465
466ST COUNT (*)
467----------
468NY 4312
469PA 4297
470TX 4123
471CA 3982
472
473 restricting grouped data with having clauses
474
475 Now you already know. Using the main function in the SELECT statement and ORDER BY clause of the query, the group function can only be used in two substrings, and the group function cannot be used in the where substring, for example, the following query is wrong:
476
477 Error
478SELECT Sales_clerk,sun (sale_amount) from Gross_sales WHERE sales_dept= ' OUTSIDE ' and SUM (sale_amount) >10000 GROUP by Sales_clerk
479
480
481 in this statement the database does not know what sum is, and when we need to instruct the database to group rows and then limit the output of the grouped rows, the correct approach is to use the having statement:
482
483SELECT Sales_clerk,sun (Sale_amount)
484FROM Gross_sales
485WHERE sales_dept= ' OUTSIDE '
486GROUP by Sales_clerk
487HAVING SUM (Sale_amount) >10000;
488
489 nested functions
490
491 functions can be nested. The output of one function can be the input of another function. The operand has an inheritable execution process. But the precedence of the function is based only on the position, and the function follows the principle from the inside out to the left to the right. Nesting techniques are commonly used for functions such as decode that can be used to if.thenelse logical judgment statements.
492
493 nested functions can include nested single-line functions in a group function, or a group function nested into a single line function or group function. For example, the following examples:
494
495SELECT Deptno, Greatest (count (DISTINCT job), COUNT (DISTINCT Mgr) CNT,
496COUNT (DISTINCT job) jobs,
497COUNT (DISTINCT Mgr) Mgrs
498FROM EMP
499GROUP by Deptno;
500
501DEPTNO CNT JOBS MGRS
502-----------------
50310 4 4 2
50420 4 3 4
50530 3 3 2
-->
Previous page
prev [1] [2] [3] [4]
