0828,10-30

dba_views에서 뷰검색

select view_name from dba_views

where view_name like 'DBA_%COLUMNS%";

 

검색한 뷰로 원하는 내용검색

SQL> r
  1  select column_name,data_type from dba_tab_columns
  2* where owner='HR' and table_name='DEPARTMENTS'

테이블삭제후 되살리기

 SQL> drop table emp2;

Table dropped.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------EMP2             BIN$VYLA42+uua7gQAB/AQA2nA==$0 TABLE        2008-08-28:19:39:37SQL> flashback table emp2 to before drop;

Flashback complete.

 쓰레기통안거치고 바로삭제

 SQL> drop table emp2 purge;

Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
YY                             TABLE
XX                             TABLE
EXCEPTIONS                     TABLE

7 rows selected.

 

 

 

 7-26

 scott계정의 인덱스모음 보기..

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_DEPT
PK_EMP
PK_DEPT2
PK_EMPX

 

인덱스 지워주기..(pk는 자동인덱스)

SQL> alter table empx drop constraint pk_empx;

Table altered.

인덱스 생성

SQL> create index ix_emp18_sal on emp(sal);

Index created.

 

 rowid값으로 바로 찾아 들어간다

SQL> select rowid,sal from emp order by sal;

ROWID                     SAL                                                                  <--- rowid는 7-26의 row pointer, sal은 key
------------------ ----------
AAAMfMAAEAAAAAgAAA        800
AAAMfMAAEAAAAAgAAL        950
AAAMfMAAEAAAAAgAAK       1100
AAAMfMAAEAAAAAgAAC       1250
AAAMfMAAEAAAAAgAAE       1250
AAAMfMAAEAAAAAgAAN       1300
AAAMfMAAEAAAAAgAAJ       1500
AAAMfMAAEAAAAAgAAB       1600
AAAMfMAAEAAAAAgAAG       2450
AAAMfMAAEAAAAAgAAF       2850
AAAMfMAAEAAAAAgAAD       2975

ROWID                     SAL
------------------ ----------
AAAMfMAAEAAAAAgAAH       3000
AAAMfMAAEAAAAAgAAM       3000
AAAMfMAAEAAAAAgAAI       5000

14 rows selected.

 

 인덱스의 기본기.

 SQL> select * from emp where rowid='AAAMfMAAEAAAAAgAAL';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7900 JAMES      CLERK           7698 03-DEC-81        950
        30

 

 

 

 B-Tree Index   -   데이터양이 많을때

와

BITMAP index   -   데이터양이 적을때

 

 7-32

 

em에서 만들어서 sql문 본것..

 CREATE INDEX "SCOTT"."IX_EMP_SAL" ON "SCOTT"."EMP" ("SAL")

 

unique

CREATE UNIQUE INDEX "SCOTT"."IX_EMP_SAL" ON "SCOTT"."EMP" ("SAL")

 

descending

CREATE INDEX "SCOTT"."IX_EMP_SAL" ON "SCOTT"."EMP" ("SAL" DESC )

 

reverse

CREATE INDEX "SCOTT"."IX_EMP_SAL" ON "SCOTT"."EMP" ("SAL") REVERSE

 

compress(어느정도 길이가 되어야함)

CREATE INDEX "SCOTT"."IX_EMP_SAL" ON "SCOTT"."EMP" ("SAL", "JOB") COMPRESS 1

 

composite(and검색시,ex)주민등번,unique인덱스와 같이 사용)

CREATE UNIQUE INDEX "SCOTT"."IX_EMP_SAL" ON "SCOTT"."EMP" ("SAL", "JOB")

 

 

 CREATE BITMAP INDEX "SCOTT"."TEST_IX" ON "SCOTT"."EMP" ("JOB") TABLESPACE "EXAMPLE"

 

 ALTER INDEX "SCOTT"."TEST_IX" REBUILD REVERSE

 

 CREATE INDEX "SYS"."IKKK" ON "SCOTT"."EMP" ("JOB", "MGR", upper(job))

 

 CREATE INDEX "SYS"."IKKK" ON "SCOTT"."EMP" ("JOB", "MGR", upper(job)) COMPRESS 1

 

 

 

 7-37

 시퀀스   -   PK의 기본값을 만드는것이 목적

em에서 만들었슴

 CREATE SEQUENCE "SCOTT"."TEST_SE" CYCLE NOORDER NOCACHE MAXVALUE 10 MINVALUE 2 INCREMENT BY 2 START WITH 3

 SQL> select TEST_SE.nextval from dual;

   NEXTVAL
----------
         3
..3.5.7.2.4.6.8.2.4.6.8....식으로 증가함.

 

 

 7-47

1번

select view_name from dba_views where view_name like 'DBA%TABLES%'

2번

desc DBA_TABLES

3번

 SQL> select TABLE_NAME,OWNER from DBA_TABLES where OWNER in('SCOTT','HR');

 

 

 

 SQL> desc v_$fixed_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------- NAME                                               VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 TYPE                                               VARCHAR2(5)
 TABLE_NUM                                          NUMBER

 

 

 

 SQL> desc dictionary
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------- TABLE_NAME                                         VARCHAR2(30)
 COMMENTS                                           VARCHAR2(4000)

 

 select * from dictionary; 하면 코맨트가 붙어있다.

TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------Synonym for GV_$STREAMS_CAPTURE

V$STREAMS_CAPTURE
Synonym for V_$STREAMS_CAPTURE

DBA_APPLY_VALUE_DEPENDENCIES
Synonym for _DBA_APPLY_CONSTRAINT_COLUMNS

DBA_APPLY_OBJECT_DEPENDENCIES

TABLE_NAME

 

 

 7-47의 a를 찾는다 할때의 방법..

1.   describe dba_indexes
2. select * from dictionary where table_name like '%table%';           
3. desc user_tables;

 

 

 

 8장

---

PL/SQL   -   자동화처리하는데 좋음.

Lock처리

 

PL/SQL

언어적 특징이 있다.

언어의 특징  

• 변수
• 조건문
• 반복문
• 함수
• 프로시져

 

함수와 프로시져의 차이점은

return값이 있느냐 없느냐..

 

 

기본필수

출력가능하게 하기

프로시져만들때 or replace

프로시져 만든후에 저장하기

 에러보기

 

 

출력하는법..

그냥 아래처럼 입력하면 출력이 안된다.

 SQL> exec dbms_output.put_line('AAAA');

PL/SQL procedure successfully completed.

 

 

show all하면 쭉 pl/sql 옵션값이 나오는데 이중에

serveroutput 을 on으로

 SQL> set serveroutput on                                    <- 해줘야 출력됨

 

SQL> exec dbms_output.put_line('AAAA');                     <- function
AAAA

PL/SQL procedure successfully completed.

 

 

 

 프로시져생성

or replace가 들어있어야 나중에 수정후 재실행이 가능해진다.

SQL> create or replace procedure test                              <- function을 묶은 프로시져, or replace는 ..프로시져에 반드시 들어가야함
  2  as
  3  begin
  4  dbms_output.put_line('AAAA');
  5  end;
  6  /

Procedure created.

 

SQL> exec test
AAAA

PL/SQL procedure successfully completed.

 

 SQL> sav test               <-- 만든후에는 꼭 세이브 시켜두자.
Created file test.sql

 

 

 에러 확인하는 방법

 SQL> ed test                                                                     <- 일부로 틀리게 만들고

컴파일은 @

SQL> @test                                                                              <- 실행

Warning: Procedure created with compilation errors.

 

SQL> show error                                                                              <- 에러를 보여달라.
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/26     PLS-00103: Encountered the symbol ");                                                <4/26 4번째 줄에 26번째에 오류가 떴다고 호출
         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> l                                                                                                   < 문장 출력
  1  create or replace procedure test
  2  as
  3  begin
  4  dbms_output.put_line(AAAA');
  5* end;

 

 

프로시져 만든것 보는 방법

 SQL> select object_name from user_procedures;

OBJECT_NAME
------------------------------
PT

 

 

 로딩시켜서 다른 이름으로 저장하기

 SQL> get pt2
  1  create or replace procedure pt2(su number,su2 number)
  2  as
  3  begin
  4  dbms_output.put_line(su+su2);
  5* end;
SQL> sav pt3

 

argument를 이용한

값을 입력받아서 출력하기.

 create or replace procedure pt2(su number)
as
begin
dbms_output.put_line(su);
end;
/

두개의 값을 입력받고 연산후 값을 출력하기.

 create or replace procedure pt2(su number,su2 number)
as
begin
dbms_output.put_line(su+su2);
end;
/

 

 

function 의 간단한예

 create or replace function pt3(su number,su2 number)
return number
as
begin
return su+su2;
end;
/

 SQL> @pt3

Function created.

 

실행할때는 함수실행,,

SQL> select pt3(4,5) from dual;

  PT3(4,5)
----------
         9

 

 

 피보나치 수열

create or replace procedure pt6
as
        a number(6);
        sum1 number(6);
        b number(6);
begin
a:=1;
b:=2;
sum1:=0;
for i in 1..100 loop
if sum1 > 100 then

i=100

else
sum1:=a+b;
a:=b;
b:=sum1;
        dbms_output.put_line(sum1);
end if;
end loop;
end;
/

 

 SQL> @pt6

Procedure created.

SQL>
SQL> exec pt6
3
5
8
13
21
34
55
89
144

PL/SQL procedure successfully completed.

 

 

결론...

pl/sql문으로 프로시져같은것을 만들어서

아래와 같은 문장을 테이블 tt에 넣어줄수도 있다.

 insert into tt values(dan||'X'||i||'='||dan*i);

 

 

 

 SQL> sav test2
Created file test2.sql
SQL> ed test2

 create or replace procedure test2
as
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/

 

SQL>
SQL>
SQL> @test2

Procedure created.

SQL> exec test2
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

ed로 수정후

@test2로 프로시져 생성하고

exec 프로시져명

으로 실행..

 

 

 

 

 SQL> ed test2

 create or replace procedure test2            <- 프로시져 시작
as
begin
for i in 1..9 loop               <- loop문 시작
dbms_output.put_line(2*i);         <- 출력문 종료
end loop;            <- loop문 종료
end;               <- 프로시져 종료
/

SQL> @test2

Procedure created.

SQL> exec test2
2
4
6
8
10
12
14
16
18

PL/SQL procedure successfully completed.

 

 SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure test3
  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;
SQL> @test3

Procedure created.

SQL> exec test3
2X1=2
2X2=4
2X3=6
2X4=8
2X5=10
2X6=12
2X7=14
2X8=16
2X9=18

PL/SQL procedure successfully completed.

 

 SQL> ed test3

create or replace procedure test3(DAN NUMBER)
as
begin
for i in 1..9 loop
dbms_output.put_line(DAN||'X'||i||'='||DAN*i);
end loop;
end;
/

SQL> @test3

Procedure created.

SQL> exec test3(3)               <-- 3단을 뽑기위해 3이란값을 넣어줄수있다..
3X1=3
3X2=6
3X3=9
3X4=12
3X5=15
3X6=18
3X7=21
3X8=24
3X9=27

PL/SQL procedure successfully completed.

 

 

 SQL> ed test3

 create or replace procedure test3(DAN NUMBER)
as
begin
        if DAN>1 AND DAN<10 THEN                                             <- 조건문 if문..
        for i in 1..9 loop
                dbms_output.put_line(DAN||'X'||i||'='||DAN*i);
        end loop;
        ELSE
                dbms_output.put_line('WRONG DAN..INPUT 2~9');
        END IF;
end;
/

SQL> @test3

Procedure created.

 SQL> exec test3(11)
WRONG DAN..INPUT 2~9

PL/SQL procedure successfully completed.

SQL> exec test3(6)
6X1=6
6X2=12
6X3=18
6X4=24
6X5=30
6X6=36
6X7=42
6X8=48
6X9=54

PL/SQL procedure successfully completed.

 DB에 로직을 두고...exec를 만들어서 호출시키는 것을 쓰면..

App,Web에서는 호출만 하게하면 됨..

PL/SQL은 개발회사면 기본으로 요구를 한다..

 

 

 테이블생성

 SQL> create table tm(id number(10),data varchar2(50),
  2  constraint tm_pk primary key(id));

Table created.

시퀀스생성
SQL> create sequence tm_s;

Sequence created.
프로시져생성

 SQL> r
  1  create or replace procedure tm_i
  2  as
  3  begin
  4  for i in 1..10 loop
  5  insert into tm values(tm_s.nextval,'AAAAA');
  6  end loop;
  7* end;

Procedure created.

SQL> sav tm_i
Created file tm_i.sql

프로시져로 테이블에 값 집어넣기..

 SQL> @tm_i

Procedure created.

SQL> exec tm_i

PL/SQL procedure successfully completed.
SQL> select count(*) from tm;

  COUNT(*)
----------
        10

 

 

 SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure tm_i(N number)
  2  as
  3  begin
  4  for i in 1..N loop
  5  insert into tm values(tm_s.nextval,'AAAAA');
  6  commit;
  7  end loop;
  8* end;
SQL>
SQL> sav tm_i replace
Wrote file tm_i.sql
SQL>
SQL>
SQL> ed tm_i;

SQL>
SQL>
SQL> @tm_i                                                      <-- 여기서는 sav를 먼저했는데..원래는 @를 먼저해야하는게 맞다!!

Procedure created.

SQL>
SQL>
SQL> exec tm_i(100)

PL/SQL procedure successfully completed.

SQL> select count(*) from tm;

  COUNT(*)
----------
       110

 SQL> exec tm_i(10000)

PL/SQL procedure successfully completed.

SQL> select count(*) from tm;

  COUNT(*)
----------
     10110

 

 

 tm_i는 계속쓰일꺼임

create or replace procedure tm_i(N number)
as
begin
for i in 1..N loop
insert into tm values(tm_s.nextval,'AAAAA');
commit;
end loop;
end;
/

 

 

 pl/sql관련 교재..

1.oracle 9i pl-sql.pdf

2.oracle datebase plsql usel

3.oracle 10g advanced plsql w...

 

 

 

 SQL> ed f1

create or replace function f1(su number)
return number
as
begin
return su*2;
end;
/

SQL> @f1                        <- ed로 수정한거 @로 보여주기 후 sav

Function created.

SQL> sav f1
SP2-0540: File "f1.sql" already exists.
Use "SAVE filename[.ext] REPLACE".
SQL> sav f1 replace
Wrote file f1.sql
SQL> ed f1

SQL> select f1(2) from dual;

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

 

 서브쿼리를 대신해 쓸수있다

변수사용,사용자 정의함수

 SQL> ed f2

create or replace function emp_max
return number
is
        mx number(6);            <- 변수크기지정.
begin
select max(sal) into mx from emp;
return mx;
end;
/

SQL>
SQL>
SQL> @f2

Function created.

SQL> select emp_max from dual;

   EMP_MAX
----------
      5000
SQL> select ename from emp where sal=emp_max;

ENAME
----------
KING