[ Oracle ] 기본 개념

문자열 

CHAR: 고정된 길이의 문자열을 저장 

EX) CHAR(20) 지정 후 'AA'만 저장하면 나머지 공간은 공백으로 채움

VARCHAR2 : 가변 문자열을 저장.

EX) VARCHAR2(20) 지정 후 'AA'만 저장. 즉, 저장 길이가 가변적.

VARCHAR : VARCHAR2와 같지만 오라클에서 향 후 다른 용도로 사용한다고 공식문서에 적혀있다. 그래서 대부분 VARCHAR2 사용

 

[ 최대크기 ] 

CHAR : 2000Byte 

VARCHAR2, VARCHAR:4000바이트

 

숫자 NUMBER

NUMBER(유효 자릿수, 소수점 갯수)

NUMBER(5, 0) : 소수점이 없는 숫자 5자리 까지 저장

NUMBER(3, 2) : 123.45를 저장 시 오류 

유효숫자 3자리 .기준 왼쪽에 숫자 한 개만 표시 가능, 소수점 2자리까지 표시 

NUMBER(11, 2) : 1.345가 들어오면 1.35가 저장됨, 즉 반올림 

 

날짜 DATE, TIMESTAMP

DATE: 날짜, 시간, 분, 초를 저장 (주로 사용)

TIMESTAMP : 날짜, 시간, 분, 초 초단위 이하 9자리까지 저장 (시간이 매우 중요할 경우 사용)

 

무결성

무결성 제약조건: 제약조건은 데이터의 무결성을 보장받기 위해서 사용

무결성 : 데이터베이스에 데이터의 정확성 (일관성)

제약 조건 (Constraint) : NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

 

* NOT NULL

형식 : 컬럼명 데이터타입 NOT NULL

CREATE TABLE MYUSER (
	USERNO NUMBER(11,0),
	USERID VARCHAR2(20),
	USERPW VARCHAR(30) NOT NULL,
	NICKNAME VARCHAR(30),
	EMAIL VARCHAR(50) NULL,
	PRIMARY KEY (USERNO)
);

NOT NULL은 데이터삽입(INSERT)시 값을 꼭 넣어줘야됩니다.

이와 반대로 데이터를 넣지않게되면 즉 NULL값이 들어가면 오류가 발생합니다.

 

사용하면 좋을 때 : 없어서는 안되는 중요한 값일 경우.

 

* NULL에 관해 

INSERT시 값을 넣지 않게되면 NULL로서 표시됩니다. 즉 데이터값이 존재하지 않다는 것 입니다.

하지만 오라클에서는 ''빈 공백, 빈 값을 NULL로 인식하며 ''는 =''가 아닌 IS NULL로 조회해야합니다.

 

* PRIMARY KEY

형식: PRIMARY KEY (컬럼명1, 컬럼명2, 컬럼명3,..)

CREATE TABLE MYUSER (
	USERNO NUMBER(11,0),
	USERID VARCHAR2(20),
	USERPW VARCHAR(30) NOT NULL,
	NICKNAME VARCHAR(30),
	EMAIL VARCHAR(50) NULL,
	PRIMARY KEY (USERNO)
);

- PRIMAY KEY 즉 기본키는 하나의 테이블에 있는 데이터들을 고유하게 식별하는 제약조건입니다.

- 기본키는 한 개의 테이블에 하나만 생성가능 합니다.

- 기본키로 설정된 열에 중복된 값을 가질 수 없으며 NULL값 또한 가질 수 없습니다.

 

* FOREIGN KEY 

형식: 컬럼명 데이터타입 CONSTRAINT 외래키 이름 REFERENCES 참조테이블 명(참조 컬럼)

CREATE TABLE MYUSER (
	USERNO NUMBER(11,0),
	USERID VARCHAR2(20),
	USERPW VARCHAR(30) NOT NULL,
	NICKNAME VARCHAR(30),
	EMAIL VARCHAR(50) NULL,
	PRIMARY KEY (USERNO)
);

CREATE TABLE MARKET_CART(
	CARTNO NUMBER(11,0),
	USERNO NUMBER(11,0) CONSTRAINT FK_USERNO REFERENCES MYUSER(USERNO),
	PRIMARY KEY (CARTNO )
);

- FOREIGN KEY 즉, 외래키는 참조하는 테이블에 존재하는 값만 사용하는 제약조건입니다.

- 참조할 수 있는 컬럼은 참조할 수 있는 테이블의 기본키이거나 UNIQUE만 가능합니다.

 

* CHECK

형식 : CONSTRAINT 제약명 CHECK(조건)

CREATE TABLE MYUSER (
	USERNO NUMBER(11,0),
	USERID VARCHAR2(20),
	USERPW VARCHAR(30) NOT NULL,	
	NICKNAME VARCHAR(30),
	EMAIL VARCHAR(50) NULL,
	BIRTHYEAR NUMBER(11,0) CONTSRAINTS (CH_BIRTHYEAR > 14),
	PRIMARY KEY (USERNO) CHECK(BIRTHYEAR)
);

- CHECK 소괄호 안의 조건에 맞는 데이터만 삽입가능한 제약조건

- 조건에는 기본연산자, 비교연산자, IN, NOT IN 등을 사용

 

* UNIQUE

형식: 컬럼명 데이터타입 UNIQUE

CREATE TABLE MYUSER (
	USERNO NUMBER(11,0),
	USERID VARCHAR2(20),
	USERPW VARCHAR(30) NOT NULL,
	NICKNAME VARCHAR(30) UNIQUE,
	EMAIL VARCHAR(50) NULL,
	BIRTHYEAR NUMBER(11,0) CONSTRAINTS CH_BIRTHYEAR CHECK(BIRTHYEAR >14),
	PRIMARY KEY (USERNO)
);

- UNIQUE로 설정된 해당 컬럼에 중복값을 허용하지 않음

 

DROP TABLE 

형식 : DROP TABLE 테이블명

DROP TABLE MYUSER;

- DROP TABLE 명령어를 사용하면 테이블의 모든 데이터, 구조를 삭제한다.

 

ALTER TABLE 

컬럼 삭제 형식 : ALTER TABLE 테이블명 DROP COLUMN 컬럼명

ALTER TABLE MYUSER DROP COLUMN BIRTHYEAR;

 

컬럼 추가 형식 :  ALTER TABLE 테이블명 ADD 추가할 컬럼명 데이터타입 (NOT NULL, NULL)

ALTER TABLE MYUSER ADD BIRTHYEAR NUMBER(11, 0) NOT NULL;

 

컬럼 변경 형식 : ALTER TABLE 테이블명 MODIFY 컬럼명 변경할 데이터타입 (NOT NULL, NULL)

ALTER TABLE MYUSER MODIFY EMAIL VARCHAR(100) NULL;

 

시퀀스

USERNO NUMBER(11,0) 기본키를 사용자가 다음 숫자를 예상하고 입력하는 것은 불가능하다.

그렇다면 어떻게 해야할까?

 

오라클에서 시퀀스를 사용 

: 자동으로 다음 숫자를 생성하고 기본키로 사용가능하다.

 

시퀀스 생성형식

CREATE SEQUENCE 시퀀스명
    START WITH 시작번호 
    INCREMENT BY 증가값 
    MAXVALUE 최대값
    MINVALUE 최소값 
    CYCLE 또는 NOCYCLE;

 

시퀀스 검색 

SELECT * FROM USER_SEQUENCES;

 

시퀀스 생성

CREATE SEQUENCE SEQ_MYUSER
START WITH 1
INCREMENT BY 1
MAXVALUE 100000
MINVALUE 1
NOCYCLE;

 

시퀀스의 다음값을 이용한 유저테이블에 기본키(USERNO)값 넣기

INSERT INTO MYUSER
(
USERNO,
USERID,
USERPW,
NICKNAME,
EMAIL,
BIRTHYEAR
)
VALUES
(
SEQ_MYUSER.nextval,
'id1234',
'pw1234',
'nm1234',
'aaa@namer.com',
50
);

 

시퀀스의 현재 값 조회

SELECT seq_myuser.currval FROM DUAL;

 

시퀀스 수정

ALTER SEQUENCE 시퀀스명
INCREMENT BY 수정할 증가값
MAXVALUE 수정할 최대값
MINVALUE 수정할 최소값
CYCLE 또는 NOCYCLE;

- 시퀀스의 시작 값을 변경하는 방법은 시퀀스를 삭제 후 다시 생성해야한다.

- 또는 INCREMENT BY 수정할 증가값을 변경하여 원하는 시작값으로 이동

 

시퀀스 삭제 

DROP SEQUENCE 시퀀스명;

 

데이터 정렬 ORDER BY

오름차순 정렬 ORDER BY ASC
형식 : ORDER BY ASC

- 데이터를 오름차순으로 정렬시킨다.

- 숫자는 작은 숫자부터, 문자는 알파벳 순서로 출력된다.

- Default 값은 Ascending 오름차순으로써 ASC는 생략해도된다. 

SELECT *
FROM BBSTABLE
ORDER BY BBSNO ASC;

ASC는 DEFAULT 값이라 생략가능하다.

또한 SELECT절에 ORDER BY에 사용된 컬럼을 꼭 사용할 필요는 없다.

SELECT BBSTITLE
FROM BBSTABLE
ORDER BY BBSNO;

 

 

내림차순 정렬 ORDER BY DESC
형식 : ORDER BY DESC

- 데이터를 내림차순으로 정렬시킨다. 숫자는 큰 숫자부터 문자는 알파벳 역순서로 출력된다.

- ASC 와 달리 DESC는 꼭 명시해 주어야 한다. 

SELECT *
FROM BBSTABLE
ORDER BY BBSNO DESC;

 

ORDER BY 여러 개 사용

SELECT *
FROM BBSTABLE
ORDER BY BBSTITLE DESC,bbscontents ASC;

- 정렬 후 중복되는 데이터는 콤마(,)다음 오는 정렬순서를 적용.

 

조건절 WHERE과 AND, OR, SELECT 시 ROWNUM과 DISTINCT

WHERE과 AND

- MYUSER의 USERID가 500이고 EMAIL은 NULL이 아닌 것만 조회 

SELECT * FROM MYUSER WHERE USERID = 500 AND EMAIL != NULL;

 

BBSTABLE의 게시판번호가 100이하이고 게시판제목이 ABC로 시작하는 것

SELECT * FROM BBSCONTENTS WHERE BBSNO<=100 AND BBSTITLE LIKE 'ABC%'

 

WHERE과 OR

- MYUSER의 USERID가 500또는 EMAIL은 NULL이 아닌것만 조회

SELECT * FROM MYUSER WHERE USERID = 500 OR EMAIL != NULL;

BBSTABLE의 게시판번호가 100초과 또는 게시판제목이 ABC가 포함된 것만 조회

SELECT * FROM BBSCONTENTS WHERE BBSNO>100 OR BBSTITLE LIKE '%ABC%'

 

SELECT와 ROWNUM

 

조회되는 행의 수를 제한할 수 있다.

 

글 1개만 조회 

SELECT *
FROM BBSTABLE
WHERE ROWNUM = 1;

 

글10개만 조회(행의 수 10개)

SELECT *
FROM BBSTABLE
WHERE ROWNUM <= 10;

 

SELECT와 DISTINCT

중복된 값을 제거하고 하나만 결과로서 조회한다.

 

중복된 글 제목은 2개 이상 조회안되도록 SELECT

SELECT DISTINCT BBSTITLE
FROM BBSTABLE;

 

오라클 함수

* 문자열 함수

LOWER(문자열): 소문자로 변환

SELECT LOWER(BBSTITLE)
FROM BBSTABLE;

UPPER(문자열): 대문자로 변환

SELECT UPPER(BBSTITLE)
FROM BBSTABLE;

CONCAT(‘문자열1’,’문자열2’): 문자열1과 문자열2를 합침. 연결연산자||와 같은 역할.

SELECT CONCAT('abc','def')
FROM DUAL;

SUBSTR('문자열',시작위치,갯수): 문자열에서 시작위치로부터 갯수만큼 리턴.

SELECT SUBSTR('abcdefghij',3,4)
FROM DUAL;

LPAD(데이터,전체길이,'추가할문자열'):모자란 길이만큼 왼쪽에서부터 문자열을 추가해준다

SELECT LPAD(BBSCONTENTS,20,'A')
FROM BBSTABLE;

RPAD(데이터,전체길이,'추가할문자열'):모자란 길이만큼 오른쪽에서부터 문자열을 추가해준다.

SELECT RPAD(BBSCONTENTS,20,‘B')
FROM BBSTABLE;

TRIM(‘문자열’):문자열 양쪽 공백을 제거해준다.

SELECT TRIM(' ABC ')
FROM DUAL;

TRIM('특정문자' FROM '문자열'):문자열 앞뒤로 특정문자를 제거해준다.

SELECT TRIM('A' FROM 'AABA BC AA DD AABA')
FROM DUAL;

TRUNC(숫자,소수점표시할 수):소수점 숫자를 어디까지 보여주고 나머지는 버린다.

SELECT TRUNC(123.56789,2)
FROM DUAL;

ROUND(숫자,소수점표시할 수):소수점 숫자를 어디까지 보여주고 나머지는 반올림한다.

SELECT ROUND(123.56789,2)
FROM DUAL;

MOD(숫자,나눌 숫자):숫자를 나누고 나머지값을 얻는다.

SELECT MOD(1234567,10)
FROM DUAL;

ADD_MONTHS('25-DEC-91',6):

SELECT ADD_MONTHS(SYSDATE, 1)
FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, -1)
FROM DUAL;

 

하루추가

SELECT SYSDATE+1 FROM DUAL;

 

** 가장 많이 사용될 것으로 예상됨.그만큼 중요. **

 

NVL(값, NULL일시 출력할 값)

SELECT NVL(NULL,'NULL이군요') FROM DUAL;

NVL2(값 , NULL이 아니면 출력할 값,NULL일시 출력할 값)

SELECT NVL2(NULL,'NULL이 아닙니다','NULL이군요')
FROM DUAL;

 

Inner Join,Left Outer Join,Right Outer Join,Self Join

 

Join을 사용하는 이유  

join을 사용하면 관련있는 테이블끼지 묶어줄 수 있다.

 

INNER JOIN

SELECT A.*,B.*
FROM MYUSER A
INNER JOIN BUYLIST B
ON A.USERNO = B.USERNO;

- 컬럼명이 동일한 경우 별명을 사용하거나 각 테이블 명을 기술.

여기서 알 수 있는 사실 A테이블XB테이블: 

SELECT A.*,B.*
FROM MYUSER A
INNER JOIN BUYLIST B
ON 1=1;

같은 것 끼리만 결과 얻기.

SELECT A.*,B.*
FROM MYUSER A
INNER JOIN BUYLIST B
ON A.USERNO = B.USERNO;

위 결과에서 USERNO=2인 것만 조회하기.

SELECT A.*,B.*
FROM MYUSER A
INNER JOIN BUYLIST B
ON A.USERNO = B.USERNO
WHERE A.USERNO = 2;

 

LEFT OUTER JOIN

INNER JOIN처럼 같은 것 끼리 묶어준다.

그 후에 LEFT OUTER JOIN은 왼쪽의 나머지행도 결과에 포함시킨다.

SELECT A.*,B.*
FROM MYUSER A
LEFT OUTER JOIN BUYLIST B
ON A.USERNO = B.USERNO;

 

RIGHT OUTER JOIN

INNER JOIN처럼 같은 것 끼리 묶어준다.

그 후에 RIGHT OUTER JOIN은 오른쪽의 나머지행도 결과에 포함시킨다.

SELECT A.*,B.*
FROM MYUSER A
RIGHT OUTER JOIN BUYLIST B
ON A.USERNO = B.USERNO;

LEFT OUTER JOIN: 왼쪽 나머지도 포함

RIGHT OUTER JOIN: 오른쪽 나머지도 포함.

 

Union, Union All

UNION: 두개의 쿼리문을 합쳐 하나의 결과집합으로 만들어줍니다.중복된 행은 제거해줍니다.

UNION ALL: 두개의 쿼리문을 합쳐 하나의 결과집합으로 만들어줍니다.

사용시 제약조건: 컬럼타입이 같아야한다.

SELECT
1 AS COLUMN0,
'1ssss' AS COLUMN1
FROM DUAL

UNION

SELECT
2 AS COLUMN0,
'2aaaa' AS COLUMN1
FROM DUAL

UNION

SELECT
3 AS COLUMN0,
'3aaaa' AS COLUMN1
FROM DUAL;

SELECT
BIRTHYEAR
FROM MYUSER

UNION

SELECT
BIRTHYEAR
FROM MYUSER
WHERE USERNO =7;

SELECT
BIRTHYEAR
FROM MYUSER

UNION ALL

SELECT
BIRTHYEAR
FROM MYUSER
WHERE USERNO =7;

SELECT
1 AS COL0,
USERNO AS COL1,
USERID AS COL2,
USERPW AS COL3
FROM MYUSER

UNION ALL

SELECT
2 AS COL0,
BBSNO AS COL1,
TO_CHAR(USERNO) AS COL2,
BBSTITLE AS COL3
FROM BBSTABLE;

 

서브쿼리

 

하나의 SQL문에 존재하는 또 다른 SQL문.

 

WHERE 절 서브쿼리

SELECT *
FROM MYUSER A
WHERE A.USERNO IN (
	SELECT B.USERNO
	FROM BUYLIST B)

SELECT *
FROM MYUSER A
WHERE A.USERNO NOT IN (
	SELECT B.USERNO
	FROM BUYLIST B)

SELECT 절 서브쿼리

반드시 단일 값만을 결과로서 얻어야한다. SUM, AVG, COUNT, MIN, MAX와 같은 집계함수와 많이 사용된다.

 

구매자의정보와 구매자의 전체구매금액.

SELECT A.*,(
	SELECT SUM(PRICE) 
    FROM BUYLIST 
    WHERE BUYLIST.USERNO = A.USERNO) 
    AS TOTALBUY)
FROM MYUSER A
WHERE A.USERNO = 2;

 

FROM 절 서브쿼리

FROM 다음 오는 서브쿼리의 결과를 사용할 수 있게 된다.

SELECT
A.USERNO,
A.USERID,
A.USERPW,
B.BBSNO,
B.USERNO,
B.BBSTITLE
FROM MYUSER A, (SELECT BBSNO,USERNO,BBSTITLE FROM BBSTABLE) B;

INSERT+서브쿼리

SELECT결과를 INSERT INTO 다음오는 테이블(여기서는MYUSER)에 데이터삽입.

INSERT INTO MYUSER
(
USERNO,
USERID,
USERPW,
NICKNAME,
EMAIL,
BIRTHYEAR
)
SELECT
9, --값을 적었지만 프로시저로 받는 매개변수값, 변수값을 사용가능.
'bbbbb',
'bbbbb',
'55555',
'bbb@naver.com',
55
FROM DUAL;

Group By와 그룹 함수

특정 컬럼을 기준으로 그룹으로 묶어준다.

SELECT USERNO
FROM BBSTABLE
GROUP BY USERNO;

GROUP BY 다음에오는 컬럼 외에는 집계함수(SUM, AVG, MIN, MAX, COUNT 등)을 써준다

 

오류의 예

SELECT USERNO,BBSTITLE 
FROM BBSTABLE 
GROUP BY USERNO;

USERNO를 기준으로 그룹으로 묶어주었지만 나머지는 그룹함수의 도움이 필요하다.

 

COUNT 함수

GROUP BY에 사용되는 컬럼을 기준으로 그룹으로 묶어줄 시 COUNT(컬럼)을 적어주면 COUNT(컬럼)에 적힌 컬럼도 묶어서표시,

요약표시 해주지만 행의 수를 요약해준다. 단 NULL은 세지 않는다.

SELECT COUNT(BBSNO),USERNO,COUNT(BBSTITLE)
FROM BBSTABLE
GROUP BY USERNO;

 

SUM함수

누적구매액수,기말고사중간고사 수학시험의 합계 등등에 사용가능하다.

그룹으로 묶어서표시, 요약표시 해주면서 SUM이사용된 컬럼의 숫자적인 합계를 계산할 때 사용한다

SELECT SUM(BBSNO),USERNO
FROM BBSTABLE
GROUP BY USERNO;

회원마다 누적구매액수

SELECT USERNO,SUM(PRICE) 
FROM BUYLIST
GROUP BY USERNO;

AVG함수

그룹으로 묶어서표시,

요약표시 해주면서 AVG이 사용된 컬럼의 숫자적인 평균을 계산할 때 사용한다.

 

--회원마다 평균상품금액

SELECT USERNO,AVG(PRICE)
FROM BUYLIST
GROUP BY USERNO;

--전체 평균상품금액

SELECT AVG(PRICE)
FROM BUYLIST;

 

MAX함수

그룹으로 묶어서표시, 요약표시 해주면서 그룹마다 숫자적인 최대값을 표시해준다.

 

--회원마다 구매한 상품중 최고상품금액

SELECT USERNO,MAX(PRICE)
FROM BUYLIST
GROUP BY USERNO;

--구매한 상품중 최고상품금액

SELECT MAX(PRICE)
FROM BUYLIST;

 

MIN함수

그룹으로 묶어서표시, 요약표시 해주면서 그룹마다 숫자적인 최소값을 표시해준다.

 

--회원마다 구매한 상품중 최저상품금액

SELECT USERNO,MIN(PRICE)
FROM BUYLIST
GROUP BY USERNO;

--구매한 상품중 최저상품금액

SELECT MIN(PRICE)
FROM BUYLIST;

 

HAVING

GROUP BY로 묶인 그룹에 조건을 준다.

 

BBSNO의 NULL이 아닌 행의 갯수 10개 초과만 조회.

SELECT COUNT(BBSNO),USERNO,COUNT(BBSTITLE)
FROM BBSTABLE
GROUP BY USERNO
HAVING COUNT(BBSNO)>10;

 

HAVING을 사용하는이유

SELECT COUNT(BBSNO),USERNO,COUNT(BBSTITLE)
FROM BBSTABLE
WHERE COUNT(BBSNO)>10
GROUP BY USERNO;--오류쿼리
그룹함수(SUM,AVG,COUNT,MAX,MIN)는 WHERE절에서 사용불가.

 

트랜잭션

 

트랜잭션은 데이터 처리의 한 단위 입니다

트랜잭션을 통해 데이터의 일관성을 보증합니다.

 

COMMIT: 데이터베이스에 트랜잭션으로 묶인 작업을 영구적으로 데이터베이스에 반영 후 트랜잭션 종료.

ROLLBACK: 데이터베이스에 트랜잭션으로 묶인 작업을 취소함.

 

ROLLBACK이 사용되는 곳

올바르지 않은 데이터가 입력되었거나 데이터베이스에 오류가 발생했을 경우 트랜잭션을 취소하는 명령어입니다.

데이터베이스가 비정상적으로 종료되면 자동으로 ROLLBACK이 이루어진다.

 

COMMIT:데이터베이스에 영구적으로 작업이 반영된다.

: 여기서 ROLLBACK을 사용한다해도 INSERT 문이 취소가 안된다. 그러하기 때문에 조심할 필요가 있다.

 

: 자동COMMIT: COMMIT명령을 사용하지 않아도 자동 COMMIT되는 경우는 아래와 같음.

: DDL(Data Definition Language:CREATE,DROP 등)문 또는 DCL(Data Control Language:GRANT,REVOKE)문을 사용하는 경우.

 

여러 사람이 다른 컴퓨터상에서 동일한 데이터베이스에서 작업을 한다.

 

: A가 BUYLIST테이블에 UPDATE,DELETE,INSERT문을 통해 COMMIT,ROLLBACK없이 작업.(BUYLIST테이블에 LOCK이 걸림) B가 BUYLIST테이블에 접근하여 변경하려고하면 변경할 수 없다.

: A가 해당 Transaction을 Commit또는 Rollback을 하면 B가 사용가능.(LOCK이 해제됨.)

 

 

이렇듯 상사 또는 여타 개발자와 합의된 사안에 대해서만 COMMIT 또는 ROLLBACK을 꼭 해줘야된다.

 

PL/SQL

PL/SQL(Procedure Language/SQL)

: PL/SQL은 프로그래밍 언어의 절차적 특징과 함께 SQL의 조합이다.

: 이로인해 SQL의 모듈화. 작업처리 속도의 성능향상을 도모할 수 있음

 

PL/SQL Block 예제

DECLARE
	Variable declaration
BEGIN
	Program Execution
EXCEPTION
	Exception handling
END;

 

프로시저란?

: 이름을 가지고 데이터베이스에 저장되는 PL/SQL 블록으로써 매개변수를 받고 반복적으로 호출가능하다.

: 또한 뭐리문을 여러 개 실행할 수 있다.

: 여러 어플리케이션에서 공유가능 

 

프로시저 형식

CREATE OR REPLACE PROCEDURE 프로시저이름
(
	파라미터명 파라미터방향 파라미터타입,
	파라미터명 파라미터방향 파라미터타입
) IS
프로시저에서 사용할 변수선언.
BEGIN
쿼리작성.
END;

create or replace PROCEDURE PROC_INMYUSER
(
In_userid IN VARCHAR2,
Out_state OUT NUMBER
)
IS
--변수
BEGIN
INSERT INTO MYUSER
(
USERNO,
USERID,
USERPW,
NICKNAME,
EMAIL,
BIRTHYEAR
)
VALUES
(
16,
In_userid,
'assv',
'asssv4',
'aaas@naber.com',
50
);

UPDATE MYUSER
SET EMAIL = 'bbbb@naber.com'
WHERE USERNO = 16;
Out_state := 0;
END;

명령어로 호출 

EXEC 프로시저이름; --파라미터가 없는 프로시저 호출 시.

EXEC 프로시저이름(값1,값2,값3,....); --파라미터가 존재하는 프로시저 호출 시.

 

테스트용 사전작업

DELETE FROM MYUSER;
CREATE SEQUENCE SEQ_MYUSER
	START WITH 0
	INCREMENT BY 1
	MAXVALUE 1000000000
	MINVALUE 0
	NOCYCLE;

 

프로시저 호출

 

익명블록으로 PL/SQL 작성

set serveroutput on;
--쿼리결과를 화면에 출력.

DECLARE
-- [변수이름] [변수타입] [:=초기값];
v_state NUMBER(11,0):=50;
BEGIN

--프로시저호출.
INSERT_USER('aabs543',v_state);

--결과값 출력
dbms_output.put_line('결과'||v_state);
END;

:= VS =

:=는 대입연산자이다 자바의 = 과 같다.

=는 비교연산자이다 자바의 ==과 같다.

 

create or replace PROCEDURE INSERT_USER
(
In_userid IN VARCHAR2,
In_userpw IN VARCHAR2,
In_nickname IN VARCHAR2,
InOut_state IN OUT NUMBER
)
IS
[변수명] [변수타입] [:=초기값(생략가능)];
v_username VARCHAR2(250) := 'AAA';
v_userno NUMBER(11,0);
BEGIN

프로시저와 변수선언:
create or replace PROCEDURE PROC_INMYUSER
(
In_userid IN VARCHAR2,
In_userpw IN VARCHAR2,
In_usernickname IN VARCHAR2,
Out_state OUT NUMBER
)
IS
--변수
v_userid VARCHAR2(250) := 'AAA';
v_state NUMBER(11,0);
BEGIN
SELECT 5 INTO v_state
FROM MYUSER
WHERE USERID =In_userid
AND ROWNUM = 1;
INSERT INTO MYUSER
(
USERNO,
USERID,
USERPW,
NICKNAME,
EMAIL,
BIRTHYEAR
)
VALUES
(
SEQ_MYUSER.nextval,
In_userid,
In_userpw,
In_usernickname,
'aaas@naber.com',
50
);
UPDATE MYUSER
SET EMAIL = 'bbbb@naber.com';
Out_state := v_state;
END;

 

PL/SQL 예외처리

테스트방법: 프로시저 호출 시

SELECT 1 INTO v_userno
FROM MYUSER
WHERE USERID = In_userid
AND ROWNUM = 1;

쿼리에서 일치하는 USERID가 미존재로 오류 발생시.

BEGIN
	SELECT 1 INTO v_userno
	FROM MYUSER
	WHERE USERID = In_userid
	AND ROWNUM = 1;
	
    InOut_state:=v_userno;

	EXCEPTION WHEN NO_DATA_FOUND THEN
	InOut_state:=0;
END;

create or replace PROCEDURE INSERT_USER
(
In_userid IN VARCHAR2,
In_userpw IN VARCHAR2,
In_nickname IN VARCHAR2,
InOut_state IN OUT NUMBER
)
IS
v_username VARCHAR2(250) := 'AAA';
v_userno NUMBER(11,0);
BEGIN
/*
SELECT또는
INSERT,
DELETE존재가능.
앞단의 로직..
*/
BEGIN
SELECT 1 INTO v_userno
FROM MYUSER
WHERE USERID = In_userid
AND ROWNUM = 1;
InOut_state:=v_userno;
EXCEPTION WHEN NO_DATA_FOUND THEN
InOut_state:=0;
END;
/*
뒷단의 로직처리..
*/
INSERT INTO MYUSER
(
USERNO,
USERID,
USERPW,
NICKNAME,
EMAIL,
BIRTHYEAR
)
VALUES
(
SEQ_MYUSER.nextval,
In_userid,
In_userpw,
In_nickname,
'aa@naver.com',
50
);
UPDATE MYUSER
SET EMAIL = 'bb@naver.com'
WHERE BIRTHYEAR = 50;
END;

데이터베이스 EXCEPTION에서 주로하는 일

ROLLBACK; INSERT INTO 테이블(컬럼1,컬럼2...) VALUES('프로시저이름',오류발생위치,파라미터로 받은값,에러코드,에러메시지); 파일로 기록남기기 등등..

 

연습용 테이블생성:
CREATE TABLE ERROR_LOG(
ERRORID NUMBER(10,0),
PROCNM VARCHAR2(250),
STEP NUMBER(5,0),
ERRORNO NUMBER(10,0),
ERRORMSG VARCHAR2(2000),
TXT_PARAMETER VARCHAR2(2000),
ERRORDAY DATE
);
시퀀스 생성
CREATE SEQUENCE SEQ_ERRORLOG
START WITH 1
INCREMENT BY 1
MAXVALUE 100000
MINVALUE 1
NOCYCLE;

create or replace PROCEDURE PROC_INMYUSER
(
In_userid IN VARCHAR2,
In_userpw IN VARCHAR2,
In_usernickname IN VARCHAR2,
Out_state OUT NUMBER
)
IS
--변수
v_userid VARCHAR2(250) := 'AAA';
v_state NUMBER(11,0);
v_step NUMBER(5,0):=0;
v_errcode NUMBER(10,0);
v_errmsg VARCHAR2(2000);
v_userparm VARCHAR2(2000);
BEGIN
/*SELECT,DELETE,UPDATE,INSERT*/
v_step:=0;
SELECT 5 INTO v_state
FROM MYUSER
WHERE USERID =In_userid
AND ROWNUM = 1;
/*SELECT,DELETE,UPDATE,INSERT*/
v_step:=1;
INSERT INTO MYUSER
(
USERNO,
USERID,
USERPW,
NICKNAME,
EMAIL,
BIRTHYEAR
)
VALUES
(
SEQ_MYUSER.nextval,
In_userid,
In_userpw,
In_usernickname,
'aaas@naber.com',
50
);
v_step:=2;
UPDATE MYUSER
SET EMAIL = 'bbbb@naber.com';
v_step:=3;
--Out_state := v_state;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
v_errcode:=SQLCODE;
v_errmsg:=SQLERRM;
v_userparm:=('In_userid:'||In_userid||','||'In_userpw:'||In_userpw||',In_usernickname:'||In_usernickname);
dbms_output.put_line('PROC_INMYUSER오류가 발생했습니다.');
INSERT INTO ERROR_LOG(ERRORID, PROCNM, STEP, ERRONO, ERRORMSG, TXT_PARAMETER, ERRORDAY) VALUES(SEQ_ERRORLOG.nextval,'PROC_INMYUSER',v_step,v_errcode,v_errmsg,v_userparm,sysdate);
COMMIT;
END;

 

익명블록으로 PL/SQL 작성

set serveroutput on;
--쿼리결과를 화면에 출력.
DECLARE
-- [변수이름] [변수타입] [:=초기값];
v_state NUMBER(11,0):=50;
BEGIN
--프로시저호출.
INSERT_USER('aa','bb','cc',v_state);
--결과값 출력
dbms_output.put_line('결과'||v_state);
END;

 

패키지란?

패키지는 다수의 프로시저를 묶어서 관리해주는 역할을 해준다. 재 사용성을 높여줌. 유지보수가 쉬워짐.

패키지는 선언부와 본문 두 부분으로 나뉜다.

 

패키지 선언부(Package Specification)

CREATE OR REPLACE PACKAGE 패키지명 [AS 또는 IS]
커서 선언
변수 선언
프로시저 선언
함수 선언
예외 선언

패키지 본문(Package Body)

프로시저 정의
함수 정의

 

앞서 프로시저를 패키지안에 넣어보자.(선언부)

create or replace PACKAGE USERENTER IS
PROCEDURE PROC_INMYUSER
(
In_userid IN VARCHAR2,
In_userpw IN VARCHAR2,
In_usernickname IN VARCHAR2,
Out_state OUT NUMBER
);
--앞으로 생성할 프로시저
PROCEDURE INSERT_BUYLIST
(
In_name IN VARCHAR2
);
END USERENTER;

create or replace PACKAGE BODY USERENTER IS
PROCEDURE PROC_INMYUSER
(
In_userid IN VARCHAR2,
In_userpw IN VARCHAR2,
In_usernickname IN VARCHAR2,
Out_state OUT NUMBER
)
IS
--변수
v_userid VARCHAR2(250) := 'AAA';
v_state NUMBER(11,0);
v_step NUMBER(5,0):=0;
v_errcode NUMBER(10,0);
v_errmsg VARCHAR2(2000);
v_userparm VARCHAR2(2000);
BEGIN
/*SELECT,DELETE,UPDATE,INSERT*/
v_step:=0;
SELECT 5 INTO v_state
FROM MYUSER
WHERE USERID =In_userid
AND ROWNUM = 1;
/*SELECT,DELETE,UPDATE,INSERT*/
v_step:=1;
INSERT INTO MYUSER
(
USERNO,
USERID,
USERPW,
NICKNAME,
EMAIL,
BIRTHYEAR
)
VALUES
(
SEQ_MYUSER.nextval,
In_userid,
In_userpw,
In_usernickname,
'aaas@naber.com',
50
);
v_step:=2;
UPDATE MYUSER
SET EMAIL = 'bbbb@naber.com';
v_step:=3;
--Out_state := v_state;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
v_errcode:=SQLCODE;
v_errmsg:=SQLERRM;
v_userparm:=('In_userid:'||In_userid||','||'In_userpw:'||In_userpw||',In_usernickname:'||In_usernickname);
dbms_output.put_line('PROC_INMYUSER오류가 발생했습니다.');
INSERT INTO ERROR_LOG(ERRORID, PROCNM, STEP, ERRONO, ERRORMSG, TXT_PARAMETER, ERRORDAY) VALUES(SEQ_ERRORLOG.nextval,'PROC_INMYUSER',v_step,v_errcode,v_errmsg,v_userparm,sysdate);
COMMIT;
END PROC_INMYUSER;
PROCEDURE INSERT_BUYLIST
(
In_name IN VARCHAR2
)IS
BEGIN
INSERT INTO BUYLIST
(
BUYNO,
USERNO,
NAME,
BUYDAY,
PRICE
)
VALUES
(
500,--고정으로넣지만 나중에 다시실행 할때 변경해야 오류가안남.
4,
'AA초콜릿',
sysdate,
5000
);
END INSERT_BUYLIST;
END USERENTER;

패키지안의 프로시저 호출.

형식: EXEC 패키지명.프로시저명(‘값1’,’값2’…..);

예제: EXEC USERENTER.INSERT_BUYLIST('aaaaaaa');

 

114p 부터 다시 보기!