VIEW
- 기존의 테이블은 그대로 둔 채 필요한 컬럼 및 새로운 컬럼을 만든 가상 테이블
- 실제데이터가 저장되는 것은 아니지만 view를 통해 데이터 관리 가능
- 일반 테이블은 메모리에 저장되지만 VIEW 테이블은 (오라클 객체로)SQL에만 저장
- 특징
- 독립성: 다른 곳에서 변경하지 못하도록
- 편리성: 긴 쿼리문을 짧게
- 보안성: 짧게 만들기 때문에 기존의 쿼리는 보이지 않음
- 코드
CREATE VIEW PLAYER_AGE AS (SELECT ROUND((SYSDATE-BIRTH_DATE)/365) AGE, P.* FROM PLAYER);-
버리기
DROP -
검색 SELECT*FROM PLAYER_AGE WHERE AGE>30;
-
수정하기
CREATE OR REPLACE VIEW view_name AS
SELECT ----
FROM -----
WHERE -----; -
외부 유저의 테이블
유저명.테이블명CREATE VIEW : 권한이 필요 ->관리자 계정으로 들어가서 권한을 주어야함 SQL> grant create view to C##IDEV; 실행 후 재연결 혹은 재접속 CREATE VIEW V_CUSTOMSALE AS SELECT TB.CUSTOMID , TB.PCODE ,SUM(TP.PRICE*TB.QUANTITY) MONEY, RANK() OVER (PARTITION BY TB.CUSTOMID ORDER BY SUM(TP.PRICE*TB.QUANTITY)DESC) "RANK" FROM TBL_BUY TB, TBL_PRODUCT TP WHERE TB.PCODE = TP.PCODE GROUP BY TB.CUSTOMID, TB.PCODE ; -일반적인 select처럼 조회형식 모두 사용 가능 SELECT vc.*, TP.PNAME FROM V_CUSTOMSALE vc ,TBL_PRODUCT tp WHERE vc.PCODE=tp.PCODE AND "RANK" =1;
-
- WITH는 한번만 사용
- SQL View의 이점
- 복잡한 쿼리를 하나의 간단한 이름으로 저장하여 접근하기 쉽다
- 커다란 데이터베이스에서 여러가지 데이터가 공존시 사용자가 특정열이나 행에 대한 접근가능
- 뷰는 쿼리에 사용된 필요한 데이터만 제공
- 동일한 쿼리를 여러번 사용시, 뷰를 사용하면 여러번 비슷한, 혹은 같은 코드를 작성을 방지
- 때문에 정기적으로 뽑아내야하는 차트나 보고서가 있다면 복잡한 쿼리를 뷰로 저장해두고 사용가능
- 자바에서도 가능 O
프로시저, IF
- Procedure(절차,순서) Language , 기존의 단순한 SQL이 확장된 언어(SQL로 만드는 프로그램)
- 변수,제어문(if,반복문)을 사용하여 프로그래밍언어와 같이 sql 실행의 흐름을 제어
- 프로시저 선언 및 실행
- create or replace ->만들어주고 혹시 있으면 대체
- %TYPE ->TYPE 만 복사
- OUT 밖에 결과물로 줄거야
- IN 밖에서 값을 받아와서 쓸거야
- BEGIN 안 SELECT문들은 각각이지만 해당 결과물을 사용하기에 연결되어있는 것처럼 보임
- 프로시저 내부에는 주로 트랜잭션을 구성하는 DML 명령문들을 작성, 중복 DML 가능
- 저장 프로시저에서 선언할때마다 ; 붙여줘야함(익명에서는 X) - 프로시저 오류 수정은 직접 해당 파일로 들어가서 DECLARATION에서 수정해야함
- 프로시저에서도 조인 O
- 프로시저에서 일반변수 대입문 기호 :=
- 여러개 행 조회될때는 각 행에 접근하기 위한 cursor 필요.
- 구성 : 선언부(DECLARE), 실행부(BEGIN), 예외처리부 (EXCEPTION)로 구성됨
- 유형Declare (선언부) Executable (실행부) -BEGIN ~ END Exception (예외 처리부)
- 프로시저 선언 및 실행
- Anonymous PL/SQL Block(익명블록) - 일회성 1) 익명 프로시저(일회용)
DECLARE -- 변수선언부
-- vname varchar2(40); -- 스칼라변수
-- vage number(3,0);
vname tbl_custom.name %TYPE; -- 참조변수(타입변수): 테이블명.컬럼명 %TYPE
vage tbl_custom.age %TYPE;
BEGIN --프로시저 시작(실행부)
SELECT name,age
INTO vname , vage -- 프로시저 구문: 검색결과를 프로시저 일반 변수 vname, vage 에 저장/ 홍길동의 이름,나이 저장
FROM "TBL_CUSTOM" tc
WHERE CUSTOM_ID ='hongGD'; -- 1개 행만 결과 조회되는 조건 (직접 지정)
DBMS_OUTPUT.PUT_LINE('고객이름 : ' || vname);
DBMS_OUTPUT.PUT_LINE('고객나이 : ' || vage);
EXCEPTION -- 예외(오류)처리
WHEN no_data_found THEN -- no_data_found : (지정된) 예외 이름 , 모든 예외: OTHERS
DBMS_OUTPUT.PUT_LINE('찾는 데이터가 없습니다.');
END; --끝
- Stored PL/SQL Block (저장 프로시저) -반복적
서버에 파싱해서 저장해 놓고 주기적으로 반복해서 사용할 경우 사용
서브프로그램 또는 프로그램 단위라고도 하며, 스키마를 구성하는 오브젝트로서 파싱된 후 오라클 서버 내부에 저장되거나 오라클 툴 안에 라이브러리 형태로 저장되어 있음
-저장 프로시저 생성
CREATE OR REPLACE PROCEDURE 프로시저 이름
( 매개변수이름 [IN 또는 OUT] 데이터 타입,.....) --IN: 입력매개변수(넣을것)
[IS 또는 AS] -- OUT:출력매개변수(리턴값 저장하는 변수)
프로시저 변수 선언
BEGIN
프로시저 작업 내용 SQL들....
EXCEPTION
WHEN 예외이름 THEN 처리 내용;
END ;
- 코드1 (익명에서 저장으로)
- 1번(익명)을 저장 프로시저로 변경했습니다.
-인자와 리턴을 주는 형식. 저장프로시저 정의하기
CREATE OR REPLACE PROCEDURE search_custom( -- 프로시저 이름 설정
c_id IN tbl_custom.CUSTOM_ID %TYPE -- 매개변수(인자) IN
)
IS --DECLARE없음... AS도 가능(AS=IS)
vname tbl_custom.name %TYPE; -- 지정된 테이블의 컬럼과 동일형식의 변수
vage tbl_custom.age %TYPE;
BEGIN
SELECT name,age
INTO vname , vage
FROM "TBL_CUSTOM" tc
WHERE CUSTOM_ID =c_id; -- 1개 행만 결과 조회되는 조건. 매개변수로 전달된 값으로 조건 실행
DBMS_OUTPUT.PUT_LINE('고객이름 : ' || vname);
DBMS_OUTPUT.PUT_LINE('고객나이 : ' || vage);
EXCEPTION -- 예외(오류)처리
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE(CHR(10)'찾는 데이터가 없습니다.');
END;
--프로시저 실행
BEGIN
SEARCH_CUSTOM('mina012');
END;
--vs에서는 됨 디비버는 안됨
EXEC SEARCH_CUSTOM('twice');
- 코드2 (출력변수 사용)
- 구매 수량이 최대인 고객의 이름, 나이 출력하는 프로시저 : max_custom
CREATE OR REPLACE PROCEDURE max_custom( -- 자바의 메소드 인자와 같은 개념/ NAME, AGE->매개변수
p_name OUT tbl_custom.NAME %TYPE, -- 출력(리턴) 변수
p_age OUT tbl_custom.AGE %TYPE
)
IS
maxval number(5);
cid tbl_custom.custom_id %TYPE;
BEGIN
SELECT max(quantity) --구매 수량 최대값 찾기
INTO maxval --조회결과를 일반 변수에 저장
FROM tbl_buy; --다음 문의 조건식으로 사용함
SELECT customid
INTO cid --조회결과를 일반 변수에 저장
FROM tbl_buy
WHERE quantity = maxval; --최대값을 가지고 고객의 아이디를 찾는구나
SELECT name,age
INTO p_name , p_age --출력 매개변수에 저장
FROM "TBL_CUSTOM" tc
WHERE CUSTOM_ID =cid; --고객의 아이디로 고객의 이름과 나이를 찾는구나
END;
-프로시저 실행: 출력 매개변수가 있는 프로시저
DECLARE
VNAME TBL_CUSTOM.NAME %TYPE;
VAGE TBL_CUSTOM.AGE %TYPE;
BEGIN
MAX_CUSTOM(VNAME,VAGE);
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('=고객이름: ' || VNAME);
DBMS_OUTPUT.PUT_LINE('=고객나이: ' || VAGE);
END;
- 출력코드 (DBMS_OUTPUT)
- DBMS_OUTPUT 는 콘솔에 출력하는 오라클 패키지의 하나이며 PUT_LINE 함수
- 오라클 문자열에서 줄바꿈은 아스키코드값 10 :CHR(10)
- ||는 문자열 연결 연산
DBMS_OUTPUT.PUT_LINE('고객이름 : ' || vname);
DBMS_OUTPUT.PUT_LINE('고객나이 : ' || vage);
DBMS_OUTPUT.PUT_LINE(CHR(10)'찾는 데이터가 없습니다.');
- 출력코드: dbms_output.put_line('결과 : ' || vresult )
- 테이블복사 : PK, FK는 복사 안됨IF문
JDBC
- JDBC
- 세계에서 제일 많이쓰는 인코딩 : UTF-8
- PreparedStatement pstm : 쿼리문을 담고, 전송하는 역할 (state보다 자주 쓰임)
ResultSet rs: 결과를 받음 - 닫을때는 연순서의 반대로...
- 단방향 암호화
전역변수 선언 private static final int key = 3; public String encrypt(String pw){ String en_pw = ""; for(int i = 0; i<pw.length(); i++){ en_pw += (char)(pw.charAt(i) + key) 혹은 (pw.charAt(i) * key) return en_pw;}- 이제 다른 메소드에서 매개변수는 String pw 그대로 받되,
pstm.setString(1, encrypt(pw)); <- encrypt 메소드에 담아서 사용..
사용자는 기억하는대로 검색하지만 실제 암호는 다른 값으로 저장되어 있음
=해당 메소드를 거쳐서(개발자가 설계한대로) pw를 치지않으면 로그인 불가
- 이제 다른 메소드에서 매개변수는 String pw 그대로 받되,
:) GITHUB: https://github.com/YJ2123412