IT실전교육,탑크리에듀교육센터: 3월 2014

2014년 3월 31일 월요일

Dynamic SQL(EXECUTE IMMEDIATE, DBMS_SQL패키지) DBMS_SQL, DYNAMIC SQL 이란? ,[오라클교육/오라클/ORACLE강좌/오라클교육잘하는곳/오라클교육추천/ORACLE실무교육/ORACLE/ORACLE교육/ORACLE학원/오라클실무교육]

Dynamic SQL(EXECUTE IMMEDIATE, DBMS_SQL패키지) DBMS_SQL, DYNAMIC SQL 이란? ,[오라클교육/오라클/ORACLE강좌/오라클교육잘하는곳/오라클교육추천/ORACLE실무교육/ORACLE/ORACLE교육/ORACLE학원/오라클실무교육]

Dynamic SQL(EXECUTE IMMEDIATE, DBMS_SQL패키지)

DBMS_SQL, DYNAMIC SQL 이란?　　　

PL/SQL은 Binding이 Compile시에 일어나므로 Database Object의 Name이 Compile시에 고정되어야 하는 등의 제한이 있다. PL/SQL 2.1(RDBMS 7.1)이후 Version에서는 DBMS_SQL Package로 Dynamic SQL Statement의 사용을 가능하게 했는데 이는 Database Object의 Name을 Runtime에 줄 수 있을 뿐 아니라 DDL문장을 기술할 수도 있는 장점이 있다.　　

　* Function Open_Cursor　　　

　 : SQL문의 실행에 필요한 새로운 Cursor를 열고 Cursor ID Number를Return 한다.　　　

　* Function Is_Open　　　

　 : 주어진 Cursor가 현재 Open되어 있으면 TRUE를, 아니면 FALSE를 Return한다.　　　

　* Procedure Parse　　　

　 : Statement를 Check하고 Cursor와 결합시킨다.　　　

　* Procedure Bind_Variable　　　

　 : Program내에서 Data를 저장한 Placeholder의 값을 제공하는 역할을 한다.　　　

　* Procedure Define_Column　　　

　 : Cursor로부터 Select된 Column의 값을 받는 변수를 지정한다.　　　

　* Function Execute　　　

　 : SQL문을 실행하고 처리된 Row의 수를 Return한다. (Insert, Update, Delete인 경우에만　　　

　　　 해당)　　　

　* Function Fetch_Rows　　　

　 : Cursor로부터 Row를 Fetch하고 실제로 Fetch된 Row의 수를 Return한다. 이 Row들은 Buffer에 들어가며, Column_Value를 호출하여 읽어들여야 한다.　　　　

　* Function Execute_And_Fetch　　　

　 : Execute와 Fetch Row를 동시에 수행하고 실제로 Fetch된 Row의 수를 Return한다.　　　

　* Procedure Variable_Value　　　

　 : 주어진 변수의 값을 Return한다.　　　

　* Procedure Column_Value　　　

　 : Fetch_Rows에 의해 Fetch된 Data의 값을 Return한다.　　　

　* Procedure Close_Cursor　　　

　 : Cursor를 닫는다.　　

Dynamic SQL은 사용자의 입력에 의해 SQL문이 작성되거나 다른 STATIC SQL문의 결과에 의해서 SQL문이 생성되는 경우인데 Oracle에서의 Dynamic SQL은 EXECUTE IMMEDIATE 절을 사용하여 embedded dynamic sql을 구현하는 방법과 DBMS_SQL 패키지를 이용하여 동적 SQL을 구현할 수 있습니다.

아래는 두 방법을 이용하여 동적 SQL을 구현한 예제 입니다.

SQL> conn scott/tiger

연결되었습니다.

SQL> CREATE OR REPLACE PROCEDURE CREATE_TABLE1 AS

2 sql_stmt varchar2(4000);

3 BEGIN

4 EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

5 END;

6 /

프로시저가 생성되었습니다.

SQL>-- EXECUTE IMMEDIATE를 실행하기 위해서는 create any table 권한이 필요…!

SQL> exec CREATE_TABLE1;

BEGIN CREATE_TABLE1; END;

1행에 오류:

ORA-01031: 권한이 불충분합니다

ORA-06512: "SCOTT.CREATE_TABLE1", 줄 4에서

ORA-06512: 줄 1에서

SQL> conn / as sysdba

연결되었습니다.

SQL> grant create any table to scott;

권한이 부여되었습니다.

SQL> conn scott/tiger

연결되었습니다.

SQL> exec CREATE_TABLE1;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> desc x;

이름 널? 유형

----------------------------------------- -------- -----------------------

A NUMBER

SQL> CREATE OR REPLACE PROCEDURE CREATE_TABLE2 AS

2 cur integer;

3 rc integer;

4 BEGIN

5 cur := DBMS_SQL.OPEN_CURSOR;

6 DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);

7 rc := DBMS_SQL.EXECUTE(cur);

8 DBMS_SQL.CLOSE_CURSOR(cur);

9 END;

10 /

프로시저가 생성되었습니다.

SQL> drop table x;

테이블이 삭제되었습니다.

SQL> exec CREATE_TABLE2;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> desc x;

이름 널? 유형

----------------------------------------- -------- ------

Y DATE

SQL> -- 다음은 bind variable을 사용한 예제 입니다.

SQL> CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS

2 v_cursor integer;

3 v_dname char(20);

4 v_rows integer;

5 BEGIN

6 v_cursor := DBMS_SQL.OPEN_CURSOR;

7 DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS

_SQL.V7);

8 DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);

9 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);

10 v_rows := DBMS_SQL.EXECUTE(v_cursor);

11 loop

12 if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then

13 exit;

14 end if;

15 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);

16 DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);

17 end loop;

18 DBMS_SQL.CLOSE_CURSOR(v_cursor);

19 EXCEPTION

20 when others then

21 DBMS_SQL.CLOSE_CURSOR(v_cursor);

22 raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcod

e||' '||sqlerrm);

23 END;

24 /

프로시저가 생성되었습니다.

SQL> exec DEPARTMENTS(10);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> set serveroutput on

SQL> exec DEPARTMENTS(10);

Deptartment name: RESEARCH

Deptartment name: SALES

Deptartment name: OPERATIONS

PL/SQL 처리가 정상적으로 완료되었습니다.

아래는 DBMS_SQL을 이용하여 REF CURSOR를 사용한 예제이다.

DBMS_SQL의 ref_cursor지원,DBMS_SQL Dydnamic SQL

SQL> DECLARE

2 type emp_type IS TABLE OF emp%ROWTYPE

3 INDEX BY PLS_INTEGER;

5 v_emps emp_type;

6 v_sql CLOB;

7 v_cursor BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;

8 v_rc SYS_REFCURSOR;

9 v_execute BINARY_INTEGER;

10 BEGIN

11 v_sql := 'SELECT * FROM emp WHERE job = :job';

14 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

17 DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', '&job');

20 v_execute := DBMS_SQL.EXECUTE(v_cursor);

21 v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor);

23 LOOP

24 FETCH v_rc BULK COLLECT INTO v_emps LIMIT 100;

25 FOR i IN 1 .. v_emps.COUNT LOOP

26 DBMS_OUTPUT.PUT_LINE('Emp = ' || v_emps(i).ename);

27 END LOOP;

28 EXIT WHEN v_rc%NOTFOUND;

29 END LOOP;

30 CLOSE v_rc;

32 END;

33 /

job의 값을 입력하십시오: SALESMAN

구 17: DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', '&job');

신 17: DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', 'SALESMAN');

Emp = ALLEN

Emp = WARD

Emp = MARTIN

Emp = TURNER

PL/SQL 처리가 정상적으로 완료되었습니다.

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=220

오라클자바커뮤니티에서 운영, 개발자 전문교육, 개인80%환급 오엔제이프로그래밍실무교육센터(www.onjprogramming.co.kr)

평일주간(9:30~18:30) 개강
(4/07)[기업100%환급]SQL기초에서 Schema Object까지
(4/07)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(4/14)C#4.0,ADO.NET,Network 프로그래밍
(4/14)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(4/14)[기업100%환급]PL/SQL,ORACLE HINT,TUNING

평일야간(19:00~22:00) 개강
(4/01)안드로이드개발자과정
(4/04)웹퍼블리싱 마스터
(4/04)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(4/04)C#,ASP.NET마스터
(4/07)SQL초보에서실전전문가까지
(4/08)Spring3.X, MyBatis, Hibernate실무과정

주말(10:00~18:00) 개강
(4/05)웹퍼블리싱 마스터
(4/05)닷넷실무자를위한WPF개발자과정
(4/05)Spring3.X, MyBatis, Hibernate실무과정
(4/05)SQL초보에서실전전문가까지
(4/12)C#,ASP.NET마스터
(4/12)안드로이드개발자과정
(4/12)JAVA기초에서실무까지

(Spring3.2 Board) Spring 3.2에서 게시판 작성하기[6. DAO 만들기] , [자바교육/자바강좌/SpringFramework/스프링교육/자바교육잘하는곳/자바교육추천/자바실무교육/JAVA/JAVA교육/JAVA학원/JAVA실무교육]

(Spring3.2 Board) Spring 3.2에서 게시판 작성하기[6. DAO 만들기] , [자바교육/자바강좌/SpringFramework/스프링교육/자바교육잘하는곳/자바교육추천/자바실무교육/JAVA/JAVA교육/JAVA학원/JAVA실무교육]

데이터베이스 SQL문장을 실행하기 위한 DAO클래스를 만들어 보자.

우선 BoardDAO라는 인터페이스를 만든 후 이를 구현한 SpringBoardDAO를 만들자. SpringBoardDAO는 configBoard.xml 파일에서 빈으로 등록되어 스프링 프레임워크가 기동될 때 WebApplicationContext에 의해 boardDAO라는 이름의 빈으로 등록되어 서비스 된다.:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

BoardDAO 인터페이스 에서는 게시판 리스트 보기를 위한 boardList()만 정의한다.

[BoardDAO.java]

package onj.board.dao;

import java.util.List;

import onj.board.model.BoardDTO;

import org.springframework.dao.DataAccessException;

public interface BoardDAO {

public List<BoardDTO> boardList() throws DataAccessException;

}

아래 SpringBoardDAO에서 setDataSource를 통해 DataSource를 세터주입 받는데 boardConfig.xml의 다음 구문에 의해 주입 받는다.

(property의 name값에 대한 set method가 있어야 하는데 SpringBoardDAO에 setDataSource 메소드가 정의되어 있어야 한다.)

</property>

</bean>

Spring DI중 하나인 세터주입(Setter Inject)에 대해서는 다음 URL을 참고하자.

http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecSpring&wr_id=193&page=3

jdbcTemplate객체의 query 명령문에 의해 SELECT문장을 실행하며 인자로 넘겨 준 RowMapper의 maprow 구현에 의해 실행된 결과를 한 건씩 BoardDTO에 담으며 최종 결과물을 boardList에 담아서 리턴해 준다. 즉 모든 게시물을 읽기 위해서 new RowMapper()에서 mapRow를 구현 한 것이다.

다음은 전체 소스코드이다.

[SpringBoardDAO.java]

package onj.board.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import javax.sql.DataSource;

import onj.board.model.BoardDTO;

import org.springframework.dao.DataAccessException;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

public class SpringBoardDAO implements BoardDAO {

private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource){

this.jdbcTemplate = new JdbcTemplate(dataSource);

}

@Override

public List<BoardDTO> boardList() throws DataAccessException {

List<BoardDTO> boardList = null;

String sql = "select * from board";

boardList = jdbcTemplate.query(sql,

new RowMapper() {

public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

BoardDTO board = new BoardDTO();

board.setSeq(rs.getInt("seq")); board.setName(rs.getString("name")); board.setPasswd(rs.getString("passwd")); board.setTitle(rs.getString("title")); board.setContent(rs.getString("content")); board.setFileName(rs.getString("filename"));

board.setDate(rs.getString("regdate"));

board.setReadCount(rs.getInt("readcount"));

board.setReply(rs.getInt("reply"));

board.setReply_step(rs.getInt("reply_step"));

board.setReply_level(rs.getInt("reply_level"));

return board;

}

});

return boardList;

}

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecSpring&wr_id=241

(Spring3.2 Board) Spring 3.2에서 게시판 작성하기[5. BoardDTO 만들기 ] ,[자바교육/자바강좌/SpringFramework/스프링교육/자바교육잘하는곳/자바교육추천/자바실무교육/JAVA/JAVA교육/JAVA학원/JAVA실무교육] 게시판 리스트 제작을 먼저 하기로 하고 BoardDTO 부터 만들어 보자.:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> BoardDTO는 게시판 테이블과 구조가 같은 도메인객체로서 게시판 리스트 보기 같은 곳에서 전체 게시물을 읽어 하나씩 BoardDTO에 담아서 LIST 객체를 이용하여 JSP로 전송해 준다. DTO라는 이름처럼 웹 애플리케이션에서 서로 다른 Layer 에서 데이터를 전송하기 위한 객체로서의 역할을 하는 클래스이다. [BoardDTO.java] package onj.board.model; public class BoardDTO { private int seq; private String name; private String passwd; private String title; private String content; private String fileName; private String regdate; private int readCount; private int reply; private int reply_step; private int reply_level; public BoardDTO() {} public BoardDTO(String name, String passwd, String title, String content, String fileName) { this.name = name; this.passwd = passwd; this.title = title; this.content = content; this.fileName = fileName; } public BoardDTO(String name, String passwd, String title, String content, String fileName, int readCount) { this.name = name; this.passwd = passwd; this.title = title; this.content = content; this.fileName = fileName; this.readCount = readCount; } public int getSeq() { return seq; } public void setSeq(int seq) { this.seq = seq; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPasswd() { return passwd; } public void setPasswd(String passwd) { this.passwd = passwd; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } public String getFileName() { return fileName; } public void setFileName(String fileName) { this.fileName = fileName; } public String getRegdate() { return regdate.substring(0, 10); //2013-07-15 형태 } public void setDate(String regdate) { this.regdate = regdate; } public int getReadCount() { return readCount; } public void setReadCount(int readCount) { this.readCount = readCount; } public int getReply() { return reply; } public void setReply(int reply) { this.reply = reply; } public int getReply_step() { return reply_step; } public void setReply_step(int reply_step) { this.reply_step = reply_step; } public int getReply_level() { return reply_level; } public void setReply_level(int reply_level) { this.reply_level = reply_level; } } [출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecSpring&wr_id=240 오라클자바커뮤니티에서 운영, 개발자 전문교육, 개인80%환급 오엔제이프로그래밍실무교육센터(www.onjprogramming.co.kr) 평일주간(9:30~18:30) 개강 (4/07)[기업100%환급]SQL기초에서 Schema Object까지 (4/07)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지 (4/14)C#4.0,ADO.NET,Network 프로그래밍 (4/14)[기업100%환급]Spring ,MyBatis,Hibernate실무과정 (4/14)[기업100%환급]PL/SQL,ORACLE HINT,TUNING 평일야간(19:00~22:00) 개강 (4/01)안드로이드개발자과정 (4/04)웹퍼블리싱 마스터 (4/04)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 (4/04)C#,ASP.NET마스터 (4/07)SQL초보에서실전전문가까지 (4/08)Spring3.X, MyBatis, Hibernate실무과정 주말(10:00~18:00) 개강 (4/05)웹퍼블리싱 마스터 (4/05)닷넷실무자를위한WPF개발자과정 (4/05)Spring3.X, MyBatis, Hibernate실무과정 (4/05)SQL초보에서실전전문가까지 (4/12)C#,ASP.NET마스터 (4/12)안드로이드개발자과정 (4/12)JAVA기초에서실무까지

(Spring3.2 Board) Spring 3.2에서 게시판 작성하기[5. BoardDTO 만들기 ] ,[자바교육/자바강좌/SpringFramework/스프링교육/자바교육잘하는곳/자바교육추천/자바실무교육/JAVA/JAVA교육/JAVA학원/JAVA실무교육]

게시판 리스트 제작을 먼저 하기로 하고 BoardDTO 부터 만들어 보자.:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

BoardDTO는 게시판 테이블과 구조가 같은 도메인객체로서 게시판 리스트 보기 같은 곳에서 전체 게시물을 읽어 하나씩 BoardDTO에 담아서 LIST 객체를 이용하여 JSP로 전송해 준다. DTO라는 이름처럼 웹 애플리케이션에서 서로 다른 Layer 에서 데이터를 전송하기 위한 객체로서의 역할을 하는 클래스이다.

[BoardDTO.java]

package onj.board.model;

public class BoardDTO {

private int seq;

private String name;

private String passwd;

private String title;

private String content;

private String fileName;

private String regdate;

private int readCount;

private int reply;

private int reply_step;

private int reply_level;

public BoardDTO() {}

public BoardDTO(String name, String passwd, String title, String content, String fileName) {

this.name = name;

this.passwd = passwd;

this.title = title;

this.content = content;

this.fileName = fileName;

}

public BoardDTO(String name, String passwd, String title, String content, String fileName, int readCount) {

this.name = name;

this.passwd = passwd;

this.title = title;

this.content = content;

this.fileName = fileName;

this.readCount = readCount;

}

public int getSeq() {

return seq;

}

public void setSeq(int seq) {

this.seq = seq;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getPasswd() {

return passwd;

}

public void setPasswd(String passwd) {

this.passwd = passwd;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public String getContent() {

return content;

}

public void setContent(String content) {

this.content = content;

}

public String getFileName() {

return fileName;

}

public void setFileName(String fileName) {

this.fileName = fileName;

}

public String getRegdate() {

return regdate.substring(0, 10); //2013-07-15 형태

}

public void setDate(String regdate) {

this.regdate = regdate;

}

public int getReadCount() {

return readCount;

}

public void setReadCount(int readCount) {

this.readCount = readCount;

}

public int getReply() {

return reply;

}

public void setReply(int reply) {

this.reply = reply;

}

public int getReply_step() {

return reply_step;

}

public void setReply_step(int reply_step) {

this.reply_step = reply_step;

}

public int getReply_level() {

return reply_level;

}

public void setReply_level(int reply_level) {

this.reply_level = reply_level;

}

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecSpring&wr_id=240