oracle 0828

컬럼 뷰의 내용을 보는 질의

SQL>  select view_name from dba_views
  2* where view_name like 'DBA_%COL%'

VIEW_NAME
------------------------------
DBA_CLU_COLUMNS
DBA_COL_COMMENTS
DBA_COL_PRIVS
DBA_ENCRYPTED_COLUMNS
DBA_IND_COLUMNS
DBA_JOIN_IND_COLUMNS

SQL>  select COLUMN_NAME, DATA_TYPE from DBA_TAB_COLUMNS
  2* where owner='HR' and table_name='DEPARTMENTS'
COLUMN_NAME                          DATA_TYPE
--------------------------------------------------------------------------------

DEPARTMENT_ID                     NUMBER
DEPARTMENT_NAME              VARCHAR2
MANAGER_ID                           NUMBER
LOCATION_ID                           NUMBER

PURGE: 즉시 삭제

SQL> drop table emp2 purge;

Table dropped.

Truncate

• DDL명령어(롤백 안됨, FlashBack은 됨)

• EXTENT 할당과 HWM정보까지 클리어

BITMAP INDEXES - DW전용, 문제점 : B-tree를 사용할 것인데 bitmap을 사용할시 200배 느림

SQL> create bitmap index emp_deptno_ix on emp(deptno)

INDEX

1 .Descending order

SQL> CREATE UNIQUE INDEX "SCOTT"."TEST_ID" ON "SCOTT"."EMP" ("JOB" DESC ) TABLESPACE "EXAMPLE"

2. function-based index: 대소문자 구별없이 검색

SQL> select * from emp where upper(job) = 'MANAGER'

 8장 PL/SQL

기본 출력문

SQL> set serveroutput on    <=   출력가능하게 만드는 문장 무조건
SQL> exec dbms_output.put_line('Hello World');
Hello World
SQL> create or replace procedure test
  2  as
  3  begin
  4     dbms_output.put_line('Hello World');
  5 end;

  6 */

Procedure created.

SQL> exec test
Hello World

에러구문 찾기

SQL> show error

 Errors for PROCEDURE TEST:

LINE/COL                ERROR
--------------- ----------------------------------------------------------
4/27                       PLS-00103: Encountered the symbol ");

줄/컬럼                   end;" when expecting one of the following:
                              . ( ) , * @ % & | = - + < / > at in is mod remainder not
                              range rem => .. <an exponent (**)> <> or != or ~= >= <= <>
                              and or like LIKE2_ LIKE4_ LIKEC_ as between from using ||
                              multiset member SUBMULTISET_

SQL> create or replace procedure test
  2  as
  3  begin
  4     dbms_output.put_line(AAAA');
  5* end;

반복문

 SQL> create or replace procedure test2
  2  as
  3  begin
  4     for i in 1..9 loop
  5     dbms_output.put_line('2 X '|| i ||' = ' || 2*i);
  6     end loop;
  7* end;

Procedure created.

SQL> exec test2
2 X 1 = 2
2 X 2 = 4
2 X 3 = 6
2 X 4 = 8
2 X 5 = 10
2 X 6 = 12
2 X 7 = 14
2 X 8 = 16
2 X 9 = 18

조건문

SQL> create or replace procedure test2(dan number)
2   as
3   begin
4        if dan>1 and dan<10 then
5        for i in 1..9 loop
6                dbms_output.put_line(dan || ' X ' || i || ' = ' || dan*i);
7        end loop;
8        else
9                dbms_output.put_line('Wrong dan');
10        end if;
11   end;
12* /
SQL> exec test2(11)
Wrong dan

PL/SQL procedure successfully completed.

SQL> exec test2(9)
9 X 1 = 9
9 X 2 = 18
9 X 3 = 27
9 X 4 = 36
9 X 5 = 45
9 X 6 = 54
9 X 7 = 63
9 X 8 = 72
9 X 9 = 81

함수만들기

 SQL> create or replace function f1(nu number)
  2  return number
  3  as
  4  begin
  5    return nu*2;
  6* end;
SQL> select f1(2) from dual;

     F1(2)
----------
         4

변수 사용

 SQL> create or replace function emp_max
  2  return number      
  3  is                              <=== 값 하나만 변수 선언할때
  4    mx number(6);
  5  begin
  6    select max(sal) into mx from emp;
  7    return mx;
  8* end;
SQL> select emp_max from dual;

   EMP_MAX
----------
      5000