201. / * nocache (Table) * /
When the full-use end of the full-table scanning table LRU is performed, the Cache prompts to place the search block of the table in the buffer cache.
E.g:
SELECT / * FULL (BSEMPMS) Nocahe (BSEMPMS) * / EMP_NAM from bsempms;
202. / * append * /
Inserted directly into the table, can increase the speed.
INSERT / * APPEND * / INTO TEST1 SELECT * "
203. / * noApppend * /
Start regular insert by stopping parallel mode within the insertion statement survival.
INSERT / * NoAppnd * / INTO TEST1 SELECT * AROM TEST4;
Oracle internal function
204. How to get the ASCII value of the first character of the string?
ASCII (Char)
SELECT ASCII ('Abcde') from Dual;
Result: 65
205. How do I get the character specified by 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 values in the column as 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 string char is uppercase, the rest is lowercase.
SELECT INITCAP ('Abcde') from Dual;
209. Length (char)
Take a string char length.
SELECT Length ('Abcde') from Dual;
210. Lower (char)
The string char is all lowercase.
SELECT LOWER ('Abcde') from Dual;
211. LPAD (Char1, N, Char2)
Use the character string Char2 to fill the left fill the left fills CHAR1 to make its length N.
SELECT LPAD ('Abcdefg', 10'123 ') from Dual;
Result: '123ABCDEFG'
212. LTRIM (Char, SET)
From the string char. The left side is removed from the character in the string set until the first one is not the character in the set.
SELECT ('cdefg', 'cd') from dual
Result: 'EFG'
213. NLS_INITCAP (CHAR)
Take the first character of Charity char, the rest of the characters.
SELECT NLS_INITCAP ('Abcde') from Dual;
214. NLS_Lower (char)
All characters including string Char are written.
SELECT NLS_LOWER ('AAAA') from Dual; 215. NLS_UPPER (CHAR)
All characters included in the string Char are all capitalized.
Select NLS_UPPER ('AAAA') from Dual;
216. Replace (Char1, Char2, Char3)
With a string char3 instead of each column of each column value of CHAR2, the result is placed in Char1.
SELECT Replace (EMP_NO, '123', '456') from Dual;
217. RPAD (Char1, N, Char2)
Use a string char2 to right to right, and the length is N.
SELECT RPAD ('234', 8, '0') from dual;
218. RTRIM (Char, SET)
Remove the characters in the string set on the right side of the string CHAR until the last one is not a character in the set.
SELECT RTRIM ('Abcde', 'DE') from dual;
219. Substr (Char, M, N)
Get the n characters that start from the string char. Double-byte characters, one Chinese character is a character.
SELECT SUBSTR ('Abcde', 2, 3) from Dual;
220. SubstRB (Char, M, N)
Get the n characters starting at 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 the portion of Char2 in Char1 with CHAR3.
Select Translate ('Abcdefgh', 'DE', 'Mn') from DUAL;
222. Upper (char)
All string char is all uppercase.
223. Add_MontHS (D, N)
Add N months to the D date.
SELECT Add_MontHS (Sysdate, 5) from Dual;
224. Last_Day (D)
Get the date of the last day of the month containing the D date.
SELECT LAST_DAY (SYSDATE) from DUAL
225. Month_between (D1, D2)
Get 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 than the date D night.
Select next_day (to_date ('2003/09/20'), 'satday') from dual;
227. Rount (D, FMT)
Get the most intake date rounded by the specified mode FMT.
Select Rount ('2003/09/20', Month) from Dual;
228. sysdate
Get the date and time of the current system.
SELECT SYSDATE from DUAL;
229. TO_CHAR (D, FMT) converts the date D to the FMT string.
Select to_char (sysdate, 'yyyy / mm / dd') from dual;
230. TO_DATE (CHAR, FMT)
Convert the string char to the FMT format.
SELECT TO_DATE ('2003/09/20', 'YYYY / MM / DD') from Dual;
231. ABS (N)
Gets absolute value of N.
SELECT ABS (-6) from Dual;
232. CEIL (N)
A maximum integer greater than or equal to N is obtained.
SELECT CEIL (5.6) from Dual;
233. COS (N)
Got the cosine value of n.
SELECT COS (1) from dual;
234. Sin (N)
Gets the sinusoidal value of n.
Select sin (1) from dual;
235. Cosh (n)
Gets the hyperbolic cosine value of N.
SELECT COSH (1) from dual;
236. Exp (n)
Get N power of the E of N.
SELECT Exp (1) from dual;
237. Floor (n)
A minimum integer less than or equal to N is obtained.
Select floor (5.6) from dual;
238. ln (n)
Get N natural logarithm.
Select ln (1) from dual;
239. log (m, n)
The logarithm of the bottom N is obtained.
Select log (2,8) from dual;
240. MOD (M, N)
The amount of M was removed in N.
SELECT MOD (100, 7) from Dual;
241. Power (M, N)
Gets the n power of M.
Select Power (4, 3) from Dual;
242. Round (n, m)
Round N to the m bit after the decimal point.
SELECT (78.87653, 2) from dual;
243. Sign (N)
When n <0, get -1;
When n> 0, get 1;
When n = 0, get 0;
SELECT SIGN (99) from Dual
244. SINH (N)
Gets the dual sine value of n.
SELECT SINH (1) from dual;
245. sort (n)
Get the square root of N, N> = 0
Select Sort (9) from dual;
246. Tan (N)
Got N the positive cut value.
SELECT TAN (0) from dual;
247. Tanh (N)
Gets the hyperbolic positive cut value of N.
SELECT TANH (0) from dual;
248. Trunc (N, M)
Gets the value of n truncated in the M bit.
SELECT TRUNC (7.7788, 2) from Dual;
249. count ()
Calculate the number of records that meet the conditions.
SELECT Count (*) from table1 where col1 = 'aaa';
250. max ()
For the specified columns, the maximum value is required.
SELECT MAX (col1) from table1;
251. min ()
For the specified sum: the minimum value.
SELECT min (col1) from table1;
252. AVG ()
For the specified columns. SELECT AVG (COL1) from table1;
253. Sum ()
Calculate the sum.
SELECT SUM (col1) from dual;
254. TO_NUMBER (CHAR)
Convert characters to values.
SELECT TO_NUMBER ('999') from Dual;
255. Chartorowid (char)
The CHAR containing the external syntax RowID or the 18-character word VARCHAR2 value that includes an external syntax is converted to the internal binary syntax, the parameter char string.
Select Name from bsempms where rowid = chartorowid ('AaaafzaabaaAcp8aao);
Name: Leixue
256. Convert (char, dest_char_set, source_char_set)
CONVERT converts the character set of character dest_char_set in the string char to the character set from the Source_Char_SET identified by the character set
Select Convert ('grob', 'US7ASCII', 'WE8HP') 'CONVERSION' from PUBS;
Conversion: Gross
257. HEXTORAW (char)
Converts the CHAR that contains hexadecimal to a RAW value.
INSERT INTO BSEMPMS (Raw_Column) SELECT HEXTORAW ('7D') from test;
258. RawtoHex (RAW)
Converting the RAW value into a CHAR value containing hexadecimal.
Select Rawtohex (Raw_Column) 'Conversion' from bsempms;
Conversion: 7D
259. RowidTochar (RowID)
Convert a ROWID value to a VARCHAR2 data type.
Select Rowid from bsempms where rowidtochar (rowid) Like '% br1aab%;
260. TO_MULTI_BYTE (Char)
Convert the single byte in the char to the equivalent multi-byte character.
SELECT TO_MULTI_BYTE ('asfdfd') from test;
261. to_single_byte (char)
Convert the multi-byte characters in the char.
SELECT TO_SINGLE_BYTE ('asfdfd') from test;
262. Translate using (Text using {char_cs | nchar_cs})
Convert text Text to the specified conversion mode into a database character set and national character set.
Where Text is to be converted.
Using char_cs parameter conversion Text is a database character set, and the output data type is VARCHAR2.
Using nchar_cs parameter conversion Text is a database character set, and the output data type is NVARCHAR2.
Create Table Test (Char_Colchar (20), nchar_col nchar (20));
INSERT INTO TEST VALUES ('Hi, N'Bye');
SELECT *.
263. DUMP (expr, return_format, start_position, length)
Returns a VARCHAR2 value containing the data type code, the current database character set, and the data type according to the following frame. The following is the return result is the code of the internal data type of data to return as a number:
Code data type
0 varchar2
1 Number
8 long
12 Date
23 RAW
24 Long Raw
69 rowid
96 char
106 MSSLabel
The parameter retuen_format specifies the number of returned by the base number below.
Return_Format Result
8 8
10 10
16 16
17 single character
If the parameter return_format is not specified, press the decimation to return.
If the byte of the parameter start_p will be returned, the default is to return to LENGTH, represented from the S back toll. Long length starting at tart_position
SELECT DUMP ('ABC', 1016) from test;
Select Dump (ENAME, 8, 3, 2) 'Example' fromemp where name = 'ccbzzp';
264. EMPTY_B | Clob ()
Returns an empty LOB locator, uses the LOB column or set it empty. Initialize the LOB variable, or use in INSERT and UPDATE declaration to initial
INSERT INTO TABLE1 VALUES (EMPTY_BLOB ());
Update table1 set clob_col = Empty_blob ();
265. BfileName ('Directory', 'FileName')
Returns a BFile locator, the associated Directory refers to the file name of the server's file system. Binary LOB physical files on the server's file system. Directory search path full name alias. FileName refers to the server's file
INSERT INTO FILE_TAB VALUES (BFileName ('LOB_DIR', 'Image1.gif');
266. Greatest (expr, expr, ...)
Greatest returns the maximum value of the parameter.
SELECT GRETEST ('Harry', 'Harriot', 'Harold') 'Sample' from Table1;
267. Least (expr, expr, ...)
Least returns the minimum value of the parameter.
Select Least ('Harry', 'Harriot', 'Harold') 'Sample' from Table1;
268. NLS_CHARSET_DECL_LEN (Bytecnt, CSID)
Returns a width of a NCHAR column.
SELECT NLS_CHARSET_DECL_LENTABLE1; (200, NLS_CHARSET_ID ('JA16EEFDFDF'))
269. NLS_CHARSET_ID (Text)
Returns the number of NLS character set IDs corresponding to NLS character set name.
SELECT NLS_CHARSET_D ('jadfdffdf') from table1;
270. NLS_CHARSET_NAME (N)
Returns the NLS character set name corresponding to the ID number N.
SELECT NLS_CHARSET_NAME (2) from table1; 271. NVL (expr1, expr2)
If EXPR1 is NULL, EXPR2 is returned, otherwise it returns EXPR1.
SELECT NAME, NVL (To_Char (Comm), 'Not Application') from table1;
272. UID
Returns the unique identifier to the current database user.
SELECT Uid from Table1;
273. User
Returns the name of the current Oracle user with a varchar2 data type.
SELECT User, Uid from Table1;
274. Userenv (option)
Returns the current session information.
Option = 'isdba' If the current DBA role is current, true, otherwise false.
Option = 'language' Returns the character set of the database.
OPTION = 'sessionID' is the current session identifier.
Option = 'entryID' Returns the auditable session identifier.
OPTION = 'lang' is an ISO news that returns a session language name.
Option = 'instance' returns the current instance.
Select Userenv ('Language') from Dual;
275. vsize (expr)
Returns the number of bytes represented inside the EXPR.
Select Name, vsize (name) from table1;
276. Deref (e)
Returns the object reference of the parameter E.
Select deref (c2) from table1;
277. REFTOHEX (R)
Convert parameter R to 16.
SELECT REFTOHEX (C2) from table1;
278. make_ref (Table, Key, Key ...)
Create a reference to a row in a given view object by using a given key as a primary key.
Create Type T1 as Object (Anumber, B Number);
Create Table TB1 (C1 Number, C2 Number, Primary Key (C1, C2));
Create View V1OF T1 with Object Oid (A, Asselect * from TB1;
SELECT MAKE_REF (V1, 1, 3) from PUBS;
279. stddev (Distinct | All X)
STDDEV gives a quotation of a set of row values.
SELECT STDDEV (SALARY) AS EXAMPLE from EMPLOYEE
280. Variance (Distinct | All X)
Variance returns the variance of all Value in a group.
Select Variance (Salary) ASEXample from Employee
9i management V $ view
281. V $ Access
Displays objects in the currently locked database and is accessing their sessions.
282. V $ ACTIVE_INSTANCES
Establish a mapping from the instance name to the instance number to all instances that appear in the currently installed database.
283. V $ ACTIVE_SESS_POOL_MTH
All active session resource allocation methods.
284. V $ AQ
The queue of the queue in the current database.
285. V $ Archive
The information you need to make the log file required.
286. V $ Archive_Dest's file files for all archive logs of the current instance and their current value, mode, and status.
287. V $ archive_processes
Provide information for different ARCH process status for an example.
288. V $ archive_log
Control the archive log information in the file.
289. V $ BACKUP
The backup status of all online data files.
290. V $ backup_async_io
Display the information of the backup set from the control file.
291. V $ backup_corruption
Displays information about corruption from the control file backup.
292. V $ backup_datafile
Displays the backup data files and backup control files from the control file.
293. V $ backup_device
Display information about supporting the backup device.
294. V $ backup_piece
Display the information of the backup block from the control file.
295. V $ backup_redolog
Display information about the backup set archive log from the control file.
296. V $ backup_set
Display the information of the backup set from the control file.
297. V $ backup_sync_io
Display the information of the backup set from the control file.
298. V $ BGPROCESS
Describe the background process.
299. V $ BH
It is a real-time application cluster view of 9i. The number of status and probing is given to each buffer in the system's global zone.
300. V $ BSP
Displays statistics on the background process of the block server background in the cache.
