동적 SQL이란
: 고정 된 SQL이 아닌 변경 가능한 쿼리를 말한다
: 사용 될 SQL문이 실행시에 결정된다
사용을 하는 이유
-컴파일 시 SQL문이 확정되지 않을 때
-PL/SQL 블록 상에서DDL문을 사용해야 할 때
NDS(NATIVE DTNAMIC SQL) : 원시 동적 SQL
-동적 SQL한 종류
-EXECUTE IMMEDIATE ~ INTO 문 사용
-형식:
EXECUTE IMMEDIATE SQL문자열 INTO 담은 변수1, 담을 변수2...;
USING SQL문자열에서 사용한 변수에 대응되는 바인드변수1, 바인드변수2,...;
SQL문자열은 SQL쿼리문을 담기때문에 자료형의 사이즈를 좀 넉넉하게 해준다.
바인드 변수 사용: 연결해주는 변수를 바인드라고 함
SQL뭐이문에 변수를 설정하여 해당 변수에 대응하는 값을 연결해주는 방식
여러 SQL 안의 값이 변경되어도 구문을 변경해줄 필요가 없다
프로시저 : 반환값이 없는 프로그램 특정 연산을 수행하기만 함
--테이블의 생신이나 다른 테이블에 데이터를 백업하거나 하는 경우 들 이용한다
저장 프로시저(STORED PROCEDURE)
-자주 사용되는 쿼리문을 모듈화시켜서 필요할 때마다 호출하여 사용하는 것
-프로시저 선언 형식 :
CREATE OR REPLACE PROCEDURE 프로시저명 [(P1[IN(디폴트)| OUT | INOUT] 데이터타입 := 값,...)]
IS[AS] -- IS 또는 AS 가능 : 별 차이 없다. 변수, 상수 등 선언
BEGIN 실행문; ....
[EXCEPTION 예외처리문 ; ]
END [프러시저명];
/
키포인트
--프로시저는 반환값이 없으므로 함수처럼 SELECT 절에는 사용할 수 없다
--호출문 형식 : EXEC(EXECUTE) 프로시저명(매개변수 1의값, 매개변수 2의값,...);
반환값이 없으면 (저장)프로시저 있으면 함수
RETURN; --실행문 완전 종료
함수(FUNCTION):
오라클에서 제공하고 있는 내장함수(=빌트인함수)처럼 매개변수를 받아 뭔가를 처리해 그 결과를 반환하는 데이터 베이스 객체이며, 사용자 정의 함수를 말함. 꼭 반환값이 있어야 한다
형식:
CREATE OR REPLACE FUNCTION 함수 이름(매개변수1, 매개변수2,...) RETURN 데이터타입;
IS[AS]변수, 상수 등 선언
BEGIN 실행부; --실행문, 출력문, 조건, 제어문 등등
RETURN 반환값;
[EXCEPTION 예외처리부] END [함수이름];
함수 호출 시 출력문은 익명으로 받으면 된다
ROWTYPE은 SELECT문으로 출력 불가능하므로, 해당 타입의 변수로 직접 값을 대입 받아야 한다
호출문 형식:
--함수제거
DROP FUNCTION 함수명;
TABLE TYPE 객체 생성
구체적인 예외명을 알 수 없을 때: OTHERS를 사용한다.
OTHER는 오라클 시스템에서 PL/SQL 코드 상에서 발생한 런타임 예외를 자동으로 잡아 준다.
--오라클에서 제공하는 빌트인 함수(언어 설계 과정에서 미리 만들어진 함수)
SQLCODE : 실행부에서 발생한 예외에 해당하는 코드를 반환한다.
예: BY ZERO에러:‘-1476’을 반환, 에러가 없으면 0을 반환
SQLERRM : 발생한 예외에 대한 오류 메시지를 반환한다.
매개변수로 예외코드 값을 받는데, 매개변수를 넘기지 않으면
디폴트로 SQLCODE가 반환한 예외코드 값과 연관된 예외 메시지를 반환한다.
DBMS_UTILITY: 좀더 세부적인 예외 정보제공.
예: DBMS_UTILITY. FORAMT_ERROR_BACKTRACE 함수: 몇 번째 줄에서 예외가 발생했는지 알려줌.
--형식 : CASE문과 유사
EXCEPTION WHEN 예외명1 THEN 예외처리 구문1
WHEN 예외명2 THEN 예외처리 구문2
...
WHEN OTHERS(예외명) THEN 예외처리 구문n;
*/
===========================
미리 정의된 예외 종류들
ACCESS_INTO_NULL : (ORA-06530) 객체가 초기화되지 않은 상태에서 사용시
CASE_NOT_FOUND : (ORA-06592) CASE문 사용시 구문 오류
CURSOR_ALREADY_OPEN : (ORA-06511) 커서가 OPEN상태에서 재OPEN 시도 시
INVALID_CURSOR : (ORA-01001) 존재하지 않는 커서를 참조
INVALID_NUMBER : (ORA-01722) 문자를 숫자로 변환할 때 실패할 경우
NO_DATA_FOUND : (ORA-01403) SELECT INTO 시 데이터가 한 건도 없을 경우
TOO_MANY_ROWS : (ORA-01422) SELECT INTO절 시 결과가 2개 이상일 때
VALUE_ERROR : (ORA-06502) 수치 또는 값 오류
ZERO_DIVIDE : (ORA-01476) 젯수가 0일때 오류
트리거(TRIGGER):
단순 DML트리거
DML이벤트에 의해 수행되는 트리거를 말함
테이블과 뷰에 종속되어있으며 대상 객체를 삭제하면 함께 삭제된다
단일 타이밍 포인드(TIMING POING)를 지정한다
DML이벤트 : INSERT, UPSATE, DELETE
형식:
CREATE [OR REPLACE] TRIGGER [스키마.] 트리거명
{BEFORE 또는 AFTER} DML이벤트 [OR DML이벤트 OR DML이벤트] ON [스키마명] {테이블명 또는 뷰명}
[REFERENCING_CLAUSE] --상관명 별칭 사용
[FOR EACH ROW] -- 행 단위로 수행하는 경우 기술(없으면 문장단위)
[FOLLOWS] -- 트리거 순서 정하기
[WHEN 조건][DECLARE 선언부]
BEGIN 실행문, 출력문, 조건, 제어문 등등;
[EXCEPTION 예외처리부]
END [트리거명];
/
-두 개의 트리거가 동일한 컬럼에 대한 이벤트에 반응하는 경우
-FOLLOWS를 통해 이벤트 순서를 정할 수 있다
-서로 다른 컬럼인 겨우 FOLLOWS는 적용되지 않는다
트리거 비활성화 하기
ALTER TRIGGER TRG_T1 DISABLE;
테이블에 종속되 전체 트리거 활성화 /비활성화 하기
ALTER TABLE [스키마명.]테이블명 {ENABLE 또는 DISABLE} ALL TRIGGER;
트리거 삭제하기
DROP TRIGGER 트리거명;
행단위 DML 트리거 [FOR EACH ROW] 사용
:OLD, :NEW 상관명을 이용하며 변경 전, 후 값을 참조할 수 있다.
별칭 줄 때 REFERENCING 사용
PRAGMA AUTONOMOUS_TRANSACTION; --커밋이나 롤백이 가능하다
DML---------:OLD---------:NEW
INSERT----- NULL---------추가 값
DELETE----삭제전 값------NULL
UPDATE --갱신전 값 -----갱신후 값
데이터베이스의 모델링 = 데이터베이스의 설계
: 사용자의 요구를 분석하여 사용자의 요구를 컴퓨터에 저장할 수 있는
데이터베이스의 구조에 맞는 형식의 데이터로 변환하여
특정 DBMS로 데이터베이스를 구현하여 일반 사용자들이 사용할 수 있도록 하는 것을 말한다.
프로그램을 설계하는데 가장 기본적인 틀이나 방향을 제시해준다.
데이터를 보다 정확하고 무결하게 처리할 수 있는 기반을 제공해준다.
데이터베이스 모델링 단계
요구사항분석 - 개념적설계 - 논리적설계 - 물리적설계 - 구현
=> 요구사항 정의서
대학교 학생들의 수강신청 관리 시스템을 만들고자 한다.
학생은 학번(기본키), 이름, 연락처, 이메일, 주소 등이 있다.
-연락처는 핸드폰, 유선번호 , 주소- 우편번호, 기본주소, 상세주소
-학부코드와, 학과코드에는 값이 반드시 있어야 한다.
대학교의 학과는 학부코드(기본키-복합), 학과코드(기본키-복합), 학부명, 학과명로 나뉘어져 있다.
-학부/학과
: 인문사회학부(학부1000) - (학과)국어국문학과(1001), 중어중문학과(1002)
: 자연과학학부(학부3000) - (학과)수리과학과(3001), 생명과학과(3002)
과목은 학과에 따라 선택여부(필수/교양/선택), 과목번호(기본키), 과목명으로 나뉜다.
-선택여부는 값이 반드시 있어야 한다. (값은 필수, 교양, 선택 중 하나)
-선택여부가 필수인 경우는 해당 학과학생들이 무조건 수강해야하는 과목이다.(타학과 학생 신청 불가)
-선택여부가 교양인 경우는 해당 학부학생들 중 해당 과목을 신청한 학생들만이 수강하는 과목이다.(타학부 학생 신청 불가)
-선택여부가 선택인 경우는 해당 과목을 신청한 학생들만이 수강하는 과목이다.(학부,학과 제한 없음)
-인문학과 필수 과목명(과목번호)은 '삶과인문학(1001-01)', '문학입문(1001-02)'
-중어중문학과 필수 과목명은 '중국의 대중문학(1002-01)', '중국고전문학탐색(1002-02)'
-인문사회학부 교양 과목명은 '현대한국사회론(1000-01)', '동아시아의문화(1000-02)'
-수리과학과 필수과목명은 '선형대수학(3001-01)'
-생명과학과 필수과목명은 '유전학(3002-01)'
-자연과학학부 교양 과목명은 '대수적코딩이론(3000-01)', '심층신경망(3000-02)'
-선택 과목명은 '역사와철학(9000-01)', '스페인어(9000-02)', '기초수학(9000-03)'
수강신청에 들어갈 속성은 학번, 이름, 학부코드, 학부명, 학과코드, 학과명, 선택여부, 과목번호, 과목명, 신청일자이다. (트리거로 인서트 되기 전 학생의 학부코드와 과목의 학부코드 일치여부 체크 후 인서트 되도록)
(1) 엔티티 , 속성 추출
요구사항 정의서에 정리한 내용 중 명사로 표현할 수 있는 것을 추려내어
개체화할 것인지 속성화할 것인지 관계화할 것인지를 구분
(2) ERM(Entity-Relationship-Model)-ERD(Entity-Relationship-Diagram)
개체 : 직사각형으로 표현 (=릴레이션,테이블이 되야하는 곳)
속성 : 타원형으로 표현(테이블의 속성(=컬럼)이 되는 곳)
기본키속성 : 속성에서 밑줄(실선)으로 표현(테이블의 기본키가 되는 곳)
참조키속성 : 속성에서 밑줄(점선)으로 표현(테이블의 외래키가 되는 곳)
복합속성: 트리구조처럼 루트속성1개 아래에 하위 서브속성 여러개가 연결되어 있다.(차후 별도의 테이블로 분리할 지, 여러개의 속성으로 분해해서 사용할지, 한 개의 속성으로 합쳐 사용할지는 논리적단계에서 정해도 된다.)
다중값속성 : 이중겹타원형으로 표현(차후 여러 행으로 나누어 받을지, 하나의 속성으로 합쳐 사용할지는 논리적 단계에서 지정 가능)
유도속성: 저장된 속성에 의해 유추할 수 있는 속성을 의미.
(예시: 저장된 속성- 생년월일, 유도속성- 나이)
관계 : 마름모로 나타내며, 개체와 다른 개체사이의 관계가 될 수 있는 개체나 속성을 나타냄. 대부분 개체가 옴.
참고: https://mjn5027.tistory.com/43
ERD: http://wiki.hash.kr/index.php/%ED%8C%8C%EC%9D%BC:ERD%EC%98%88%EC%8B%9C.PNG
(3) 관계스키마(관계구조) 정의
기본키, 외래키(참조키) 정의
일대일, 일대다, 다대다 관계정의
속성 설정 및 관계를 명확히 한다.
다중값 속성, 복합속성, 기본키속성, 참조키 속성, 개체간의 상관관계
이 부분에서 정확한 ERD가 생성된다.
해당 속성의 제약조건도 명시
참고:
https://m.blog.naver.com/PostView.naverisHttpsRedirect=true&blogId=drv98&logNo=221656377986
구현
: 실제 테이블 구현
다른 방법
--이전버전 jdk 1.8(8)버전 이하 가능
C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib 폴더에서 ojdbc6.jar 복사
C:\Java\jdk1.8.0_261\jre\lib\ext 폴더 안에 붙여넣기
나중에 자바 프로젝트를 이클립스가 아닌 윈도우에서 실행할 경우는
C:\Java\jre\lib\ext 폴더 안에도 ojdbc6.jar를 넣어줘야 한다.
JDBC 프로그래밍 순서
//모두 예외처리 해줄 것
1. JDBD 드라이버 로딩 =>
Class.ForName("oracle.jdbc.driver.OracleDriver"); //사용할 수 있게 접근하는 과정
2. Connection 객체생성 및 연경 =>
String url = "jdbc:oracle:thin:@localhost:1521:xe";
url값 형식 => "jdbc:oracle:thin:@도메인주소나 ip주소값:포트번호:전역데이터베이스명(sid)";
String user = "lion";
user 형식 => 사용자계정
String pwd = "1234";
password형식 => 해당 계정의 비밀번호값
Connection conn = DriverManager.getConnection(url,user,pwd);//연결통로를 만든다
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@locahost:1521:xe", "lion","1234");//한번에 이런식으로 가능
3. sql문 담을 객체 생성 및 실행 =>
Statement stmt = conn.createStatemint(); //Statemen 일회성 객체 동적쿼리 사용 x 단순쿼리만 사용 가능
stmt.executeUpdate("update emp set sal = 1000 where empno = 7900");
//int resultrow = stmt.executeUpdate("update emp set sal = 1000 where empno = 7900");//처리할 줄 수를 반환한다
//select문은 executeQuery를 사용
//executeUpdate insert update delete 사용 가능
PreparedStatement pstmt = conn.prepareStatement("update emp set sal = ? where empno = ?");
//PreparedStatement : 동적쿼리 사용 가능하다
한 번만 컴파일됨 (캐시=임시저장공간을 사용한다)
보안에 적합
pstmt.setint(1, 1000); //자료형에 맞춰서 값을 대입한다 //자동 commit가 된다 (자동 커밋이 안되게 하는 방법도 있음)
pstmt.setint(2, 7900);
pstmt.executeUpdate();
//date자료형은 String 으로 맞춘다
4. sql문이 select문인 경우 쿼리문 실행 및 ResultSet객체에 담기 =>
ResultSet rs = stmt.executeQuery(sql쿼리문); //레코드 단위로 반환
ResultSet rs = pstmt.executeQuery();
//close처리를 해줘야 한다
5. 자원 반환(반납) : 열였던 순서 역순으로 닫기 =>
re.close();
pstmt.close();
conn.close();
V 주요 상수
** 기존 row수에서 새로 insert된 줄의 데이터는 감지하지 못한다.
*동시성제어 타입
*결과집합의 유형
V 주요 메서드
V 커서 관련 메서드
rs의 총 줄 수 : rs.last() -> rs.getRow()
v 인터넷: 지역네트워크를 통신으로 연결한 것
v 네트워크란: 여러대 의 컴퓨터들을 통신회선으로 연결한 것. 그물망처럼 연결된 통신망을 말함.
v 네트워크 종류:
v 프로토콜(protocol): 통신을 주고 받기 위한 통신 규칙. 컴퓨터와 컴퓨터도 서로 이해 할 수 있는 언어, 공용된 언어를 사용 해야 한다는 것
v ip(Internet protocol address) : 네트워크상에서 장치들이 서로 통신을 하기 위한 규칙에 필요한 서로를 식별하기 위한 고유 주소 또는 특수번호를 말함. (집주소처럼 컴퓨터 주소라고 보면 됨. 하나의 랜카드당 한 개의 ip가 할당되어있다.)
** 랜카드 : 네트워크를 사용하기 위한 어댑터 역할을 하는 장치
v 전송 제어 프로토콜(Transmission Control Protocol, TCP, 문화어: 전송조종규약) : 웹 브라우저들이 월드 와이드 웹(www-대부분 웹이라고 함)에서 서버에 연결할 때 사용되며, 이메일 전송이나 파일 전송에도 사용된다.
v 포트란(port) : 같은 ip에서 접근해야하는 프로그램이 여러개 일 경우 해당 프로그램에 대해 접근하기 위해 부여된 프로그램주소값이라고 생각하면 된다.
v 서버: 서비스를 제공하는 프로그램을 의미함.
v 클라이언트: 서비스를 요청하고, 받는 프로그램을 의미함.
v InetAddress 클래스
v 객체를 반환하는 메소드(키워드가 모두 static임)
v 메소드
v 서버: 서비스를 제공하는 프로그램을 의미함.
v 클라이언트: 서비스를 요청하고, 받는 프로그램을 의미함.
v 소캣(Socket): 프로그램이 네트워크에서 데이터를 통신할 수 있도록 연결해주는 연결부
v ServerSocket 클래스(서버측):
v Socket 클래스(클라이언트측/서버측):
v TCP/IP 송수신 과정
서버: 클라이언트의 요청을 받기 위한 준비를 한다.(ServerSocket)
ServerSocket echoServer = new ServerSocket(4000);
클라이언트: 서버에 접속 요청을 한다. (Socket)
Socket socket = new Socket("127.0.0.1", 4000);
서버: 클라이언트의 요청을 받아 들인다. (accept)
Socket socket = echoServer.accept();
클라이언트: 서버에 메시지를 보낸다. ( BufferedWriter )
서버: 클라이언트가 보낸 데이터를 출력 한다. (BufferedReader)
서버: 클라이언트에 메시지를 보낸다. ( BufferedWriter )
클라이언트: 서버가 보낸 메시지를 출력한다. ( BufferedReader )
서버, 클라이언트 모두 종료 : 종료 한다. ( socket.close() )