※ 스토어드 프로시져는 반드시 수정, 삭제전에 소스백업을 해놓고 수정, 삭제해야 한다.
PL/SQL (ProcedureLanguage/SQL) 정의
Oracle 에서 스토어드 프로시져를 개발할때 사용하는 언어(문법)입니다.
PL/SQL은 ANSI SQL 보다 확장된 SQL로써 좀더 다양한 처리를 구현할수 있습니다.
* PL/SQL과 스토어드 프로시져는 같은 의미로 해석함
스토어드 프로시져 사용목적
일반 SQL 실행
흐름 : 클라이언트에서 서버로 단일 쿼리를 날려 실행함
단점 : 여러개의 SQL를 실행시 실행할 SQL 갯수만큼 실행
스토어드 프로시져 SQL 실행
흐름 : 서버에 스토어드 프로시져를 먼저 생성후 클라이언트에서 호출하여 실행함
장점 : 여러개의 SQL을 실행시 하나의 스토어드 프로시져에 담아 컴파일하여 서버에 생성한후 한번만 호출하여 실행
※ 스토어드 프로시져 생성후 커밋처리를 해줘야 한다.
※ 스토어드 프로시져 생성시 정적쿼리에서 사용하는 테이블, 컬럼이 해당 DB에 존재하지 않으면 에러를 발생한다. 단 동적쿼리의 테이블과 컬럼은 체크하지 않는다.
※ 스토어드 프로시져 생성시 다른 스토어드 프로시져를 호출하는 명령이 있을때 해당 스토어드 프로시져가 현재 존재안하면 에러를 발생한다.
스토어드 프로시져 사용이점
1. 프로그램 의존성이 낮고 독립성이 높다.
PRO-C, SQC, 쉘스크립트에서 스토어드 프로시져 수정시 스토어드 프로시져만 컴파일하면 된다. PRO-C, SQC, 쉘스크립트 파일은 재컴파일을 안해도 된다.
오라클 스토어드 프로시저로 할수없는 SQL
DDL(CREATE TABLE, DROP TABLE, ALTER TABLE)
PL/SQL 사용의 장점?
변수를 선언하고 사용할수 있으며 조건문을 사용할수 있다.
예외처리도 가능하며 네트웍트랙픽도 감소시켜주며 프로그래개발을 모듈화할수 있다.
트랜잭션 로직으로 개발가능하다.
서버에 이미 저장되어있으므로 실행속도가 빠르다.
서버에 저장되어 있으므로 보안에 좋다.
PL/SQL 블록 구조
※ PL/SQL 블럭 내부에는 SQL명령문과 PL/SQL명령문이 포함되어있다.
DECLARE
--변수,상수 선언
BEGIN
--실행 가능 SQL문,PL/SQL문
EXCEPTION
--에러처리
END;
※ BEGIN과 END SECTION은 꼭 필수 부이다.
※ SP는 컴파일하기전에 사용된 컬럼과 테이블이 있는지 체크한후에 컴파일을
시작하며 존재하지 않는 컬럼,테이블이 있다면 에러를 발생한다.
PL/SQL 블록에서 사용못하는 SQL문
CREATE,GRANT
※ SELECT,UPDATE,INSERT,DELETE는 사용가능하다.
PL/SQL 에서 여러행을 리턴하는 방법
여러개의 리턴값을 넘길때 "var1 || '/' || var1" 와 같이 구분자를 넣어서 하나로 넘겨서 사용하면 됩니다.
사용예제
CREATE OR REPLACE FUNCTION FUN_EX
(
IN_VAR1 VARCHAR2,
IN_VAR2 VARCHAR2,
IN_VAR2 VARCHAR2) RETURN NUMBER IS
RTN_VAR VARCHAR2(100);
INTO1 VARCHAR2(10);
INTO2 VARCHAR2(10);
INTO3 VARCHAR2(10);
BEGIN
SELECT VAR1, VAR2, VAR3
INTO INTO1, INTO2, INTO3
FROM 테스트
WHERE 조건하나
AND 조건둘
AND.. .;
RTN_VAR := INTO1 || '/' || INTO2 || '/' || INTO3;
-- 구분자는 편의대로 정하시면 됩니다.
RETURN RTN_VAR;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0; -- check
WHEN OTHERS THEN
RETURN 0; -- check
END FUN_EX;
PL/SQL 데이타타입 종류
v_hire date;
v_deptno number(2) not null = 0;
/* not null이 붙으면 꼭 초기화를 해야한다. */
v_location varchar2(13) := 'allanta';
/* not null이 없으면 꼭 초기화를 하지 않아도 된다. */
/* 가변길이 문자열 */
v_name char(4) := 'lee';
/* 고정길이 문자열 */
v_address long;
/* 32760 바이트를 저장할수는 문자열 */
v_true boolean;
/* 진리값을 저장한다. */
c_com constant number :=1400;
/* constant는 상이다. */
v_age emp.age%TYPE;
v_age2 v_age%TYPE;
/* %TYPE을 쓰는이유는 TABLE의 컬럼의 데이타형이 달라지거나 제어할 TABLE */
/* 컬럼과 같게하기위해 매번 확인하야하는 번거로움을 없애기 위해 */
/* 큰시스템에서는 프로시저형이 달라지면 다바꾸어야 하기때문에 테이블의 형을 참조
/* 해서 사용하면 테이블구조가 바뀌어도 자동으로 바뀌게 좋다. 대신 제대로 형을 */
/* 참조해야 한다.*/
v_loc loc /* 구조화되지않은 큰데이타 저장 4기가바이트까지 저장 */
PL/SQL 블록 유형
Anonymouse 유형(매번 서버에 전송하여 컴파일하여 실행함)
[declare]
begin
--실행소스
[exception]
end;
Procedure 유형(초이 한번만 서버에 생성하여 컴파일하여 호출하여 실행함)
CREATE OR REPLACE Procedure name
is
begin
--실행소스
[exception]
end;
Function 유형(초이 한번만 서버에 생성하여 컴파일하여 호출하여 실행함)
CREATE OR REPLACE Function name
Return datatype
is
begin
--실행소스[exception]
end;
Anonymouse PL/SQL 블록 유형
클라이언트에서 매번 PL/SQL블록을 만들어 서버에 전송하여 자동으로 컴파일되어 실행하는 방식으로 여러 SQL문을 하나의 PL/SQL블록으로 만들어 보내면 한번에 여러 SQL문실행이 가능하지만 매번 서버에서 컴파일되기때문에 PROCEDURE,FUNCTION유형보다 성능이 좋지 않다.
Anonymouse PL/SQL 사용예제
create table test
(
a number,
b date
);
select * from test;
/* select문에서 INTO절사용시 1개의 행(로우)만이 into절에 변수에 저장가능하다. */
Declare
/* 2개의 선언 */
a account.a_strday%TYPE;
b account.a_in%TYPE;
begin
/* a_strday,a_in의 값을 a,b에 넣어라 */
select a_strday,a_in
into a,b
from account
where a_type = '뮤직';
end;
/* select문에서 INTO절사용시 1개의 행(로우)만이 into절에 변수에 저장가능하다. */
/* 1개의 행의 컬럼값을 v_empno에 저장한후 test테이블에 대입한후 commit한다. */
Declare
v_empno number;
v_date date := sysdate;
begin
select 1
into v_empno
from dual;
insert into test (a,b) values (v_empno,v_date);
commit;
end;
/* 1개의 행의 컬럼값을 v_empno에 저장한후 test테이블에 대입한후 commit한다. */
/* PL/SQL UPDATE예제(UPDATE,DELETE는 다중행처리 가능) */
Declare
v_sale number := 2000;
begin
update test set a = v_sale;
delete from test where a = v_sale;
commit;
end;
/* PL/SQL UPDATE예제(UPDATE,DELETE는 다중행처리 가능) */
스토어드 프로시저 PL/SQL 블록 유형?
스토어드 프로시저란 어떤 업무를 수행 하기 위한 절차를 뜻하며 반복되는 코딩을
모듈화 하기 위하여 함수나 프로시져를 사용합니다.
보통 DB에서는 데이타를 조회하여 가져오고 그 데이타를 받는 언어쪽에서는 데이타를
가공하여 화면에 출력을 합니다. 하지만 DB에서 데이타조회와 가공까지 하면 좀더 빠르게
화면 출력이 되며 서버부하를 줄일수 있습니다.
ORACLE의 PROCEDURE을 이용하면 서버부하를 줄이며 좀더 빠른 화면출력이 되며 여러
쿼리들을 하나의 프로시저,함수로 만들어 한번에 실행시킬수 있습니다.
예를 들어 인터넷을 통해 극장표를 구매하는 프로시저로 표현 한다면
[극장표 구매 Procedure 시작 ]
1. 예매 사이트에 접속 한다.
2. 예매할 영화 선택 한다.
3. 예매 일자와 시간을 선택 한다.
4. 예매 매수를 선택 한다.
5. 위 과정이 모두 끝났으면 결재를 한다.
[극장표 구매 Procedure 종료 ]
위와 같이 어떤 프로세스 절차를 기술해 놓을것을 프러시저 라고 합니다.
Procedure에서 Procedure를 호출하는 방법
A프로시져에서 "프로시져명(변수, 변수2);" 명령으로 B프로시져를 호출할수 있다.
PROCEDURE and FUNCTION 의 차이점?
프로시저와 펀션 둘다 파라미터를 받고 리턴값을 지정할수있으나 가장 큰 차이점은
function은 반드시 리턴값을 하나만 가져야 하지만 procedure는 여러개의 리턴값을 가질수 있습니다.
생성된 PROCEDURE & FUNCTION 리스트보기
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE';
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION';
PROCEDURE & FUNCTION 삭제하기
DROP FUNCTION lee2;
DROP PROCEDURE lee2;
※ 삭제시 아래 메세지가 출력되는 경우
ORA-04021: 객체 KMS.KM_GET_KNOWLEDGE_LIST_PAGE의 잠금 대기중 시간이
초과됐습니다.
library lock 이 발생하여 해당 procedure 를 사용중이거나, 머 library lock 이 발생하면
해당 package, procedure, function 에 대한 작업을 할수 없습니다.
lock 관련 체크 script 를 통해서 체크 해보세요.
생성된 Procedure & Function의 내용보기
select * from user_source;
select * from user_source where name='DATETOSTRDAY';
또는
SELECT * FROM ALL_SOURCE WHERE OWNER = '소유자' AND NAME = '프로시져명'
※ MSSQL, MySQL, DB2도 특정 테이블에 프로시져와 펑션의 내용이 들어있습니다.
PROCEDURE & FUNCTION 실행하기
EXECUTE lee;
--PROCEDURE 실행하기EXEC lee;
--PROCEDURE 실행하기 예제 select function_name(column) from table_name;
PROCEDURE & FUNCTION 기본 문법
create or replace procedure lee
--파라미터 선언부
is
--변수 선언부
begin
--실행부(쿼리적용)
end lee;
create procedure lee
--파라미터 선언부
is
--변수 선언부
begin
--실행부(쿼리적용)
end lee;
LOOP문 :LOOP와 END LOOP사이의 문장을 반복 수행하며 BREAK문으로 빠져나감
WHILE문 : 제어 조건이 TRUE가 아닐 때까지 문장을 반복수행
PROCEDURE 예제
※ 주의할점
1) END; 와 END 프로시저&함수명; 은 같다. 보통 안쓰는게 편합니다.
2) CREATE OR replace PROCEDURE LEE 와 CREATE PROCEDURE LEE의 차이는 전자는 같은 프로시저 이름으로 계속 생성해도 새롭게 내용이 갱신되고 후자는 같은 프로시저명이 있으면 에러를 내고 실행을 멈춥니다.
3)프로시저나 함수 생성시에 에러가 나면 sqlplus에서 show error을 쳐서 에러내용을 확인합니다.
숫자를 받은후 UPDATE쿼리를 합니다.
CREATE OR REPLACE PROCEDURE LEE1
(v_empno IN NUMBER) /* 정수형 파라미터를 v_empno변수로 받는다. */
IS
BEGIN
UPDATE emp SET sal = sal * 1.1
WHERE empno = v_empno;/* query runtime */
COMMIT; /* update query commit */
END LEE1;
/
execute lee1(7369);
숫자를 받은후 UPDATE쿼리를 합니다.
CREATE OR REPLACE PROCEDURE LEE2
(v_empno in emp.sal%type)
/* v_empno변수에 파라미터로 받고 v_empno type은 emp테이블의 sal컬럼과 같은 타입이다. */
IS
BEGIN
UPDATE emp SET sal = sal * 1.1
WHERE empno = v_empno;/* query runtime */
COMMIT; /* update query commit */
END LEE2;
/
execute lee1(7369);
list 테이블에서 사번을 입력받아 COMMISSION_PCT 값을 수정하시오.
COMMISSION_PCT 는 영업사원이 담당한
주문(s_ord) 총금액(s_ord.total)으로 결정한다.
update_comm 라는 procedure 작성하시오.
a. 총금액이 100,000 미만이면 COMMISSION_PCT는 10
b. 총금액이 100,000 이상이고 1,000,000 미만이면 15
c. 총금액이 1,000,000 이상이면 20
d. NULL이면 0
create table list
(
id number,
money number,
comm number
);
create or replace procedure update_comm
(a_id in list.id%type)
is
v_total list.money%type;
v_comm list.comm%type;
begin
select sum(money)
into v_total
from list
where id = a_id;
if v_total < 100000 then
v_comm := 10;
elsif v_total < 1000000 then
v_comm := 15;
elsif v_total >= 1000000 then
v_comm := 20;
else
v_comm := null;
end if;
dbms_output.put_line('사번 : '||to_char(a_id));
dbms_output.put_line('TOTAL : '||to_char(v_total, '999,999,999'));
dbms_output.put_line('커미션 : '||to_char(v_comm,'999.99'));
update list
set comm = v_comm
where id = a_id;
commit;
end;
/
execute update_comm(1);
FUNCTION 예제
사원번호를 입력받아 사원의 월급을 출력합니다.
CREATE OR REPLACE FUNCTION lee3
(id IN NUMBER)
return number
IS
value1 NUMBER;
BEGIN
select sal into value1 from emp where empno=id;
return(value1);
END;
/
select lee3(empno) from emp;
부서번호을 입력받아 부서명을 return 해주는 함수를 만들자.
함수명 : f_deptname
인자 : id(s_dept.id%type)
리턴값 : varchar2
사원정보에 대해서
사번(id), 이름(last_name), 부서번호, 부서명을 조회하자.
create or replace function f_deptname
(a_id in dept.deptno%type)
return varchar2
is
v_dname dept.dname%type;
begin
select dname
into v_dname
from dept
where deptno= a_id;
return(v_dname);
end;
/
select f_deptname(deptno) from dept;
S_EMP 테이블에서 사번을 입력받아 해당 사원의 급여에 따른
세금을 구하시오.
급여가 1000 미만이면 급여의 5%, 급여가 2000 미만이면 7%,
급여가 3000 미만이면 9%, 그 이상은 12%로 세금을 정한다.
create or replace function f_tax
/*
정수형 파라미터 설정
(id in emp.empno%type)
*/
(id in number)
return number
/* 변수선언 */
is
v_sal NUMBER;
/* v_sal s_emp.salary%type; */
v_tax NUMBER;
/* 함수 실행문 */
begin
select sal
into v_sal
from emp
where empno = id;
if v_sal < 1000 then
v_tax := v_sal * 0.05;
elsif v_sal < 2000 then
v_tax := v_sal * 0.07;
elsif v_sal < 3000 then
v_tax := v_sal * 0.09;
else
v_tax := v_sal * 0.12;
end if;
return(v_tax);
end;
/
select f_tax(empno) from emp;
/*
함수명:숫자형식요일리턴함수
함수설명:날짜를 입력받아 요일을 숫자로 리턴하는 함수
함수작성자:이 준식
*/
create or replace FUNCTION DateToNumDay(DateValue IN date)/*받는 변수*/
return number/*리턴 데이타형*/
IS
ShowDay number;/*숫자변수 선언*/
BEGIN
/**********************************/
SELECT decode(to_char(DateValue,'D'),
'2','1',
'3','2',
'4','3',
'5','4',
'6','5',
'7','6',
'1','7') "요일숫자" into ShowDay
from dual;
/**********************************/
return(ShowDay);/*해당 변수를 리턴*/
end;
/
/*
함수명:문자형식요일리턴함수
함수설명:숫자요일을 입력받아 문자로 리턴하는 함수
함수작성자:이 준식
*/
create or replace FUNCTION NumdayToStrDay(NumValue IN number)/*받는 변수*/
return varchar2/*리턴 데이타형*/
IS
StrValue varchar2(10);/*문자변수 선언*/
BEGIN
/**********************************/
SELECT decode((NumValue),
1,'월요일',
2,'화요일',
3,'수요일',
4,'목요일',
5,'금요일',
6,'토요일',
7,'일요일') "요일" into StrValue
from dual;
/**********************************/
return(StrValue);/*해당 변수를 리턴*/
end;
/
/*
함수명:문자형식요일리턴함수
함수설명:날짜를 입력받아 요일을 문자로 리턴하는 함수
함수작성자:이 준식
*/
create or replace FUNCTION DateToStrDay(DateValue IN date)/*받는 변수*/
return varchar/*리턴 데이타형*/
IS
ShowDay varchar(10);/*숫자변수 선언*/
BEGIN
/**********************************/
SELECT decode(to_char(DateValue,'D'),
'1','일요일',
'2','월요일',
'3','화요일',
'4','수요일',
'5','목요일',
'6','금요일',
'7','토요일') "요일" into ShowDay
from dual;
/**********************************/
return(ShowDay);/*해당 변수를 리턴*/
end;
/
/*
함수명:숫자형식콤마문자리턴함수
함수설명:숫자를 입력받아 콤마처리를 문자로 리턴하는 함수
함수작성자:이 준식
*/
create or replace FUNCTION IntToComma(IntValue IN number)/*받는 변수*/
return varchar/*리턴 데이타형*/
IS
ShowDay varchar(20);/*문자변수 선언*/
BEGIN
/**********************************/
SELECT replace(to_char(IntValue,'999,999,999,999'),' ','') into ShowDay
from dual;
/*SELECT trim(to_char(IntValue,'999,999,999,999')) into ShowDay*/
/**********************************/
return(ShowDay);/*해당 변수를 리턴*/
end;
/
/*
함수명:문자형식 숫자,문자,null 체크 출력
함수설명:문자형식을 숫자인지 문자와 숫자인지 null인지 알려준다.
함수작성자:이 준식
*/
create or replace function NumCheck(asString varchar2)
return varchar2
is
nTemp number(1);
begin
select sign(asString)
into nTemp
from dual;
if nTemp is null then
return 'null';
end if;
return 'number';
exception
when others then
return 'string';
end;
/
/*
함수명:출력컬럼명을 보내서 해당날짜에 그컬럼값을 출력한다.
함수작성자:이 준식
*/
create or replace function test_01(param1 IN date,param2 IN VARCHAR2)
return varchar2
is
ls_code varchar2(20);
BEGIN
SELECT decode(param2,'a_type',a_type) INTO ls_code from ACCOUNT where A_DATE = param1;
return ls_code;
/* 정상적으로 실행되었으나 조회갯수가 0개면 발생 */
exception when no_data_found then
return '데이타가 없습니다.';
/* return null; */
END test_01;
/
select test_01(sysdate) from dual;
select test_01(to_date('2005-10-18','yyyy-mm-dd'),'a_type') from dual;
create or replace function test_01(param1 IN varchar2)
return varchar2
is
ls_code varchar2(20);
BEGIN
SELECT A_TYPE INTO ls_code from ACCOUNT where A_DATE = to_date(param1,'yyyy-mm-dd');
return ls_code;
/* 정상적으로 실행되었으나 조회갯수가 0개면 발생 */
exception when no_data_found then
return '데이타가 없습니다.';
/* return null; */
END test_01;
/
select test_01(sysdate) from dual;
select test_01('2005-10-18') from dual;
프로시져는 SQL문에서 호출할수 없지만 함수는 SQL문에서 호출할수 있다.
PL/SQC 자동실행 방법
윈도우의 경우
보조프로그램 > 시스템도구 > 예약된 작업에서 .bat 파일을 만든후 등록함
유닉스의 경우
쉘스크립트 파일을 만든후 크론에 등록하거나 nohup으로 실행
오라클의 기능을 이용한 경우
dbms_job 패키지를 이용한다.
