PL/SQL積累

--1.UNION ALL 

SELECT SUM(one) ,two FROM 
    (SELECT 1 one, 3 two FROM dual
    UNION ALL
    SELECT 3, 5 FROM dual
    UNION ALL
    SELECT 4, 3 FROM dual)
GROUP BY two
--2,插入記錄
INSERT INTO table_name 
      SELECT  '1','1','1','1','1','1',SYSDATE,'1',SYSDATE,SYSDATE FROM dual;
--3,批量提取游標

TYPE Inventory_Part_Table IS TABLE OF get_allparts%ROWTYPE
      INDEX BY PLS_INTEGER;
inv_part_tab_ Inventory_Part_Table;
 OPEN get_allparts;
 FETCH get_allparts BULK COLLECT INTO inv_part_tab_;
 CLOSE get_allparts;
--4,查詢數據字典表中的字段和長度
select t.COLUMN_NAME, t.data_length from user_tab_columns t where COLUMN_NAME ='NN_CONTRACT' and rownum < 5;
--5,賦值表結構和表里面的值
CREATE TABLE operation_history_tab_bak AS SELECT * FROM operation_history_tab --WHERE NULL IS NOT NULL; 
--6 將表橫着輸出
SELECT t1.No_ no_, SUM(CASE WHEN t1.kc = 'yuwen' THEN t1.chengji ELSE 0 END) yuwen,
SUM(CASE WHEN t1.kc = 'shuxue' THEN t1.chengji ELSE 0 END ) shuxue
FROM 
(
SELECT 1 no_, 'yuwen' kc, 99 chengji FROM dual 
UNION ALL
SELECT 2,'shuxue',80 FROM dual
UNION ALL
SELECT 2,'yuwen',80 FROM dual
UNION ALL
SELECT 1,'shuxue',70 FROM dual
)t1 
 GROUP BY t1.no_

--改变序列名称
rename seach_history_seq to SEARCH_HISTORY_SEQ