Day 104

ChangWoo·2023년 2월 1일
0

중앙 HTA

목록 보기
48/51




  • row는 하나의 독립된 행
  • row 안에서는 12개의 col을 가질 수 있다.

PL SQL

/* 시퀀스 테이블 생성 */
CREATE TABLE CUSTOM_SEQUENCES(   
    SEQ_DATE VARCHAR2(20), 
    SEQ_NAME VARCHAR2(50),
   SEQ_COUNT NUMBER(4,0),             
    PRIMARY KEY (SEQ_DATE, SEQ_NAME)
);

CREATE OR REPLACE FUNCTION CUSTOM_SEQUENCE
(I_NAME IN VARCHAR2, 
 I_PATTERN IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
    PRAGMA AUTONOMOUS_TRANSACTION; 
    
    V_COUNT NUMBER;    
    V_CURRENT_DATE VARCHAR2(20);
BEGIN
    IF I_PATTERN IS NULL THEN
        V_CURRENT_DATE := TO_CHAR(SYSDATE, 'YYYYMMDD');
    ELSE
        V_CURRENT_DATE := TO_CHAR(SYSDATE, I_PATTERN);
    END IF;
   
    SELECT SEQ_COUNT
    INTO V_COUNT
    FROM CUSTOM_SEQUENCES
    WHERE SEQ_DATE = V_CURRENT_DATE AND SEQ_NAME = I_NAME;

    V_COUNT := V_COUNT + 1;
    
    UPDATE CUSTOM_SEQUENCES
    SET SEQ_COUNT = V_COUNT
    WHERE SEQ_DATE = V_CURRENT_DATE AND SEQ_NAME = I_NAME;
   
    COMMIT;

    RETURN V_CURRENT_DATE || LPAD(V_COUNT, 4, '0');

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            V_COUNT := 1;
            INSERT INTO CUSTOM_SEQUENCES (SEQ_DATE, SEQ_COUNT, SEQ_NAME)
            VALUES (V_CURRENT_DATE, V_COUNT, I_NAME);
           
            COMMIT;

            RETURN V_CURRENT_DATE || LPAD(V_COUNT, 4, '0'); 
END;
/

시퀀스 생성

/* 시퀀스 생성 */    
CREATE SEQUENCE EMPLOYEES_SEQ START WITH 1 NOCACHE;
CREATE SEQUENCE USERS_SEQ START WITH 1000 NOCACHE;

CREATE SEQUENCE FITNESS_PROGRAMS_SEQ NOCACHE;
CREATE SEQUENCE FITNESS_PROGRAM_CATEGORY_SEQ NOCACHE;
CREATE SEQUENCE FITNESS_CLUBS_SEQ NOCACHE;

직원 테이블 데이터 생성

/* 직원 테이블 데이터 */
INSERT INTO EMPLOYEES
    (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_PASSWORD, EMPLOYEE_EMAIL, EMPLOYEE_TEL, EMPLOYEE_ZIPCODE, EMPLOYEE_BASIC_ADDRESS, EMPLOYEE_DETAIL_ADDRESS, FITNESS_NO)
VALUES
    ('hong', '홍길동', 'zxcv1234', 'hong@gmail.com', '010-1111-1111', '06035', '서울 강남구 가로수길 5', '201호', FITNESS_CLUBS_SEQ.CURRVAL);

INSERT INTO EMPLOYEES
    (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_PASSWORD, EMPLOYEE_EMAIL, EMPLOYEE_TEL, EMPLOYEE_ZIPCODE, EMPLOYEE_BASIC_ADDRESS, EMPLOYEE_DETAIL_ADDRESS, FITNESS_NO)
VALUES
    ('moon', '문지훈 ', 'moon1234', 'moon@gmail.com', '010-1251-9734', '06035', '서울시 은평구 역촌동 40-55', '405호', FITNESS_CLUBS_SEQ.CURRVAL);

INSERT INTO EMPLOYEES
    (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_PASSWORD, EMPLOYEE_EMAIL, EMPLOYEE_TEL, EMPLOYEE_ZIPCODE, EMPLOYEE_BASIC_ADDRESS, EMPLOYEE_DETAIL_ADDRESS, FITNESS_NO)
VALUES
    ('lee', '이순신', 'zxcv1234', 'lee@naver.com', '010-1234-5678', '06074', '서울 강남구 학동로 513', '1706호', FITNESS_CLUBS_SEQ.CURRVAL);

INSERT INTO EMPLOYEES
    (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_PASSWORD, EMPLOYEE_EMAIL, EMPLOYEE_TEL,  EMPLOYEE_ZIPCODE, EMPLOYEE_BASIC_ADDRESS, EMPLOYEE_DETAIL_ADDRESS, FITNESS_NO)
VALUES
    ('reina','레이나','zxcv1234','reina@gmail.com', '010-3452-1111', '04768','서울특별시 성동구 서울숲2길 1(성수동1가)','101호',FITNESS_CLUBS_SEQ.CURRVAL);

INSERT INTO EMPLOYEES
   (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_PASSWORD, EMPLOYEE_EMAIL, EMPLOYEE_TEL, EMPLOYEE_ZIPCODE, EMPLOYEE_BASIC_ADDRESS, EMPLOYEE_DETAIL_ADDRESS, FITNESS_NO)
VALUES
   ('ryu', '류관순', 'zxcv1234', 'ryu@naver.com', '010-1234-1234', '12345', '서울특별시 종로구 종로 1', '교보생명빌딩 4층', FITNESS_CLUBS_SEQ.CURRVAL);

피트니스 정보 데이터 생성

/* 피트니스 정보 데이터 */
INSERT INTO FITNESS_CLUBS
(FITNESS_NO, FITNESS_NAME, FITNESS_TEL, FITNESS_ZIPCODE, FITNESS_BASIC_ADDRESS, FITNESS_DETAIL_ADDRESS, FITNESS_LATITUDE, FITNUSS_LONGITUDE,
FITNESS_DESCRIPTION, FITNESS_WEEKDAYS_OPEN_HOURS, FITNESS_WEEKENDS_OPEN_HOURS, FITNESS_CLOSED_DAYS)
VALUES
(FITNESS_CLUBS_SEQ.NEXTVAL, '중앙피트니스', '070-8240-3211', '03134', '서울특별시 종로구 율곡로10길 105', '디아망 4층', '37.57295444857797', '126.99223901854133',
 '중앙피트니스입니다.', '09:00~22:00', '09:00~18:00', '일요일 및 공휴일');

피트니스 분류 데이터 생성

/*피트니스 분류 데이터*/
INSERT INTO FITNESS_PROGRAM_CATEGORY
(FITNESS_PROGRAM_CATEGORY_NO, FITNESS_PROGRAM_CATEGORY_NAME)
VALUES
(FITNESS_PROGRAM_CATEGORY_SEQ.NEXTVAL, '크로스핏');
INSERT INTO FITNESS_PROGRAM_CATEGORY
(FITNESS_PROGRAM_CATEGORY_NO, FITNESS_PROGRAM_CATEGORY_NAME)
VALUES
(FITNESS_PROGRAM_CATEGORY_SEQ.NEXTVAL, '요가');
INSERT INTO FITNESS_PROGRAM_CATEGORY
(FITNESS_PROGRAM_CATEGORY_NO, FITNESS_PROGRAM_CATEGORY_NAME)
VALUES
(FITNESS_PROGRAM_CATEGORY_SEQ.NEXTVAL, '필라테스');
INSERT INTO FITNESS_PROGRAM_CATEGORY
(FITNESS_PROGRAM_CATEGORY_NO, FITNESS_PROGRAM_CATEGORY_NAME)
VALUES
(FITNESS_PROGRAM_CATEGORY_SEQ.NEXTVAL, '스피닝');
INSERT INTO FITNESS_PROGRAM_CATEGORY
(FITNESS_PROGRAM_CATEGORY_NO, FITNESS_PROGRAM_CATEGORY_NAME)
VALUES
(FITNESS_PROGRAM_CATEGORY_SEQ.NEXTVAL, '에어로빅');
INSERT INTO FITNESS_PROGRAM_CATEGORY
(FITNESS_PROGRAM_CATEGORY_NO, FITNESS_PROGRAM_CATEGORY_NAME)
VALUES
(FITNESS_PROGRAM_CATEGORY_SEQ.NEXTVAL, '줌바');

피트니스 프로그램 데이터 생성

/* 피트니스 프로그램 데이터 */
insert into FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR,  PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
values
(FITNESS_PROGRAMS_SEQ.nextval, '초보맞춤 크로스핏', '2023-02-01', '2023-03-01', '09:00', '10:00', 20, 100000, 'moon', 1);    
    
insert into FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR,  PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
values
(FITNESS_PROGRAMS_SEQ.nextval, 'WOD', '2023-04-01', '2023-05-01', '13:00', '13:30', 15, 150000, 'moon', 1);     

insert into FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR,  PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
values
(FITNESS_PROGRAMS_SEQ.nextval, '1일 체험', '2023-02-01', '2023-02-01', '13:00', '13:50', 10, 10000, 'moon', 1); 

insert into FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR, PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
values
(FITNESS_PROGRAMS_SEQ.nextval, '핫요가', '2023-01-01', '2024-12-31', '19:00', '19:50', 10, 50000, 'reina', 2); 

insert into FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR, PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
values
(FITNESS_PROGRAMS_SEQ.nextval, '반야사요가', '2023-01-01', '2024-12-31', '20:00', '20:50', 10, 55000, 'reina', 2);

insert into FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR, PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
values
(FITNESS_PROGRAMS_SEQ.nextval, '대기구 필라테스 1개월', '2023-02-01', '2023-02-28', '09:00', '10:30', 5, 300000, 'reina', 3);

insert into FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR, PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
values
(FITNESS_PROGRAMS_SEQ.nextval, '소기구 필라테스 1개월', '2023-03-15', '2023-04-14', '20:00', '21:20', 8, 180000, 'reina', 3);

INSERT INTO FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR,  PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
VALUES
(FITNESS_PROGRAMS_SEQ.NEXTVAL, '파워스피닝', '2023-02-13', '2023-02-27', '10:00', '11:00', 20, 200000, 'lee',4);

INSERT INTO FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR,  PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
VALUES
(FITNESS_PROGRAMS_SEQ.NEXTVAL, '멘탈스피닝', '2023-03-01', '2023-04-01', '09:00', '10:00', 30, 250000, 'lee',4);

INSERT INTO FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR,  PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
VALUES
(FITNESS_PROGRAMS_SEQ.NEXTVAL, '퍼포먼스스피닝', '2023-03-13', '2023-03-27', '11:00', '12:00', 15, 150000, 'lee',4);

insert into FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR, PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
values
(FITNESS_PROGRAMS_SEQ.nextval, '에어로빅 1개월', '2023-02-01', '2023-03-01', '09:00', '10:00', 15, 75000, 'lee', 5);   

insert into FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR, PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
values
(FITNESS_PROGRAMS_SEQ.nextval, '에어로빅 1일', '2023-02-01', '2023-02-01', '09:00', '10:00', 5, 10000, 'lee', 5);   

insert into FITNESS_PROGRAMS
(PROGRAM_NO, PROGRAM_NAME, PROGRAM_START_DATE, PROGRAM_END_DATE,PROGRAM_START_HOUR, PROGRAM_END_HOUR, PROGRAM_QUOTA, PROGRAM_PRICE, EMPLOYEE_ID, FITNESS_PROGRAM_CATEGORY_NO)
values
(FITNESS_PROGRAMS_SEQ.nextval, '줌바', '2023-02-01', '2023-03-01', '20:00', '20:50', 30, 80000, 'ryu', 6);

프로그램 일정 데이터 생성

/*프로그램 일정 데이터*/ 
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (4, '화');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (4, '목');       
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (5, '월');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (5, '금');    
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (13,'월');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (13,'수');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (13,'금');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (11, '월');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (11, '화');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (11, '수');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (11, '목');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (11, '금');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (12, '월');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (12, '화');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (12, '수');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (12, '목');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (12, '금');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (1, '화');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (1, '목');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (2, '월');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (2, '화');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (2, '수');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (2, '목');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (2, '금');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (3, '월');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (3, '화');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (3, '수');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (3, '목');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (3, '금');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (6, '월');    
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (6, '수');   
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (6, '금');   
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (7, '월');   
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (7, '수');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (7, '금');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (8, '월'); 
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (8, '수');  
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (8, '금');  
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (9, '월');    
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (9, '화');     
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (9, '수');    
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (9, '목');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (9, '금');      
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (10, '월');    
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (10, '화');     
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (10, '수');    
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (10, '목');
INSERT INTO FITNESS_PROGRAM_DAYS
    (PROGRAM_NO, PROGRAM_OPEN_DAY)
VALUES
    (10, '금');
profile
ChangWoo
한 걸음 한 걸음 나아가는 개발자
이전 포스트

Day 103

다음 포스트

Day 105

0개의 댓글