-- 중첩 테이블 : 중첩 테이블은 varray 와 흡사하지만 중첩 테이블은 선언시에 전체 크기를 명시할 필요가 없음
declare
type nested_test is table of varchar2(10);
--위에서 선언한 nested_test 타입 변수
nested1 nested_test;
begin
--중첩 테이블에 데이터 저장
nested1:=nested_test('A','B','C','D');
dbms_output.put_line(nested1(2));
end;
-- Associative array(index-by table) 연관배열 : 키와 값의 쌍으로 구성된 컬렉션
declare
type assoc_array_num_type is table of number index by pls_integer;
--key는 pls_integer 형이며, value는 number형인 요소들로 구성
--[주의]key에 자료형을 명시할 때 integer로 명시하면 오류 발생
assoc1 assoc_array_num_type;
begin
--key는 3, value는 33 저장
assoc1(3):=33;
dbms_output.put_line(assoc1(3));
end;
declare
type assoc_array_str_type2 is table of varchar2(32) index by varchar2(64);
assoc3 assoc_array_str_type2;
begin
assoc3('K'):='KOREA';
dbms_output.put_line(assoc3('K'));
end;
-- 콜렉션을 데이터베이스 객체로 생성
create type alphabet_type as varray(26) of varchar2(2);
-- 데이터베이스 객체로 생성된 콜렉션을 호출함
declare
test_alph alphabet_type;
begin
test_alph:=alphabet_type('A','B','C','D');
dbms_output.put_line(test_alph(1));
end;
-- 레코드 : 각기 다른 데이터 타입을 가질 수 있는 구조 ,프로그래밍 언어와 비교하면 구조체 또는 자바 클래스에 멤버변수만 명시한 형태
declare
type record1 is record(deptno number not null:=50,
dname varchar2(14),
loc varchar2(13)
);
--위에 선언한 record1을 받는 변수 선언
rec1 record1;
begin
rec1.dname:='레코드';
rec1.loc:='SEOUL';
--rec1 레코드 값을 dept 테이블에 insert
INSERT INTO dept VALUES rec1;
--insert 작업이 정상적으로 수행
COMMIT;
exception when others then
Rollback;
end;
-- if 문
declare
grade char(1);
begin
grade:='B';
if grade='A' then
dbms_output.put_line('Excellent');
elsif grade='B' then
dbms_output.put_line('Good');
elsif grade='C' then
dbms_output.put_line('Fair');
elsif grade='D' then
dbms_output.put_line('Poor');
end if;
end;
-- case 문
declare
grade char(1);
begin
grade:='B';
case grade
when 'A' then
dbms_output.put_line('Excellent');
when 'B' then
dbms_output.put_line('Good');
when 'C' then
dbms_output.put_line('Fair');
when 'D' then
dbms_output.put_line('Poor');
else
dbms_output.put_line('Not Found');
end case;
end;
-- loop문
declare
test_number integer;
result_num integer;
begin
test_number:=1;
loop
result_num:=2*test_number;
if result_num > 20 then
exit; --블록 종료
else
dbms_output.put_line(result_num);
end if;
test_number:=test_number+1;
end loop;
end;
declare
test_number integer;
result_num integer;
begin
test_number:=1;
loop
result_num:=2*test_number;
--exit when 조건
exit when result_num>20;
dbms_output.put_line(result_num);
test_number:=test_number + 1;
end loop;
end;
-- while-loop문
declare
test_number integer;
result_num integer;
begin
test_number:=1;
result_num:=0;
while result_num<20 loop
result_num:=2*test_number;
dbms_output.put_line(result_num);
test_number:=test_number+1;
end loop;
end;
-- for ... loop 문
declare
test_number integer;
result_num integer;
begin
test_number:=1;
result_num:=0;
for test_number in 1..10 loop
result_num:=2*test_number;
dbms_output.put_line(result_num);
end loop;
end;
----------------------------------------------------
declare
test_number integer;
result_num integer;
begin
test_number:=1;
result_num:=0;
for test_number in reverse 1..10 loop
result_num:=2*test_number;
dbms_output.put_line(result_num);
end loop;
end;
-- ★ 커서 : SELECT 문장을 실행하면 조건에 따라 결과가 추출되는데 결과 집합에 접근하기 위해서 커서(CURSOR) 사용
declare
cursor emp_csr is
SELECT empno
FROM emp
WHERE deptno=10;
emp_no emp.empno%type;
begin
open emp_csr;
loop
fetch emp_csr into emp_no;
--%notfound : 커서에서만 사용 가능한 속성
--더 이상 패치(할당)할 로우(행)가 없음을 의미
exit when emp_csr%notfound;
dbms_output.put_line(emp_no);
end loop;
close emp_csr;
end;
-- PL/SQL 서브프로그램
--데이터베이스 객체로 저장해서 필요할 때마다 호출할 수 있는 형태
--프로시저, 함수
-- 함수
--입력받은 값으로부터 10%의 세율을 얻는 함수
create or replace function tax(p_value in number) --in은 생략가능
return number
is
begin
return p_value*0.1;
end;
SELECT TAX(100) FROM dual;
SELECT ename,sal,TAX(sal) tax FROM emp;
--급여와 커미션을 합쳐서 세금 계산
create or replace function tax2(p_sal in emp.sal%type,
p_bonus emp.comm%type)
return number
is
begin
return (p_sal + nvl(p_bonus,0))*0.1;
end;
SELECT empno,ename,sal,comm,TAX2(sal,comm) AS tax FROM emp;
--급여(보너스 포함)에 대한 세율을 다음과 같이 정의함.
--급여가 월 $1,000보다 적으면 세율을 5% 적용하며,
--$1,000이상 $2,000이하면 10%, $2,000을 초과하면 20%를 적용함
create or replace function tax3(
p_sal in emp.sal%type,
p_bonus emp.comm%type)
return number
is
l_sum number;
l_tax number;
begin
l_sum:=p_sal+nvl(p_bonus,0);
if l_sum<1000 then
l_tax:=l_sum*0.05;
elsif l_sum<=2000 then
l_tax:=l_sum*0.1;
else
l_tax:=l_sum*0.2;
end if;
return l_tax;
end;
SELECT empno,ename,sal,comm,TAX3(sal,comm) as tax FROM emp;
--사원번호를 통해서 급여를 알려주는 함수
create or replace function emp_salaries(emp_no number)
return number is
nSalaries number(9);
begin
nSalaries:=0;
SELECT sal
INTO nSalaries
FROM emp
WHERE empno=emp_no;
return nSalaries;
end;
SELECT emp_salaries(7698) FROM dual;
--부서번호를 전달하면 부서명을 구할 수 있는 함수
create or replace function get_dept_name(dept_no number)
return varchar2 is
sDeptName varchar2(30);
begin
SELECT dname
INTO sDeptName
FROM dept
WHERE deptno=dept_no;
return sDeptName;
end;
SELECT GET_DEPT_NAME(10) FROM dual;
SELECT empno,ename,sal,GET_DEPT_NAME(deptno) "Department Name" FROM emp;
-- 생성된 함수 확인하기
SELECT object_name,object_type FROM user_objects Where object_type='FUNCTION';
-- 작성된 함수의 소스 코드 확인
SELECT text FROM user_source WHERE type='FUNCTION' AND name='TAX';
--<실습문제>
--1)두 숫자를 제공하면 덧셈을 해서 결과값을 반환하는 함수를 정의(add_num)
create or replace function add_num(
num1 integer,
num2 integer)
return integer
is
begin
return num1+num2;
end;
SELECT ADD_NUM(5,6) FROM dual;
SELECT ename, ADD_NUM(sal,NVL(comm,0)) 실급여
FROM emp;
--2)부서번호를 입력하면 해당 부서에서 근무하는 사원 수를 반환하는 함수를 정의
create or replace function get_emp_count(
dept_no emp.deptno%type)
return integer
is
emp_count integer;
begin
SELECT COUNT(*) count
INTO emp_count
FROM emp
WHERE deptno=dept_no;
return emp_count;
end;
SELECT deptno,get_emp_count(deptno) FROM dept;
--3)emp테이블의 입사일을 입력하면 근무연차를 구하는 함수를 정의하시오.(소수점 자리는 절삭)
create or replace function get_info_hiredate(
hire_date emp.hiredate%type)
return integer
is
begin
return TRUNC(MONTHS_BETWEEN(SYSDATE,hire_date)/12);
end;
SELECT ename,get_info_hiredate(hiredate) FROM emp;
--4)emp테이블을 이용해서 사원번호를 입력하면 사원의 관리자 이름을 구하는 함수를 정의
create or replace function get_mgr_name(
emp_no emp.empno%type)
return varchar2
is
m_name varchar2(10);
begin
SELECT ename
INTO m_name
FROM emp
WHERE empno=(SELECT mgr
FROM emp
WHERE empno=emp_no);
return m_name;
end;
SELECT empno,ename,mgr 관리자번호,get_mgr_name(empno) 관리자이름 FROM emp;
--5)emp테이블을 이용해서 사원번호를 입력하면 급여등급을 구하는 함수를 정의
①
create or replace function get_sal_grade(
emp_no emp.empno%type)
return char
is
sgrade char(1);
begin
SELECT CASE WHEN sal>=4000 AND sal<=5000 THEN 'A'
WHEN sal>=3000 AND sal<4000 THEN 'B'
WHEN sal>=2000 AND sal<3000 THEN 'C'
WHEN sal>=1000 AND sal<2000 THEN 'D'
ELSE 'F'
END grade
INTO sgrade
FROM emp
WHERE empno=emp_no;
return sgrade;
end;
SELECT ename,sal,get_sal_grade(empno) 등급 FROM emp;
②
create or replace function get_sal_grade2(
emp_no emp.empno%type)
return integer
is
sgrade integer;
begin
SELECT s.grade
INTO sgrade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND e.empno=emp_no;
return sgrade;
end;
SELECT ename,sal,GET_SAL_GRADE2(empno) 등급 FROM emp;
