SQL의 확장
절차적 프로그래밍을 가능
Block 단위로 중첩 가능
모든 문장의 종결 기호는 ;을 사용
대입 연산자 는 :=
실행은 /를 이용(또는 exec)
기본 구조
[DECLARE
variables, cursor, user_defined, exception]
BEGIN
SQL,PL/SQL statements;
[EXCEPTION
actions to perform when errors occur]
END;
//////////////////////////////////////////////////////////////////
PL/SQL 기본 예제
set serveroutput on;
-- 서버쪽에서 출력되는 결과를 클라이언트 측에서 볼 수 있도록 한다는 의미
/* 여러 줄 주석 */
create table test123(
a number,
b varchar2(10)
);
declare
a number;
begin
a := 30;
dbms_output.put_line('a : ' || a);
insert into test123 values(111,'aaa');
end;
/
select * from test123;
PL/SQL은 null 연산을 하기 때문에 변수 선언시 초기화를 해주지 않으면 그 변수에 어떠한 값을 넣어도 null로 나오게 되므로 null + n을 하여도 변수에는 null이 나오게 된다.
//////////////////////////////////////////////////////////////////
PL/SQL 외부에 선언된 변수 사용하기
variable aaa number;
declare
a number := 10;
b number := 20;
begin
:aaa := a + b;
-- 외부에서 선언된 변수는 변수명 앞에 :을 사용한다.
dbms_output.put_line('두 수의 합 구하기 성공');
dbms_output.put_line('aaa : ' || :aaa);
end;
/
print aaa;
//////////////////////////////////////////////////////////////////
위와 같은 코드는 매번 실행할때마다 컴파일/러닝을 하게 된다.
그러나 실제로 사용되는 것은 아래와 같이 프로시져를 만들며 한번만 컴파일하고 만들어진 것을 이용하는 방법을 쓴다.
CREATE OR REPLACE PROCEDURE proTest
IS
a number := 10;
b number := 20;
begin
a := a + b;
-- 외부에서 선언된 변수는 변수명 앞에 :을 사용한다.
dbms_output.put_line('두 수의 합 구하기 성공');
dbms_output.put_line('aaa : ' || a);
end;
/
show error;
--컴파일 에러가 발생하면 에러 확인
생성된 프로시저를 / 말고 실행하기 하는 방법
exec proTest;
//////////////////////////////////////////////////////////////////
기본 문법
CREATE OR REPLACE procedure name
IN argument
OUT argument
IN OUT argument
IS
[변수의 선언]
BEGIN
[PL/SQL Block]
-- SQL문장, PL/SQL 제어 문장
[EXCEPTION]
-- error가 발생할 때 수행하는 문장
END;
//////////////////////////////////////////////////////////////////
agument 사용 예제
두 수를 입력받아 합을 출력하는 프로시저를 생성하라
create or replace procedure prosum(
a IN number,
b In number,
c OUT number
)is
begin
c := a + b;
dbms_output.put_line('두 수의 합 : ');
dbms_output.put_line(a || ' + ' || b || ' = ' || c);
end;
/
variable num number;
exec prosum(3, 5, :num);
print num;
//////////////////////////////////////////////////////////////////
두 수를 입력받아 두 번째 수에 합의 결과를 반환하고 출력하는 프로시저를 생성하라
create or replace procedure prosum(
a IN number,
b In OUT number
)is
temp number;
begin
temp := b;
b := a + b;
dbms_output.put_line('두 수의 합 : ');
dbms_output.put_line(a || ' + ' || temp || ' = ' || b);
end;
/
variable num number;
exec :num := 10;
exec prosum(3, :num);
print num;
//////////////////////////////////////////////////////////////////
변수
비 조합 형 변수
Scalar : 일반적인 형태의 데이터 타입
VARCHAR2(n)
NUMBER(p,s)
DATE
CHAR(n)
LONG
LONG RAW
BOOLEAN
BINARY_INTEGER
PLS_INTEGER
%Type : 특정 테이블의 컬럼에 대한 데이터 타입 또는 먼저 선언된 변수(컬럼)
%Rowtype : 특정 테이블의 모든 컬럼을 받는 데이터 타입(테이블)
//////////////////////////////////////////////////////////////////
number 변수와 integer의 차이점
create or replace procedure proTest1
is
a1 number;
a2 number;
a3 number;
b1 integer;
b2 integer;
b3 integer;
begin
a1 := 10;
a2 := 3;
a3 := a1 / a2;
dbms_output.put_line('a3 : ' || a3);
b1 :=10;
b2 :=3;
b3 := b1 / b2;
dbms_output.put_line('b3 : ' || b3);
end;
/
exec protest1;
-- integer 정수형은 소수점에 대하여서는 반올림 처리 한다.
//////////////////////////////////////////////////////////////////
boolean형 테스트
true, false, null 3가지 형태로 존재한다.
create or replace procedure proTest1
is
a1 number;
a2 number;
b boolean;
b1 boolean;
begin
a1 := 10;
a2 := 10;
b1 := true and false;
if a1 > a2 then
dbms_output.put_line('a1이 크다');
elsif a1 < a2 then
dbms_output.put_line('a2이 크다');
else
dbms_output.put_line('a1과 a2가 같다');
end if;
if b1 then
dbms_output.put_line('b1 : true');
elsif b1 = false then
dbms_output.put_line('b1 : false');
else
dbms_output.put_line('b1 : null');
end if;
end;
/
exec protest1;
and 연산
true true : true
true false : false
true null : null
false null : false
//////////////////////////////////////////////////////////////////
create or replace procedure pronull
is
a varchar2(10);
begin
-- a = b : 같다를 의미함
if a is null then
dbms_output.put_line('a : null');
else
dbms_output.put_line('is not null');
end if;
end;
/
exec pronull;
//////////////////////////////////////////////////////////////////
PL/SQL
select 사용법
select aaa, bbb into xxx, yyy
from emp where 조건절
반환되는 레코드가 한 건인 경우
반환되는 레코드가 여러 건인 경우
set serveroutput on;
create or replace procedure proSearch(
--num IN Number
num IN emp.empno%Type
)is
/*
name varchar2(10);
sal number(7,2);
*/
name emp.ename%Type;
sal emp.sal%Type;
begin
-- 사원번호를 통한 이름과 급여 검색
select ename, sal into name, sal from emp where empno = num;
dbms_output.put_line('사원번호 : ' || num);
dbms_output.put_line('이름 : ' || name);
dbms_output.put_line('급여 : ' || sal);
end;
/
exec prosearch(7521);
//////////////////////////////////////////////////////////////////
제어문
기본 문법
IF condition THEN
statements;
[ELSIF condition THEN]
statements;
[ELSE
statements;]
END IF;
//////////////////////////////////////////////////////////////////
사원번호를 입력받아 그 사원의 급여를 구하고
급여가 0~1000 사이면 일반사원
급여가 1001~2000 사이면 관리직
급여가 2001 이상이면 경영진
이라고 출력하는 프로시저를 생성하라
create or replace procedure empin(
enInput IN Number
)is
sal emp.sal%type;
msg varchar(20);
begin
select sal into sal from emp where empno = eninput;
if sal >= 0 and sal <= 1000 then
msg := '일반사원';
elsif sal >= 1001 and sal <= 2000 then
msg := '관리직';
elsif sal >= 2001 then
msg := '경영진';
else
msg := '뭘입력한거야?';
end if;
dbms_output.put('사원번호 : ' || eninput || '은 ');
dbms_output.put_line(msg || '입니다.');
end;
/
exec empin(7934);
exec empin(7839);
//////////////////////////////////////////////////////////////////
사원번호를 입력받아 그 사원의 모든 정보를 출력하라.
create or replace procedure proemp(
num IN emp.empno%type
)is
myemp emp%rowtype;
begin
select * into myemp from emp where empno = num;
dbms_output.put_line('사원번호 : ' || num);
dbms_output.put_line('이름 : ' || myemp.ename);
dbms_output.put_line('업무 : ' || myemp.job);
dbms_output.put_line('매니져 : ' || myemp.mgr);
dbms_output.put_line('입사일 : ' || myemp.hiredate);
dbms_output.put_line('급여 : ' || myemp.sal);
dbms_output.put_line('커밋 : ' || myemp.comm);
dbms_output.put_line('부서번호 : ' || myemp.deptno);
end;
/
exec proemp(7839);
//////////////////////////////////////////////////////////////////
반복문
for문
FOR index_counter IN [REVERSE] lower_bound..upper_bound LOOP
statement1;
statement2;
. . . . . .
END LOOP;
while문
WHILE condition LOOP
statement1;
statement2;
. . . . . .
END LOOP;
loop문
LOOP
statement1;
statement2;
. . . . . .
EXIT [WHEN condition];
END LOOP;
//////////////////////////////////////////////////////////////////
-- 1부터 10까지의 합을 구하는 프로시저
create or replace procedure profor
is
tot number := 0;
begin
for i in 1..10 loop
tot := tot + i;
dbms_output.put_line('i = ' || i);
end loop;
dbms_output.put_line('tot = ' || tot);
end;
/
exec profor;
//////////////////////////////////////////////////////////////////
-- 시작수와 종착수를 입력받아서
-- 합을 구하는 프로시저
create or replace procedure profor(
st number,
ed number
)is
tot number := 0;
begin
for i in st..ed loop
tot := tot + i;
dbms_output.put_line('i = ' || i);
end loop;
dbms_output.put_line('tot = ' || tot);
end;
/
exec profor(4, 20);
//////////////////////////////////////////////////////////////////
-- 구구단 시작단부터 끝단 입력받아 출력
create or replace procedure profor(
st number,
ed number
)is
tot number := 0;
begin
for i in st..ed loop
for j in 1..9 loop
tot := i*j;
dbms_output.put_line(i || ' * ' || j || ' = ' || tot);
end loop;
end loop;
end;
/
exec profor(4,7);
//////////////////////////////////////////////////////////////////
goto문 사용법(알고만 있고 쓰지 않는 것을 권장)
goto aaa;
를 하면 <<aaaa>> 삽입되어진 위치로 이동되어 진다.
하지만 <<aaaa>>가 가장 end; 직전에 들어가면 오류가 발생한다
사용하지 말아야 하는 이유는
논리적인 구조로 제어라인을 벗어나야 하는데
goto문은 제어와 상관없이 강제적으로 구조를 벗어나게 되므로
논리적 에러를 유발시킬 수 있다. 또한 속도저하를 유발한다(?)
//////////////////////////////////////////////////////////////////
-- 1부터 10까지의 수의 합을 구하시오
(단, while문을 사용)
create or replace procedure prowhile
is
tot number := 0;
i number := 1;
begin
while i < 11 loop
tot := tot + i;
i:= i + 1;
end loop;
dbms_output.put_line('tot : ' || tot);
end;
/
exec prowhile;
//////////////////////////////////////////////////////////////////
-- 위와 동일한 1에서 10까지의 합을 loop문을 사용하여 구하시오
create or replace procedure proloop
is
tot number := 0;
i number := 1;
begin
loop
tot := tot + i;
i:= i + 1;
exit when i > 10;
end loop;
dbms_output.put_line('tot : ' || tot);
end;
/
exec proloop;
//////////////////////////////////////////////////////////////////
조합형 변수
PL/SQL Record – 구조체 형태
PL/SQL Table – 배열 형태
레코드 기본 문법
TYPE type_name IS RECORD
(field_name1 {scalar_datatype|record_type} [NOT NULL] [{:= | DEFAULT} expr],
(field_name2 {scalar_datatype|record_type} [NOT NULL] [{:= | DEFAULT} expr],
. . . . . . .);
identifiee_name type_name;
테이블 기본 문법
TYPE table_type_name IS TABLE OF
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
identifier table_type_name;
//////////////////////////////////////////////////////////////////
테이블 변수 만들기
number 형식의 myType이라는 타입을 생성하고 arr라는 배열 변수를 생성함.
create or replace procedure protable
is
type myType is table of number index by binary_integer;
arr myType;
begin
arr(1) := 30;
arr(2) := 60;
arr(3) := 50;
arr(4) := 80;
dbms_output.put_line('arr(2) : ' || arr(2));
dbms_output.put_line('arr 테이블의 개수 : ' || arr.count);
-- count를 반복문에 사용하면 유용함. 자바의 length
arr.delete(2);
if arr.exists(2) = false then
dbms_output.put_line('2번 테이블은 없다');
end if;
dbms_output.put_line('arr 테이블의 개수 : ' || arr.count);
end;
/
exec protable;
// 확인해보면 delete하면 count는 감소하지만 해당 배열은 빈 공란으로 남겨져 있다.
자동으로 데이터가 당겨지지 않는 것을 알 수 있으므로 주의하여
exists()를 사용하여 확인하여야 한다.
//////////////////////////////////////////////////////////////////
레코드 변수 만들기
-- 사원번호를 입력받아 이름, 급여, 부서코드, 업무만 출력하라.
create or replace procedure proEmpInfo(
eno emp.empno%type
)is
type myRec is record(
iname emp.ename%type,
isal emp.sal%type,
ideptno emp.deptno%type,
ijob emp.job%type
);
arr myRec;
begin
select ename, sal, deptno, job into arr
from emp
where empno = eno;
dbms_output.put_line('사원번호:'||eno);
dbms_output.put_line('이름:'||arr.iname);
dbms_output.put_line('급여:'||arr.isal);
dbms_output.put_line('부서코드:'|| arr.ideptno);
dbms_output.put_line('업무:'||arr.ijob);
end;
/
exec proEmpInfo(7521);
//////////////////////////////////////////////////////////////////
-- 이름, 나이, 주소를 가지는 레코드 형태를 만들고
-- 세명의 정보를 입력하고
-- for문을 활용하여 내용을 출력하는 프로시저
-- (단, 테이블 제외)
create or replace procedure proRecTable
is
type myRec is record(
name varchar2(10),
age number,
addr varchar2(50)
);
type myArr is table of myRec index by binary_integer;
arr myArr;
-- 레코드 배열 형태로 만듬
begin
arr(1).name := '홍길동';
arr(1).age := 20;
arr(1).addr := '안양';
arr(2).name := '박찬호';
arr(2).age := 30;
arr(2).addr := '미국';
arr(3).name := '차승원';
arr(3).age := 35;
arr(3).addr := '서울';
for i in 1..arr.count loop
dbms_output.put('번호 : ' || i);
dbms_output.put(', 이름 : ' || arr(i).name);
dbms_output.put(', 나이 : ' || arr(i).age);
dbms_output.put_line('주소 : ' || arr(i).addr);
end loop;
end;
/
exec proRecTable;
//////////////////////////////////////////////////////////////////
case when 문법(switch case문과 유사)
create table testa(
a varchar2(10)
);
insert into testa values('A');
insert into testa values('C');
insert into testa values('A');
insert into testa values('B');
insert into testa values('B');
insert into testa values('F');
insert into testa values('A');
select a, decode(a, 'A', '사과', 'B', '바나나', 'C', '배', '몰라') as "등급"
from testa;
select a, case a
when 'A', then '사과'
when 'A', then '바나나'
when 'A', then '배'
else '몰라'
end "등급"
from testa;
위와 같은 것을 아래로도 사용 가능함.
case
when d between 90 and 100 then '90-100'
when d between 80 and 89 then '80-89'
when d between 70 and 79 then '70-79'
when d between 60 and 69 then '60-69'
else '0-69'
end
//////////////////////////////////////////////////////////////////
프로시저 소스 보기
set heading off;
set linesize 300;
set pagesize 3000;
desc user_source;
select name, text from user_source;
select distinct name, type from user_source;
select text from usr_source where name = 'PROTEST';
//////////////////////////////////////////////////////////////////
함수(Function)
기본 문법
CREATE OR REPLACE FUNCTION function name
[(argument...)]
RETURN datatype
IS
[변수 선언 부분]
BEGIN
[PL/SQL Block]
-- 블록에는 적어도 한 개의 RETURN 문이 있어야 한다.
-- 블록은 함수가 수행할 내용을 정의한 몸체부분.
END;
//////////////////////////////////////////////////////////////////////
--사원의 번호를 입력받아 그 사원의 급여를 반환하는 함수를 생성하라.
create or replace function funsal(
num emp.empno%type
)return emp.sal%type
is
isal emp.sal%type;
begin
select sal into isal from emp where num = empno;
dbms_output.put_line('정상적으로 검색됨');
return isal;
end;
/
variable aaa number;
exec :aaa := funsal(7521);
print aaa;
--프로시저 안에서 함수 호출하기
create or replace procedure samIT
is
isal emp.empno%type;
begin
isal := funsal(7521);
dbms_output.put_line('검색된 급여' || isal);
end;
/
exec samIT;
//////////////////////////////////////////////////////////////////////
설문조사 테이블(객관식)
당신이 좋아하는 계절은?
1.봄 2.여름 3.가을 4.겨울
당신이 좋아하는 과일
1.사과 2.배 3.복숭아
-- 설문에 사용할 질문 테이블
CREATE TABLE reserchT(
ino NUMBER PRIMARY KEY,
iquest VARCHAR(70) not null,
idate DATE DEFAULT sysdate,
sdate DATE,
edate DATE
);
-- 설문 개수 증가
CREATE SEQUENCE res_seq start with 1 increment by 1;
-- 설문 추가
insert into reserchT values(
res_seq.nextval, '당신이 좋아하는 과일은?', sysdate,
to_date('2009/05/22', 'YYYY/MM/DD'),
to_date('2009/05/30', 'YYYY/MM/DD')
);
-- 설문에 사용될 보기 테이블
create table reserchA(
ano number primary key,
atext varchar2(30) not null,
ino number,
constraint res_ino_fk foreign key(ino) references reserchT(ino)
);
-- 보기 개수
create sequence a_seq start with 1 increment by 1;
-- 보기 추가
insert into reserchA values(
a_seq.nextval, '사과', (select max(ino) from reserchT));
insert into reserchA values(
a_seq.nextval, '배', (select max(ino) from reserchT));
insert into reserchA values(
a_seq.nextval, '복숭아', (select max(ino) from reserchT));
-- max() 함수 말고 currval을 사용하여도 되지만 자바하고 연동시에 정상적인 값이 나오지 않을 수 있으므로 max() 함수를 이용할 수도 있다.
select * from reserchT;
-- 설문 뽑아오기
select t.ino, iquest, atext
from reserchT t, reserchA a
where t.ino = a.ino;
--투표 테이블
create table reserchR(
rno number primary key,
ino number,
ano number,
idate date default sysdate,
constraint r_ino_fk foreign key (ino) references reserchT(ino),
constraint r_ano_fk foreign key (ano) references reserchA(ano)
);
-- 투표한 사람 번호 증가
create sequence r_seq start with 1 increment by 1;
-- 투표
insert into reserchR values(r_seq.nextval, 1, 1, sysdate);
insert into reserchR values(r_seq.nextval, 1, 2, sysdate);
insert into reserchR values(r_seq.nextval, 1, 2, sysdate);
insert into reserchR values(r_seq.nextval, 1, 1, sysdate);
insert into reserchR values(r_seq.nextval, 1, 3, sysdate);
insert into reserchR values(r_seq.nextval, 1, 1, sysdate);
--1번 설문 투표 결과 확인(ano는 보기 번호, count는 투표 인원)
select ano, count(*)
from reserchR
where ino = 1
group by ano
order by ano;
-- 마지막 설문 투표 결과 확인
select ano, count(*)
from reserchR
where ino = (select max(ino) from reserchT)
group by ano
order by ano;
-- 지금까지 만든거 만든거 삭제
drop table reserchA;
drop table reserchT;
drop table reserchR;
drop sequence a_seq;
drop sequence res_seq;
drop sequence r_seq;
//////////////////////////////////////////////////////////////////////
테이블 만들때 아래와 같이 분리하여 만드는 것을 권장
1. 번호, 이름, 아이디, 비밀번호
2. 일련번호, 번호, 취미, 주소, 우편번호, 성별, 전화번호, 언어, 기타등등
1번은 자주 사용하는 컬럼이다. 자주 로그인하고 글쓰기를 하게 되면 자주 db를 사용하게 된다. 2번은 자주 사용하지는 않지만 관련이 있는 컬럼이다. 두 테이블을 하나로 합쳐서 사용하다보면, 로그인만 하는데도 기타 관련 컬럼들까지 같이 들어 있으므로 빈번한 db 사용시 효율적이지 못하다.
//////////////////////////////////////////////////////////////////////
예외처리
종류
정의된 ORACLE SERVER ERROR
정의되지 않은 ORACLE SERVER ERROR
사용자 정의 ERROR
기본 구조
BEGIN
EXCEPTION
WHEN exception1 [OR exception2, . . . .] THEN
statement1;
statement2;
. . . . . .
[WHEN exception2 [OR exception3, . . . .] THEN
statement3;
statement4;
. . . . . .]
[WHEN OTHERS THEN
statement5;
statement6;
. . . . . .]
END;
--정의되지 않은 예외
DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name,
error_number);
--사용자 정의 예외
DECLARE
exception_name EXCEPTION;
BEGIN
…
RAISE exception_name;
Exception ~~
END
-- RAISE는 자바의 throws와 같은 의미(예외를 발생시키는 것)
에러 함수
SQLCODE : 에러 코드에 대한 숫자를 RETURN한다.
SQLERRM : 에러 번호에 해당하는 MESSAGE를 RETURN한다.
SQL CODE값
0 : 예외가 없습니다.(NO ERROR)
1 : 사용자 정의 ERROR NUMBER
+100 : NO_DATA_FOUND 예외 -- 데이터가 없을 경우
양의 정수 : 표준 에러 번호
//////////////////////////////////////////////////////////////////////
EXCEPTION에 주로 사용되는 것들
-- NO_DATA_FOUND : 레코드가 없습니다.
-- TOO_MANY_ROWS : 반환 레코드가 여러줄임
-- OTHERS : 나머지 모든 경우
-- ZERO_DIVIDE : 0으로 나누어서 발생한 오류
//////////////////////////////////////////////////////////////////////
사원번호를 검색하여 그 이름과 급여를 출력하라
(단, 사원번호가 검색되지 않으면 예외처리하라)
CREATE or replace procedure proSearch(
num emp.empno%type
)
is
type myRec is record(
iname emp.ename%type,
isal emp.sal%type
);
idata myRec;
begin
select ename, sal into idata.iname, idata.isal from emp
where empno = num;
dbms_output.put_line('사원번호 : ' || num);
dbms_output.put_line('이름 : ' || idata.iname);
dbms_output.put_line('급여 : ' || idata.isal);
Exception
when NO_DATA_FOUND then
dbms_output.put_line('사원번호 : ' || num || '은 없습니다.');
when TOO_MANY_ROWS then
dbms_output.put_line('검색되어 반환되어진 레코드가 여러 개 입니다.');
when OTHERS then
dbms_output.put_line('기타 오류가 발생했습니다.');
end;
/
exec proSearch(7521);
exec proSearch(100);
//////////////////////////////////////////////////////////////////////
사용자 정의 익셉션 만들기
CREATE or replace procedure proSearch(
num emp.empno%type
)
is
type myRec is record(
iname emp.ename%type,
isal emp.sal%type
);
idata myRec;
myException EXCEPTION;
begin
if num < 1000 or num > 9000 then
RAISE myException;
end if;
select ename, sal into idata from emp
where empno = num;
Exception
when myException then
dbms_output.put_line('사용자 정의 오류 : 범위 입력에서 문제가 발생했습니다.');
end;
/
exec proSearch(9999);
-- 범위에서 어긋나므로 사용자 정의 예외가 발생하게 된다.
//////////////////////////////////////////////////////////////////////
커서(Cursor)
오라클 서버에 의해 실행되는 모든 SQL문은 연관된 각각의 커서(오라클이 SQL문장을 실행시키기 위한 공간)를 소유
암묵적(암시적) 커서
SQL문장이 처리되는 곳에 대한 익명의 address
속성
SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수
SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 1개 이상일 경우 TRUE
SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE
SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색
(암시적 커서는 SQL 문이 실행되는 순간 자동으로 열림과 닫힘 실행)
명시적 커서
프로그래머에 의해 선언되며 이름이 있는 커서
커서 선언
Cursor cursor_name is SELETE 구문
커서 open
Open cursor_name
커서 패치
Fetch cursor_name is var1, var2, …
커서 닫기
Close cursor_name
속성
cursor_name%ISOPEN -커서가 OPEN되어 있으면 TRUE
cursor_name%NOTFOUND - 패치한 데이터가 행을 반환하지 않으면 TRUE
cursor_name%FOUND - 패치한 데이터가 행을 반환하면 TRUE
cursor_name%ROWCOUNT - 현재까지 반환된 모든 데이터 행의 수
//////////////////////////////////////////////////////////////////////
암묵적 커서 예제 1)
create table TestIT as select * from emp;
create or replace procedure proCur
is
cnt number;
begin
update testIT set sal = sal*1.2 where deptno = 20;
cnt := sql%rowcount;
dbms_output.put_line('수정된 레코드는 ' || cnt || '개입니다.');
end;
/
exec proCur;
암묵적 커서 예제 2)
create table TestIT as select * from emp;
create or replace procedure proCur(
num emp.empno%type
)is
cnt number;
iname emp.ename%type;
isal emp.sal%type;
begin
select ename, sal into iname, isal from emp
where empno = num;
if sql%found then
dbms_output.put_line('이름 : ' || iname || '급여 : ' || isal);
end if;
end;
/
exec proCur(7521);
//////////////////////////////////////////////////////////////////////
명시적 커서 예제 1)
-- 부서코드가 20인 사람들의 정보를 출력하는 프로시서 생성
create or replace procedure proCurA(
dnum emp.deptno%type
)
is
iname emp.ename%type;
isal emp.sal%type;
ideptno emp.deptno%type;
-- 커서의 선언
cursor myCur is select ename, sal, deptno from emp where deptno = dnum;
begin
for ia in myCur loop
dbms_output.put_line('이름 : ' || ia.ename);
dbms_output.put_line('급여 : ' || ia.sal);
dbms_output.put_line('부서코드 : ' || ia.deptno);
end loop;
/* // loop문보다 for문이 더 간결하므로 주로 사용된다.
-- 커서의 open
-open myCur;
--Fetch 실행
loop
fetch myCur into iname, isal, ideptno;
exit when myCur%notfound;
dbms_output.put_line('이름 : ' || iname);
dbms_output.put_line('급여 : ' || isal);
dbms_output.put_line('부서코드 : ' || ideptno);
end loop;
close myCur;
*/
end;
/
--부서코드를 입력받아서 출력
exec proCurA;
exec proCurA(30);
명시적 커서 예제 2)
create or replace procedure proCurB
is
iname emp.ename%type;
isal emp.sal%type;
ideptno emp.deptno%type;
-- 커서의 선언
cursor myCur(dnum emp.deptno%type) is select ename, sal, deptno from emp where deptno = dnum;
begin
dbms(output.put_line('10번 부서 코드 직원만 출력');
for ia in myCur(10) loop
dbms_output.put_line('이름 : ' || ia.ename);
dbms_output.put_line('급여 : ' || ia.sal);
dbms_output.put_line('부서코드 : ' || ia.deptno);
end loop;
end;
/
exec proCurB;
//////////////////////////////////////////////////////////////////////
-- 부서코드를 입력받아 사람의 이름과, 급여와 업무를 출력하고
-- 그 부서의 급여 합계와 평균을 출력하라
-- (단, 커서와 레코드 테이블을 이용하라)
create or replace procedure proDept(
dnum emp.deptno%type
)
is
type myRec is record(
iname emp.ename%type,
isal emp.sal%type,
ijob emp.job%type
);
type myTab is table of myRec index by binary_integer;
arr myTab;
cursor myCur is select ename, sal, job from emp where deptno = dnum;
cnt number := 0;
tot number := 0;
avr number := 0;
begin
for myi in myCur loop
cnt := cnt + 1;
arr(cnt).iname := myi.ename;
arr(cnt).isal := myi.sal;
arr(cnt).ijob := myi.job;
end loop;
for i in 1..cnt loop
tot := tot + nvl(arr(i).isal, 0);
end loop;
avr := round(tot / cnt, 0);
dbms_output.put_line('부서번호 : ' || dnum || '정보 출력');
dbms_output.put_line('--------------------------------------------');
dbms_output.put_line(' 이 름 급여 업무 ');
dbms_output.put_line('--------------------------------------------');
for i in 1..cnt loop
dbms_output.put(arr(i).iname || ' ');
dbms_output.put(arr(i).isal || ' ');
dbms_output.put_line(arr(i).ijob);
end loop;
dbms_output.put_line('--------------------------------------------');
dbms_output.put_line('총원 : ' || cnt || ' 명');
dbms_output.put_line('급여합계 : ' || to_char(tot, '999,999') || ' 원');
dbms_output.put_line('급여평균 : ' || to_char(avr, '999,999') || ' 원');
dbms_output.put_line('--------------------------------------------');
end;
/
exec proDept(30);
//////////////////////////////////////////////////////////////////////
package
1. 패키지 선언
2. 패키지 바디 구현
-- 사원번호 검색 프로시저
-- 부서코드를 입력받아 합계를 반환하는 함수
-- 사원번호와 급여를 입력받아 사원번호의 급여를 수정하는 프로시저
-- 사용하게 될 테이블 생성
create table TestIT1 as select * from emp;
-- 패키지 선언 부분
create or replace package packTest
as
procedure searchSawon(num TestIT1.empno%type);
function funSum(dnum TestIT1.deptno%type) return number;
procedure proUpdate(num TestIT1.empno%type, isal TestIT1.sal%type);
end;
/
-- 패키지 바디 구현 부분
create or replace package body packTest
as
-- 사원번호 검색 프로시저
procedure searchSawon(num TestIT1.empno%type)
is
iname TestIT1.ename%type;
isal TestIT1.sal%type;
ijob TestIT1.job%type;
begin
dbms_output.put_line('패키지 내부의 검색 실행 ');
select ename, sal, job into iname, isal, ijob
from TestIT1
where empno = num;
dbms_output.put_line('사원번호 : ' || num);
dbms_output.put_line('이름 : ' || iname);
dbms_output.put_line('급여 : ' || isal);
dbms_output.put_line('업무 : ' || ijob);
end;
--부서코드를 입력받아 합계를 반환하는 함수
function funSum(dnum TestIT1.deptno%type) return number
is
tot number;
begin
select sum(sal) into tot from TestIT1 where deptno = dnum;
dbms_output.put_line('패키지 내부의 함수 실행 ');
dbms_output.put_line(dnum || ' 부서의 합 ' || tot);
return tot;
end;
-- 사원번호와 급여를 입력받아 사원번호의 급여를 수정하는 프로시저
procedure proUpdate(num TestIT1.empno%type,
isal TestIT1.sal%type)
is
begin
dbms_output.put_line('패키지 내부의 수정 프로시저 실행 ');
update TestIT1 set sal = isal where empno = num;
if sql%rowcount > 0 then
dbms_output.put_line('레코드 정상 수정 ');
end if;
end;
end;
/
-- 확인
exec packTest.searchSawon(7521);
variable data number;
exec :data := packTest.funSum(30);
print data;
exec packTest.proUpdate(7521, 4000);
///////////////////////////////////////////////////////////////////
Trigger
어떤 이벤트(insert, update, delete)가 발생하면 자동적인 작업을 진행할 수 있도록 하는 PL/SQL 문법
그러나 트리거라는 것이 있고 이러한 사용법이라는 것을 알아두어야 하겠지만, 되도록 사용을 최소화하고 프로시저를 이용하도록 한다.
기본 문법)
create or replace trigger [triger_name]
befre / after -- 둘 중 하나 선택, insert가 일어나기 전에, 일어난 후에
insert or update on [table_name] -- table에 insert나 update 작업을 하게 되면
for each row -- 매 행마다 실행
-- after에서만 사용 가능한 변수
-- :new : trigger에서 제공되는 새로운 값 변수 :예) new.sal
-- :old : trigerr에서 제공되는 예전 값 변수 :예) old.sal
begin
dbms_output.put_line('레코드가 등록되거나 수정되었습니다.');
end;
/
trigger 사용 예제)
set serveroutput on;
-- 트리거에서 사용할 테이블 생성
create table triemp as select empno, ename, sal, deptno from emp;
-- 트리거 생성
create or replace trigger mytrigger
after
insert or update on triemp
for each row
begin
dbms_output.put_line('trigger : 레코드가 등록되거나 수정되었습니다.');
dbms_output.put_line('예전 급여 : ' || :old.sal);
dbms_output.put_line('새로운 급여 : ' || :new.sal);
end;
/
-- 트리거 확인
insert into triemp values(1234, 'Aaaa', 3000, 30);
update triemp set sal = sal*1.2 where deptno = 30;
트리거의 비 활성화 / 비활성화
alter trigger [trigger_name] disable; -- enable
not as usual