Chapter 11 C와 Java를 사용한 SQL 응용 프로그래밍 2018-10-13 Chapter 11 C와 Java를 사용한 SQL 응용 프로그래밍 Copyright © 2004 Pearson Education, Inc.

Chapter Outline 11.1 데이터베이스 프로그래밍 기법과 이슈 11.2 내포된 SQL, 동적 SQL 및 SQLJ 11.3 함수 호출을 이용한 DB 프로그래밍 - SQL/CLI와 JDBC 11.4 데이터베이스 저장 프로시저와 SQL/PSM 11.5 세가지 접근 방법에 대한 비교 11.6 요약

Speed-start Web services 2004 - SOAs and Web services DBMS란? 데이터를 적절하고 효율적인 관리의 필요성이 대두되었는데, 이러한 요구를 만족시키고자 등장한 체계적으로 데이터를 관리하는 시스템 DBMS 의 종류 - 관계형 데이터베이스 관리 시스템 - 객체지향형 데이터베이스 관리 시스템 - 객체관계형 데이터베이스 관리 시스템 관계형 DBMS - 가장 대표적인 DBMS로, 1970년대 중반 E. F. Codd 에 의해 관계형 데이터 모델 개념이 제안됨. - 실세계 데이터들을 2차원적 테이블 형태로 표현 - 데이터 무결성, 트랜잭션 처리 등 기본적 기능이 우수 - 질의어(Query Language)를 사용한 데이터 접근 Oracle Programming ibm.com/developerWorks/webservices - © Copyright 2004 IBM Corporation.

Speed-start Web services 2004 - SOAs and Web services DBMS란? 객체지향형 DBMS - 사용자 정의 타입 지원 - 비정형 복합 정보 모델링 가능 - 객체들 사이의 참조 구조를 이용한 접근 가능 - 객체지향 프로그래밍 언어들과의 구조의 유사성 객체관계형 DBMS - 관계형 및 객체지향형 DBMS의 장점을 수용 - 참조 타입, 중첩 테이블 지원 - 대단위 객체의 저장 추출 - 객체간 상속관계 지원 Oracle Programming ibm.com/developerWorks/webservices - © Copyright 2004 IBM Corporation.

데이터베이스 개념 데이터베이스 시스템(DBS: Database System)의 구성 요소 1장. 데이터베이스 시스템

DBMS 사용 방식/환경

DBMS 사용 방식/환경

데이터베이스 프로그래밍의 개념 ‘프로그래밍’이란 프로그램을 설계하고 소스코드를 작성하여 디버깅하는 과정 데이터베이스 프로그래밍 : DBMS에 데이터를 정의하고 저장된 데이터를 읽어와 데이터를 변경하는 프로그램을 작성하는 과정. 일반 프로그래밍과는 데이터베이스 언어인 SQL을 포함한다는 점이 다름 SQL 전용 프로그램 (SQL) 응용 프로그램 (SQL + 자바) DBMS DBMS DB DB (a) SQL Interface (b) 내포된 프로그래밍 그림 5-1 데이터베이스 프로그래밍

Speed-start Web services 2004 - SOAs and Web services 오라클은 왜 오라클인가? 오라클(Oracle) : 가장 대표적인 관계형 DBMS 중의 하나 1977년 래리 앨린슨이 설립 1983년 회사 이름을 Oracle Corporation 으로 변경 오라클이란 이름은 회사 이름인 동시에 DBMS 제품 이름임 이후 지속적으로 기능을 향상시켜 제품을 출시 버전 8부터 버전번호 뒤에 I (internet)가 붙음 버전 10부터 버전번호 뒤에 g(grid)가 붙음 2008년 현재 버전 11g 까지 출시됨(2007.07) 본서에서는 10g를 기준으로 진행 Oracle Programming ibm.com/developerWorks/webservices - © Copyright 2004 IBM Corporation.

Speed-start Web services 2004 - SOAs and Web services Oracle 10g 2003년 출시 그리드(grid) 특성을 추가함. 대표적 특징 - 그리드 컨트롤(Grid Control) - 프로비저닝(Provisioning) - 부하관리 (Load Balancing) - 자동화 (Automation) “Oracle 10g는 여러 대의 물리적 서버를 한 대의 논리적 서버로 구성하여 다운타임을 최소화 하고 고가용성을 위한 기능을 지원 Oracle Programming ibm.com/developerWorks/webservices - © Copyright 2004 IBM Corporation.

오라클 개요(계속) 3장. 오라클

4.1 데이터베이스 프로그래밍 목적: 필요성: 응용 프로그램에서 데이터베이스 접근 (대화식 인터페이스와 다름) 대화식 인터페이스는 충분하게 편리하지 않음 대부분 데이터베이스 연산은 응용 프로그램을 통해 통해 실행 오늘날, 웹 인터페이스를 이용

데이터베이스 프로그래밍 접근 방법들 범용 프로그래밍 언어내에 내포된 DB 명령문 데이터베이스 함수 라이브러리 데이터베이스 명령문은 호스트 프로그래밍 언어 내에 내포시키는 방법 C언어, Java 등에서 SQL문 사용 데이터베이스 함수 라이브러리 호스트 프로그램에서 데이터베이스 호출을 위해 사용하는 방법 응용 프로그래밍 인터페이스(API: Application Programming Inteface) ODBC, JDBC 새로운 언어의 개발 데이터베이스 모델 및 질의어와 호환 가능하도록 개발 오라클의 PL/SQL, MS SQLServer의 Transact SQL 등

01. 데이터베이스 프로그래밍의 개념 그림 5-2 DBMS 플랫폼과 데이터베이스 프로그래밍의 유형

임피던스 불일치 커서 데이터베이스 모델과 호스트 프로그래밍 언어 사이의 차이점 때문에 발생하는 문제를 언급할 때 사용하는 용어 데이터 타입과 호환성 타입; 각 언어에 대한 바인딩하는 것이 필요 set vs. record-at-a-time processing 개별 애트리뷰트 값들을 추출하기 위해 질의 결과 내에 있는 반복하는 루프 기능이 필요 커서

데이터베이스 프로그래밍의 동작 순서 클라이언트 프로그램은 데이터베이스 서버에 대한 연결을 설정하거나 개방 클라이언트 프로그램은 데이터베이스에서 질의나 갱신을 사용함 데이터베이스 접근이 더 이상 필요할 필요가 없을 때, 클라이언트 프로그램은 연결 종료해야 함

11.2 내포된 SQL, 동적 SQL 및 SQLJ 대부분 SQL 문장들은 COBOL, C, Java와 같은 범용 호스트 프로그래밍 언어에 내포될 수 있음 내포된 SQL 문장은 EXEC SQL 와 END-EXEC (또는 세미콜론(;))에 의해 호스트 프로그래밍 언어 문장으로 구분함 공유 변수들은 SQL 문장 내에서 사용될 때는 콜론(:)을 그 앞에 붙임

예제: 언어 C에서 변수 선언 DECLARE 내에 변수들은 SQL 구문에서(콜론(:)을 붙여 사용) 공유될 수 있거나 사용됨 SQLCODE 는 데이터베이스와 프로그램 사이에 오류와 예외 조건들을 전달하기 위해 사용 int loop; EXEC SQL BEGIN DECLARE SECTION; varchar dname[16], fname[16], …; char ssn[10], bdate[11], …; int dno, dnumber, SQLCODE, …; EXEC SQL END DECLARE SECTION;

내포된 SQL C 프로그래밍 예 loop = 1; while (loop) { prompt (“Enter SSN: “, ssn); EXEC SQL select FNAME, LNAME, ADDRESS, SALARY into :fname, :lname, :address, :salary from EMPLOYEE where SSN == :ssn; if (SQLCODE == 0) printf(fname, …); else printf(“SSN does not exist: “, ssn); prompt(“More SSN? (1=yes, 0=no): “, loop); END-EXEC }

내포된 SQL C 프로그래밍 예 커서(반복자)는 여러 튜플들을 처리하기 위해 필요 FETCH 명령어는 다음 튜플로 커서를 이동할 때 사용 CLOSE CURSOR는 질의 결과에 대한 처리를 종료했음을 나타냄

내포된 SQL C 프로그래밍 예

동적 SQL을 사용하여 실행 시점에 질의 명시 목적: 실행 시 새로운 SQL 문장 수행 동적 수정문을 포함하는 것은 비교적 간단하지만 동적 질의는 훨씬 더 복잡함 검색된 애트리뷰트의 타입과 개수를 프로그램의 컴파일 시간을 알 수 없음

동적 SQL: 예제 EXEC SQL BEGIN DECLARE SECTION; varchar sqlupdatestring[256]; EXEC SQL END DECLARE SECTION; … prompt (“Enter update command:“, sqlupdatestring); EXEC SQL PREPARE sqlcommand FROM :sqlupdatestring; EXEC SQL EXECUTE sqlcommand;

SQLJ: Java에서 내포된 SQL 명령 SQLJ: Java 내에 내포된 SQL의 표준 SQLJ 번역기는 SQL문을 Java로 변환 Java로 변환된 문장은 JDBC 인터페이스를 통해 실행됨 클래스 라이브러리(예;java.sql)를 가져오는 것이 필요함

Java 데이터베이스 연결 JDBC: Java에서 함수 호출을 사용하여 SQL 데이터베이스를 접근하는 데 사용 JDBC 함수를 이용한 Java 프로그램을 사용하려면 JDBC 드라이버를 설치하는 것이 필요 JDBC는 여러 데이터베이스를 연결하기 위한 프로그램임

JDBC 데이터베이스 접근 단계 JDBC 라이브러리(java.sql.*) 가져오기 JDBC 드리이버를 로드: Class.forname(“oracle.jdbc.driver.OracleDriver”) 적절한 변수들의 선언 연결 객체 생성 (getConnection 이용) Statement 클래스에서 문장 객체를 생성: PreparedStatment CallableStatement

JDBC 데이터베이스 접근 단계 (cont.) 물음표(“?”)기호는 문장 매개변수들을 나타냄 실행시에 매개변수를 C 변수에 바인딩할 때 결정 모든 매개변수들이 프로그램 변수에 바인드 JDBC의 executeQuery 함수를 사용하여 (객체에 의해 참조된)SQL 문장 실행 질의 결과 처리(ResultSet 타입의 객체에 리턴) ResultSet은 이차원 배열이나 테이블과 비슷

Java에서 내포된SQL: 예제 ssn = readEntry(“Enter a SSN: “); try { #sql{select FNAME< LNAME, ADDRESS, SALARY into :fname, :lname, :address, :salary from EMPLOYEE where SSN = :ssn}; } catch (SQLException se) { System.out.println(“SSN does not exist: “,+ssn); return; System.out.println(fname+“ “+lname+… );

SQLJ에서 다중 튜플 SQLJ는 반복자의 두가지 타입을 제공 명명된 반복자(named iterator): 질의 결과에 연관될 때 결과에 있는 애트리뷰트의 이름과 타입들의 목록을 표시 위치 반복자(positional iterator): 질의 결과에 애트리뷰트 타입들의 목록만 표시 FETCH 연산은 질의 결과에서 다음 튜플들을 검색 fetch iterator-variable into program-variable

SQLJ에서 다중 튜플

SQLJ에서 다중 튜플

11.3 함수 호출을 이용한 DB 프로그래밍: SQL/CLI와 JDBC API: 함수 라이브러리를 이용한 동적 데이터베이스 프로그래밍 특징: 전처리기를 필요하지 않음(많은 융통성 제공) 단점: SQL 명령에 대해 구문 검사 및 여타 검사를 실행시에 해야 함

SQL Call Level Interface 여러 데이터베이스들을 한 응용 프로그램에서 좀더 쉽게 접근하기 위해 제공 적절한 라이브러리들 (e.g., sqlcli.h for C) 설치하거나 이용가능해야 함 SQL 문장들은 동적으로 만들어져 호출 함수 내에 문자열 매개변수로 전달됨

SQL/CLI의 구성요소 환경 레코드(environment record) 연결 레코드(connection record) 하나 이상의 데이터베이스 연결을 추적하고 환경 정보를 유지하는 데 사용 연결 레코드(connection record) 특정 데이터베이스 연결을 위해 필요한 정보를 유지 문장 레코드(statement record) 한 SQL 문장을 위해 필요한 정보를 유지 설명 레코드(description record) 튜플이나 매개변수들에 관한 정보를 유지

SQL/CLI를 통한 C 프로그램 작성 단계 SQL/CLI libraries을 포함 레코드 핸들 변수들을 선언 (called: SQLHSTMT, SQLHDBC, SQLHENV, SQLHDEC) 환경 레코드 SQLAllocHandle 사용하여 설정 연결 레코드 SQLAllocHandle 사용하여 설정 문장 레코드 SQLAllocHandle 사용하여 설정

SQL/CLI를 통한 C 프로그램 작성 단계 (con’t.) SQL/CLI 함수 SQLPrepare를 사용하여 준비 SQL/CLI 함수 SQLBindParameter를 사용하여 매개변수들을 프로그램 변수에 바인딩 SQLExecute를 사용하여 SQL 문장을 실행 SQLBindCol 함수를 사용하여 질의 결과 내의 C 프로그램 변수에 바인드 열의 값을 C 변수에 검색하기 위해 SQLFetch를 사용

SQL/CLI를 사용한 C 프로그램(1)

SQL/CLI를 사용한 C 프로그램(2)

SQL/CLI를 사용한 Java 프로그램(1)

SQL/CLI를 사용한 Java 프로그램(2)

오라클에서 Java 예제 프로그램 Driver Loading & Connection

오라클에서 Java 예제 프로그램 SQL문 호출 및 결과 반환

오라클에서 Java 예제 프로그램 SQL문 실행 예 :

오라클에서 Java 예제 프로그램 INSERT문 실행 예 :

11.4 새로운 DB 프로그래밍 언어 – 저장 프로시저와 SQL/PSM Oracle의 PL/SQL, MS SQLServer의 Transact-SQL 등 데이터베이스를 활용하는 프로시저나 함수를 작성하여 DBMS내에 미리 저장하는데 사용 응용 프로그램에서 저장 프로시저를 호출하므로 내장형 방식에 비해 훨씬 효율적인 방법임

데이터베이스 저장 프로시저 프로시저/함수(모듈들)을 데이터베이스 서버에 저장하고 실행하는 것이 유용 장점들: 프로시저가 여러 응용에서 필요로 할 경우, 여러 응용에서 이를 호출할 수 있음(중복된 노력 감소) 서버에서의 실행은 통신 비용을 감소 뷰의 모델링 능력을 높일 수 있음

저장 프로시저의 구성 저장 프로시저 저장 함수 프로시저나 함수 호출 CREATE PROCEDURE procedure-name (params) local-declarations procedure-body; 저장 함수 CREATE FUNCTION fun-name (params) RETRUNS return-type function-body; 프로시저나 함수 호출 CALL procedure-name/fun-name (arguments);

SQL 지속적 저장 모듈 SQL/PSM: SQL + 저장 프로시저/함수 + 추가적인 프로그래밍 구조 포함 예., 조건(분기) 문장과 반복 로프 문장 SQL의 능력을 높일 수 있음

SQL/PSM: 예제 CREATE FUNCTION DEPT_SIZE (IN deptno INTEGER) RETURNS VARCHAR[7] DECLARE TOT_EMPS INTEGER; SELECT COUNT (*) INTO TOT_EMPS FROM SELECT EMPLOYEE WHERE DNO = deptno; IF TOT_EMPS > 100 THEN RETURN “HUGE” ELSEIF TOT_EMPS > 50 THEN RETURN “LARGE” ELSEIF TOT_EMPS > 30 THEN RETURN “MEDIUM” ELSE RETURN “SMALL” ENDIF;

오라클의 저장 프로시저 예제

오라클의 저장 함수 예제

SQL Server 저장 프로시저 예제 CREATE PROCEDURE usp_return @userName nvarchar(10) AS DECLARE @userGrade NVARCHAR(5); SELECT @userGrade = grade FROM userTbl WHERE name = @userName; IF (@userGrade <> ‘ ‘) RETURN 0; ELSE RETURN -1; DECLARE @retVal INT; EXEC @retVal = usp_return ‘안정환’; SELECT @retVal;

SQLServer 저장 함수 예제 --함수 수행 SELECT * FROM simplereports() CREATE FUNCTION SimpleReports() RETURNS @retInvoice TABLE (OrderID int primary key, CustomerID varchar(5), OrderDate smalldatetime, RequiredDate smalldatetime, ShipAddress varchar(60), ShipCity varchar(15), OrderTotal decimal(15,0)) AS BEGIN INSERT @retInvoice SELECT o.OrderID, o.CustomerID, OrderDate, RequiredDate, ShipAddress, ShipCity, SUM(UnitPrice*Quantity) OrderTotal FROM Orders o JOIN [Order Details] od ON o.OrderID = od.OrderID GROUP BY o.OrderID, o.CustomerID, OrderDate, RequiredDate, ShipAddress, ShipCity Order BY o.OrderID RETURN END --함수 수행 SELECT * FROM simplereports()