2016년 9월 28일 수요일

[오라클학원,SQL학원,PLSQL학원추천◆탑크리에듀]#10. [PLSQL강좌]%ROWTYPE 속성

#10. [PLSQL강좌]%ROWTYPE 속성

%ROWTYPE 속성

n SELECT LIST에서 전체 칼럼을 선택해서 변수에서 받을 때(SELECT * ) 유용하게 이용된다.

n 어떤 테이블의 ROW(로우, 행, 레코드)와 타입을 같이 하라는 속성이다.

n 테이블의 칼럼에 대해 잘 모르는 경우, 칼럼의 속성들이 자주 변하는 경우에 유용하다.

SQL> edit ojc10

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE ojc10(p_empno IN NUMBER)

emp_record emp%ROWTYPE;

BEGIN

SELECT *

INTO emp_record

FROM emp

WHERE empno = p_empno ;

DBMS_OUTPUT.PUT_LINE(emp_record.empno || ',' || emp_record.ename || ',' || emp_record.sal || ',' || emp_record.deptno);

END;

SQL> @ojc10

프로시저가 생성되었습니다.

SQL> exec ojc10(7369)

7369,SMITH,4500,20

[오라클학원,SQL학원,PLSQL학원추천◆탑크리에듀]#9. [PLSQL강좌]%TYPE 속성

#9. [PLSQL강좌]%TYPE 속성

n 실무에서 테이블의 모든 칼럼에 대해 데이터 타입 및 길이를 정확히 알고 있을 수는 없고, 가끔 칼럼 사이즈는 변하기도 한다.이 경우 %TYPE 속성을 사용한다면 능동적으로 대처할 수 있다.

n 변수의 데이터 타입을 어떤 테이블의 어떤 칼럼과 같이 하라는 의미 또는 이전에 정의한 다른 변수와 타입을 같이하라는 의미를 지닌다.

DECLARE

v_ename emp.ename%TYPE;

v_sal emp.sal%TYPE;

tot_sal v_sal%TYPE;

[오라클학원,SQL학원,PLSQL학원추천◆탑크리에듀]#8. PLSQL복합데이터타입(Composite Data Type, Record Type)

#8. PLSQL복합데이터타입(Composite Data Type, Record Type)

Record Type

n 프로그래밍 언어의 구조체와 유사하다.

n 하나의 레코드 타입안에는 Scalar Data Type, 다른 Record Type, Table Type들을 가지고 있어야 한다.

n 먼저 TYPE을 정의 후 변수의 데이터 타입으로 할당하여 사용한다.

n 다음과 같이 Table Type을 정의할 때 Record Type을 사용할 수도 있다.

DECLARE

TYPE empRecordType IS RECORD (

empno SMALLINT,

ename VARCHAR2

);

TYPE empTableType IS TABLE OF empRecordType

INDEX BY BINARY_INTEGER;

SQL> edit ojc9

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE ojc9(p_empno IN NUMBER)

TYPE emp_record_type is record (

empno emp.empno%TYPE,

ename emp.ename%TYPE,

sal emp.sal%TYPE

);

emp_record emp_record_type;

BEGIN

SELECT empno, ename, sal

INTO emp_record.empno, emp_record.ename, emp_record.sal

FROM emp

WHERE empno = p_empno;

DBMS_OUTPUT.PUT_LINE(emp_record.empno || ' : ' || emp_record.ename || ' : ' || emp_record.sal);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('no data...');

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('to many rows...');

END ojc9;

-- 위에서 작성한 ojc9.sql 파일을 실행하여 ojc9 라는 프로시저를 생성한다.

-- 오류가 발생하면 show errors로 확인 후 디버깅 해야 한다.

SQL> @ojc9

프로시저가 생성되었습니다.

SQL> exec ojc9(7788)

7788 : SCOTT : 3000

-- EMP 테이블에 없는사원번호를 입력, SELECT되는 데이터가 한건도 없으므로 오류발생.

-- EXCEPTION절로 빠져 WHEN NO_DATA_FOUND 처리부가 실행된다.

-- NO_DATA_FOUND는 오라클서버에 미리 정의된 예외명이며, 오라클 서버 오류인 ORA-01403에러에 대해 부여된 예외 명칭이다.

SQL> exec ojc9(1234)

No data…

[오라클학원,SQL학원,PLSQL학원추천◆탑크리에듀]#7. PLSQL복합데이터타입(Composite Data Type, Table Type)

#7. PLSQL복합데이터타입(Composite Data Type, Table Type)

Table Type

n 1차원 배열과 같은 데이터 타입으로 크기가 동적으로 늘어난다.

n 색인을 위해 Binary_Integer 타입의 Key와 실제 값을 저장하는 Scalar Type의 두 구성요소가 있어야 한다.

n 먼저 TYPE을 정의 후 변수의 데이터 타입으로 할당하여 사용한다.

n table of 구 다음에 Scalar Type 및 Record Type과 같은 실제 저장되는 데이터의 타입을 기술한다.

SQL> edit ojc8

SET SERVEROUTPUT ON

DECLARE

TYPE emp_table_type is table of emp%rowtype

index by binary_integer;

emp_table emp_table_type;

begin

select * into emp_table(1) from emp where empno = 7369;

select * into emp_table(2) from emp where empno = 7788;

for i in emp_table.first..emp_table.last LOOP

dbms_output.put_line(emp_table(i).empno || ',' || emp_table(i).ename );

END LOOP;

END;

SQL> @ojc8

7369,SMITH

7788,SCOTT

[오라클학원,SQL학원,PLSQL학원추천◆탑크리에듀]#6. [PLSQL강좌]변수, 데이터 타입

[PLSQL강좌]변수, 데이터 타입

PL/SQL 변수, 데이터 타입

변수, 데이터타입

[형식]

변수이름 [Constant] 데이터타입 [NOT NULL] [:= | Default 수식]

n Pl/SQL BEGIN ~ END 안에서 사용할 변수들은 DECLARE 섹션에서 정의하며 변수명은 오라클 오브젝트명과 동일한 네이밍룰을 따른다.

n NOT NULL로 정의한 변수는 값이 반드시 값이 입력되야 하므로 기본값을 주거나 초기값을 할당해야 하는데 초기값은 할당 연산자(:=)를 사용하여 부여한다.

sal number(5) not null := 0;

sal number(5) not null default 0;

n 상수를 정의하기 위해서는 CONSTANT 키워드를 사용하고 반드시 초기값을 부여해야 한다.

v_max_sal CONSTANT NUMBER(9) := 999999999;

3.1.1 Scalar Data Type

한가지 타입의 값을 가지는 변수로써 Numeric, Character, Boolean, Datetime 계열이 있다.

[Numeric 계열]

PLS_INTEGER or BINARY_INTEGER	부호있는 4바이트 정수표시 -2,147,483,648 ~ 2,147,483,647, PLS_INTEGER가 저장공간을 적게 차지하고 BINARY_INTEGER보다 빠르다
NUMBER	고정/부동 소수점 숫자 표현

[Character 계열]

`CHAR`	32,767 바이트까지 표시하는 고정길이 문자열
`VARCHAR2`	32,767 바이트까지 표시하는 가변길이 문자열
`LONG`	32,767 바이트까지 표시하는 가변길이 문자열
`LONG RAW`	32,767 바이트까지 표시하는 가변길이 바이너리데이터, PL/SQL에서는 해석되지 않는다.
`ROWID`	물리적인 레코드를 나타내는 식별자

[Datetime 계열]

YEAR	-4712 to 9999 사이의 연도를 표시
MONTH	월
DAY	일
HOUR	시
MINUTE	분
SECOND	초
DATE	날짜 및 시각을 표시

[Boolean 계열]

BOOLEAN

논리값인 TRUE or FALSE를 표시

SQL> edit ojc5

-- CHAR TYPE의 비교예제

-- 작은쪽을 큰쪽과 같이 늘여 사이즈를 맞춘 후 비교

SET SERVEROUTPUT ON

DECLARE

name1 CHAR(5) := 'OJC'; -- 공백없음

name2 CHAR(10) := 'OJC '; -- 뒤부분에 공백있음

BEGIN

IF name1 = name2 THEN

DBMS_OUTPUT.PUT_LINE(name1 || ' = ' || name2);

ELSE

DBMS_OUTPUT.PUT_LINE(name2 || ' != ' || name1);

END IF;

END;

SQL> @ojc5

OJC = OJC

SQL> edit ojc6

/* VARCHAR2 TYPE의 비교예제

비교대상 한쪽이라도 VARCHAR2라면 CHAR와 달리 공백을

채우지 않고 비교(작은쪽을 늘이지않는다)

그러므로 아래 두 값은 같지않다.

SET SERVEROUTPUT ON

DECLARE

name1 VARCHAR2(5) := 'OJC'; -- 공백없음

name2 VARCHAR2(5) := 'OJC '; -- 뒤부분에 공백있음

BEGIN

IF name1 = name2 THEN

DBMS_OUTPUT.PUT_LINE(name1 || ' = ' || name2);

ELSE

DBMS_OUTPUT.PUT_LINE(name2 || ' != ' || name1);

END IF;

END;

SQL> @ojc6

OJC != OJC

SQL> edit ojc7

/* VARCHAR2, CHAR TYPE의 비교예제

비교대상 한쪽이라도 VARCHAR2라면 CHAR와 달리 공백을

채우지 않고 비교(작은쪽을 늘이지않는다)

그러므로 아래 두 값은 같지않다.

SET SERVEROUTPUT ON

DECLARE

name1 VARCHAR2(10) := 'OJC'; -- 공백으로 채우지 않는다.

name2 CHAR(10) := 'OJC'; -- 나머지 7자리는 공백으로 채움

BEGIN

IF name1 = name2 THEN

DBMS_OUTPUT.PUT_LINE(name1 || ' = ' || name2);

ELSE

DBMS_OUTPUT.PUT_LINE(name2 || ' != ' || name1);

END IF;

END;

SQL> @ojc7

OJC != OJC