국비교육 32일차 Oracle : 명시적 CURSOR, FOR LOOP CURSOR, PACKAGE(패키지), Constraint(제약조건)
1. 명시적 CURSOR
PL/SQL 에서 SELECT 되어져 나오는 행의 개수가 2개 이상인 경우에는 table 타입의 변수를 사용하여 나타낼 수 있고, 또는 CURSOR 를 사용하여 나타낼 수도 있다. table 타입의 변수를 사용하는 것 보다 CURSOR 를 사용하는 것이 더 편하므로 대부분 CURSOR 를 많이 사용한다.
1) 형식
1단계 -- CURSOR 의 선언(정의)
CURSOR 커서명
IS
SELECT 문;
2단계 -- CURSOR 의 OPEN
OPEN 커서명;
3단계 -- CURSOR 의 FETCH
(FETCH 란? SELECT 되어진 결과물을 끄집어 내는 작업을 말한다)
FETCH 커서명 INTO 변수;
4단계 -- CURSOR 의 CLOSE
CLOSE 커서명;2) 커서의 속성 변수
1. 커서명%ISOPEN ==> 커서가 OPEN 되어진 상태인가를 체크하는 것.
만약에 커서가 OPEN 되어진 상태이라면 TRUE.
2. 커서명%FOUND ==> FETCH 된 레코드(행)이 있는지 체크하는 것.
만약에 FETCH 된 레코드(행)이 있으면 TRUE.
3. 커서명%NOTFOUND ==> FETCH 된 레코드(행)이 없는지 체크하는 것.
만약에 FETCH 된 레코드(행)이 없으면 TRUE.
4. 커서명%ROWCOUNT ==> 현재까지 FETCH 된 레코드(행)의 갯수를 반환해줌.3) 예시
create or replace procedure 프로시저명(변수명 IN 테이블명.컬럼명%type) -- 파라미터
is
-- 1단계 cursor의 선언
CURSOR 커서명
IS
SELECT 문;
begin
-- 2단계 -- CURSOR 의 OPEN
OPEN 커서명;
-- 3단계 -- CURSOR 의 FETCH
-- (FETCH 란? SELECT 되어진 결과물을 끄집어 내는 작업을 말한다)
LOOP -- SELECT 되어진 행만큼 반복해주어야한다.
FETCH 커서명 INTO 변수명 --select 되어진 순서대로 적어주어야 한다.
...
END LOOP;
-- 4단계 -- CURSOR 의 CLOSE
CLOSE 커서명;
end 프로시저명;2. FOR LOOP CURSOR
FOR LOOP CURSOR 문을 사용하면
커서의 OPEN, 커서의 FETCH, 커서의 CLOSE 가 자동적으로 발생되어지기 때문에 우리는 커서의 OPEN, 커서의 FETCH, 커서의 CLOSE 문장을 기술할 필요가 없다.
1) 형식
FOR 변수명(select 되어진 행의 정보가 담기는 변수) IN 커서명 LOOP
실행문장;
END LOOP;2) 예시
create or replace procedure 프로시저명(파라미터)
is
-- 1단계 -- CURSOR 의 선언(정의)
CURSOR 커서명
IS
SELECT 문
begin
-- 2단계
FOR 변수명 IN 커서명 LOOP
-- select 되어진 행의 정보가 담기는 변수
실행문장;
END LOOP;
end 프로시저명;3. PACKAGE(패키지)
- 여러개의 Procedure 와 여러개의 Function 들의 묶음
1) 패키지 선언하기
create or replace package 패키지명
is
-- 패키지에 들어올 프로시저 혹은 함수를 선언해준다.
procedure 프로시저명(파라미터);
function 함수명(파라미터) return 타입;
end 패키지명2) 패키지의 본문(BODY) 생성하기
create or replace package body 패키지명
is
procedure 프로시저명(파라미터)
is ...
begin ...
end 프로시저명;
function 함수명(파라미터)
return 타입
is ...
begin ...
end 함수명;
end 패키지명;- 생성된 패키지 실행해보기
begin
패키지명.프로시저명(입력값);
end;
select employee_pack.func_gender('9007301234567')
from dual;
3) 패키지 소스 보기
select line, text
from user_source
where type = 'PACKAGE' and name = '패키지명';
-- 선언부만 나온다4) 패키지 BODY(본문) 소스 보기
select line, text
from user_source
where type = 'PACKAGE BODY' and name = '패키지명';4. Constraint(제약조건)
1) 제약조건(constraint)의 종류
- 제약조건의 이름은 오라클 전체에서 고유해야한다.
(1) Primary Key(기본키, 대표식별자) 제약 [P]
- 하나의 테이블당 오로지 1개만 생성할 수 있다.
- 어떤 컬럼에 Primary Key(기본키) 제약을 주면 그 컬럼에는 자동적으로 NOT NULL 이 주어지면서 동시에 그 컬럼에는 중복된 값은 들어올 수 없고 오로지 고유한 값만 들어오게 되어진다.
- 컬럼 1개를 가지고 생성된 Primary Key 를 Single Primary Key 라고 부르고,
- 컬럼 2개 이상을 가지고 생성된 Primary Key 를 Composite(복합) Primary Key 라고 부른다.
(2) Unique 제약 [U]
- 하나의 테이블당 여러개를 생성할 수 있다.
- 어떤 컬럼에 Unique 제약을 주면 그 컬럼에는 NULL 을 허용할 수 있으며, 그 컬럼에는 중복된 값은 들어올 수 없고 오로지 고유한 값만 들어오게 되어진다.
- Unique Key 중에 후보키, 후보식별자가 되려면 해당 컬럼은 NOT NULL 이어야 한다.
- 후보키로 알림 등을 보내야 하기 때문에, 인증키로 사용하기 위해서는 not null이어야 한다.
(3) Foreign Key(외래키) 제약 [R]
- 하나의 테이블당 여러개를 생성할 수 있다.
- Foreign Key(외래키) 제약에 의해 참조(Reference)받는 컬럼은 반드시 NOT NULL 이어야 하고, 중복된 값을 허락하지 않는 고유한 값만 가지는 컬럼이어야 한다. --> primary key를 의미한다
(4) Check 제약 [C]
- 하나의 테이블당 여러개를 생성할 수 있다.
- insert(입력) 또는 update(수정) 시 어떤 컬럼에 입력되거나 수정되는 데이터값을 아무거나 허락하는 것이 아니라 조건에 맞는 데이터값만 넣고자 할 경우에 사용되는 것이다.
(5) NOT NULL 제약 [C]
- 하나의 테이블당 여러개를 생성할 수 있다.
- 특정 컬럼에 NOT NULL 제약을 주면 그 컬럼에는 반드시 데이터값을 주어야 한다는 말이다.
2) Primary Key(기본키, 대표식별자) 제약 [P] 및 UNIQUE 제약 [U]
- '회원' 테이블 생성
create table tbl_new_member
(member_id varchar2(20) not null -- 아이디
,passwd varchar2(20) not null -- 비밀번호
,name Nvarchar2(10) not null -- 성명
,email varchar2(50) not null -- 이메일
,mobile varchar2(11) -- 휴대폰
,constraint PK_tbl_new_member_member_id primary key(member_id) -- single primary key
-- 참고로 ORACLE 11g 에서는 객체명은 최대 글자수가 30글자이다. 그래서 ORACLE 11g 에서 30글자가 넘으면 오류이다.
-- ORACLE 18c 에서는 객체명의 글자수 제한이 없다.
-- primary key는 not null을 넣지 않아도 자동적으로 들어간다.
-- primary key는 테이블 당 하나만 가질 수 있다.
,constraint UQ_tbl_new_member_email Unique(email) -- Unique는 하나의 테이블당 여러개를 가질 수 있다.
,constraint UQ_tbl_new_member_mobile Unique(mobile) -- Unique는 하나의 테이블당 여러개를 가질 수 있다.
);
-- Table TBL_NEW_MEMBER이(가) 생성되었습니다.
insert into tbl_new_member(member_id, passwd, name, email, mobile)
values('hong', 'qwer1234!', '홍길동', 'hong@naver.com', null);
-- unique 값에는 다른 행과의 중복 값은 들어가지 못한다.
-- 오라클은 컬럼이 null을 허용하면 null을 여러개 쓸 수 있다.
-- mssql은 null도 데이터로 취급하여 unique를 주는 순간 null은 한번밖에 사용할 수 없다. 중복 불가3) Composite(복합) Primary key
- '주문'테이블 생성하기
create table tbl_new_jumun -- '주문'테이블
(member_id varchar2(20) not null -- 회원 아이디
,product_id varchar2(50) not null -- 제품 아이디
,jumun_date date default sysdate not null -- 주문일자
,jumun_qty number not null -- 주문량
,constraint PK_tbl_new_jumun primary key(member_id, product_id, jumun_date) -- Composite(복합) Primary key
-- 주문 테이블은 같은 사람이 같은 걸 여러번 주문하기도 때문에,
-- 특정한 하나의 값을 primary 키로 줄 수 없어서 여러가지 항복을 묶어서 primary key를 준다.
);
-- Table TBL_NEW_JUMUN이(가) 생성되었습니다.4) Foreign Key(외래키) 제약 [R]
create table tbl_new_board
(boardno number not null -- 게시글 글번호
,subject Nvarchar2(200) not null -- 글제목
,content Nvarchar2(2000) not null -- 글내용
,registerday date default sysdate not null -- 작성일자
,fk_member_id varchar2(20) not null -- 작성자아이디
-- 타입 넣을 때도 references 받는 테이블과 똑같이 넣어주어야 한다.
-- foreign 키는 fk 붙이면 헷갈리지 않을 수 있다.
,constraint PK_tbl_new_board_boardno primary key(boardno)
,constraint FK_tbl_new_board_fk_member_id foreign key(fk_member_id) references tbl_new_member(member_id)
-- foreign key로 사용되어지는 키 -- tbl_new_member 이 테이블에 있는 컬럼값만 들어온다.
-- 참조 받는 테이블(tbl_new_member)의 컬럼은 유니크하거나 프라이머리키 어야 한다.
-- 동명이인이나 데이터가 겹치는 것을 방지하기 위해서 고유한 값을 가지고 있어야한다.
-- 모바일과 같은 unique로 하려 하니 모바일엔 null 값이 있어서 안된다.
-- 따라서!! 참조를 받는 테이블은 not null이면서 고유한 값을 가져야 한다. == primary key
-- 참조를 받는 member 테이블을 부모테이블이라고 부르고,
-- board 테이블은 멤버 테이블에 있는 값이 여러번 글을 쓸 수 있으니 자식테이블 이다.
);
-- Table TBL_NEW_BOARD이(가) 생성되었습니다.
-- cascade 옵션이 없는 foreign key 이다. SQL 재밌다.. 이러면서 듣고 있었는데... 어디서부터 이렇게 어려워진걸까... 한동안 퀴즈 잘 풀려서 신났었는데 이제 퀴즈나오면 동공지진 올듯
이런 저런 기록들