PL/SQL
<기본 형식>
declare
-변수를 정의
begin
-SQL구문 작성
-출력구문 작성 --> 쿼리문의 수행결과를 반드시 출력함수를 통해서 확인해야 한다.
exception
-예외 처리 구문
end;
<변수 및 상수 선언>
- decleare 선언부 블럭내에서만 가능
- 상수 선언시 초기값 필수 할당
- 변수 미 할당시 데이터타입 상관없이 초기값 null 로 지정
- 변수 :
변수명 데이터타입 := 초기값;
ex_num numnber := 1;
ex_str varchar(10) := 'str1';
- 상수 :
상수명 constrant 데이터타입 := 상수값;
ex_num constant number := 1;
- 출력 예 :
DECLARE
EX_NUM CONSTANT NUMBER := 10; --상수 선언
EX_STR VARCHAR2(10); --변수 선언
BEGIN
EX_STR := 'EXAMPLE'; --변수 초기값 설정
DBMS_OUTPUT.PUT_LINE(EX_NUM); --상수 출력
DBMS_OUTPUT.PUT_LINE(EX_STR); --변수 출력
END;
10
EXAMPLE
<데이터 조회>
- 초기세팅
set serveroutput on;
- 출력 명령어
DBMS_OUTPUT.PUT_LINE(변수,상수 명)
- 출력 예시
begin
dbms_output.put_line('Hello World'); --출력함수 (자바의 sysout구문과 같음.)
end;
<PL/SQL 구문 변수 활용과 선언 및 초기화 >
declare
-- vempno number(4); --변수의 선언
-- vename varchar2(10);
vempno constant number(4) := 7777; --변수의 선언과 동시에 초기화
vename varchar2(10) not null := 'SCOTT'; --not null을 붙이면 null값을 변수의 값으로 사용할 수 없다.
begin
-- vempno := 7777; --변수의 초기화 / := 콜론는 은 같이 붙여서 사용해야 한다.
-- vename := 'SCOTT';
dbms_output.put_line('사원 / 이름');
dbms_output.put_line(vempno || ' ' || vename);
end;
<PL/SQL 구문 변수 활용과 선언 및 초기화 >
DECRARE
1. 단순변수
1) 스칼라 방식 : 단일 값을 가지는 변수의 데이터형을 직접 지정해주는 변수
Identifier [constant] datatype [not null] [:=default expr];
- identifier : 변수명
- constant : 상수 선언 (읽기전용 변수), 초기값 반드시 지정, 값이 없을 경우 생략
- datatype : %type, %rowtype 등
- not null : 초기값 반드시 지정, 생략가능
- := 또는 default : 변수에 기본값 할당
- expr : 변수에 부여할 기본값
-주요 스칼라 변수의 데이터 타입
CHAR(최대길이) | 고정길이의 문자 (최소값 : 1) |
VARCHAR2(최대길이) | 가변 길이의 문자 (기본값 없음) |
NUMBER(전체자리수, 소수점이하 자리수) | 전체자리수와 소수점 이하의 자리수를 가진 숫자 전체 범위 : 1 ~ 38 소수점 이하 자리수 범위 : -84 ~ 127 |
BINARY_INTEGER | -2,147,483,647 ~ 2,147,483,647 사이의 정수 |
PLS_INTEGER | -2,147,483,647 ~ 2,147,483,647 사이의 부호있는 정수 NUMBER 보다 저장공간이 적게 필요하고 연산속도가 빠름 |
예시)
Vno number(5);
Vname varchar2(10);
Vday date;
2) 레퍼런스 방식 (참조 변수) : 변수의 데이터 형을 다른 컬럼에서 참조 후 지정하는 방식
저장되어야 할 정확한 데이터 형태를 모를경우 해당 데이터가 들어있는 컬럼의 정보를 참조
Vno emp.empno%TYPE - emp테이블의 empno와 동일한 데이터형으로 선언
Vname emp.ename%TYPE - emp테이블의 ename과 동일한 데이터형으로 선언
Vrow emp%ROWTYPE -emp테이블의 여러컬럼을 한번에 저장할 변수로 선언.
- %TYPE : 기존 테이블의 컬럼의 타입을 참조한다.
- %ROWTYPE : 예로 tno number, tname varchar2(10), tday date로 이루어진 test 테이블이 있는 경우 test%ROWTYPE 으로 선언시 하나의 변수에 세가지 (number, varchar2(10), date) 를 모두 저장 할 수 있는 변수로 선언된다.
-예시
declare
--vempno number(4); --스칼라 방식
vempno emp.empno%type := 7777; --레퍼런스 방식
begin
--vempno := 7777;
dbms_output.put_line(vempno);
end;
<레퍼런스 방식 활용>
-제약이 크나 PL/SQL 구문이 주는 장점이 있음.
-%TYPE
declare
vempno emp.empno%type;
vename emp.ename%type;
begin --try 영역
select empno,ename
into vempno,vename --empno,ename 을 vempno,vename에 넣어준다.
from emp;
--where empno = 7788;
--select구문을 PL/SQL 구문에 쓸때는 into 절과 where절이 필수로 반드시 들어와야 한다.
dbms_output.put_line('사번 / 이름');
dbms_output.put_line(vempno || ' ' || vename);
exception --catch 영역 --where절이 없을 경우, / when-then 구문을 쓴다.
when TOO_MANY_ROWS then dbms_output.put_line('행의 수가 여러 개 입니다.');
when OTHERS then dbms_output.put_line('모든 예외에 대한 처리');
end;
-%ROWTYPE
declare
v_row emp2%ROWTYPE
Begin
Select * into v_row
From emp3
Where melon = 7300;
2. 복합변수
1) Table Type
- 사용자 정의 변수 타입
- 배열의 형식과 같음
<형식>
TYPE type_name IS TABLE OF
columntype [not null]
[index by binary_interger];
- type_name : 동일 유형의 데이터 또는 데이터 구조를 하나의 연속적인 메모리 공간에 확보
- index by : 배열 내의 요소에 접근하기 위한 위치값으로 사용
-사용 예시 1)
사원번호가 7999인 사원의 이름을 조회해 해당 변수에 저장한 후 출력 (Table_type명 : tbl_emp_name)
set serveroutput on;
declare
t_name varchar2(20);
TYPE tbl_emp_name IS TABLE OF --table type 변수 생성
emp.ename%TYPE
INDEX BY BINARY_INTEGER;
v_name tbl_emp_name; --table type 선언
begin
select ename
into t_name
from emp
where empno=7999;
v_name(0) := t_name; --t_name에 있던 사원 ename을 v_name의 1번째 칸에 할당.
dbms_output.put_line(v_name(0);
end;
-사용 예시2)
- for 반복문 사용
- emp 테이블에 있는 사원의 이름과 직업 추출하기
declare
--vename varchar2(10)
TYPE empno_table_type IS TABLE OF emp.empno%type
INDEX BY BINARY_INTEGER;
TYPE ename_table_type IS TABLE OF emp.ename%type
INDEX BY BINARY_INTEGER;
TYPE job_table_type IS TABLE OF emp.job%type
INDEX BY BINARY_INTEGER;
TYPE mgr_table_type IS TABLE OF emp.mgr%type
INDEX BY BINARY_INTEGER;
TYPE hiredate_table_type IS TABLE OF emp.hiredate%type
INDEX BY BINARY_INTEGER;
TYPE sal_table_type IS TABLE OF emp.sal%type
INDEX BY BINARY_INTEGER;
TYPE comm_table_type IS TABLE OF emp.comm%type
INDEX BY BINARY_INTEGER;
TYPE deptno_table_type IS TABLE OF emp.deptno%type
INDEX BY BINARY_INTEGER;
empnoArr empno_table_type;
enameArr ename_table_type; --배열 형식의 변수 선언
jobArr job_table_type; --배열 형식의 변수 선언
mgrArr mgr_table_type;
hiredateArr hiredate_table_type;
salArr sal_table_type;
commArr comm_table_type;
deptnoArr deptno_table_type;
i BINARY_INTEGER := 0; --변수 i 선언
begin --반복문 사용
for k in (select * from emp) loop
i := i + 1; --index번호는 자바 0번 시작과 달리 1번부터 사용된다.
empnoArr(i) := k.empno;
enameArr(i) := k.ename; --select구문에서 조회된 k.ename , k.job
jobArr(i) := k.job;
mgrArr(i) := k.mgr;
hiredateArr(i) := k.hiredate;
salArr(i) := k.sal;
commArr(i) := k.comm;
deptnoArr(i) := k.deptno;
end loop;
for j in 1..i loop
dbms_output.put_line(empnoArr(j) || ' / ' || enameArr(j) || ' / ' || jobArr(j) || ' / ' || mgrArr(j) || ' / ' || hiredateArr(j) || ' / ' || salArr(j) || ' / ' || commArr(j) || ' / ' || deptnoArr(j));
end loop;
end;
/
2) Record Type
- 사용자 정의 변수 타입
- 배열의 형식과 같음
declare
TYPE emp_record_type IS RECORD(
v_empno emp.empno%type,
v_ename emp.ename%type,
v_job emp.job%type,
v_deptno emp.deptno%type
);
emp_record emp_record_type; --레코드 타입의 변수 선언;
begin
--레코드 type(여러개의 변수를 묶어서 사용한다) => 사용자 정의 변수 타입
--클래스랑 유사하다.
select empno,ename,job,deptno
into emp_record
from emp
where empno = 7788;
dbms_output.put_line(emp_record.v_empno || ' ' || emp_record.v_ename || ' ' || emp_record.v_job || ' ' || emp_record.v_deptno);
end;
<데이터 생성 및 삽입 >
create table dept_record
as
select * from dept;
declare
TYPE rec_dept IS RECORD(
v_deptno dept_record.deptno%type,
v_dname dept_record.dname%type,
v_loc dept_record.loc%type
);
dept_rec rec_dept; --변수와 테이블명이 같으면 오류남.
begin
dept_rec.v_deptno := 50;
dept_rec.v_dname := 'DEV';
dept_rec.v_loc := 'BUSAN';
insert into dept_record
values dept_rec; --values에 (괄호)는 레코드문에서는 안써야 한다.
end;
select * from dept_record;
<UPDATE>
declare
TYPE rec_dept IS RECORD(
v_deptno dept_record.deptno%type not null := 99,
v_dname dept_record.dname%type,
v_loc dept_record.loc%type
);
dept_rec rec_dept;
begin
dept_rec.v_deptno := 50;
dept_rec.v_dname := 'INSA';
dept_rec.v_loc := 'SEOUL';
update dept_record
set dname = dept_rec.v_dname , loc = dept_rec.v_loc
where deptno = dept_rec.v_deptno;
end;
select * from dept_record;
<DELETE>
declare
v_deptno dept_record.deptno%type := 50;
begin
delete from dept_record
where deptno = v_deptno;
end;
<조건문>
1) IF문
-if then end if
-if then else end if
-if then elsif then end if
declare
vempno number(4);
vename varchar2(10);
vdeptno varchar2(10);
vdname varchar2(10) := null ;
begin
select empno,ename,deptno
into vempno,vename,vdeptno
from emp
where empno = 7788;
--oracle에서의 if문 특성
-- if( 조건식 ) then
-- 실행문
-- end if ;
if( vdeptno = 10 ) then
vdname := 'AAA';
end if ;
if( vdeptno = 20 ) then
vdname := 'BBB';
end if ;
if( vdeptno = 30 ) then
vdname := 'CCC';
end if ;
if( vdeptno= 40 ) then
vdname := 'DDD';
end if ;
dbms_output.put_line('부서명 : ' || vdname);
end;
-문제 1-1 if-then / end if 문
declare
vemp emp%rowtype;
annsal number(7,2);
begin
dbms_output.put_line('사번 / 이름 / 연봉');
dbms_output.put_line('------------- ');
select *
into vemp
from emp
where empno = 7788;
--Q1.
--해당 사원의 연봉을 출력하라. 단, 커미션이 null인 경우 0으로 출력하라.
--계산된 연봉을 변수 annsal에 넣어서 출력하라.
if( vemp.comm is null ) then
vemp.comm := 0;
end if ;
annsal := vemp.sal*12 + vemp.comm;
dbms_output.put_line('사번 : ' || vemp.empno ||' '|| '이름 : ' ||vemp.ename ||' '||'연봉 : ' || annsal ||'만원');
end;
-문제 1-2 if-then / else if 문
declare
vemp emp%rowtype;
annsal number(7,2);
begin
dbms_output.put_line('사번 / 이름 / 연봉');
dbms_output.put_line('------------- ');
select *
into vemp
from emp
where empno = 7788;
if( vemp.comm is null ) then
annsal := vemp.sal*12;
else
annsal := vemp.sal * 12 + vemp.comm;
end if ;
dbms_output.put_line('사번 : ' || vemp.empno ||' '|| '이름 : ' ||vemp.ename ||' '||'연봉 : ' || annsal ||'만원');
end;
-다중 if문
declare
vemp emp %rowtype;
vdname varchar2(10);
begin
select *
into vemp
from emp
where empno = 7788;
if(vemp.deptno = 10) then
vdname := 'AAA';
elsif (vemp.deptno = 20) then --elsif =else if 의 오라클 특성
vdname := 'BBB';
elsif (vemp.deptno = 30) then
vdname := 'CCC';
elsif (vemp.deptno = 40) then
vdname := 'DDD';
end if;
dbms_output.put_line(vdname);
end;
<반복문>
-loop end loop;
-for in loop end loop;
-for in reverse loop end loop;
-while loop end loop;
1) LOOP문
- 형식
loop
실행문( 무한 반복문 )
무한 반복문의 제어
1. EXIT WHEN 조건식;
2. IF THEN END IF;
end loop;
- 예시)
declare
n number := 1;
begin
loop
dbms_output.put_line(n);
n := n +1;
exit when n > 10; --반복문을 멈추기 위한 조건
end loop;
end;
2) FOR문
-형식
for 증감변수 in 초기값..최종값
loop
처리문;
end loop;
- 예시)
declare
--변수 선언 필요 없음
begin
--in 구문 뒤에 작성되는 값이 반복의 횟수를 결정한다.
--in : 증가
for n in 1..10
loop
dbms_output.put_line(n);
end loop;
end;
-in 뒤에있는 값은 시작값..끝값 1씩 증가 / 1~10까지 10회 반복하는 것.
2) FOR문-reverse (for문이 거꾸로 시작)
begin
--in reverse : 감소
for n in reverse 1..10 loop --in 뒤에있는 값은 시작값..끝값 1씩 감소
dbms_output.put_line(n);
end loop;
end;
declare
vdept dept %rowtype;
begin
for n in 1..4 loop
select *
into vdept
from dept
where deptno = 10 * n ; --where절이 없으면 오류로 판단한다.
dbms_output.put_line(vdept.deptno || '/ ' || vdept.dname || '/ ' || vdept.loc);
end loop;
end;
3) WHILE문
-형식
declare
n number := 1;
begin
while (조건식)
loop
end loop;
end;
- 예시
declare
n number := 1;
begin
while (n <= 10) loop
dbms_output.put_line(n);
n := n + 1;
end loop;
end;
https://rosebud90.tistory.com/entry/15-PLSQL-%EB%B3%80%EC%88%98
--정리
--스칼라 방식
--레퍼런스 방식
-- 1. emp.empno%type
-- 2. emp%rowtype
--사용자 정의 변수 타입
-- 1. 테이블 type
-- -TYPE empno_table_type IS TABLE OF emp.empno%type
-- -INDEX BY BINARY_INTEGER;
-- 2. 레코드 type
-- -TYPE rec_dept IS RECORD(
-- -v_deptno dept_record.deptno%type not null := 99,
-- -v_dname dept_record.dname%type,
-- -v_loc dept_record.loc%type
-- - );
%ROWTYPE
declare
--%ROWTYPE : 테이블의 모든 컬럼의 이름과 변수를 참조하겠다.
--컬럼명이 변수명으로 사용되고 컬럼의 타입을 변수의 타입으로 사용한다.
vemp emp%rowtype;
begin
select *
into vemp --%rowtype으로 정의된 타입
from emp
where empno = 7788;
dbms_output.put_line(vemp.empno); --empno : 7788을 가진 사번 정보 조회
dbms_output.put_line(vemp.ename);
dbms_output.put_line(vemp.job);
dbms_output.put_line(vemp.mgr);
dbms_output.put_line(vemp.hiredate);
dbms_output.put_line(vemp.sal);
dbms_output.put_line(vemp.comm);
dbms_output.put_line(vemp.deptno);
end;
'oracle' 카테고리의 다른 글
1021_데이터 사전 (0) | 2022.10.31 |
---|---|
1027_저장 프로시저 (0) | 2022.10.30 |
1026_시퀀스 (0) | 2022.10.26 |
1024_제약조건(foreign key, check, default) (0) | 2022.10.26 |
뷰 (View) (0) | 2022.10.26 |