2024.07.15

privatekim·2024년 7월 15일
oracle

아이티윌

목록 보기
4/6

제약 조건 조회

DROP TABLE HR.TEST PURGE;
CREATE TABLE hr.test(id number(1) PRIMARY KEY);
SELECT * FROM user_constraints WHERE table_name = 'TEST';

복습

배열 값의 데이터를 입력하는 도중 예외사항 발생하더라도 끝까지 실행하고 싶으면?

  • 중첩블락 : exception 처리 해야함.
DECLARE
    TYPE t_id IS TABLE OF NUMBER;
    v_id t_id := t_id(1,2,3,4,5,1,6,10,7);
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        BEGIN 
             INSERT INTO hr.test(id) VALUES (v_id(i));
        EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(sqlerrm); --오류 내용 출력
        END;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(sqlerrm); --오류 내용 출력
END;
/
ROLLBACK;
  • FORALL (SQL문 뭉텅이로 뭉처셔 던짐 - 문맥전환 감소)
DECLARE
    TYPE t_id IS TABLE OF NUMBER;
    v_id t_id := t_id(1,2,3,4,5,21,1,1,6,10,7);
    dml_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_err, -24381);
BEGIN
    FORALL i IN v_id.first..v_id.last SAVE EXCEPTIONS
        INSERT INTO hr.test(id) VALUES (v_id(i));
EXCEPTION
    WHEN dml_err THEN--오류 내용 출력
        FOR i in 1..sql%bulk_exceptions.count loop 
        dbms_output.put_line(sql%bulk_exceptions(i).error_index);
        DBMS_OUTPUT.PUT_LINE(sqlerrm(-sql%bulk_exceptions(i).error_code));-- 오류코드의 오름차 순으로 출력
        end LOOP;
END;
/

sparse collection

배열의 중간에 값이 없다. 즉 갭이 있는 경우 forall 이용해서 대량의 데이터 입력시 오류 발생. -> 비정상적인 종료 발생(auto rollback)

  • 기존 예외 처리 방식
DECLARE
    TYPE t_tab IS TABLE OF test%rowtype index by pls_integer;
    v_tab t_tab;
    dml_err exception;
    pragma exception_init(dml_err,-24381);
BEGIN
    SELECT object_id, object_name
    BULK COLLECT INTO v_tab
    FROM all_objects
    WHERE rownum <= 10;
    
    dbms_output.put_line(v_tab.count);
    v_tab.delete(2);
    v_tab.delete(4);
    v_tab.delete(6);
    dbms_output.put_line(v_tab.count);
    
    FORALL i in v_tab.first..v_tab.last 
        INSERT INTO hr.test values v_tab(i);
    
    rollback;
EXCEPTION
    WHEN dml_err THEN--오류 내용 출력
        for i in v_tab.first..v_tab.last loop
            dbms_output.put_line(sql%bulk_rowcount(i));
        end loop;
        FOR i in 1..sql%bulk_exceptions.count loop 
        dbms_output.put_line(sql%bulk_exceptions(i).error_index);
        DBMS_OUTPUT.PUT_LINE(sqlerrm(-sql%bulk_exceptions(i).error_code));-- 오류코드의 오름차 순으로 출력
        end LOOP;
END;
/

예외처리가 너무 길다.

FORALL IN INDICIES OF

예외처리를 하지 않고 forall 문에서 한번에 처리 가능. 즉 오류를 발생시키지 않음.

DECLARE
    TYPE t_tab IS TABLE OF test%rowtype index by pls_integer;
    v_tab t_tab;
BEGIN
    SELECT object_id, object_name
    BULK COLLECT INTO v_tab
    FROM all_objects
    WHERE rownum <= 10;
    
    v_tab.delete(2);
    v_tab.delete(4);
    v_tab.delete(6);
    
    FORALL i IN INDICES OF v_tab
        INSERT INTO hr.test values v_tab(i);
END;
/

하지만 오류를 발생시키지 않기 때문에 어떤 배열에서 오류를 발생시켰는지 확인하기 위해서는 기존의 방식을 활용해야 한다.

SUB PROGRAM

익명블록구조 단점

자주 사용하는 익명 블록구조를 다시 불러올 때 마다 PARSE과정을 거쳐 컴파일 해야한다.
즉 데이터베이스에 저장되어 있지 않는다. 소스코드를 우리가 가져가야 한다.
다른 응용프로그램에서도 해당 프로그램을 호출할 수 없다.
입력값, RETURN값 처리를 바인드 변수를 이용해야 한다. (PARAMETER 사용 불가)

  • 사용하고자 하는 테이블에 대한 접근 권한만 있으면 된다.
DECLARE
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = :b_id;
    
    dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.salary);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE(:b_id || '에 해당하는 사원은 존재하지 않는다.');
END;
/

이러한 문제를 SUB PROGRAM을 이용해 극복하고자 한다.

  • procedure, function, package

PROCEDURE(프로시저)

특정 작업을 수행하는 서브 프로그램으로 데이터베이스에 객체로 저장된다.

DB에 객체로 저장되기 떄문에 재사용성과 유지관리에 대한 편리성이 보장된다.

  • 프로시저 vs 함수 : 없어요... / 함수 : SQL, 프로시저문에서 사용 return값 있음. / 프로시저 : SQL(x) 함수처럼 못 씀. exec에서 사용. 호출단위 프로그램에서 사용. 예도 리턴값 사용 가능.

SUB PROGRAM(프로시저) 생성

이름이 있는 PL/SQL 구조이다.

데이터베이스에 저장되어 한 번만 컴파일 한 후 호출시에는 컴파일된 코드를 사용한다.
이름도 존재하기 때문에 다른 프로그램에서도 호출할 수 있다.
PARAMETER를 이용해 입력값, RETURN값을 처리할 수 있다.

-내가 받은 system권한

select * from session_privs;

에서 CREATE PROCEDURE가 있어야지만 PROCEDURE, FUNCTION, PACKAGE를 생성할 수 있다.

CREATE OR REPLACE PROCEDURE emp_proc(p_id IN number) --procedure with formal parameter
IS -- DECLARE 대신에 사용
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = p_id;
    
    dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.salary);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE(p_id || '에 해당하는 사원은 존재하지 않는다.');
END;
/

생성된 프로시저 확인

select *
from user_source
where name = 'EMP_PROC'
ORDER BY  LINE;

또는

또는

select * from user_objects where object_name = 'EMP_PROC';

formal parameter(형식 파라미터), 형식 메개변수

(변수 모드 데이터타입)

  • 파라미터 모드 (in, out, in out)

  • in : 입력값 받겠다. 입력값을 처리하는 기능. 호출환경에서 프로시저로 값을 전달한다. 입력된 실제 파라미터 값은 반드시 상수값으로 동작한다. 임의로 수정 X ( 생략 가능 - 기본값 in모드)

  • out : 결과값 주겠다. 프로그램 안에서 값을 호출환경으로 전달한다. return. 호출 환경에서 받아올 변수가 필요하다. 변수로 동작한다.

  • in out : 변수로 동작한다. 입력된 초기 value를 수정하여 갱신 , return 할 수 있다.

actual parameter(실제 파라미터), 실제 매개변수

호출 서브프로그램의 파라미터 리스트에 사용되는 실제 값.
파라미터 안에 들어간 실제 매개변수로 입력된 파라미터.

SUB PROGRAM(프로시저) 실행

생성한 프로시저를 사용하는 방법은 execute를 이용해서 해당 프로시저를 실행할 수 있다.

EXEC emp_proc(100) -- procedure with actual parameter

또는 다른 프로그램에서 호출하여 사용할 수 있다.

BEGIN
	emp_proc(100); -- procedure with actual parameter
END;
/

프로시저 with out 모드

CREATE OR REPLACE PROCEDURE emp_proc(p_id number, p_name out varchar2, p_sal out number)
is
    -- 선언할 거 없더라도 is절 반드시 선언해야 함
begin
    select last_name, salary
    into p_name, p_sal -- out모드의 변수에 값 할당
    from hr.employees
    where employee_id = p_id;
    dbms_output.put_line(p_name || ' ' || p_sal);
exception
    when no_data_found then
        dbms_output.put_line(p_id ||'번 사원은 존재하지 않는다.');
end;
/

실행

DECLARE
    v_name varchar2(30);
    v_sal number;
BEGIN
    emp_proc(100,v_name,v_sal);
    dbms_output.put_line(v_name);
    dbms_output.put_line(v_sal);
    
END;
/

또는 바인드 변수 사용 가능

var b_name varchar2(30);
var b_sal number;
exec emp_proc(100,:b_name,:b_sal)
print :b_name, b_sal

프로시저 with in out 모드

CREATE OR REPLACE PROCEDURE p_time(id in out number)
IS

BEGIN
    id := id * 10; -- 입력 값을 * 10 하여 수정함.
END;
/

또는

CREATE OR REPLACE PROCEDURE phone(p_phone in out varchar2)
is
begin
    p_phone := substr(p_phone,1,3)||'-'
    		||substr(p_phone, 4,4)||'-'
    		||substr(p_phone, 8);
end;
/

실행

DESC 프로시저명

SHOW ERROR

프로시저에서 오류가 발생할 때 컴파일러에서 오류를 보여주는 명령어.
프로시저가 컴파일 실패하더라도 코드소스 객체정보는 딕셔너리에 저장되어 user_object, user_source에서 확인할 수 있다.

형식 매개변수의 지정방식

다음 프로세서에 대하여...

CREATE OR REPLACE PROCEDURE insert_proc(id in number, name in varchar2, day in date default sysdate, deptno in number default 10)
is
begin
    insert into hr.test values (id, name,day,deptno);
end insert_proc; --사용 가능 / 트랜잭션 살아있음
/
  • 위치 지정 방식
exec insert_proc(1,'scott',to_date('2022-01-01','yyyy-mm-dd'), 10);
  • 이름 지정 방식
exec insert_proc(id => 1,name => 'scott', deptno => 10);

특정 매개변수에 대해 defualt 값을 넣고 싶을 때 사용할 수 있다.

  • 혼합 방식 (위치지정 + 이름지정)
exec insert_proc(3,name => 'khazan', deptno => 30);

이름 지정방식 사용 이후에는 위치지정방식을 사용할 수 없다.

exec insert_proc(3,name => 'khazan', sysdate, deptno => 30); -- 오류 발생

느낀 점

일지 작업 하는 습관.

profile
privatekim

0개의 댓글