PL/SQL의 유형
-
익명 블록
- (Anonymous block),
- 이름 없는 블록이라 불리며 간단한 block 수행 시 사용됨
-
함수
- Function
- procedure와 수행되는 결과가 유사하나 값 반환 여부에 따라 차이가 있음
- 함수는 반환값이 있음
-
프로시저
-
procedure
-
지정된 특정처리를 실행하는 서브프로그램의 한 유형으로 단독으로 실행되거나
-
다른 프로시저나 다른 툴 등에 의해 호출되어 실행됨
-
프로시저란 : 내가 실행하고 싶은 코드를 저장해놓는것이다.
- 예 : a테이블의 내용을 삭제하는 내용 ``` create proceduer 프로시저명 is begin 실행하고싶은 코드 end; / ``` - 실행법 ``` execute 프로시저명; ``` - 내가 저장해둔 코드가 자동 실행된다 - a의 내용이 삭제되는것이다. - 코드 확인 법 ``` SELECT * FROM USER_SOURCE WHERE NAME = '프로시저명 반드시 대문자로 쓸것!'; ```
- 매개변수를 이용하여 프로시저 만들기 ``` create procedure proc_del_emp_id(p_emp_id emp_dup.emp_id%type) --create procedure 프로시저명 (변수명 타입을 가지고올 표.칼럼%type) is begin delete from emp_dup where emp_id = p_emp_id; -- delete from 실행할표 where 실행칼럼 = 칼럼과 동기화할 지정한 변수 commit; dbms_output.put_line(p_emp_id||'번 사원을 삭제했습니다.'); -- 실행메세지 end; / exec proc_del_emp_id('201'); --exec 프로시저명('201'); <- 201번 사원이 삭제된다 exec proc_del_emp_id('&숫자'); --입력한 숫자의 사원이 삭제된다 ``` - 매개변수 IN : 데이터를 전달받을때 - 매개변수 OUT : 수행된 결과를 반환할떄 - 매개변수 INOUT : 데이터를 전달받고 수행된 결괄르 반홚ㄹ대 - 바인드변수 : 값을 받아 사용하는 변수] ```sql create procedure proc_select_emp_id ( p_emp_id in employee.emp_id%type, --p_emp_id는 입력받는값이라 in 타입을 불러오고 쉼표 p_emp_name out employee.emp_name%type, --p_emp_name은 출력값이라 out --이하동일 p_salary out employee.salary%type, p_bonus out employee.bonus%type ) is begin select emp_name,salary, nvl(bonus,0) --연결할 컬럼 지정 into p_emp_name,p_salary, p_bonus --변수를 연결한 칼럼값으로 할당 from employee where emp_id=p_emp_id; --할당받을 조건 end; / var b_emp_name varchar2(20); var b_salary number; var b_bonus number; -- 출력할 변수값 만들기 exec proc_select_emp_id('202', :b_emp_name, :b_salary, :b_bonus); -- 아까 만들었던 프로시저 실행 입력받을값 -- :출력해줄 변수명 print b_emp_name; --print하고 변수명 적기 -- 모아보기 var result varchar2(100); exec :result := (:b_emp_name|| ' '|| :b_salary ||' '|| :b_bonus); --result라는 변수 하나만 지정한다 -- result의 값으로 실행 print result; --값이 출력된다 ``` 참고용 예제 ```sql -- 실습1) JOB테이블에 INSERT를 할 때 같은 직급코드가 있으면 UPDATE를 수행하고 없으면 -- 그대로 INSERT를 하는 PROCEDURE를 작성하시오. -- 1단계. JOB테이블에 INSERT하는 프로시저를 작성 -- 2단계. 요구사항에 맞게 조건문을 추가하여 변경 create table copy_job as select * from job; ------테이블 생성-------------------- create procedure insert_job( p_job_code in job.job_code%type, p_job_name in job.job_name%type --값을 받을것이기에 in을 넣었다 --in은 기본값이라 안써도 된다 ) is begin insert into copy_job values(p_job_code, p_job_name); commit; -- 표에 값을 입력받을 코드를 작성한다 end; / ------프로시저 생성------------------------------------- exec insert_job('J8','인턴'); --------프로시저 실행--------------------------- -------추가 칼럼에 제약조건 걸기--------------------- alter table copy_job add constraint py primary key (job_code); alter table copy_job drop constraint py; --제약조건 삭제 alter table copy_job add constraint jcode_pk primary key (job_code); --제약조건 다시 걸기 ```데이터가 없는것을 확인할때
select count(job_code) from copy_job where job_code='D9';
-
- count를 써보자
## 원래 값이 있다면 그 값을 다시 쓴다
```sql
CREATE OR REPLACE PROCEDURE PROC_ADD_JOB_DUP(
P_JOB_CODE IN copy_job.JOB_CODE%TYPE,
P_JOB_NAME IN copy_job.JOB_NAME%TYPE
)
IS
V_CNT NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO V_CNT
FROM copy_job
WHERE JOB_CODE = P_JOB_CODE;
IF(V_CNT > 0)
THEN
UPDATE copy_job SET JOB_NAME = P_JOB_NAME
WHERE JOB_CODE = P_JOB_CODE;
ELSE
INSERT INTO copy_job VALUES(P_JOB_CODE, P_JOB_NAME);
END IF;
COMMIT;
END;
/
exec PROC_ADD_JOB_DUP ('J8','인턴');
select * from copy_job;
```funtion
- 리턴값이 반드시 존재하는 객체
- 문자형 사용시에는 매개변수 리턴의 데이터 타입의 크기를 지정하지 않는다.
-
varchar2(30) XXXx → varchar2
--헤드폰 씌우기 create or replace function make_headphone(p_str varchar2) --펑션만들기 return varchar2 --리턴할 행식 선언하기 is madeheadphone varchar2(32767); --변수 생성 begin madeheadphone := 'd'||p_str||'b'; --리턴할 값 구성 return madeheadphone; --리턴 end; / var result varchar2; --리턴값 출력할 변수 선언 exec :result := make_headphone('(●^◡^●)'); --변수안에 함수를 넣어 출력 print result; --출력코드 begin dbms_output.put_line(make_headphone('&얼굴')); end; / --출력코드--예제1. 사번을 입력 받아 해당 사원의 연봉을 계산하여 리턴하는 저장함수를 만들어 출력하시오 CREATE OR REPLACE FUNCTION FN_BONUS_CALC(V_EMPID VARCHAR2) RETURN NUMBER IS V_SAL EMPLOYEE.SALARY%TYPE; V_BONUS EMPLOYEE.BONUS%TYPE; CALC_SAL NUMBER; BEGIN SELECT SALARY, NVL(BONUS, 0) INTO V_SAL, V_BONUS FROM EMPLOYEE WHERE EMP_ID = V_EMPID; --RETURN (V_SAL * 12 + V_SAL * V_BONUS); CALC_SAL := (V_SAL * 12 + V_SAL * V_BONUS); RETURN CALC_SAL; END; /
-
Trigger
- 특정이벤트나 DDL 의 DML문장이 실행되었을떄
- 자동적으로 어떤 일련의 동작(Operation)처리가 수행되도록 하는 데이터베이스 객체의 하나
- 회원탈퇴가 이루어질 경우 (employee에서 delete되었을떄)
- 해당 회원정보가 다른 테이블에 저장이 필요한 경우 (delmember에서 insert가 됨)
- 데이터 변경이 있을때 (department에서 update가 되었을때)
- 해당 정보들을 저장하는 경우 log에서 insert가 됨
- 회원탈퇴가 이루어질 경우 (employee에서 delete되었을떄)
CREATE [OR REPLACE] TRIGGER 트리거 이름
BEFORE(OR AFTER)
UPDATE (OR DELETE OR INSERT) ON 테이블명
[FOR EACH ROW]
BEGIN
(실행문)
END;
/ - 트리거 속성
-
bdfore : 데이터 처리가 실행되기 전 수행
-
after : 데이터 처리가 실행 된 후 수행
-
for each row : 데이터 처리시 건별로 (형별로) 실행, 테이블 레벨 트리고
-
old. 컬럼명 :
-
new. 컬럼명 :
-## 의사레코드 OLD, NEW
-- FOR EACH ROW를 사용해야 함
-- 1. INSERT 트리거 : OLD -> NULL, NEW -> 데이터변경후의 레코드
-- 2. UPDATE 트리거 : OLD -> 데이터변경전 레코드, NEW -> 데이터변경후의 레코드
-- 3. DELETE 트리거 : OLD -> 데이터변경전 레코드, NEW -> NULLcreate or replace trigger trg_emp_new after insert on employee for each row begin dbms_output.put_line('신입 사원이 입사했습니다.'); end; / insert into employee (emp_name,emp_id,emp_no,job_code,sal_level) values ('가나다','301',000000000,'J8','L5'); commit; delete employee where emp_name = '가나다';
-
hello world