-- 1.출력 프로시저
exec dbms_output.put_line(3*4);
2. 출력안될때 실행해야 하는 명령
set serveroutput ON
--기본 문
DECLARE
event VARCHAR2(15); -- 변수 선언
BEGIN
--변수 정의
event := q'!Father's day!';
--출력
DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is :'||event);
event := q'[Mother's day]';
DBMS_OUTPUT.PUT_LINE('2nd Sunday in May is :'||event);
END;
--대입
:=
x NUMBER DEFAULT 2;
--비교
=
--%type : 테이블의 타입정의를 가지고 옴
declare
v_id txx.id%type := &id;
v_data txx.data%type := '&data';
begin
insert into txx values(v_id,v_data);
end;
var x number
EXEC :x := 30
SELECT * FROM dept WHERE deptno=:x;
print x
--암시적 커서 예
begin
for c in(select * from emp) loop
dbms_output.put_line(c.ename);
end loop;
end;
--명시적 커서 예
declare
cursor c is select ename from emp;
e emp.ename%type;
begin
open c;
loop
fetch c into e;
exit when c%notfound;
dbms_output.put_line(e);
end loop;
dbms_output.put_line(c%rowcount);
end;
--IF문
declare
v_dept varchar(10);
begin
for c in(select * from emp) loop
if c.deptno = 10 then
v_dept := 'acc';
elsif c.deptno = 20 then
v_dept := 'res';
elsif c.deptno = 30 then
v_dept := 'sal';
else
v_dept :='n/a';
end if;
dbms_output.put_line(c.ename || ',' || v_dept);
end loop;
end;
--case 문
declare
v_dept varchar(10);
begin
for c in(select * from emp) loop
dbms_output.put_line(c.ename || ',' ||
case c.deptno
when 10 then 'acc'
when 20 then 'res'
when 30 then 'sal'
end
);
end loop;
end;
--for문
begin
for dan in 2..9 loop
exit when dan >= 2;
for i in 1..9 loop
dbms_output.put_line(dan ||'X'||i||'='||dan*i);
end loop;
end loop;
end;
-- scalar types( 값 <> vector)
-- records 변수인데 값이 여러개 C언어의 struct
-- collections record를 묶어 놓은 c언어에서의 struct array
--rocord
DECLARE
--일부 컬럼도 가능
type dept_type is record(DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(14));
dept_row dept_type;
--dept_row dept%rowtype; -- 전체 컬럼만 가능
begin
select * into dept_row from dept where deptno=10;
insert into dept values(dept_row.deptno+1, dept_row.dname, dept_row.loc);
end;
--레코드를 이용한 출력 방법
-- 변수 r은 암시적으로 emp%rowtype이 됨.
begin
for r in (select * from emp) loop
if r.deptno=10 and r.sal+nvl(r.comm,0) > 2000 then
dbms_output.put_line(r.ename || ',' || r.sal);
end if;
end loop;
end;
declare
type t is record(deptno number(2),dname varchar2(14),loc varchar2(13));
type tt is table of t index by pls_integer;
r t;
rs tt;
i number(2) :=0;
begin
select * into r from dept where deptno=10;
dbms_output.put_line(r.dname);
for r in(select * from dept) loop
rs(i) :=r;
-- dbms_output.put_line(r.dname||i);
i:=i+1;
end loop;
for j in 0..i-1 loop
dbms_output.put_line(rs(j).dname);
end loop;
end;
/
--Explicit cursors (명시적 커서)
--명시적
begin
FOR i IN (SELECT * FROM emp) loop
dbms_output.put_line(i.ename);
end loop;
end;
/
--명시적 커서
declare
cursor c is select ename from emp; --선언
ename emp.ename%type;
begin
open c; --open
loop
fetch c into ename; -- fetch
exit when c%notfound; --속성이용 나오기
dbms_output.put_line(ename);
end loop;
CLOSE c; --close
end;
/
-- 타입 지정과 명시적 커서 사용
--
declare
type t is record(ENAME emp.ename%type, SAL emp.sal%type);
deptnox emp.deptno%type :=10;
cursor c is select ENAME, SAL from emp
where deptno=deptnox;
r t;
begin
deptnox:=20;
open c;
loop
fetch c into r;
exit when c%notfound;
dbms_output.put_line(r.ename || ',' || r.sal);
end loop;
close c;
end;
--- 커서 카운트
declare
deptnox emp.deptno%type;
rcount number(2);
cursor c is select * from emp
where deptno=deptnox;
begin
deptnox:=20;
for r in c loop
dbms_output.put_line(r.ename||','||r.sal);
rcount := c%rowcount;
end loop;
dbms_output.put_line(rcount);
end;
----------------중요 코드(함수형식으로 콜) 파라미터
declare
cursor c(deptnox number) is select ename,sal from emp
where deptno=deptnox;
ename emp.ename%type;
rcount number(2);
begin
for r in c(10) loop
dbms_output.put_line(r.ename||','||r.sal);
rcount := c%rowcount;
end loop;
dbms_output.put_line(rcount);
end;
/
--select update 동시
declare
r emp%rowtype;
cursor e is select * from emp where ename='BLAKE' for update of sal nowait;
begin
open e;
fetch e into r;
dbms_output.put_line(r.ename || ',' || r.sal);
update emp set sal=sal+1 where current of e;
close e;
end;
--error handling
begin
dbms_output.put_line(1/(1-1));
exception
when ZERO_DIVIDE then
dbms_output.put_line('su /0 <--err');
end;
declare
ename emp.ename%type;
cursor c is select ename from emp where deptno=40;
begin
open c;
loop
fetch c into ename;
exit when c%notfound;
dbms_output.put_line(ename);
end loop;
end;
--2개의 에러 처리
declare
r emp%rowtype;
begin
select * into r from emp where deptno=30;
dbms_output.put_line(r.ename);
exception
when NO_DATA_FOUND then
dbms_output.put_line(' no data');
when TOO_MANY_ROWS then
dbms_output.put_line('many row');
end;
트랙백 주소 :: 이 글에는 트랙백을 보낼 수 없습니다
1585105
37
72
