SEQUENCE 객체
연속, 열거, 순서
자동적으로 번호를 생성하기 위한 객체 => 키 값
테이블과 독립적 => 여러 곳에서 사용 가능
이용
:Primary Key를 설정할 후보키가 없거나 PK를 특별히 의미 있게
만들지 않아도 되는 경우
:자동으로 순서적인 번호가 필요한 경우
사용되는 경우
- SELECT문의 SELECT 절 (단, SUBQUERY, VIEW가 아닌)
- INSERT 문의 SELECT 절
- INSERT 문의 VALUES 절
- UPDATE문의 SET 절
Sequence가 제한되는 경우
- SELECT, DELETE, UPDATE 에서의 서브쿼리
- VIEW의 QUERY구문
- DISTINCT 가 있는 SELECT 문
- GROUP BY, ORDER BY 가 있는 SELECT 문
- SET 연산자(UNION, INTERSECT, MINUS)가 있는 SELECT 문
- SELECT 문의 WHERE 절
- CREATE / ALTER TABLE 에서 컬럼의 DEFAULT 값
- CHECK 제약조건
SYNONYM 객체
INDEX 객체
Data Dictionary
PL/SQL
PL/SQL은 Procedural Language/SQL
서버에서 절차적인 처리를 위해 표준 SQL을 확장한 절차적 언어
블록(block) 구조로 여러 SQL문을 한번에 실행
모듈화, 캡슐화가 가능
Anonymous block
:단순 스크립트에서 실행되는 블록, 서버에 저장되지 않음
Stored Procedure
: 자주 실행되거나, 복잡한 비즈니스 로직을 미리 작성하여 서버에 저장하여 사용
User Function
: Procedure와 유사하며, 실행결과를 반환한다.
Package
: 여러 Procedure, Function 및 변수 등을 하나로 묶는다.
Trigger
: 테이블이나 뷰에 INSERT, UPDATE, DELETE등이 수행 전 또는 수행 후 자동 실행되는 Procedure
Declation Section :옵션
-변수, 상수, CURSOR 와 USER_DEFINE Exception등 선언
Excutabl Section : 필수
처리할 명령문들을 절차적으로 기술, SQL문, 반복문, 조건문
BEGIN으로 시작 END로 끝남
Exception Section : 옵션
오류 처리에 관한 명령문을 기술
변수의 데이터 타입 (cont.)
References 변수에 사용되는 데이터 타입
테이블명.컬럼명%TYPE: 해당 테이블의 해당컬럼의 타입과 동일하게 지정
테이블명%ROWTYPE : 해당 테이블의 모든 컬럼과 동일하게 지정
변수 선언
식별자 [CONSTANT] 데이터타입 [NOT NULL][:= 초기값];
-
초기값을 지정하고자 할 때는 할당연산자(:=)를 사용한다.
-
식별자를 상수로 지정하고 하는 경우는 CONSTANT라는 KEYWORD를 명시하고 반드시 초기값을 지정한다.
-
NOT NULL이 정의되어 있으면 초기값을 반드시 지정 한다.
-
초기값을 정의하지 않으면 변수는 NULL값을 가진다.
--다른Table로부터 데이터 입력
INSERT INTO REMAIN(REMAIN_YEAR, REMAIN_PROD, REMAIN_J_00, REMAIN_I, REMAIN_0, REMAIN_J_99, REMAIN_DATE)
SELECT '2004', PROD_ID, TO_NUMBER(SUBSTR(PROD_ID, -2))-- 기초(전년)재고 : 상품코드의 우측 2자리를 숫자로 Conversion하여 처리 ( 원칙은 전년도말 재고가 되어야 함 )
, 10, 7, TO_NUMBER(SUBSTR(PROD_ID, -2)) + 10 -7, SYSDATE FROM PROD;
SELECT * FROM REMAIN;
SELECT * FROM PROD;
INSERT INTO REMAIN(REMAIN_YEAR, REMAIN_PROD, REMAIN_J_00, REMAIN_I, REMAIN_0, REMAIN_J_99, REMAIN_DATE)
SELECT '2023', PROD_ID, TO_NUMBER(SUBSTR(PROD_ID, -2)), prod_qtyin, prod_qtysale, (TO_NUMBER(SUBSTR(PROD_ID, -2))+ prod_qtyin) - prod_qtysale, SYSDATE FROM PROD;--재고 수불 테이블을 복사하여 remain2 테이블을 생성
CREATE TABLE REMAIN2
AS
SELECT * FROM REMAIN;
SELECT * FROM REMAIN2;
DELETE FROM REMAIN2;
ROLLBACK;
TRUNCATE TABLE REMAIN2;
DROP TABLE REMAIN2;--재고수불 테이블에서 2003년도 자료 중 입고수량 +출고수량이 20개 이상인 자료를 삭제
--1. 조회
SELECT REMAIN_YEAR 연도
, REMAIN_PROD 상품
, REMAIN_I 입고량
, REMAIN_0 출고량
, REMAIN_I + REMAIN_0 합계수량
FROM REMAIN
WHERE REMAIN_YEAR = '2003'
AND (NVL(REMAIN_I, 0) + NVL(REMAIN_0, 0)) >= 20;
--2. 삭제
DELETE
FROM REMAIN
WHERE REMAIN_YEAR = '2023'
AND (NVL(REMAIN_I, 0) + NVL(REMAIN_0, 0)) >= 20;--view_prod1 생성 : 상품분류, 상품수
CREATE VIEW VIEW_PROD1(분류, 상품수)
AS
SELECT PROD_LGU
, COUNT(*)
FROM PROD
GROUP BY PROD_LGU;
SELECT * FROM view_prod1;--SEQUENCE 객체
--상품 분류테이블에 'P103', 'USB 제품'을 등록하시오?
--(단, lprod_id 컬럼의 값은 최대값을 구하여 1을 더한 서브쿼리를 작성하여 입력)
SELECT NVL(MAX(LPROD_ID), 0) FROM LPROD;
INSERT INTO LPROD(lprod_id, lprod_gu, lprod_nm)
VALUES((SELECT NVL(MAX(LPROD_ID), 0) +1 FROM LPROD), 'P103', 'USB제품');lprod_seq 시퀀스 생성
CREATE SEQUENCE LPROD_SEQ
INCREMENT BY 1 START WITH 15;
--상품 분류테이블에 'P203' , '아동복' 을 등록
INSERT INTO LPROD(LPROD_ID, LPROD_GU, LPROD_NM) VALUES (LPROD_SEQ.nextval, 'P203', '아동복');
--다음 번호로 증가
SELECT LPROD_SEQ.NEXTVAL FROM DUAL;
--현재 번호
SELECT LPROD_SEQ.CURRVAL FROM DUAL;
INSERT INTO LPROD(LPROD_ID, LPROD_GU, LPROD_NM) VALUES (LPROD_SEQ.nextval, 'P909', '스포츠용품');Sequence 변경
ALTER SEQUENCE LPROD_SEQ
INCREMENT BY 2
MAXVALUE 999;
-- START WITH는 변경할 수 없다
--ALTER SEQUENCE LPROD_SEQ
--INCREMENT BY 2 START WITH 120;
SELECT LPROD_SEQ.NEXTVAL FROM DUAL;
SELECT LPROD_SEQ.CURRVAL FROM DUAL;
--다음 요건을 만족하는 시퀀스를 생성(객체명 : cart_seq, 증감값 : 1, 최소값 : 10000, 최대값 : 99999, 순환가능)
CREATE SEQUENCE CART_SEQ
INCREMENT BY 1
MINVALUE 10000
MAXVALUE 99999
CYCLE;
SELECT CART_SEQ.NEXTVAL FROM DUAL;
SELECT SUBSTR(SYSDATE, 1, 2) || SUBSTR(SYSDATE, 4, 2) ||
SUBSTR(SYSDATE, 7, 2) || CART_SEQ.nextval FROM DUAL;--SYNONYM 객체
GRANT CREATE SYNONYM TO PC22;
create SYNONYM mem for member;
SELECT * FROM MEM;
CREATE SYNONYM MYDUAL FOR SYS.DUAL;
SELECT 'Hello World' FROM MYDUAL;
DROP SYNONYM MYDUAL;
--INDEX 객체
SELECT * FROM MEMBER;
CREATE INDEX IDX_MEMBER_BIR
ON MEMBER(MEM_BIR);
-- 회원 생일이 조건절에 자주 사용되어 Index를 생성
SELECT MEM_ID
, MEM_NAME
, MEM_JOB
, MEM_BIR
FROM MEMBER
WHERE TO_CHAR(MEM_BIR, 'YYYY') = '1975';
-- 회원생일에서 년도만 분리하여 인덱스를 생성(Function-based Index)
CREATE INDEX IDX_MEMBER_BIR_YEAR
ON MEMBER(TO_CHAR(MEM_BIR, 'YYYY'));
SELECT MEM_ID
, MEM_NAME
, MEM_JOB
, MEM_BIR
FROM MEMBER
WHERE TO_CHAR(MEM_BIR, 'YYYY') = '1999';ALL_OBJECTS의 모든 컬럼 상세
DESC ALL_OBJECTS;
DESC USER_OBJECTS;
DESC DBA_OBJECTS;
DESC V$_OBJECTS;
--Data Dictionary
-- Dictionary 뷰에서 'ALL_'로 시작 하는 모든 테이블 조회
SELECT TABLE_NAME
, COMMENTS
FROM DICTIONARY
WHERE TABLE_NAME LIKE 'ALL_%';-- 현재 로그인한 사용자가 만든 모든 객체 정보를 출력
SELECT OBJECT_NAME
, OBJECT_TYPE
, CREATED
FROM ALL_OBJECTS
WHERE OWNER = 'PC22'
ORDER BY OBJECT_TYPE ASC;
--SER_TABLES의 컬럼 상세를 확인하고 각 테이블 전체 레코드 개수를 출력. ( 테이블명, 레코드 수)
DESC USER_TABLES;
SELECT TABLE_NAME, NUM_ROWS
FROM USER_TABLES;--USER_CONSTRAINTS, USER_CONS_COLUMNS의 컬럼 상세를 확인하고 상품 테이블의 제약조건을 출력(컬럼명, 제약명, 타입, 제약내용)
SELECT * FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = 'PROD';
SET SERVEROUTPUT ON
DECLARE
v_i NUMBER(9,2) := 0;
v_name VARCHAR2(20);
c_pi CONSTANT NUMBER(8,6) := 3.121592;
v_flag BOOLEAN NOT NULL := TRUE;
v_date VARCHAR2(10) := TO_CHAR(SYSDATE, 'YYYY-MM-DD');
BEGIN
v_name := '홍길동';
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('v_i : ' || v_i);
DBMS_OUTPUT.PUT_LINE('v_name : ' || v_name);
DBMS_OUTPUT.PUT_LINE('c_pi : ' || c_pi);
DBMS_OUTPUT.PUT_LINE('v_date : ' || v_date);
END;
/ --출력 시 끝 구분용--IF문
DECLARE
v_num NUMBER := 37;
BEGIN
DBMS_OUTPUT.ENABLE;
IF MOD(v_num, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE(v_num || '는 짝수');
ELSE
DBMS_OUTPUT.PUT_LINE(v_num || '는 홀수');
END IF;
END;
/--조건에 따른 다중 ELSIF
DECLARE
v_num NUMBER := 67;
BEGIN
DBMS_OUTPUT.ENABLE;
IF v_num > 90 THEN
DBMS_OUTPUT.PUT_LINE('수');
ELSIF v_num > 80 THEN
DBMS_OUTPUT.PUT_LINE('우');
ELSIF v_num > 70 THEN
DBMS_OUTPUT.PUT_LINE('미');
ELSE
DBMS_OUTPUT.PUT_LINE('분발');
END IF;
END;
/