Lecture 17 - PLSQL(1)

오라클 PL/SQL 강좌
PL/SQL은 Oracle's Procedural Language extension to SQL의 약자이다.

종류
    익명 프로시저 - file을 통해서 실행
    내장 프로시저 - Oracle에 저장
    함수 - 함수형식으로 Oracle에 저장

plsql 01 시작

set serveroutput ON

begin
-- 내용 구문
-- 출력

-- 한 줄 + 엔터
dbms_output.put_line('Hello PL/SQL');
dbms_output.put_line('Hello PL/SQL');

dbms_output.put('Hello PL/SQL');
dbms_output.put('Hello PL/SQL');
dbms_output.put_line('');

dbms_output.put('Hello PL/SQL');
dbms_output.put('Hello PL/SQL');
dbms_output.put_line('');
end;
/

set serveroutput OFF

plsql 02 파라미터 설명

set serveroutput ON

DECLARE --선언부


begin --실행부
-- 모든 함수 사용이 가능
-- || 문자열 연결 연산자
dbms_output.put_line('Hello' || 'PL/SQL');
dbms_output.put_line(concat('Hello', 'PL/SQL'));
dbms_output.put_line(concat(sysdate, '입니다.'));
end;
/

set serveroutput OFF

plsql 03 변수 선언

set serveroutput ON

DECLARE
-- 변수 선언
-- 변수명 자료형;
-- 변수명 자료형 = 값;
-- v_
v_hiredate date;
v_ename varchar2(10);
begin
v_hiredate := '17/08/11';
v_ename := '홍길동';

dbms_output.put_line('v_hiredate : ' || v_hiredate);
dbms_output.put_line('v_ename : ' || v_ename);
end;
/

set serveroutput OFF

plsql 04 변수 선언

set serveroutput ON

DECLARE
v_deptno number not null := 10;
v_loc varchar2(13) := upper('atlanta');
-- constant : 상수 -> 값을 변경할 수 없는 변수
c_com constant number := 1400;
begin
dbms_output.put_line('v_deptno : ' || v_deptno);
dbms_output.put_line('v_loc : ' || v_loc);
dbms_output.put_line('c_com : ' || c_com);

v_deptno := 20;
dbms_output.put_line('v_deptno : ' || v_deptno);
c_com := 1600;
dbms_output.put_line('c_com : ' || c_com);

end;
/

set serveroutput OFF

plsql 05 변수 선언

set serveroutput ON

DECLARE
v_deptno number(2);
v_dname varchar2(14);
v_loc varchar2(13);
begin
-- 반드시 select한 결과가 1개 이여야 한다.
select deptno, dname, loc
into v_deptno, v_dname, v_loc
from dept
where deptno=80;

dbms_output.put_line(v_deptno);
dbms_output.put_line(v_dname);
dbms_output.put_line(v_loc);

end;
/

set serveroutput OFF

plsql 06 %type

set serveroutput ON

DECLARE
v_deptno dept.deptno%type;
v_dname dept.dname%type;
v_loc dept.loc%type;
begin
select deptno, dname, loc
into v_deptno, v_dname, v_loc
from dept
where deptno=10;

dbms_output.put_line(v_deptno);
dbms_output.put_line(v_dname);
dbms_output.put_line(v_loc);

end;
/

set serveroutput OFF

plsql06_1
10부서의 최고급여를 받는 사원에 대한 정보를 사원번호, 사원명, 최고급여 출력

plsql 07 %rowtype

set serveroutput ON

DECLARE
v_dept dept%rowtype;
begin
select * 
into v_dept
from dept
where deptno = 10;

dbms_output.put_line(v_dept.deptno);
dbms_output.put_line(v_dept.dname);
dbms_output.put_line(v_dept.loc);
end;
/

set serveroutput OFF

plsql 08 외부에서 데이터 받아오기

set serveroutput on

set verify off
accept p_ename prompt '조회할 사원의 이름을 입력하세요 : '
declare
v_ename varchar2(10) := upper('&p_ename');
begin
--dbms_output.put_line('사원명 : ' || '&p_ename');
dbms_output.put_line('사원명 : ' || v_ename);
end;
/

set serveroutput off
set verify on

plsql 09 외부에서 입력받은 데이터 정보를 이용하여 출력하기

set verify off

set serveroutput on

accept p_deptno prompt '부서번호입력 : ';
declare
v_deptno dept.deptno%type := &p_deptno;
v_dept dept%rowtype;
begin
select *
into v_dept
from dept
where deptno = v_deptno;

dbms_output.put_line(v_dept.deptno);
dbms_output.put_line(v_dept.dname);
dbms_output.put_line(v_dept.loc);
end;
/

set serveroutput off
set verify on

plsql09_1
사원명을 입력받아서 사원번호, 사원명, 급여, 호봉을 출력하는 스크립트 생성

set verify off

set serveroutput on

accept p_ename prompt '사원명 : ';
declare
v_ename emp.ename%type := upper('&p_ename');
v_emp emp%rowtype;
v_salgrade salgrade.grade%type;
begin
select empno, ename, sal, grade
into v_emp.empno, v_emp.ename, v_emp.sal, v_salgrade
from emp e, salgrade s
where e.sal between s.losal and s.hisal
and e.ename = v_ename;

dbms_output.put_line(v_emp.empno);
dbms_output.put_line(v_emp.ename);
dbms_output.put_line(v_emp.sal);
dbms_output.put_line(v_salgrade);
end;
/

set serveroutput off
set verify on

plsql 10 Record type : 자료형태 사용자 정의

set serveroutput ON

DECLARE
-- record type 
-- 사용자 정의 데이터 타입(자료형)

type emp_record_type is record
(
v_empno emp.empno%type,
v_ename emp.ename%type,
v_job emp.job%type,
v_mgr emp.mgr%type
);

-- 변수 선언
v_emp emp_record_type;
BEGIN
select empno, ename, job, mgr
into v_emp
from emp
where empno = 7369;

dbms_output.put_line(v_emp.v_empno);
dbms_output.put_line(v_emp.v_ename);

end;
/

set serveroutput OFF

plsql 11 begin end 안에 또 begin end 넣어서 나오는 출력값 확인

set serveroutput ON

DECLARE
-- 변수가 영향을 미치는 영역
x number;
y number;

BEGIN
x := 1; 
y := 2;

dbms_output.put_line('x1 : ' || x);
dbms_output.put_line('y1 : ' || y);

DECLARE
y number;
z number;
begin
x := 3;
y := 4;
z := 5;

dbms_output.put_line('x2 : ' || x);
dbms_output.put_line('y2 : ' || y);
dbms_output.put_line('z2 : ' || z);

end;
dbms_output.put_line('x3 : ' || x);
dbms_output.put_line('y3 : ' || y);
--dbms_output.put_line('z3 : ' || z);

end;
/

set serveroutput OFF

plsql 12 사칙연산, 형변환

set serveroutput ON

DECLARE
v_cnt number :=0;

BEGIN
-- ++ / -- (x)
v_cnt := v_cnt + 1;
dbms_output.put_line(v_cnt); 

-- = / !=, <>
-- to_char / to_date / to_number
end;
/

set serveroutput OFF

plsql 13 제어문

set serveroutput ON

DECLARE
v_n1 number := 1;
v_n2 number := 2;
BEGIN
-- 제어문
-- {} 개념이 없다
-- 조건에 의한 분기
/*
if 조건 then
실행문
end if;

if 조건 then
실행문
else
실행문
end if;

if 조건 then
elsif 조건 then
elsif 조건 then
else
end if;
*/
-- 조건에 의한 반복

if v_n1 = v_n2 then
dbms_output.put_line('같다.');
else
dbms_output.put_line('다르다.');
end if;

if v_n1 > v_n2 then
dbms_output.put_line('크다.');
elsif v_n1 < v_n2 then
dbms_output.put_line('작다.');
else
dbms_output.put_line('같다.');
end if;
end;
/

set serveroutput OFF

plsql 13_1 입력받은 점수로 학점 계산하기

set verify OFF

set serveroutput ON

accept p_hakjum prompt '점수를 입력해 주세요 : ';
DECLARE
v_hakjum number := &p_hakjum;
BEGIN
if v_hakjum >= 90 then
dbms_output.put_line('학점은 : A');
elsif v_hakjum >= 80 then
dbms_output.put_line('학점은 : B');
elsif v_hakjum >= 70 then
dbms_output.put_line('학점은 : C');
elsif v_hakjum >= 60 then
dbms_output.put_line('학점은 : D');
else
dbms_output.put_line('학점은 : F');
end if;
end;
/

set serveroutput OFF
set verify ON

plsql 14 반복문

set serveroutput ON

-- 반복문
/*
loop
조건을 통해서 탈출
end loop;
*/
DECLARE
v_cnt number := 1;

BEGIN
loop
dbms_output.put_line(v_cnt);
v_cnt := v_cnt + 1;
/*
if v_cnt = 11 then
exit; -- break
end if;
*/

exit when v_cnt = 11;
end loop;
end;
/

set serveroutput OFF

plsql 14_1 1부터 100까지 짝수의 합 더하기

set serveroutput ON

-- 반복문
/*
loop
조건을 통해서 탈출
end loop;
*/
DECLARE
v_cnt number := 1;
v_sum number := 0;

BEGIN
loop
exit when v_cnt = 101;

if v_cnt mod 2 = 0 THEN
v_sum := v_sum + v_cnt;
end if;

v_cnt := v_cnt + 1;
end loop;

dbms_output.put_line(v_sum);
end;
/

set serveroutput OFF

plsql 15

set serveroutput ON

-- 이중 루프
DECLARE
v_cnt1 number := 1;
v_cnt2 number;
BEGIN
loop
v_cnt2 := 1;
loop
dbms_output.put('*');
v_cnt2 := v_cnt2 + 1;
exit when v_cnt2 >= 11;
end loop;
dbms_output.put_line('');

v_cnt1 := v_cnt1 + 1;
exit when v_cnt1 >= 11;
end loop;
end;
/

set serveroutput OFF

plsql 15_1 피라미드 형태로

set serveroutput ON

-- 이중 루프
DECLARE
v_cnt1 number := 1;
v_cnt2 number;
BEGIN
loop
v_cnt2 := 1;
loop
dbms_output.put('*');
v_cnt2 := v_cnt2 + 1;
exit when v_cnt2 > v_cnt1;
end loop;
dbms_output.put_line('');

v_cnt1 := v_cnt1 + 1;
exit when v_cnt1 >= 11;
end loop;
end;
/

set serveroutput OFF

plsql 16 for

set serveroutput ON

-- for
DECLARE

BEGIN
for v_idx in reverse 1..10 loop
dbms_output.put_line(v_idx);
end loop;
end;
/

set serveroutput OFF

plsql 17

set serveroutput ON

-- for
DECLARE
v_cnt number := 1;
BEGIN
while v_cnt <= 10 loop
dbms_output.put_line(v_cnt);
v_cnt := v_cnt + 1;
end loop;
end;
/
 
set serveroutput OFF

plsql 18
구구단 출력
1. 시작단과 끝단
2. 시작단이 끝단보다 작아야함 / 에러

set verify off

set serveroutput ON

accept p_input_begin prompt '시작단수 : ';
accept p_input_last prompt '끝단수 : ';
DECLARE
v_start number := &p_input_begin;
v_end number := &p_input_last;
v_tot number;
BEGIN
if v_start > v_end then
dbms_output.put_line('START가 END보다 큽니다.');
else
for i_row in v_start..v_end loop
dbms_output.put_line(i_row || '단');
for i_col in 1..5 loop
v_tot := i_row * i_col;
dbms_output.put(i_row || ' * ' || i_col || ' = ' || v_tot || ' ');
end loop;
dbms_output.put_line('');
end loop;
end if;
end;
/

set serveroutput OFF

set verify on

plsql 19 배열 varray

set serveroutput ON

DECLARE
-- 배열 (varray)
type varray_type is varray(20) of number;
v_varray varray_type;
BEGIN
v_varray := varray_type(10, 20, 30, 40, 50);
-- index : 0 부터 시작
dbms_output.put_line(v_varray(1));
dbms_output.put_line(v_varray(5));
dbms_output.put_line(v_varray.count);

-- for 문을 돌려서 전체 데이터를 가져오고 싶다
for idx in 1..v_varray.count loop
dbms_output.put_line(v_varray(idx));
end loop;
end;
/

set serveroutput OFF

plsql 19_1 주민등록번호
1. 주민번호 입력
2. 맞다 / 틀리다

내가 한거 -1

set verify off
set serveroutput ON

accept p_jumin1 prompt '주민등록번호 앞자리를 입력하시오 : ';
accept p_jumin2 prompt '주민등록번호 뒷자리를 입력하시오 : ';
DECLARE
v_jumin1 number := &p_jumin1;
v_jumin2 number := &p_jumin2;
v_num1 number;
v_num2 number;
v_num3 number;
v_num4 number;
v_num5 number;
v_num6 number;
v_num7 number;
v_num8 number;
v_num9 number;
v_num10 number;
v_num11 number;
v_num12 number;
v_num13 number;
v_tot number;
BEGIN
if length(v_jumin1) = 6 and length(v_jumin2) = 7 then
v_num1 := to_number(substr(v_jumin1, 1, 1))*2;
v_num2 := to_number(substr(v_jumin1, 2, 1))*3;
v_num3 := to_number(substr(v_jumin1, 3, 1))*4;
v_num4 := to_number(substr(v_jumin1, 4, 1))*5;
v_num5 := to_number(substr(v_jumin1, 5, 1))*6;
v_num6 := to_number(substr(v_jumin1, 6, 1))*7;

v_num7 := to_number(substr(v_jumin2, 1, 1))*8;
v_num8 := to_number(substr(v_jumin2, 2, 1))*9;
v_num9 := to_number(substr(v_jumin2, 3, 1))*2;
v_num10 := to_number(substr(v_jumin2, 4, 1))*3;
v_num11 := to_number(substr(v_jumin2, 5, 1))*4;
v_num12 := to_number(substr(v_jumin2, 6, 1))*5;
v_num13 := to_number(substr(v_jumin2, 7, 1));

v_tot := v_num1 + v_num2 + v_num3 + v_num4 + v_num5 + v_num6 + v_num7
+ v_num8 + v_num9 + v_num10 + v_num11 + v_num12;
v_tot := v_tot mod 11;
v_tot := 11 - v_tot;
v_tot := v_tot mod 10;

if v_tot = v_num13 then
dbms_output.put_line('맞습니다');
else
dbms_output.put_line('틀립니다.');
end if;
end if;
end;
/

set serveroutput OFF
set verify on

내가한거 -2

set verify off
set serveroutput ON

accept p_jumin prompt '주민등록번호를 입력하시오 : ';
DECLARE
v_jumin varchar2(14) := trim('&p_jumin');
type varray_type is varray(12) of number;
v_varray varray_type;
v_tot number := 0;
BEGIN
v_varray := varray_type(2, 3, 4, 5, 6, 7, 8, 9, 2, 3, 4, 5);
if length(v_jumin) = 14 then
v_jumin := replace(v_jumin, '-', '');

for idx in 1..v_varray.count loop
v_tot := v_tot + (to_number(substr(v_jumin, idx, 1)) * v_varray(idx));
end loop;
v_tot := v_tot mod 11;
v_tot := 11 - v_tot;
v_tot := v_tot mod 10;

if v_tot = to_number(substr(v_jumin, 13, 1)) then
dbms_output.put_line('맞습니다');
else
dbms_output.put_line('틀립니다');
end if;
end if;
end;
/

set serveroutput OFF
set verify on

강사님이 한거

set verify off
set serveroutput on

accept p_jumin prompt '주민등록번호를 입력하시오 : '

declare
type check_varray_type is varray(12) of number;

v_jumin varchar2(14) := trim('&p_jumin');
v_error number := 1;
v_tot number := 0;

check_varray check_varray_type;
begin
check_varray := check_varray_type(2, 3, 4, 5, 6, 7, 8, 9, 2, 3, 4, 5);

if length(v_jumin) = 14 then
v_jumin := replace(v_jumin, '-', '');

for idx in 1..check_varray.count loop
v_tot := v_tot + (to_number(substr(v_jumin, idx, 1)) * check_varray(idx));
end loop;
v_tot := v_tot mod 11;
v_tot := 11 - v_tot;
v_tot := v_tot mod 10;

if v_tot = to_number(substr(v_jumin, 13, 1)) then
v_error := 0;
else
v_error := 1;
end if;
else
v_error := 2;
end if;

if v_error = 0 then
dbms_output.put_line('검사여부 : 정확합니다.');
elsif v_error = 1 then
dbms_output.put_line('검사여부 : 부정확합니다.');
elsif v_error = 2 then
dbms_output.put_line('Error2 : 주민등록번호의 길이가 정확하지 않습니다.');
else
dbms_output.put_line('Error4 : 기타에러 입니다.');
end if;
end;
/

set verify on
set serveroutput off