每日归档： 2017年4月21日

SQL技巧笔记

原创文章，转载请注明： 转载自慢慢的回味

本文链接地址: SQL技巧笔记

Content:
  1. Where条件中的与或
  2. 临时数据或变量
  3. 多表插入
  4. Query Plan查询
Where条件中的与或

在Where条件中对子查询进行and和or逻辑的替代方式。因为and和or不支持这样用。

SELECT 'exist' 
 FROM dual 
WHERE EXISTS (
        SELECT 1 FROM dual
        UNION
        (
          SELECT 2 FROM dual
          INTERSECT
          SELECT 2 FROM dual
        )
      );
临时数据或变量

使用with as语句存储临时数据或变量

WITH temp1 AS
(
  SELECT 'a' AS col1, 'b' AS col2 ,'c' AS col3 FROM dual
)
SELECT 'exist' 
 FROM dual 
WHERE EXISTS (
        SELECT 1 FROM temp1 WHERE col1 = 'b'
        UNION
        (
          SELECT 2 FROM temp1 WHERE col2 = 'b'
          INTERSECT
          SELECT 2 FROM  temp1 WHERE col3 = 'c'
        )
      );
多表插入

在Oracle数据库中，单SQL对多表进行插入，且多表中有外键引用。
例如：
现在有两个表，学生表student和学生所在班表student_class，所在班表会引用学生表的主键id(s_num_id)。
学生学号从SN001到SN005分配到班级Class A，学生学号从SN006到SN010分配到班级Class B。
因为insert all语句中不能使用序列，所以必须采用extractvalue(dbms_xmlgen.getxmltype(绕过去。

INSERT ALL 
WHEN 1 = 1 THEN 
 INTO student (id, s_num) 
   VALUES (num_id, num) 
WHEN 2 > 0 THEN 
 INTO student_class (id, s_num_id, s_class_name ) 
   VALUES (student_class_id, num_id, class_name ) 
 WITH temp (num, class_name) AS
   ( 
      SELECT regexp_replace( to_char(level,'FM000'),'([0-9]{3})', 'SN\1'), 'Class A' FROM dual WHERE level >= 001 CONNECT BY level <=005 
      SELECT regexp_replace( to_char(level,'FM000'),'([0-9]{3})', 'SN\1'), 'Class B' FROM dual WHERE level >= 006 CONNECT BY level <=010
   ) SELECT rownum, 
            extractvalue(dbms_xmlgen.getxmltype('select STUDENT_SEQ.nextval from dual'),'//text()') AS num_id, 
            extractvalue(dbms_xmlgen.getxmltype('select STUDENT_CLASS_SEQ.nextval from dual'),'//text()') AS student_class_id, 
            num, class_name 
       FROM temp;

最后两表产生的数据如下：

student                    student_class
id   s_num                 id    s_num_id    s_class_name
1    'SN001'               31    1           'Class A'
2    'SN002'               32    2           'Class A'
3    'SN003'               33    3           'Class A'
4    'SN004'               34    4           'Class A'
5    'SN005'               35    5           'Class A'
6    'SN006'               36    6           'Class B'
7    'SN007'               37    7           'Class B' 
8    'SN008'               38    8           'Class B'
9    'SN009'               39    9           'Class B'
10   'SN010'               40    10          'Class B'
Query Plan查询

先根据SQL里面的关键字查到sql_id，然后通过第二句传入sql_id得到plan。

SELECT hash_value, sql_id,child_number,address,sql_text,is_reoptimizable  FROM v$sql WHERE sql_text LIKE '%aaaaa%';
SELECT * FROM TABLE(dbms_xplan.display_cursor('bcyctu75fnav4',NULL,'ADVANCED'));

本作品采用知识共享署名 4.0 国际许可协议进行许可。