121 lines
4.0 KiB
SQL
121 lines
4.0 KiB
SQL
-- Oracle 초기화 스크립트
|
|
-- 데이터베이스와 사용자 생성
|
|
|
|
-- wace 사용자 생성 (PDB에서 실행)
|
|
ALTER SESSION SET CONTAINER = ORCLPDB1;
|
|
|
|
-- 사용자가 이미 존재하는지 확인하고 생성
|
|
DECLARE
|
|
user_count NUMBER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO user_count FROM dba_users WHERE username = 'WACE';
|
|
|
|
IF user_count = 0 THEN
|
|
EXECUTE IMMEDIATE 'CREATE USER wace IDENTIFIED BY "wace0909!!" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp';
|
|
EXECUTE IMMEDIATE 'GRANT CONNECT, RESOURCE, DBA TO wace';
|
|
EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO wace';
|
|
EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO wace';
|
|
EXECUTE IMMEDIATE 'GRANT CREATE VIEW TO wace';
|
|
EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE TO wace';
|
|
EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO wace';
|
|
EXECUTE IMMEDIATE 'GRANT CREATE TRIGGER TO wace';
|
|
EXECUTE IMMEDIATE 'ALTER USER wace QUOTA UNLIMITED ON users';
|
|
DBMS_OUTPUT.PUT_LINE('사용자 wace가 생성되었습니다.');
|
|
ELSE
|
|
DBMS_OUTPUT.PUT_LINE('사용자 wace가 이미 존재합니다.');
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
-- wace 사용자로 연결하여 테이블 생성
|
|
CONNECT wace/"wace0909!!"@//localhost:1521/ORCLPDB1;
|
|
|
|
-- 샘플 테이블 생성
|
|
DECLARE
|
|
table_count NUMBER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO table_count FROM user_tables WHERE table_name = 'USERS';
|
|
|
|
IF table_count = 0 THEN
|
|
EXECUTE IMMEDIATE '
|
|
CREATE TABLE users (
|
|
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
username VARCHAR2(50) NOT NULL UNIQUE,
|
|
email VARCHAR2(100) NOT NULL UNIQUE,
|
|
password_hash VARCHAR2(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)';
|
|
DBMS_OUTPUT.PUT_LINE('users 테이블이 생성되었습니다.');
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
DECLARE
|
|
table_count NUMBER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO table_count FROM user_tables WHERE table_name = 'USER_PROFILES';
|
|
|
|
IF table_count = 0 THEN
|
|
EXECUTE IMMEDIATE '
|
|
CREATE TABLE user_profiles (
|
|
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
user_id NUMBER NOT NULL,
|
|
first_name VARCHAR2(50),
|
|
last_name VARCHAR2(50),
|
|
phone VARCHAR2(20),
|
|
address CLOB,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_user_profiles_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
)';
|
|
DBMS_OUTPUT.PUT_LINE('user_profiles 테이블이 생성되었습니다.');
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
-- 업데이트 트리거 생성
|
|
CREATE OR REPLACE TRIGGER trg_users_updated_at
|
|
BEFORE UPDATE ON users
|
|
FOR EACH ROW
|
|
BEGIN
|
|
:NEW.updated_at := CURRENT_TIMESTAMP;
|
|
END;
|
|
/
|
|
|
|
CREATE OR REPLACE TRIGGER trg_user_profiles_updated_at
|
|
BEFORE UPDATE ON user_profiles
|
|
FOR EACH ROW
|
|
BEGIN
|
|
:NEW.updated_at := CURRENT_TIMESTAMP;
|
|
END;
|
|
/
|
|
|
|
-- 샘플 데이터 삽입
|
|
DECLARE
|
|
data_count NUMBER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO data_count FROM users WHERE username = 'admin';
|
|
|
|
IF data_count = 0 THEN
|
|
INSERT INTO users (username, email, password_hash) VALUES
|
|
('admin', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');
|
|
|
|
INSERT INTO users (username, email, password_hash) VALUES
|
|
('testuser', 'test@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');
|
|
|
|
INSERT INTO user_profiles (user_id, first_name, last_name, phone) VALUES
|
|
(1, '관리자', '시스템', '010-1234-5678');
|
|
|
|
INSERT INTO user_profiles (user_id, first_name, last_name, phone) VALUES
|
|
(2, '테스트', '사용자', '010-9876-5432');
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE('샘플 데이터가 삽입되었습니다.');
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
DBMS_OUTPUT.PUT_LINE('Oracle wace 데이터베이스 초기화 완료');
|
|
/
|