로메오의 블로그

SELECT 해 온 ROW를 변수로 저장합니다. DECLARE V_CITY_ID VARCHAR2(10); BEGIN SELECT OBJECT_ID INTO V_CITY_ID FROM CITY_TABLE WHERE OBJECT_ID = 1000; EXCEPTION WHEN NO_DATA_FOUND THEN V_CITY_ID := NULL; DBMS_OUTPUT.PUT_LINE('조회된 DATA = ' || V_CITY_ID); END ARRAY로 저장하기 DECLARE V_CITIES_IDS DBMS_SQL.NUMBER_TABLE; BEGIN SELECT OBJECT_ID BULK COLLECT INTO V_CITIES_IDS FROM CITY_TABLE WHERE OBJECT_ID = 1000; FOR ..

INSERT, UPDATE, DELETE 수행된 ROW를 반환해서 변수에 저장합니다. CREATE OR REPLACE SP_COMPANY_CUD ( IN_COMPANY_NAME IN VARCHAR2 RETURN_CODE IN OUT VARCHAR2 ) IS V_COMPANY_ID VARCHAR2(10); V_COMPANY_NAME VARCHAR2(10); V_COMPANY_IDS DBMS_SQL.NUMBER_TABLE; -- V_COMPANY_IDS DBMS_SQL.VARCHAR2_TABLE; BEGIN -- 추가 INSERT INTO COMPANY_TABLE (COMPANY_ID, COMPANY_NAME) VALUES (10, 'GOOGLE') RETURNING COMAPANY_ID, COMPANY_N..

시퀀스 생성 CREATE SEQUENCE seq_name 시퀀스이름 [START WITH start_num] 시퀀스 시작값 [INCREMENT BY increse_num] 시퀀스 증가값 [MAXVALUE max_value] 시퀀스 최대값 [MINVALUE min_value] 시퀀스 최소값 [CYCLE | NOCYCLE] 최대값도달후 다시 최소값부터 시작할건지(사이클여부) [CACHE | NOCACHE] CACHE 여부, 원하는 숫자만큼 미리 만들어 Shared Pool의 Library Cache 상주 CREATE SEQUENCE MEMBER_SEQ START WITH 8000 INCREMENT BY 1 MAXVALUE 100000 MINVALUE 100000 CACHE 20; 시퀀스 수정 ALTER SE..

날짜 연산 -- 1일 후 SELECT SYSDATE + 1 FROM DUAL; -- 1일 전 SELECT SYSDATE - 1 FROM DUAL; -- 1시간 후 SELECT SYSDATE + 1/24 FROM DUAL; -- 5시간 후 SELECT SYSDATE + 5/24 FROM DUAL; -- 1시간 전 SELECT SYSDATE - 1/24 FROM DUAL; -- 5분 후 SELECT SYSDATE + 5/24/60 FROM DUAL; -- 5초 후 SELECT SYSDATE + 5/24/60/60 FROM DUAL; -- 2달 후 SELECT ADD_MONTHS(SYSDATE, 2) FROM DUAL; -- 2달 전 SELECT ADD_MONTHS(SYSDATE, -2) FROM DUAL; ..

CREATE OR REPLACE PROCEDURE SP_WORK ( IN_OPERATION IN VARCHAR2 , IN_USER_JOB IN VARCHAR2 , RETURN_CODE IN OUT VARCHAR2 , ERROR_MESSAGE IN OUT VARCHAR2 ) IS BEGIN RETURN_CODE := 0; ERROR_MESSAGE := ''; IF IN_OPERATION = 'CREATE' THEN FOR USERS IN ( SELECT USER_ID , USER_NAME , USER_GENDER , USER_JOB FROM USER_TABLE WHERE USER_JOB = IN_USER_JOB ) LOOP DBMS_OUTPUT.PUT_LINE(USERS.USER_NAME); MERGE I..

Spring Boot / Oracle / Mybatis 차례 CASE1 Service.java package project; import java.util.ArrayList; import java.util.List; @Service public class Service { public List retrieveTasks(DataDTO dataDTO) { // 파라미터 states는 "10,20,30" 형식으로 받는다. String [] states = dataDTO.getStates().split(","); List stateList = new ArrayList(); for (String state : states) { stateList.add(state) } dataDTO.setStateList(stat..

Spring Boot / Oracle / Mybatis 차례 PersonController package com.gaeyou.firstproject.contoller; import java.io.IOException; import java.util.Arrays; import java.util.stream.Collectors; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpHeaders; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import org.spri..

Spring Boot / Oracle / Mybatis 차례 { CALL /* saveData */ SP_SAVE_DATA ( IN_OPERATION => #{operation , mode=IN , jdbcType=VARCHAR} ,IN_DATA => #{data , mode=IN , jdbcType=VARCHAR} ,RETURN_CODE => #{returnCode , mode=OUT , jdbcType=VARCHAR} ,ERROR_MSG => #{errorMsg , mode=OUT , jdbcType=VARCHAR} ) } CREATE OR REPLACE PROCEDURE SP_SAVE_DATA ( IN_OPERATION IN VARCHAR2 -- , IN_DATA IN VARCHAR2 -- , RE..