프로시저 사용법
execute immediate 'truncate table INSA_ALL';
truncate 사용법 단순 truncate table INSA_ALL -> 에러 발생
프로시저 호출
EXEC INSALOGIC.OK_INSERT;
EXEC INSALOGIC.OK_INSERT('VALUE');
프로시저 기본 구조
PROCEDURE PROC_TEST AS
BEGIN
--- 기능 구현 --->해당 부분 없을 경우 컴파일 에러
END PROC_TEST;
변수 값 받는 방법
PROCEDURE PROC_TEST(COLTEST1 IN VARCHAR2) AS
BEGIN
--- 기능 구현 --->해당 부분 없을 경우 컴파일 에러
END PROC_TEST;
변수 설정 방법
PROCEDURE PROC_TEST AS V_COUNT NUMBER := 0;
BEGIN
--- 기능 구현 --->해당 부분 없을 경우 컴파일 에러
select user_id into value_id from insa_table where user_id = 'id_value'
END PROC_TEST;
여러개도 설정 가능하다.
PROCEDURE PROC_TEST AS
V_COUNT NUMBER := 0;
V_VARCHAR VARCHAR2(50); := 'VARCHAR2';
BEGIN
*%TYPE 사용
패키지 선언부
테이블 컬럼과 동일한 타입을 사용함으로써 유지보수성을 높인다.
CREATE OR REPLACE PACKAGE user_package AS
-- 테이블 컬럼의 타입을 변수에 할당
v_user_id insa_all.user_id%TYPE; -- insa_all 테이블의 user_id 컬럼 타입 사용
v_user_name insa_all.user_nm%TYPE; -- insa_all 테이블의 user_nm 컬럼 타입 사용
-- 프로시저 선언
PROCEDURE get_user_details;END user_package;
패키지 본문
CREATE OR REPLACE PACKAGE BODY user_package AS
-- 프로시저 구현
PROCEDURE get_user_details IS
BEGIN
-- 변수에 값 할당
v_user_id := 'A123';
v_user_name := 'John Doe';
-- DBMS_OUTPUT 사용하여 출력
DBMS_OUTPUT.PUT_LINE('User ID: ' || v_user_id);
DBMS_OUTPUT.PUT_LINE('User Name: ' || v_user_name);
END get_user_details;END user_package;
-본문에 바로 %TYPE 사용 예시
CREATE OR REPLACE PACKAGE BODY user_package AS
PROCEDURE get_user_details(p_user_id IN VARCHAR2) IS
-- insa_all 테이블의 user_id 컬럼 타입을 사용
v_user_id insa_all.user_id%TYPE;
v_user_name insa_all.user_nm%TYPE; -- insa_all 테이블의 user_nm 컬럼 타입 사용
BEGIN
-- p_user_id에 해당하는 사용자 정보를 조회하여 변수에 담기
SELECT user_id, user_nm
INTO v_user_id, v_user_name
FROM insa_all
WHERE user_id = p_user_id;
-- 조회된 정보 출력
DBMS_OUTPUT.PUT_LINE('User ID: ' || v_user_id);
DBMS_OUTPUT.PUT_LINE('User Name: ' || v_user_name);
END get_user_details;END user_package;
*IF문
IF 조건 THEN 조건에 맞을 때 실행
END IF
예시)
PROCEDURE OK_INSERT AS V_COUNT NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO V_COUNT FROM INSA_ALL WHERE USER_ID = 'USER_ID_VALUE';
DBMS_OUTPUT.PUT_LINE('OK_INSERT 결과값'|| V_COUNT);
IF V_COUNT >= 1 THEN
INSERT INTO TABLE_A(COL1,COL2,COL3) VALUES('DATA1','DATA2','DATA3');
END IF;
*IF문 ELSEIF
IF 조건 THEN
ELSEIF (띄어쓰면 안된다.) THEN
ELSE
END IF
연선자의 경우 AND,OR 사용(&&,|| 사용불가)
IF A>B AND B=C THEN
END IF;
*반복문
LOOP문 EXIT 문이 없으면 무한 루프 EXIT 조건에 맞으면 반복 종료
DECLARE
i NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('i = ' || i);
i := i + 1;
EXIT WHEN i > 5; --> 반복 종료 조건
END LOOP;
END;
FOR문 정해진 횟수만큼 반복
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('i = ' || i);
END LOOP;
END;
WHILE문 조건이 참일 동안 반복
조건을 검사하고 그 조건이 참일 때만 실행
BEGIN
WHILE i <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('i = ' || i);
i := i + 1;
END LOOP;
END;
LOOP와 WHILE 차이
LOOP는 반복이 기본 WHILE은 조건을 우선 확인 후 반복 진행 여부 결정
CASE문
여러 조건 중 하나에 맞는 분기를 실행하고 싶을 때 사용
DECLARE
grade CHAR := 'B';
BEGIN
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Fair');
ELSE DBMS_OUTPUT.PUT_LINE('Poor');
END CASE;
END;
*커서 사용법(다중 값을 넣을 때는 커서 사용)
-기본형
DECLARE
-- 커서 선언
CURSOR c_user IS
SELECT user_id, user_nm FROM insa_all;
-- 커서 데이터를 받을 변수 선언
v_user_id insa_all.user_id%TYPE;
v_user_nm insa_all.user_nm%TYPE;
BEGIN
-- 커서 열기
OPEN c_user;
-- 루프를 돌며 한 행씩 가져오기
LOOP
FETCH c_user INTO v_user_id, v_user_nm;
EXIT WHEN c_user%NOTFOUND;
-- 처리 로직
DBMS_OUTPUT.PUT_LINE('ID: ' || v_user_id || ', NAME: ' || v_user_nm);END LOOP;
-- 커서 닫기
CLOSE c_user;
END;
-간단한 버전
BEGIN
FOR rec IN (SELECT user_id, user_nm FROM insa_all) LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || rec.user_id || ', NAME: ' || rec.user_nm);
END LOOP;
END;
-프로시저에서 사용
CREATE OR REPLACE PROCEDURE PROC_CURSOR_TEST IS
BEGIN
FOR rec IN (SELECT user_id, user_nm FROM insa_all WHERE user_dept = '개발팀') LOOP
INSERT INTO insa_move(user_id, user_nm, user_dept)
VALUES (rec.user_id, rec.user_nm, '이동됨');
END LOOP;
COMMIT;
END;
-머지문
MERGE INTO 대상테이블 A
USING (SELECT ... FROM 소스테이블) B
ON (A.매칭조건 = B.매칭조건)
WHEN MATCHED THEN
UPDATE SET A.컬럼1 = B.컬럼1, A.컬럼2 = B.컬럼2
WHEN NOT MATCHED THEN
INSERT (컬럼1, 컬럼2) VALUES (B.컬럼1, B.컬럼2);
-예시
MERGE INTO insa_move A
USING (SELECT user_id, user_nm, user_dept FROM insa_all) B
ON (A.user_id = B.user_id)
WHEN MATCHED THEN
UPDATE SET
A.user_nm = B.user_nm,
A.user_dept = B.user_dept
WHEN NOT MATCHED THEN
INSERT (user_id, user_nm, user_dept)
VALUES (B.user_id, B.user_nm, B.user_dept);
기본 쿼리
insert into insa_one select * from insa_two (뒤에 where 가능)
insa_one 테이블에 insa_two 데이터 모두 적재(테이블 구조가 동일해야 한다.)
