기술노트 블로그

* Youtube Link - https://www.youtube.com/watch?v=x7NBTq-XNTo&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=34

웹  사이트 개발 환경 구축

Apache(웹 서버), Oracle(DB 툴), PHP(프로그래밍 언어) 세 소프트웨어를 통해서 웹 사이트 개발 환경을 구축해보겠다.

Oracle만 64bit를 사용하고 나머지는 32bit를 사용한다.

cafe.naver.com/thisisMySQL 사이트를 참조한다.

서버 충돌을 피하기 위한 비주얼 스튜디오에서 제공하는 IIS Express 제거

1. Apache HTTP Server 2.2 를 C:\Apache2.2\ 경로에 설치한다.

fcgid 설치

네이버 카페에서 fcgid를 설치하고 modules 폴더에 있는 mod_fcgid.pdb 파일과 mod_fcgid.so 파일을

Apache 폴더의 modules 폴더에 복사 붙여넣기 한다.

php 설치

php를 설치하고 이름이 길어서 php-5.4로 변경하고 c드라이브 바로 밑에 설치한다.

그리고 php 폴더 안의 php.ini-development 파일을 수정한다.

먼저 이름을 php.ini 파일로 -development를 지운다.

그러면 파일이 톱니바퀴가 있는 파일로 변한다.

date.timezone = Asia/Seoul
extension_dir = C:\php-5.4\ext

파일에 위 두줄의 코드를 추가한다.

그리고 extension=php_oci8_11g.dll ;Use with Oracle 11gR2 Instant Clinet 이렇게

앞의 ;을 지우고 주석을 지운다.

Oracle 11gR2와 php가 연동이 가능하다.

아파치 설정

Apache2.2\conf\httpd.conf 파일에 맨 위에 다음과 같이

LoadModule fcgid_module modules/mod_fcgid.so
FcgidInitialEnv PHPRC "c:/php-5.4"
AddHandler fcgid-script .php
FcgidWrapper "c:/php-5.4/php-cgi.exe" .php

이 코드들을 추가하여

아파치와 php 그리고 오라클 연동이 가능하게 한다.

그리고 Options Indexes FollowSymLinks에 한 칸 띄고 ExecCGI를 추가한다.

오라클 인스턴트 클라이언트 설치

instantclient_11_2.zip 폴더를 압축을 해제한다.

그리고 폴더명을 버전명을 지우고 첫 글자를 대문자로 바꾸어서 Instantclient로 바꾸어서

c드라이브 밑으로 옮긴다.

그리고 윈도우 키를 마우스 우클릭하고 터미널(관리자)를 실행한다.

그리고 다음과 같이 SETX PATH "%PATH%;C:\Instantclient" 를 입력하면

오라클 인스턴트 클라이언트의 경로가 적용된다.

그리고 재부팅을 해주어야 한다.

그리고 웹 서버가 잘 작동하는지 확인해보겠다.

http://localhost/oracle.php 웹 사이트에 접속을 하면

다음과 같은 화면이 열린다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=_X6GOarxaXg&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=33

11.3.3 재귀 트리거

실습용 테이블 준비

- (1) 간접 재귀 트리거용 테이블 A,B 생성 및 시퀀스 생성

- 트리거의 단계 숫자가 저장되는 테이블

이렇게 0으로 초기화 시키고, 계속해서 값을 올리는 테이블이다.

- 간접 재귀 트리거 테스트를 위한 트리거 A, B  생성

recuA 테이블에 입력을 하면 트리거가 작동을 해서

recuB 테이블에 입력되도록 한다.

그리고 recuB 테이블에 입력되면

recuA 테이블에 입력되도록 한다.

이렇게 서로를 간접적으로 재귀한다.

- 호출

그리고 recuA에 데이터를 입력하면

서로를 무한히 재귀하여 트리거가 작동하므로 

50번까지 작동을 하고

이후에는 에러가 나서 롤백된다.

- 50번 반복 횟수 제한 트리거

IF문을 주어서 반복횟수가 50회가 넘어가면

자동으로 트리거를 탈출하도록 만들었다.

값들이 정상적으로 들어간다.

11.3.4 순차번호의 자동 입력 트리거

- 테스트 테이블

- 시퀀스 (10000값부터 증가)

- 트리거 생성

BEFORE INSERT: 입력되기 이전에

INSERTING: 입력 중인가

:NEW.seqNum IS NULL : :NEW 테이블에 있는 seqNum이 null인가?

SELECT autoSEQ.NEXTVAL INTO :NEW.seqNum FROM DUAL; 을 통해서 다음 시퀀스 값을 :NEW 테이블에 시퀀스 값으로 넣어준다.

:NEW 테이블에 있는 값은 자동으로 새 테이블에 입력된다.

이렇게 순차적으로 시퀀스값이 자동으로 입력되는 트리거를 만들어보았다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=0KxIvQRPIIs&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=32

BEFORE 트리거

INSERT의 값이 테이블에 들어가기 전에 :NEW 임시 테이블에 들어가요

DELETE의 값이 테이블에서 지워지고, :OLD 테이블에 예전 값이 입력된다.

UPDATE의 값이 :NEW 테이블에 들어가고 테이블에 새값이 들어가고 :OLD 테이블에 예전 값이 들어간다.

BEFORE 트리거 UPDATE문 실습

데이터를 삽입하기 전에 :NEW 테이블에 데이터가 저장된다.

그러면 그림처럼 자동으로 새 테이블에 :NEW 테이블에 있는 데이터가 저장된다.

특정 테이블의 열 이름을 트리거로 조정할 수 있다.

이처럼 특정 이벤트가 발생을 할 때 방아쇠 같은 트리거가 발생을 해서

CREATE OF REPLACE TRIGGER trg_columnChange

AFTER UPDATE OF userName ON userTBL FOR EACH ROW

BEGIN RAISE_APPLICATOIN_ERROR(-20888, '이름은 변경이 안됩니다. !!!'); END;

userName 컬럼에 UPDATE문을 날리면 이후에 자동으로 트리거가 실행된다.

그러면 BEGIN END 안의 구문이 실행된다.

RAISE_APPLICATOIN_ERROR(-20888, '이름은 변경이 안됩니다. !!!'); 는

에러를 발생시키는 구문이다.

INSTEAD OF 트리거

배송 조회 뷰 트리거 실습

다음 뷰는 조인을 한 뷰이다.

그래서 INSERT가 어렵다.

하지만 트리거를 통해서 해결이 가능하다.

INSERT INTO userTBL 을 하고

INSERT INTO buyTBL 을 한다.

그러면 뷰 INSERT가 가능하다.

만든 뷰 삭제는 이렇게 가능하다.

뷰는 스키마의 트리거 탭에서 확인이 가능하다.

다중 트리거와 중첩 트리거

다중 트리거란 하나의 테이블에 동일한 트리거가 여러개 부착되어 있는 것

중첩 트리거란 트리거가 또 다른 트리거를 작동시키는 것을 말한다.

구매 테이블 물품 테이블 그리고 배송 테이블을 통해서

구매 테이블에 입력되면 물품 테이블에 개수 감소 시키고, 배송 테이블에 건수 입력 시키는 과정을

빵빵 연쇄적인 트리거로 작동을 시킨다.

그래서 INSERT를 시키면 자동으로 트리거로 인해서

연쇄적으로 연관된 테이블들에 입력되고 수정되고 삭제가 이루어진다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=9Hm7qaN3xhQ&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=31

트리거 개념

무결성을 위한 제약조건이다.

방아쇠 역할을 한다.

예를 들어 백업 테이블에 자동으로 입력되도록 하는 것이다.

주로 DML 문에서 사용을 한다.

트리거는 프로시저와 비슷해 보인다.

하지만 트리거는 어떠한 이벤트가 발생을 할 때 실행되는 것이다.

트리거를 생성하였다.

BEGIN END로 묶어서 쿼리를 실행하는 이유는

트리거에서 만든 BEGIN END 구문 안에 있는 DBMS_OUTPUT.PUT_LINE('트리거가 작동했습니다.'); 를 실행하기 위해서다.

실제 트리거가 작동하는 것은 BEGIN END와 상관 없다.

트리거 종류

AFTER 트리거는 작업 후에 작동한다. 뷰에서는 작동하지 않는다.

BEFORE 트리거는 작업 전에 작동한다. 뷰에서는 작동하지 않는다.

INSTEAD OF 트리거는 뷰에서만 작동된다. 주로 뷰에 업데이트가 가능하도록 할 때 사용한다.

실습

변경하기 위한 백업 테이블을 생성하였다.

AFTER 트리거 예제

변경 또는 삭제가 일어나면

OLD 테이블의 내용을 백업 테이블에 삽입한다.

트리거가 생성하는 임시 테이블

:NEW 테이블과 :OLD 테이블은 

트리거가 사용하는 임시 테이블이다.

감사합니다.

*Youtube Link - https://www.youtube.com/watch?v=5b2lOuOnHiI&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=30

커서

커서의 선언

패키지

우리는 DBMS_OUTPUT 등의 패키지를 사용해 왔다.

패키지 안에는 여러 프로시저들이 담겨있다.

그래서 SELECT * FROM ALL_PROCEDURES WHERE OBJECT_NAME = 'DBMS_OUTPUT';

을 하면 DBMS_OUTPUT 패키지 안에 있는 프로시저들을 확인할 수 있다.

ALL_OBJECTS 는 모든 오브젝트를 반환한다.

ALL_PROCEDURES 는 해당 이름의 오브젝트의 모든 프로시저들을 반환한다.

ALL_SOURCE는 해당 이름의 패키지의 모든 소스를 반환한다. 쿼리는 주로 암호화되어 있다.

이렇게 wrapped 되어 있다.

패키지 선언

다음과 같이 간단하게 패키지를 만들어보았다.

v_age NUMBER

v_bYear NUMBER 이렇게 변수를 두개를 선언해주고

프로시저와 함수를 선언만 해주고

패키지 생성 구문을 완료한다.

그리고 패키지 바디 부분에 다음과 같이 파라미터와 함께 

프로시저와 함수를 정의해준다.

프로시저에 패키지 내부에 있는 함수를 사용할 수 있다.

그리고 전달 받은 파라미터를 가공해서 변수에 저장한 다음

그 변수를 반환하여 준다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=TVscLvy4d_A&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=29

10.2 함수

실습

- 함수의 쓰임새

파라미터 타입과 반환할 타입을 정한다.

VAR retValue NUMBER; 변수를 선언한다.

EXECUTE :retValue = := getAgeFunc(1979); 함수를 실행하고 결괏값을 변수에 저장한다.

PRINT retValue; 그리고 출력한다.

그러나 보통 이렇게 사용은 잘 하지 않는다.

행으로 결괏값을 이렇게 출력할 수 있다.

기존의 테이블의 값을 SELECT해서 

getAgeFunc(birthYear) 생년월일을 조회해서 

현재의 나이값을 조회한다.

이름이면 blind 처리 (예: 이00)

전화번호이면 blind 처리한다. (010-1234-xxxx)

테이블 반환 함수

CREATE OR REPLACE TYPE tableType AS TABLE OF tableRowType; 명령어로 

* Youtube Link - https://www.youtube.com/watch?v=PB6i20Fwas8&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=28

예제 1) 입력 파라미터와 기본 변수를 설정한 프로시저

저장 프로시저를 생성할 때 스크립트 실행을 통해서 통째로 실행을 해야 한다.

여러 SQL문들을 실행하기 때문에 한꺼번에 실행을 해야 한다.

() 안에 파라미터를 선언하고

AS 다음에 변수를 선언한다.

선언한 파라미터와 변수를 프로시저 내에서 사용할 수 있다.

BEGIN END 안에 프로시저를 선언한다.

- 시퀀스 생성

CREATE SEQUENCE userSEQ; 명령어를 통해서 userSEQ라는 이름의 시퀀스를 생성할 수 있다.

userSEQ.NEXTVAL 함수를 통해서 userSEQ의 그 다음 변수를 호출할 수 있다.

- 출력 파라미터의 활용

그리고 익명 프로시저를 통해서

기존의 출력 파라미터를 사용한 프로시저를 호출해본다.

미리 출력 파라미터를 받을 변수를 선언해준다.

그리고 DMBS_OUTPUT.PUT_LINE(outData) 를 통해서 콘솔에 출력한다.

- 시퀀스를 출력하는 저장 프로시저

익명 프로시저를 실행하면 할 수록

다음 시퀀스를 콘솔에 출력한다.

- IF/ELSE 문

- 띠 출력 프로시저

- 구구단 프로시저

v_str = '' 초기화를 실행하고

2중 WHILE 반복문을 통해서

v_str에 계속 append를 시키는 구조이다.

- 회원 여부 확인 프로시저

DECLARE로 선언한 변수를

프로시저의 아웃풋 파라미터로 준다.

그리고 결과를 아웃풋 파라미터에 저장을 하고

출력을 해본다.

'은지원'은 회원이다.

- 오류가 발생하는 경우

오류 종류의 확인이 필요하다.

- 오류처리

오류를 처리하니

'그런 사람 없어요 ㅠㅠ'라는 내용이 콘솔에 출력된다.

- 입력 출력 파라미터 설정

pio_userName IN OUT NVARCAHR2 로 설정을 하면

이 변수를 입력으로 사용할 수도 있고'

출력으로도 사용할 수 있다.

변수를 재할당하는 것이다.

한 변수에 용도가 두개인 것이다.

- USER_OBJECTS

USER_OBJECTS 에서 만든 OBJECT의 목록을 확인할 수 있다.

OBJECT_TYPE = 'PROCEDURE' 라는 조건을 주면

프로시저만 볼 수 있다.

- USER_SOURCE

USER_SOURCE를 통해서 TEXT로 저장된 소스들을 각 행별로 볼 수 있다.

계층으로 이렇게 처리를 하면

각 행에 저장된 변수들을 실행할 수 있다.

- 사용자명 암호화 프로시저

사용자명 암호화

- 문자열 프로시저 

문자열 소스를 만들어서

EXECUTE IMMEDIATE DBMS_DDL.WRAP(DDL => v_source);

명령어를 통해서 WRAP 패키지 함수를 사용해서 DDL문으로 변경한다.

그리고 프로시저의 소스코드를 확인해보면 암호화가 되어있는 것을 볼 수 있다.

- 테이블을 파라미터로 넘겨서 실행하는 프로시저

이렇게 없는 테이블을 그대로 쿼리로 사용을 하면 오류가 발생한다.

테이블이 없기 때문에 먼저 오류가 발생하는 것이다.

문자열 테이블 이름을 전달해서

EXECUTE IMMEDIATE v_sql INTO v_count; 명령어로 처리를 해서

쿼리를 실행할 수 있다.

최종적으로 전달된 문자열 테이블 이름의 테이블을 조회하는 것을 볼 수 있다.

- 변수의 초기화 및 변수들 데이터 삽입

v_constant CONSTANT NCHAR(3) := '-->'; 명령어를 통해서 상수를 바로 초기화하였다.

또한 v_userData usertbl%ROWTYPE; 명령어를 사용해서 usertbl의 모든 열 타입을 v_userData에 넣을 수 있다. 그러면 v_userData.userName, v_userData.addr 등 다양하게 변수가 생길 수 있다.

SELECT userName, addr, height INTO v_userData.userName, v_userData.addr, v_userData.height FROM userTBL WHERE userID = pi_userid;

명령어를 통해서 해당 조회된 결과를 변수들에 삽입할 수 있다.

- IS RECORD

TYPE myRecordType IS RECORD (r_userName NVARCHAR2(20), r_addr NCHAR(2), r_height NUMBER(3));

v_record myRecordType;

이렇게 변수의 타입을 생성하여서 사용할 수 있다.

그러면 v_record.r_userName, v_record.r_addr, v_record.r_height 이렇게 사용할 수 있다.

- 타입의 Collection

Collection을 타입으로 만들 수 있다.

TYPE myVarrayType IS VARRAY(3) OF NUMBER(10); -- NUMBER 타입의 배열 3개를 지정한다.

TYPE myNestType IS TABLE OF NVARCHAR2(10); -- 갯수와 상관없이 배열을 만들 수 있다.

TYPE myAssocType IS TABLE OF NUMBER(5) INDEX BY STRING(10); -- INDEX로 STRING(10)을 줄 수 있다. 인덱스가 있는 자료구조의 타입을 만들 수 있다.

그리고 해당 타입의 변수를 초기화할 수 있다.

v_varray := myVarrayType(10, 20, 30); -- NUMBER(10) 배열 변수를 초기화 하였다.

v_nest := myNestType('이것이', '오라클', '학습 중'); -- NVARCHAR2(10) 배열 변수를 초기화하였다.

v_assoc('짜장') := 4500; -- 변수에 '짜장' : 4500 를 입력하였다.

v_assoc('피자') := 12000; -- 변수에 '피자' : 12000 를 입력하였다.

v_assoc('치킨') := 19000; -- 변수에 '치킨' : 19000 를 입력하였다.

10.1.2 저장 프로시저의 특징

프로시저를 사용하면 이미 쿼리가 서버에 저장되어 있기 때문에

간편한 코드로 쿼리를 계속 실행할 수 있다.

또한 긴 쿼리를 전송하는 것 보다 이미 프로시저를 생성해 놓았으므로 네트워크 전송량도 감소할 수 있어

시스템 성능이 향상된다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=ba_mUEJ4qtw&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=27

#의의

저장 프로시저와 함수

실무에서는 프로시저를 굉장히 많이 사용한다.

프로그래밍을 할 수 있기 때문이다.

또한, 시스템 성능에도 굉장히 도움이 된다.

#저장 프로시저

오라클 뿐만 아니라 모든 곳에서 사용된다.

CREATE OR REPLACE 프로시저

주로 프로시저를 생성할 때 이와 같은 형식으로 사용을 한다.

실습

- CREATE OR REPLACE PROCEDURE

프로시저 USERPROC이 컴파일 되었다.

- EXECUTE 저장_프로시저_이름

EXECUTE userProc();을 실행하였다.

- SET SERVEROUTPUT ON

SET 명령어를 통해서 SERVEROUTPUT 변수를 ON으로 변경하였다.

그리고 EXECUTE 명령어를 통해서 userProc() 프로시저를 실행하였다.

화면에 1980이 정상적으로 출력이 된다.

- 프로시저의 수정과 삭제

- 파라미터

- 파라미터 종류

- 익명 프로시저

프로시저명이 없는 프로시저다.

DECLARE 명령어를 통해서 실행한다.

- 컬렉션

배열과 비슷한 개념.

VARRAY, 중첩 테이블(NESTED TABLE), 연관  배열(ASSOCIATIVE ARRAY) 등 3가지가 있다.

- 여러 타입들의 변수 저장

또한 저장 프로시저 내의 예외 처리가 가능하다.

감사합니다.