ORACLE随笔

****************使用表名%rowtype****************

****************给部门表插入记录****************

DECLARE 
--定义部门表记录
dept_record dept%ROWTYPE;
BEGIN
  --初始化变量中的值
  dept_record.deptno := 'cccc';
  dept_record.dname  := 'programer';
  dept_record.loc    := 'green';
  INSERT INTO dept VALUES (dept_record.deptno,dept_record.dname,dept_record.loc);
  --提交记录
  COMMIT;
  EXCEPTION WHEN OTHERS THEN 
    ROLLBACK;
    dbms_output.put_line('ddddd');
END;

注意：异常需要我们手动回滚。

****************定义PL/SQl表****************

DECLARE
TYPE emp_table_type IS TABLE OF emp%ROWTYPE  INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
--定义游标
CURSOR emp_cursor IS SELECT ename,empno FROM emp;
BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor BULK COLLECT INTO emp_table;
  CLOSE emp_cursor;
  FOR v_i IN 1..emp_table.count LOOP
      dbms_output.put_line(emp_table(v_i).ename);
  END LOOP;
END;

****************使用RAISE****************

BEGIN
    DELETE FROM emp WHERE empno = 33;
    RAISE no_data_found; --想当于JAVA中throw一个异常
    EXCEPTION WHEN OTHERS THEN 
      dbms_output.put_line('ddd');
      ROLLBACK;
END;

****************代码块的嵌套***************

BEGIN
  BEGIN
    dbms_output.put_line('0');
    GOTO test_1;
    RAISE no_data_found;    
    EXCEPTION WHEN OTHERS THEN 
      dbms_output.put_line('1');
      ROLLBACK;
  END;
  dbms_output.put_line('2');
  <<test_1>>
  dbms_output.put_line('4');
END;

目的:异常处理之后执行后面的代码。（结构化程序设计语言，要了解代码的运行流程）

****************ORACLE制造列（当前年的前五年和后五年）****************

 SELECT DECODE(B.ROWN,
              1,
              COUNTS - 5,
              2,
              COUNTS - 4,
              3,
              COUNTS - 3,
              4,
              COUNTS - 2,
              5,
              COUNTS - 1,
              6,
              COUNTS,
              7,
              COUNTS + 1,
              8,
              COUNTS + 2,
              9,
              COUNTS + 3,
              10,
              COUNTS + 4,
              11,
              COUNTS + 5)
  FROM (SELECT TO_CHAR(SYSDATE, 'YYYY') AS COUNTS FROM DUAL) A,
       (SELECT ROWNUM ROWN FROM ALL_OBJECTS WHERE ROWNUM <= 11) B

　　　　　　　　　　*******************后期*******************

 SELECT DECODE(B.lev,
              1,
              COUNTS - 5,
              2,
              COUNTS - 4,
              3,
              COUNTS - 3,
              4,
              COUNTS - 2,
              5,
              COUNTS - 1,
              6,
              COUNTS,
              7,
              COUNTS + 1,
              8,
              COUNTS + 2,
              9,
              COUNTS + 3,
              10,
              COUNTS + 4,
              11,
              COUNTS + 5)
  FROM (SELECT TO_CHAR(SYSDATE, 'YYYY') AS COUNTS FROM DUAL) A,
       (SELECT LEVEL lev FROM dual CONNECT BY LEVEL <= 7)B

 

 *******************行转列*******************

select * from t1 pivot(max(phone) for type in ('1' as home ,'2' as mobile,'3' as office));

 *******************给AID用户下的HD_DC_PARTY_LEARN表建同义词 *******************

  create synonym apps.HD_DC_PARTY_LEARN for AID. HD_DC_PARTY_LEARN;
  grant all on AID. HD_DC_PARTY_LEARN to apps with grant option;