반응형



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
/* 
    Table Type 변수(컬렉션)
    - 컬렉션 : 일반 프로그래밍 언어에서 사용하는 배열 타입을 PL/SQL에서는 컬렉션이라 한다.
    - 종류
        : 연관배열(associative array / index-by table) : 키와 값의 쌍으로 구성된 컬렉션
                                                       : JAVA의 해시테이블과 같은 개념
            - key 데이터 유형 : 숫자(binary_integer, pls_integer 
                                    - 이 데이터 타입은 number보다 작은 저장영역 필요, 
산술연산의 경우 number보다 빠르다 - )  
                              : 문자 (varchar2)
            - 값(value) 유형: 일반 데이터 타입 사용됨,레코드 데이터 타입사용가능- 
레코드 타입의 경우, 여러개의 값을 가질 수 있다.-
            
        : varray(variable array) : 고정 길이를 가진 배열
                                 : 일반 프로그래밍에서 사용하는 배열과 같다.
        : 중첩테이블(nested table) : varray와 흡사한 구조의 배열
                                   : 배열의 크기를 명시하지 않고, 동적으로 배열의 크기가 설정됨
                                   
    - Table Type 선언 형식
    1. 정의
        Type 타입명 is table of
        employees.first_name%Type
        INDEX BY binary_integer;
    2. 선언 ( 메모리화 - 변수의 공간이 메모리에 설정됨 )
        식별자 타입명;
*/
 
set serveroutput on;
 
declare
    tname varchar2(20);
    
    type t_emp_name is table of
    employees.last_name%type
    index by binary_integer;
    
    v_name t_emp_name;
    
begin
    select last_name into tname
    from employees
    where employee_id = 100;
    
    v_name(0) := tname;
    dbms_output.put_line(v_name(0));
end;
/
 
 
 
    
declare
    type tbl_type is table of 
    employees.last_name%type
    index by binary_integer;
    
    vtbl_type tbl_type;
    a binary_integer := 0;
    
begin
    for emp_name in(select last_name from employees) loop
        a:= a+1;
        vtbl_type(a):= emp_name.last_name;
    end loop;
 
    for i in 1..a loop
        dbms_output.put_line(vtbl_type(i));
    end loop;
 
end;
/
 
 
 
/*  
    바인드 변수 (비 PL/SQL 변수)
    - 호스트환경에서 생성되어 데이터를 저장하기 때문에 호스트 변수라고 한다
    - 키워드 variable을 이용하며, SQL문에서 사용 가능, PL/SQL블럭에서도 사용 가능
    - PL/SQL블록이 실행된 후에도 엑세스가 가능하다
    - print명령을 이용해 출력 가능
    - :을 붙여 이용한다
*/
 
set autoprint on;
 
begin
    select (salary*12 + nvl(commission_pct*salary,0)) into :vsal
    from employees
    where employee_id = 100;
end;
/
 
print vsal;

cs








반응형
저작자표시 비영리 변경금지

'프로그래밍 > SQL' 카테고리의 다른 글

[Oracle SQL] ROWTYPE 변수 및 복합변수 활용  (0) 2017.09.08
[Oracle SQL] PL/SQL 변수 선언 및 데이터 타입  (0) 2017.09.06
[Oracle SQL] PL/SQL  (0) 2017.08.21
[Oracle SQL] 계층형 쿼리  (0) 2017.08.17
[Oracle SQL] Sequence  (0) 2017.08.16
반응형





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
/* ROWTYPE 변수 및 복합변수 활용 */
/* rowType 변수를 활용한 데이터의 변경 */
declare
    c_rec row_test%rowtype;
    
begin
    select * into c_rec
    from row_test
    where no = 3;
    
    c_rec.name := '강길동';
    
    update row_test2 
    set row=c_rec
    where no = 3;
 
end;
/
 
select * from row_test2;
 
 
 
/* 
   사용자로부터 두개의 숫자를 입력받아 합을 구하는 예
   치환연산자 & 사용 
 */
 
declare
    no1 number := '&no1';
    no2 number := '&no2';
    vsum number;
 
begin
    vsum := no1 + no2;
    dbms_output.put_line('첫번째 수 : ' || no1 ||', 두번째 수 : '|| no2 || ', 합 : '|| vsum || '입니다.');
 
end;
/
 
 
/* 
   복합변수
   레코드 타입 변수 지정 방법
    - type 타입명 is record()
    - 식별자 type 타입명  
*/
declare
    type emp_rec is record
    (emp_id employees.employee_id%type,
     emp_name employees.first_name%type,
      emp_job employees.job_id%type
    );
 
    rec1 emp_rec;
 
begin
    select employee_id, first_name, job_id
    into rec1
    from employees
    where department_id = 10;
 
    dbms_output.put_line('사번              이름               업무아이디');
    dbms_output.put_line(rec1.emp_id || '     ' || rec1.emp_name || '       '|| rec1.emp_job);
     
end;
/
 
 
declare
    type emp_rec2 is record(
        emp_id employees.employee_id%type,
        emp_name employees.last_name%type,
        emp_email employees.email%type,
        emp_salary employees.salary%type
    );
    
    rec2 emp_rec2;
    
    vemp_id employees.employee_id%type := '&empid';
    
begin
    select employee_id, last_name, nvl(email,'없음'), salary
    into rec2
    from employees
    where employee_id = vemp_id;
    
    dbms_output.put_line('사번 : '||rec2.emp_id);
    dbms_output.put_line('이름 : '||rec2.emp_name);
    dbms_output.put_line('이메일 : '||rec2.emp_email);
    dbms_output.put_line('급여 : '||rec2.emp_salary);
 
end;
/
cs


반응형
저작자표시 비영리 변경금지

'프로그래밍 > SQL' 카테고리의 다른 글

[Oracle SQL] 콜렉션, 바인드 변수 사용  (0) 2017.09.30
[Oracle SQL] PL/SQL 변수 선언 및 데이터 타입  (0) 2017.09.06
[Oracle SQL] PL/SQL  (0) 2017.08.21
[Oracle SQL] 계층형 쿼리  (0) 2017.08.17
[Oracle SQL] Sequence  (0) 2017.08.16
반응형






1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
/* 
    변수
    - 변수의 생성 규칙
        : 반드시 문자로 시작해야 한다
        : 문자나 숫자, 특수문자를 포함할 수 있다
        : 변수명은 30byte이하여야 한다
        : 예약어(키워드)를 사용할 수 없다
        
    - 변수의 선언은 선언부(declare)에서 선언, 값으로 초기화 가능
    - 실행부에서 실행될 경우 값이 할당된다
    - 서브프로그램의 파라미터로 전달되기도 하며, 서브 프로그램의 출력결과를 저장하기도 한다
    
    - 변수 선언 예
    emp_no number(6,3) : 숫자를 저장하는 변수로 총6자리를 의미하며, 소수점 이하 3자리를 의미한다.
    emp_name varchar2(5) : 문자를 저장하는 변수로 총 5byte를 저장할 수 있다.
    emp_date date : 날짜를 저장하는 변수
    
    - 변수의데이터 타입
    char : 고정길이의 문자를 저장, 기본최소값 1 ~ 최대 32,767byte를 저장
    varchar2 : 가변길이의 문자를 저장, 기본값은 없다. 최대 32,767byte 값을 저장
    number(전체자릿수, 소수점이하 자릿수) : 전체자릿수와 소수점이하 자리 수를 가진 숫자를 저장 
        전체자리 수 범위는 1~38까지 가능하고, 소수점 자리수의 범위는 -84~127까지 가능
    
    binary_double : 부동 소수점 수를 저장, 9byte 필요
    date : 날짜 및 시간을 저장, 초단위로 저장
           날짜의 범위는 4712B.C ~ 9999A.D
    timestamp : date 타입을 확장한 개념, 연도 / 월 / 일 / 시 / 분 /초 및 소수로 표시되는 초단위를 저장
                자리수를 표현할 때는 0~9 범위의 정수를 사용, 기본값은 6
    
    - 참조변수 
        테이블명.필드명%TYPE 
        empNo empolyees.employee_id%TYPE  <- employees테이블의 employee_id타입을 empNo의 타입으로 사용하겠다는 의미
    
        emp_name employees.first_name%TYPE <- employees테이블의 first_name과 동일한 데이터 타입으로 선언
        
        empRow employees%ROWTYPE <- employees테이블의 모든 칼럼을 한꺼번에 저장하기위한 변수로 선언
    
*/
create table employees1 as 
select employee_id, salary, department_id
from employees;
 
select * from employees1;
 
set serveroutput on;
 
 
 
declare
    empNo employees1.employee_id%TYPE;
    empSalary employees1.salary%TYPE;
    
begin
    select employee_id, salary
    into empNo, empSalary
    from employees1
    where department_id = 10;
 
    dbms_output.put_line(empNo ||' '||empSalary);
    
end;
/
 
 
 
 
 
declare
emp_row employees1%RowType;
 
begin
    select * into emp_row
    from employees1
    where employee_id = 100;
    
    dbms_output.put_line( emp_row.employee_id || ' ' || emp_row.salary || ' ' || emp_row.department_id );
 
end;
/
 
 
 
 
create table row_test(
    no number,
    name varchar2(20),
    hdate date
);
 
 
create table row_test2 as
select * from row_test;
 
 
insert into row_test 
values(1,'아무개',sysdate);
 
 
insert into row_test
values(2,'홍길동',sysdate);
 
 
insert into row_test
values(3,'고길동',sysdate);
 
 
select * from row_test;
 
 
commit;
 
 
 
 
declare
c_rec row_test%ROWTYPE;
 
begin
    select * into c_rec
    from row_test
    where no=3;
    
    insert into row_test2
    values c_rec;
    
 end;
/
 
 
select * from row_test2;
cs


반응형
저작자표시 비영리 변경금지

'프로그래밍 > SQL' 카테고리의 다른 글

[Oracle SQL] 콜렉션, 바인드 변수 사용  (0) 2017.09.30
[Oracle SQL] ROWTYPE 변수 및 복합변수 활용  (0) 2017.09.08
[Oracle SQL] PL/SQL  (0) 2017.08.21
[Oracle SQL] 계층형 쿼리  (0) 2017.08.17
[Oracle SQL] Sequence  (0) 2017.08.16
반응형






1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/*  
   PL/SQL (Procedural Language / SQL)
   :오라클에서 제공하는 프로그래밍 언어
   :일반 프로그래밍 언어적인 요소를 다 가지고 있어 데이터베이스 업무를 처리하기 위한 최적화된 언어
   
   //기본 구조
   - 선언부(Declare) : 모든 변수나 상수를 선언하는 부분
   - 실행부(Executable) : begin ~ end / 제어문, 반복문, 함수정의 등의 로직을 기술하는 부분
   - 예외처리부(Exception) : 실행도중 에러발생 시 문제를 해결하기위한 명령들을 기술하는 부분
   
   Declare, begine, exception 키워드들은 ';'을 붙이지 않는다.
   나머지 문장들은 ';'으로 처리한다
   
   //처리
   - 익명블록(Anonymous PL/SQL Block) : 주로 1회성으로 사용할 경우 많이 사용
   - 저장블록(Stored PL/SQL Block) : 서버에 저장해 놓고 주기적으로 반복해서 사용할 경우 사용
*/
set serveroutput on; /* set serveroutput off;  */
 
declare
    cnt integer;
begin
    cnt := cnt+1;  /* 할당연산자 ':=' */
    if cnt is null then
        dbms_output.put_line('결과 : cnt는 null');  /* 출력문 */
    end if;
end;
/  /* 실행하라는 의미 */
 
 
declare
    empNo number(20);
    empName varchar2(10);
begin
    select employee_id, first_name into empNo, empName
    from employees
    where employee_id = 124;
    
    dbms_output.put_line(empNo || ' ' || empName);
end;
/
 
 
cs


반응형
저작자표시 비영리 변경금지

'프로그래밍 > SQL' 카테고리의 다른 글

[Oracle SQL] ROWTYPE 변수 및 복합변수 활용  (0) 2017.09.08
[Oracle SQL] PL/SQL 변수 선언 및 데이터 타입  (0) 2017.09.06
[Oracle SQL] 계층형 쿼리  (0) 2017.08.17
[Oracle SQL] Sequence  (0) 2017.08.16
[Oracle SQL] VIEW  (0) 2017.08.16
반응형



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
/* 
    계층형 쿼리 
        : 오라클에서만 제공됨
*/
 
create table bom_sphone(
    item_id number(3) not null,
    parent_id number(3),
    item_name varchar2(20) not null,
    primary key (item_id)
);
 
 
insert into bom_sphone values(100,null,'스마트폰');
insert into bom_sphone values(101,100,'메인pc');
insert into bom_sphone values(102,100,'배터리');
insert into bom_sphone values(103,101,'cpu');
insert into bom_sphone values(104,101,'메모리');
insert into bom_sphone values(105,101,'블루투스');
 
 
select * from bom_sphone;
 
 
 
select  s1.item_name, s1.item_id,s2.item_name parent_name
from bom_sphone s1, bom_sphone s2
where s1.parent_id = s2.item_id (+)
order by s1.item_id;
 
 
 
/* start with, connect by절을 이용한 계층형 쿼리를 할 수 있다 */
 
select lpad('*', 2*(level-1)) || item_name itemnames
from bom_sphone
start with parent_id is null /* 최상위 nod지정 */
connect by prior item_id = parent_id; 
 
 
 
/* 
    prior는 부모열을 찾아주는 의미 
    = connect by parent_id = prior item_id
*/
 
select level, lpad(' ', 4*(level-1)) || first_name||' '||last_name "이름"
from employees
start with manager_id is null /* 부모가 없으므로 최상위가 됨 */
connect by manager_id = prior employee_id;
 
 
select job.job_title, lpad(' ', 4*(level-1)) || emp.first_name||' '||emp.last_name "이름"
from employees emp, jobs job
where emp.job_id = job.job_id
start with emp.manager_id is null 
connect by emp.manager_id = prior emp.employee_id;
cs








반응형
저작자표시 비영리 변경금지

'프로그래밍 > SQL' 카테고리의 다른 글

[Oracle SQL] PL/SQL 변수 선언 및 데이터 타입  (0) 2017.09.06
[Oracle SQL] PL/SQL  (0) 2017.08.21
[Oracle SQL] Sequence  (0) 2017.08.16
[Oracle SQL] VIEW  (0) 2017.08.16
[Oracle SQL] 무결성 제약조건  (0) 2017.08.12
반응형





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
/* 
    Sequence (시퀀스) 
        : 순서
        : 연속적인 번호를 만들어 주는 기능
        : 각각의 레코드가 추가될 때마다 자동으로 줄번호가 생기는 것
        : mySQL에서 auto_increment 사용
        : 오라클에서는 시퀀스 제공
    
    구문형식
    create sequence 시퀀스 이름
       
        increment by n <- 증가값(n) 설정, 기본값은 1 
        start with n <- 시작값(n) 설정, 기본값은 1
       
        maxvalue n   <- 시퀀스 최대값(n) 설정
        nomaxvalue n  <- 맥스를 설정하지 않고 계속 증가하게 함
       
        minvalue n <- 시퀀스 최소값 : cycle옵션일 경우 시작 값
        nominvalue n <- 최소를 설정하지 않고 계속 증가하게 함
        
        cycle <- 시퀀스를 순환사용할지 설정
        nocycle
       
        cache n <- 시퀀스의 속도를 개선하기 위해 캐싱여부 지정
        no cache n
    
*/
 
/* 시퀀스 생성 : 제품번호 생성하는 시퀀스 만들기 */
 
create sequence seq_serial_no
increment by 1
start with 100
maxvalue 110
minvalue 99
cycle
cache 2;
 
 
 
create table good(
    good_no number(3),
    good_name varchar2(10)    
);
 
 
 
insert into good 
values (seq_serial_no.nextval, '제품1');
 
insert into good 
values (seq_serial_no.currval, '제품1');
 
 
 
 
/*  
   nextval : 다음 value
   currval : 현재 value
*/
 
insert into good 
values (seq_serial_no.nextval, '제품2');
 
 
select * from good;
 
 
select seq_serial_no.currval from dual;
 
 
 
/* 
    제품번호가 Maxvalue인 110까지 생성된 이후(Cycle이 끝난 후) 한번 더 생성하면
    minvalue인 99로 생성됨
 */
 
insert into good 
values (seq_serial_no.nextval, '제품12');
 
 
create table good2(
    good_no number(3),
    good_name varchar2(10)
    );
    
 
create sequence seq_serial_no2
increment by 1
start with 100
maxvalue 105
cache 2;
 
 
insert into good2
values (seq_serial_no2.nextval, '제품1');
commit;
 
 
select * from good2;
 
 
 
/* 
    데이터베이스 다운시키는 명령어
    cmd창에서 입력
    sqlplus 
    hr
    비번입력
    select * from good2;
    
    conn sys/비번 as sysdba;
    
    shutdown abort;
    데이터베이스 시작시키는 명령어
    starup
    conn hr /비번
    select * from good2;
    
    insert into good2 values(seq_serial_no2.nextval,'제품2');
    select * from good2;
    
 */
 
 
/* 시퀀스 삭제 */
drop sequence 시퀀스명;
 
 
/* 
    시퀀스 감소
    increment by 1 값에 음수를 적용 
*/
cs


반응형
저작자표시 비영리 변경금지

'프로그래밍 > SQL' 카테고리의 다른 글

[Oracle SQL] PL/SQL  (0) 2017.08.21
[Oracle SQL] 계층형 쿼리  (0) 2017.08.17
[Oracle SQL] VIEW  (0) 2017.08.16
[Oracle SQL] 무결성 제약조건  (0) 2017.08.12
[Oracle SQL] INSERT, UPDATE, DELETE, COMMIT, ROLLBACK  (0) 2017.08.10
반응형







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
/* 
    View (뷰) : table과 유사하며, 테이블처럼 사용한다
              : 데이터를 저장하기 위한 물리적 공간이 필요하지 않은 가상테이블
              : 데이터를 물리적으로 가지지 않지만 논리적인 집합을 가짐
              : 테이블과 마찬가지로 select, insert, update, delete 명령어가 가능하다
              : create을 사용해서 view를 생성함
              
    사용이유
        : 보안관리
            - 보안등급에 맞춰 컬럼 및 범위를 정해 조회하도록 함
            - 연산 결과만 제공하고 알고리즘을 숨기기 위해 사용
            - Selectlist를 함수로 가공하여 update, insert를 못하도록 함
            - 테이블 명이나 컬럼 명을 숨기도록 함
            
        : 사용편의성
            - 검색조건을 단순화하여 사용할 수 있도록 함
            - 조인을 단순화
            - 사용자를 위한 컬럼명이나 테이블명 제공
*/
 
/* View 생성 */
 
create view v_emp(emp_id, first_name,job_id, hiredate, dept_id) as
select employee_id, first_name, job_id, hire_date, department_id
from employees
where job_id = 'ST_CLERK';
 
 
 
select * from v_emp;
 
 
 
create view v_emp1(emp_id, first_name,job_id, hiredate, dept_id) as
select employee_id, first_name, job_id, hire_date, department_id
from employees
where job_id = 'SH_CLERK';
 
 
 
select * from v_emp1;
 
 
 
 
/* View 관리 */
 
select * from v_emp1;
 
 
 
/* View 삭제 */
 
drop view v_emp1;
 
desc v_emp1;
 
 
 
/* View 수정 */
 
create or replace view v_emp(emp_id, first_name,job_id, hiredate, dept_id) as
select employee_id, first_name, job_id, hire_date, department_id
from employees
where job_id = 'SH_CLERK';
 
select * from v_emp;
 
 
 
/* View활용 */
/* 
    보안 
    nvl (컬럼명,null) - 데이터를 수정할 수 없도록 함
*/
create view v_emp3(emp_id, first_name,job_id, hiredate, dept_id) as
select employee_id , nvl(first_name, null), job_id, hire_date, department_id
from employees
where job_id = 'SH_CLERK';
 
 
select * from v_emp3;
 
 
 
/* 에러발생 */
 
update v_emp3 set first_name = 'kim'
where first_name = 'Julia'; 
 
select * from v_emp3;
 
 
 
update v_emp set first_name = 'kim'
where first_name = 'Julia'; 
 
select * from v_emp;
 
 
 
 
/* 연산과정 숨기는 방법 */
 
create view v_emp_salary(emp_id, last_name, annual_sal) as
select employee_id, last_name, (salary+nvl(commission_pct,0)) * 12
from employees;
 
 
select * from v_emp_salary;
 
 
 
 
/* read only : 데이터 수정 불가 */
 
create view v_emp_readonly(emp_id, last_name, annual_sal) as
select employee_id, last_name, (salary+nvl(commission_pct,0)) * 12
from employees
with read only;
 
 
select * from v_emp_readonly;
desc v_emp_readonly;
 
 
 
 
/* 읽기전용이므로 수정 불가  */
 
update v_emp_readonly
set last_name ='kim'
where last_name='Grant';
 
 
 
/* 사용자 편의성 */
 
create view v_samp1 as
select employee_id, last_name, department_id, hire_date
from employees
where (salary+nvl(commission_pct,0))*12 > 30000
  and department_id = 50
  and job_id = 'ST_CLERK'
  and sysdate - 365 * 5> hire_date ;
 
 
 
select * from v_samp1;
 
 
 
create view 사원 (사번, 이름, 부서번호, 입사일) as
select employee_id, first_name ||' '||last_name, department_id, hire_date 
from employees
where department_id = 50;
 
 
select * from 사원;
 
 
 
/* Join table View */
 
create view v_join(사번, 이름, 부서번호, 부서명, 입사일) as
select emp.employee_id, emp. first_name ||' '||last_name, emp.department_id, 
       dept.department_name, emp.hire_date
from employees emp, departments dept
where emp.DEPARTMENT_ID = dept.DEPARTMENT_ID;
 
select * from v_join;
cs


반응형
저작자표시 비영리 변경금지

'프로그래밍 > SQL' 카테고리의 다른 글

[Oracle SQL] 계층형 쿼리  (0) 2017.08.17
[Oracle SQL] Sequence  (0) 2017.08.16
[Oracle SQL] 무결성 제약조건  (0) 2017.08.12
[Oracle SQL] INSERT, UPDATE, DELETE, COMMIT, ROLLBACK  (0) 2017.08.10
[Oracle SQL] CREATE, ALTER, DROP, TRUNCATE  (0) 2017.08.10
반응형






1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
/* 
    제약조건 : 데이터 저장 시 저장가능한 조건이 존
    무결성 제약조건 = 컬럼 속성
    NOT NULL : 널값이 입력되지 못하게하는 조건
    UNIQUE : 중복된 값이 입력되지 못하게하는 조건 (NULL값 허용가능)
    PRIMARY KEY : NOT NULL + UNIQUE의 의미
                : 데이터 식별 시 유일성을 가짐
    FOREIGN KEY (외래키) : 다른테이블 칼럼 참조해서 무결성을 검사하는 조건
    CHECK : 주어진값만 허용하는 조건
*/
/* NULL : 데이터가 없음을 의미 */
 
create table null_test(
    col1 varchar2(20) not null,
    col2 varchar2(20) null,
    col3 varchar2(20) 
    );
 
 
desc null_test;
 
 
insert into null_test (col1,col2)
values ('aaa','bb');
 
 
select * from null_test;
 
 
/* col1이 not null이므로 에러발생 */
 
insert into null_test (col2,col3)
values ('cc','dd');
 
 
 
/* Unique  */
 
create table unique_test(
    col1 varchar2(20) unique not null,
    col2 varchar2(20) unique,
    col3 varchar2(20) not null,
    col4 varchar2(20) not null,
    constraints temp_unique unique(col3,col4)
    );
 
 
 
/* 
    constraints : 강제로 제약을하겠다는 의미
                : col3과 col4을 조합한 값을 unique로 사용하겠다는 의미
                : temp_unique는 unique의 이름
*/
 
 
insert into unique_test (col1,col2,col3,col4)
values ('aa','bb','cc','dd');
 
 
insert into unique_test (col1,col2,col3,col4)
values ('a2','b2','c2','d2');
 
 
select * from unique_test;
 
 
/* 무결성제약조건 위배 : col1의 값은 unique해야하는데 이미 aa가 존재하므로 에러 */
 
update unique_test
set col1 = 'aa'
where col2 = 'b2';
 
 
 
/* Unique는 null을 허용하므로 에러없음 */
 
insert into unique_test (col1,col2,col3,col4)
values ('a3','','c3','d3');
 
 
insert into unique_test( col1,col2,col3,col4)
values ('a4','','c4','d4');
 
 
 
/* Primary Key : 기본키 (null허용하지 않음) */
/* 테이블 생성 시 기본 키 생성방법 */
 
create table primary_test(
    student_id number(10) primary key, /* 인라인 방식 */
    name varchar2(20)
);
 
 
create table primary_test(
    student_id number(10), 
    name varchar2(20),
    constraints student_pk primary key(student_id) /* 아웃라인 방식 */
);
 
 
/*  
    인라인, 아웃라인 차이
    primary key의 이름을 명시하느냐 오라클에서 자동으로 생성하느냐의 차이
*/
 
desc primary_test;
 
 
/* 테이블 생성 후 기본키 생성방법 */
 
alter table primary_test
add constraints "기본키 이름" primary key(필드명);
 
 
 
/* 
    FOREIGN KEY : 외래키
                : 부모테이블이 존재해야 자식테이블에서 외래키 사용 가능
 */
 
create table foreign_key(
    department_id constraints dept_fk 
    references departments (department_id) /* 인라인 */
    );
 
 
create table foreign_key(
    department_id
    constraints dept_fk 
    foreign key (department_id)
    references departments(department_id) /* 아웃라인 */
    );
 
 
 
/* 테이블을 생성하고 나서 외래키를 지정하는 방법 */
 
alter table foreign_key
add constraints dept_fk foreign key (department_id)
references departments(department_id);
 
 
 
/* 자식테이블에서 사용하지 않은 값은 부모테이블에서 삭제 가능 */
/* Check : 입력값을 체크해서 조건에 해당하지 않으면 입력불가 */
 
create table check_test(
    gender varchar2(10) not null
    constraints check_sex check(gender in('M','F'))
    );
 
 
create table check_test(
    gender varchar2(10) not null
    constraints check_sex check(gender in('남성','여성'))
    );
 
 
 
/* 체크 제약조건에 위배되므로 에러 */
 
insert into check_test
values('남자');
 
 
insert into check_test
values('여성');
 
 
select * from check_test;
cs



반응형
저작자표시 비영리 변경금지

'프로그래밍 > SQL' 카테고리의 다른 글

[Oracle SQL] Sequence  (0) 2017.08.16
[Oracle SQL] VIEW  (0) 2017.08.16
[Oracle SQL] INSERT, UPDATE, DELETE, COMMIT, ROLLBACK  (0) 2017.08.10
[Oracle SQL] CREATE, ALTER, DROP, TRUNCATE  (0) 2017.08.10
[Oracle SQL] SubQuery 서브쿼리  (0) 2017.08.10

+ Recent posts

티스토리툴바