2022-12-01 데이터 베이스 오라클 SQL : 프로시져(Procedure)
Stored Procedure : Procedure
- 특정 결과를 산출할 수 있는 모듈을 작성하고 컴파일하여 서버의 프로시져 캐쉬에 저장하여 사용
- 모든 응용프로그램에서 사용할 수 있도록 캡슐화 기능 제공 => 일관성 제공
- 데이터베이스 내부 구조에 대한 보안성 제공 -- 뷰... 구조를 사용자가 모르고 결과값만 반환
- 프로시져는 반환 값이 없음
※ function 과 procedure
procedure : procedure 이름을 통한 반환.
- procedure 값을 내보낼수 있음. ' out '이라는 매개변수 사용
function : 함수명을 통해서 결과가 반환됨.
모듈화 : 특정한 기능의 모음을 보관
응용프로그램에 의해서 내부 값이 변경될 수 없음
(사용형식)
CREATE [OR REPLACE] PROCEDURE 프로시져명[(
매개변수 [IN|OUT|INOUT] 데이터타입[:=디폴트 값],--데이터타입 크기는 쓰지말자
:
매개변수 [IN|OUT|INOUT] 데이터타입[:=디폴트 값])
IS|AS
선언영역
BEGIN
실행역역
END; -- 매개변수 : 프로시져를 수행하기 위해 필요함.
-- 매개변수가 in 입력용 / out 출력용 /
-- inout 둘다가능 = 컴파일할때 결정불가 / 실행할때 결정(DBMS 부담으로 사용자제)
-- [IN|OUT|INOUT] 생략가능하면 in으로 취급함
-- 데이터를 정의하지 않고 프로시져를 호출했을 때 자동으로 입력하는 디폴트 => 사용자제
수행예) 직무코드,직무명,최대급여,최소급여를 입력 받아 HR계정의 JOBS테이블에 신규 데이터를
삽입하는 프로시져를 작성하시오.
CREATE OR REPLACE PROCEDURE insert_jobs_proc(
p_job_id IN HR.JOBS.JOB_ID%TYPE,
p_job_title IN HR.JOBS.JOB_TITLE%TYPE,
p_misal IN NUMBER,
p_mxsal IN NUMBER)
IS -- (변수 필요없음)
BEGIN
INSERT INTO HR.JOBS VALUES(p_job_id,p_job_title,p_misal,p_mxsal);
COMMIT;
END;
[프로시져의 실행]
1. 독립적 실행 -- 프로시져에 전달할 값만 기술해준다.
-- OUT 매개변수를 사용하지 않은 경우
-- 프로시져는 셀렉트문에 실행될수 없다. / 독립적 실행뿐...
EXEC|EXECUTE 프로시져명(매개변수list);
2. PL/SQL의 블록에서 실행 -- 변수 사용할 경우
- OUT 매개변수를 사용한 경우 - OUT매개변수에 의해 전달되는 값을 보관한 변수를 사용
-- 다른 프로시져나 다른함수 사용 할때 EXEC|EXECUTE 사용안함
-- OUT매개변수가 아니여도 필요에 의해 값을 저장할수 있는 변수 사용
프로시져명(매개변수list)
EXECUTE insert_jobs_proc('sample1','SAMPLE JOB TITLE',3000,5000);
사용예)직무코드를 입력받아 해당 자료를 JOBS테이블에서 삭제하는 프로시져 작성
CREATE OR REPLACE PROCEDURE delect_jobs_proc(
p_job_id IN JOBS.JOB_ID%TYPE)
IS
L_CNT NUMBER:=0;
BEGIN--삭제는 데이터가 있어야 삭제가 된다. 해당 자료의 존재유무 확인
SELECT COUNT(*) INTO L_CNT
FROM JOBS
WHERE JOB_ID=p_job_id; -- 찾은 자료를 L_CNT에 넣어줌
IF L_CNT!=0 THEN
DELETE FROM JOBS WHERE JOB_ID=p_job_id;
ELSE
DBMS_OUTPUT.PUT_LINE('해당자료가 테이블에 없습니다..');
END IF;
COMMIT;
END;
EXEC delect_jobs_proc('sample1');
사용예)오늘이 2020년 7월 28일이라고 가정하고 매출정보를 장바구니에 저장하는 프로시져를 작성하시오
입력값은 회원번호,오늘날짜,상품코드, 수량이다.
CREATE OR REPLACE PROCEDURE insert_cart_proc(
p_mid IN MEMBER.MEM_ID%TYPE,
p_date IN DATE,
p_pid IN PROD.PROD_ID%TYPE,
p_qty IN NUMBER)
IS
L_CART_NO CART.CART_NO%TYPE;
L_FLAG CHAR(9):=TO_CHAR(p_date,'YYYYMMDD')||'%';
L_CNT NUMBER:=0;
BEGIN
SELECT COUNT(*) INTO L_CNT
FROM CART
WHERE CART_NO LIKE L_FLAG;
IF L_CNT=0 THEN
L_CART_NO:=TO_CHAR(p_date,'YYYYMMDD')||TRIM('00001');
ELSE
SELECT MAX(CART_NO)+1 INTO L_CART_NO
FROM CART
WHERE CART_NO LIKE L_FLAG;
END IF;
INSERT INTO CART VALUES(p_mid,L_CART_NO,p_pid,p_qty);
COMMIT;
END;
EXECUTE insert_cart_proc('a001',TO_DATE('20200728'),'P201000008',5);
사용예) 회원번호와 년도를 입력받아 해당회원의 구매금액합계를 반환받는 프로시져를 작성
입력 2개. 출력 1개
CREATE OR REPLACE PROCEDURE sum_cart_proc(
p_mid IN MEMBER.MEM_ID%TYPE,
p_year IN CHAR,
p_sum OUT NUMBER) -- OUT매개변수 선언
IS
L_FLAG CHAR(5):=P_YEAR||'%';
--LIKE 미리결합시켜서 사용할수도...
BEGIN
SELECT SUM(A.CART_QTY*B.PROD_PRICE) INTO p_sum -- OUT 매개변수로 선언한 값이 자동으로 들어옴
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE L_FLAG
AND A.CART_MEMBER=p_mid; -- 특정회원으로 제한
END;-- Procedure SUM_CART_PROC이(가) 컴파일되었습니다.
-- 실행 : 실행문은 블럭 내부에 있어야 함.
[실행]
DECLARE
L_SUM NUMBER:=0; -- 구매금액합계를 보관할 변수
L_NAME MEMBER.MEM_NAME%TYPE;
BEGIN
SELECT MEM_NAME INTO L_NAME
FROM MEMBER
WHERE MEM_ID='c001';
sum_cart_proc('c001','2020',L_SUM); -- 회원번호,년도, L_SUM <= p_sum
DBMS_OUTPUT.PUT_LINE('c001, '||L_NAME||' => '||L_SUM);
END;
---------------------------------------------------------------
c001, 신용환 => 19212000-- 독립실행이 아닌경우 EXECUTE는 안쓴다. // 독립실행이 아닐 땐 프로시져 이름만 쓴다.
-- OUT은 프로시져 안에서 계산된 것을 프로시져 밖으로 꺼내기 위해 사용
사용예) 거주지를 입력받아 CUSTOMERS 테이블에서 해당 거주지를 가진 회원정보를 삭제하고
삭제한 회원의 수를 반환하는 프로시져를 작성하시오
-- 프로시져는 반환값이 없다. 반환을 하려면 매개변수가 필요하다.
CREATE OR REPLACE PROCEDURE delete_cust_proc(
p_add char, p_cnt OUT NUMBER)
IS
BEGIN
SELECT COUNT(*) INTO p_cnt
FROM CUSTOMERS
WHERE MEM_ADD1 LIKE p_add||'%'; --입력받은 거주지의 숫자를 COUNT한걸 p_cnt에 넣는다.
DELETE FROM CUSTOMERS
WHERE MEM_ADD1 LIKE p_add||'%'; --입력받은 거주지와 맞는 사람 삭제
END;
[실행] -- OUT매개변수를 받으려면 변수가 필요하다./EXECUTE 안씀
ACCEPT P_ADDRESS PROMPT '삭제할 거주지명 입력 : '
DECLARE
L_CNT NUMBER:=0; -- L_CNT는 p_cnt에서 전달받은 것
BEGIN
delete_cust_proc('&P_ADDRESS',L_CNT);
DBMS_OUTPUT.PUT_LINE('&P_ADDRESS'||'에 거주한 회원 '||L_CNT||'명의 정보를 삭제함');
END;
-------------------------------------------------------------
서울에 거주한 회원 1명의 정보를 삭제함