종료하기 전 티스토리 네이버 로그아웃 할 것
1. 툴
동일
추가시:
2. 폴더
동일
추가시:
3. 사용할 사이트
동일
추가시:
4. 공부하는 것
[08] Trigger
[01] Trigger
- UPDATE, INSERT, DELETE 쿼리가 시행될 때마다 부가적으로 다른 쿼리를
실행 할 수 있는 기술입니다.
- 트리거링 사건(Triggering Event)에 의해 자동으로 실행되는 PL-SQL라고 할 수 있습니다.
- 너무 복잡한 트리거의 사용은 DB의 속도를 저하시키는 단점이 있습니다.
- 트리거는 문장 트리거 와 행 트리거로 나눌 수 있습니다.
- 문장 트리거는 FOR EACH ROW 옵션 절을 사용하지 않고 트리거링 사건에 의해서
단 한번 실행되며, 컬럼의 데이터값에 상관없이 칼럼에 변화가 일어남을 감지하여
실행되는 트리거입니다.
- 행 트리거는 FOR EACH ROW 옵션 절을 사용하고 칼럼의 각 데이터 행에 변화가
생길때마다 실행되며 데이터 행의 실제 값을 수정, 변경, 저장시 사용합니다.
1. 트리거의 구성요소
- 트리거 유형: Statement Level, Row Level
- 트리거 타이밍
. BEFORE: 쿼리가 실행되기 전에 실행하는 트리거
. AFTER : 쿼리가 실행되고 난후 실행하는 트리거
- 트리거 이벤트: INSERT, UPDATE, DELETE
- 트리거 몸체: PL/SQL 블럭
- 트리거 조건: WHEN 조건
- 문법
CREATE OR REPLACE TRIGGER 트리거명 BEFORE | AFTER
trigger_evnet ON table_name
[FOR EACH ROW]
[WHEN (condition) ]
PL/SQL block
2. 문장 레벨의 트리거
- 전체 transaction 작업에 대해 1번발생되는 트리거로 default 입니다.
- emp 테이블에 대해서 insert, update, delete가 발생하면 아래의
트리거가 자동으로 작동되어 요일이 '토, 일'인 경우는 아래처럼
메시지를 출력합니다.
CREATE or REPLACE TRIGGER t_test1
BEFORE INSERT or UPDATE or DELETE ON emp
BEGIN
IF (to_char(sysdate, 'DY') IN ('토', '일')) or (to_char(sysdate, 'DY') IN ('SAT', 'SUN')) THEN
DBMS_OUTPUT.PUT_LINE('주말에는 데이터를 변경할 수 없습니다.!!');
ELSE
DBMS_OUTPUT.PUT_LINE('월요일 ~ 금요일에는 데이터를 변경할 수 있습니다.!!');
END IF;
END;
/
- 오라클 서버의 날짜를 토요일이나 일요일로 변경해 테스트합니다.
UPDATE emp SET sal = sal * 1.1;
SELECT * FROM emp;
3. 문장 트리거 예제
CREATE TABLE test (
name VARCHAR2(10),
d_date VARCHAR2(20)
);
CREATE OR REPLACE TRIGGER trigger1 AFTER
UPDATE on EMP
BEGIN
INSERT INTO test VALUES('test1', sysdate);
END;
/
-- 실행
UPDATE emp SET mgr = 2 ;
SELECT * FROM test;
4. 행 트리거 예제
CREATE OR REPLACE TRIGGER trigger2 AFTER
UPDATE on EMP
FOR EACH ROW -- 행 트리거
BEGIN
INSERT INTO test VALUES('test2', sysdate);
END;
/
--실행
DELETE FROM test
UPDATE emp SET mgr = 1;
SELECT * FROM test;
[09] Trigger - 매출액 관리 실습
[01] 날짜별 매출액 관리
1. 테이블 구조
-- 예매 정보 테이블
DROP TABLE advance_sale PURGE;
CREATE TABLE advance_sale(
NUM NUMBER NOT NULL, -- 일련번호
SDATE VARCHAR2(30 BYTE) NOT NULL, -- 예매 날짜
QTY NUMBER NOT NULL, -- 수량
AMOUNT NUMBER NOT NULL -- 금액
);
SELECT * FROM advanced_sale;
-- 총 매출액 테이블
DROP TABLE sales PURGE;
CREATE TABLE sales(
SDATE VARCHAR2(30 BYTE) NOT NULL, -- 날짜
DAYAMOUNT NUMBER NOT NULL, -- 일단위 총 금액
DAYQTY NUMBER NOT NULL -- 일단위 총 수량
)
SELECT sdate, dayamount, dayqty FROM sales;
2. 트리거
- advance_sale 테이블에 insert, update, delete 이벤트가 발생하면
t_sales 트리거를 작동합니다.
- FOR EACH ROW: 행 트리거, 각 행에 대해서 트리거가 발생된다.
CREATE OR REPLACE TRIGGER t_sales
AFTER INSERT OR UPDATE OR DELETE ON advance_sale -- advance_sale 테이블
FOR EACH ROW -- 각각의 행에 대해서 작동
DECLARE
v_cnt NUMBER;
BEGIN
-- :new 객체는 추가되는 레코드를 저장하고 있는 객체
-- 기존에 등록된 레코드의 날짜와 새로 추가되는 컬럼의 날짜를 비교하여
-- 같은 레코드의 수를 리턴합니다.
SELECT count(*) INTO v_cnt FROM sales WHERE sdate = :new.sdate;
IF INSERTING THEN -- advance_sale 테이블에 레코드 추가일경우
IF v_cnt > 0 THEN
-- 기존에 같은 날짜에 등록된 레코드가 있다면 금액, 수량 누적
-- 금액 = 기존 금액 + 새로운 금액
-- 수량 = 기존 수량 + 새로운 수량
UPDATE sales SET dayamount=dayamount+:new.amount, dayqty=dayqty+:new.qty
WHERE sdate = :new.sdate; -- 날짜가 같은 레코드
ELSE -- 기존에 등록된 날짜가 없다면 새로 레코드 추가
INSERT INTO sales(sdate, dayamount, dayqty)
VALUES(:new.sdate, :new.amount, :new.qty);
END IF;
ELSIF DELETING THEN -- 레코드 삭제일 경우, 금액, 수량 삭제
-- :old: 삭제된 레코드를 가지고 있는 객체
-- 같은 날짜에 한해서 금액 및 수량을 감산 합니다.
UPDATE sales SET dayamount=dayamount-:old.amount, dayqty=dayqty-:old.qty
WHERE sdate = :old.sdate;
END IF;
END;
/
DELETE FROM advance_sale;
DELETE FROM sales;
COMMIT;
3. 새로운 날짜의 매출이 발생한 경우 INSERT Test
INSERT INTO advance_sale(num, sdate, qty, amount)
VALUES(1, TO_CHAR(sysdate, 'yyyy-mm-dd'), 1, 5000);
SELECT * FROM advance_sale;
NUM SDATE QTY AMOUNT
---------- ------------------------------ ---------- -------------
1 2006-11-13 1 5000
1 row selected
-- 매출액 테이블
SELECT sdate, dayamount, dayqty FROM sales;
SDATE DAYAMOUNT DAYQTY
------------------------------ ---------- --------------
2006-11-13 5000 1
1 row selected
4. 같은 날짜에 매출이 발생한 경우 UPDATE Test
INSERT INTO advance_sale(num, sdate, qty, amount)
VALUES(2, TO_CHAR(sysdate, 'yyyy-mm-dd'), 1, 5000);
SELECT * FROM advance_sale;
NUM SDATE QTY AMOUNT
---------- ------------------------------ ---------- ----------
1 2006-11-13 1 5000
2 2006-11-13 1 5000
2 rows selected
-- 매출액 테이블
SELECT sdate, dayamount, dayqty FROM sales;
SDATE DAYAMOUNT DAYQTY
------------------------------ ---------- ----------
2006-11-13 10000 2
1 row selected
5. 날짜가 다른경우
- Oracle Server의 시간 변경후 테스트를 하던지 또는 sysdate+1을 합니다.
INSERT INTO advance_sale(num, sdate, qty, amount)
VALUES(3, TO_CHAR(sysdate+1, 'yyyy-mm-dd'), 1, 5000);
SELECT * FROM advance_sale
NUM SDATE QTY AMOUNT
---------- ------------------------------ ---------- ----------
2 2006-11-13 1 5000
3 2006-11-13 1 5000
3 2006-11-14 1 5000
3 rows selected
- 매출액 테이블
SELECT sdate, dayamount, dayqty FROM sales
SDATE DAYAMOUNT DAYQTY
------------------------------ ---------- ----------
2006-11-13 10000 2
2006-11-14 5000 1
2 rows selected
6. 매출액이 취소되는 경우
DELETE FROM advance_sale WHERE num=1;
SELECT * FROM advance_sale;
NUM SDATE QTY AMOUNT
---------- ------------------------------ ---------- ----------
2 2006-11-13 1 5000
3 2006-11-13 1 5000
2 rows selected
-- 매출액 테이블
SELECT sdate, dayamount, dayqty FROM sales;
SDATE DAYAMOUNT DAYQTY
------------------------------ ---------- ----------
2006-11-13 5000 1
1 row selected
create table product(
code char(6) not null,
name varchar2(25) null,
company varchar2(25) null,
price number null,
stock number null,
primary key (code)
);
create table inbound(
inno number not null,
indate date null,
inquantity number null,
inprice number null,
amount number null,
code char(6) not null,
primary key(inno),
foreign key(code) references product
);
insert into product
values('A001','세탁기','LG',500,0);
insert into product
values('A002','컴퓨터','LG',700,0);
insert into product
values('A003','냉장고','삼성',600,0);
create or replace trigger in_trigger
after insert or delete or update on inbound
for each row
declare
begin
if inserting then
update product set stock = stock + :new.inquantity
where code = :new.code;
elsif updating then
update product set stock = stock + :new.inquantity - :old.inquantity
where code = :old.code;
elsif deleting then
update product set stock = stock - :old.inquantity
where code = :old.code;
end if;
end;
/
insert into inbound(inno,code,indate,inquantity,inprice,amount)
values(1, 'A001', sysdate, 5,320,16000);
select * from inbound;
select * from product;
insert into inbound(inno,code,indate,inquantity,inprice,amount)
values(2, 'A002',sysdate, 8,500,4000);
update inbound
set inquantity = 200,
amount = 1000000
where inno = 2
[11] Trigger - 주문후 판매통계 누적하기 실습
[01] Trigger 실습
- 주문이 이루어지면 판매통계 테이블에 월별 판매수량 판매금액 증가
- 주문이 취소되면 판매통계 테이블에 월별 판매수량 판매금액 감소1. 테이블 구조CREATE TABLE porder (
ono INTEGER NOT NULL,
odate CHAR(10) NULL,
oID CHAR(8) NULL,
oquantity INTEGER NULL,
oprice INTEGER NULL,
code CHAR(6) NOT NULL,
PRIMARY KEY (ono),
FOREIGN KEY (code)
REFERENCES product
);
CREATE TABLE sales_total (
no NUMBER NOT NULL,
yearmonth CHAR(7) NOT NULL, -- 년월
code CHAR(6) NULL,
monthsalesqty NUMBER NULL, --월별판매수량
monthsalesprice NUMBER NULL, --월별판매금액
PRIMARY KEY (no)
);
CREATE SEQUENCE seq_no
START WITH 1 --시작번호
INCREMENT BY 1 --증가값
MAXVALUE 99999 --최대값
CACHE 20 --시쿼스 변경시 자주 update되는 것을 방지하기위한 캐시값
NOCYCLE;
2. 주문이 생성되면 판매통계 테이블의 월별금액과 월별 수량이 증가 또는 감소되도록 Trigger 작성하세요.
3. 주문이 일어났을때 상품의 재고수량의 변화를 트리거로 추가 하세요.
ORACLE 내장함수에는
LEFT(), RIGHT() 함수가 없다.
substr 함수를 통해 똑같이 구현이 가능하다.
@ 3번째 자리부터 쭉-
SELECT substr('ABC권경안Z9', 3) FROM DUAL;
C권경안Z9
@ 4번째 자리부터 2글자
SELECT substr('ABC권경안Z9', 4, 2) FROM DUAL;
권경
@ 우측부터 추출하고 싶다면 마이너스(-) 기호를 사용하면 된다. 우측 2글자
SELECT substr('ABC권경안Z9', -2) FROM DUAL;
Z9
create table porder(
ono integer not null,
odate char(10) null,
oID char(8) null,
oquantity integer null,
oprice integer null,
code char(6) not null,
primary key(ono),
foreign key(code) references product
);
select * from product
select * from porder
insert into porder(ono, odate, oID, oquantity, oprice, code)
values(1, to_char(sysdate, 'yyyy-MM-dd'), 'kim', 2 ,1000,'A001');
create table sales_total(
no number not null,
yearmonth char(7) not null, --년월
code char(6) null,
monthsalesqty number null, --월별 판매 수량
monthsalesprice number null, --월별 판매 금액
primary key(no)
);
select * from sales_total
- 주문이 이루어지면 판매통계 테이블에 월별 판매수량 판매금액 증가
- 주문이 취소되면 판매통계 테이블에 월별 판매수량 판매금액 감소
create sequence seq_no
start with 1
increment by 1
maxvalue 99999
cache 20
nocycle;
2. 주문이 생성되면 판매통계 테이블의 월별금액과 월별 수량이 증가 또는 감소되도록
Trigger 작성하세요.
CREATE OR REPLACE TRIGGER p_sales
AFTER INSERT OR UPDATE OR DELETE ON porder
FOR EACH ROW
DECLARE
v_cnt NUMBER;
v_date char(7) := '';
BEGIN
DBMS_output.put_line(substr(:new.odate,1,7));
v_date := substr(:new.odate,1,7);
SELECT count(*) INTO v_cnt FROM sales_total
WHERE yearmonth = v_date;
IF INSERTING THEN
IF v_cnt > 0 THEN
UPDATE sales_total
SET monthsalesqty=monthsalesqty+:new.oquantity,
monthsalesprice=monthsalesprice+:new.oprice
WHERE yearmonth = v_date;
ELSE
INSERT INTO sales_total
(no, yearmonth, monthsalesqty,monthsalesprice,code)
VALUES(seq_no.nextval,v_date,:new.oquantity
,:new.oprice,:new.code);
END IF;
ELSIF DELETING THEN
UPDATE sales_total
SET monthsalesqty=monthsalesqty+:old.oquantity,
monthsalesprice=monthsalesprice+:old.oprice
WHERE yearmonth = v_date;
END IF;
END;
/
insert into porder(ono,odate,oid,oquantity,oprice,code)
values(1,'2016-07-24','user2',1,500,'A001');
insert into porder(ono,odate,oid,oquantity,oprice,code)
values(2,'2016-07-24','user2',2,1400,'A002');
insert into porder(ono,odate,oid,oquantity,oprice,code)
values(3,'2016-07-24','user2',2,1400,'A002');
select * from sales_total
select substr('2016-06-24',1,7) from dual
[12] CallableStatement를 이용한 Update, Out Parameter
[01] CallableStatement를 이용한 PL/SQL의 호출
1. Oracle DB 설정
>>>>> 실습용 테이블
-------------------
DROP TABLE DEPT CASCADE CONSTRAINTS;
-- 부서 테이블
CREATE TABLE DEPT
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
DROP TABLE EMP CASCADE CONSTRAINTS;
-- 직원 테이블
CREATE TABLE EMP
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
DELETE FROM EMP;
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,
sysdate, 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,
sysdate, 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,
sysdate, 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,
sysdate, 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL,
sysdate, 5000, NULL, 10);
SELECT * FROM EMP;
COMMIT;
2. 프로시저의 생성 및 테스트
- 부서번호, 급여 인상률을 입력받아 급여를 인상하는 프로시져를 생성합니다.
CREATE or REPLACE PROCEDURE proc1
(
i_deptno IN number, -- 부서 번호
i_increase_rate IN number -- 급여 인상율
)
IS
BEGIN
UPDATE EMP set sal = sal * i_increase_rate
WHERE deptno = i_deptno;
END;
/
SELECT * FROM emp WHERE deptno=10 ORDER BY deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- ---------- ----------- ---------- -------- ----------
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
DECLARE
i_deptno number :=10;
i_increase number(2,1) := 1.1;
BEGIN
proc1(i_deptno, i_increase);
END;
/
SELECT * FROM emp WHERE deptno=10 ORDER BY deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- -------- ---------- ----------
7782 CLARK MANAGER 7839 81/06/09 2695 10
7839 KING PRESIDENT 81/11/17 5500 10
SELECT * FROM emp WHERE deptno=10 ORDER BY deptno;
3. CallableStatement를 실행
>>>>> Proc1.java
package test.plsql;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Proc1 {
public Proc1(){ }
public static void main(String[] args) {
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@127.0.0.1:1521:XE";
String id = "soldesk";
String password = "1234";
Connection con = null;
// PL/SQL 처리
//--------------------------------
CallableStatement cstmt = null;
//--------------------------------
try{
Class.forName(driver);
con = DriverManager.getConnection(url, id, password);
// call 명령은 프로시저를 호출합니다.
cstmt = con.prepareCall("{call proc1(?,?)}");
// 프로시저에 넘겨줄 인수 지정
cstmt.setInt(1, 10);
cstmt.setFloat(2, 1.5f);
cstmt.executeUpdate();
System.out.print("로직 처리 완료");
}catch(Exception e){
e.printStackTrace();
}finally{
try{ cstmt.close(); }catch(Exception e){}
try{ con.close(); }catch(Exception e){}
}
System.out.println("정상적으로 실행되었습니다.");
}
}
SQL> select * from emp where deptno=10 order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 81/06/09 2695 10
7839 KING PRESIDENT 81/11/17 5500 10
4. Out Parameterm의 사용
- 부서번호를 입력받아 직원수를 리턴합니다.
CREATE or REPLACE PROCEDURE proc2(
i_deptno IN number, -- 부서 번호
o_count OUT number -- 직원 수
)
IS
BEGIN
SELECT count(*) INTO o_count FROM EMP
WHERE deptno = i_deptno;
END;
/
-- 직원수 출력
SELECT count(*) as o_count FROM EMP
WHERE deptno = 10;
-- Toad에서 DBMS Output을 Enable 하고 실행
DECLARE
i_deptno number :=10;
o_count number := 0;
BEGIN
proc2(i_deptno, o_count);
DBMS_OUTPUT.PUT_LINE(o_count);
END;
/
>>>>> Proc2.java
package test.plsql;
import java.sql.*;
import java.io.*;
public class Proc2 {
public Proc2(){ }
public static void main(String[] args) {
try{
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@127.0.0.1:1521:XE";
String id = "soldesk";
String password = "1234";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, id, password);
/*
CREATE or REPLACE PROCEDURE proc2(
i_deptno IN number, -- 부서 번호
o_count OUT number -- 직원 수
)
IS
BEGIN
SELECT count(*) INTO o_count FROM EMP
WHERE deptno = i_deptno;
END;
/
*/
CallableStatement cstmt = conn.prepareCall("{call proc2(?,?)}");
cstmt.setInt(1, 10);
// output 파라미터의 지정
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.executeUpdate();
// Output 파라미터를 가져오는 부분, output 파라미터는 2번째
// 임으로 getInt(2)를 지정 합니다.
System.out.println("10번 부서의 직원수: " + cstmt.getInt(2));
}catch(Exception e){
System.out.println(e.toString());
}
System.out.println("정상적으로 실행되었습니다.");
}
}
Connection 에 의한 SQL 처리는 크게 3가지로 나뉘어 진다.
- Statement : 단순히 sql문을 실행하는 역할만 한다.(값을 sql문에 +연산자로 추가해야 한다.)
- PreparedStatement : sql 문에 ? 를 사용해서 값을 간편히 추가한다.
- CallableStatement : DB에 생성된 저장프로시저를 사용한다. 역시 ? 사용가능
Statement 객체를 만들기 위해서는 사용되는 Connection 클래스의 메서드
- Statement stmt = conn.creatStatement();
- PreparedStatement pstmt = conn.prepareStatement(String sql)
- CallableStatement cstmt = conn.prepareCall(String sql)
-----------------------------------------------------------
Statement
-----------------------------------------------------------
Statement 인터페이스는 Connection 객체로부터 Instance 화 하여 만들어 내며, DB에 SQL을 보내기 위한 준비 작업과 실제 SQL을 실행하여 결과값을 주는 API 를 제공한다.
Statement의 주요 메소드
execute()
execute()라는 메소드는 질의문(SELECT)이나 갱신문(UPDATE, INSERT, DELETE) 모두 사용할 수 있다. 질의문의 결과(ResultSet)를 얻기 위해서는 별도로 마련된 getResultSet()이라는 메소드를 사용해야 한다.
executeQuery()
executeQuery()메소드는 주로 SELECT문을 Database로 넘겨 실행시키고자 할 때 사용한다.
이 메소드는 ResultSet객체를 Return하며, Argument로는 Query할 SELECT문장을 String형태로 받는다.
executeUpdate()
executeUpdate()메소드는 INSERT문이나 CREATE문, DELETE문 같이 Database를 갱신(update)할 때 사용하는 메소드이다. 이 메소드는 갱신된 행의 수를 반환한다.
<<<<<<<<<<<< JSP >>>>>>>>>>
<%
DataSource ds = null;
Connection conn = null;
java.sql.Statement stmt = null;
PreparedStatement pstmt;
CallableStatement pstmt2;
ResultSet rs= null;
try{
initContext = new InitialContext();
envContext = (Context) initContext.lookup("java:comp/env");
ds = (DataSource) envContext.lookup("jdbc/KSFC");
conn = ds.getConnection();
stmt = conn.createStatement();
int SqlNo = 1;
String SP_SQL1 = "{CALL ODS..SP_EM002_Proc(?,?,?,?,?,?)}" ;
pstmt2 = conn.prepareCall(SP_SQL1);
pstmt2.clearParameters();
pstmt2.setInt(1,SqlNo);
pstmt2.setString(2,firstdt);
pstmt2.setString(3,startdt);
pstmt2.registerOutParameter(4,java.sql.Types.DECIMAL);
pstmt2.registerOutParameter(5,java.sql.Types.DECIMAL);
pstmt2.registerOutParameter(6,java.sql.Types.DECIMAL);
pstmt2.execute();
double pung1 = 0.0;
double pung2 = 0.0;
double pung3 = 0.0;
pung1 = pstmt2.getDouble(4);
pung2 = pstmt2.getDouble(5);
pung3 = pstmt2.getDouble(6);
SqlNo = 2;
String SP_SQL2 = "{CALL ODS..SP_EM002_Proc(?,?,?,?,?,?)}" ;
pstmt = conn.prepareStatement(SP_SQL2);
pstmt.clearParameters();
pstmt.setInt(1,SqlNo);
pstmt.setString(2,firstdt);
pstmt.setString(3,startdt);
rs = pstmt.executeQuery();
double pung2 = 0;
while(rs.next())
{
pung2 = rs.getDouble(1);
} rs.close();
%>
<%
stmt.close();
}catch(Exception e){
out.println(e);
}finally{
try{if(stmt != null) stmt.close();} catch(Exception e){}
try{if(conn != null) conn.close();} catch(Exception e){}
}
%>
<<<<<<<<<<<< SP >>>>>>>>>>
create PROCEDURE [dbo].[SP_EM_002_Proc]
@SqlNo AS INT ,
@firstDt AS VARCHAR(8),
@startDt AS VARCHAR(8),
@PUNGJAN_1 AS DECIMAL(20,0) OUTPUT,
@PUNGJAN_2 AS DECIMAL(20,0) OUTPUT,
@PUNGJAN_3 AS DECIMAL(20,0) OUTPUT
AS ]
[14] Sequence를 가져오는 Stored Procedure의 제작
[01] Sequence를 가져오는 Stored Procedure의 제작
CREATE TABLE sequence (
seqname VARCHAR(20) NOT NULL, --table명
seqnum NUMBER(10) NOT NULL,--일렬번호
PRIMARY KEY (seqname)
);
1. Stored Procedure
CREATE OR REPLACE PROCEDURE sequence_write(
i_seqname IN sequence.seqname%TYPE,
o_seqnum OUT sequence.seqnum%TYPE
)
IS
v_count number;
BEGIN
SELECT count(*) into v_count
FROM sequence
WHERE seqname = i_seqname;
if(v_count>0) then
UPDATE sequence
SET seqnum = seqnum + 1
WHERE seqname = i_seqname;
elsif(v_count=0) then
INSERT into sequence(seqname,seqnum)
VALUES(i_seqname,1);
end if;
COMMIT;
SELECT seqnum
INTO o_seqnum
FROM sequence
WHERE seqname = i_seqname;
END sequence_write;
/
-- 테스트
SELECT * FROM sequence;
DECLARE
o_seqnum number := 0;
BEGIN
sequence_write('bbs', o_seqnum);
DBMS_OUTPUT.PUT_LINE('일련번호 추출: ' || o_seqnum);
END;
/
2. JAVA 에서의 사용
>>>>> Sequence_write.java
package test.plsql;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Sequence_write2 {
public static void main(String[] args) {
try{
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@127.0.0.1:1521:XE";
String id = "soldesk";
String password = "1234";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, id, password);
CallableStatement cstmt = conn.prepareCall("{call sequence_write2(?,?)}");
cstmt.setString(1, "communityData");
// output 파라미터의 지정
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.executeUpdate();
// Output 파라미터를 가져오는 부분, output 파라미터는 2번째
// 임으로 getInt(2)를 지정 합니다.
System.out.println("communityData 일련 번호: " + cstmt.getInt(2));
}catch(Exception e){
System.out.println(e.toString());
}
System.out.println("정상적으로 실행되었습니다.");
}
}
5. 수업
진도:
hw:
6. 할것
오 오늘 드디어 홈페이지 끝냄 :-D
뷰나 트리거 - callablestatement를 쓰는 것이 아니고
storedprocedure만 callablestatmenet를 씀
dbmodeling -> ojt랑 같이 시작하는 것
시간 줄 때 홈페이지 체크 전에 pl-sql 복습 할 것
