728x90
sql의 장점과 단점
장점
1) 사용자가 이해하기 쉬운 단어로 구성
2) 쉽게 배울 수 있다
3) 복잡한 로직을 간단하게 작성할 수 있다.
4) ansi에 의해 문법이 표준화되어 있다.
단점
1) 반복처리를 할 수 없다
2) 비교처리를 할 수 없다
3) error 처리를 할 수 없다.
4) sql문을 캡슐화 할 수 없다.
5) 변수선언을 할 수 없다.
6) 실행할 때 마다 분석작업 후 실행
7) Network Traffic을 유발한다
이러한 단점을 극복하기 위해 pl/sql을 사용한다
pl/sql의 사용 이유
1) 반복 처리를 할 수 있다
2) 비교 처리를 할 수 있다
3) error 처리를 할 수 있다.
4) sql 문을 캡슐화 할 수 있다.
5) 변수 선언을 할 수 있다
6) 실행할 때 마다 분석된 결과를 실행 만 하기 때문에 성능이 빠르다
7) Network Traffic이 감소된다. 여러 sql문장을 block으로 묶고
한번에 블럭 전부를 서버로 전송하기 때문에 통신량을 줄일 수 있다.
set serveroutput on;
declare
--변수 선언 영역
msg varchar2(20) default 'gildong';
begin
--실행문
--대입문
msg := '홍길동';
dbms_output.put_line(msg);
end;
----------------------------------------------------------select 프로시져----------------------------------------------
-- procedure 생성하기
-- 사원번호 입력받아 사원의 정보(사원명, 담당업무, 급여를 선택하는 프로시저를 만든다
-- 프로시저명
create or replace procedure emp_select(p_empno in number)
is
-- 변수
s_ename varchar2(20);
s_job varchar2(20);
s_sal number(7,2);
begin
--실행문
select ename, job, sal into s_ename,s_job,s_sal from emp where empno=p_empno;
dbms_output.put_line('사원명->' || s_ename);
dbms_output.put_line('업무->' || s_job);
dbms_output.put_line('급여->' || s_sal);
dbms_output.put_line('처리 종료 됨..');
end;
-- procedure 소스 코드 확인하기 - 데이터 사전
select * from user_source;
-- 실행
execute emp_select(7900);
------------------------------------------------------insert 프로시져 ------------------------------------------------
-- %type : 실제 필드의 데이터형과 자릿수를 적용하는 변수 선언시
-- 레코드 추가하는 프로시저 생성
-- 사원번호, 사원명, 급여, 부서코드를 추가
create or replace procedure emp_insert(p_empno emp.empno%type, p_ename emp.ename%type,
p_sal emp.sal%type, p_deptno emp.deptno%type)
is
begin
insert into emp(empno, ename, sal, deptno) values(p_empno, p_ename, p_sal, p_deptno);
commit;
dbms_output.put_line(p_empno||'->레코드가 추가됨');
end;
select * from user_source;
execute emp_insert(1234,'XXXX',5000,30);
---------------------------------------------------------------------------------------------------------------------------
--7369, 7499
-- 레코드 1개의 모든 필드를 선택하는 프로시저 생성
create or replace procedure emp_record(p_empno emp.empno%type)
is
r_emp emp%rowtype;
begin
select empno, ename, mgr, job, hiredate, sal, comm, deptno
into r_emp.empno, r_emp.ename, r_emp.mgr, r_emp.job, r_emp.hiredate, r_emp.sal, r_emp.comm, r_emp.deptno
from emp where empno=p_empno;
dbms_output.put_line('사원명='|| r_emp.ename|| ', 입사일='|| r_emp.hiredate);
end;
execute emp_record(7369);
select * from emp;
----------------------------------------------------------------------------------------------------------
-- 프로시저명 emp_update
-- 사원번호, 급여, 부서번호, 보너스 입력받아
-- 입력받은 사원번호의 급여, 부서번호, 보너스를 수정하는 프로시저 생성
create or replace procedure emp_update(p_empno emp.empno%type, p_sal emp.sal%type,
p_deptno emp.deptno%type, p_comm emp.comm%type)
is
e_rec emp%rowtype;
begin
update emp set sal= p_sal, deptno=p_deptno, comm=p_comm
where empno=p_empno;
select empno, ename, sal, comm
into e_rec.empno, e_rec.ename, e_rec.sal, e_rec.comm
from emp where empno=p_empno;
dbms_output.put_line('사원번호='||e_rec.empno);
dbms_output.put_line('사원명='||e_rec.ename);
dbms_output.put_line('급여='||e_rec.sal);
dbms_output.put_line('보너스='||e_rec.comm);
end;
execute emp_update(1234, 7000,50,100);
select * from emp;
select * from user_source;
-------------------------------------------------------------------------------------------------------
-- procedure parameter in parameter, out parameter, in out parameter
-- in parameter : 입력 매개변수, out : 출력 매개변수, in out(생략) : 변수 1개로 입력과 출력 할 수 있다.
-- 사원번호를 입력받아 이름을 선택하여 프로시저 외부로 데이터 보내기
create or replace PROCEDURE emp_name_search(pempno in emp.empno%type, p_ename out emp.ename%type)
is
begin
select ename into p_ename from emp where empno=pempno;
end;
variable s_ename varchar2(20);
execute emp_name_search(7876, :s_ename);
print s_ename;
desc member;
select * from user_sequences;
select * from member;
-- 회원등록 프로시저
-- num -> memsq
-- 이름, 연락처, 이메일, 주소 - 입력
-- writedate -> sysdate
------------------------------------------------------------------------------------------------------
create or replace procedure mem_insert(m_username in member.username%type, m_tel in member.tel%type,
m_email in member.email%type, m_addr in member.addr%type)
is
begin
insert into member values(memsq.nextval, m_username, m_tel, m_email, m_addr,sysdate);
dbms_output.put_line('레코드 추가 됨...');
end;
select * from member;
------------------------------------------------------------------------------------------------------------
create or replace procedure mem_update(m_username member.username%type,
m_tel member.tel%type,
m_email member.email%type,
m_addr member.addr%type)
is
begin
update member set tel = m_tel, email = m_email, addr=m_addr where username=m_username;
end;
select * from member;
commit;
----------------------------------------------------------
create or replace procedure empInsert
(
v_eno emp.empno%type,
v_ename emp.ename%type,
v_deptno emp.deptno%type)
is
begin
insert into emp(empno, ename, hiredate, deptno)
values(v_eno, v_ename, sysdate, v_deptno);
DBMS_OUTPUT.put_line('사번: '||v_eno);
DBMS_OUTPUT.put_line('이름: '||v_ename);
DBMS_OUTPUT.put_line('부서: '||v_deptno);
DBMS_OUTPUT.put_line('위의 데이터 입력 성공');
end;
execute empInsert(1000,'홍길동',20);
select * from emp;
--------------------------------------------------------------
create or replace procedure empUpdate
(
v_eno in emp.empno%type,
v_rate in number)
is
-- 수정할 데이터를 확인하기 위한 변수 선언
v_emp emp%rowtype;
begin
dbms_output.enable;
update emp set sal=sal+(sal*(v_rate/100)) where empno=v_eno;
dbms_output.put_line('데이터 수정 성공');
-- 수정된 데이터 확인하기 위해 검색
select empno, ename, sal into v_emp.empno, v_emp.ename, v_emp.sal
from emp where empno=v_eno;
dbms_output.put_line('==결과=====');
dbms_output.put_line('사번: '||v_emp.empno);
dbms_output.put_line('이름: '||v_emp.ename);
dbms_output.put_line('급여: '||v_emp.sal);
end;
set serveroutput on
execute empUpdate(7900,20);
select * from emp;
-------------------------------------------------------------------
--사원번호를 입력받아 사원번호,사원명, 부서명을 선택하는 프로시저 생성
--10: 회계부, 20: 기획부, 30: 인사부, 40: 총괄부
create or replace procedure emp_search(p_empno in emp.empno%type)
is
s_deptno emp.deptno%type;
s_ename emp.ename%type;
s_dname varchar2(20);
begin
select ename, deptno into s_ename, s_deptno from emp where empno=p_empno;
--7499
if s_deptno=10 then
s_dname := '회계부'; -- <---- := 대입
elsif s_deptno=20 then
s_dname := '기획부';
elsif s_deptno=30 then
s_dname := '인사부';
elsif s_deptno=40 then
s_dname := '총괄부';
else
s_dname := '해당부서가 없습니다.';
end if;
dbms_output.put_line(p_empno||', '||s_ename||', '||s_deptno ||', '||s_dname);
end;
set serveroutput on;
EXECUTE emp_search(7499);
select * from emp;
------ loop 문
create or replace procedure loop_test(i in number)
is
p_i number default 1000;
p_max number;
begin
p_max := p_i + i;--1005
loop
insert into emp(empno, ename) values(p_i, 'A'||p_i);
p_i := p_i + 1;
exit when p_i>p_max;
end loop;
end;
execute loop_test(4);
------------ job이 null 레코드를 삭제하는 프로시저
-- 프로시저명 : emp_del
create or replace procedure emp_del
is
begin
delete from emp where job is null;
commit;
end;
execute emp_del;
-- 테이블 변수 생성
create or replace procedure emp_table_for
is
-- 테이블 변수 정의 1컬럼(같은 종류의 데이터들)을 저장할수 있는 공간을 만든다
type empno_table is table of emp.empno%type index by binary_integer;
type ename_table is table of emp.ename%type index by binary_integer;
type sal_table is table of emp.sal%type index by binary_integer;
-- 변수 선언
p_empno empno_table;
p_ename ename_table;
p_sal sal_table;
idx integer :=0;-- index로 사용할 변수
begin
--select empno, ename, sal from emp order by ename;
for d in(select empno, ename, sal from emp order by ename) loop
idx := idx + 1;-- 1
p_empno(idx) := d.empno;
p_ename(idx) := d.ename;
p_sal(idx) := d.sal;
end loop;
--table형 변수의 값을 출력-------------------------------------
for i in 1..idx loop -- 1부터 idx까지 반복
dbms_output.put_line(p_empno(i) || ', ' || p_ename(i) || ', ' || p_sal(i));
end loop;
end;
execute emp_table_for;
--------------------while-----------------------------
create or replace procedure emp_while_test
is
i number := 1;
begin
while i<100 loop
insert into emp(empno, ename) values(1000+i, 'AAA'||i);
exit when i>31;
i := i+10;
end loop;
end;
execute emp_while_test;
select * from emp;
728x90
'Infrastructure > Database' 카테고리의 다른 글
oracle 커서 (0) | 2021.03.01 |
---|---|
oracle 트리거 (0) | 2021.03.01 |
oracle 시퀀스 (0) | 2021.03.01 |
oracle 뷰 (0) | 2021.03.01 |
oracle 조인 (0) | 2021.03.01 |