▣ 33. PL/SQL 연산자의 예제
SQL 시간에 배웠던 산술연산자와 기타 비교연산자를 PL/SQL 연산자로 사용할 수 있다.
▣ 34. 프로그래밍 지침
accept p_ename prompt '이름을 입력하세요 ~'
DECLARE
v_ename emp.ename%type := upper('&p_ename'); .
v_sal emp.sal%type;
BEGIN
select sal into v_sal
from emp
where ename = v_ename;
dbms_output.put_line( v_sal );
END;
/accept p_ename prompt '이름을 입력하세요 ~'
▣ 4장. 오라클 데이터베이스 서버와 상호 작용
PL/SQL 프로그램의 SQL 문
중요한 부분 ) PL/SQL에서 트랜잭션 제어문 사용 → TCL commit;
암시적 커서와 명시적 커서 구별
▣ 35. PL/SQL 에서의 SQL문
PL/SQL 내에 SELECT 문과 DML문과 TCL 문을 사용할 수 있습니다.
- DML 문 : insert, update, delete, merge, select
- TCL 문 : commit, rollback, savepoint 참고: DDL문은 뒤에서 배울 execute immediate 절과 같이 사용됩니다.
( dba 의 작업을 편하게 해주는 PL/SQL 스크립트 생성시 유요함)
- DDL 문 : create, alter, drop, truncate, rename
▣ 36. PL/SQL 에서의 select 문장
PL/SQL 에서의 select 문장은 select .. into 절로 사용이 됩니다.
select .. into 를 안쓰고 그냥 select 만 하는 경우는 명시적 커서 선언할 때만
입니다. 대부분 select .. into 절을 사용해서 PL/SQL 프로그래밍을 합니다.
예제: "select .. into 절을 사용할 때는 1개의 행만 인출해야 합니다.
다음과같이 여러개의 값을 인출하려 하면 오류가 발생합니다."
set serveroutput on
declare
v_ename varchar2(25);
begin
select ename into v_ename
from emp
where job='SALESMAN';
dbms_output.put_line( v_ename );
end;
/
ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다문제32. (시연예제) 부서번호를 물어보게 하고 부서번호를 입력하면
해당 부서번호의 토탈월급이 출력되게하시오 !
accept p_deptno prompt '부서번호를 입력하세요 ~ '
declare
v_deptno emp.deptno%type := &p_deptno;
v_sumsal number(10,2) ;
begin
select sum(sal) into v_sumsal
from emp
where deptno = v_deptno ;
dbms_output.put_line ( '토탈월급은 : ' || v_sumsal );
end;
/※ group 함수의 특징 ? 1. null 값을 무시합니다.
2. where 절의 조건이 거짓이어도 결과를 리턴합니다.
예: max, sum,avg,min 은 null 값을 리턴합니다.
count 는 0 을 리턴합니다.
선택된 레코드가 없다고 출력되지 않습니다.group 함수의 특징!
SQL> select sal
2 from emp
3 where deptno = 50;
선택된 레코드가 없습니다.
SQL> select sum(sal)
2 from emp
3 where deptno = 50;
SUM(SAL)
----------
SQL> select count(*)
2 from emp
3 where deptno = 50;
COUNT(*)
----------
0
문제33. (학생) 직업을 물어보게 하고 직업을 입력하면 해당 직업의
토탈월급이 출력되게 하는 PL/SQL 을 작성하시오 !
직업을 입력하세요 ! SALESMAN
토탈월급은 5600 입니다.
set serveroutput on
accept p_job prompt '직업을 입력하세요 ! '
declare
v_job emp.job%type := UPPER('&p_job') ;
v_sumsal number(10,2) ;
-- emp emp.ename%type;
begin
select sum(sal) into v_sumsal
from emp
where job = v_job;
dbms_output.put_line ( '토탈월급은 :' || v_sumsal || ' 입니다') ;
end;
/▣ 이름 지정 모호성
- 모호할 가능성이 있는 SQL 문에서 데이터베이스 열의 이름은 로컬 변수의 이름보다 우선합니다.
- where 1 = 1 전체 검색이 되어 버림, 하나의 행만 return 해야하는데 여러행이 나오므로 에러발생.
PL/SQL 변수명을 명명할 때는 컬럼명과 동일하게 지정하지 않습니다.
아래와 같이 변수명과 컬럼명이 동일하면 컬럼명이 변수명보다 우선순위가
먼저 입니다.
예: SELECT ename, hiredate into ename, hiredate
from emp
where empno = e
- 열이름을 식별자로 사용하지 마라 → 테이블의 열이름을 변수로 사용하지마라
참조 : 1. 컬럼명과 동일한 컬럼명 변수가 있다면 컬럼명이 우선순위가 높습니다.
2. **테이블명과 동일한 테이블명 변수가 있다면 변수명이 우선순위가
높습니다.**set serveroutput on
accept p_job prompt '직업을 입력하세요 ! '
declare
v_job emp.job%type := UPPER('&p_job') ;
v_sumsal number(10,2) ;
**-- emp emp.ename%type;**
begin
select sum(sal) into v_sumsal
from emp
where job = v_job;
dbms_output.put_line ( '토탈월급은 :' || v_sumsal || ' 입니다') ;
end;
/▣ PL/SQL 내에서 DML 문 사용법
만약 아래의 작업을 오늘밤 10시에 해야한다면 ?
update emp
set sal = 7000
where ename='KING';
10시까지 남아서 수행하는게 아니라 프로시져에 위의 update 문을 코딩하고
밤 10시에 오라클 dbms_job 또는 dbms_scheduler 를 이용해서 자동으로
작업되게 할 수 가 있습니다.
문제34.(예제시연) 아래의 데이터를 emp 테이블에 입력하는 PL/SQL문을
작성하시오 ! ( 프로시져로 생성하시오 )
1. 시퀀스를 생성합니다.
create sequence emp_seq
start with 8000
increment by 1
maxvalue 9000;
2. 프로시져를 생성합니다.
create procedure insert_emp
as
begin
insert into emp(empno, ename, sal, deptno, job )
values( emp_seq.nextval, 'JAMES', 3000, 10, 'SALESMAN');
end;
/
3. 프로시져를 실행합니다.
execute insert_emp;
select * from emp;문제35. (학생) salgrade 테이블에 6번으로 아래의 데이터를 입력하는
프로시져를 생성하시오 ! 프로시져 이름: insert_salgrade
grade : 6
losal : 10000
hisal : 20000
답: create or replace procedure insert_salgrade
as
begin
insert into salgrade values( 6, 10000, 20000);
commit;
end;
/
-> 지금말고, 밤 10시에 자동으로 하고싶으면 어떻게 해야될까???
dbms_job, dbms_scheduler 패키지를 사용해서 시간을 설정한다▣ PLSQL 내에서의 UPDATE 문 사용법
만약 밤 10시에 EMP 테이블에 직업이 SAELSMAN인 사원들의 월급을
자신의 월급에서 10% 인상시킨 월급으로 갱신되게 하려면
다음과 같이 procedure 로 만들어서 밤 10시에 자동으로 수행되게 하면 됩니다.
예제:
1. 프로시져를 생성합니다.
create or replace procedure update_emp
as
begin
update emp
set sal = sal * 1.1
where job='SALESMAN';
commit;
end;
/
2. 프로시져를 실행합니다.
execute update_emp;
PL/SQL 처리가 정상적으로 완료되었습니다.
※ 프로시져를 실행했을때 성공적으로 수행되었다라고 위와 같이 메세지는
출력되었는데 진짜로 잘 되었는지 바로 알기가 어렵습니다.
뭔가 몇개의 행이 갱신되었다라고 메세지가 출력되면 확실히 알 수 있을텐데
없어서 조금 꺼림칙 합니다. 그래서 오라클에서 암시적 커서 함수를
제공하고 있습니다. 암시적 커서 함수를 이용하면 몇건이 갱신 되었다라고
출력해줍니다.문제36. (학생) 부서번호가 20번인 사원들의 커미션을 9000 으로 변경하는
프로시져를 생성하시오 ! (프로시져 이름: update_emp_comm )
1. 프로시져를 만듭니다.
create or replace procedure update_emp_comm
as
begin
update emp
set comm = 9000
where deptno = 20;
end;
/
2. 프로시져를 실행합니다.
execute update_emp_comm;
select *
from emp;
- 데이터 삭제
DELETE 문은 테이블에서 불필요한 행을 제거합니다. 무결성 제약 조건이 없는 경우 WHERE절을 사용하지 않으면 테이블의 모든 행이 제거될 수 있습니다.
문제37.(시연예제) 사원 테이블에 부서번호가 10번인 사원들의 데이터를
지우는 PROCEDURE 를 생성하시오 ! (프로시져 이름: delete_deptno )
1. 프로시져를 생성합니다.
create or replace procedure delete_deptno
as
begin
delete from emp
where deptno = 10;
commit;
end;
/
2. 프로시져를 실행합니다.
execute delete_deptno;문제38.(학생) 커미션이 null 인 사원들의 데이터를 지우는 프로시져를
생성하시오 ! ( 프로시져 이름: delete_comm )
답:
1. 프로시져 생성
create or replace procedure delete_comm
as
begin
delete from emp
where comm is null ;
commit;
end;
/
2. 프로시져 실행
exec delete_comm문제39. (예제시연) merge 문을 PL/SQL 내에 넣어서 실행하기 위한 환경구성을
먼저 다음과 같이 진행합니다.
1. emp 와 dept 를 초기화 합니다.
@init_emp.sql
2. emp 테이블을 가지고 copy_emp 테이블을 생성합니다.
create table copy_emp
as
select *
from emp;
3. emp 테이블에 월급을 모두 0 으로 변경합니다.
update emp
set sal = 0;
4. emp 테이블에서 부서번호가 20번인 사원들을 지웁니다.
delete from emp where deptno = 20;
5. emp 테이블에서 커미션이 null 이 아닌 사원들의 직업을 ANALYST 로
변경하고 부서번호를 50번으로 변경하시오 !
update emp
set job='ANALYST', deptno= 50
where comm is not null;
commit;
문제40. (학생) emp 테이블 데이터를 copy_emp 테이블의 내용과 일치하도록
merge 하시오 ! ( copy_emp 테이블을 사용해서 emp 테이블을 merge
하세요)
merge into emp e
using copy_emp c
on ( e.empno = c.empno )
when matched then
update set e.ename = c.ename,
e.job = c.job,
e.sal = c.sal,
e.comm = c.comm,
e.hiredate = c.hiredate,
e.mgr = c.mgr,
e.deptno = c.deptno
when not matched then
insert (e.empno,e.ename, e.job, e.sal, e.comm, e.hiredate, e.mgr, e.deptno )
values (c.empno, c.ename, c.job, c.sal, c.comm, c.hiredate, c.mgr, c.deptno );▣ SQL 커서
커서(cursor) 는 oracle 서버에서 할당한 전용 메모리 영역에 대한 포인터 입니다.
select 문의 결과집합을 처리하는데 사용이 됩니다.
- 커서의 종류 : 1. 암시적 커서
2. 명시적 커서 - 암시적 커서의 종류 3가지 ?
→ 이게 왜 중요하냐? 밤 10시에 delete 작업을 할건데, procedure로 만들어 놓은건데, SQL%rowcount를 단순 프린트가 아닌, 다른 table에다 insert를 해버려서 몇건이 영향을 받았는지 쉽게 확인할 수 있다.문제41. (시연예제) 사원 테이블에 사원번호가 7788 번인 사원의 데이터를 지우는
익명 PL/SQL 블럭을 작성하는데 암시적 커서를 이용해서
몇건이 지워졌다라고 결과 메세지를 출력하게 하시오 !
set serveroutput on
declare
v_empno emp.empno%type := 7788;
row_deleted varchar2(50);
begin
delete from emp
where empno = v_empno;
row_deleted := ( SQL%rowcount || '행이 지워졌습니다. ' );
dbms_output.put_line ( row_deleted );
end;
/문제42.(학생) 익명 PL/SQL 블럭으로 작성하세요.
직업을 물어보게하고 직업을 입력하면 해당 직업의 월급을
9000으로 변경하는 PL/SQL을 작성하세요.
PL/SQL 이 성공적으로 수행되었을때 몇행이 갱신되었다라는
메세지가 화면에 출력되게 암시적 커서 함수를 사용하세요 !
직업을 입력하세요 ~ SALESMAN
5개의 행이 잘 갱신되었습니다.
set serveroutput on
accept p_job prompt '직업을 입력하세요 ~ '
declare
v_job emp.job%type := upper('&p_job');
begin
update emp
set sal = 9000
where job = v_job;
dbms_output.put_line( SQL%ROWCOUNT || '의 행이 잘 갱신되었습니다.' );
commit;
end;
/
※ 주의사항 ! commit 은 SQL%ROWCOUNT 다음에 작성해야 합니다.
만약 update 문 바로 다음에 commit 을 쓰면 0 행이 갱신되었다라고
출력됩니다.쿠팡 ---> 매일밤 그날 하루동안 판매한 매출액등을 집계해서 어느 집계 테이블에
입력이 됩니다. 매일밤 어느 PL/SQL 프로그램이 돌면서 집계테이블에 입력을 해줍니다. 입력작업이 성공적으로 수행되었는지 그 건수를 암시적 커서로 잡아서 입력해줍니다.
문제43. (시연예제) 다음과 같이 job_sumsal 이라는 테이블을 생성하시오 !
create table job_sumsal
( job varchar2(20),
sumsal number(10,2 ) );
문제44. (시연예제) job_sumsal 테이블에 직업, 직업별 토탈월급을 입력하시오 !
insert into job_sumsal
select job, sum(sal)
from emp
group by job;
select * from job_sumsal;
문제45. (학생) 아래의 SQL을 실행하는 프로시져를 생성하시오 !
프로시져를 실행하면 몇개의 행이 입력되었습니다. 라는 메세지가
출력되게하시오 ! 프로시져 이름은 daily_batch1 로 하세요 !
insert into job_sumsal
select job, sum(sal)
from emp
group by job;
답:
1. 프로시져를 생성합니다.
create or replace procedure daily_batch1
as
begin
insert into job_sumsal
select job, sum(sal)
from emp
group by job;
dbms_output.put_line( SQL%ROWCOUNT || '행이 입력되었습니다.');
commit;
end;
/
2. 프로시져를 수행합니다.
execute daily_batch1;문제46. (시연예제) 아래의 테이블을 생성하고 daily_batch1 프로그램을 수행했을
때 SQL%rowcount 에 잡히는 숫자값이 입력되게 하시오 !
create table log_table
( time_id timestamp,
tablename varchar2(10),
insert_cnt number(10) );
insert into log_table values( systimestamp, 'job_sumsal', 5 );
rollback;
문제47. (학생) daily_batch1 프로그램에 insert 문장으로 영향을 받은 건수가
log_table 에 입력될 수 있도록 daily_batch1 프로그램을 수정하세요.
v_cnt := SQL%rowcount ;
insert into log_table values( systimestamp, 'job_sumsal', v_cnt );
commit;
답:
1. 프로시져를 생성합니다.
create or replace procedure daily_batch1
as
**v_cnt number(10);**
begin
insert into job_sumsal
select job, sum(sal)
from emp
group by job;
v_cnt := SQL%rowcount ;
insert into log_table
values( systimestamp, 'job_sumsal', v_cnt );
commit;
end;
/
▣ 5장. 제어문 (if 문)
▣ 실행흐름 제어
▣ if 문 문법
예제: 아래의 if 문의 조건은 False 이므로 dbms_output.put_line 실행문이
실행되지 않습니다.
set serveroutput on
declare
v_myage number := 31 ;
begin
if v_myage < 11 then
dbms_output.put_line(' i am a child ');
end if;
end;
/▣ if ~ then ~ else 문
set serveroutput on
declare
v_myage number := 31 ;
begin
if v_myage < 11 then
dbms_output.put_line(' i am a child ');
else
dbms_output.put_line('i am a not child');
end if;
end;
/문제48.(학생) 위의 예제를 수정해서 나이를 물어보게 하고 나이를 입력하면
i am a child 또는 i am a not child 가 출력되게하시오 !
나이를 입력하세요 ~ 17
i am a not child
나이를 입력하세요 ~ 9
i am a child
답:
set serveroutput on
accept p_age prompt '나이를 입력하세요 ~'
declare
v_myage number := &p_age ;
begin
if v_myage < 11 then
dbms_output.put_line(' i am a child ');
else
dbms_output.put_line('i am a not child');
end if;
end;
/▣ if ~ elsif ~ else 절
- 첫번째 if 절 만나서 조건을 만족하면 뒤에 elsif 절 수행하지 않고, endif 만나 끝난다.
accept p_age prompt '나이를 입력하시오 ~'
declare
v_myage number := &p_age ;
begin
if v_myage < 11 then
dbms_output.put_line(' i am a child ');
elsif v_myage < 20 then
dbms_output.put_line(' i am a young');
elsif v_myage < 30 then
dbms_output.put_line(' i am in my twnties');
elsif v_myage < 40 then
dbms_output.put_line('i am in my thirties');
else
dbms_output.put_line('i am always young');
end if;
end;
/문제49. (학생) 위의 코드를 수정하는데 우리반 테이블을 가지고 작성하세요
이름을 물어보게하고 이름을 입력하면 해당 학생의 나이를
테이블에서 읽어와서 위의 예제대로 메세지가 출력되게하시오 !
이름을 입력하세요 ~ 김동휘
I am in my thirties
DECLARE
v_ename emp17.ename%type := '&p_ename';
v_age NUMBER;
begin
select age into v_age
from emp17
where ename = v_ename;
if v_age < 11
then dbms_output.put_line ( ' I am a child ' );
elsif v_age < 20
then dbms_output.put_line ( ' I am young ' );
elsif v_age < 30
then dbms_output.put_line ( ' I am in my twenties' );
elsif v_age < 40
then dbms_output.put_line ( ' I am in my thirties' );
else
dbms_output.put_line ( ' I am always young ' );
end if;
end;
/