一日一学！

方法一SQL> WITH SHOPPING AS 2 ( 3 SELECT '1' U_ID, '苹果' GOODS, '2' QTY FROM DUAL UNION ALL 4 SELECT '2' U_ID, '梨子' GOODS, '5' QTY FROM DUAL UNION ALL 5 SELECT '1' U_ID,...

SQL> SELECT DEPTNO, SUBSTR(MAX(SYS_CONNECT_BY_PATH(ENAME, '/')), 2) PATH 2 FROM ( 3 SELECT ENAME, DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RN 4 ...

CREATE TABLE T1 (C1 NUMBER(1), C2 VARCHAR2(2));INSERT INTO T1 VALUES (1, '01');INSERT INTO T1 VALUES (2, '02');INSERT INTO T1 VALUES (2, '03');INSERT INTO T1 VALUES (3, '04');INSERT INTO...

SELECT * FROM SCOTT.EMP T1, SCOTT.EMP T2 WHERE T1.MGR = T2.EMPNO(+) ORDER BY T2.EMPNO; 

--显示上下级关系 SELECT EMPNO, ENAME, LEVEL, MGR FROM SCOTT.EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR;  --利用LPAD每一级添加4个空格 SELECT LPAD(' ', 4 * LEVEL - 4...

-- 每月入职人数统计 CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 结果N END AS ALIAS -- 方法一 SELECT CASE WHEN TO_CHAR(HIREDATE, 'MM') = '01' THEN 1 END AS "1月"...

-- 每月入职人数统计 DECODE(字段, 条件1, 结果值1 条件2, 结果值2, 默认值); -- 方法一 SELECT DECODE(TO_CHAR(HIREDATE, 'MM'), '01', 1) AS "1月" , DECODE(TO_CHAR(HIREDATE, 'MM'...

--COALESCE用法 --COALESCE (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。 --1. 建立普通的表 WITH T_CUST AS ( SELECT '张一' CUST_NM, '186108...

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL; 

--日期 + 日期 相加 SELECT SYSDATE + 1 FROM DUAL; --日期 + 时间 相加 SELECT SYSDATE + 1/24 FROM DUAL; --日期 + 分 相加 SELECT SYSDATE + 1/(24 * 60) FROM DUAL; ...

-- Oracle分页（效率好） SELECT * FROM (SELECT A.* , ROWNUM RN FROM (SELECT * FROM EMP ) A WHERE...

SELECT TO_CHAR(TO_DATE('20140101', 'YYYYMMDD') + LEVEL - 1, 'YYYY-MM-DD') AS MYDATE FROM DUAL CONNECT BY LEVEL <= (TO_DATE('20141231', 'YYYYMMDD') - TO_DATE('20140101', 'YYYYMMDD') + 1); 

-- 查看当前登陆用户所属的全部表SELECT * FROM USER_TABLES;  

SELECT * FROM DBA_USERS;  

CREATE OR REPLACE PROCEDURE WHERE_CURRENTIS CURSOR EMP_LIST IS SELECT EMPNO , ENAME , SAL FROM EMP WHERE EMPNO = 1000 FOR UPDATE...

CREATE OR REPLACE PROCEDURE EXPCURSOR_TEST( V_DEPTNO IN DEPT.DEPTNO%TYPE) IS -- 声明CURSOR CURSOR DEPT_AVG IS SELECT B.DNAME AS DNAME , COUNT(A.EMP...

DECLARE V_CNT NUMBER := 100;BEGIN DBMS_OUTPUT.ENABLE; LOOP INSERT INTO EMP(EMPNO, ENAME, HIREDATE) VALUES (V_CNT, 'TEST' || TO_CHAR(V_CNT), SYSDATE);...

[code="sql"]SQL> SELECT DEPTNO AS 部门代码, DECODE(DEPTNO, 10, '会计', 20, '研发', 30, '销售', '运营') AS ...

SQL> SELECT HIREDATE 2 , TO_CHAR(HIREDATE, 'YYYY') 年 3 , TO_CHAR(HIREDATE, 'MM') 月 4 , TO_CHAR(HIREDATE, 'DD') 日 5 , TO_CHAR(HIREDATE, 'DY') ...