▣ empCreate.sql
conn / as sysdba
create table hr.emp as select * from scott.dept
/
create table hr.dept as select * from scott.emp
/
conn hr/hr
alter table emp add constraint pk_emp primary key(empno)
/
alter table dept add constraint pk_dept primary key(deptno)
/
alter table dept add constraint fk_emp foreign key(deptno) references dept(deptno)
/
create sequence emps start with 7950
/
create sequence depts start with 60 increment by 10
/
▣ empTest.sql
conn hr/hr
select count(*) from emp;
select count(*) from dept;
select emps.nextval from dual;
select depts.nextval from dual;
▣ empDrop.sql
conn hr/hr
drop table emp;
drop table dept;
drop sequence emps;
drop sequence depts;
▣ empInsert.sql
create or replace function empInsert (
pEname emp.ename%type,
pJob emp.job%type,
pMgr emp.mgr%type,
pHiredate emp.hiredate%type,
pSal emp.sal%type,
pComm emp.comm%type,
pDeptno emp.deptno%type)
return number
as
ret emp.empno%type; => emps.currval은 리턴값으로 리턴할 수 없기 때문에 이 구문 사용해서 리턴한다.
begin
insert into emp values(emps.nextval,pEname,pJob,pMgr,pHiredate,pSal,pComm,pDeptno);
select emps.currval into ret from dual;
return ret;
end;
/
▣ 확인
S HR> var e number;
S HR> exec :e := empInsert('hoho','MANAGER',7934,sysdate,980,null,20);
PL/SQL procedure successfully completed.
S HR> select * from emp where empno=:e;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7951 hoho MANAGER 7934 01-JUN-09 980 20
S HR> begin
2 :e := empInsert('hoho','MANAGER',7934,sysdate,980,null,20);
3 end;
4 /
S HR> sav empInsertTest
▣ empUpdate.sql
empUpdate
select 'p'||column_name||' '||'&&TABLE_NAME.'||column_name||'%type.'
from user_tab_cols where table_name='&TABLE_NAME'
/
undefine TABLE_NAME
/
:%s/EMP/\&TABLE\_NAME/g
sav argList
define TABLE_NAME;
select column_name||'=p'||column_name||','
from user_tab_cols where table_name='&TABLE_NAME'
/
sav ucolList
위에서 sav 한 sql을 이용하여 empUpdate 작성 시 이용한다.
create or replace function empUpdate (
pEMPNO emp.empno%type,
pEname emp.ename%type,
pJob emp.job%type,
pMgr emp.mgr%type,
pHiredate emp.hiredate%type,
pSal emp.sal%type,
pComm emp.comm%type,
pDeptno emp.deptno%type)
return number
as
updateCount number(10) :=0;
begin
update emp set
EMPNO=pEMPNO,
ENAME=pENAME,
JOB=pJOB,MGR=pMGR,
HIREDATE=pHIREDATE,
SAL=pSAL,
COMM=pCOMM,
DEPTNO=pDEPTNO
where empno=pEMPNO;
select count(*) into updateCount from emp where empno=pEMPNO;
return updateCount;
end;
/
var uc number;
begin
:uc :=empUpdate(7369,'SMITH','CLERK',7902,to_date('1980.12.17','yyyy.mm.dd'),999,10,20);
end;
/
print uc;
▣ empDelete.sql
empDelete
create or replace function empDelete (
pEMPNO emp.empno%type)
return number
as
deleteCount number(10) :=0;
begin
select count(*) into deleteCount from emp where empno=pEMPNO;
delete from emp where empno=pEMPNO;
return deleteCount;
end;
/
▣ Package
create or replace package empDml
is
function empInsert(
pEname emp.ename%type,
pJob emp.job%type,
pMgr emp.mgr%type,
pHiredate emp.hiredate%type,
pSal emp.sal%type,
pComm emp.comm%type,
pDeptno emp.deptno%type) return number;
function empUpdate(
pEMPNO emp.empno%type,
pEname emp.ename%type,
pJob emp.job%type,
pMgr emp.mgr%type,
pHiredate emp.hiredate%type,
pSal emp.sal%type,
pComm emp.comm%type,
pDeptno emp.deptno%type) return number;
function empDelete(pEMPNO emp.empno%type) return number;
end;
/
create or replace package body empDml
is
function empInsert(
pEname emp.ename%type,
pJob emp.job%type,
pMgr emp.mgr%type,
pHiredate emp.hiredate%type,
pSal emp.sal%type,
pComm emp.comm%type,
pDeptno emp.deptno%type) return number
as
ret emp.empno%type;
begin
insert into emp values(
emps.nextval,
pEname,
pJob,
pMgr,
pHiredate,
pSal,
pComm,
pDeptno);
select emps.currval into ret from dual;
return ret;
exception
when others then
if SQLCODE=-2291 then
dbms_output.put_line('no Fk.err');
return null;
end if;
end;
function empUpdate(
pEMPNO emp.empno%type,
pEname emp.ename%type,
pJob emp.job%type,
pMgr emp.mgr%type,
pHiredate emp.hiredate%type,
pSal emp.sal%type,
pComm emp.comm%type,
pDeptno emp.deptno%type)
return number
as
updateCount number(10) :=0;
begin
update emp set
EMPNO=pEMPNO,
ENAME=pENAME,
JOB=pJOB,MGR=pMGR,
HIREDATE=pHIREDATE,
SAL=pSAL,
COMM=pCOMM,
DEPTNO=pDEPTNO
where empno=pEMPNO;
select count(*) into updateCount from emp where empno=pEMPNO;
return updateCount;
end;
function empDelete(
pEMPNO emp.empno%type)
return number
as
deleteCount number(10) :=0;
begin
select count(*) into deleteCount from emp where empno=pEMPNO;
delete from emp where empno=pEMPNO;
return deleteCount;
end;
end;
/
▣ empTruncate
empTruncate
commit;
drop table emp;
drop table dept;
drop sequence emps;
drop sequence depts;
@@empCreate.sql @@ : 같은 위치에 있는 sql 파일 실행
truncate table emp; truncate는 상황에 맞게 설정해 줌
truncate table dept;
drop package empDml;
▣ 파일 리스트
empCreate.sql | emp/dept 테이블 생성 및 시퀀스 생성 |
empDelete.sql | Row 삭제 |
empDeleteTest.sql | empDelete 내용 실행 |
empDrop.sql | 생성된 테이블 및 시퀀스, 패키지 삭제 |
empInsert.sql | Row 추가 |
empInsertTest.sql | empInsert 실행 |
empTruncate.sql | 테이블 삭제 및 시퀀스 삭제 |
empUpdate.sql | Row 업데이트 |
empUpdateTest.sql | empUpdate실행 |
댓글 없음:
댓글 쓰기