커서 변수
한 개 이상으 ㅣ쿼리를 연결해 사용할 수 있으며, 변수처럼 커서 변수를 함수나 프로시저의 매개변수로 전달할 수 있다.
ㆍ커서 변수 선언
TYPE 커서타입명 IS REF CURSOR; < 커서 타입 선언(생략)
커서변수명 커서_타입명; < 커서 변수 선언
오라클에서 제공하는 커서 타입(결과 집합이 고정되어 있지 않으므로)인 SYS_REFCURSOR이란 타입을 사용하는 것이다. 따라서 SYS_REFCURSOR를 사용할 때는 별도로 커서 타입을 선언할 필요 없이 다음과 같이 커서 변수만 선언하면 된다.
ㆍ커서 변수의 사용
1) 커서 변수와 커서 정의 쿼리문 연결
커서를 정의하는 쿼리가 있어야 하는데 커서변수와 쿼리문을 연결할 때 다음과 같이 OPEN...FOR 구문을 사용한다.
OPEN 커서변수명 FOR SELECT 문;
2)커서 변수에서 결과집합 가져오기
커서를 구성하는 쿼리에 커서 변수까지 연결했으니 커서 변수에 결과 집합을 가져오는 패치 작업이 남
았는데, 이때도 FETCH문을 사용한다.
FETCH 커서변수명 INTO 변수1, 변수2,...;
ㆍ커서 변수의 사용EX)
DECLARE
vfirst_name employees.first_name%type;
--type employeescursur IS REF CURSOR; --커서 타입 선언
--vemployees employeescursor; --커서 변수 선언
vemployees SYS_REFCURSOR; -- 오라클 서버가 제공하는 커서타입(SYS_REFCURSOR)으로 커서변수 선언.
BEGIN
-- 커서 변수를 사용한 커서 정의 및 오픈
OPEN vemployees FOR SELECT first_name FROM employees WHERE department_id = 30;
--LOOP문
LOOP
--커서 변수를 사용해 결과 집합을 vfirst_name 변수에 할당
FETCH vemployees INTO vfirst_name;
EXIT WHEN vemployees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('사원명 : ' || vfirst_name); -- 사원명 출력
END LOOP;
END;
/
ORACLE SUBPROGRAM
저장 프로시저 ㆍ 트리거 ㆍ 함수
-테이블에서 데이터를 조회해 원하는대로 조작하고 그 결과를 다른 네이블에 다시 저장하거나 수정하는 일련의 처리를 할 때 주로 프로시저를 사용하는데 그 프로시저에 대해 학습한다.
-관련 있는 프로시저를 보다 효율적으로 관리하기 위해서 패키지 단위로 배포하기 위한 패키지를 학습한다.
-특정 테이블의 데이터에 변경이 가해졌을 때 자동으로 수행되는 트리거를 학습한다.
1)저장 프로시저(Stored Procedure: 스토어드 프로시저)
프로시저는 지정된 특정 처리를 실행하는 서브 프로그램의 한 유형이다.
저장 프로시저는 자주 사용되는 쿼리문을 모듈화 시켜서 필요할 때마다 호출하여 사용하는 것을 말한다.
프로시저 생성구문
CREATE [OR REPLACE] PROCEDURE procedure_name
매개변수1[mode] 자료형, (//MODE 는 IN은 외부로부터 데이터 받아오는 것 OUT은 데이터를 밖으로 반환 하는 것)
매개변수2[mode] 자료형...
IS
local_variable declaration
BEGIN
statement1;
END [procedure_name];
생성된 저장 프로시저는 EXECUTE 프로시저명 또는 EXEC 프로시저명 명령어로 실행시킨다.
CREATE OR REPLACE PROCEDURE EMPPROC
IS
vword VARCHAR2(1); --스칼라 변수
vemployees employees%rowtype;
CURSOR C1 (vword VARCHAR2) -- 매개변수를 가지고있는 커서
IS
SELECT employee_id, first_name, salary
FROM employees
WHERE LOWER(first_name) LIKE '%' || vword || '%';
BEGIN
SELECT DBMS_RANDOM.STRING('L',1) INTO vword FROM DUAL;
DBMS_OUTPUT.PUT_LINE('임의의 문자 : ' || vword);
OPEN C1(vword);
DBMS_OUTPUT.PUT_LINE('사번 / 사원명 / 급여');
DBMS_OUTPUT.PUT_LINE('------------------------');
LOOP
FETCH C1 INTO vemployees.employee_id,vemployees.first_name, vemployees.salary;
IF C1%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('해당 사원이 존재하지 않습니다.');
END IF;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vemployees.employee_id ||'/'|| vemployees.first_name||'/'||
vemployees.salary);
END LOOP;
END;
/
EXECUTE EMPPROC;
실행을 할때 마다 임의의 문자값이 바뀌면서 사원 정보가 다르게 나오는 것을 확인할 수 있다.
1)매개 변수
매개변수는 프로시저 이름 뒤에 ()를 기술하여 그 내부에 매개변수를 정의한다. 형식은 변수명 모드자료형으로 기술한다.
--프로시저 매개변수
CREATE OR REPLACE PROCEDURE EMPPROC02 (vdepartment_id IN employees.department_id%type)
IS
CURSOR C1
IS
SELECT * FROM employees WHERE department_id = vdepartment_id;
BEGIN
DBMS_OUTPUT.PUT_LINE('사원번호/사원명/급여');
DBMS_OUTPUT.PUT_LINE('------------------');
FOR vemployees IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(vemployees.employee_id ||'/'|| vemployees.first_name||'/'||
vemployees.salary);
END LOOP;
END;
/
SHOW ERROR;
EXECUTE EMPPROC02(90);EXECUTE 프로시저 구문은 주석문과 함께 사용할 수 없다.
ㆍIN MODE 매개변수
실행환경에서 서브 프로시저로 값을 전달한다.
부서별로 salary 인상. 부서코드가 10이면 10%인상, 20이면 20%인상, 나머지는 동결하는 쿼리문을 작성하여 보자. 그전에 변경 전 데이터를 확인한다.
select department_id,first_name, salary FROM employees01 WHERE department_id = 20;변경 전 데이터
CREATE OR REPLACE PROCEDURE EMPPROC_INMODE
(vdepartment_id IN employees01.department_id%type)
IS
BEGIN
UPDATE employees01 SET salary = DECODE(vdepartment_id, 10 ,salary*1.1, 20, salary*1.2,salary)
WHERE department_id = vdepartment_id;
COMMIT;
DBMS_OUTPUT.PUT_LINE('수정이 완료되었습니다.');
END EMPPROC_INMODE;
/
SHOW ERROR;
EXEC EMPPROC_INMODE(20);
elect department_id,first_name, salary FROM employees01 WHERE department_id = 20;
create or replace procedure DEPTPROC_INMODE
(DEPTNO IN DEPT01.DEPTNO%TYPE,
DNAME IN DEPT01.DNAME%TYPE,
LOC IN DEPT01.LOC%TYPE)
IS
BEGIN
INSERT INTO DEPT01(DEPTNO,DNAME,LOC,CREDATE)
VALUES(DEPTNO,DNAME,LOC,SYSDATE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('부서번호/부서명/지역명/등록일');
DBMS_OUTPUT.PUT_LINE('-------------------------');
FOR VDEPT IN(SELECT DEPTNO,DNAME,LOC,CREDATE FROM DEPT01 ORDER BY DEPTNO)LOOP
DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||'/'||RPAD(VDEPT.DNAME,10)||'/'
||VDEPT.LOC||'/'||TO_CHAR(VDEPT.CREDATE,'YYYY-MM=DD'));
END LOOP;
END;
/
SHOW ERROR;
EXEC DEPTPROC_INMODE(60,'기획부','부산');
DEPTPROC_INUP 이라는 이름으로 프로시저를 생성해 주세요, 프로시저 실행시 인수로 부서번호, 부서명, 지역명을 얻어 현제 DEPT01테이블에 부서번호가 존재하면 수정을 실행하고 부서번호가 존재하지 않는다면 입력을 실행하도록 생성한다.
CREATE OR REPLACE PROCEDURE DEPTPROC_INUP
(PDEPTNO IN DEPT01.DEPTNO%TYPE,
PDNAME IN DEPT01.DNAME%TYPE,
PLOC IN DEPT01.LOC%TYPE)
IS
CNT NUMBER :=0;
VDEPT DEPT01%ROWTYPE;
BEGIN
SELECT COUNT(*) INTO CNT FROM DEPT01 WHERE DEPTNO = PDEPTNO;
IF CNT = 0 THEN
INSERT INTO DEPT01(DEPTNO,DNAME,LOC,CREDATE)
VALUES(PDEPTNO,PDNAME,PLOC,SYSDATE);
ELSE
UPDATE DEPT01
SET DNAME = PDNAME,LOC = PLOC, CREDATE = SYSDATE
WHERE DEPTNO = PDEPTNO;
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE('부서번호/부서명/지역명/등록일');
DBMS_OUTPUT.PUT_LINE('--------------------------');
SELECT DEPTNO,DNAME,LOC,CREDATE INTO VDEPT
FROM DEPT01 WHERE DEPTNO = PDEPTNO;
DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||'/'||RPAD(VDEPT.DNAME,10)||'/'
||VDEPT.LOC||'/'||TO_CHAR(VDEPT.CREDATE,'YYYY-MM-DD'));
END;
/
SHOW ERROR;
EXECUTE DEPTPROC_INUP(60,'기획부','전주');
EXECUTE DEPTPROC_INUP(70,'영업부','서울');
ㆍOUT MODE 매개변수(프로시저 호출 후 해당 매개변수 값을 받아 사용 가능)
프로시저에 구한 결과 값을 얻어내기 위해서는 MODE를 OUT으로 지정하낟. OUT 매개변수는 프로시저 내에서 로직 처리 후, 해당 매개변수에 값을 할당해 프로시저 호출 부분에서 이결과값을 참조할 수 있다.
사원 번호로 특정 고객을 조회하기 때문에 사원 번호는 IN으로 지정하고 조회해서 얻은 고객의 정보 중에서 고객의 이름과 급여와 담당업무를 얻어오기 위해서 이름과 그병와 담당 업무컬럼을 OUT으로 지정
CREATE OR REPLACE PROCEDURE EMPPROC_OUTMODE(
vemployee_id IN employees.employee_id%type,
vfirst_name OUT employees.first_name%type,
vsalary OUT employees.salary%type,
vjob_id OUT employees.job_id%type
)
IS
BEGIN
SELECT first_name, salary, job_id INTO vfirst_name , vsalary, vjob_id
from employees
where employee_id = vemployee_id;
END;
/
DECLARE
vemployee employees%rowtype;
BEGIN
EMPPROC_OUTMODE(120,vemployee.first_name,vemployee.salary,vemployee.job_id);
DBMS_OUTPUT.PUT_LINE('사원명 : '||vemployee.first_name );
DBMS_OUTPUT.PUT_LINE('급여 : '||vemployee.salary );
DBMS_OUTPUT.PUT_LINE('직무 : ' ||vemployee.job_id );
END;
/
--부서번호를 입력받아 해당 부서의 사원수, 급여의 합, 급여의 평균을 외부로 전달하는 프로시저를 생성해 주세요.
CREATE OR REPLACE PROCEDURE DEPT_DATA
(dept_id in employees.department_id%type,
dept_count out employees.salary%type,
dept_sal_sum out employees.salary%type,
dept_sal_avg out employees.salary%type
)
IS
BEGIN
SELECT COUNT(*), SUM(salary),AVG(salary) INTO dept_count,dept_sal_sum,dept_sal_avg
FROM employees
WHERE department_id = dept_id;
END;
/
DECLARE
dept_cnt number(10);
dept_sum number(10);
dept_avg number(10);
BEGIN
DEPT_DATA(30,dept_cnt,dept_sum,dept_avg);
DBMS_OUTPUT.PUT_LINE('부서 사원수 : ' || dept_cnt);
DBMS_OUTPUT.PUT_LINE('급여의 합 : ' || dept_sum);
DBMS_OUTPUT.PUT_LINE('급여의 평균 : ' || dept_avg);
END;
/
--급여를 입력받아 그 급여보다 많이 받는 사원의 사원번호, 이름 , 급여를 외부로 전달하는 프로시저를 생성해 주세요
CREATE OR REPLACE PROCEDURE EMP_SAL_DATA
(vsalary IN employees.salary%type, vemployees OUT SYS_REFCURSOR)
IS
BEGIN
OPEN vemployees FOR SELECT employee_id,first_name,salary
FROM employees WHERE salary > vsalary;
END;
/
SHOW ERROR;
DECLARE
pemployees SYS_REFCURSOR;
vemployees employees%ROWTYPE;
BEGIN
EMP_SAL_DATA(12000,pemployees);
LOOP
FETCH pemployees INTO vemployees.employee_id,vemployees.first_name,vemployees.salary;
EXIT WHEN pemployees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vemployees.employee_id||'.'||vemployees.first_name||''||
vemployees.salary);
END LOOP;
END;
/
ㆍIN OUT MODE 매개변수
매개변수를 통해 결과값을 입력 받아 다시 해당 매개변수값으로 변형된 DATA를 받는 형태이다.
IN OUT의 기능을 모두 수행한다. 즉 ,실행 환경에서 프로시저로 값을 전다랗고 프로시저에서실행환경으로 변경된 값을 전달할 수 있다.
JAVA SQL 데이터 연동
java eclips 로 sql develope에 있는 subject 테이블에서 입력하여 데이터 삭제하기
if(!mode.equals("delete")) {
System.out.print("학과명 입력 : ");
s_name = MenuViewer.keyboard.nextLine();
}
if(mode.equals("delete")) {
System.out.println("삭제 전 소속된 학생 여부를 확인하고자학과 번호를 입력해 주세요.");
System.out.println("학과코드 입력 : ");
s_num = MenuViewer.keyboard.nextLine();
public void delete() {
SubjectVO vo = inputData("delete");
int data = dao.studentDataCheck(vo);
if(data != 0) {
System.out.println("소속된 학생이 존재함으로 학괴데이터를 삭제할 수 없습니다.");
}else{
//int no = inputDataNo();
//boolean result = dao.subjectDelete(no);
int no = inputDataNo();
SubjectVO svo = new SubjectVO();
svo.setNo(no);;
boolean result = dao.subjectDelete(svo);
if(result) {
System.out.println("학과 데이터 삭제 성공.");
}else {
System.out.println("학과 데이터 삭제 실패.");
}
}
}public boolean subjectDelete(SubjectVO svo) {
//public boolean subjectDelete(int no) {
StringBuffer sql = new StringBuffer();
sql.append("delete from subject where no = ?");
Connection con = null;
PreparedStatement pstmt = null;
boolean success = false;
try {
con = getConnection();
pstmt = con.prepareStatement(sql.toString());
//pstmt.setInt(1, no);
pstmt.setInt(1, svo.getNo());
int i = pstmt.executeUpdate();
if(i == 1 ) {
success = true;
}
}catch(SQLException se) {
System.out.println("삭제에 문제가 있어 잠시 후에 다시 진행해 주세요.");
se.printStackTrace();
}catch(Exception e) {
System.out.println("error = [ " + e + " ] ");
}finally {
try {
if(pstmt != null) pstmt.close();
if(con != null) con.close();
}catch(SQLException e) {
System.out.println("디비 연동 해제 error = [ \"+e+\" ] \");");
}
}
return success;
}
}
데이터를 삭제하고 조회해 보면 삭제된것을 확인할 수 있다 !.
search로 학과명을 입력하여 데이터 찾기.
public void read(String mode) {
SubjectVO vo = null;
if(mode.equals("search")) {
vo = inputData("search");
System.out.println("검색 단어 : "+vo.getS_name());
}
ArrayList<SubjectVO> svo = dao.getSubjectTotal(vo);
System.out.println("\n**** subject 테이블 데이터 출력 ****");
System.out.println("번호\t학과번호\t학과명");
if(svo.size() > 0) {
//for(int i = 0 ; i < svo.size(); i++){
//SubjectVO sub = svo.get(i);
for(SubjectVO sub : svo) {
System.out.print(sub.getNo()+ "\t");
System.out.print(sub.getS_num()+ "\t");
System.out.println(sub.getS_name()+ "\t");
}
}else {
System.out.println("학과 정보가 존재하지 않습니다.");
}
}
public ArrayList<SubjectVO> getSubjectTotal(SubjectVO vo){
StringBuffer sql = new StringBuffer();
sql.append("select no, s_num, s_name from subject ");
if(vo != null) {
sql.append("where s_name like ? ");
}
sql.append("order by no");
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
SubjectVO svo = null;
ArrayList<SubjectVO> list = new ArrayList<SubjectVO>();
try {
con = getConnection();
pstmt = con.prepareStatement(sql.toString());
if(vo != null) {
pstmt.setString(1, "%" + vo.getS_name() + "%");
}
rs = pstmt.executeQuery();
//ResultSet의 결과에서 모든 행을 각각의 SubjectVO 객체에 저장
while(rs.next()) {
//한행의 학과 정보를 저장할 VO 객체 생성
svo = new SubjectVO();
//한 행의 학과 정보를 VO 객체에 저장
svo.setNo(rs.getInt("no"));
svo.setS_num(rs.getString("s_num"));
svo.setS_name(rs.getString("s_name"));
//↑VO담는 작업
//ArrayList 객체에 원소로 추가
list.add(svo);
}
}catch(SQLException se) {
System.out.println("조회에 문제가 있어 잠시 후에 다시 진행해 주세요.");
se.printStackTrace();
}catch (Exception e) {
System.out.println("error = [ " + e.getMessage() +" ]");
}finally {
try {
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if( con != null) con.close();
}catch(SQLException e) {
System.out.println("디비 연동 해제 error = [ " + e +" ]");
}
}
return list;
}
개발 일지