8강 - INDEX / 시퀀스 / PLSQL - 무명프로시져,프로시져,함수

- USER : KIM

 

1. INDEX 만들기

a. CREATE INDEX INDEX명

  ON 테이블명(컬럼명);

 

              ** SELECT *FROM USER_INDEXES; = INDEX 확인

              ** SELECT *FROM USER_IND_COLUMNS;  = 어떤 컬럼에 INDEX를 주었나 확인

 

 

2. PK와 INDEX

a. PK를 만들면 내부적으로는 PK 와 INDEX (NON-클러스터드 형식)가 자동으로

   만들어진다.

b  PRIMARY KEY가 지워지면 PRIMARY KEY때문에 자동으로 만들어 졌던 INDEX도
              자동으로 지워진다

 

 

3. SEQUENCE(일렬변호)

a. 시작/끝 값, 증가수를 사용자가 정하기

b. 실제 일렬번호의 역할은 못함(ex,’2’가 지워지면 ‘2’의 자리가 비어있다)

c. CREATE SEQUENCE 시퀀스명

      START WITH 10       //시작값

      INCREMENT BY 1   //증가값

      MAXVALUE 100       //최댓값

      NOCYCLE                

      NOCACHE;

 

              ** SELECT *FROM USER_SEQUENCES; = SEQUENCE의 정보 확인

 

 

4. SEQUENCE 활용

a.SEQUENCE명.NEXTVAL

  = SEQUENCE의 다음번호

 

b.SEQUENCE명.CURRVAL

  = SEQUENCE의 현재번호

 

c.INSERT INTO 테이블명 VALUES (SEQUENCE명.NEXTVAL,’컬럼값’,..);

  = 위의 것을 활용하여 일렬번호 넣기

 

** 한번 호출하면 끝이기 때문에 대부분 이걸로 쓰기 너무 민감해서 대부분 MAX값을                         

   불러와 +1한다.

5. SEQUENCE 수정

= ALTER SEQUENCE 시퀀스명

옵션명 변경값;

 

6. SEQUENCE 삭제

= DROP SEQUENCE 시퀀스명;

 

 

 

 

- USER : LEE

 

PL/SQL

1. PL/SQL개념

a. SQL을 절차적 언어로 만든 것

b. 네트워크 트래픽이 줄어든다

 

 

2. PL/SQL  종류

a. 무명 프로시져

   = DECLARE

   = 저장안되서 SCRIPT로 만들어야함

   = 실행방법 : 컴파일하면 바로 실행

   = SELECT 컬럼 INTO 변수명 FROM 테이블;

   = 사용자에게 입력받을때 -> Accept

b. 프로시져

   = PROCEDURE

   = DB에저장(나중에 불러올 수 있음)

   = 매개변수 있음 - IS 위에는 매개변수

                              - IS 밑에는 BEGIN안에서 사용하는 일반 변수

   = 실행방법 : EXECUTE 프로시져명();  

   = 사용자에게 입력받을때 -> IN

c. 함수

   = FUNCTION

   = DB에저장(나중에 불러올 수 있음)

   = 매개변수 / 반환값 있음

   = 실행방법 : SELECT 함수명() FROM 테이블;

 

 

3. PL/SQL문법

a. := -> 대입

b. :객체이름

c. DBMS_OUTPUT.PUT_LINE

d. CMD에서 실행하려면 'SET SERVEROUT ON'을 해주어야 함

 

4. 무명 프로시져1 - TYPE IS RECORD

DECLARE

TYPE 변수이름 IS RECORD

(값1 TYPE, 값2 TYPE,...);

객체이름 변수이름;

BEGIN

SELECT 컬럼명1,컬럼렴2.. INTO 객체이름 FROM 테이블명 WHERE 조건식;

DBMS_OUTPUT.PUT_LINE(객체이름.값들);

END;

 

a. RECORD형식 = 배열과 비슷

b. 자바에서의 - CLASS : 변수이름

                      - 객체: 객체이름

c. SELECT 컬럼명1,컬럼렴2.. INTO 객체이름 FROM 테이블명 WHERE 조건식;

   = SELECT된 결과의 값들을 객체에 집어넣어라!

5. 무명 프로시져2 - 테이블명%ROWTYPE

DECLARE

     변수명 테이블명%ROWTYPE;

     BEGIN

     ELECT * INTO 변수명 FROM 테이블명 WHERE 조건절;

            ….

 

= 테이블의 한줄의 행의 정보를 읽어옴

    => SELECT *FROM 과 짝궁

6. 무명 프로시져3 - TYPE IS TABLE OF INDEX BY

DECLARE

TYPE 변수이름1 IS TABLE OF 변수타입

INDEX BY BINARY_INTEGER;

--인덱스(컬럼의 여러 값을 인덱스를 이용하여 접근하기 위해 사용)

 

객체이름1 변수이름1 ;

I BINARY_INTEGER :=0; --인덱스 초기화

 

BEGIN

 

--사원에서 SELECT되는 만큼 FOR문 돌기

FOR K IN (SELECT 컬럼1 FROM 테이블) LOOP

I := I+1;

 

객체이름1(I) := K.컬럼1;

END LOOP;

 

FOR J IN 1..I LOOP

DBMS_OUTPUT.PUT_LINE(객체이름1(J));

 

END LOOP;

END;

 

 

7. 무명프로세스 - Script저장 & Accept로 사용자에게 입력받기

--CMD에서 실행하기 위한 설정

SET SERVEROUTPUT ON

SET VERIFY OFF

--무명프로세스 SCRIPT로 만들기 => 저장 후 '@파일이름'으로 실행

--'ID'라는 변수에 입력한 값 저장

ACCEPT ID PROMPT '검색할 아이디를 입력하세요: ';

DECLARE

...

BEGIN

..

WHERE 컬럼값 = '&ID'; --위에서 ACCEPT해서 받은 값 -> &ID

..

END;

8. 프로시져

CREATE OR REPLACE PROCEDURE 프로시져명

(변수1 IN TYPE, 변수2 TYPE) -- IN은 생략가능

 

IS

--IS 위에는 매개변수

--IS 밑에는 BEGIN에서 사용하는 일반 변수

 

BEGIN

 

--매개변수 활용 SQL문

UPDATE 테이블 SET 컬럼1=변수1 WHERE 컬럼2=변수2;

COMMIT;

 

END;

 

--만든 프로시저 실행

EXECUTE 프로시저명(변수1,변수2);

9. 함수

CREATE OR REPLACE FUNCTION 함수명

(매개변수명 IN NUMBER) --매개변수

RETURN 리턴값타입

 

IS

 

리턴값명 리턴값타입; --리턴값

 

BEGIN

 

SELECT 컬럼 INTO 리턴값명 FROM 테이블명

WHERE '매개변수를 활용한 조건절';

 

RETURN 리턴값명;

 

END;

 

--함수 실행하기

SELECT 함수명(값) FROM 테이블명WHERE 조건절;

10. 프로시져 IN/OUT 따로

CREATE OR REPLACE PROCEDURE P_OUTEX

(V_SANO IN 사원.사원번호%TYPE,

V_SANAME OUT 사원.사원명%TYPE,

V_PAY OUT 사원.급여%TYPE,

V_COMM OUT 사원.커미션%TYPE)

IS

BEGIN

SELECT 사원명, 급여, 커미션 INTO V_SANAME,V_PAY,V_COMM

FROM 사원 WHERE 사원번호=V_SANO;

END;

--CMD에서 실행

EXECUTE P_OUTEX(2001,:NAME,:PAY,:COMM);

11. 프로시져 IN/OUT 한번에

CREATE OR REPLACE PROCEDURE 프로시져명

(변수명 IN OUT 변수타입)

IS

BEGIN

변수명:= 변수명을 이용한 식;

-- EX) V_TEL := SUBSTR(V_TEL,1,3)||'-'||SUBSTR(V_TEL,4);

END;

 

--CMD에서 실행

VARIABLE 새로운변수명 타입 --CMD안에서 새로운 변수를 정의해서 사용

EXEC :새로운변수명 := 값입력; --IN의 역할

EXEC 프로시져명(:새로운변수명) --프로시저 호출

PRINT 새로운변수명; --새로운 변수 출력