복습)
desc hr.employees;
declare
v_name varchar2(30); -- hard coding (변화되지 않은(크기) 케이스로 코딩)
v_sal number;
begin
select last_name, salary
into v_name, v_sal
from hr.employees
where employee_id = 100;
dbms_output.put_line(v_name||' '||v_sal);
end;
/
▶ 변수를 선언할 때 꼭 타입을 맞추는 습관을 가지자.
▶ 변수 설정하기 전에 desc hr.employees;로 테이블 변수 어떤 타입인지 확인해 보기
ex)
desc hr.employees;
declare
v_name hr.employees.last_name%type; -- soft coding
v_sal hr.employees.salary%type;
begin
select last_name, salary
into v_name, v_sal
from hr.employees
where employee_id = 100;
dbms_output.put_line(v_name||' '||v_sal);
end;
/▶ 타입의 크기를 유연하게 바꾸기 위해서 %type을 쓰는 게 유지관리하기에도 좋다.
ex) 사원값을 입력값으로 처리하고 싶을 경우
var b_id number
execute :b_id := 100;
declare
v_name varchar2(30);
v_sal number;
begin
select last_name, salary
into v_name, v_sal
from hr.employees
where employee_id = :b_id;
dbms_output.put_line(v_name||' '||v_sal);
end;
/
▶ 입력변수 처리하고 싶을 때는 바인드 변수를 선언해주고, execute를 통해 바인드변수에 값을 할당해서 where절에 변수를 적어주면 된다.
▶ 같이 쓰는 사람들이 실행계획을 쉐어링 할 수 있게 하기 위해서 입력처리하는 게 좋다.
ex)
declare
v_name varchar2(30);
v_sal number;
v_date hr.employees.hire_date%type;
begin
select last_name, salary, hire_date
into v_name, v_sal, v_date
from hr.employees
where employee_id = :b_id;
dbms_output.put_line('이름 : '||upper(v_name));
dbms_output.put_line('급여 : '||to_char(v_sal, 'l999,999.00'));
dbms_output.put_line('입사일 : '||to_char(v_date, 'yyyy-mm-dd'));
end;
/
▶ 급여의 공백을 지우고, 날짜에 년 월 일을 붙여서 출력하고 싶을 경우
declare
v_name varchar2(30);
v_sal number;
v_date hr.employees.hire_date%type;
begin
select last_name, salary, hire_date
into v_name, v_sal, v_date
from hr.employees
where employee_id = :b_id;
dbms_output.put_line('이름 : '||upper(v_name));
dbms_output.put_line('급여 : '||trim(to_char(v_sal, 'l999,999.00')));
dbms_output.put_line('입사일 : '||to_char(v_date, 'yyyy"년"-mm"월"-dd"일"'));
end;
/
▶월에 0을 없애고 싶을 경우 (fm사용)
declare
v_name varchar2(30);
v_sal number;
v_date hr.employees.hire_date%type;
begin
select last_name, salary, hire_date
into v_name, v_sal, v_date
from hr.employees
where employee_id = :b_id;
dbms_output.put_line('이름 : '||upper(v_name));
dbms_output.put_line('급여 : '||trim(to_char(v_sal, 'l999,999.00')));
dbms_output.put_line('입사일 : '||to_char(v_date, 'yyyy"년"-fmmm"월"-dd"일'));
end;
/
# 암시적 커서에서 DML 작업
1) 테이블 생성
create table hr.test(id number, name varchar2(20), day date);
desc hr.test;
2) 데이터 넣기
insert into hr.test(id, name, day)
values(1, '홍길동', to_date('2024-12-23', 'yyyy-mm-dd'));
3) 프로그램에 적용
begin
insert into hr.test(id, name, day)
values(1, '홍길동', to_date('2024-12-23', 'yyyy-mm-dd'));
-- commit;
-- rollback;
end;
/
select * from hr.test;
-- commit;
-- rollback;▶ 프로그램 안에서 트랜잭션이 돌고 있을 때 꼭 프로그램 내에서 롤백, 커밋을 할지 프로그램 밖에서 커밋, 롤백을 할지 꼭 정해줘야 한다.
ex)
var b_id number
var b_nmae varchar2(20)
var b_day date
begin
insert into hr.test(id, name, day)
values(:b_id, :b_name, :b_day);
end;
/
select * from hr.test;
▶ 바인드변수 중에 date 타입은 없기 때문에 문자 타입으로 해줘야 한다.
ex)
var b_id number
var b_nmae varchar2(20)
var b_day varchar2(20)
begin
insert into hr.test(id, name, day)
values(:b_id, :b_name, to_date(:b_day,'yyyy-mm-dd'));
end;
/
select * from hr.test;▶ values절에 to_date를 통해 형을 맞춰줘야 한다.
# 데이터를 넣고 싶을 경우 (디벨로퍼에서 실행하고 싶을 경우)
# 데이터를 넣고 싶을 경우 (sqlplus에서 실행하고 싶을 경우)
exec :b_id := 4
exec :b_name := '이승엽'
exec :b_day := '20211231'▶ execute를 exec로 단축해서 써도 된다.
ex)
insert into hr.test(id, name, day)
select employee_id, last_name, hire_date
from hr.employees;
select * from hr.test;
rollback;
begin
insert into hr.test(id, name, day) -- 출력 1
select employee_id, last_name, hire_date
from hr.employees;
end;
/
# 만약 위에 출력처럼 말고 로우 수를 화면에 출력하고 싶을 경우
begin
insert into hr.test(id, name, day)
select employee_id, last_name, hire_date
from hr.employees;
dbms_output.put_line(sql%rowcount||'행이 입력 되었습니다.');
commit; -- 안에다 해도 똑같다.
end;
/
# 암시적 커서에 대한 속성 (암시적 select into절엔 쓰지 말고, DML 작업 시 사용할 것)
- sql%rowcount : 가장 최근의 DML문의 의해 영향을 받은 행의 수를 리턴하고 싶을 경우 쓴다, 그냥 select에서 쓰는 것은 의미가 없지만 DML 작업을 할 경우 쓰는 게 좋다.
- sql%found : 가장 최근의 DML문에 의해 하나 이상의 행에 영향을 준 경우 TRUE, 영향을 준 게 없으면 FALSE (boolean)
- sql%not found : 가장 최근의 DML문에 의해 영향을 받은 행이 없으면 TRUE, 있으면 FALSE로 평가되는 (boolean)속성
ex)
begin
update hr.test
set name = '홍길동'
where id = 300;
end;
/
▶ 몇 건이 업데이트 됐는지 알 수가 없다.
ex)
begin
update hr.test
set name = '홍길동'
where id = 300;
dbms_output.put_line(sql%rowcount||'행이 수정되었습니다.');
end;
/
▶ sql%rowcount를(암시적 커서 속성) 이용해서 몇 개가 수정이 됐는지 알 수도 있다.
ex)
begin
update hr.test
set name = '홍길동'
where id = 100;
dbms_output.put_line(sql%rowcount||'행이 수정되었습니다.');
rollback;
end;
/
ex)
begin
update hr.test
set name = '홍길동'
where id = 100;
if sql%found then
dbms_output.put_line(sql%rowcount||'행이 수정되었습니다.');
else
dbms_output.put_line('사원이 존재하지 않습니다.');
end if;
rollback;
end;
/▶ sql%rowcount 이전에 영향이 있으면 TRUE, 없으면 else를 만나서 출력한다.
ex)
begin
update hr.test
set name = '홍길동'
where id = 300; -- 없는 사원을 넣을 경우
if sql%found then
dbms_output.put_line(sql%rowcount||'행이 수정되었습니다.');
else
dbms_output.put_line('사원이 존재하지 않습니다.');
end if;
rollback;
end;
/
▶ 이전의 영향을(300번 사원) 받지 않았기 때문에 없는 데이터여서 else를 타고 출력이 된 것이다.
ex)
begin
update hr.test
set name = :b_name
where id = :b_id;
if sql%found then
dbms_output.put_line(sql%rowcount||'행이 수정되었습니다.');
else
dbms_output.put_line('사원이 존재하지 않습니다.');
end if;
rollback;
end;
/
▶ 변수 처리해서 실행할 수도 있다.
ex) 2003년 이전에 입사한 사원들 보고 싶을 경우
select *
from hr.test
where day < to_date('2003-01-01', 'yyyy-mm-dd');
ex) delete
begin
delete from hr.test where day < to_date('2003-01-01', 'yyyy-mm-dd');
if sql%found then
dbms_output.put_line(sql%rowcount||'행이 삭제되었습니다.');
else
dbms_output.put_line('사원이 존재하지 않습니다.');
end if;
rollback;
end;
/
ex)
begin
delete from hr.test where day < to_date('2000-01-01', 'yyyy-mm-dd');
if sql%found then
dbms_output.put_line(sql%rowcount||'행이 삭제되었습니다.');
else
dbms_output.put_line('사원이 존재하지 않습니다.');
end if;
rollback;
end;
/
▶ 2000년 이전에 입사한 사원이 있으면 행을 삭제하고 없기 때문에 else 타고 출력이 된 것이다.
문제) 사원번호를 입력값으로 받아서 근속연수가 20년 이상이면 10% 인상급여로 수정하는 프로그램을 생성해 주세요.
출력화면
ex) 100번 사원의 입사일은 XXXX 근속연수는 XXXX입니다.
100번 사원의 이전 급여는 24000, 수정된 급여는 26400입니다.
var b_id number -- id는 입력값을 받아야 하기때문에 바인드 변수 선언
declare
v_day hr.employees.hire_date%type;
v_sal_before hr.employees.salary%type;
v_sal_after hr.employees.salary%type;
v_years number;
begin
select hire_date, salary
into v_day, v_sal_before
from hr.employees
where employee_id = :b_id;
v_years := trunc(months_between(sysdate, v_day) / 12);
if v_years >= 20 then
update hr.employees
set salary = salary * 1.1
where employee_id = :b_id;
select salary
into v_sal_after
from hr.employees
where employee_id = :b_id;
dbms_output.put_line(:b_id||'번 사원의 이전 급여는 '||v_sal_before||', 수정된 급여는 '||v_sal_after||'입니다.');
else
dbms_output.put_line(:b_id||'번 사원은 근속연수가 20년 미만입니다.');
end if;
rollback;
end;
/
# sqlplus에서 실행하고 싶을 경우
exec :b_id := 100 -- 바인드변수 선언은 위에서 했기 때문에 생략 가능
declare
v_day hr.employees.hire_date%type;
v_sal_before hr.employees.salary%type;
v_sal_after hr.employees.salary%type;
v_years number;
begin
select hire_date, salary
into v_day, v_sal_before
from hr.employees
where employee_id = :b_id;
v_years := trunc(months_between(sysdate, v_day) / 12);
update hr.employees
set salary = salary * 1.1
where employee_id = :b_id;
select salary
into v_sal_after
from hr.employees
where employee_id = :b_id;
if v_years >= 20 then
dbms_output.put_line(:b_id||'번 사원의 입사일은 '||v_day||' 근속연수는 '||v_years||'년입니다.');
dbms_output.put_line(:b_id||'번 사원의 이전 급여는 '||v_sal_before||', 수정된 급여는 '||v_sal_after||'입니다.');
else
dbms_output.put_line(:b_id||'번 사원은 근속연수가 20년 미만입니다.');
end if;
rollback;
end;
/
★ 코드 짤 때 프로시저문에 select문 짜면 오류날 때 수정이 힘드므로 프로시저문 밖에서 실행이 되는지 확인을 하고 오류가 발생하지 않으면 프로시저문 안에 대입해서 코드 짜는 게 편하다.
▶ 여러 개의 SQL문장을 같이 실행할 경우에는 프로시저문 안에 작성을 해야 트래픽을 줄일 수 있다.
ex)
declare
v_dept_id hr.departments.department_id%type;
v_dept_name hr.departments.department_name%type;
v_dept_mgr hr.departments.manager_id%type;
v_dept_loc hr.departments.location_id%type;
begin
select *
into v_dept_id, v_dept_name, v_dept_mgr, v_dept_loc
from hr.departments
where department_id = 10;
dbms_output.put_line('부서번호 : '||v_dept_id);
dbms_output.put_line('부서이름 : '||v_dept_name);
dbms_output.put_line('부서장 : '||v_dept_mgr);
dbms_output.put_line('부서위치 : '||v_dept_loc);
end;
/
■ 조합데이터 유형
- 스칼라 유형(scalar data type) : 단일값을 보유하는 변수
- 스칼라 유형과 달리 다중값을 보유할 수 있다.
- 레코드(record) : 서로 다른 데이터 유형의 값을 저장
- 배열(array) : 동일한 데이터 유형의 값을 저장
1) index by table (연관배열)
2) nested table (중첩테이블)
3) varray
ex) 레코드 형식
declare
/* 레코드 타입 선언 */
type dept_record_type is record
(dept_id number,
dept_name varchar2(30),
dept_mgr number,
dept_loc number);
v_rec dept_record_type;
begin
select *
into v_rec
from hr.departments
where department_id = 10;
dbms_output.put_line('부서번호 : '||v_rec.dept_id);
dbms_output.put_line('부서이름 : '||v_rec.dept_name);
dbms_output.put_line('부서장 : '||v_rec.dept_mgr);
dbms_output.put_line('부서위치 : '||v_rec.dept_loc);
end;
/
▶ 하지만 컬럼의 수가 많아지면 코드가 길어지는 불편함이 있다.
ex) 레코드 타입 좀 더 쉽게 선언하는 방법
- %rowtype : 테이블 또는 뷰의 열에서 컬럼 및 데이터 타입을 가져온다.
1) 레코드 타입을 손쉽게 사용할 수 있다.
2) 테이블의 컬럼의 개수와 데이터 타입을 알 필요가 없으며 실제로 런타임에 변경된다는 이점이 있다.
3) select * 문 사용 시 많이 쓴다.
4) 행 레벨의 insert, update문 수행할 때 편하다.
declare
/* 레코드 타입 선언 */
v_rec hr.departments%rowtype;
begin
select *
into v_rec
from hr.departments
where department_id = 10;
dbms_output.put_line('부서번호 : '||v_rec.department_id);
dbms_output.put_line('부서이름 : '||v_rec.department_name);
dbms_output.put_line('부서장 : '||v_rec.manager_id);
dbms_output.put_line('부서위치 : '||v_rec.location_id);
end;
/
ex) record 응용
declare
type rec_type is record
(v_sal number,
v_minsal number default 1000,
v_hire_date hr.employees.hire_date%type,
v_rec hr.employees%rowtype);
v_myrec rec_type;
begin
v_myrec.v_sal := v_myrec.v_minsal + 500;
v_myrec.v_hire_date := sysdate;
select *
into v_myrec.v_rec
from hr.employees
where employee_id = 100;
dbms_output.put_line('이름 : '||v_myrec.v_rec.first_name);
dbms_output.put_line('근무연수 : '||trunc(months_between(v_myrec.v_hire_date, v_myrec.v_rec.hire_date)/12));
end;
/
▶ 필드 구성할 때 레코드 타입을 구성할 수 있다.(v_rec hr.employees%rowtype)
▶ v_myrec.v_sal := v_myrec.v_minsal + 500; : v_myrec래코드 타입 내의 v_sal 컬럼에다가 v_myrec.v_minsal + 500은 v_minsal은 위에서 defalut로 1000으로 설정했기 때문에 1500이 된다.
▶
ex) 퇴사 사원들의 정보를 담아두는 테이블
1) 테이블 생성
create table hr.retired_emp
(empno number,
ename varchar2(30),
job varchar2(10),
mgr number,
hiredate date,
leavedate date,
sal number,
comm number,
deptno number);desc hr.retired_emp;
2) 퇴사한 115번 사원의 데이터 넣고 조회
declare
v_emp_rec hr.employees%rowtype;
begin
select *
into v_emp_rec
from hr.employees
where employee_id = 115;
insert into hr.retired_emp(empno, ename, job, mgr, hiredate, leavedate, sal, comm, deptno)
values(v_emp_rec.employee_id, v_emp_rec.first_name, v_emp_rec.job_id,
v_emp_rec.manager_id, v_emp_rec.hire_date, sysdate, v_emp_rec.salary,
v_emp_rec.commission_pct, v_emp_rec.department_id);
end;
/
# values절의 내용을 쓰기가 귀찮아서 편하게 쓰고 싶을 경우
ex) 일부러 똑같이 맞추고 가정해서 했을 때
select employee_id, first_name, job_id, manager_id, hire_date, sysdate, salary, commission_pct,
department_id
from hr.employees
where employee_id = 115;
declare
v_emp_rec hr.retired_emp%rowtype; -- 테이블의 구조가 같기 때문에 레코드 타입으로 쓸 수 있다.
begin
select employee_id, first_name, job_id, manager_id, hire_date, sysdate, salary, commission_pct,
department_id
into v_emp_rec
from hr.employees
where employee_id = 115;
insert into hr.retired_emp values v_emp_rec; -- 행 레벨 insert, 2개의 테이블의 구조가 같기 때문에 위에처럼 안 써도 된다.
end;
/
select * from hr.retired_emp;
commit;
# 행 레벨의 update
ex)
declare
v_emp_rec hr.retired_emp%rowtype;
begin
select *
into v_emp_rec
from hr.retired_emp
where empno = 115;
v_emp_rec.ename := upper(v_emp_rec.ename); -- 필드에 있는 이름 수정
v_emp_rec.leavedate := to_date('2024-12-01','yyyy-mm-dd'); -- 퇴사 날짜가 잘못돼서 수정
v_emp_rec.comm := 0; -- null값이 들어있는 걸 0으로 수정
update hr.retired_emp
set row = v_emp_rec
where empno = 115;
end;
/
▶ set절에 row(행을 의미)는 행 레벨로 update 수행 시 키워드라 적으면 된다.
▶ 전제조건은 레코드 형식과 테이블 형식이 동일할 경우이다. 동일하지 않으면 일일이 다 수정해 주는 작업을 해야 한다.
'oracle PLSQL' 카테고리의 다른 글
| 2024.12.26 6일차 (2) | 2024.12.26 |
|---|---|
| 2024.12.24 PL/SQL 5일차 (5) | 2024.12.24 |
| 2024.12.20 PL/SQL 3일차 (6) | 2024.12.20 |
| 2024.12.19 PL/SQL 2일차 (0) | 2024.12.19 |
| 2024.12.18 PL/SQL 시작 (0) | 2024.12.18 |
