电脑安全

您当前的位置:主页 > 电脑安全 >

Oracle SQL精妙SQL语句讲解-网络编程

来源:[db:来源]标题:   行列    编辑:电脑技术全能网 时间:2019-09-18 12:29

原标题:Oracle SQL精妙SQL语句讲解-网络编程
   中心提醒: --行列转换 行转列DROP TABLE t_change_lc;CREATE TABLE t_change_lc (card_code VARCHAR2  --行列转换 行转列  DROP TABLE t_change_lc;  CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);  INSERT INTO t_change_lc   SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4  UNION   SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;  SELECT * FROM t_change_lc;  SELECT a.card_code,  SUM(decode(a.q, 1, a.bal, 0)) q1,  SUM(decode(a.q, 2, a.bal, 0)) q2,  SUM(decode(a.q, 3, a.bal, 0)) q3,  SUM(decode(a.q, 4, a.bal, 0)) q4  FROM t_change_lc a  GROUP BY a.card_code  ORDER BY 1;  --行列转换 列转行  DROP TABLE t_change_cl;  CREATE TABLE t_change_cl AS   SELECT a.card_code,  SUM(decode(a.q, 1, a.bal, 0)) q1,  SUM(decode(a.q, 2, a.bal, 0)) q2,  SUM(decode(a.q, 3, a.bal, 0)) q3,  SUM(decode(a.q, 4, a.bal, 0)) q4  FROM t_change_lc a  GROUP BY a.card_code  ORDER BY 1;  SELECT * FROM t_change_cl;  SELECT t.card_code,  t.rn q,  decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal  FROM (SELECT a.*, b.rn  FROM t_change_cl a,  (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t  ORDER BY 1, 2;  --行列转换 行转列 兼并  DROP TABLE t_change_lc_comma;  CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;  SELECT * FROM t_change_lc_comma;  SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q  FROM (SELECT a.card_code,  a.q,  row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn  FROM t_change_lc_comma a) t1  START WITH t1.rn = 1  CONNECT BY t1.card_code = PRIOR t1.card_code  AND t1.rn - 1 = PRIOR t1.rn  GROUP BY t1.card_code;  --行列转换 列转行 宰割  DROP TABLE t_change_cl_comma;  CREATE TABLE t_change_cl_comma AS  SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q  FROM (SELECT a.card_code,  a.q,  row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn  FROM t_change_lc_comma a) t1  START WITH t1.rn = 1  CONNECT BY t1.card_code = PRIOR t1.card_code  AND t1.rn - 1 = PRIOR t1.rn  GROUP BY t1.card_code;  SELECT * FROM t_change_cl_comma;  SELECT t.card_code,  substr(t.q,  instr(';' || t.q, ';', 1, rn),  instr(t.q || ';', ';', 1, rn) - instr(';' || t.q, ';', 1, rn)) q  FROM (SELECT a.card_code, a.q, b.rn  FROM t_change_cl_comma a,  (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b  WHERE instr(';' || a.q, ';', 1, rn) > 0) t  ORDER BY 1, 2;    -- 完成一笔记录依据前提多表拔出  DROP TABLE t_ia_src;  CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;  DROP TABLE t_ia_dest_1;  CREATE TABLE t_ia_dest_1(flag VARCHAR2(10) , c VARCHAR2(10));  DROP TABLE t_ia_dest_2;  CREATE TABLE t_ia_dest_2(flag VARCHAR2(10) , c VARCHAR2(10));  DROP TABLE t_ia_dest_3;  CREATE TABLE t_ia_dest_3(flag VARCHAR2(10) , c VARCHAR2(10));  SELECT * FROM t_ia_src;   SELECT * FROM t_ia_dest_1;  SELECT * FROM t_ia_dest_2;  SELECT * FROM t_ia_dest_3;  INSERT ALL  WHEN (c1 IN ('a1','a3')) THEN   INTO t_ia_dest_1(flag,c) VALUES(flag1,c2)  WHEN (c1 IN ('a2','a4')) THEN   INTO t_ia_dest_2(flag,c) VALUES(flag2,c2)  ELSE  INTO t_ia_dest_3(flag,c) VALUES(flag1||flag2,c1||c2)  SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;  -- 假如存在就更新,不存在就拔出用一个语句完成  DROP TABLE t_mg;  CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));  SELECT * FROM t_mg;  MERGE INTO t_mg a  USING (SELECT 'the code' code, 'the name' NAME FROM dual) b  ON (a.code = b.code)  WHEN MATCHED THEN  UPDATE SET a.NAME = b.NAME  WHEN NOT MATCHED THEN  INSERT (code, NAME) VALUES (b.code, b.NAME);  -- 抽取/删除反复记载   DROP TABLE t_dup;  CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5) NAME FROM dual CONNECT BY ROWNUM<=10;   INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5) NAME FROM dual CONNECT BY ROWNUM<=2;  SELECT * FROM t_dup;  SELECT * FROM t_dup a WHERE a.ROWID <> (SELECT MIN(b.ROWID) FROM t_dup b WHERE a.code=b.code);  SELECT b.code, b.NAME  FROM (SELECT a.code,  a.NAME,  row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rn  FROM t_dup a) b  WHERE b.rn > 1;  -- IN/EXISTS的差别实用情况  -- t_orders.customer_id有索引  SELECT a.*  FROM t_employees a  WHERE a.employee_id IN  (SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);  SELECT a.*  FROM t_employees a  WHERE EXISTS (SELECT 1  FROM t_orders b  WHERE b.customer_id = 12  AND a.employee_id = b.sales_rep_id);  -- t_employees.department_id有索引  SELECT a.*  FROM t_employees a  WHERE a.department_id = 10  AND EXISTS  (SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);  SELECT a.*  FROM t_employees a  WHERE a.department_id = 10  AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b);  -- FBI  DROP TABLE t_fbi;  CREATE TABLE t_fbi AS  SELECT ROWNUM rn, dbms_random.STRING('z',10) NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual   CONNECT BY ROWNUM <=10;  CREATE INDEX idx_nonfbi ON t_fbi(dt);  DROP INDEX idx_fbi_1;  CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));  SELECT * FROM t_fbi WHERE trunc(dt) = to_date('2006-09-21','yyyy-mm-dd') ;  -- 不倡议应用  SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd') = '2006-09-21';  -- LOOP中的COMMIT/ROLLBACK  DROP TABLE t_loop PURGE;  create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;  SELECT * FROM t_loop;  -- 逐行提交  DECLARE  BEGIN  FOR cur IN (SELECT * FROM user_objects) LOOP  INSERT INTO t_loop VALUES cur;  COMMIT;  END LOOP;  END;  -- 模仿批量提交http://blog.knowsky.com/  DECLARE  v_count NUMBER;  BEGIN  FOR cur IN (SELECT * FROM user_objects) LOOP  INSERT INTO t_loop VALUES cur;  v_count := v_count + 1;  IF v_count >= 100 THEN  COMMIT;  END IF;  END LOOP;  COMMIT;  END;  -- 真正的批量提交  DECLARE  CURSOR cur IS  SELECT * FROM user_objects;  TYPE rec IS TABLE OF user_objects%ROWTYPE;  recs rec;  BEGIN  OPEN cur;  WHILE (TRUE) LOOP  FETCH cur BULK COLLECT  INTO recs LIMIT 100;  -- forall 完成批量  FORALL i IN 1 .. recs.COUNT  INSERT INTO t_loop VALUES recs (i);  COMMIT;  EXIT WHEN cur%NOTFOUND;  END LOOP;  CLOSE cur;  END;  -- 达观锁定/悲观锁定   DROP TABLE t_lock PURGE;  CREATE TABLE t_lock AS SELECT 1 ID FROM dual;  SELECT * FROM t_lock;  -- 罕见的完成逻辑,隐含bug  DECLARE  v_cnt NUMBER;  BEGIN  -- 这里有并发性的bug  SELECT MAX(ID) INTO v_cnt FROM t_lock;  -- here for other operation  v_cnt := v_cnt + 1;  INSERT INTO t_lock (ID) VALUES (v_cnt);  COMMIT;  END;  -- 高并发情况下,保险的完成逻辑  DECLARE  v_cnt NUMBER;  BEGIN  -- 对指定的行获得lock  SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;  -- 在有lock的情形下持续上面的操纵  SELECT MAX(ID) INTO v_cnt FROM t_lock;  -- here for other operation  v_cnt := v_cnt + 1;  INSERT INTO t_lock (ID) VALUES (v_cnt);  COMMIT; --提交而且开释lock  END;  -- 硬剖析/软剖析  DROP TABLE t_hard PURGE;  CREATE TABLE t_hard (ID INT);  SELECT * FROM t_hard;  DECLARE  sql_1 VARCHAR2(200);  BEGIN  -- hard parse  -- java中的等同语句是 Statement.execute()  FOR i IN 1 .. 1000 LOOP  sql_1 := 'insert into t_hard(id) values(' || i || ')';  EXECUTE IMMEDIATE sql_1;  END LOOP;  COMMIT;  -- soft parse  --java中的等同语句是 PreparedStatement.execute()  sql_1 := 'insert into t_hard(id) values(:id)';  FOR i IN 1 .. 1000 LOOP  EXECUTE IMMEDIATE sql_1  USING i;  END LOOP;  COMMIT;  END;    -- 准确的分页算法   SELECT *  FROM (SELECT a.*, ROWNUM rn  FROM (SELECT * FROM t_employees ORDER BY first_name) a  WHERE ROWNUM <= 500)  WHERE rn > 480 ;  -- 分页算法(why not this one)  SELECT a.*, ROWNUM rn  FROM (SELECT * FROM t_employees ORDER BY first_name) a  WHERE ROWNUM <= 500 AND ROWNUM > 480;  -- 分页算法(why not this one)  SELECT b.*  FROM (SELECT a.*, ROWNUM rn  FROM t_employees a  WHERE ROWNUM < = 500  ORDER BY first_name) b  WHERE b.rn > 480;  -- OLAP  -- 小计共计  SELECT CASE  WHEN a.deptno IS NULL THEN  '共计'  WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN  '小计'  ELSE  '' || a.deptno  END deptno,  a.empno,  a.ename,  SUM(a.sal) total_sal  FROM scott.emp a  GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());  -- 分组排序  SELECT a.deptno,  a.empno,  a.ename,  a.sal,  -- 可腾跃的rank  rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1,  -- 麋集型rank  dense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2,  -- 不分组排序  rank() over(ORDER BY sal DESC) r3  FROM scott.emp a  ORDER BY a.deptno,a.sal DESC;  -- 以后行数据和前/后n行的数据比拟  SELECT a.empno,  a.ename,  a.sal,  -- 下面一行  lag(a.sal) over(ORDER BY a.sal DESC) lag_1,  -- 上面三行  lead(a.sal, 3) over(ORDER BY a.sal DESC) lead_3  FROM scott.emp a  ORDER BY a.sal DESC;

上一篇:MSSQL Server面试题整合-网络编程

下一篇:没有了