PL/SQL
- 자료형
- 특수자료형
- 집합자료형
- 변수 / 상수의 선언부에 쓴다.
제어문
- if
- if~elsif ~else
- loop
- for
- while
- →SQL문 + 프로그램 혼합
- →SQL문 + java
cursor
1.
set serveroutput on
declare
v_empno emp.empno%type;
v_ename emp.ename%type;
v_job emp.job%type;
v_mgr emp.mgr%type;
v_hiredate emp.hiredate%type;
v_sal emp.sal%type;
v_comm emp.comm%type;
v_deptno emp.deptno%type;
begin
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_mgr := emp_record.mgr;
v_hiredate := emp_record.hiredate;
v_sal := emp_record.sal;
v_comm := emp_record.comm;
v_deptno := emp_record.deptno;
if v_deptno = 10 then
insert into emp10
values (v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno);
elsif v_deptno = 20 then
insert into emp20
values (v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno);
elsif v_deptno = 30 then
insert into emp30
values (v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno);
end if;
end loop;
dbms_output.put_line('처리가 완료되었습니다.');
end;
/
set serveroutput off
SQL> rollback; --PL/SQL를 사용한 것도 롤백을 하면 지워짐 commit을 해줘야함
2.
set serveroutput on
declare
begin
--create table a (col1 varchar(10));
--에러남 PL/SQL 안에서 DDL/DCL 직접 실행 불가
--transaction때문에 commit 되는 것 방지
--Dynamic SQL 기법
--문자열을 SQL문 처럼 실행
execute immediate 'create table a (col1 varchar(10))';
end;
/
set serveroutput off
set serveroutput on
declare
sql_stmt varchar2(2000);
begin
--Dynamic SQL 기법
--문자열을 SQL문 처럼 실행
sql_stmt := 'create table b (col1 varchar(10))';
execute immediate sql_stmt;
--문자열 실행문을 변수처럼 가져올 수 있음
end;
/
set serveroutput off
set serveroutput on
declare
sql_stmt varchar2(2000);
begin
--Dynamic SQL 기법
--문자열을 SQL문 처럼 실행
--execute immediate 'create table a (col1 varchar(10))';
sql_stmt := 'drop table b purge';
execute immediate sql_stmt;
--테이블 지우기
end;
/
set serveroutput off
3.
set serveroutput on
declare
sql_stmt varchar2(2000);
begin
--Dynamic SQL 기법
--문자열 작은따옴표 escape
sql_stmt := 'insert into dept values(90,'||'''개발'''||', '||'''부산'''||')';
dbms_output.put_line(sql_stmt);
execute immediate sql_stmt;
end;
/
set serveroutput off
4.
set serveroutput on
declare
sql_stmt varchar2(2000);
sql_stmt2 varchar2(2000);
dept_id number(2) := 91;
dept_name varchar(14) := '총무';
dept_loc varchar(13) := '서울';
begin
--statement 기법 :문자열으로 sql문을 만드는 것
sql_stmt := 'insert into dept values('||dept_id||','''||dept_name||''','''||dept_loc||''')';
sql_stmt2 := 'insert into dept values('||dept_id||','||''''||dept_name||''''||','||''''||dept_loc||''''||')';
dbms_output.put_line(sql_stmt);
dbms_output.put_line(sql_stmt2);
--execute immediate sql_stmt;
end;
/
set serveroutput off
5.
set serveroutput on
declare
sql_stmt varchar2(2000);
dept_id number(2) := 91;
dept_name varchar(14) := '총무';
dept_loc varchar(13) := '서울';
begin
--prepared statement 기법
--미완성 문자열을 가지고 변수를 넣어줌 자바에 printf와 비슷
sql_stmt := 'insert into dept values(:1,:2,:3)';
execute immediate sql_stmt
using dept_id, dept_name, dept_loc;
end;
/
set serveroutput off
6.
--사원 이름을 통해서 사원 정보 출력
set verify off
set serveroutput on
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,
v_deptno emp2.deptno%type
);
emp_record emp_record_type;
g_ename emp2.ename%type := upper('&p_ename');
begin
--select 문장에 cursor가 없으면 반드시 한개의 데이터만 가져옴
select empno, ename, sal, deptno
into emp_record
from emp2
where ename = g_ename;
dbms_output.put_line('사원번호 :' || emp_record.v_empno);
dbms_output.put_line('사원급여 :' || emp_record.v_sal);
dbms_output.put_line('부서번호 :' || emp_record.v_deptno);
end;
/
set serveroutput off
set verify on
7.
SQL> create table emp2 as select * from emp;
SQL> insert into emp2 select * from emp where deptno=10;
--(예외처리)사원 이름을 통해서 사원 정보 출력
set verify off
set serveroutput on
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,
v_deptno emp2.deptno%type
);
emp_record emp_record_type;
g_ename emp2.ename%type := upper('&p_ename');
begin
--select 문장에 cursor가 없으면 반드시 한개의 데이터만 가져옴
select empno, ename, sal, deptno
into emp_record
from emp2
where ename = g_ename;
dbms_output.put_line('사원번호 :' || emp_record.v_empno);
dbms_output.put_line('사원급여 :' || emp_record.v_sal);
dbms_output.put_line('부서번호 :' || emp_record.v_deptno);
--예외 처리
exception
when no_data_found then
dbms_output.put_line('자료가 없습니다');
when too_many_rows then
dbms_output.put_line('2개 이상의 자료는 출력할 수 없습니다');
when others then
dbms_output.put_line('기타 에러입니다');
end;
/
set serveroutput off
set verify on
8.
SQL> drop table emp2 purge;
SQL> create table emp2 as select empno, ename, sal, deptno from emp;
SQL> alter table emp2 add constraint emp2_ename_uk unique(ename);
set verify off
set serveroutput on
accept p_empno prompt '사원번호 입력:'
accept p_ename prompt '사원이름 입력:'
accept p_sal prompt '사원급여 입력:'
accept p_deptno prompt '부서번호 입력:'
declare
v_empno emp2.empno%type := &p_empno;
v_ename emp2.ename%type := upper('&p_ename');
v_sal emp2.sal%type := &p_sal;
v_deptno emp2.deptno%type := &p_deptno;
begin
insert into emp2
values (v_empno, v_ename, v_sal, v_deptno);
dbms_output.put_line('입력 완료');
exception
--입력값 중복 에러
when dup_val_on_index then
dbms_output.put_line('&p_ename' || '은 중복되었습니다');
dbms_output.put_line('코드: ' || to_char(sqlcode));
--에러 코드 번호
dbms_output.put_line('내용: ' || sqlerrm);
--에러코드 내용
end;
/
set serveroutput off
set verify on
9.
--서버 에러의 이름 선언
--foriegn key를 지울수 없는 에러는 에러명이 설정되어 있지 않아서 이름을 만들어 줌
SET VERIFY OFF
SET SERVEROUTPUT ON
ACCEPT p_ename PROMPT '삭제하고자 하는 사원의 이름을 입력하시오 : '
DECLARE
v_ename emp.ename%TYPE := '&p_ename';
v_deptno dept.deptno%TYPE;
emp_constraint EXCEPTION;
PRAGMA EXCEPTION_INIT (emp_constraint, -2292);
--에러코드 -2292번은 에러명이 설정되지 않았음
BEGIN
SELECT deptno
INTO v_deptno
FROM emp
WHERE ename = UPPER(v_ename);
DELETE dept
WHERE deptno = v_deptno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('&p_ename' || '는 자료가 없습니다.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('&p_ename' || '는 자료가 여러개 있습니다.');
WHEN emp_constraint THEN
DBMS_OUTPUT.PUT_LINE('&p_ename' || '는 삭제할 수 없습니다.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('기타 에러입니다.');
END;
/
SET VERIFY ON
SET SERVEROUTPUT OFF
10.
--에러 강제 만들기
SET VERIFY OFF
SET SERVEROUTPUT ON
ACCEPT p_deptno PROMPT '부서명 : '
DECLARE
v_deptno emp.deptno%type := &p_deptno;
emp_deptno_ck EXCEPTION;
BEGIN
--부서명이 10,20,30 중 하나를 입력하지 않으면 에러처리함
IF v_deptno not in (10,20,30) THEN
RAISE emp_deptno_ck;
ELSE
dbms_output.put_line('정상 처리');
END IF;
EXCEPTION
WHEN emp_deptno_ck THEN
dbms_output.put_line('입력오류');
WHEN others THEN
dbms_output.put_line('기타오류');
END;
/
SET VERIFY ON
SET SERVEROUTPUT OFF
11.
--프로시저 프로그램을 내장함. 실행속도가 더 빠름
create or replace procedure tel1
is --( =as, declare)
v_tel varchar2(10) := '123456';
begin
v_tel := substr(v_tel,1,3) || '-' || substr(v_tel,4);
dbms_output.put_line('전화번호:' || v_tel);
end;
/
--프로시저 조회
desc user_procedures;
select object_name, procedure_name from user_procedures;
select text from user_source where name =upper('tel1');
--프로시저 실행
SQL> set serveroutput on
SQL> execute tel1;
SQL> set serveroutput off
--프로시저 실행 다른방법
SQL> set serveroutput on
SQL> exec tel1;
SQL> call tel1();
--함수()처럼 사용가능함 = 입출력이 가능함
SQL> set serveroutput off
--main01파일에 프로시저 실행문 넣음
set serveroutput on
begin
tel1;
tel1();
end;
/
set serveroutput off
12.
--프로시저 외부 데이터 받기
create or replace procedure tel2 (
p_tel in varchar2
--입력받을 변수의 varchar size 정하지 않음
)
is
v_tel varchar2(10);
begin
v_tel := substr(p_tel,1,3) || '-' || substr(p_tel,4);
dbms_output.put_line('전화번호:' || v_tel);
end;
/
--main03 프로시저 실행 구문
set serveroutput on
begin
tel2(1234567);
tel2(4567890);
tel2(1234321);
end;
/
set serveroutput off
13.
--프로시저 내보내기
create or replace procedure tel3 (
p_tel out varchar2
--연산 처리결과를 p_tel로 내보냄
)
is
v_tel varchar2(10) := '123456';
begin
p_tel := substr(v_tel,1,3) || '-' || substr(v_tel,4);
end;
/
--SQL PLUS에서 프로시저 리턴값을 받을 변수를 만들고 프린트함
SQL> variable g_tel varchar2(20)
SQL> @c:\oracle\proc04
SQL> exec tel3(:g_tel)
SQL> print :g_tel
--main04 프로시저의 리턴값 받아서 프린트하는 것 실행구문
set serveroutput on
declare
v_tel varchar2(20);
begin
tel3(p_tel => v_tel);
dbms_output.put_line('결과 :' || v_tel);
end;
/
set serveroutput off
14.
--구구단 프로시저 만들기
create or replace procedure gugu (
p_dan in number
)
is
v_dan varchar(30);
begin
dbms_output.put_line(p_dan||'단');
FOR i in 1..9 LOOP
v_dan := p_dan || '*' || i || '=' || p_dan*i;
dbms_output.put_line(v_dan);
END LOOP;
dbms_output.put_line(chr(7));
end;
/
--main05 구구단 실행구문
set serveroutput on
begin
gugu(1);
gugu(2);
end;
/
set serveroutput off
15.
--구구단 프로시저 입력받아서 내보내기
create or replace procedure gugu (
p_dan in number,
v_result out varchar2
)
is
v_tot number;
begin
FOR i_col in 1..9 LOOP
v_tot := p_dan * i_col;
v_result := v_result || p_dan || '*' || i_col || '=' ||v_tot || ' ';
END LOOP;
end;
/
--SQL PLUS에서의 구구단 프로시저 실행문
SQL> @c:\oracle\proc05
SQL> variable g_result varchar2(100);
SQL> exec gugu(8, :g_result);
SQL> print :g_result;
--PL/SQL에서의 main05 구구단 실행구문
set serveroutput on
declare
g_result varchar2(2000);
begin
gugu(8, v_result => g_result);
dbms_output.put_line(g_result);
end;
/
set serveroutput off
16.
--사원의 이름을 입력받아 속한 부서에 최대 급여와 최소 급여를 출력하는 프로시저 생성
create or replace procedure sawon (
p_ename in varchar2,
v_result out varchar2
)
is
v_hisal number;
v_losal number;
v_deptno number;
begin
select deptno
into v_deptno
from emp
where ename= upper(p_ename);
select max(sal), min(sal)
into v_hisal, v_losal
from emp
group by deptno
having deptno=v_deptno;
v_result := '최대급여: '|| v_hisal ||' 최소급여:' || v_losal ;
end;
/
--SQL PLUS에서 실행문
SQL> @c:\oracle\proc06
SQL> set serveroutput on
SQL> variable g_result varchar2(2000);
SQL> exec sawon('scott', :g_result);
SQL> variable g_result varchar2(2000);
SQL> exec sawon('scott', :g_result);
SQL> set serveroutput off
17.
--지정한 테이블 입력받아 그 외의 테이블 삭제 프로시저 생성, 삭제 테이블 목록 출력
create or replace procedure droptab (
p_tab in varchar2,
v_result out varchar2
)
is
--삭제하면 안되는 테이블 명을 담을 nested table 타입을 정함
type varchar_table_type is table of varchar2(30)
index by binary_integer;
v_tab varchar_table_type;
v_bol number;
v_del varchar2(2000);
begin
FOR tab_record in (select tname from tab) LOOP
--현재 있는 테이블을 CURSOR로 가져와서 하나하나씩 비교를 위해 꺼냄
v_bol := 1;
FOR i in 1..regexp_count(p_tab,',')+1 LOOP
--쉼표를 기준으로 입력된 문자열을 잘라서 nested table에 넣음
--LOOP횟수는 쉼표+1 / 대문자 처리 /스페이스바를 없앰
v_tab(i) := regexp_replace(regexp_substr(upper(p_tab), '[^,]+',1,i),' ','');
IF tab_record.tname = v_tab(i) THEN
-- 테이블이 nested table에 있을경우 -1을 곱함
v_bol := v_bol*(-1);
END IF;
END LOOP;
IF v_bol=-1 THEN
-- bol 값이 -1이면 데이터를 보존함
dbms_output.put_line('보존 : ' || tab_record.tname);
ELSE
-- bol 값이 1이면 데이터를 삭제하고, 삭제테이블 목록에 추가함
v_del := v_del || tab_record.tname || chr(9);
execute immediate 'drop table '|| tab_record.tname;
END IF;
END LOOP;
dbms_output.put_line('삭제완료');
--삭제 테이블 목록을 리턴값으로 정함
v_result := '삭제데이터 : ' ||v_del;
end;
/
