Detailed description of OraclePL/SQL single-row functions and group functions

The ORACLE tutorial is: OraclePL/SQL single-row functions and group functions. A 1 function is a program with zero or multiple parameters and a return value. Oracle has a series of built-in functions in SQL, which can be called SQL or PL/SQL statements. functions are mainly divided into two categories:
2
3 single row Functions
4
5 groups of functions
6
7. This article will discuss how to use single-row functions and rules.
8
9 single row functions in SQL
10
11 SQL and PL/SQL contain many types of functions, including character, number, date, conversion, and hybrid functions for processing single row data, therefore, these functions can be collectively referred to as single-row functions. These functions can be used in clauses such as SELECT, WHERE, and order by. For example, the following example contains single-row functions such as TO_CHAR, UPPER, and SOUNDEX.
12
13 SELECT ename, TO_CHAR (hiredate, 'day, DD-Mon-YYYY ')
14 FROM emp
15 Where UPPER (ename) Like 'al %'
16 order by soundex (ename)
17
18 single-row functions can also be used in other statements, such as the SET clause of update, the VALUES clause of INSERT, And the WHERE clause of DELET. During the certification exam, pay special attention to using these functions in SELECT statements, so our attention is also concentrated in the SELECT statement.
19
20 NULL and single row Functions
21
22. It is very difficult to understand NULL, and even a very experienced person is still confused about it. NULL indicates an unknown data or a NULL value. Any operand of the arithmetic operator is NULL, and the result is a NULL value. This rule is also applicable to many functions, only CONCAT, DECODE, DUMP, NVL, and REPLACE can return non-NULL values when the NULL parameter is called. Among these functions, NVL is the most important because it can directly process NULL values. NVL has two parameters: NVL (x1, x2), x1, and x2 expressions, if x1 is null, return X2; otherwise, return x1.
23
24. Let's take a look at the emp data table. It includes two items: salary and bonus. We need to calculate the total compensation.
25
26 column name emp_id salary bonus
27
28key type pk
29 nulls/unique nn, u nn
30fk table
31 datatype number
32 length 11.2 11.2
33
34. You can simply add up the salary and bonus. If a row is null, the result will be null. For example:
35
36 update emp
37 set salary = (salary + bonus) * 1.1
38
39 in this statement, the employee's salary and bonus will be updated to a new value, but if there is no bonus, that is, salary + null, then the wrong conclusion will be drawn, in this case, the nvl function is used to exclude the impact of null values.
40. The correct statement is:
41
42 update emp
43 set salary = (salary + nvl (bonus, 0) * 1.1
44
45 single-line string functions
46
47 single-line string functions are used to manipulate string data. Most of them have one or more parameters, and most of them return strings.
48
49 ASCII ()
50 c1 is a string and returns the ASCII code of the first letter of c1. Its Inverse Function is CHR ()
51
52 select ascii ('A') BIG_A, ASCII ('Z') BIG_z FROM emp
53
54BIG_A BIG_z
5565 122
56
57 CHR (<I>) [NCHAR_CS]
58 I is a number. The function returns a decimal character.
59
60 select CHR (65), CHR (122), CHR (223) FROM emp
61
62CHR65 CHR122 CHR223
63A z B
64
65 CONCAT (,)
66 c1 and c2 are strings. The function connects c2 to the end of c1. If c1 is null, c2. if c2 is null, c1 is returned. If c1 and c2 are both null, returns null. He and operator | the returned result is the same
67
68 select concat ('slobo', 'svoboda') username from dual
69
70 username
71
72 slobo Syoboda
73
74
75 INITCAP ()
76 c1 is a string. The function returns the first letter of each word in upper case and other letters in lower case. Words are limited by spaces, control characters, and punctuation marks.
77
78 select INITCAP ('Veni, vedi, vici') Ceasar from dual
79
80 Ceasar
81
82 Veni, Vedi, Vici
83
84
85 INSTR (, [, <I> [,])
Both c1 and c2 are strings. I and j are integers. The function returns the position where c2 appears for the nth occurrence of c1, and searches for the position starting from the nth occurrence of c1. If no expected character is found, 0 is returned. If I is a negative number, the search proceeds from right to left, but the position is calculated from left to right, the default values of I and j are 1.
87
88 select INSTR ('Mississippi ',' I ', 3, 3) from dual
89
90 INSTR ('Mississippi ',' I ', 3, 3)
91
9211
93
94 select INSTR ('Mississippi ',' I ',-2, 3) from dual
95
96 INSTR ('Mississippi ',' I ', 3, 3)
97
982
99
100
101 10000b (, [, I [, j])
102 is the same as the INSTR () function, but it only returns bytes. For a single byte, bytes B () is equal to INSTR ()
103
104 LENGTH ()
105 c1 is a string and the length of c1 is returned. If c1 is null, null is returned.
106
107 select LENGTH ('ipso facto') ergo from dual
108
109 ergo
110
11110
112
113 LENGTHb ()
114 is returned in bytes like LENGTH.
115
116 lower ()
117 return lower-case characters of c, often appearing in the where substring
118
119 select LOWER (colorname) from itemdetail where lower (colorname) LIKE '% white %'
120
121 COLORNAME
122
123 Winterwhite
124
125
126 LPAD (, <I> [,])
127 c1, c2 are strings, and I is an integer. On the left side of c1, use the c2 string to supplement the length I, which can be repeated multiple times. If I is less than the length of c1, only the c1 characters that are as long as I are returned, and the others are truncated. The default value of c2 is single space. See RPAD.
128
129 select LPAD (answer, 7, '') padded, answer unpadded from question;
130
131 PADDED UNPADDED
132
133Yes Yes
134NO NO
135 Maybe maybe
136
137
138 LTRIM (,)
13

[1] [2] [3] [4] Next page

The ORACLE tutorial is: OraclePL/SQL single-row functions and group functions. 9 remove the leftmost character from c1 so that the first character is not in c2. If there is no c2 character, c1 will not change.
140
141 select LTRIM ('Mississippi ', 'mis') from dual
142
143LTR
144
145ppi
146
147 RPAD (, <I> [,])
148 fill in the length I with the c2 string on the right side of c1, which can be repeated multiple times. If I is less than the length of c1, only the c1 characters as long as I are returned, and the others will be truncated. The default value of c2 is a single space. Others are similar to LPAD.
149
150 RTRIM (,)
151 remove the rightmost character from c1 so that the last character is not in c2. If there is no c2 character, c1 will not change.
152
153 REPLACE (, [,])
154 c1, c2, and c3 are strings. The function uses c3 instead of c2 that appears in c1 and returns the result.
155
156 select REPLACE ('uptown', 'up', 'low') from dual
157
158 REPLACE
159
160 downtown
161
162 STBSTR (, <I> [,])
163 c1 is a string, where I and j are integers. Starting from the I-bit of c1, a substring with the length of j is returned. If j is empty, it is returned until the end of the string.
164
165 select SUBSTR ('message',) from dual
166
167 SUBS
168
169 Mess
170
171
172 SUBSTRB (, <I> [,])
173 is roughly the same as SUBSTR, except that I and J are calculated in bytes.
174
175 SOUNDEX ()
176 return words with the same pronunciation as c1
177
178 select SOUNDEX ('dawes') dawes SOUNDEX ('daws') daws, SOUNDEX ('dawson') from dual
179
180 Dawes Daws Dawson
181
182D200 D200 D250
183
184 TRANSLATE (,,)
185 Replace the characters in c1 and c2 with c3
186
187 select TRANSLATE ('fumble ', 'U', 'ar') test from dual
188
TEXT 189
190
191 ramble
192
193 TRIM ([[] from c3)
194 Delete the first, last, or both of the c3 strings.
195
196 select TRIM ('space padded') trim from dual
197
198 TRIM
199
200 space padded
201
202 UPPER ()
203 return the uppercase value of c1, which is often in the where substring.
204
205 select name from dual where UPPER (name) LIKE 'Ki %'
206
207 NAME
208
209 KING
210
211 single-row numeric Functions
212
213 single-row numeric functions operate on numeric 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 rather than degrees. oracle does not provide built-in radians and angle conversion functions.
214
215 ABS ()
216 returns the absolute value of n.
217
218 ACOS ()
219 return the number between-1 and 1 for the anti-Yuxuan function. N indicates radians
220
221 select ACOS (-1) pi, ACOS (1) zero from dual
222
223PI ZERO
224
2253.14159265 0
226
227 ASIN ()
228 The antizheng Xuan function returns-1 to 1, and n indicates the radian.
229
230 ATAN ()
231 arc tangent function, returns the arc tangent value of n, and n indicates the radian.
232
233 CEIL ()
234 returns the smallest integer greater than or equal to n.
235
236 COS ()
237 returns the remainder Xuan value of n, and n is a radian.
238
239 COSH ()
240 returns the hyperbolic remainder of n, where n is a number.
241
242 select COSH (<1.4>) FROM dual
243
244 COSH (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. N must be greater than 0.
256
257 LOG (,)
258 return the base n2 logarithm of n1
259
260 MOD ()
261 returns the remainder of n1 divided by n2,
262
263 POWER (,)
264 returns the n2 power of n1
265
266 ROUND (,)
267 return the n1 value rounded to the right of the decimal point. The default value of n2 is 0. This round returns the nearest integer to the decimal point. If n2 is a negative number, it is rounded to the corresponding digit on the left of the decimal point, n2 must be an integer.

[NextPage]

268
269 select ROUND (12345,-2), ROUND (12345.54321, 2) FROM dual
270
271 ROUND (12345,-2) ROUND (12345.54321, 2)
272
27312300 12345.54
274
275 SIGN ()
276 if n is negative,-1 is returned. If n is positive, 1 is returned. If n = 0, 0 is returned.
277
278 SIN ()
279 returns the positive and negative values of n, and n is a radian.
280
281 SINH ()
282 returns the hyperbolic positive Xuan value of n, and n is a radian.
283
284 SQRT ()
285 returns the square root of n, and n is a radian.
286
287 TAN ()
288 returns the tangent of n, and n is a radian.
289
290 TANH ()
291 returns the hyperbolic tangent of n, and n is a radian.
292
293 TRUNC (,)
294 return the value of n1 from the end to the n2 decimal point. The default value of n2 is 0. When n2 is the default value, the end of n1 is an integer. If n2 is a negative value, it is truncated at the right of the decimal point.
295
296 single row date functions
297
298 single-row date functions operate on DATA types, most of which have DATA type parameters, and most of which return DATA Type values.
299
300 ADD_MONTHS (, <I>)
301 return the result of date d plus I months. I can make any integer. If I is a decimal point, the database implicitly converts it to an integer, and the part after the decimal point is truncated.
302
303 LAST_DAY ()
The 304 function returns the last day of the month containing the date d.
305
306 MONTHS_BETWEEN (,)
307 return the number of months between d1 and d2. If the date of d1 and d2 is the same, or both make the last day of the month, an integer is returned, otherwise, the returned result contains a score.
308
309 NEW_TIME (,,)
310 d1 is a date data type. When the date and time in Zone tz1 are d, the date and time in Zone tz2. Tz1 and tz2 are strings.
311
312 NEXT_DAY (,)
313 return the first day of the condition given by dow after date d. dow specifies a day in a week using the language given in the current session, and returns the same time component as the time component of d.
314
315 select 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 round the date d in the format specified by fmt, and fmt is a string.
323
324 SYADATE
325 if the function does not have a parameter, the current date and time are returned.
326
327 TRUNC ([,])
328 return the date d of the unit specified by fmt.
329
330 single row conversion functions
331
332

Previous Page [1] [2] [3] [4] Next page

The ORACLE tutorial is: OraclePL/SQL single-row functions and group functions. The single-row conversion function is used to operate on multiple data types and convert data types.
333
334 CHARTORWID ()
335 c converts a string to the RWID data type.
336
337 SELECT test_id from test_case where rowid = CHARTORWID ('aaaa0saacaaaaliaaa ')
338
339 CONVERT (, [,])
340 c tail string. dset and sset are two character sets. The function converts string c from the sset character set to the dset character set, and the sset is set to the database character set by default.
341
342 HEXTORAW ()
343 x is a hexadecimal string. The function converts hexadecimal x to the RAW data type.
344
345 RAWTOHEX ()
346 x is a RAW data string. The function converts the RAW data class to a hexadecimal 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 data type. The function converts x to the char data type in the specified format of fmt. If x is the date nlsparm = NLS_DATE_LANGUAGE, the language used to control the returned month and day. If x is the number nlsparm = NLS_NUMERIC_CHARACTERS, it is used to specify the Separators for decimal places and kilobytes, as well as currency symbols.
353
354NLS_NUMERIC_CHARACTERS = "dg", NLS_CURRENCY = "string"
355
356 TO_DATE ([,[,)
357 c indicates a string, and fmt indicates a string in a special format. Returns the c language in the fmt Format. The nlsparm indicates the language used. The function converts string c to the date data type.
358
359 TO_MULTI_BYTE ()
c indicates a string, and the function converts the string truncation character of c to multi-byte characters.
361
362 TO_NUMBER ([,[,)
363 c indicates a string, and fmt indicates a special string. The function return value is displayed in the format specified by fmt. Nlsparm indicates the language, and the function returns numbers represented by c.
364
365 TO_SINGLE_BYTE ()
366 convert multi-byte characters in string c into equivalent single-byte characters. This function is used only when the database character set contains single-byte and multi-byte characters at the same time.
367
368 other single-row Functions
369
370 BFILENAME (
371 ,)
372 dir is a directory object, and file is a file name. The function returns an empty BFILE Location value indicator. The function is used to initialize the BFILE variable or BFILE column.
373
374 DECODE (, [, [])
375 x is an expression, m1 is a matching expression, and x is compared with m1. If m1 is equal to x, r1 is returned. Otherwise, x is compared with m2, and so on, m5. until there is a returned result.
376
377 DUMP (, [, [, [,])
378 x is an expression or character. fmt indicates octal, decimal, hexadecimal, or single character. The function returns a value of the VARCHAR2 type that contains the internal representation of x. If n1 and n2 are specified, bytes starting from n1 with n2 will be returned.
379
380 EMPTY_BLOB ()
381 this function has no parameters. The function returns an empty BLOB location indicator. Function is used to initialize a BLOB variable or BLOB column.
382
383 EMPTY_CLOB ()
384 this function does not have a parameter. The function returns an empty CLOB location indicator. The function is used to initialize a CLOB variable or CLOB column.
385
386 GREATEST ()
387 exp_list is a column expression that returns the largest expression in the column. Each expression is implicitly converted to the Data Type of the first expression. If the first expression is any of the string data types, the returned result is of the varchar2 data type, and the comparison is of the non-padding space type.
388
389 LEAST ()
390 exp_list is a column expression that returns the smallest expression. Each expression is implicitly converted to the Data Type of the first expression. If the first expression is any of the string data types, the returned result is of the varchar2 data type, and the comparison is of the non-padding space type.
391
392 UID
393 If this function does not have a parameter, return an integer that uniquely identifies the current database user.
394
395 USER
396 return the User Name of the current user.
397
398 USERENV ()
399 the message containing the current session is returned Based On opt. The optional value of opt is:
400
401 SYSDBA role response in ISDBA session, returns TRUE
402 SESSIONID: Audit Session identifier returned
403 ENTRYID returns available audit item identifiers
404 after a session is connected, the INSTANCE identifier is returned. This value is only used when the Parallel server is running and multiple instances exist.
405 LANGUAGE: the character set for the LANGUAGE, region, and database.
406 LANG returns the ISO abbreviation of the language name.
407 TERMINAL indicates the identifier of the operating system returned by the TERMINAL or computer used by the current session.
408
409 VSIZE ()
410 x is an expression. Returns the number of bytes in x.
411
412 group functions in SQL
413
A group of functions is also called a set function. If a single result based on multiple rows is returned, the exact number of rows cannot be determined unless the query is executed and all results are included. Different from a single-row function, all rows are known during parsing. Due to this difference, the requirements and behaviors of group functions and single-row functions are slightly different.
415
416 group (multi-row) Functions
417
418 compared with single-row functions, oracle provides a wide range of group-based, multi-row functions. These functions can be used in select or select having clauses. They are often used together with group by when used for select substrings.
419
420 AVG ([{DISYINCT | ALL}])
421 returns the average value of the value. The default value is ALL.
422
423 select avg (sal), AVG (ALL sal), AVG (DISTINCT sal) FROM scott. emp
424
Incluavg (SAL) AVG (all sal) AVG (distinct sal)
426
4271877.94118 1877.94118 1916.071413
428
429
430 COUNT ({* | DISTINCT | ALL })
431 return the number of rows in the query. The default value is ALL. * indicates that ALL rows are returned.
432
433 MAX ([{DISTINCT | ALL}])
434 return the maximum value of the selected list item. If x is a string DATA type, a VARCHAR2 DATA type is returned. If X is a DATA type, a date is returned, if X is of the numeric data type, a number is returned. Note that distinct and all do not work. The maximum value must be the same as the two settings.
435
436 MIN ([{DISTINCT | ALL}])
437 return the minimum value of the selected list item.
438
439 STDDEV ([{DISTINCT | ALL}])
440 return the standard deviation of the selected item list. The so-called standard deviation is the square root of the variance.
441
442 SUM ([{DISTINCT | ALL}])
443 return the total number of items in the selected list.
444
445 VARIANCE ([{DISTINCT | ALL}])
446 return the statistical variance of the selected items.
447
448 use group by to GROUP data
449
450 as the question implies, a GROUP function is used to operate data that has already been divided into groups. We will tell the database how to GROUP or classify data using group, when we use GROUP functions in the SELECT clause of the SELECT statement, we must place grouping or extraordinary series in the group BY clause. If we do not use GROUP by for special processing, the default classification is to set the entire result to a class.
451
452 select stat, counter (*) zip_count from zip_codes group by state;
453
454ST ZIP_COUNT
455 -----------
456AK
45 7Al 1212
458AR 1309
459AZ 768
460CA 3982
461
462 in this example, we use the state field for classification. If we want to sort the results BY zip_codes, we can use the order by statement. The order by clause can use columns or group functions.
463
464sel

Previous Page [1] [2] [3] [4] Next page

The ORACLE tutorial is: OraclePL/SQL single-row functions and group functions. Ect stat, counter (*) zip_count from zip_codes group by state order by count (*) DESC;
465
466ST COUNT (*)
467 ----------
468NY 4312
469 PA 4297
470TX 4123
471CA 3982
472
473 use HAVING clause to restrict grouped data
474
475 now you know that you can use the primary function in the SELECT statement and order by clause of the query. Group functions can only be used in two substrings, and group functions cannot be used in WHERE substrings, for example, the following query is incorrect:
476
Error 477
478 SELECT sales_clerk, SUN (sale_amount) FROM gross_sales WHERE sales_dept = 'outline' and sum (sale_amount)> 10000 group by sales_clerk
479
480
481 in this statement, the database does not know what SUM () is. When we need to instruct the database to group rows and then limit the output of rows after the grouping, the correct method is to use the HAVING statement:
482
483 SELECT sales_clerk, SUN (sale_amount)
484 FROM gross_sales
485 WHERE sales_dept = 'outside'
486 group by sales_clerk
487 having sum (sale_amount)> 10000;
488
489 nested Functions
490
491 functions can be nested. The output of a function can be the input of another function. The operands have an inherited execution process. However, the priority of a function is only based on the position, and the function follows the principle from inside to outside, from left to right. Nesting is generally used for functions such as DECODE that can be used to logically determine the statement IF. THENELSE.
492
493 nested functions can include nested single-row functions in group functions, or nested group functions into single-row functions or group functions. For example:
494
495 SELECT deptno, GREATEST (COUNT (DISTINCT job), COUNT (DISTINCT mgr) cnt,
496 COUNT (DISTINCT job) jobs,
497 COUNT (DISTINCT mgr) mgrs
498 FROM emp
499 group by deptno;
500
501 DEPTNO CNT JOBS MGRS
502 -----------------
50310 4 4 2
50420 4 3 4
50530 3 3 2

Previous Page

Previous Page [1] [2] [3] [4]