/FUNCTION/
UPPER,LOWER
–FLOOR
SELECT FLOOR(1.5) FROM DUAL;
–CEIL
SELECT CEIL(1.5) FROM DUAL;
–INITCAP
SELECT INITCAP(‘ABCDE’) FROM DUAL;
–CONCAT
SELECT CONCAT(‘A’,’B’) FROM DUAL;
–||
SELECT ‘A’||’B’ FROM DUAL;
–LENGTH
SELECT LENGTH(‘ABCD’) FROM DUAL;
–INSTR
SELECT INSTR(‘WORLD’, ‘OR’) FROM DUAL;
SELECT *
FROM (SELECT ‘WORLD’ C1, SYSDATE C2 FROM DUAL) T1
WHERE INSTR(T1.C1, ‘OR’) > 0;
–REPLACE
SELECT REPLACE(‘WORLD’,’O’,’O’) FROM DUAL;
–ROUND
SELECT ROUND(25.16,1) FROM DUAL;
SELECT ROUND(25.16,-1) FROM DUAL;
–MOD
SELECT MOD(10,3) FROM DUAL;
–TRUNC
SELECT TRUNC(SYSDATE,1/24) FROM DUAL;
–SYSDATE
SELECT SYSDATE FROM DUAL;
–TO_CHAR
SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD’) FROM DUAL;
–SUBSTR
SELECT SUBSTR(TO_CHAR(SYSDATE,’YYYY-MM-DD’),1,4) FROM DUAL;
–ADD_MONTHS
SELECT ADD_MONTHS(SYSDATE,+1) FROM DUAL;
–DISTINCT
SELECT DISTINCT C1 FROM (SELECT 1 C1,1 C2 FROM DUAL UNION SELECT 1 C1,2 C2 FROM DUAL);
–DECODE
SELECT C1,DECODE(C2,1,’男’,2,’女’,’NULL’) AS 性别 FROM (SELECT 1 C1,1 C2 FROM DUAL UNION SELECT 1 C1,2 C2 FROM DUAL);
–CASE
SELECT C1,CASE C2 WHEN 1 THEN ‘男’
WHEN 2 THEN ‘女’ END AS 性别 FROM (SELECT 1 C1,1 C2 FROM DUAL UNION SELECT 2 C1,2 C2 FROM DUAL);
SELECT C1,CASE WHEN C2=1 THEN ‘男’
WHEN C2=2 THEN ‘女’ END AS 性别 FROM (SELECT 1 C1,1 C2 FROM DUAL UNION SELECT 2 C1,2 C2 FROM DUAL);
–NVL
SELECT C1,NVL(C2,’100’) FROM (SELECT 1 C1,1 C2 FROM DUAL UNION SELECT 1 C1,NULL C2 FROM DUAL);
–NVL2
SELECT C1,NVL2(C2,’100’,’101’) FROM (SELECT 1 C1,NULL C2 FROM dual);
–TRIM
SELECT TRIM(’ 1 ‘) FROM DUAL;
SELECT LTRIM(’ 1 ‘) FROM DUAL;
SELECT RTRIM(’ 1 ‘) FROM DUAL;
–RETURN
退出
–EXIT
退出循环
–GOTO
跳转到指定行
–NULL
NULL做加减乘除操作,结果仍为空;
–REGEXP正则
/*
^
.?+∗|M.N\D\D\W\WREGEXPREPLACEREGEXPLIKEREGEXPSUBSTRREGEXPINSTR∗/IF(TOCHAR(SYSDATE,′DD′)>10)THENSELECTREGEXPREPLACE(TOCHAR(SYSDATE,′YYYY−MM−DD′),′\D2
.
?
+
∗
|
M
.
N
\D
\D
\W
\W
R
E
G
E
X
P
R
E
P
L
A
C
E
R
E
G
E
X
P
L
I
K
E
R
E
G
E
X
P
S
U
B
S
T
R
R
E
G
E
X
P
I
N
S
T
R
∗
/
I
F
(
T
O
C
H
A
R
(
S
Y
S
D
A
T
E
,
′
D
D
′
)
>
10
)
T
H
E
N
S
E
L
E
C
T
R
E
G
E
X
P
R
E
P
L
A
C
E
(
T
O
C
H
A
R
(
S
Y
S
D
A
T
E
,
′
Y
Y
Y
Y
−
M
M
−
D
D
′
)
,
′
\D
2
’,’20’) FROM DUAL;
END IF;
SELECT SUBSTR((TO_CHAR(SYSDATE,’YYYY-MM-DD’)),1,8)||’20’ FROM DUAL;
/*IF THEN
ELSE
END IF;
LOOP
EXIT WHEN …;
END LOOP;
WHILE … LOOP
NULL;
END LOOP;
IF THEN
ELSIF THEN
ELSE
IF;
*/
SELECT *
FROM (SELECT 1 C1, SYSDATE C2
FROM DUAL
UNION
SELECT 2 C1, SYSDATE C2 FROM DUAL) T1
,
(SELECT 2 C1, SYSDATE C2
FROM DUAL
UNION
SELECT 3 C1, SYSDATE C2 FROM DUAL) T2
WHERE T1.C1=T2.C1(+)
AND T2.C1 IS NOT NULL;
SELECT *
FROM (SELECT 1 C1, SYSDATE C2
FROM DUAL
UNION
SELECT 2 C1, SYSDATE C2 FROM DUAL) T1
WHERE EXISTS (SELECT 1
FROM (SELECT 2 C1, SYSDATE C2
FROM DUAL
UNION
SELECT 3 C1, SYSDATE C2 FROM DUAL) T2
WHERE T1.C1 = T2.C1);
/递归查询/
SELECT *
FROM (SELECT 12 ORG_ID, 1 P_ORG_ID
FROM DUAL
UNION
SELECT 1234 ORG_ID, 123 P_ORG_ID
FROM DUAL
UNION
SELECT 123 ORG_ID, 12 P_ORG_ID FROM DUAL
UNION
SELECT 13 ORG_ID, 1 P_ORG_ID
FROM DUAL
UNION
SELECT 134 ORG_ID, 13 P_ORG_ID FROM DUAL) t
START WITH t.p_org_id=1 CONNECT BY PRIOR t.org_id=t.p_org_id;
–MERGE INTO
MERGE INTO TABLE_NAME1
USING (SELECT COLUMN_NAME1,COLUMN_NAME2… FROM TABLE_NAME2) ON
(TABLE_NAME1.COLUMN_NAME1=TABLE_NAME2.COLUMN_NAME1 AND TABLE_NAME1.COLUMN_NAME2=TABLE_NAME2.COLUMN_NAME2)
WHEN MATCHED THEN
UPDATE SET =
WHEN NOT MATCHED THEN
INSERT INTO VALUES;
–OBJECT
SELECT * FROM ALL_VIEWS;
SELECT * FROM USER_VIEWS;
SELECT * FROM ALL_TABLES;
SELECT * FROM USER_TABLES;
SELECT * FROM ALL_OBJECTS;
SELECT * FROM USER_OBJECTS;
SELECT T.COLUMN_NAME FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME = ‘B_SYS_PARAMETER’ ORDER BY T.COLUMN_NAME;
SELECT * FROM ALL_OBJECTS T WHERE INSTR(T.OBJECT_NAME,’REPICK_EQUIP_CHECK’)>0;
SELECT * FROM USER_OBJIECTS T;
–TABLE
DROP TABLE table_name;
drop table table_name cascade constraints;
ALTER TABLE table_name ADD column_name;
ALTER TABLE table_name DROP COLUMN column_name;
DELETE FROM table_name;
TRUNCATE TABLE table_name;
INSERT INTO table_name1 SELECT * FROM table_name2;
–SEQUENCE
CREATE SEQUENCE SEQ_TABLE_NAME
MINVALUE 1
MAXVALUE 9999999999999999
START WITH 1000000000000000
INCREMENT BY 1
CACHE 20;
DROP SEQUENCE SEQ_TABLE_NAME;
–HOW TO USE SEQUENCE
INSERT INTO table_name (SEQ_ID) VALUES (SEQ_TABLE_NAME.NEXTVAL);
–FLASHBACK
ALTER TABLE table_name ENABLE ROW MOVEMENT;
FLASHBACK TABLE table_name TO TIMESTAMP to_timestamp(‘2018-08-15 11:50:00’,’YYYY-MM-DD HH24:mi:SS’);
–UNLOCK
SELECT * FROM V
LOCKEDOBJECTT;SELECTOBJECTNAME,OBJECTTYPEFROMDBAOBJECTSTWHERET.OBJECTIDIN(‘
L
O
C
K
E
D
O
B
J
E
C
T
T
;
S
E
L
E
C
T
O
B
J
E
C
T
N
A
M
E
,
O
B
J
E
C
T
T
Y
P
E
F
R
O
M
D
B
A
O
B
J
E
C
T
S
T
W
H
E
R
E
T
.
O
B
J
E
C
T
I
D
I
N
(
‘
OBJECT_ID’);
SELECT SID,SERIAL#,MACHINE,PROGRAM FROM V
SESSIONWHERESID=′
S
E
S
S
I
O
N
W
H
E
R
E
S
I
D
=
′
SID’;
ALTER SYSTEM KILL SESSION ‘
SID,
S
I
D
,
SERIAL#’;
–LOCKED TABLE
LOCK TABLE table_name IN EXCLUSIVE MODE;
–ora-01403
SELECT INTO 结果为空;
ora-00001:违反唯一约束条件:
插入数据有重复
–ora-00054 资源正忙,但指定以NOWAIT方式,或者超时失效。
–执行DML语句操作不当,造成数据库对所要的操作的表上锁,此时oracle已经对返回的结果集上加了排它的行级锁,所有其他对这些数据进行的操作将发生阻塞
–explain plan解释计划
EXPLAIN PLAN FOR sqlstr;
SELECT * FROM TABLE(dbms_xplan.display);