PL/SQL

1. PL/SQL (프로시져언어)

• 오라클 자체에 내장되어 있는 프로시져 언어
• 블록구조를 갖는 언어다. <END~BEGIN사이에 명령을 한꺼번에 서버에 제공해서 한꺼번에 수행하므로 그 속도가 빠르다.>
• SQL문을 지원하는 제어문,반복문등을 지원한다.
• PL/SQL은 그 자신의 컴파일 엔진을 가지고 있다.
• 목적:SQL문을 블록단위로 처리하므로 network상에서의 처리속도를 향샹시킨다.
• 지원하는 TOOL : ORACLE FORMS,DEVELOPER/2000,SQL*plus.

 

2. PL/SQL구조

DATABASE		ORACLE	START	CLIENT
		PL/SQL
		SQL EXECUTE

 

DECLARE 변수선언문; //모든변수는 선언되어야 한다. BEGIN 프로그램 END;

 

3. 변수

• 모든 변수는 선언되어야 사용할 수 있다.
• (형식) 변수형 DATATYPE [:=초기값];
• 변수 TYPE

숫 자 문 자 날 짜 논리형	NUMBER,INTEGER ,FLOAT CHAR,VARCHAR2 DATE BOOLEAN /TRUE와 FALSE를 갖는다
변수형의 예	A NUMBER ; A NUMBER(5); A NUMBER:=200; := (대입기호)
문자형의 예	B VARCHAR2(10); B VARCHAR2(10):=’KOREA’;
날짜형의 예	C DATE:=SYSDATE
논리형의 예	D BOOLEAN := TRUE;

4. 기타변수의 TYPE

• %TYPE -TABLE의 COL의 TYPE
  형식) 변수명 테이블명.COL명%TYPE [:=초기값]

예) A SAWON.SAPAY%TYPE;

sawon.sapay와 똑같은 type으로 a를 설정

B A%TYPE;

• %ROWTYPE -테이블의 레코드와 같은 구조의 구조체 변수를 선언
  형식) 변수명 테이블명%ROWTYPE;

a(row type)

sabun

saname

deptno

.......

예) SAWON %ROWTYPE;
SELECT ……필드명………INTO ……변수명………
SELECT * INTO A FROM SAWON

ROW TYPE의 변수
***참조시 a.sanam으로 참조

 

5. 사용자 정의 Record Type
- 사용자가 임의로 정의하는 구조체 타입

• 형식) Type 새타입명 Is Record(변수명Datatype[:=초기값], ..... );
  select * into 변수

예)Type myrec is record(name varchar2(10), pay sawon.sapay%type, job varchar2(10);
//지금 변수가 잡힌 것은 하니고 구조만 잡힌것이다.(사용자 정의 타입)
a varchar2(10); // 변수지정
b cust_type;
b.job : = ‘과장’
a:= b.name;
select saname,sapay,sajob into a,b,c

 

제어문과 반복문

1. 제어문

IF 조건 Then 문장; else 문장 ; end if ;

IF 조건 Then ~ end if ;

IF 조건 Then 문장; elseif 조건 then 문장 ; else 문장 ; end if ;

2.분기문

: goto 문장; : : <<문장>>

3. 반복문

Loop //무한루프형성 문장; end Loop;

while 조건 Loop //조건이 참인 동안에 반복하고 거짓이면 빠져나간다. 문장; end loop;

For 변수 IN [reverse] 최소값..최대값 loop 문장; end loop;

4. 기타

Exit; => 반복 loop를 탈출한다.
exit when 조건 => 조건이 만족할 때 반복 loop를 탈출한다.

 

커서를 이용한 프로그램

1. 커서란?

커서는 나중에 액세스하기 위한 특정문자를 수용할 수 있는 메모리 영역을 사용자가 명명하는 것이다.

2. 커서의 종류

암시적 커서 : Insert, delete , update 작업시 생성되는 커서를 말한다.
명시적 커서 : 사용자가 필요에 따라 정의하는 cursor

3. 커서의 사용순서

1) 커서의 정의.

형식) cursor 커서명 Is select 문장; 커서를 담을수 있는 메모리를 확보한다 예) cursor a_cur is select * from sawon; //사원테이블의 모든 정보를 변수에 넣는 것과 같은 의미 단 변수는 하나의 레코드만 저장되는 반면에 커서는 모든 정보가 들어간다.

2) 커서를 Open한다.

형식) open cursor명; - Create된 커서를 open 한다. Open된 커서의 작업포인트는 첫번째 레코드에 위치한다. 예) Open a_cur

3) 커서를 Fetch한다.

형식) Fetch 커서명 into 변수; - Open 된 커서에서 Record를 하나씩 읽어낸다. - Fetch시 커서의 작업포인터는 다을으로 이동한다. - 커서는 순차적으로만 fetch 할수 있으며 fetch시 작업포인터는 다음으로 자동 이동한다. - 커서의 필드 개수와 변수의 개수와 datatype이 일치해야 한다.

4) 커서를 닫는다.

형식) close 커서명;

4. 커서의 속성변수

%ISOpen : 커서가 오픈상태인가를 체크 Open 이면true
%Found : Fetch된 레코드가 있는가 체크, 있으면 True
%Notfound : Fetch된 레코드가 없는가 체크, 없으면 True
%RowCount : Fetch된 행의 수를 return
(사용예) if a_cur%isopen then ~

 

커서의 for loop

• 커서에 있는 행을 불러오는 반복문이다.
• 반복문이 시작할 때 커서가 open 된다.
• 반복할때마다 커서는 한 행이 fetch 된다.
• 모두 fetch 되면 반복문은 중지하고 커서는 close된다

형식) for 레코드변수 IN 커서명 LOOP
……
END LOOP;

//for문을 쓰면 open, fetch, close 가 자동으로 된다.
//즉 간단히 된다.
//커서의 구조와 같은 변수로 된다.

예)
FOR A IN MYCUR LOOP
INSERT INTO IMSI VALUES(A.ENAME,A.SAL);
END LOOP;

 

Procedure/Function만들기

1.프로시져
:매개변수를 이용해서 값을 주고 받는다.

형식) CREATE [OR REPLACE] PROCEDURE 프로시져명(매개변수 모델 TYPE,……)
IS
변수선언 ;
BEGIN
내용;
:
END;
/
　

모델의 종류)
IN :입력매개변수
OUT :출력매개변수
IN OUT :입출력 매개변수
　
실행예)
SQL> VARIABLE GA NUMBER
SQL> EXECUTE GESAN(50,60,:GA)
SQL> PRINT GA

2.함수
:리턴값을 갖는 함수로서 리턴 결과는 1개이다.

형식)

CREATE [OR REPLACE] FUNCTION 함수명(매개변수 IN TYPE,……) RETURN DATATYPE IS 변수선언 ; BEGIN 함수내용; : RETURN 값; END; /

모델 TYPE IN:입력용 OUT:출력용 INOUT:입출력용

삭제 :Drop Function 함수명 (한번 컴파일하면 계속사용가능하다.)

실행예)
SQL> VARIABLE GA NUMBER
SQL> EXECUTE :GA := GESAN(50,60)
SQL> PRINT GA