Oracle 学习整理（一）

 

--存储过程练习 01
CREATE OR REPLACE PROCEDURE TEST_XJT(X OUT VARCHAR2,
                                     Y OUT VARCHAR2,
                                     Z OUT VARCHAR2,
                                     S IN VARCHAR2) IS

  TYPE listtest IS TABLE OF TEST2%ROWTYPE;
  V_TM_NEXT_WEIGHT_PRICE listtest := listtest();

  sss        TEST2%ROWTYPE;
  sql_string varchar2(30000);
  STR_SQL    VARCHAR2(200);

BEGIN

  SELECT T.* BULK COLLECT INTO V_TM_NEXT_WEIGHT_PRICE FROM TEST2 T;

  FOR I IN V_TM_NEXT_WEIGHT_PRICE.FIRST .. V_TM_NEXT_WEIGHT_PRICE.LAST LOOP
    DBMS_OUTPUT.put_line(V_TM_NEXT_WEIGHT_PRICE(I).CT1);
    -- fetch V_TM_NEXT_WEIGHT_PRICE
  --  INTO sss;
  END LOOP;

  FOR I IN 1 .. 3 LOOP
    DBMS_OUTPUT.put_line(V_TM_NEXT_WEIGHT_PRICE(I).CT1);
  END LOOP;

  sql_string := 'UPDATE TEST T SET T.A1=1 WHERE T.A2 = :a';
  EXECUTE IMMEDIATE sql_string
    USING TO_CHAR(NULL);

  --STR_SQL := 'SELECT * FROM TT_BIL_SFBANK_CZ_COST T WHERE T.EMP_CODE = :S';
  STR_SQL := 'UPDATE TEST T SET T.A1 = :S WHERE T.A3 = :D';
  EXECUTE IMMEDIATE STR_SQL
    USING SYSDATE, '20000';

  X := '1';
  Y := '2';
  Z := '3';

  INSERT INTO TEST
    (A1, A2, A3)
    SELECT C1,
           C2,
           (SELECT T1.DEPT_CODE
              FROM (SELECT T.*,
                           ROW_NUMBER() OVER(PARTITION BY T.EMP_CODE ORDER BY T.CREATED_TM DESC) ROWNO
                      FROM TM_EMPLOYEE T
                     WHERE T.VALID_FLG = 1) T1
             WHERE T1.ROWNO = 1
               AND T1.EMP_CODE = K3.C1) A3
      FROM (SELECT J.*,
                   ROW_NUMBER() OVER(PARTITION BY J.C2, J.C3 ORDER BY J.C1) ROWNO
              FROM TEST1 J
             WHERE J.C1 = 1
               AND NOT EXISTS (SELECT 1
                      FROM TT_BANK_COST T
                     WHERE J.C2 = T.EMP_CODE
                       AND J.C3 = T.COST_DT)) K3
     WHERE K3.ROWNO = 1;

/*  SELECT SUM(A3) A3
    FROM (SELECT T.A1, NULL A2, NULL A3
            FROM TEST T
          UNION ALL
          SELECT NULL, T2.CT2, NULL
            FROM TEST2 T2
          UNION ALL
          SELECT NULL, NULL, T1.C3 FROM TEST1 T1);*/
         -- SELECT 1 FROM DUAL CONNECT BY LEVEL <3;
  --提交
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    X := SQLERRM;
    Y := SQLCODE;
    Z := SYSDATE;
    DBMS_OUTPUT.put_line(SQLERRM);
    ROLLBACK;
END TEST_XJT;

-- 存储过程练习，过程之间的调用
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS

  PROCEDURE SP_MAIN IS
    L_LIST T_TAB;
  BEGIN
    SELECT ROWNUM BULK COLLECT
      INTO L_LIST
      FROM DUAL
    CONNECT BY LEVEL <= 200;
  
    /* for i in 1..l_list.count loop
      dbms_output.put_line(l_list(i));
    end loop;*/
  
    SP_CALL(L_LIST);
  
  END;

  PROCEDURE SP_CALL(P_LIST IN T_TAB) IS
  
    L_SQL VARCHAR2(32767) := NULL;
    L_RES NUMBER;
  BEGIN
  
    L_SQL := 'select max(column_value)
               from table(:p_fid)
              where column_value <=100';
    DBMS_OUTPUT.PUT_LINE(L_SQL);
  
    EXECUTE IMMEDIATE L_SQL
      INTO L_RES
      USING P_LIST;
  
    DBMS_OUTPUT.PUT_LINE(L_RES);
  
  END;

END PKG_TEST;


-- SQL优化

--CTRL + E  本机执行的SQL
--SELECT * FROM V$SQL; 执行的SQL
--Hint

SELECT * FROM TEST1;

--索引  Unique 唯一索引 Normal 正常型B树  Bitmap 位图索引
-- 创建主键 默认 创建 唯一索引
/*+INDEX(T IDX_TEST_01) LEADING(T)*/ T为表名，IDX_TEST_01索z引名

--create bitmap index index_name on 表名(字段名);

CREATE BITMAP INDEX index_test ON TEST1(C2); --创建位图索引
CREATE INDEX index_test ON TEST1(C1); --创建正常型 B树索引

DROP INDEX index_test; --删除索引

--SELECT /*+ INDEX(T index_test)*/ T.* FROM TEST1 TWHERE T.C1 = 'A';
--SELECT /*+ LEADING(T) */ T.* FROM TEST1 T; --在多表关联查询中，指定哪个表作为驱动表，即告诉优化器首先要访问哪个表上的数据。 
--SELECT /*+parallel(T 4)*/ T.*  FROM TEST T; --开并行

SELECT /*+full(A) use_hash(A,B) leading(B)*/
 A.CODE
  FROM TEST A, TEST2 B, TEST3 M --leading 先查询那一个表，最好先查小表

/*   二．oracle访问数据的存取方法
    1） 全表扫描（Full Table Scans， FTS）
    2） 通过ROWID的表存取（Table Access by ROWID或rowid lookup）
    3）索引扫描（Index Scan或index lookup）有4种类型的索引扫描：
    　（1） 索引唯一扫描（index unique scan）
    　（2） 索引范围扫描（index range scan）
           在非唯一索引上都使用索引范围扫描。使用index rang scan的3种情况：
    　　  （a） 在唯一索引列上使用了range操作符（> < <> >= <= between）
    　　  （b） 在组合索引上，只使用部分列进行查询，导致查询出多行
    　　  （c） 对非唯一索引列上进行的任何查询。　　   
    　（3） 索引全扫描（index full scan）   
    　（4） 索引快速扫描（index fast full scan）
            跳跃式索引(Skip Scan Index)。
            
            INDEX (FAST FULL SCAN)和INDEX (FULL SCAN)的区别:前者不会按照索引的顺序执行，因此不保证查询结果按照索引排序。而后者是根据索引本身的顺序进行扫描。
            
    三、表之间的连接

    1，排序 - - 合并连接（Sort Merge Join， SMJ）
    2，嵌套循环（Nested Loops， NL）
    3，哈希连接（Hash Join， HJ）
    另外，笛卡儿乘积（Cartesian Product）*/
    
/*    nested loops适用于一大一小表。其中内表是小表，每取一次值，然后与外表匹配。 
若内表复杂度为n，外表复杂度为m，则整体为O(n*m) 
hash join 适用于大表关联。若内表复杂度为n，外表复杂度为m，则整体为O(n+m) */
SELECT TO_CHAR(SYSDATE-5,'yyyy-MM-dd hh24:mm:ss') from dual;
SELECT TO_date('2014-06-10 12:05:22','yyyy-MM-dd HH24:mi:ss') from dual;
SELECT j.*,j.rowid FROM DBA_JOBS J WHERE J.WHAT LIKE 'PRO_SIC_TEST%'  --查看过程执行JOB的时间
SELECT * FROM DBA_JOBS_RUNNING T WHERE T.JOB = 19197; --查找JOB

--创建 JOB脚本
DECLARE
  JOBNO NUMBER;
BEGIN
   SYS.DBMS_JOB.SUBMIT(JOB       => JOBNO,
                      WHAT      => 'PKG_TEST.PRO_STY_TEST(SYSDATE);',
                      NEXT_DATE => SYSDATE + 1/24,
                      INTERVAL  => 'TRUNC(SYSDATE+1)+11/24');
  COMMIT;
END;


SELECT SUBSTR('ABCDE',2,3) FROM DUAL;

SELECT LENGTH('12345') FROM DUAL;

SELECT LPAD('B',10,'-') FROM DUAL;

SELECT RPAD('B',10,'-') FROM DUAL;


--查看Session
select *
  from gv$process s, Gv$session gn
 where s.ADDR = gn.PADDR
   and gn.STATUS = 'ACTIVE'
   and gn.SCHEMANAME = 'EXB4' --所有者
   AND SID = 1159
   
   
select round(BYTES/1024/1024,2)||'M' from user_segments where segment_name='表名';   --查看表的大小   



--Oracle SQL 数据字典
ALL_TABLE_COMMENTS Oracle 数据字典表 可能通过模糊查询，来查询表的注释

Oracle查询表的名字和comments

select a.table_name,b.comments   from   user_tables a,ALL_TAB_COMMENTS b where a.table_name=b.table_name

SELECT * FROM ALL_TAB_COMMENTS T WHERE T.COMMENTS  LIKE '%客户%';


--Oracle 创建Job

DECLARE
  JOBNO NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT(JOB       => JOBNO,
                      WHAT      => 'PRO_MONTH(TRUNC(ADD_MONTHS(SYSDATE,-1),''MM''),TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))));',
                      NEXT_DATE => ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1) + 5 +
                                   3 / 24,
                      INTERVAL  => 'ADD_MONTHS(TRUNC(SYSDATE,''MM''),1)+5+ 3/24');

  COMMIT;
END;


-----Oralce OVER()函数
--ROW_NUMBER() OVER()
SELECT *
  FROM (SELECT EMP.*,
               ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ROWNUM) CN
          FROM EMP)
 WHERE CN = 1;

--MAX() OVER() 
SELECT MAX(T.NAME) OVER(),T.* FROM EMP T WHERE T.CODE_NO = '762151550'

--CONNECT BY 
SELECT 1 FROM DUAL CONNECT BY ROWNUM <= 5

SELECT DECODE(LEVEL, 1, '1', '2'),LEVEL FROM DUAL CONNECT BY LEVEL <= 5

INSERT /*+APPEND */ INTO TEST (A,B,C,D) SELECT AA,BB,CC,DD FROM T;




--Oracle Function
--L_CURSTR_CODE VARCHAR2(30) := 'SE'; 

FUNCTION FNT_GEN_NO(P_HEAD_CHAR VARCHAR2,
                    P_DATE      DATE,
                    P_DEPT_CODE VARCHAR2,
                    P_CUR_CODE  VARCHAR2) RETURN VARCHAR2 IS
  L_CE_NO VARCHAR2(30);
BEGIN
  SELECT P_HEAD_CHAR || TO_CHAR(P_DATE, 'YYMM') ||
         DECODE(P_CUR_CODE,
                L_CURSTR_CODE,
                LPAD(SEQ_MON_INV.NEXTVAL, 9, '0'),
                LPAD(SEQ_ABROAD_INV.NEXTVAL, 9, '0'))
    INTO L_CE_NO
    FROM DUAL;
  RETURN L_CE_NO;
EXCEPTION
  WHEN OTHERS THEN
    RETURN '000000000000';
END FNT_GEN_NO;