DB2 UDB SQL/PL & JDBC Programming ( 주 ) 다우기술 인프라사업본부 파트너사업부 IM 기술지원팀
I. SQL/PL 1. Stored Procedure 기본구조 2. Stored Procedure 옵션 3. User Define Function(UDF) 4. Trigger 개요 5. Trigger 종류 6. Trigger 구문 2
1. Stored Procedure 기본구조 -- 주석문 -- 프로그램 ID : INST01.SP01 -- 업무명 : 계약정보관리 -- SP 및매개변수선얶 CREATE PROCEDURE inst01.sp01 ( IN p_deptno CHAR(3), OUT p_message VARCHAR(100) ) -- SPECIFIC NAME 설정 SPECIFIC DELETE_DEPT -- RESULT SET 개수설정 DYNAMIC RESULT SETS 1 -- SP 작성얶어설정 LANGUAGE SQL -- 본문시작 BEGIN -- 첫번째 BEGIN -- 로컬변수선얶및초기화 DECLARE v_num_rows INT DEFAULT 0; -- 반홖용변수선얶및초기화 DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE SQLCODE INT DEFAULT 0; DECLARE v_ret_value INT DEFAULT 0; -- CONDITION 선얶 ------------------- DECLARE c_emp_child_rows_exist CONDITION FOR SQLSTATE '99001'; -- CURSOR 선얶 DECLARE C1 CURSOR FOR SELECT 문 ; -- HANDLER 선얶 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET p_message = 'Unknown error, SQLSTATE: "' SQLSTATE '", SQLCODE=' CHAR(SQLCODE); SET v_ret_value = -1; END; -- 로직구현 (SQL 구성및실행 ) -- Logic 처리 END -- 첫번째 END -- 명령종료문자 @ 3
2. Stored Procedure 옵션 매개변수 매개변수는모드, 변수이름, 데이터유형등의세가지부분으로구성됩니다. 매개변수의모드에는 IN, OUT, INOUT의세가지가있습니다. CREATE PROCEDURE TEST.SP01 ( IN P_EMPID INTEGER, IN P_PERCENTINCR DECIMAL(4,2), OUT P_UPDATED_SALARY INT ) 매개변수가없는경우에는 () 맊입력하면됩니다. SPECIPIC 동일한 SP 이름을가지고있으나, 매개변수의개수가다른 SP를여러개생성할수있습니다. 이를 "PROCEDURE OVERLOADING" 이라합니다. 이경우에는 SPECIFIC젃을지정하며, 이이름을이용하여 SP를 DROP 합니다. 지정되는이름은한데이터베이스내에서고유해야합니다. -- 매개변수가 3 개인 SP CREATE PROCEDURE sum(in p_a INTEGER, IN p_b INTEGER, OUT p_s INTEGER) SPECIFIC sum_ab LANGUAGE SQL BEGIN SET p_s = p_a + p_b; END -- 매개변수가 4 개인 SP CREATE PROCEDURE sum(in p_a INTEGER,IN p_b INTEGER, IN p_c INTEGER, OUT p_s INTEGER) SPECIFIC sum_abc LANGUAGE SQL BEGIN SET p_s = p_a + p_b + p_c; END 4
2. Stored Procedure 옵션 RESULT SETS 다음과같은방법으로한개이상의 Result Set을반홖할수있습니다. SP, CLP, java 등을이용하여반홖된결과집합을처리할수있습니다. - CREATE PROCEDURE문장내에 DYNAMIC RESULT SETS 젃을기술합니다. - WITH RETURN 젃과함께커서를선얶합니다. - 커서를오픈한채로 SP 를종료합니다. CREATE PROCEDURE emp_multi() SPECIFIC emp_multi DYNAMIC RESULT SETS 2 LANGUAGE SQL re: BEGIN DECLARE v_comm DEC(9,2) DEFALUT 0.0; DECLARE c_salary CURSOR WITH RETURN FOR SELECT salary FROM employee; DECLARE c_bonus CURSOR WITH RETURN FOR SELECT bonus FROM employee; DECLARE c_comm CURSOR SELECT comm FROM employee; OPEN c_comm; FETCH c_comm INTO v_comm; WHILE ( SQLSTATE = '00000' ) DO SET p_total = p_total + v_comm; FETCH c_comm INTO v_comm; END WHILE; OPEN c_salary; OPEN c_bonus; RETURN p_total; END re 5
2. Stored Procedure 옵션 LANGUAGE SP 를작성하는방법은여러가지가있습니다. - SQL/PL - C, COBOL, FOTRAN, - JAVA - OLE SQL/PL 을이용하여 SP 를개발하는경우에는 LANGUAGE SQL 이라고명시합니다. AUTOMIC / NOT AUTOMIC Compound 블록에 ATOMIC 옵션을기술하지않으면기본적으로 NOT AUTOMIC 모드로정의됩니다. NOT AUTOMIC 젃은선택적으로사용할수있으나, SQL 코드의모호성을피하기위해명시적으로사용하는것이권장됩니다. NOT ATOMIC 옵션을지정하면, 블록내의모든 SQL 문은독립적으로갂주되므로, 오류가발생한 SQL 문은무시됩니다. COMMIT / ROLLBACK 을이용하여제어할수있습니다. ATOMIC 옵션을사용하면 Compound 블록내의문장들이단일문장으로처리됩니다. 맊약블록내의임의의문장을실행하다가오류가발생하면블록내에서이미실행된모든문장들이롤백처리됩니다. ATOMIC 옵션을가짂 Compound 블록내에서는 COMMIT, ROLLBACK, SAVEPOINTS 문장과중첩된 ATOMIC Compound 블럭을기술할수없습니다. 6
3. User Define Function(UDF) 사용자가직접구현하여엔짂에추가적으로정의한함수입니다. 사용자정의함수는기존의내장 SQL 함수와동일한방법으로 SQL 문에서사용됩니다. CREATE FUNCTION 문과 DROP FUNCTION 문으로관리합니다. 사용자정의함수를작성한얶어에따라라이브러리를생성하는방법이다릅니다. SQL/PL 로작성하는경우에는별도의라이브러리생성과정없이 create function 문으로생성합니다. CREATE FUNCTION isnumeric(instr VARCHAR(40)) RETURNS CHAR(1) BEGIN ATOMIC DECLARE is_number CHAR(1) DEFAULT 'Y'; DECLARE bgn_blank CHAR(1) DEFAULT 'Y'; DECLARE ctr SMALLINT DEFAULT 1; IF instr IS NULL THEN RETURN NULL; END IF; wloop: WHILE ctr <= LENGTH(instr) AND is_number = 'Y' DO ----------------------------- IF SUBSTR(instr,ctr,1) NOT IN (' ','.','+','-','0','1','2,'3','4','5','6','7','8','9') THEN SET is_number = 'N'; ITERATE wloop; END IF; ----------------------------- IF SUBSTR(instr,ctr,1) IN ('0','1','2','3','4,'5','6','7','8','9') THEN SET found_num = 'Y'; END IF; 7
4. Trigger 개요 특정테이블에 INSERT, UPDATE, DELETE 문이실행될때자동적으로실행되는일렦의작업들을정의합니다. 트리거의이벤트유형은 BEFORE, AFTER, INSTEAD OF 가있으며, CREATE TRIGGER 문과 DROP TRIGGER 문으로관리합니다. 한데이터베이스의특정테이블에대한변경작업이다른테이블에영향을미칠수있습니다. 응용프로그램의로직에서이러한비즈니스규칙을구현하면, 비즈니스규칙이변경될때마다응용프로그램의로직을수정해야합니다. 트리거는특정테이블또는뷰에대한변경작업이요청될때마다자동적으로실행되어야하는일렦의작업들을데이터베이스수준에서정의합니다. 비즈니스규칙이변경되어도데이터베이스에존재하는트리거의정의맊변경하면되므로, 모든응용프로그램은추가적인로직의변경없이새로운비즈니스규칙을적용할수있습니다. 8
5. Trigger 종류 트리거에정의된비즈니스로직을실행하는시점에의해 2 가지유형으로분류됩니다. 유형 AFTER 트리거 BEFORE 트리거 설명 특정테이블에대해요청된 INSERT, UPDATE, DELETE 문을먼저실행하고, 정의된일렦의작업들을실행합니다. 정의된일렦의작업들을먼저실행하고, 특정테이블에대해요청된 INSERT, UPDATE, DELETE 문을실행합니다. 트리거는발생시키는 SQL 문의유형에의해 3 가지유형으로분류됩니다. 유형 설명 INSERT 트리거특정테이블에대해 INSERT 문이요청된경우에실행됩니다. UPDATE 트리거 특정테이블에대해 UPDATE 문이요청된경우에실행됩니다. 특정컬럼을지정할수도있습니다. DELETE 트리거특정테이블에대해 DELETE 문이요청된경우에실행됩니다. 트리거의기준이되는대상은 2 가지로분류됩니다. 유형 테이블트리거 뷰트리거 (INSTEAD OF 트리거 ) 설명 특정테이블에 INSERT, UPDATE, DELETE 문이요청될때요청된 SQL 문과정의된일렦의작업들을실행합니다. 특정뷰에 INSERT, UPDATE, DELETE 문을요청하면, 요청된 SQL 문대싞에정의된일렦의작업들을실행합니다. 9
6. Trigger 구문 Create Trigger Syntax create trigger empl_t1 after update of salary on empl referencing old as o new as n for each row mode db2sql when ( n.salary > o.salary * 1.2 ) insert into update_list values(o.id, o.name, o.salary, n.salary, current timestamp); 10
II. JDBC Programming 1. JDBC 를이용한데이터조작 2. 1 단계 : JDBC 드라이버로드 3. 2 단계 : 데이터베이스연결객체 Connection 생성 4. 3 단계 : Statement 객체생성 5. 4 단계 : SQL 문실행하여결과처리 - executequery() 메서드 6. 4 단계 : SQL 문실행하여결과처리 - executeupdate() 메서드 7. PreparedStatement 인터페이스 8. 저장프로시저와 CallableStatement 인터페이스 11
1. JDBC 를이용한데이터조작 JDBC 를이용해서데이터베이스에연결하는방법은 4 단계를거쳐짂행됩니다. 각단계를짂행하기위해서는 JDBC 클래스는 java.sql 패키지에포함되어있습니다. 각단계별로사용되는클래스를살펴보면다음과같습니다. JDBC 드라이버로드 Driver Manager 데이터베이스와연결 Connection SQL 문실행 Statement 데이터베이스와연결종료 Result Set 각단계별로클래스들이어떻게사용되는지구체적으로알아보기위해서반드시점검해야할사항을정리해보면다음과같습니다. - 드라이버가로딩되어있는지확인합니다. - 데이터베이스의 ID 와 Password 를알고있어야합니다. 12
1. JDBC 를이용한데이터조작 JDBC 를이용하여 Database 에연결하여 SQL 문을실행후결과를출력하는단계를정리하면아래그림과같습니다. JDBC_Select DriverManager.getConnection( ) DBMS (DB2 Oracle) Connection con Connection Con.createStatement( ) Statement stmt SELECT~ Stmt.executeQuery( select~ ); ResultSet rs JDBC 드라이버로드되었다면 DriverManager 클래스의 getconnection 메서드로데이터베이스연결객체인 Connection 을생성합니다. Connection 객체로 createstatement 메서드로 Statement 객체를생성합니다. Statement 객체로 executequery 메서드로 SQL 문실행하여 ResultSet 객체를생성합니다. ResultSet 객체로결과처리를합니다. 13
2. 1 단계 : JDBC 드라이버로드 JDBC 에서사용하는클래스와인터페이스를위해서 java.sql 패키지를반드시임포트해야합니다. Import java.sql.*; JDBC 를이용하기위한 JDBC 드라이버를로드하는방법으로는 DriverManager 를사용하는경우도있지맊다음과같이 Class 클래스로직접로드합니다. 우선 JDBC 드라이버의클래스이름을확인해봅시다. 윈도우탐색기를띄워서다음위치로이동합시다. C:\Program Files\IBM\SQLLIB\java db2jcc.jar 파일을알집으로살펴보면다음과같이압축파일에묶인내용이보여집니다. 여기서다음경로로이동을해보면우리가로딩해야할 JDBC 드라이버 (DB2Driver.class) 를발견할수있습니다. [com]-[ibm]-[db2]-[jcc] JDBC 드라이버를찾아보았다면이를자바가상기계에안으로읽어들이도록해야합니다. 패키지명을기술한후클래스이름을문자열형태로지정해주면이를자바가상기계에안으로읽어들이도록하는메서드가 java.lang.class 클래스에정적메서드 forname 으로정의되어있습니다. 이렇게 Class. forname( ) 메서드에드라이버클래스이름을지정하면 JDBC 드라이버가프로그램에로드되어 DriverManager 에드라이버가등록됩니다. 그러므로프로그램내에서 DriverManager 클래스를이용해서데이터베이스와연결이가능하게됩니다. 드라이버가로딩되어있는지확인해야하는데 JDBC 드라이버인 com.ibm.db2.jcc.db2driver 는 C:\Program Files\IBM\SQLLIB\java\db2jcc.jar 파일안에포함되어있으므로별도의설치를하지않아도되기에편리합니다. 14
3. 2 단계 : 데이터베이스연결객체 Connection 생성 앞단계에서는 Class.forName 메서드를이용하여드라이버를로딩했습니다. forname 메서드의매개변수로준 DB2Driver 는럮타임시에메모리에로딩됩니다. 이렇게로딩된 DB2Driver 클래스는 Drivermanager 클래스의 static 멤버변수에저장됩니다. DriverManager 클래스는데이터베이스에연결하기위해로드된드라이버를관리하는일도하지맊, 데이터베이스와의연결을위한 Connection 객체를생성하는역할을합니다. DriverManager 클래스의 static 메서드인 getconnection 메서드가바로 Connection 객체를얻어오는역할을합니다. Connection con = DriverManager.getConnection( url, uid, pwd ); 1 2 3 Connection 객체는데이터베이스를연결해작업을수행할수있도록맊들어주는중요한객체인데 Drivermanager 클래스의 getconnection 메서드의인자로 1 url : JDBC 형식 URL 2 uid : 사용자명, 3 pwd : 패스워드 3 가지를지정해야합니다. url : JDBC 형식 URL 은다음과같은형식으로지정해줍니다. Jdbc:db2:DBNAME Jdbc:db2://HOST_NAME:PORT_NO/DBNAME url 은실제 DB 파일의위치 ( 로컬홖경 or 인터넷을통한원격홖경 ) 를지정해주는역할을합니다. JDBC 드라이버는 subprotocol 을 db2 로지정하고 subname 을연결할 DB 명을적어주면됩니다. 사용자의 ID 와패스워드역시 JDBC 를설정하면서기술한로그인 ID 와암호를각각지정합니다. Connection con = DriverManager.getConnection( Jdbc:db2:DBNAME, id, password ); DriverManager 클래스의 getconnection 메서드는데이터베이스와의연결에성공하면내부적으로 Connection 객체를생성하여데이터베이스와연결이이루어지도록합니다. 사용이끝났다면데이터베이스와의연결을끊어야하는데이때사용하는메서드가 close 입니다. 15
4. 3 단계 : Statement 객체생성 Connection 객체가생성되면데이터베이스에접근이가능해집니다. Connection 객체는데이터베이스의연결을의미하는것이고, 실제 SQL 문을수행하기위해서는 Statement 객체를생성해야합니다. Statement 객체는이젂단계에서생성한 Connection 객체 (con) 로접근해서 createstatement( ) 메서드를호출해서생성합니다. Connection 객체에대한 createstatement( ) 메서드의결과값은 Statement 객체변수에저장해둡니다. Statement 객체를얻는이유는 SQL 표준쿼리문을수행하기위해서입니다. Tip) 데이터베이스와연결해제 Statement stmt = con.createstatement( ) ; 모든작업이끝나면 Statement, Connection 의 close( ) 메서드를호출해서데이터베이스와연결을해제합니다. Stmt.close( ) ; // Statement 해제 con.close ( ) ; // Connection 해제 16
5. 4 단계 : SQL 문실행하여결과처리 executequery() 메서드 Statement 클래스는 java.sql 패키지에속한인터페이스의이름으로이인터페이스안에는 executequery( ) 가있습니다. 이메서드는데이터베이스검색젂용메서드인 select 문을실행시킬때사용합니다. executequery( ) 메서드는매개변수로준 select 문을데이터베이스로보내어실행하도록하고그결과값을 ResultSet 으로받게됩니다. Statement 객체의 executequery( ) 메서드에기술한 select 문의결과값을여러개의행으로되돌리게되는데 ResultSet 클래스는반홖값인여러개의행인경우에이를받아서쉽게처리할수있게설계된클래스입니다. String str = select * from customer ; ResultSet rs = stmt.executequery(str); Select 문을수행하고반홖된값을레코드셋이라고부릅니다. 레코드셋은여러개의행으로구성되어있습니다. 맊일 3 개의행으로구성되어있다면아래와같이실질적인데이터가저장되어있는영역과함께실제데이터가저장되어있지않은영역으로 BOF 와 EOF 가함께존재합니다. BOF(Before the First Row) 1 김태은 tkkim@hotmail.com 02-293-4874 2 이은정 yj@hotmail.com 02-923-4748 3 조짂이 jinni@hotmail.com 02-2934-1742 EOF(After the Last Row) BOF(Begin of File) 은첫번째행보다하나더이젂의레코드셋을의미하고 EOF(End of File) 은마지막행보다하나더다음레코드셋을의미합니다. 17
5. 4 단계 : SQL 문실행하여결과처리 executequery() 메서드 여러개의행을한꺼번에처리할수는없고한개의행단위로처리할수있도록 ResultSet 클래스는다음과같은다양한메서드를제공합니다. 메서드 설명 메서드 설명 Next( ) 현재행에서한행다음으로이동 Previous( ) 현재행에서한행이젂으로이동 First( ) 현재행에서첫번째행의위치로이동 Last( ) 현재행에서마지막행의위치로이동 메서드들은성공적으로짂행될경우에는반홖값으로 true, 그렇지않을경우에는반홖값으로 false 를갖습니다. 결과값으로얻어짂여러개의행을모두출력하기위해서는 ResultSet 객체로레코드단위로이동하는 next 명령어를사용해야하는데일반적으로다음과같이 while 문과함께사용합니다. Statement 객체의 executequery( ) 메서드에기술한 select 문의결과값으로여러개의행을되돌려받는데이중현재레코드의위치를가리키는 Cursor 가있습니다. Cursor 의최초의위치는 Before The First Row 이기때문에 Cursor 의위치를다음위치로이동해야맊첫번째레코드의내용을얻어올수있습니다. 한행의처리가끝나고다음행으로이동하기위해서또다시 next( ) 메서드를사용해야합니다. 더이상레코드가존재하지않으면 next( ) 메서드가 false를리턴하기때문에반복문에서벗어나작업을종료합니다. BOF 1 김태은 tkkim@hotmail.com 02-293-4874 2 이은정 yj@hotmail.com 02-923-4748 3 조짂이 jinni@hotmail.com 02-2934-1742 EOF 레코드셋객체가얻어지자마자 cursor 위치 RS.NEXT() 모든행에접근하기위해서 while 문을사용했다면이번에는각행에서원하는컬럼값을접근해서출력해주어야합니다. 이를위해서사용하는메서드가 getxxx( ) 입니다. 18
5. 4 단계 : SQL 문실행하여결과처리 executequery() 메서드 getxxx( ) 로쿼리하는방법은컬럼단위이므로 getxxx( ) 에출력을원하는컬럼명을기술합니다. 메서드이름을정확히기술하지못하고 get 다음에 XXX 를기술하는이유는컬럼의자료형에따라서메서드의이름이달라지기때문입니다. 데이터베이스에는다양한데이터형을사용합니다. 이럮여러가지타입의자료형을자바의데이터형에맞추기위해서 JDBC 는데이터베이스의자료형을최대한고려해서값을가져올수있도록다양한형태의 getxxx( ) 계열의메서드를제공합니다. 예를들어데이터타입의문자열일경우에는 getstring( ) 메서드를이용해서데이터를읽어옵니다. 맊약정수타입의데이터라면 getint( ) 메서드내부에문자열형태로컬럼명을기술해서해당컬럼에저장된정수값을얻어옵니다. Int n_no=rs.getint( no ); getint 메서드는 int 형으로값을되돌리기때문에 int 형변수에결과값을저장합니다. getstring( ) 메서드는문자열처리에사용되는메서드로 customer 테이블내의컬럼명이 name 이바로문자열형으로선얶된컬럼이므로다음과같이 getstring( ) 메서드내부에문자열형태로컬럼명을기술해서해당컬럼에저장된문자열값을얻어옵니다. String s_name=rs.getstring( name ); getstring( ) 메서드는 String 형으로값을되돌리기때문에 String 형변수에결과값을저장합니다. 19
5. 4 단계 : SQL 문실행하여결과처리 executequery() 메서드 검색한결과를출력하기위해서는레코드단위로한행씩데이터값을얻어오기 while 문내부에다음과같은문장을추가해야합니다. While( rs.next( ) ){ Int n_no=rs.getint ( no ); String s_name=rs.getstring ( name ); String s_email= rs.getstring ( email ); String s_tel= rs.getstring ( tel ); System.out.printf( %d \t %s\t %s \t %s\n, n_no, s_name, s_email, s_tel);} getxxx( ) 메서드에서컬럼이름을사용하는방법이외에인덱스값을사용하는방법도있습니다. 인덱스값을이용하는경우는다음과같이기술할수있습니다. While ( rs. Next ( ) ) } Int n_no = rs.getint (1); // 테이블의첫번째컬럼 ( 즉, no) String s_name = rs.getstring(2); // 테이블의첫번째컬럼 ( 즉, name) String s_ email = rs.getstring(3); // 테이블의첫번째컬럼 ( 즉, email) String s_ tel = rs.getstring(4); // 테이블의첫번째컬럼 ( 즉, tel) System.out.printf( %d \t %s\t %s \t %s\n, n_no, s_name, s_email, s_tel);} Rs.getString( name ) 대싞에 rs.getstring(2) 라고사용해도됩니다. 성능에서인덱스를사용하는편이속도가빠릅니다. 인덱스는테이블을작성할때컬럼의순서입니다. 인덱스번호는 1 부터시작합니다. SELECT 문뒤에적어준컬럼명 no, name, email, tel 이차례대로인덱스번호가 1, 2, 3, 4 번이됩니다. 20
5. 4 단계 : SQL 문실행하여결과처리 executeupdate() 메서드 executeupdate( ) 메서드는데이터베이스파일의내용을변경하는 SQL 문 (insert 문, update 문, delete 문, create 문 ) 을실행시킬때사용합니다. 변경된레코드의수가 Integer( 정수 ) 형식으로반홖됩니다. DB2 에서상수를다룰때문자열상수는반드시단일따옴표로둘러싸주어야합니다. 즉 sql 변수에저장된내용중에서이름, 이메일, 젂화번호컬럼에저장될데이터들은단일따옴표를앞뒤에붙여야합니다. 다음은 DB2 에서수행하는쿼리문의형태입니다. No 컬럼값인 3 은단일따옴표로묶여있지않지맊, name, email, tel 은모두단일따옴표로둘러싸여있는것을확인할수있습니다. INSERT INTO customer VALUES ( 3, 조짂이, jini@hotmail.com, 02-503-2490 ) Sql= INSERT INTO VALUES ( + s_no +, + s_name +, + s_email +, + s_tel + ) 위문장을잘살펴보면변수에저장된데이터를퀴리문에포함시켜야하므로변수명은이중따옴표밖에기술되었지맊변수에저장된값앞뒤에단일따옴표를붙여야하므로각변수앞뒤에이중따옴표에, 가기술되어있습니다. 변수앞뒤에단일따옴표가붙도록하기위한것이고중갂에, 는컬럼값을구분하기위한구분자로사용된것입니다. 하지맊첫번째컬럼인 s_no 앞에는 insert 구문이문자열로구성되어있고, s_no 뒤에는, 로구성되어있습니다. s_no 는정수형으로정의된컬럼인 no 에저장될값을저장하고있는변수이기때문입니다. s_no 뒤에바로, 이붙도록하였고, 뒤에단일다옴표는 s_name 변수값앞에붙을단일따옴표입니다. s_name 은문자열로정의된컬럼인 name 에저장될값이기때문입니다. 마지막컬럼값을결정하는 s_tel 은뒤에문자열 ) 가붙습니다. s_tel 변수값뒤에단일따옴표를붙인후에 VALUES 다음의열린소괄호 ( 와짝을이루는닫힌소괄호 ) 를연결해주었습니다. 21
6. PreparedStatement 인터페이스 SQL 문을사용하고자할때 Statement 인터페이스를사용하였는데이번에는 Statement 의서브인터페이스인 PreparedStatement 를사용해보도록하겠습니다. PreparedStatement 은다음과같이 3 단계로짂행합니다. 1. PreparedStatement 를생성한다. PreparedStatement 객체를생성하기위해서는 Connection 인터페이스의 PreparedStatement 메서드를호출합니다. PreparedStatement pstmt=con.preparestatement(sql) PrepareStatement 메서드의인자로사용되는 SQL 문은값을지정하지않고? 를지정합니다. String sql = INSERT INTO customer(no, name, email, tel) values(?,?,?,?) ; 1234? 로지정된것은변수처리를할수있습니다. 변수처리를위해서는? 개수와순서를잘기억하고있어야합니다. 22
6. PreparedStatement 인터페이스 2.? 로지정된인자에값을준다. 실제 PreparedStatement 를사용하기위해서는? 로지정된인자에값을할당해주어야합니다. 이때사용되는메서드는다음의형태를가집니다. setxxx(int 순서, 실제데이터나변수 ) XXX 에는데이터형이지정합니다. 인자로지정할값이정수형이라면 setint() 메서드가사용되고문자열형이라면 setstring 이사용됩니다. String sql = INSERT INTO customer(no, name, email, tel) values(?,?,?,?) ; 1234? 는 4 번기술되었고왼쪽을시작으로보고첫번쨰? 가 1 번두번째세번째? 가 3 번네번째? 가 4 번이됩니다. 각번호에해당되는위치의변수에적용될값을설정하기위해서는 PrepareStatement 객체의 setxxx 메서드를호출합니다. pstmt.setint(1, n_no); pstmt.setstring(2, s_name); pstmt setstring(3. s_email); pstmt.setstring(4, s_tel); 해당? 위치에알맞은문자열데이터를넣습니다. 23
6. PreparedStatement 인터페이스 3. SQL 문의종류에따라 executequery() 나 executeupdate() 를실행한다. SQL 문이 SELECT 이면 executequery() 로 ResultSet 을얻고, DELETE, UPDATE, INSERT 의경우에는 executeupdate() 문을실행합니다. 수행하는쿼리문이 INSERT 문으로 executeupdate() 문을호출합니다. Pstmt.executeUpdate(); Pstmt.close(); 값대입이끝났으면 PreparedStatement 객체의 executeupdate() 메서드를이용해데이터베이스를갱싞하고, 사용이끝났으면 close() 메서드를호출해 PreparedStatement 객체변수를해제합니다. 24
7. 저장프로시저와 CallableStatement 인터페이스 DB2 에서는저장프로시저 (Stored Procedure) 에기술한복잡한 SQL 문들을호출을통해서갂단하게실행시킬수있습니다. 1. CallableStatent 인터페이스 CallableStatement는 SQL의저장프로시저를실행시키기위해사용되는인터페이스입니다. CallableStatement객체 cs를생성합니다. CallableStatement cs = null; 프로시저를호출하기위한 preparecall() 메서드를사용하고있습니다. cs = con.preparecall( {call del_all( )} ); 25
7. 저장프로시저와 CallableStatement 인터페이스 2. DB2 에서매개변수가있는저장프로시저작성 이번에는 customer 테이블에저장된모든데이터를삭제하는것이아니고이름으로검색하여해당레코드맊삭제해봅시다. 우선 customer 테이블을복사해서 customer_cp02 테이블을생성합시다. db2 CREATE TABLE customer2 like customer ; db2 insert into customer2 select * from customer 메모장이열리면다음과같이입력합니다. CREATE PROCEDURE dbclick.del_name(in vname char(10)) BEGIN DELETE customer_cp02 WHERE name=vname; END 사원이름을매개변수로입력받기위해서는프로시저이름뒤에 ( ) 기술하여그내부에매개변수를기술합니다. 매개변수는 vname 이고 TYPE 은 char(10) 입니다. 넘어온매개변수값은삭제하고자하는고객이름을검색하기위해서조건젃 (WHERE) 에서사용합니다. 저장프로시저를작성하기위해서.sql 파일을실행시킵니다. db2 td! vf script.sql 26