Oracle basic knowledge-4

194./* + ordered */
According to the order in which the table appears in from, ordered connects Oracle to the table in this order.
For example:
Select/* + ordered */a. col1, B. col2, C. col3 from Table1 A, Table2 B,
Table3 c Where a. col1 = B. col1 and B. col1 = C. col1;
195./* + use_nl (table )*/
Connect the specified table to the nested connected row source and use the specified table as the internal table.
For example:
Select/* + ordered use_nl (bsempms) */bsdptms. dpt_no,
Bsempms. emp_no, bsempms. emp_nam from bsempms, bsdptms where
Bsempms. dpt_no = bsdptms. dpt_no;
196./* + use_merge (table )*/
The specified table is connected to other row sources by means of merged sort connections.
For example:
Select/* + use_merge (bsempms, bsdptms) */* From bsempms, bsdptms
Where bsempms. dpt_no = bsdptms. dpt_no;
197./* + use_hash (table )*/
Connects the specified table to other row sources by means of hash connections.
For example:
Select/* + use_hash (bsempms, bsdptms) */* From bsempms, bsdptms
Where bsempms. dpt_no = bsdptms. dpt_no;
198./* + driving_site (table )*/
The query execution is performed on tables in different locations than that selected by Oracle.
For example:
Select/* + driving_site (Dept) */* From bsempms, DEPT @ bsdptms where
Bsempms. dpt_no = Dept. dpt_no; 199./* + leading (table )*/
Use the specified table as the first table in the connection order.
200./* + cache (table )*/
When a full table scan is performed, the cache prompts that the retrieval block of the table can be placed in the latest minimal list in the buffer cache.
Least recently used end of LRU
For example:
Select/* + full (bsempms) cahe (bsempms) */emp_nam from bsempms;
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 latest minimal list in the buffer cache.
Least recently used end of LRU
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', 10' 123 ') from dual;
Result: '123abcdefg'
212. ltrim (char, set)
Remove the characters in the string set from the left side of the string char until the first character that is not in the set is
.
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 ('aaa') from dual;
215. nls_upper (char)
All characters in the string Char are capitalized.
Select nls_upper ('aaa') 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, '20140901', '20160901') 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 ('20140901', 8, '0') from dual;
218. rtrim (char, set)
Remove the characters 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)
Add n months to date D.
Select add_months (sysdate, 5) from dual;
224. last_day (d)
Obtain the date of the last day of the month that contains the d date.
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 ('2014/1/20'), 'satday') from dual;
227. rount (D, FMT)
Obtains the most advanced date rounded to by the specified FMT mode.
Select rount ('2014/1/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 ('2014/1/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 ('123') from dual;
255. chartorowid (char)
Convert the char or varchar2 values that contain the external syntax rowid to the internal binary syntax.
Char must be an 18-character string containing the rowid of 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
Character Set identified by dest_char_set
Select convert ('grob', 'us7ascii ', 'we8hp') 'version' 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) 'version' 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 ('asfdfs') from test;
261. to_single_byte (char)
Converts multiple bytes in Char to an equivalent single-byte character.
Select to_single_byte ('asfdfs') 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, n' bye ');
Select * from test;
263. Dump (expr, return_format, start_position, length)
Returns the varchar2 value that contains the data type code, byte length, and other internal information.
The former database character set. The data type is returned as a number based on the encoding of the internal data type specified below:
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, the length starting from start_position is
The Length byte is returned. The default value is an integer.
Select dump ('abc', 1016) from test;
Select dump (ename, 8, 3, 2) 'example 'from EMP where name = 'ccbzzp ';