Oracle faq one thousand [201 to 300]

201./* + NOCACHE (TABLE )*/
When a full table scan is performed, the CACHE prompts that the retrieval block of the table can be placed in the nearest least recently used LRU list in the buffer CACHE.
For example:
SELECT/* + FULL (BSEMPMS) NOCAHE (BSEMPMS) */EMP_NAM from bsempms;
202./* + APPEND */
Insert directly to the end of the table to increase the speed.
Insert/* + append */into test1 select * from test4;
203./* + NOAPPEND */
By stopping parallel mode during the lifetime of the insert statement, you can start regular insert.
Insert/* + noappend */into test1 select * from test4;
ORACLE internal functions
204. How do I obtain the ASCII value of the first character of a string?
ASCII (CHAR)
Select ascii (ABCDE) from dual;
Result: 65
205. How do I obtain the characters specified by the value N?
CHR (N)
Select chr (68) from dual;
Result: D
206. How to connect two strings?
CONCAT (CHAR1, CHAR2)
Select concat (ABC, DEFGH) from dual;
Result: ABCDEFGH
207. How to replace the value in a column with a string?
DECODE (CHAR, N1, CHAR1, N2, CHAR2 ...)
Select decode (DAY, 1, SUN, 2, MON) from dual;
208. INITCAP (CHAR)
The first character of the character string CHAR is in upper case, and the rest are in lower case.
Select initcap (ABCDE) from dual;
209. LENGTH (CHAR)
Returns the length of a string CHAR.
Select length (ABCDE) from dual;
210. LOWER (CHAR)
Converts all CHAR strings to lowercase letters.
Select lower (ABCDE) from dual;
211. LPAD (CHAR1, N, CHAR2)
Enter CHAR1 on the left of the character string CHAR2 to set its length to N.
Select lpad (ABCDEFG, 10123) from dual;
Result: 123 ABCDEFG
212. LTRIM (CHAR, SET)
Remove the characters in the string SET from the left of the string CHAR until the first character is not in the SET.
SELECT (CDEFG, CD) from dual;
Result: EFG
213. NLS_INITCAP (CHAR)
Take the first character of CHAR in upper case, and the remaining characters in lower case.
SELECT NLS_INITCAP (ABCDE) from dual;
214. NLS_LOWER (CHAR)
All characters in the CHAR string are in lowercase.
SELECT NLS_LOWER (AAAA) from dual;
215. NLS_UPPER (CHAR)
All characters in the string CHAR are capitalized.
SELECT NLS_UPPER (AAAA) from dual;
216. REPLACE (CHAR1, CHAR2, CHAR3)
Use the character string CHAR3 to replace the CHAR2 column. The result is placed in CHAR1.
Select replace (emp_no, 123,456) from dual;
217. RPAD (CHAR1, N, CHAR2)
Use the character string CHAR2 to enter CHAR1 in the right and set it to N in length.
Select rpad (234,8, 0) from dual;
218. RTRIM (CHAR, SET)
Remove the character in the character SET on the right of the character string CHAR until the last character is not in the SET.
Select rtrim (ABCDE, DE) from dual;
219. SUBSTR (CHAR, M, N)
The string CHAR starts from M and contains N double-byte characters. One Chinese character is a character.
Select substr (ABCDE, 2, 3) from dual;
220. SUBSTRB (CHAR, M, N)
Obtain the N characters starting from M in the string CHAR. double byte characters. One Chinese character is two characters.
Select substrb (ABCDE, 2, 3) from dual;
221. TRANSLATE (CHAR1, CHAR2, CHAR3)
Replace CHAR2 in CHAR1 with CHAR3.
Select translate (ABCDEFGH, DE, MN) from dual;
222. UPPER (CHAR)
Converts all CHAR strings to uppercase.
223. ADD_MONTHS (D, N)
Increase N months to the D-day period.
SELECT ADD_MONTHS (SYSDATE, 5) from dual;
224. LAST_DAY (D)
Obtain the date of the last day of the month that contains the dday period.
SELECT LAST_DAY (SYSDATE) from dual;
225. MONTH_BETWEEN (D1, D2)
Obtain the number of months between two dates.
SELECT MONTH_BETWEEN (D1, D2) from dual;
226. NEXT_DAY (D, CHAR)
Get the date of the first Sunday named by CHAR later than date D.
SELECT NEXT_DAY (TO_DATE (2003/09/20), SATDAY) from dual;
227. ROUNT (D, FMT)
Obtains the most advanced date rounded to by the specified FMT mode.
Select rount (2003/09/20, MONTH) from dual;
228. SYSDATE
Obtain the date and time of the current system.
Select sysdate from dual;
229. TO_CHAR (D, FMT)
String that converts the date D to FMT.
SELECT TO_CHAR (SYSDATE, YYYY/MM/DD) from dual;
230. TO_DATE (CHAR, FMT)
Convert the string CHAR to a date in FMT Format.
SELECT TO_DATE (2003/09/20, YYYY/MM/DD) from dual;
231. ABS (N)
Obtain the absolute value of N.
Select abs (-6) from dual;
232. CEIL (N)
Obtain the maximum integer greater than or equal to N.
Select ceil (1, 5.6) from dual;
233. COS (N)
Returns the cosine of N.
Select cos (1) from dual;
234. SIN (N)
Returns the sine of N.
Select sin (1) from dual;
235. COSH (N)
Returns the hyperbolic cosine of N.
Select cosh (1) from dual;
236. EXP (N)
Obtain the N power of N's E.
Select exp (1) from dual;
237. FLOOR (N)
Returns the smallest integer less than or equal to N.
Select floor (5.6) from dual;
238. LN (N)
Obtain the natural logarithm of N.
Select ln (1) from dual;
239. LOG (M, N)
Obtain the base N logarithm of M.
Select log (2, 8) from dual;
240. MOD (M, N)
Returns the remainder of M divided by N.
Select mod (100,7) from dual;
241. POWER (M, N)
Obtain the N power of M.
Select power (4, 3) from dual;
242. ROUND (N, M)
Round N to M digits after the decimal point.
SELECT (78.87653, 2) from dual;
243. SIGN (N)
-1 is obtained when N is <0;
When N> 0, 1 is obtained;
When N = 0, 0 is obtained;
Select sign (99) from dual;
244. SINH (N)
Returns the hyperbolic sine of N.
Select sinh (1) from dual;
245. SORT (N)
Obtain the square root of N, N> = 0.
Select sort (9) from dual;
246. TAN (N)
Returns the tangent of N.
Select tan (0) from dual;
247. TANH (N)
Returns the hyperbolic tangent of N.
Select tanh (0) from dual;
248. TRUNC (N, M)
Obtain the N value truncated at the M position.
Select trunc (7.7788, 2) from dual;
249. COUNT ()
Calculate the number of records that meet the condition.
Select count (*) FROM TABLE1 WHERE COL1 = AAA;
250. MAX ()
Calculates the maximum value for the specified column.
Select max (COL1) FROM TABLE1;
251. MIN ()
Calculates the minimum value for the specified column.
Select min (COL1) FROM TABLE1;
252. AVG ()
Calculates the average value for the specified column.
Select avg (COL1) FROM TABLE1;
253. SUM ()
Calculate the sum of columns.
Select sum (COL1) from dual;
254. TO_NUMBER (CHAR)
Converts a character to a numeric value.
SELECT TO_NUMBER (999) from dual;
255. CHARTOROWID (CHAR)
Convert the CHAR or VARCHAR2 values that contain the external syntax ROWID to the internal binary syntax. The CHAR parameter must be the ROWID that contains the external syntax.

.
Select name from bsempms where rowid = CHARTOROWID (AAAAfZAABAAACp8AAO );
NAME: LEIXUE
256. CONVERT (CHAR, DEST_CHAR_SET, SOURCE_CHAR_SET)
CONVERT Converts the characters in the string CHAR from the character set identified by SOURCE_CHAR_SET to the character set identified by DEST_CHAR_SET.
Select convert (GroB, US7ASCII, WE8HP) conversion from pubs;
CONVERSION: Gross
257. HEXTORAW (CHAR)
Converts a hexadecimal CHAR into a RAW value.
Insert into bsempms (RAW_COLUMN) select hextoraw (7D) from test;
258. RAWTOHEX (RAW)
Convert the RAW value to a CHAR value that contains the hexadecimal format.
Select rawtohex (RAW_COLUMN) conversion from bsempms;
CONVERSION: 7D
259. ROWIDTOCHAR (ROWID)
Converts a ROWID value to the VARCHAR2 data type.
Select rowid from bsempms where rowidtochar (ROWID) LIKE % BR1AAB %;
260. TO_MULTI_BYTE (CHAR)
Converts a single byte in CHAR to an equivalent multi-byte character.
SELECT TO_MULTI_BYTE (ASFDFD) from test;
261. TO_SINGLE_BYTE (CHAR)
Converts multiple bytes in CHAR to an equivalent single-byte character.
SELECT TO_SINGLE_BYTE (ASFDFD) from test;
262. translate using (text using {CHAR_CS | NCHAR_CS })
Convert TEXT to the database character set and national character set according to the specified conversion method.
TEXT is to be converted.
The USING CHAR_CS parameter is converted to the database character set TEXT, and the output data type is VARCHAR2.
The USING NCHAR_CS parameter is converted to the database character set TEXT, and the output data type is NVARCHAR2.
Create table test (CHAR_COL CHAR (20), NCHAR_COL NCHAR (20 ));
Insert into test values (HI, NBYE );
SELECT * from test;
263. DUMP (EXPR, RETURN_FORMAT, START_POSITION, LENGTH)
Returns a VARCHAR2 value that contains internal information such as data type code and byte length. The returned result is the current database character set and data type.

Return the data according to the encoding of the internal data type as a number as follows:
Code Data Type
0 VARCHAR2
1 NUMBER
8 LONG
12 DATE
23 RAW
24 LONG RAW
69 ROWID
96 CHAR
106 MSSLABEL
The RETUEN_FORMAT parameter specifies the value returned according to the base number below.
RETURN_FORMAT RESULT
Octal
10 decimal
Hexadecimal
17 single character representation
If the RETURN_FORMAT parameter is not specified, return in decimal format.
If START_POSITION and LENGTH are specified, bytes starting from START_POSITION with LENGTH will be returned.

Return integer.
Select dump (ABC, 1016) from test;
Select dump (ename, 8, 3, 2) example from emp where name = ccbzzp;
264. empty_ B | clob ()
Returns an empty LOB locator, which is used to initialize the LOB variable, or to initialize the LOB column or