oracle入门笔记——承书（零一）

/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 \W REGEXP_REPLACE REGEXP_LIKE REGEXP_SUBSTR REGEXP_INSTR */ IF(TO_CHAR(SYSDATE,’DD’)>10) THEN SELECT REGEXP_REPLACE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’\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$LOCKED_OBJECT T; SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS T WHERE T.OBJECT_ID IN (‘$OBJECT_ID’);
SELECT SID,SERIAL#,MACHINE,PROGRAM FROM V$SESSION WHERE SID='$SID’;
ALTER SYSTEM KILL SESSION ‘$SID,$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);