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]