8. PL-SQL개요및 작성법
– 기존의 SQL/Plus, SQL Developer
SQL+프로그램적 요소(변수의 자료형,제어문, 프로시저,함수사용)
SQL> declare
a number;
b number;
c number;
begin
a:=12;
b:=11;
c:=a+b;
end;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
<문제1> PL-SQL구문을 연습
SQL> set serveroutput on –> 출력하기 처음에 한번씀
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 declare
2 a number;
3 b number;
4 c number;
5 begin
6 a:=12;
7 b:=11;
8 c:=a+b;
9 — System.out.println(“<result c>” + c);
10 dbms_output.put_line(‘<result c>’);
11 dbms_output.put_line(c);
12* end;
SQL> /
<result c>
23
PL/SQL 처리가 정상적으로 완료되었습니다.
— SQL Developer툴을 사용 —
C:\jdk1.8\bin\java.exe
실행화면
파일 타입 설정
접속:test
사용자이름:scott
비빌번호:tiger
호스트이름:localhost
포트:1521
SID:orcl로 설정
테스트버튼 클릭->성공
글꼴설정
=============================
<문제2> 산술연산자를 이용해서 출력
begin
dbms_output.put_line(2*2);/* 4 */
dbms_output.put_line(81/9);/* 9 */
dbms_output.put_line(3+3);/* 6 */
dbms_output.put_line(12-7);/* 5 */
dbms_output.put_line(2**3);/* 8 */
end;
/
실행화면
4
9
6
5
8
============================
<문제3> select~into구문을 이용해서 불러오기
Ex) 부서명이 ACCOUNTING인 부서번호,위치?
declare
v_deptno NUMBER; — 변수선언
v_loc VARCHAR2(20); — 문자저장
begin
select deptno, loc
into v_deptno, v_loc
from dept
where dname=’ACCOUNTING';
–출력
dbms_output.put_line(‘부서번호:’|| v_deptno); — || -> +
dbms_output.put_line(‘부서위치:’|| v_loc);
end;
/
실행결과
부서번호:10
부서위치:NEW YORK
<문제4> %Type을 배워보자
Ex) 사번 7788인 사원에 대한 부서번호에 따른 부서명을 구하기
declare
– %type=>필드의 자료형을 정확히 모를때
– 테이블명.필드명%TYPE
v_deptno emp.deptno%TYPE; –number
v_ename emp.ename%TYPE; –VARCHAR2(20);
v_dept_name VARCHAR2(50); –emp테이블에 dname
begin
select deptno, ename
into v_deptno, v_ename
from emp
where empno=7788;
— 제어문
if (v_deptno=10) then
v_dept_name:=’ACCOUNTING';
elsif(v_deptno=20) then
v_dept_name:=’RESEARCH';
elsif(v_deptno=30) then
v_dept_name:=’SALES';
elsif(v_deptno=40) then
v_dept_name:=’OPERATION';
else
v_dept_name:=’NONE';
end if;
–출력
dbms_output.put_line(‘부서명은 ‘ || v_dept_name);
end;
/
실행화면
부서명은 RESEARCH
=============
<문제5> 반복문->for문
Ex) 숫자 1~9까지의 각각의 제곱수를 구하는 프로그램작성
begin
— for(int i=1;i<=9;i++)
— for 출력변수 in 초기값..종류값 loop
— i+”*”+”=”+(i*i)
for i in 1..9 loop
dbms_output.put_line(i || ‘*’ || i ||’=’ ||(i*i));
end loop;
end;
/
실행화면
1*1=1
2*2=4
3*3=9
4*4=16
5*5=25
6*6=36
7*7=49
8*8=64
9*9=81
=============
<문제6> 반복문- loop – end loop;(반복할 횟수가 정해져 있지 않은 경우)
— [exit when 조건식]=> 빠져나갈 조건
— 출력할 변수를 따로 선언
— 테이블에 데이터를 입력시켜주는 프로그램 작성
declare
v_counter number:=1;
begin
loop
— 실행구문
insert into b_dept values(90+ v_counter, ‘총무’, ‘서울’);
v_counter:=v_counter+10;
exit when v_counter > 5;
end loop;
commit; — rollback;
end;
/
실행후 결과값
SQL> select * from b_dept;
DEPTNO DNAME LOC
———- ——————- ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
80 IMSI Inchon
90 IMSI2 SEOUL
91 총무 서울
60 Testing Busan
70 Testing2 SEOUL
50 SUPPORT Kwamgju
<문제7> %Type(필드하나),%ROWTYPE(행하나), Record(행이상)
— Record=> 구조체(서로 다른 자료형을 하나로 묶어준형태)
declare
— type 레코드변수명 is record(구성요소~);
TYPE emp_record IS RECORD — class emp_record (
( v_empno number, — int v_empno;
v_ename varchar2(30), — String v_ename;
v_hiredate date — Timestamp v_hiredate);
);
— 레코드변수명 레코드자료형;
emp_rec emp_record; — emp_record emp_rec=new emp_record();
begin
select empno, ename, hiredate
into emp_rec.v_empno, emp_rec.v_ename, emp_rec.v_hiredate
from emp
where empno=&p_empno;
— 출력
dbms_output.put_line(emp_rec.v_empno);
dbms_output.put_line(emp_rec.v_ename);
dbms_output.put_line(emp_rec.v_hiredate);
end;
/
실행결과
7902
FORD
81/12/03
<문제8> %ROWTYPE을 이용
Ex) 사번을 입력받아서 사원테이블의 사번, 이름, 입사일자출력?
declare
— 행하나 저장
emp_record emp%ROWTYPE; –테이블명%ROWTYPE;
begin
select *
into emp_record
from emp
where empno=7788;
— 출력
dbms_output.put_line(‘사번:’ || emp_record.empno);
dbms_output.put_line(‘이름:’ || emp_record.ename);
dbms_output.put_line(‘입사일:’ || emp_record.hiredate);
end;
/
실행결과
사번:7788
이름:SCOTT
입사일:87/04/19
<문제9> 커서를 이용해서 사원 10명의 사번와 이름순으로 출력하시오
필드(%type) 행(%RowType) or Record(원하는 필드만구성) , 커서(행하나 이상을 메모리상에 저장)
declare
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
1. 커서를 선언 cursor 커서이름 is
— select~
CURSOR mycursor is
select empno, ename
from emp
order by 1;
begin
2.open 커서명; (데이터를 불러오기위한 준비단계)
open mycursor;
— 출력구문
dbms_output.put_line(‘사번——–이름’);
for i in 1..10 loop
3. FETCH 커서명(레크드를 1개씩 읽어들이는 기능)
FETCH mycursor
into v_empno, v_ename;
dbms_output.put_line(v_empno || ‘—‘ || v_ename);
end loop;
4.커서 종료->메모리 해제
CLOSE mycursor; — CLOSE 닫을 커서명;
end;
/
실행결과
사번——–이름
7369—SMITH
7499—ALLEN
7521—WARD
7566—JONES
7654—MARTIN
7698—BLAKE
7782—CLARK
7788—SCOTT
7839—KING
7844—TURNER
<문제10> 부서번호가 30번인 사원들의 이름과 급여를 조회?
(단 where조건절을 사용하지 말것. 커서(emp_cursor)를 이용할것)
declare
— 1.커서선언
CURSOR emp_cursor IS
select deptno,ename,sal
from emp;
begin
— 2.커서를 불러오자
— for 단수 in 복수 loop
for item in emp_cursor loop
— 조건(부서번호가 30번)
if item.deptno=30 then
dbms_output.put_line(‘이름:’|| item.ename);
dbms_output.put_line(‘급여:’|| item.sal);
end if;
end loop;
end;
/
실행결과
이름:ALLEN
급여:1600
이름:WARD
급여:1250
이름:MARTIN
급여:1250
이름:BLAKE
급여:2850
이름:TURNER
급여:1500
이름:JAMES
급여:950
<문제11> 부서번호가 10인 사원들의 이름과 급여를 조회?
(단 서브쿼리를 사용하는 커서 for루프를 이용하시오.)
select ename,sal from emp where deptno=10; –> SQL문제
begin
— for 단수 in 복수 loop
— end loop
for item in (select ename,sal from emp where deptno=10) loop
dbms_output.put_line(item.ename || ‘—‘ || item.sal);
end loop;
end;
/
실행결과
CLARK—2450
KING—5000
MILLER—1300
<문제12> while문을 이용해서 b_dept테이블에 5건의 행을 입력하시오
/* while 조건식 loop
실행문
end loop;
*/
declare
v_max dept.deptno%TYPE; — 부서번호 최대값
v_counter number:=1;
begin
select max(deptno)
into v_max
from b_dept;
while v_counter < 6 loop
insert into b_dept
values(v_max+ v_counter, ‘영업부’,’부산’);
v_counter := v_counter + 1;
end loop;
end;
/
실행결과 deptno-> 92,93,94,95,96 등록
<문제13> 프로시저(=메서드) 작성법-> 반환값이 없다.
Ex) 사원번호가 7902번인 사원번호를 외부에서 입력받고 급여를 10%인상 시키는 프로시저를 작성하시오.
select sal from emp
where empno=7902; –sal =3000
/*
create or replace procedure 작성할 프로시저명(
매개변수 in 자료형(%TYPE,number,varchar2,,,),,
)
IS
begin
실행구문
end;
/
*/
create or replace procedure myProcedure
(
pid in emp.empno%TYPE — public void myProcedure(int pid){}
)
IS
begin
update emp set sal=1.1*sal
where empno=pid;
end;
/
실행결과
procedure myProcedure이(가) 컴파일되었습니다.
execute myprocedure(7902)
select sal from emp where empno=7902;
실행결과
sal
——
3300
<문제14> 함수사용하기-> 매개변수 0, 반환값 0
Ex) 부서번호를 입력받아서 부서급여의 총금액을 구해주는 함수를 작성하시오
select deptno, sum(sal)
from emp
group by deptno;
/*
create or replace function 작성할 함수명(
매개변수 in 자료형 –public 자료형 메서드명(~){
return 자료형변수
–}
)
return 자료형
is
begin
end;
/
*/
create or replace function myFunction
(
pdeptno in emp.deptno%TYPE
)
return NUMBER
is
— 변수가 필요하면 이부분에 선언
v_sum number;
begin
select sum(sal)
into v_sum
from emp
where deptno=pdeptno;
return v_sum; — return 결과값;
end;
/
실행결과
function myFunction이(가) 컴파일되었습니다.
select myfunction(30) from dual;
MYFUNCTION(30)
——————-
9400
<문제15> emp테이블에서 모든 사원의 평균급여를 구해주는 함수를 작성하시오
(getAvgSalary)
create or replace function getAvgSalary
return NUMBER
is
v_avg NUMBER:=0 — 평균값을 저장할 변수
begin
select avg(sal)
into v_avg
from emp;
return v_avg;
end;
/
실행결과
function getAvgSalary이(가) 컴파일되었습니다.
select round(getAvgSalary) from dual;
Round(getavgsalary)
———————-
2116
<문제16> getMax함수를 작성 -> 매개변수 X
Ex) emp테이블에서 최대급여?-> v_max
create or replace function getMax
return NUMBER
is
v_max NUMBER :=0; — 최대값을 저장하는 변수
begin
select max(sal)
into v_max
from emp;
return v_max;
end;
/
실행결과
function getMax이(가) 컴파일되었습니다.
select getMax from dual;
GETMAX
———
5000
<문제17> 데이터를 입력시켜주는 프로시저작성
create table mem(
num number,
name varchar2(20),
sex varchar2(10));
insert into mem values(1,’홍길동’,’남자’);
select max(num) from mem;
select * from mem;
============================