Lecture 18 - PLSQL(2)

plsql 20 table

set serveroutput ON

DECLARE
--TABLE
type table_type is table of number
index by binary_integer;
v_table table_type;
BEGIN
v_table(1) := 10;
v_table(2) := 20;
v_table(3) := 30;
v_table(4) := 40;
v_table(5) := 50;

for idx in 1..5 loop
dbms_output.put_line(v_table(idx));
end loop;
end;
/

set serveroutput OFF

plsql 21 암시적 커서(cursor)

1. 암시적 커서 - 오라클이 내부적으로 사용하는 커서
sql%rowcount - 영향받은 함수
sql%found - sql이 발견 되었을 때
sql%notfound - sql이 발견되지 않았을 때
sql%isopen - sql이 open 되었을 때

set serveroutput ON
/*
cursor(메모리 안에 엑셀 - 행과 열)
1. 암시적 커서 - 오라클이 내부적으로 사용하는 커서
sql%rowcount - 영향받은 함수
sql%found - sql이 발견 되었을 때
sql%notfound - sql이 발견되지 않았을 때
sql%isopen - sql이 open 되었을 때
2. 명시적 커서 - 사용자가 만들어서 사용하는 커서
*/
DECLARE

BEGIN
delete from dept2 where deptno=0;

if sql%isopen then
dbms_output.put_line('커서가 열려있음');
else
dbms_output.put_line('커서가 닫혔음');
end if;

if sql%found then
dbms_output.put_line('sql 수행 : ' || sql%rowcount);
elsif sql%notfound then
dbms_output.put_line('sql 미수행');
end if;
end;
/

set serveroutput OFF

plsql 22 명시적 커서

set serveroutput ON
/*
cursor(메모리 안에 엑셀 - 행과 열)
1. 암시적 커서 - 오라클이 내부적으로 사용하는 커서
sql%rowcount - 영향받은 함수
sql%found - sql이 발견 되었을 때
sql%notfound - sql이 발견되지 않았을 때
sql%isopen - sql이 open 되었을 때
2. 명시적 커서 - 사용자가 만들어서 사용하는 커서
커서 선언 - 커서 오픈 - (fetch(데이터 가져오는거(한 행)) - 검사) - 커서 닫기
*/
DECLARE
cursor emp_cursor is
select empno, ename, sal
from emp
where deptno = 10;

v_empno emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
BEGIN
open emp_cursor;
loop
fetch emp_cursor into v_empno, v_ename, v_sal;
exit when emp_cursor%notfound;

dbms_output.put_line(v_empno);
dbms_output.put_line(v_ename);
dbms_output.put_line(v_sal);
end loop;

close emp_cursor;
end;
/

set serveroutput OFF

plsql 23 부서번호 입력받고 커서를 지정하여 입력받은 부서의 정보 출력하기

set verify off
set serveroutput ON

accept p_deptno prompt '부서번호 입력 : ';

DECLARE
v_deptno emp.deptno%type := &p_deptno;

v_empno emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_job emp.job%type;

v_sal_total emp.sal%type := 0;

cursor emp_cursor is
select empno, ename, sal, job
from emp
where deptno = v_deptno
order by empno;
BEGIN
open emp_cursor;

loop
fetch emp_cursor into v_empno, v_ename, v_sal, v_job;
exit when emp_cursor%notfound;

v_sal_total := v_sal_total + v_sal;

dbms_output.put_line(
rpad(v_empno, 6) || 
rpad(v_ename, 12) ||
lpad(v_sal, 16) ||
rpad(v_job, 16));
end loop;
dbms_output.put_line('부서의 합 ' || v_sal_total);

close emp_cursor;
end;
/

set serveroutput OFF
set verify on

plsql 23_1 
직책을 입력 받아서 사원번호, 사원명, 직책, 부서명, 부서위치를 출력하고 직책에 대한 급여합계를 출력하는 프로시저 생성

내가 한거

set verify off
set serveroutput ON

accept p_job prompt '직책명 입력 : ';

DECLARE
v_job emp.job%type := upper('&p_job');

v_empno emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_dname dept.dname%type;
v_loc dept.loc%type;

v_sal_total emp.sal%type := 0;

cursor emp_cursor is
select empno, ename, sal, job, dname, loc
from emp, dept
where job = v_job
order by empno;
BEGIN
open emp_cursor;

loop
fetch emp_cursor into v_empno, v_ename, v_sal, v_job, v_dname, v_loc;
exit when emp_cursor%notfound;

v_sal_total := v_sal_total + v_sal;

dbms_output.put_line(
rpad(v_empno, 6) || 
rpad(v_ename, 10) ||
rpad(v_job, 16) ||
rpad(v_dname, 20) ||
rpad(v_loc, 24));
end loop;
dbms_output.put_line(v_job || ' 직책의 급여합계 : ' || v_sal_total);

close emp_cursor;
end;
/

set serveroutput OFF
set verify on

plsql 24 

set verify off
set serveroutput ON

DECLARE
--cursor dept_cursor is 
-- select * 
-- from dept
-- order by deptno;
BEGIN
-- open / fetch / close
--for dept_record in dept_cursor loop
for dept_record in (select * from dept order by deptno) loop
dbms_output.put_line(
lpad(dept_record.deptno, 2) ||
' ' ||
rpad(dept_record.dname, 15) ||
rpad(dept_record.loc, 12)
);
end loop;
end;
/

set serveroutput OFF
set verify on

plsql 25 where deptno = 10 대신 dept_cursor(10) 지정하여 선언부 커서에 v_deptno number로 받게 하여 원하는 부서 내용 출력

set verify off
set serveroutput ON

DECLARE
cursor dept_cursor(v_deptno number) is 
select * 
from dept
where deptno = v_deptno
order by deptno;
BEGIN
-- open / fetch / close
for dept_record in dept_cursor(20) loop
dbms_output.put_line(
lpad(dept_record.deptno, 2) ||
' ' ||
rpad(dept_record.dname, 15) ||
rpad(dept_record.loc, 12)
);
end loop;
end;
/

set serveroutput OFF
set verify on

plsql 25_1
부서번호와 직책을 입력받아서 사원번호, 사원명, 급여를 출력하는 프로시저 생성

set verify off
set serveroutput ON
accept p_deptno prompt '부서번호 입력 : ';
accept p_job prompt '직책명 입력 : ';

DECLARE
cursor emp_cursor(
v_deptno emp.deptno%type,
v_job varchar2)
is
select empno, ename, sal
from emp
where deptno = v_deptno and job = v_job
order by empno;

BEGIN
for emp_record
in emp_cursor(&p_deptno, upper('&p_job')) loop
dbms_output.put_line(
rpad(emp_record.empno, 6) ||
rpad(emp_record.ename, 12) ||
rpad(emp_record.sal, 16));
end loop;
end;
/

set serveroutput OFF
set verify on

plsql 26 plsql과 cmd창 sqlplus와 트랜잭션이 일어난다.

set verify off
set serveroutput ON

DECLARE
v_empno emp2.empno%type := 1000;
v_ename emp2.ename%type := '홍길동';
v_job emp2.job%type := upper('manager');
v_deptno emp2.deptno%type := 10;
BEGIN
--insert into emp2
--values (v_empno, v_ename, v_job, sysdate, v_deptno);

--dbms_output.put_line(sql%rowcount || ' 행 입력 완료');

delete from emp2;
dbms_output.put_line(sql%rowcount || ' 행 삭제 완료');

-- dml commit/rollback이 따라온다.

commit;
end;
/

set serveroutput OFF
set verify on

plsql 27
10 -> emp10
20 -> emp20
30 -> emp30
기타 -> emp00
가는 프로시저 생성

set serveroutput on

declare
v_empno     emp.empno%type;
v_ename     emp.ename%type;
v_job       emp.job%type;
    v_deptno    emp.deptno%type;
begin
-- 기존 데이터 삭제
-- truncate 사용해도 됨
delete from emp10;
delete from emp20;
delete from emp30;
delete from emp00;

for emp_record in (select * from emp order by deptno) loop
v_empno := emp_record.empno;
v_ename := emp_record.ename;
v_job := emp_record.job;
        v_deptno    := emp_record.deptno;

if v_deptno = 10 then
insert into emp10
values (v_empno, v_ename, v_job);
elsif v_deptno = 20 then
insert into emp20
values (v_empno, v_ename, v_job);
elsif v_deptno = 30 then
insert into emp30
values (v_empno, v_ename, v_job);
else
insert into emp00
values (v_empno, v_ename, v_job);
end if;
end loop;

commit;

dbms_output.put_line('처리가 완료되었습니다.');
end;
/

set serveroutput off

plsql 28

set serveroutput on

declare
sql_stmt varchar2(2000);
begin
--sql_stmt := 'create table tbl2 (id number, amt number)';
sql_stmt := 'drop table tbl2 purge';
execute immediate sql_stmt;
-- dql - select
-- dml - insert / update / delete / merge
-- ddl - 실행이 안됨
/*
create table test(
col1 varchar2(10)
);
*/
-- Dynamic SQL : 문자열 -> SQL

--execute immediate 'create table tbl1 (id number, amt number)';
end;
/

set serveroutput off

plsql 29 execute immediate

set serveroutput on

declare
sql_stmt varchar2(2000);

v_id number := 1;
v_amt number := 2;
begin
sql_stmt := 'insert into tbl1 values('|| v_id || ', ' || v_amt || ')';
--dbms_output.put_line(sql_stmt);
execute immediate sql_stmt;

end;
/

set serveroutput off

execute immediate 전에 dbms_output.put_line으로 구문을 출력해서 검토해본다.

plsql 30

set serveroutput on

declare
sql_stmt varchar2(2000);

v_empno emp10.empno%type := 1000;
v_ename emp10.ename%type := '홍길동';
v_job emp10.job%type := 'CLERK';
begin
/*
- statement
sql_stmt := 'insert into emp10 values('
|| v_empno 
|| ', ''' || v_ename || ''', ''' || v_job || ''')';
--dbms_output.put_line(sql_stmt);
execute immediate sql_stmt;
*/

-- prepared statement
sql_stmt := 'insert into emp10 values(:1, :2, :3)';
execute immediate sql_stmt
using v_empno, v_ename, v_job;
end;
/

set serveroutput off

plsql 31

set serveroutput on

declare
sql_stmt varchar2(2000);
emp_record emp%rowtype;

begin
sql_stmt := 'select * from emp where empno = :1';

execute immediate sql_stmt
into emp_record
using 7934;

dbms_output.put_line(emp_record.empno);
dbms_output.put_line(emp_record.ename);
end;
/

set serveroutput off

plsql 32 에러 없음

set verify off
set serveroutput on
/*
에러 - if
예외(Exception)
- 일어날 수 있는 에러를 미리 정의해 놓은 것
=> if => exception
*/
accept p_ename prompt '사원이름 : '
declare
type emp_record_type is record (
v_empno emp2.empno%type,
v_ename emp2.ename%type,
v_sal emp2.sal%type
);
emp_record emp_record_type;
g_ename emp2.ename%type := '&p_ename';
begin
if g_ename in ('clark', 'king', 'miller') then
select empno, ename, sal
into emp_record
from emp2
where lower(ename) = g_ename;

dbms_output.put_line(emp_record.v_empno);
dbms_output.put_line(emp_record.v_ename);
else
dbms_output.put_line('사원없음');
end if;
end;
/

set serveroutput off
set verify on

NO_DATA_FOUND - 반납 없음
TOO_MANY_ROWS - 많은 행 반납
INVALID_CURSOR - 커서 오류
ZERO_DIVIDE - 0 나누기
DUP_VAL_ON_INDEX - 중복

plsql 33 exception 사용

set verify off
set serveroutput on
/*
에러 - if
예외(Exception)
- 일어날 수 있는 에러를 미리 정의해 놓은 것
=> if => exception
*/
accept p_ename prompt '사원이름 : '
declare
type emp_record_type is record (
v_empno emp2.empno%type,
v_ename emp2.ename%type,
v_sal emp2.sal%type
);
emp_record emp_record_type;
g_ename emp2.ename%type := '&p_ename';
begin
select empno, ename, sal
into emp_record
from emp2
where lower(ename) = g_ename;

dbms_output.put_line(emp_record.v_empno);
dbms_output.put_line(emp_record.v_ename);
exception
when no_data_found then
dbms_output.put_line('사원없음');

dbms_output.put_line(to_char(sqlcode));
dbms_output.put_line(sqlerrm);

end;
/

set serveroutput off
set verify on

exception을 쓰는게 좀 더 고급 기법이다

plsql 34

set verify off
set serveroutput on
accept p_ename prompt '사원이름 : '
declare
v_ename emp2.ename%type := '&p_ename';
begin
delete emp2
where ename = v_ename;

if sql%notfound then
--dbms_output.put_line('데이터 없음');
raise_application_error(-20100, '삭제 데이터 없음');
else
dbms_output.put_line('정상 삭제');
end if;
exception
when others then
dbms_output.put_line(to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;
/

set serveroutput off
set verify on