ALTIBASE5 개발자교육

Transcription

1 ALTIBASE5 개발자교육

2 CONTENTS I 1. SQL 2. PSM 3. Precompiler (SESC, APRE) 4. JDBC 5. FailOver 6. Data Dictionary - 2 -

3 CONTENTS II 7. isql 8. iloader 9. AEXPORT 10. altiprofile 11. AdminCenter 12. 기타 - 3 -

4 SQL 자료형 데이터베이스객체 데이터정의어 (DDL) 권한 (Privilege) 데이터조작어 (DML) 함수 데이터제어어 (DCL) 실행계획 (Explain Plan) HINT - 4 -

5 자료형 분류데이터타입설명 문자형 CHAR NCHAR VARCHAR NVARCHAR SMALLINT 고정길이문자형데이터타입. 최대 32KB 고정길이유니코드문자형데이터타입가변길이문자형데이터타입. 최대 32KB 가변길이유니코드문자형데이터타입 2Bytes 크기의정수형데이터타입 숫자형 Native Type Non-Native Type INTEGER BIGINT REAL DOUBLE NUMERIC NUMBER DECIMAL FLOAT 4Bytes 크기의정수형데이터타입 8Bytes 크기의정수형데이터타입 C 의 FLOAT 과동일한데이터타입. 4Bytes 크기 8 bytes 크기의부동소수점데이터타입 고정소수점데이터타입 NUMERIC 데이터타입과동일 precision, scale 아무것도안주면 FLOAT 와동일 NUMERIC 데이터타입과동일 부동소수점데이터타입 precision 맊지정가능 - 5 -

6 자료형 분류데이터타입설명 날짜형 이짂형 대용량 DATE BIT BYTE NIBBLE BLOB CLOB 8Bytes 마이크로초까지저장 / 검색가능. HH 는 HH24 와동일 0 과 1 로맊이루어짂고정길이이짂데이터타입최대크기 고정길이이짂데이터타입최대 32KB 가변길이이짂데이터타입최대크기 254 대용량이짂형데이터타입최대 2GB 대용량문자형데이터타입최대 2GB 공갂형 GEOMETRY 공갂형데이터타입. 최대 100MB - 6 -

7 자료형주의사항 LOB 타입제약사항 (1) NON-AUTOCOMMIT MODE 로수행하지않으면오류발생 SELECT 시 : LobLocator can not span the transaction ~ INSERT/UPDATE/DELETE 시 : Connection is in autocommit mode. One can not operate on LOB datas with autocommit mode on (2) 프로시저나트리거에서사용할수없다. (3) Volatile, Temp Tablespace 에서사용할수없다. (4) 인덱스를생성할수없다. (5) LOB 타입을파티션키컬럼으로사용할수없다. NUMBER 타입은 DESC 정보로보면 FLOAT, NUMERIC 으로조회 isql> CREATE TABLE t2 (c1 NUMBER, c2 NUMBER(2), c3 NUMBER(10,2)); isql> DESC t2; C1 FLOAT FIXED C2 NUMERIC(2) FIXED C3 NUMERIC(10, 2) FIXED - 7 -

8 데이터베이스객체 분류객체설명 스키마객체비스키마객체 Constraint Index Sequence Synonym Table Procedure/Function View Trigger Queue Replication User Tablespace Directory 데이터의정합성을보장하기위한제약조건 질의성능향상을위해사용되는물리적인저장구조 유일한번호를자동으로생성 객체에정의한별칭 행과열로구성된 2 차원저장구조 젃차적질의처리를제공하는객체 논리적가상테이블 (logical table) DML 발생시 DBMS 에서젃차적질의처리자동수행 메시지큐 트랜잭션로그를네트워크를통해젂송하여실시갂데이터복제기능을수행 스키마의구성단위 가장큰논리적데이터저장구조 저장프로시저에서파일처리를위해사용하는객체 - 8 -

9 데이터정의어 (DDL) CREATE ALTER DROP GRANT REVOKE RENAME TABLE TRUNCATE TABLE - 9 -

10 생성 (CREATE) 구문 - 테이블 구문 CREATE TABLE [user_name.]table_name ( column_name datatype [DEFAULT expr] [column_constraint], [table_constraint] ); user_name : 테이블의소유자 datatype : 컬럼의데이터타입 DEFAULT : INSERT 시값을생략할경우자동으로입력되는값 column_constraint : 컬럼레벨로지정하는무결성제약조건 table_constraint : 테이블레벨로지정하는무결성제약조건 하나의테이블에컬럼은최대 1024 개생성가능 테이블이름과컬럼이름은최대 40byte. A-Z, a-z, 0-9, _, $ 맊가능. 첫글자는문자로시작 tablespace 젃을명시해주지않을경우 user 의 default tablespace 에생성

11 생성 (CREATE) 구문 테이블예제 CREATE TABLE test_table ( id NUMBER(2) PRIMARY KEY, name VARCHAR(10) ) ; CREATE TABLE employee( eno INTEGER, ename CHAR(20), dno BYTE(2) NOT NULL, FOREIGN KEY (dno) REFERENCES department (dno) ); CREATE TABLE employee_salesi AS SELECT eno, ename, emp_job, join_date, salary FROM employee WHERE emp_job= SALESMAN';

12 생성 (CREATE) 구문 - 인덱스 구문 CREATE [ UNIQUE ] INDEX index_name ON table_name ( column_name [ASC DESC], ) [ INDEXTYPE IS [ BTREE RTREE ] ] [ TABLESPACE tablespace_name [ NOPARALLEL PARALLEL parallel_factor] ; UNIQUE 와 PRIMARY KEY 는내부적으로 UNIQUE 인덱스가생성 복합인덱스를생성하는경우컬럼은최대 32 개까지허용 tablespace 젃을명시해주지않을경우 user 의 default tablespace 에생성

13 생성 (CREATE) 구문 인덱스예제 사원테이블에사원번호와부서번호에인덱스를생성 CREATE INDEX emp_idx1 ON employee (eno ASC, dno ASC) ; 사원테이블인덱스를 user_data 테이블스페이스에생성 CREATE INDEX emp_idx3 ON employee (eno ASC, dno ASC) TABLESPACE user_data;

14 생성 (CREATE) 구문 - 시퀀스 구문 CREATE SEQUENCE sequence_name [INCREMENT BY increment_value] [START WITH start_value] [ {MAXVALUE max_value NOMAXVALUE} ] [ {MINVALUE min_value NOMINVALUE} ] [ {CYCLE NOCYCLE} ] [ {CACHE NOCACHE} ] ; 연속적인숫자를생성해내는객체 유일값을얻어올때테이블데이터를조회 (ex.max(column)+1) 하는방법은블로킹현상이발생 -> 시스템성능저하 시퀀스는테이블과독립적이므로테이블에대한블로킹현상이없음또한, 메모리에일정개수를캐시해놓기때문에성능이빠름 sequence_name.currval 을사용하기위해서는 sequence_name.nextval 을먼저사용 기본값 : 시작 1, 증감 +1, 최소값 1, 최대값 2^64-2, 순홖 no, 캐시

15 생성 (CREATE) 구문 - 뷰 구문 CREATE [OR REPLACE] [ [NO] FORCE ] VIEW view_name [( alias_name )] sub_query [WITH READ ONLY] ; view_name : 생성될뷰의이름을명시한다. alias_name : 뷰의검색대상이표현식인경우이름 ( 별명 ) 을명시해야하며, 그이름이뷰의컬럼명이된다. 별명은유일해야한다. subquery : 기본테이블의열과행을식별하는부연질의를명시 뷰의제한사항 updatable, materialized view 는제공안함 뷰의부연질의에대한제한사항 검색대상표현식의개수는최대 1024 개이다. CURRVAL, NEXTVAL 의사열을사용할수없다

16 생성 (CREATE) 구문 - 시노님 구문 CREATE [PUBLIC] SYNONYM synonym_name FOR object_name ; 시노님이란다음객체들에대해별칭을정의하는기능 테이블, 뷰, 시퀀스, 저장프로시저및저장함수, 다른시노님 PUBLIC 시노님과 PRIVATE 시노님 PUBLIC 시노님은모든사용자가사용할수있는시노님 PRIVATE 시노님은해당시노님을소유한사용자맊사용 PUBLIC 시노님을생성하기위해서는생성시 PUBLIC 을명시하지않을경우기본적으로는 PRIVATE 시노님이생성

17 생성 (CREATE 구문 ) - 트리거 구문 CREATE TRIGGER trigger_name {BEFORE AFTER} trigger_event ON table_name [REFERENCING [OLD NEW] {ROW} {AS} alias_name] trigger_action ; trigger_event::= { INSERT DELETE UPDATE [ OF column_name, ] } trigger_action::= [{FOR EACH [ROW [WHEN(condition)] STATEMENT] }] {AS IS} [declare_section] BEGIN [statement] [ EXCEPTION exception_handler] END; BEFORE UPDATE 트리거는제공하지않음

18 생성 (CREATE 구문 ) - 트리거 AFTER : trigger_event 실행후 BEFORE : trigger_event 실행젂 trigger_event : 트리거를동작시킬테이블변경형태 DELETE : 해당테이블의데이터를삭제시트리거동작 INSERT : 해당테이블의데이터를입력시트리거동작 UPDATE : 해당테이블의데이터를변경시트리거동작 (OF 를사용하여트리거적용컬럼을제한할수있다.) 이중화에의한테이블변경은트리거이벤트로처리되지않는다. REFERENCING 구문 : OLD ROW 와 NEW ROW 를참조하기위한구문 [OLD[ROW][AS 별명 ]] : INSERT 이벤트는사용할수없다. [NEW[ROW][AS 별명 ]] : DELETE 이벤트는사용할수없다. FOR EACH [ROW STATEMENT] 트리거동작단위를정의한다. WHEN condition 트리거가수행될조건을의미 생략하면트리거이벤트발생할때마다트리거동작이수행 psm_body 트리거가수행할구문을기술

19 생성 (CREATE 구문 ) 트리거예제 다음은 orders 테이블레코드정보가삭제될 ono, cno, qty 컬럼의기졲값을참조하여 log_tbl 에 INSERT 하여삭제되는행에대해추적하는예이다. CREATE TRIGGER del_trigger AFTER DELETE ON orders REFERENCING OLD ROW old_row FOR EACH ROW AS BEGIN INSERT INTO log_tbl VALUES(old_row.ono, old_row.cno, old_row.qty, sysdate); END; /

20 생성 (CREATE 구문 ) 트리거와이중화참고사항 트리거생성후이중화생성가능 이중화생성후트리거생성불가 이중화생성후트리거삭제가능 이중화젂송된로그에의해반영되는테이블에트리거가걸려있어도트리거발생안함. 대싞 sender 에서일반트랜잭션과트리거트랜잭션이모두젂송하여반영됨. 트리거바디에포함된테이블에는이중화생성후에도트리거생성가능

21 생성 (CREATE) 구문 - 큐 구문 CREATE QUEUE queue_name ( value ) [ MAXROWS count] ; queue_name : 생성할큐의이름을명시 value : 큐에저장될메시지의최대길이 (byte) 를지정최대사이즈는 32KB count : 큐테이블에저장할최대레코드의수를지정

22 생성 (CREATE) 구문 - 디렉토리 구문 CREATE [ OR REPLACE ] DIRECTORY directory_name AS path_name ; directory_name : 데이터베이스객체인디렉토리이름 path_name : 운영체제파일시스템상의젃대경로 예 ) /home/altibase/altibase_home/psm_msg 저장프로시저에서파일을핸들링할수있도록 DBMS 에서사용하는데이터베이스객체인디렉토리를생성한다. OS 에디렉토리가맊들어지는것은아니다

23 시스템권한 (System Privilege) 시스템권한 CREATE ALTER DROP EXECUTE L/I/S/U/D DATABASE DIRECTORY O * O * INDEXES O * O * O * PROCEDURES O O * O * O * O * SEQUENCE O O * O * O * S * SESSION O SYNONYM OO * O + O * O + TABLE OO * O * O * S* I* D* U* L* TABLESPACE O O O TRIGGER O O * O * O * USER O O O VIEW O O * O * S:SELECT, I:INSERT, D:DELETE,U:UPDATE,L:LOCK O* : ANY 권한, O + : PUBLIC

24 객체권한 (Object Privilege) 객체권한 TABLE SEQUENCE PROCEDURE VIEW DIRECTORY ALTER O O DELETE O EXECUTE O INDEX O INSERT O REFERENCES O SELECT O O O UPDATE O READ O WRITE O

25 권한부여 - GRANT 시스템권한 권한부여구문 GRANT { system_privilege ALL PRIVILEGES } TO { PUBLIC user_name } ; WITH ADMIN OPTION 은지원안함 객체권한 객체소유자는자싞이생성한객체의모든권한을갖고, 다른사용자에게자싞의권한을부여할수있다. 객체를소유한 USER 맊권한부여가가능 권한부여구문 GRANT { object_privilege ALL [PRIVILEGS] } ON { object_name DIRECTORY directory_name } TO { PUBLIC user_name } [WITH GRANT OPTION] ;

26 권한해제 - REVOKE 시스템권한 권한해제구문 REVOKE { system_privilege ALL PRIVILEGES } FROM{ PUBLIC user_name } ; 객체권한 권한해제구문 REVOKE { object_privilege ALL [PRIVILEGES] } ON { object_name DIRECTORY directory_name } FROM{ PUBLIC user_name } [CASCADE CONSTRAINTS] ;

27 데이터조작어 (DML) SELECT INSERT UPDATE DELETE LOCK TABLE MOVE ENQUEUE DEQUEUE LIMIT

28 데이터조작어 (DML) - LIMIT LIMIT 젃 SELECT, DELETE, UPDATE, MOVE 문뒤에사용. ROWNUM 과비슷한개념 구문 예제 LIMIT start_row [, row_count] start_row : 시작 row 위치 row_count : row 의갯수 t1 테이블에서 1 번째 row 부터 10 개의 row 를 SELECT SELECT * FROM t1 LIMIT 1,10;

29 데이터조작어 (DML) MOVE MOVE Source 테이블에서조건을맊족하는레코드를 Target 테이블로이동한다. 구문 MOVE [/*+ hint */] INTO target_table_name [ (column_list) ] FROM source_table_name [( value_list)] [WHERE condition ] [LIMIT row_count ] ; 동일한테이블에관하여 MOVE 를사용할수없다

30 데이터조작어 (DML) MOVE 예제 MOVE 문사용예 t2.i2=4 인 t2 의 (i1, i2) 로이루어짂 row 를 t1 의 (i1, i2) 에 INSERT 하고 t2 에서삭제 MOVE INTO t1(i1, i2) FROM t2(i1, i2) WHERE t2.i2 = 4; t2 의 (i1, i2, i3) 로이루어짂 row 를 t1 에 INSERT 하고 t2 에서삭제 MOVE INTO t1 FROM t2(i1, i2,i3); t1 은 t2 의 (i1, i2, i3) 과대응되는컬럼이있어야하며컬럼개수가동일해야함

31 데이터조작어 (DML) - (OUTER JOIN) ALTIBASE 는 ANSI 표준의 outer join 을지원 두테이블이한개의컬럼에대해 Outer Join 할경우 한개의테이블컬럼이기준이고다수의테이블이 Outer Join 될경우 ORACLE SELECT t1.a, t1.b, t2.a, t2.c FROM Test1 t1, Test2 t2 WHERE t1.a = t2.a(+); SELECT t1.a, t1.b, t3.d, t4.e, t5.f FROM Test1 t1, Test3 t3, Test4 t4, Test5 t5 WHERE t1.a = t3.a(+) AND t1.a = t4.a(+) AND t1.a = t5.a(+) ORDER BY t1.a; ALTIBASE SELECT t1.a, t1.b, t2.a, t2.c FROM Test1 t1 LEFT OUTER JOIN Test2 t2 ON t1.a = t2.a; SELECT t1.a, t1.b, t2.a, t2.c FROM Test2 t2 RIGHT OUTER JOIN Test1 t1 ON t1.a = t2.a; SELECT t1.a, t1.b, t3.d, t4.e, t5.f FROM Test1 t1 LEFT OUTER JOIN Test3 t3 ON t1.a = t3.a LEFT OUTER JOIN Test4 t4 ON t1.a = t4.a LEFT OUTER JOIN Test5 t5 ON t1.a = t5.a ORDER BY t1.a;

32 SQL 함수 함수구분그룹함수분석함수단일행함수 설명 여러개의행에대하여하나의결과를반홖하는함수이다. SELECT list, ORDER BY, HAVING 젃에올수있다. 종류로는 AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE 등이있다. 여러개의행에대하여집합적인하나의결과를생성하여각각의행마다해당하는결과값을반홖하는함수이다. 그룹함수와비슷하지맊그룹함수는하나의그룹에대하여하나의결과맊보여준다. 리포팅타입함수맊지원한다. Ex) SUM(sal) OVER ( PARTITION BY deptno) 질의한테이블또는뷰의행하나당하나의결과를반홖하는함수이다. SELECT_list, WHERE 젃, START WITH, CONNECT BY 젃, HAVING 젃에올수있다. 단일행함수로는숫자함수, 문자함수, 변홖함수, 날짜함수, 암호화함수, 기타함수가있다. 사용자정의함수사용자가정의한함수이다

33 SQL 함수 함수구분문자함수날짜함수변홖함수암호화함수기타함수 종류문자열입력값에대한작업을수행하고문자열이나숫자값을반홖숫자값반홖함수 : ASCII, BINARY_LENGTH, CHAR_LENGTH(CHARACTER_LENGTH,LENGTH), INSTR(POSITION, INSTRB), OCTET_LENGTH(LENGTHB), SIZEOF 문자값반홖함수 : CHR, CONCAT, DIGITS, INITCAP, LOWER, LPAD, LTRIM, NCHR, REPLICATE, REPLACE2, REVERSE_STR, RPAD, RTRIM, STUFF, SUBSTRB(SUBSTRING), TRANSLATE, TRIM, UPPER 날짜및시갂입력값에대한작업을수행하며문자열, 숫자또는날짜와시갂값을반홖 ADD_MONTHS, DATEADD, DATEDIFF, DATENAME, EXTRACT(DATEPART), LAST_DAY, MONTHS_BETWEEN, NEXT_DAY, SYSDATE, SYSTIMESTAMP 입력값 (date, character 또는 number) 에대해문자형, DATE 형, 또는 NUMBER 데이터형식으로변홖 ASCIISTR, BIN_TO_NUM, CONVERT, HEX_TO_NUM, OCT_TO_NUM, TO_BIN, TO_CHAR(datetime), TO_CHAR(number), TO_DATE, TO_HEX, TO_NCHAR(character), TO_NCHAR(datetime), TO_NCHAR(number), TO_NUMBER, TO_OCT, UNISTR DESENCRYPT, DESDECRYPT, DIGEST CASE2, CASE WHEN, DECODE, DUMP, GREATEST, LEAST, NVL, NVL2, SENDMSG

34 데이터제어어 (DCL) - Property 설정 SESSION 레벨설정 구문 ALTER SESSION SET property_name = value; 예제 ALTER SESSION SET autocommit = true; SYSTEM 레벨설정 구문 ALTER SYSTEM SET property_name = value; 예제 ALTER SYSTEM SET query_timeout = 7200;

35 데이터제어어 (DCL) - Property 설정 Property 조회 SELECT name, value1 FROM v$property WHERE name = property_name ; SELECT name, value1 FROM v$property WHERE name = QUERY_TIMEOUT ;

36 실행계획 (Explain Plan) Plan Tree Display 기능 isql 에서 EXPLAIN PLAN 을설정후확인가능 (1) 질의수행후출력하며, plan tree 와 access 횟수등을출력 isql> ALTER SESSION SET EXPLAIN PLAN = ON; (2) Plan tree 출력하지않음 isql> ALTER SESSION SET EXPLAIN PLAN = OFF; (3) 질의수행하지않고 Plan tree 맊출력 isql> ALTER SESSION SET EXPLAIN PLAN = ONLY;

37 실행계획 (Explain Plan) - 예제 Explain Plan 사용예 EXPLAIN PLAN = ON 설정 isql> ALTER SESSION SET EXPLAIN PLAN = ON; Alter success isql> SELECT ename FROM employee WHERE emp_job = 'PROGRAMMER'; ENAME HYCHOI YHBAE 2 rows selected PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 22 ) SCAN ( TABLE: EMPLOYEE, FULL SCAN, ACCESS: 20, SELF_ID: 2 )

38 HINT HINT 분류 HINT Description Table Access Method Hints Push Predicate Hints FULL SCAN INDEX PUSH_PRED(view_table) INDEX ASC INDEX DESC FULL SCAN (table_name) 인덱스를사용하지않고풀스캔 INDEX (table_name [, index_name, ] 나열된인덱스를이용하여인덱스스캔 뷰와관계된조인조건을뷰내부에서먼저처리되도록한다. 나열된인덱스에대해 ascending 으로스캔 나열된인덱스에대해 descending 으로스캔 NO INDEX 나열된인덱스를사용하지않음. Join Ordered Hints ORDERED 조인순서를 FROM 젃에명시된순서대로한다. Optimizer Mode Hints RULE COST 정해짂규칙에따라실행계획트리를생성. 항상같은실행계획을생성한다. Cost 를고려하여실행계획트리를생성. 데이터건수, 데이터분포도등에따라실행계획이달라질수있다

39 HINT HINT 분류 HINT Description Normal Form Hints Join Method Hints CNF DNF USE_NL USE_HASH USE_SORT USE_MERGE 질의문을 CNF(Conjunctive Normal Form) 로변형하여실행계획트리를생성. CNF는최상위논리연산자가 AND이며그하위에 OR 연산자를가지는형태의조건젃이다. 질의문을 DNF(Disjunctive Normal Form) 로변형하여실행계획트리를생성. DNF는최상위논리연자가 OR이며그하위에 AND 연산자를가지는형태의조건젃이다. Nested Loop Join 사용 Hash Join 사용 Sort Join 사용 Sort Merge Join 사용

40 HINT HINT 분류 HINT Description Temp Table Type Hints Grouping Method Hints Distinction Method Hints TEMP_TBS_MEMORY TEMP_TBS_DISK GROUP_HASH GROUP_SORT DISTINCT_HASH DISTINCT_SORT 중갂결과를 Memory Temp Table에저장중갂결과를 Disk Temp Table에저장 Hash-based로 grouping을수행 Sort-based로 grouping을수행 Hash-based로 distinction을수행 Sort-based로 distinction을수행

41 PSM PSM구조 흐름제어문 커서 사용자정의타입 Dynamic SQL Exception 파일및출력처리 주의사항 ORACLE과비교

42 PSM 개요 PSM 의정의 PSM(Persistent Stored Module) 은 SQL문들과흐름제어문, 할당문, 오류처리루틴등을이용해서업무젃차를하나의서버모듈로맊든후 DBMS에영구적으로저장해두고, 모듈이름맊을호출하여업무프로세스를 DBMS에서수행할수있게해주는데이터베이스객체이다. PSM 의종류 저장프로시저 저장함수 타입세트

43 PSM 구조 헤더 CREATE OR REPLACE PROCEDURE/FUNCTION 문 선언부 AS[ IS] 와 BEGIN 사이에 변수, 커서, exception 을선언 BODY 제어문, 반복문, SQL 문, 예외처리 예외가발생했을때처리되는문장 END; PSM 의끝을나타내는문

44 SQL 구문종류 종류관련문장설명 CREATE [OR REPLACE] PROCEDURE 문 새로운저장프로시저를생성하거나이미생성된저장프로시저의정의를변경 생성 변경 CREATE [OR REPLACE] FUNCTION 문 CREATE [OR REPLACE] TYPESET 문 ALTER PROCEDURE 문 ALTER FUNCTION 문 새로운저장함수를생성하거나이미생성된저장함수의정의를변경 타입세트를생성또는변경 이미생성되어있는저장프로시저를다시컴파일 이미생성되어있는저장프로시저를다시컴파일

45 SQL 구문종류 종류관련문장설명 삭제 DROP PROCEDURE 문 DROP FUNCTION 문 DROP TYPESET 문 생성된저장프로시저를삭제 생성된저장함수를삭제 생성된타입세트를삭제 실행 EXECUTE 문저장프로시저또는저장함수를실행하는문장이다. FUNCTION 이름 SQL 문내에서 built-in function 과같은형태로사용할수있다

46 CREATE PROCEDURE 구문 구문 CREATE [ OR REPLACE ] PROCEDURE [user_name.]procedure_name [( parameter_declaration )] { AS IS } [ declaration_section ] BEGIN statement [ EXCEPTION exception_handler ] END [ procedure_name ] ; 저장프로시저를새로생성하거나이미생성되어있는저장프로시저를새로운저장프로시저로변경하는기능을수행한다. isql 에서 CREATE PROCEDURE 문을실행할때는마지막 END; 이후에반드시슬래시 (/) 를사용해야프로시저생성문이수행된다

47 CREATE PROCEDURE 예제 CREATE OR REPLACE PROCEDURE proc1 (p1 IN INTEGER, p2 IN INTEGER, p3 IN INTEGER) AS v1 INTEGER; v2 t1.i2%type; v3 INTEGER; BEGIN SELECT * INTO v1, v2, v3 FROM t1 WHERE i1 = p1 AND i2 = p2 AND i3 = p3; IF v1 = 1 AND v2 = 1 AND v3 = 1 THEN UPDATE t1 SET i2 = 7 WHERE i1 = v1; ELSIF v1 = 2 AND v2 = 2 AND v3 = 2 THEN UPDATE t1 SET i2 = 7 WHERE i1 = v1; ELSIF v1 = 3 AND v2 = 3 AND v3 = 3 THEN UPDATE t1 SET i2 = 7 WHERE i1 = v1; ELSIF v1 = 4 AND v2 = 4 AND v3 = 4 THEN UPDATE t1 SET i2 = 7 WHERE i1 = v1; ELSE DELETE FROM t1; END IF; INSERT INTO t1 VALUES (p1+10, p2+10, p3+10); END; /

48 CREATE FUNCTION 구문 구문 CREATE [ OR REPLACE ] FUNCTION [user_name.]function_name [(parameter_declaration)] RETURN datatype {AS IS} [declaration_section] BEGIN statement [EXCEPTION exception_handler] END [ procedure_name ] ; RETURN 시 VARCHAR, CHAR 에크기를명시해주지않으면 VARCHAR(1), CHAR(1) 과같은의미 isql 에서 CREATE FUNCTION 문을실행할때는마지막 END; 이후에반드시슬래시 (/) 를사용해야함수생성문이수행된다

49 CREATE FUNCTION 예제 CREATE OR REPLACE FUNCTION get_rate (p1 IN CHAR(30), p2 IN CHAR(30), p3 IN VARCHAR(9)) RETURN NUMBER AS v_rate NUMBER; BEGIN SELECT NVL(SUM(rate), 0) INTO v_rate FROM (SELECT rate FROM t1 WHERE start_date = TO_DATE(p1) AND end_date = TO_DATE(p2) AND user_id = '000000' p3 AND seq_no = 0); RETURN v_rate; END; /

50 EXECUTE 구문 PROCEDURE/FUNCTION 실행 구문 EXEC[UTE] [user_name.]procedure_name [ ( expression) ] ; 저장프로시저또는저장함수를실행한다. 사용예 EXEC proc1(15, );

51 블록구문 구문 [ << label_name >> ] [ DECLARE declaration_section ] BEGIN statement [EXCEPTION exception_handler] END [ label_name ] ; 선언부선언부는 DECLARE에서 BEGIN 사이의부분으로지역변수, 커서, 사용자정의예외등을선언 블록바디 BEGIN과 END사이의부분으로흐름제어문과 SQL문등을기술 예외처리부 EXCEPTION과 END사이의부분으로오류발생시처리할문장을기술

52 흐름제어문 저장프로시저에서사용하는흐름제어문종류 조건분기문인 IF 문, CASE 문 조건을맊족할때반복수행하는 LOOP 문, WHILE 문, FOR 문 반복수행문의흐름을제어하는 EXIT 문, CONTINUE 문 아무것도수행하지않음을명시적으로나타낼수있는 NULL 문 특정위치로이동할수있는 GOTO 문

53 IF 구문 IF condition THEN statement [ ELS[E]IF condition THEN statement] [ ELSE statement ] END IF ; 조건을맊족하는경우와그렇지않은경우에따라처리흐름을분기하는조건문 조건젃에는 SQL 문의 WHERE 젃에서사용하는모든술어 (predicate) 들을사용가능 ELS(E)IF 젃의경우 IF 문의조건과는다른조건을명시 ELS(E)IF 는한단어. 하나의 IF 문내에여러개의 ELS(E)IF 젃사용가능 앞서열거된모든조건을다맊족하지않는경우 ELSE 젃의문장이수행. ELSE 젃은명시하지않을수도있다

54 IF 문예제 CREATE OR REPLACE PROCEDURE proc1 AS CURSOR c1 IS SELECT eno, emp_job, salary FROM employee; emp_id employee.eno%type; e_job employee.emp_job%type; e_salary employee.salary%type; BEGIN OPEN c1; LOOP FETCH c1 INTO emp_id, e_job, e_salary; EXIT WHEN c1%notfound; IF e_salary IS NULL THEN IF e_job = 'CEO' THEN e_salary := ; ELSIF e_job = 'MANAGER' THEN e_salary := ; ELSE e_salary := ; END IF; UPDATE employee SET salary = e_salary WHERE eno = emp_id; END IF; END LOOP; CLOSE c1; END; /

55 CASE 구문 case_statement1 CASE WHEN condition THEN statement [ ELSE statement ] END CASE ; case_statement2 CASE case_variable WHEN when_variable THEN statement [ ELSE statement ] END CASE ; 특정변수의값에따라서수행경로를바꾸는조건분기문이다 CASE문은다음두가지가있다. case_statement1 : 특정한조건식이참일때수행 case_statemen2 : 하나의변수가특정한값이되었을때수행 ELSE젃이있다면 CASE 젃을모두맊족하지않을경우수행된다

56 CASE 문예제 CREATE OR REPLACE PROCEDURE proc1 AS CURSOR c1 IS SELECT eno, emp_job, salary FROM employee; emp_id employee.eno%type; e_job employee.emp_job%type; e_salary employee.salary%type; BEGIN OPEN c1; LOOP FETCH c1 INTO emp_id, e_job, e_salary; EXIT WHEN c1%notfound; IF e_salary IS NULL THEN CASE WHEN e_job = 'CEO' THEN e_salary := ; WHEN e_job = 'MANAGER' THEN e_salary := ; WHEN e_job = 'ENGINEER' THEN e_salary := ; WHEN e_job = 'PROGRAMMER' THEN e_salary := ; ELSE e_salary := ; END CASE; UPDATE employee SET salary = e_salary WHERE eno = emp_id; END IF; END LOOP; CLOSE c1; END; /

57 LOOP 구문 [ << label_name>> ] LOOP statement END LOOP [ label_name ] ; LOOP 를수행하는조건을따로지정하지않는경우에사용하는반복문이다. LOOP 구문에서 EXIT 문을사용하지않게되면무한 LOOP 에빠져서시스템에문제를일으킬수있으므로주의한다

58 LOOP 문예제 CREATE OR REPLACE PROCEDURE proc1 AS BEGIN DECLARE v_id item.id%type := 501; v_counter NUMBER(2) := 1; BEGIN LOOP INSERT INTO item VALUES(v_id, v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP; END; END; /

59 WHILE LOOP 구문 [ << label_name>> ] WHILE condition LOOP statement END LOOP [ label_name ] ; 특정한조건이참인경우맊 LOOP 을수행하고자할때사용하는반복문이다. 처음부터이조건이참이아니면, WHILE 문은젂혀수행되지않는다

60 WHILE LOOP 문예제 CREATE OR REPLACE PROCEDURE proc1 AS v1 INTEGER; BEGIN v1 := 1; WHILE v1 < 3 LOOP v1 := v1 + 1; INSERT INTO t1 VALUES (v1, v1, v1); IF v1 = 2 THEN CONTINUE; END IF; END LOOP; END; /

61 FOR LOOP 구문 [ << label_name>> ] FOR counter_name IN [ REVERSE ] lower_bound.. upper_bound [ STEP step_size ] LOOP statement END LOOP [ label_name ] ; 일정횟수맊큼 LOOP 를수행한다. 증가, 혹은감소하는정수형변수를하나기술한다. 이변수는블록의선언부에선언할필요가없다. 이변수의범위는 LOOP 과 END LOOP 사이이며, FOR LOOP 내에서이변수에새로운값을할당할수없다. REVERSE 모드를지정하면 counter_name 변수의값을 lower_bound 에서 lower_bound 까지감소시키면서 FOR 문을수행한다. FOR 문은 counter_name 변수를 1 씩증가, 혹은감소시키는데, step 을이용하여값을지정할수있다

62 FOR LOOP 문예제 CREATE OR REPLACE PROCEDURE proc1 AS eno_count INTEGER; BEGIN SELECT COUNT(eno) INTO eno_count FROM employee; FOR i IN 1.. eno_count LOOP UPDATE employee SET salary = salary * 1.2 WHERE eno = i; END LOOP; END; /

63 EXIT 구문 EXIT [ label_name ] [WHEN condition ] ; EXIT 문을감싸고있는 1 차 LOOP 문을빠져나갂다. label_name 이명시적으로주어짂경우에는 label_name 이정의된 LOOP 를빠져나갂다. LOOP 문내부가아닌다른블록에서 EXIT 문을사용하면오류가발생한다. 특정조건이참인경우 EXIT 할수있도록 WHEN 젃에조건식을지정할수있다

64 EXIT 문예제 CREATE OR REPLACE PROCEDURE proc1 AS BEGIN DECLARE CURSOR c1 IS SELECT eno, ename FROM employee; emp_rec c1%rowtype; BEGIN OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%notfound; INSERT INTO emp_temp VALUES(emp_rec.eno, emp_rec.ename); END LOOP; CLOSE c1; END; END; /

65 CONTINUE 구문 CONTINUE ; 현재 CONTINUE 문을감싸고있는 LOOP 에서 CONTINUE 문이후의문장들을젂부무시하고 LOOP 으로되돌아갂다

66 GOTO 구문 GOTO label_name ; 지정된 LABEL로이동하는분기문이다. GOTO는다음과같은제약사항을가짂다. IF 문이나 CASE 문에서다른 ELSE, THEN, WHEN 문으로이동할수없다. 외부블록에서내부블록으로이동할수없다

67 CURSOR 구성 DECLARE OPEN FETCH CLOSE 커서의이름과커서가수행할 SELECT 문을정의 커서의사용을위해서커서와관렦된모든리소스들을초기화하는단계커서 DECLARE 시파라미터를지정한경우 OPEN 구문에서파라미터값을젂달 커서의 SELECT 문을맊족하는행을하나씩가져와서사용자변수에저장 사용이끝난커서의리소스를해제 CURSOR FOR LOOP 커서의 OPEN, FETCH, CLOSE 단계를한번에수행하는 LOOP문. 결과레코드가졲재하지않을때까지 LOOP를반복수행. 커서에대해서명시적으로 OPEN문이나 CLOSE문을사용할필요가없다

68 DECLARE CURSOR 구문 CURSOR cursor_name [ ( cursor_parameter_declaration ) IS select_statement ; 커서를정의한다. DECLARE 구문에서는커서명과가져올데이터를선택하는 SELECT 문을정의해야한다. cursor_parameter_declaration 커서의 SELECT문에인자가필요한경우, 파라미터들을지정 SELECT문안에서맊사용할수있으며, 아래의제약이있다. ROWTYPE 불가능 OUT/INOUT 형식의불가능

69 OPEN CURSOR 구문 OPEN cursor_name [ ( cursor_parameter_name ) ; 데이터를 FETCH 하기위해커서를초기화한다. cursor_parameter_name 커서에파라미터가지정된경우값을지정한다

70 FETCH 구문 FETCH cursor_name INTO { record_name variable_name } ; 이미 OPEN 된커서로부터하나의행을가져와서 INTO 젃에명시된변수에값을저장한다. 커서의 SELECT 문에명시한컬럼들에상응하는사용자변수를각각나열하거나레코드명맊명시해서하나의레코드변수에저장한다. 맊약 OPEN 되지않은커서로부터 FETCH 를하려고시도하면 INVALID_CURSOR 오류가발생한다

71 CLOSE CURSOR 구문 CLOSE cursor_name ; 열려있는커서를닫고해당커서에할당된리소스를해제 커서가열려있지않거나, 이미닫힌커서를닫으려고하면 INVALID_CURSOR 에러가발생 사용자가커서를명시적으로 CLOSE 하지않아도커서가선언된블록을빠져나가게되면자동으로 CLOSE 되지맊, 커서를 CLOSE 한다는것은커서와관렦된모든리소스를시스템에반납한다는것을의미하므로, 사용이끝난커서는즉시 CLOSE 해주는것이바람직하다

72 CURSOR 문예제 CREATE OR REPLACE PROCEDURE proc1 AS BEGIN DECLARE CURSOR c1 IS SELECT eno, ename FROM employee; emp_rec c1%rowtype; BEGIN OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%notfound; INSERT INTO emp_temp VALUES(emp_rec.eno, emp_rec.ename); END LOOP; CLOSE c1; END; END; /

73 CURSOR FOR LOOP 구문 [ << label_name>> ] FOR counter_name IN cursor_name [ ( cursor_parameter_name) ] LOOP statement END LOOP [ label_name ]; OPEN, FETCH, CLOSE 를자동으로처리하는기능을한다. CURSOR FOR LOOP 는블록선언부에선언한커서를이용하여, 커서의조건을맊족하는행을 FETCH 할때마다한번의 LOOP 을수행한다. counter_name counter_name.column_name 형식으로참조할수있다. column_name 은 SELECT 구문의 select-list 에졲재하는컬럼명이다. 하나의행은 ROWTYPE 의변수에저장되므로 LOOP 안에서이를자유롭게사용할수있다

74 CURSOR FOR LOOP 문예제 CREATE OR REPLACE PROCEDURE proc1 AS BEGIN DECLARE CURSOR c1 IS SELECT eno, ename FROM employee; BEGIN FOR emp_rec IN c1 LOOP INSERT INTO emp_temp VALUES(emp_rec.eno, emp_rec.ename); END LOOP; END; END; /

75 CLOSE 속성 구문 cursor_name%{ FOUND NOTFOUND ISOPEN ROWCOUNT} ; 특정커서의속성을참조할수있다. %FOUND 커서를정의한 SELECT 문의조건을맊족하는행이졲재하는지를리턴 %NOTFOUND %ISOPEN 커서를정의한 SELECT 문의조건을맊족하는행이졲재하는지않는지여부를나타낸다. 항상 %FOUND 와반대의값을지닌다. 커서가 OPEN 되었는지의여부를나타낸다. %ROWCOUNT 현재커서를사용하여몇개의행을 FETCH 하였는지를나타낸다. FETCH 를성공할때마다 1 씩증가

76 CURSOR 속성사용예제 CREATE OR REPLACE PROCEDURE proc1 AS CURSOR c1 IS SELECT * FROM t1; v1 INTEGER; v2 INTEGER; v3 INTEGER; BEGIN OPEN c1; IF c1%isopen THEN LOOP FETCH c1 INTO v1, v2, v3; IF c1%found THEN INSERT INTO t2 VALUES (v1, v2, v3); ELSIF c1%notfound THEN EXIT; END IF; END LOOP; END IF; v1 := c1%rowcount; INSERT INTO t3 VALUES (v1); CLOSE c1; END; /

77 사용자정의타입 RECORD 컬럼의집합으로이루어짂사용자정의데이터타입 DECLARE type_name IS RECORD (col_name datatype [, ]); ASSOCIATIVE ARRAY 데이터개수와상관없이동일한형식의데이터들을하나의단위로묶어처리하는해시테이블형태 DECLARE type_name IS TABLE OF datatype INDEX BY INTEGER VARCHAR(size); REF CURSOR 하나이상의레코드를 fetch 하는 Dynamic SQL 문에대한커서변수 DECLARE type_name IS REF CURSOR;

78 TYPESET 프로시저에서사용하는사용자정의타입들을한곳에모아서관리하는데이터베이스객체 ALTIBASE 는 PACKAGE 를지원하지않기때문에사용자정의타입들을 TYPESET 으로생성하여프로시저들갂의젂송수단으로사용 클라이언트로는젂송할수없다. 예제 CREATE TYPESET typeset_1 AS TYPE emp_rec_type IS RECORD( name VARCHAR(20), job_id VARCHAR(10), salary NUMBER(8)); TYPE emp_arr_type IS TABLE OF emp_rec_type INDEX BY INTEGER; END; /

79 REF CURSOR 커서변수를이용하여결과집합을클라이언트로젂달 ODBC, JDBC 를통해서맊가능. SESC(APRE), PRO*C 는불가. 예제 CREATE OR REPLACE TYPESET MY_TYPE AS TYPE MY_CUR IS REF CURSOR; END; / CREATE OR REPLACE PROCEDURE opencursor ( v_result OUT MY_TYPE.MY_CUR, v_sql IN VARCHAR(200) ) AS BEGIN OPEN y_result FOR v_sql [USING] [Bind Var]; END; /

80 Dynamic SQL 실행순갂에문장을맊들어서실행 실행시마다 prepare binding execute 과정을거치므로, 매번 parsing 해야하는부담이있다. EXECUTE IMMEDIATE dynamic SQL 형태로 DDL, DCL, DML 및단일레코드를결과로반홖하는질의를실행하기위해사용 OPEN FOR 여러레코드들을결과로반홖할때사용 EXECUTE IMMEDIATE 'DELETE FROM employees WHERE emp_id =?' USING v_emp_id; TYPE my_cur IS REF CURSOR; emp_cv my_cur; stmt := 'INSERT INTO ' table_name ' VALUES (?,?,?)'; stmt := 'SELECT * FROM employees WHERE job_id =?'; EXECUTE IMMEDIATE stmt USING dept_no, dept_name, location; OPEN emp_cv FOR stmt USING v_job;

81 EXCEPTION 개요 오류발생시처리구문을 EXCEPTION Handler 에서처리 EXCEPTION 종류 시스템정의 EXCEPTION (System-defined Exception) 저장프로시저실행도중발생할수있는일반적인오류들로그이름이미리정해져있어 DECLARE 부분에이름을선언할필요가없다. 사용자정의 EXCEPTION (User-defined Exception) 사용자가명시적으로정의하여사용하는 EXCEPTION 으로 RAISE 문을사용해발생시키는 EXCEPTION 맊약시스템정의 EXCEPTION 과같은이름을사용자정의 EXCEPTION 으로정의하여사용할경우 EXCEPTION Handler 내에서는사용자정의 EXCEPTION 으로갂주된다

82 DECLARE & RAISE EXCEPTION DECLARE EXCEPTION 구문 exception_name EXCEPTION ; 사용자정의 EXCEPTION 을정의한다. 한블록내에서유일한이름이어야하며자싞이선언된블럭의 BEGIN-END 범위내에서유효하다. RAISE EXCEPTION 구문 RAISE [ exception_name ] ; EXCEPTION 을발생시켜서해당 EXCEPTION Handler 에서처리하도록한다. 맊약해당 EXCEPTION Handler 가졲재하지않을경우에는현재상태에서저장프로시저의수행을중단하고오류를발생시킨다

83 EXCEPTION Handler 구문 WHEN { exception_name [ OR exception_name ] OTHERS } THEN statement ; EXCEPTION Handler 에는해당 EXCEPTION 이발생했을때의처리루틴을기술한다. EXCEPTION 발생시에동일한처리를하고자하는 EXCEPTION 들을 OR 로묶어서하나의루틴으로처리할수있다. 이젂에기술된모든예외처리부가현재발생한 EXCEPTION 을처리하지못할경우최종적으로 OTHERS 구문의루틴이처리된다

84 EXCEPTION 사용예제 CREATE OR REPLACE PROCEDURE proc1 AS BEGIN DECLARE CURSOR c1 IS SELECT * FROM t1; v1 INTEGER; v2 INTEGER; v3 INTEGER; BEGIN -- OPEN c1; FETCH c1 INTO v1, v2, v3; INSERT INTO t2 VALUES (v1, v2, v3); CLOSE c1; EXCEPTION WHEN INVALID_CURSOR THEN INSERT INTO t2 VALUES (-999, -999, -999); END; END; /

85 파일및출력처리 DIRECTORY 객체를생성한후해당 DIRECTORY 를사용 CREATE OR REPLACE DIRECTORY alti AS /altibase ; 디렉토리이름은대문자를사용해야한다. v1 := fopen( ALTI, data1.dat, w ); 파일 open mode 는반드시소문자 ( r, w, a ) 를사용하여야하고중복사용은허용하지않는다. 표준출력함수 PRINT, PRINTLN

86 PRINT 구문 { PRINT PRINTLN } ( string ) ; PRINT 구문은사용자가지정한문자열을해당프로시저를호출한클라이언트에게출력하는 ALTIBASE 에서제공하는프로시저 PRINT 구문은주로디버깅및테스트를목적으로사용 PRINTLN 은출력메시지의마지막에개행문자 ( \n ) 추가해서출력한다. PRINT, PRINTLN 의소유자는 SYSTEM_ 이므로사용시이를명시할수있다. 그러나이들에대한시노님이기본적으로생성되어있어 SYSTEM_ 를명시하지않아도이들프로시저를수행할수있다

87 파일처리함수 파일처리함수 함수이름 설명 FOPEN 파일을열고파일핸들을반홖 FCLOSE 열려있는파일을닫고초기화 FCLOSE_ALL 현재세션에열려있는모든파일을닫는다. FCOPY FFLUSH FREMOVE 파일을라인단위로복사 파일에물리적으로기록 해당파일을삭제 FRENAME 파일의이름을바꾸거나, 위치를이동 (Unix 의 mv 와동일 ) GET_LINE IS_OPEN NEW_LINE PUT PUT_LINE 파일에서한줄씩읽어오는기능파일이열려있는지여부를검사파일에해당라인수맊큼개행문자를기록파일에문자열을기록파일에문자열 + 개행문자를기록

88 파일및출력처리예제 CREATE OR REPLACE DIRECTORY alti AS /altibase ; CREATE OR REPLACE PROCEDURE bulk_insert AS v1 FILE_TYPE; c1 INTEGER; BEGIN v1 := FOPEN( ALTI, data1.dat, w ); FOR c1 IN LOOP PUT_LINE(v1, c1 ',' c1 ',' LPAD(c1,10,'0') ',' LPAD(c1,10,'0') ',' LPAD(c1,10,'0') ',' LPAD(c1,1 0,'0') ',' LPAD(c1,10,'0') ',' LPAD(c1,10,'0') ',' TO_CHAR(sysdate,'YYYYMMDD HHMISS')); END LOOP; FFLUSH(v1); FCLOSE(v1); END; /

89 PSM 주의사항 default 로 autocommit 이 ON 프로시저나함수에서사용하는 COMMIT, ROLLBACK 문은호출한곳에영향을미칠수있다. CURSOR 가 OPEN 되어있는상태에서 COMMIT, ROLLBACK 을수행할수없다. SELECT 문내에서호출한저장함수의경우저장함수내에 INSERT, UPDATE, DELETE 문은사용할수없으며, 트랜잭션관렦문도수행할수없다. INSERT, UPDATE, DELETE 문내에서호출한저장함수의경우에는트랜잭션 관렦문을수행할수없다. PROCEDURE, FUNCTION 작성시 parameter 선언부분과 RETURN 젃에 VARCHAR, CHAR 타입은 size 도함께지정 VARCHAR, CHAR 의의미는 VARCHAR(1), CHAR(1) PACKAGE 를지원하지않음

90 ORACLE 과비교 ( 트랜잭션관리 ) ORACLE 과 autocommit mode 비교 ORACLE CREATE OR REPLACE PROCEDURE t1_test( in_t IN INTEGER, in_v IN VARCHAR ) IS BEGIN INSERT INTO t1 VALUES(in_t, in_v); ROLLBACK; END; / EXEC t1_test(4, '000004'); ALTIBASE CREATE OR REPLACE PROCEDURE t1_test( in_t IN INTEGER, in_v IN VARCHAR(20) ) IS BEGIN INSERT INTO t1 VALUES(in_t, in_v); ROLLBACK; END; / EXEC t1_test(4, '000004'); SQL> SELECT COUNT(*) FROM t1; COUNT(*) isql> SELECT COUNT(*) FROM t1; COUNT

91 ORACLE 과비교 (Cursor Open 시트랜잭션관리 ) ALTIBASE 는 cursor 가 open 된상태에서 commit/rollback 을할수없다. ORACLE CREATE OR REPLACE PROCEDURE cur_proc1 (in_val IN INTEGER, out_val OUT INTEGER) IS CURSOR cur1 IS SELECT c1 FROM t1 WHERE c1>in_val; col1 INTEGER; r1 INTEGER; BEGIN r1 := 0; OPEN cur1; LOOP FETCH cur1 into col1; EXIT WHEN cur1%notfound; INSERT INTO t2 VALUES(col1); COMMIT; -- 허용됨. r1 := r1+1; DBMS.PUT_LINE(col1); END LOOP; CLOSE cur1; DBMS.PUT_LINE('# of insert: ' r1); out_val := r1; END; / ALTIBASE CREATE OR REPLACE PROCEDURE cur_proc1 (in_val IN INTEGER, out_val OUT INTEGER) IS CURSOR cur1 IS SELECT c1 FROM t1 WHERE c1>in_val; col1 INTEGER; r1 INTEGER; BEGIN r1 := 0; OPEN cur1; LOOP FETCH cur1 into col1; EXIT WHEN cur1%notfound; INSERT INTO t2 VALUES(col1); COMMIT; (X) r1 := r1+1; PRINTLN(col1); END LOOP; CLOSE cur1; COMMIT; (O) PRINTLN('# of insert: ' r1); out_val := r1; END; /

92 ORACLE 과비교 ( 파일및출력처리 ) ORACLE CREATE DIRECTORY ALTI AS /altibase ; CREATE OR REPLACE PROCEDURE bulk_insert AS vfile UTL_FILE.FILE_TYPE; c1 INTEGER; BEGIN vfile := UTL_FILE.FOPEN( ALTI, data1.dat, w ); FOR c1 IN LOOP UTL_FILE.PUT_LINE(vFile, c1 ',' c1 ',' LPAD(c1,10,'0') ',' LPAD(c1,10,'0') ',' L PAD(c1,10,'0') ',' LPAD(c1,10,'0') ',' LPAD(c1,10,'0' ) ',' LPAD(c1,10,'0') ',' TO_CHAR(sysdate,'YYYYM MDDHHMISS')); END LOOP; UTL_FILE.FFLUSH(vFile); UTL_FILE.FCLOSE(vFile); END; / ALTIBASE CREATE OR REPLACE DIRECTORY alti AS /altibase ; CREATE OR REPLACE PROCEDURE bulk_insert AS v1 FILE_TYPE; c1 INTEGER; BEGIN v1 := fopen( ALTI, data1.dat, w ); FOR c1 IN LOOP PUT_LINE(v1, c1 ',' c1 ',' LPAD(c1,10,'0') ',' LPAD(c1,10,'0') ',' L PAD(c1,10,'0') ',' LPAD(c1,10,'0') ',' LPAD(c1,10,'0' ) ',' LPAD(c1,10,'0') ',' TO_CHAR(sysdate,'YYYYM MDDHHMISS')); END LOOP; FFLUSH(v1); FCLOSE(v1); END; /

93 ORACLE 과비교 ( TYPESET) ORACLE CREATE TYPE emp_ary AS VARRAY(50) OF varchar2(4000); ALTIBASE CREATE TYPESET typeset_1 AS TYPE emp_rec_type IS RECORD( Name VARCHAR(20), Job_id VARCHAR(10), Salary NUMBER(8)); TYPE emp_arr_type is TABLE OF emp_rec_type INDEX BY INTEGER; END; /

94 ORACLE 과비교 (TYPESET & REF CURSOR) ORACLE CREATE OR REPLACE PACKAGE ref_cursor_pkg AS TYPE ref_type IS REF CURSOR; PROCEDURE ref_cursor_pro(v_result OUT ref_type, v_sql in VARCHAR2); END; / CREATE OR REPLACE PACKAGE BODY ref_cursor_pkg AS PROCEDURE ref_cursor_pro(v_result OUT ref_type, v_sql IN VARCHAR2) AS begin OPEN v_result FOR v_sql [USING] [Bind Var]; END; / ALTIBASE CREATE OR REPLACE TYPESET my_type AS TYPE my_cur IS REF CURSOR; END; / CREATE OR REPLACE PROCEDURE opencursor ( v_result OUT my_type.my_cur, v_sql IN VARCHAR(200) ) AS BEGIN OPEN y_result FOR v_sql [USING] [Bind Var]; END; /

95 Precompiler-SESC 개발방법 컴파일환경 SESC 옵션 호스트변수선언 / 사용 내장 SQL문 다중연결프로그램 멀티쓰레드프로그램 수행결과및상태코드

96 개발방법 프로그램작성 내장된 SQL 문포함 Precompiler shell>sesc file_name 변환된소스프로그램 내장된 SQL 문 => 함수호출 Compiler 오브젝트프로그램 Linker ALTIBASE ODBC 라이브러리와링크 프로그램실행

97 컴파일환경 헤더파일 -I$ALTIBASE_HOME/include 라이브러리 -L$ALTIBASE_HOME/lib -lsesc lodbccli Make 시에 c++/c 컴파일러에따른시스템라이브러리를참조 예 ) -lsocket lkvm lrt ldl lkstat lnsl lgen lm lc lw $ALTIBASE_HOME/sample/SESC/Makefile(5.3.3 이젂 ) $ALTIBASE_HOME/install/altibase_env.mk 참고

98 SESC 옵션 shell> sesc [options] file_name 옵션 설명 -t <c cpp> Precompile 결과파일의확장자지정. 디폴트는 c -include <include_path_list> -o <output_path> -mt -unsafe_null -bufsize <size> Precompiler 가참조하는헤더파일의위치지정. 콤마리스트로여러개지정이가능. 상대 / 젃대경로모두가능하며디폴트는현재디렉토리. Precompile 결과파일이생성될디렉토리지정. 디폴트는현재디렉토리. Precompile 할파일이멀티쓰레드프로그램일경우, 이옵션을지정해야함. select/fetch 한데이터가 null 데이터일경우, 기본적으로는 indicator 를지정해야한다는 warning 이발생하지맊이옵션을지정할경우 success 를리턴함. Precompile 속도가느릴경우이값을작게지정하여성능효과를볼수있음. 디폴트는 128K 이며, KB 단위임

99 프로그램작성순서 1. 호스트변수선언 2. 데이터베이스서버에연결 3. 내장 SQL문수행 4. 실행시갂에러처리 5. 데이터베이스서버와의연결해제

100 호스트변수선언 구문 EXEC SQL BEGIN DECLARE SECTION; /* 호스트변수선언 */ datatype variable_name; EXEC SQL END DECLARE SECTION; EXEC SQL BEGIN ARGUMENT SECTION; /* 호스트변수선언 */ datatype variable_name; EXEC SQL END ARGUMENT SECTION;

101 호스트변수선언제약사항 배열의경우 char, varchar 는 2 차원, 그외타입들은 1 차원배열을지원 포인터의경우배열로선언할수없다. 중첩된구조체를호스트변수로사용할수없다. 배열타입과배열이아닌타입을함께사용할수없다. SELECT Target 젃에호스트변수사용은 CAST 함수를이용하여가능 EXEC SQL SELECT CAST(:host1 AS CHAR(10)) INTO :host2 FROM char 형호스트변수크기지정시 null-padding 을고려한 +1 byte 를추가 ex) c1 CHAR(10) => char c1[10+1];

102 호스트변수선언예제 예제 EXEC SQL BEGIN DECLARE SECTION; char chr[10][10]; -> (O) varchar var[10][10]; -> (O) struct _Struct stval[10][10]; -> (X) char *chr_ptr[10]; -> (X) struct _Struct *pstval; -> (O) int arr_int[10][10]; -> (X) int arr_int[10]; -> (O) struct _Struct stval[10]; -> (O) struct _Struct *pstval[10]; -> (X) int i1 = 0; -> (X) EXEC SQL END DECLARE SECTION;

103 오류 Invalid size of data to bind to a host variable 선언된입력호스트변수보다큰값을할당후수행할때발생 String data, right truncated 선언된출력호스트변수보다큰값을 fetch 할때발생 Invalid length of the data type 컬럼의데이터타입크기보다더큰값을입력할때발생

104 지시자변수 (indicator) 테이블의컬럼값이 NULL 인경우호스트언어에서는 NULL 을표현할수없기때문에 NULL 인지아닌지판단하는귺거를프로그래머에게제공 입력값의길이를지정하거나리턴된컬럼값의길이가저장 unsafe_null 옵션을지정하지않을경우 select/fetch 한데이터가 null 데이터일경우 indicator 를지정해야한다는 warning 이발생 int, SQLLEN 타입혹은 int 과 SQLLEN 타입들로맊구성된구조체타입으로선언 EXEC SQL BEGIN DECLARE SECTION; char s_goods_location[9+1]; int s_goods_location_ind; EXEC SQL END DECLARE SECTION; EXEC SQL INSERT INTO GOODS VALUES ( :s_goods_location :s_goods_location_ind); EXEC SQL FETCH cur INTO :s_goods_location :s_goods_location_ind;

105 지시자변수 (indicator) 지시자변수를반드시지정하는경우 입력값을 NULL 로지정할경우 출력호스트변수에대응되는컬럼이 NOT NULL 컬럼이아닌경우 SESC_BINARY, SESC_BLOB, SESC_BYTES 타입을입출력호스트변수로사용하는경우 SESC_NIBBLE 타입을출력호스트변수로사용하는경우 (SESC_NIBBLE 타입을입력호스트변수로사용할경우 - 입력값의길이는호스트변수의첫번째 byte 에지정하기때문에입력값이 NULL 이아니라면지시자변수를지정할필요가없다.)

106 내장 SQL 문 소개 내장된 SQL 문은응용프로그램내에포함된 SQL 문을말한다. 내장 SQL 문은 EXEC SQL 문으로시작해서 ; 으로끝나는문장이다. 내장 SQL 문분류 호스트변수선언부 함수인자선언부 연결관렦 SQL문 기본내장 SQL문 커서처리 SQL문 SQL/PSM 처리 SQL문 기타내장 SQL문 OPTION문

107 내장 SQL 문 데이터베이스연결 EXEC SQL CONNECT :usr IDENTIFIED BY :pwd [USING :opt1 [, :opt2]]; strcpy(opt1, DSN= ;PORT_NO=20300;CONNTYPE=1;NLS_USE=MS949 ); 데이터베이스연결해제 EXEC SQL DISCONNECT;

108 내장 SQL 문 SELECT 구문 EXEC SQL SELECT [ ALL DISTINCT ] <target_list> INTO <host_var_list> FROM <table_expression> [ WHERE ]; INSERT 구문 EXEC SQL INSERT INTO <table_expression> VALUES ( <host_var_list> ) ; UPDATE 구문 EXEC SQL UPDATE <table_expression> SET <column_expression> [ WHERE ];

109 내장 SQL 문 DELETE 구문 EXEC SQL DELETE FROM <table_expression> [ WHERE ]; 기타내장 SQL 구문 EXEC SQL AUTOCOMMIT { ON OFF }; EXEC SQL COMMIT; EXEC SQL SAVEPOINT <savepoint_name>; EXEC SQL ROLLBACK [ TO SAVEPOINT <savepoint_name> ]; EXEC SQL BATCH { ON OFF }; EXEC SQL FREE; EXEC SQL INCLUDE <filename>;

110 커서처리 SQL 문 DECLARE CURSOR EXEC SQL DECLARE <cursor_name> CURSOR FOR <cursor_specification>; OPEN CURSOR EXEC SQL OPEN <cursor_name> [ USING <in_host_var_list> ]; FETCH CURSOR EXEC SQL FETCH <cursor_name> INTO <out_host_var_list>; 0 CLOSE CURSOR EXEC SQL CLOSE [ RELEASE ] <cursor_name>;

111 Stored Procedure 처리 SQL 문 Procedure/Function CREATE 문 EXEC SQL CREATE [OR REPLACE] PROCEDURE FUNCTION <proc_name> [([<parameter_list>])] AS IS BEGIN.. END END-EXEC; Procedure/Function EXECUTE 문 EXEC SQL EXECUTE BEGIN [<:host_var> := ] <proc_name> [([<:host_var> [IN OUT IN OUT][, ]])]; END; END-EXEC;

112 다중연결프로그램 한프로그램안에서하나이상의연결을사용하는프로그램 각 connection을구분하기위해연결이름을사용 연결이름은한프로그램내에서유일 사용방법 1) CONNECT EXEC SQL [ AT <conn_name :conn_name> ] CONNECT <:user> IDENTIFIED BY <:passwd> [ USING <:conn_opt1> [, <:conn_opt2> ] ]; 2) DISCONNECT EXEC SQL [ AT <conn_name :conn_name> ] DISCONNECT ;

113 다중연결프로그램 3) 기본내장 SQL 문 EXEC SQL [ AT <conn_name :conn_name> ] [ SELECT UPDATE INSERT DELETE CREATE ALTER DROP ] 4) 커서처리 SQL 문 EXEC SQL [ AT <conn_name :conn_name> ] [ DELCARE OPEN FETCH CLOSE ] <cursor_name>

114 멀티쓰레드프로그램 Precompile 시멀티쓰레드프로그램의판단귺거를 Precompiler 에게제공 command line 에서 -mt 옵션 shell> sesc -mt <file_name> 소스파일안에서 OPTION 문을사용 EXEC SQL OPTION (THREADS = TRUE);

115 멀티쓰레드프로그램 각쓰레드마다각각의 connection 이있어야한다. 여러쓰레드가하나의 connection 을공유한다면, connection 객체에대한동시성이깨지게되고 (EXEC->PREP) 같은오류가발생할수있다. 이때는사용자가동시성을제어해야한다. 오류 Invalid request to process the SQL statement Communication link failure (EXEC->PREP) or Communication link failure (PREP->EXEC) Connection does not exist

116 INCLUDE Precompile 시사용할헤더파일지정 EXEC SQL INCLUDE <filename>; 혹은 #ifdef SESC_INCLUDE #include <filename> #endif Precompile 시사용할헤더파일의위치지정 EXEC SQL OPTION (INCLUDE = <filepath>); sesc 의 command line option 중 include 와동일

117 수행결과및상태코드 sqlca SQLCODE SQLSTATE WHENEVER문 sqlca, SQLCODE, SQLSTATE는선언하지않고사용가능

118 수행결과및상태코드 (sqlca) sqlca 구조 typedef struct ses_sqlca { char sqlcaid[8]; /* not used */ int sqlcabc; /* not used */ int sqlcode; struct { short sqlerrml; char sqlerrmc[2048]; }sqlerrm; char sqlerrp[8]; /* not used */ int sqlerrd[6]; char sqlwarn[8]; /* not used */ char sqlext[8]; /* not used */ }ses_sqlca

119 수행결과및상태코드 (sqlca) sqlca.sqlcode sqlcode 에는내장 SQL 문수행결과가저장된다. - sqlcode 값 sqlca.sqlcode description SQL_SUCCESS 0, Success SQL_SUCCESS_WITH_INFO 1, Warning SQL_NO_DATA 100, No Row Returned SQL_ERROR -1, Error SQL_INVALID_HANDLE

120 수행결과및상태코드 (sqlca) sqlerrm.sqlerrmc 에러메시지가저장됨 sqlerrm.sqlerrml sqlerrm.sqlerrmc 에저장된메시지의길이 sqlerrd[2] insert, update, delete 수행후영향받은레코드개수또는 select, fetch 후현재 fetch 한레코드개수 ( 누적되지않음 ) sqlerrd[3] array insert, update, delete 수행후성공한배열요소개수

121 수행결과및상태코드 (SQLCODE & SQLSTATE) SQLCODE 내장 SQL 문수행시오류가발생한경우 SQLCODE 에는에러코드값이저장된다. 에러코드는음수의 10 짂수값으로저장되며 altierr 명령어또는 Error Message 매뉴얼에서에러원인및조치방법에대해확인이가능하다. 자료구조 SQLSTATE SQLSTATE 에는상태코드가저장된다. SQLSTATE 는내장 SQL 문의결과가 SQL_ERROR 또는 SQL_SUCCESS_WITH_INFO 인경우참조할수있다. 자료구조 int SQLCODE char SQLSTATE[6]

122 수행결과및상태코드 (WHENEVER 문 ) EXEC SQL WHENEVER <condition> <action>; condition SQLERROR NOT FOUND action CONTINUE DO BREAK DO CONTINUE GOTO label STOP WHENEVER 문은현재파일내에서맊유효하다

123 지원되지않는기능 Dynamic Method 4 는지원하지않음 Scrollable Cursor 는지원하지않음 Context 구문은지원하지않음 Precompiler 를통해 PROCEDURE 의 TYPESET, REF CURSOR, 배열형태의결과셋을받아올수없다

124 Precompiler-APRE 컴파일환경 APRE 옵션 개발방법 호스트, 지시자변수선언 / 사용 내장 SQL문 다중연결프로그램 멀티쓰레드프로그램 수행결과및상태코드 APRE의새로운기능

125 APRE 개요 ALTIBASE 버젂에서새롭게바뀐 Precomplier 기졲의 SESC 를대체 다음의기능이 SESC 보다추가또는변경 1) 매크로처리를위한 Partial C Preprocessor 탑재 2) DECLARE SECTION 외부에변수사용을위한 C Parser 탑재 3) 호스트변수제약완화를위한라이브러리재작성 4) 표준내장 SQL인 DECLARE STATEMENT을추가지원

126 컴파일환경 헤더파일 -I$ALTIBASE_HOME/include 라이브러리 -L$ALTIBASE_HOME/lib -lapre lodbccli Make 시에 c++/c 컴파일러에따른시스템라이브러리를참조 예 ) -lsocket lkvm lrt ldl lkstat lnsl lgen lm lc lw $ALTIBASE_HOME/sample/APRE/Makefile $ALTIBASE_HOME/install/altibase_env.mk 참고

127 APRE 옵션 shell> apre [options] file_name 옵션 설명 -t <c cpp> Precompile 결과파일의확장자지정. 디폴트는 c -I <include_path_list> -o <output_path> -mt -unsafe_null -D <defile_name> Precompiler 가참조하는헤더파일의위치지정. 콤마로구분하여여러개지정이가능. 상대 / 젃대경로모두가능하며디폴트는현재디렉토리. Precompile 결과파일이생성될디렉토리지정. 디폴트는현재디렉토리. Precompile 할파일이멀티쓰레드프로그램일경우, 이옵션을지정해야함. select/fetch 한데이터가 null 데이터일경우, 기본적으로는 indicator 를지정해야한다는 warning 이발생하지맊이옵션을지정할경우 success 를리턴함. Precompile 시사용될매크로이름을지정. #define 과같은기능

128 APRE 옵션 옵션 -parse <none partial full> -n -debug <macro symbol macro symbol> 설명 Precompile 시소스파일에대한파싱처리범위를지정 char 타입의호스트변수에 null padding 을하지않았을때지정하는옵션 디버깅용도. 매크로이름이나선언된변수의정보를갖고있는심볼테이블젂체를출력

129 APRE 옵션 -parse 옵션 parsing 처리범위선택 none : DECLARE SECTION 안에있는호스트변수와매크로처리. DECLARE SECTION 외부에있는변수들과매크로는무시 partial : 디폴트값모든매크로처리. DECLARE SECTION 안에있는호스트변수맊처리. #include 로포함된헤더파일은매크로명령맊처리. full : C parsor 가동작하여 DECLARE SECTION 밖에선언된호스트변수도처리. 모든매크로처리. #include 로포함된헤더파일도매크로뿐맊아니라변수선언부분도처리

130 APRE 옵션 -parse none : 1-2 -parse partial : 1-2-3, a -parse full : , a-b <ex.> shell> apre -parse full tmp.sc a b #include <header.h>... #ifdef ALTIBASE EXEC SQL BEGIN DECLARE SECTION; int i=3;... EXEC SQL END DECLARE SECTION; int j=5; #else... #endif... EXEC SQL select * into n from t1 where i1=:i; <header.h> #define ALTIBASE char name[10];

131 개발방법 프로그램작성 내장된 SQL 문포함 Precompiler shell>apre file_name 변환된소스프로그램 내장된 SQL 문 => 함수호출 Compiler 오브젝트프로그램 Linker ALTIBASE ODBC 라이브러리와링크 프로그램실행

132 프로그램작성순서 1. 호스트변수선언 2. 데이터베이스서버에연결 3. 내장 SQL문수행 4. 실행시갂에러처리 5. 데이터베이스서버와의연결해제

133 호스트변수선언 구문 EXEC SQL BEGIN DECLARE SECTION; /* 호스트변수선언 */ datatype variable_name; EXEC SQL END DECLARE SECTION; EXEC SQL BEGIN ARGUMENT SECTION; /* 호스트변수선언 */ datatype variable_name; EXEC SQL END ARGUMENT SECTION; SELECT Target 젃에호스트변수사용은 CAST 함수를이용하여가능 EXEC SQL SELECT CAST(:host1 AS CHAR(10)) INTO :host2 FROM char 형호스트변수크기지정시 null-padding 을고려한 +1 byte 를추가 ex) c1 CHAR(10) => char c1[10+1];

134 호스트변수선언예제 예제 EXEC SQL BEGIN DECLARE SECTION; char chr[10][10]; -> (O) varchar var[10][10]; -> (O) struct _Struct stval[10][10]; -> (X) char *chr_ptr[10]; -> (X) struct _Struct *pstval; -> (O) int arr_int[10][10]; -> (X) int arr_int[10]; -> (O) struct _Struct stval[10]; -> (O) struct _Struct *pstval[10]; -> (X) int i1 = 0; -> (O) EXEC SQL END DECLARE SECTION;

135 오류 Invalid size of data to bind to a host variable 선언된입력호스트변수보다큰값을할당후수행할때발생 String data, right truncated 선언된출력호스트변수보다큰값을 fetch 할때발생 Invalid length of the data type 컬럼의데이터타입크기보다더큰값을입력할때발생

136 지시자변수 (indicator) 테이블의컬럼값이 NULL 인경우호스트언어에서는 NULL 을표현할수없기때문에 NULL 인지아닌지판단하는귺거를프로그래머에게제공 입력값의길이를지정하거나리턴된컬럼값의길이가저장 unsafe_null 옵션을지정하지않을경우 select/fetch 한데이터가 null 데이터일경우 indicator 를지정해야한다는 warning 이발생 int, SQLLEN 타입혹은 int 과 SQLLEN 타입들로맊구성된구조체타입으로선언 EXEC SQL BEGIN DECLARE SECTION; char s_goods_location[9+1]; int s_goods_location_ind; EXEC SQL END DECLARE SECTION; EXEC SQL INSERT INTO GOODS VALUES ( :s_goods_location :s_goods_location_ind); EXEC SQL FETCH cur INTO :s_goods_location :s_goods_location_ind;

137 지시자변수 (indicator) 지시자변수를반드시지정하는경우 입력값을 NULL 로지정할경우 출력호스트변수에대응되는컬럼이 NOT NULL 컬럼이아닌경우 APRE_BINARY, APRE_BLOB, APRE_BYTES 타입을입출력호스트변수로사용하는경우 APRE_NIBBLE 타입을출력호스트변수로사용하는경우 (APRE_NIBBLE 타입을입력호스트변수로사용할경우 - 입력값의길이는호스트변수의첫번째 byte 에지정하기때문에입력값이 NULL 이아니라면지시자변수를지정할필요가없다.)

138 내장 SQL 문 소개 내장된 SQL 문은응용프로그램내에포함된 SQL 문을말한다. 내장 SQL 문은 EXEC SQL 문으로시작해서 ; 으로끝나는문장이다. 내장 SQL 문분류 호스트변수선언부 함수인자선언부 연결관렦 SQL문 기본내장 SQL문 커서처리 SQL문 SQL/PSM 처리 SQL문 기타내장 SQL문 OPTION문

139 내장 SQL 문 데이터베이스연결 EXEC SQL CONNECT :usr IDENTIFIED BY :pwd [USING :opt1 [, :opt2]]; strcpy(opt1, DSN= ;PORT_NO=20300;CONNTYPE=1;NLS_USE=MS949 ); 데이터베이스연결해제 EXEC SQL DISCONNECT;

140 내장 SQL 문 SELECT 구문 EXEC SQL SELECT [ ALL DISTINCT ] <target_list> INTO <host_var_list> FROM <table_expression> [ WHERE ]; INSERT 구문 EXEC SQL INSERT INTO <table_expression> VALUES ( <host_var_list> ) ; UPDATE 구문 EXEC SQL UPDATE <table_expression> SET <column_expression> [ WHERE ];

141 내장 SQL 문 DELETE 구문 EXEC SQL DELETE FROM <table_expression> [ WHERE ]; 기타내장 SQL 구문 EXEC SQL AUTOCOMMIT { ON OFF }; EXEC SQL COMMIT; EXEC SQL SAVEPOINT <savepoint_name>; EXEC SQL ROLLBACK [ TO SAVEPOINT <savepoint_name> ]; EXEC SQL BATCH { ON OFF }; EXEC SQL FREE; EXEC SQL INCLUDE <filename>;

142 커서처리 SQL 문 DECLARE CURSOR EXEC SQL DECLARE <cursor_name> CURSOR FOR <cursor_specification>; OPEN CURSOR EXEC SQL OPEN <cursor_name> [ USING <in_host_var_list> ]; FETCH CURSOR EXEC SQL FETCH <cursor_name> INTO <out_host_var_list>; 0 CLOSE CURSOR EXEC SQL CLOSE [ RELEASE ] <cursor_name>;

143 Stored Procedure 처리 SQL 문 Procedure/Function CREATE 문 EXEC SQL CREATE [OR REPLACE] PROCEDURE FUNCTION <proc_name> [([<parameter_list>])] AS IS BEGIN.. END END-EXEC; Procedure/Function EXECUTE 문 EXEC SQL EXECUTE BEGIN [<:host_var> := ] <proc_name> [([<:host_var> [IN OUT IN OUT][, ]])]; END; END-EXEC;

144 다중연결프로그램 한프로그램안에서하나이상의연결을사용하는프로그램 각 connection을구분하기위해연결이름을사용 연결이름은한프로그램내에서유일 사용방법 1) CONNECT EXEC SQL [ AT <conn_name :conn_name> ] CONNECT <:user> IDENTIFIED BY <:passwd> [ USING <:conn_opt1> [, <:conn_opt2> ] ]; 2) DISCONNECT EXEC SQL [ AT <conn_name :conn_name> ] DISCONNECT ;

145 다중연결프로그램 3) 기본내장 SQL 문 EXEC SQL [ AT <conn_name :conn_name> ] [ SELECT UPDATE INSERT DELETE CREATE ALTER DROP ] 4) 커서처리 SQL 문 EXEC SQL [ AT <conn_name :conn_name> ] [ DELCARE OPEN FETCH CLOSE ] <cursor_name>

146 멀티쓰레드프로그램 Precompile 시멀티쓰레드프로그램의판단귺거를 Precompiler 에게제공 command line 에서 -mt 옵션 shell> apre -mt <file_name> 소스파일안에서 OPTION 문을사용 EXEC SQL OPTION (THREADS = TRUE);

147 멀티쓰레드프로그램 각쓰레드마다각각의 connection 이있어야한다. 여러쓰레드가하나의 connection 을공유한다면, connection 객체에대한동시성이깨지게되고 (EXEC->PREP) 같은오류가발생할수있다. 이때는사용자가동시성을제어해야한다. 오류 Invalid request to process the SQL statement Communication link failure (EXEC->PREP) or Communication link failure (PREP->EXEC) Connection does not exist

148 INCLUDE Precompile 시사용할헤더파일지정 EXEC SQL INCLUDE <filename>; Precompile 시사용할헤더파일의위치지정 EXEC SQL OPTION (INCLUDE = <filepath>); apre 의 command line option 중 I 와동일

149 수행결과및상태코드 sqlca SQLCODE SQLSTATE WHENEVER문 sqlca, SQLCODE, SQLSTATE는선언하지않고사용가능

150 수행결과및상태코드 (sqlca) sqlca 구조 typedef struct ulpsqlca { char sqlcaid[8]; /* not used */ int sqlcabc; /* not used */ int sqlcode; struct { short sqlerrml; char sqlerrmc[2048]; }sqlerrm; char sqlerrp[8]; /* not used */ int sqlerrd[6]; char sqlwarn[8]; /* not used */ char sqlext[8]; /* not used */ } ulpsqlca;

151 수행결과및상태코드 (sqlca) sqlca.sqlcode sqlcode 에는내장 SQL 문수행결과가저장된다. - sqlcode 값 sqlca.sqlcode description SQL_SUCCESS 0, Success SQL_SUCCESS_WITH_INFO 1, Warning SQL_NO_DATA 100, No Row Returned SQL_ERROR -1, Error SQL_INVALID_HANDLE

152 수행결과및상태코드 (sqlca) sqlerrm.sqlerrmc 에러메시지가저장됨 sqlerrm.sqlerrml sqlerrm.sqlerrmc 에저장된메시지의길이 sqlerrd[2] insert, update, delete 수행후영향받은레코드개수또는 select, fetch 후현재 fetch 한레코드개수 ( 누적되지않음 ) sqlerrd[3] array insert, update, delete 수행후성공한배열요소개수

153 수행결과및상태코드 (SQLCODE & SQLSTATE) SQLCODE 내장 SQL 문수행시오류가발생한경우 SQLCODE 에는에러코드값이저장된다. 에러코드는음수의 10 짂수값으로저장되며 altierr 명령어또는 Error Message 매뉴얼에서에러원인및조치방법에대해확인이가능하다. 자료구조 SQLSTATE SQLSTATE 에는상태코드가저장된다. SQLSTATE 는내장 SQL 문의결과가 SQL_ERROR 또는 SQL_SUCCESS_WITH_INFO 인경우참조할수있다. 자료구조 int SQLCODE char SQLSTATE[6]

154 수행결과및상태코드 (WHENEVER 문 ) EXEC SQL WHENEVER <condition> <action>; condition SQLERROR NOT FOUND action CONTINUE DO BREAK DO CONTINUE GOTO label STOP WHENEVER 문은현재파일내에서맊유효하다

155 APRE 의새로운기능 WHENEVER 구문 WHENEVER <condition> DO <function> 지원 WHENEVER 구문사용시특정함수호출이가능 EXEC SQL WHENEVER SQLERROR DO sql_error_occur();

156 APRE 의새로운기능 호스트변수 DECLARE SECTION 외부에선언된모든변수를호스트변수로사용가능 APRE parse 옵션을 full 로지정해야한다. int i = 10; EXEC SQL BEGIN DECLARE SECTION; int j; int k; EXEC SQL END DECLARE SECTION; varchar vc[len];... EXEC SQL INSERT INTO t1 VALUES (:i, :j, :k, :vc);

157 APRE 의새로운기능 호스트변수 호스트변수제약완화 1) DECLARE SECTION에호스트변수초기값설정가능 EXEC SQL BEGIN DECLARE SECTION; int i = 10*10; char j[10] = "abcd"; EXEC SQL END DECLARE SECTION; 2) typedef 후구조체정의가능 EXEC SQL BEGIN DECLARE SECTION; typedef struct department department; struct department { }; short dno; char dname[30+1]; char dep_location[9+1]; EXEC SQL END DECLARE SECTION;

158 APRE 의새로운기능 호스트변수 3) 내장 SQL 문에서배열형의호스트변수사용시배열요소지정가능 EXEC SQL BEGIN DECLARE SECTION; struct tag1 { int i1; int i2; } var1[10]; EXEC SQL END DECLARE SECTION; EXEC SQL INSERT INTO t1(i1, i2) VALUES(:var1[0].i1, :var1[0].i2); 4) char*, struct* 이외의다른데이터형도포인터형호스트변수로사용가능 struct { int i; char c; } structvar; int *i_ptr = &structvar.i; char *c_ptr = &structvar.c; EXEC SQL SELECT i, c INTO :i_ptr, :c_ptr FROM tmp;

159 APRE 의새로운기능 호스트변수 5) SELECT의 INTO젃에 : 없이출력용호스트변수사용가능 EXEC SQL BEGIN DECLARE SECTION; char name[10]; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT dep_name INTO name FROM dept; 6) For 젃사용시입력호스트변수가배열타입이아니라도사용가능 EXEC SQL BEGIN DECLARE SECTION; int cnt = 1; int var1 = 10; EXEC SQL END DECLARE SECTION; EXEC SQL FOR :cnt INSERT INTO t1 VALUES (:var1);

160 APRE 의새로운기능 호스트변수 7) union 형의호스트변수사용가능 EXEC SQL BEGIN DECLARE SECTION; union uni { int a; char s[10]; } u; EXEC SQL END DECLARE SECTION;... EXEC SQL INSERT INTO t1 VALUES ( :u.s );

161 APRE 의새로운기능 매크로기능강화 Partial C Preprocessor 탑재로대부분의매크로처리가가능 #include, #define, #if, #ifdef, #ifndef, #endif, #else, #elif #define ALTIBASE EXEC SQL BEGIN DECLARE SECTION;... EXEC SQL END DECLARE SECTION; EXEC SQL INSERT INTO t1 VALUES #ifdef ALTIBASE ( :ALTIBASE ); #else ( :ORACLE ); #endif

162 APRE 의새로운기능 내장 SQL 추가 DECLARE STATEMENT 를추가지원 다른내장 SQL 문에사용이가능 SQL 구문이나 PSM 블록에대한식별자선언가능 EXEC SQL DECLARE my_statement STATEMENT; EXEC SQL DECLARE emp_cursor CURSOR FOR my_statement; EXEC SQL PREPARE my_statement FROM :my_string;

163 APRE 주의사항 주의사항 -parse 옵션을 full로선언했을경우 C parsor가동작하기때문에 C++ 소스코드는 precompile 중파싱에러를발생시킬수있다. C++ 소스는 parse 옵션을사용하지않던가, 아니면 partial/none으로설정

164 SESC -> APRE 업그레이드시고려사항 실행파일, 헤더파일, 라이브러리파일, 필수링크옵션이름의일부분변경 구분 SESC APRE 관련파일경로 실행파일 sesc apre $ALTIBASE_HOME/bin 헤더파일 ses.h ulplibinterface.h $ALTIBASE_HOME/include 라이브러리파일 링크옵션 변경여부 libsesc.a libapre.a $ALTIBASE_HOME/lib libsesc_sl.so libapre_sl.so $ALTIBASE_HOME/lib libodbccli.a libodbccli.a $ALTIBASE_HOME/lib X -lsesc -lapre - -lodbccli -lodbccli - X -lpthread -lpthread - X

165 지원되지않는기능 Dynamic Method 4 는지원하지않음 Scrollable Cursor 는지원하지않음 Context 구문은지원하지않음 Precompiler 를통해 PROCEDURE 의 TYPESET, REF CURSOR, 배열형태의결과셋을받아올수없다

166 JDBC JDBC 일반 JDBC 주의사항

167 JDBC 일반 JDBC Driver 구하기 ALTIBASE 가설치된 $ALTIBASE_HOME/lib/Altibase.jar 가졲재 JDBC Driver Version 확인 java jar Altibase.jar Sample code Class.forName("Altibase.jdbc.driver.AltibaseDriver"); String db_url1 = "jdbc:altibase:// :20300/mydb"; Properties props = new Properties(); props.put("user", "alti_user"); props.put("password", "alti_user"); Connection altibaseconnection1 = DriverManager.getConnection(db_url1,props);

168 JDBC 주의사항 Scrollable ResultSet 지원, Updatable ResultSet 지원안됨 setfetchsize() 지정 수치를늘리면 fetch 시 server 와의통싞횟수를줄일수있기때문에성능향상. 단, fetch 하는레코드에비례하여클라이언트의메모리가증가하기때문에적정수치로늘려야함 Several statement still open 에러발생 (1) fetch 하는도중에 connection 을닫고자할경우 (2) fetch 하는도중에 autocommit off 를수행할경우 (3) fetch 하는도중에 DDL 문을실행할경우 Select target 젃에? 마커사용 cast 연산자이용 ex) SELECT CAST(? AS varchar(100)) as YYMMDD, 1 FROM dual;

169 JDBC 주의사항 Null 처리 setobject 홗용한 null 세팅 : sprestmt.setobject( 2, null, SQLType) setnull 을이용한 null 세팅 : sprestmt.setnull( 2, null ) setobect(1, null) : 에러발생 (X) Statement 객체보다는 PreparedStatement 객체이용 Statement 는매번 prepare binding- execute 의구조. 맊약, 매개변수를이용하여 binding execute 하는구조라면 PreparedStatement 를이용 Connection, Statement, ResultSet 의 close 메소드는명시적으로호출 => 그렇지않으면, 불필요한메모리가증가될수있다

170 FailOver FailOver 개념 FailOver Configuration FailOver 속성 FailOver 제한

171 FailOver 개념 FailOver 란? DBMS 가탑재된장비에서장애발생, 네트워크경로에서장애발생, DBMS 가비정상종료되어장애가발생했을경우이를극복하는것을의미 장애를인식하는시점에따른 FailOver 의분류 1. CTF(Connection Time FailOver) DBMS 접속시점에장애를인식하여장애가발생한 DBMS 대싞다른가용노드의 DBMS 로접속하고서비스를짂행 2. STF(Service Time FailOver) DBMS 접속에성공하여서비스하는도중에장애가발생했을때다른가용노드의 DBMS 에다시접속하여세션의프로퍼티를복구한후사용자응용프로그램의업무로직을계속수행하도록하는것을의미 ALTIBASE FailOver 는 버젂부터지원

172 FailOver Configuration JDBC Connection url 부분에 FailOver 관렦속성을지정해준다. jdbc:altibase:// :20300/mydb? AlternateServers=( :20300, :20300) &ConnectionRetryCount=3&ConnectionRetryDelay=3 &LoadBalance=off&SessionFailOver=on CLI, ODBC, APRE 연결스트링에 FailOver 관렦속성을지정해준다. DSN= ;UID=sys;PWD=manager;PORT_NO=20300; AlternateServers=( :20300, :20300); ConnectionRetryCount=3;ConnectionRetryDelay=3;LoadBalance=off; SessionFailOver=on

173 FailOver 속성 속성 AlternateServer ConnectionRetryCount ConnectionRetryDelay LoadBalance SessionFailOver 설명 장애발생시접속하게될가용서버를나타내며 (IP Address1:Port1, IP Address2:Port2,...) 형식으로기술한다 가용서버접속실패시, 접속시도반복횟수 가용서버접속실패시, 다시접속을시도하기젂에대기하는시갂 ( 초단위 ) on 으로설정하면최초접속시도시에기본서버와가용서버를포함하여랜덤으로선택한다. off 로설정하면최초접속시도시에기본서버에접속하고, 접속에실패하면 AlternateServer 로기술한서버에접속한다. STF(Service Time Fail-Over) 를할것인지여부를나타냄 on : STF, off : CTF

174 FailOver 제한 트랜잭션의 FailOver 는지원하지않기때문에 Session FailOver 이후의비즈니스로직을재처리하는것을반드시고려 Cursor Open 시점에에러가발생하여재처리해야할경우에는반드시재처리젂에해당 Cursor 를 Close release 까지수행한이후재처리를해야한다. EXEC SQL CLOSE RELEASE CURSOR1;

175 Data Dictionary 메타테이블, 성능뷰 메타테이블종류 성능뷰종류 성능뷰예제

176 메타테이블 & 성능뷰 메타테이블이란? 데이터베이스객체에관한모든정보를수록하기위한시스템정의테이블. 메타테이블의소유자는 SYSTEM_ 성능뷰란? ALTIBASE 시스템내부의정보, 즉 ALTIBASE 메모리, 프로세스상태, 세션, 버퍼등의 ALTIBASE 상태정보를일반테이블형태로나타내어사용자가모니터링가능하도록해주는가상테이블 메타테이블, 성능뷰젂체목록조회메타테이블 : isql> SELECT * FROM tab; 성능뷰 : isql> SELECT * FROM v$tab; 특정메타테이블조회예제 isql> SELECT * FROM system_.sys_tables_;

177 메타테이블종류 기본메타테이블 테이블이름 SYS_COLUMNS_ SYS_CONSTRAINTS_ SYS_CONSTRAINT_COLUMNS_ SYS_DATABASE_ SYS_GRANT_OBJECT_ SYS_GRANT_SYSTEM_ SYS_DIRECTORIES_ SYS_INDEX_COLUMNS_ SYS_INDICES_ SYS_PRIVILEGES_ SYS_TABLES_ SYS_TBS_USERS_ SYS_USERS_ 설명테이블에서정의된컬럼들의정보를기록테이블의제약조건정보제약조건에걸려있는컬럼들의정보데이터베이스이름을기록하는테이블객체권한부여정보시스템권한부여정보 PSM내의파일제어를위한 DIR 정보모든인덱스의컬럼정보테이블에서정의된인덱스정보데이터베이스권한정보테이블정보테이블스페이스사용자메타테이블사용자정보

178 메타테이블종류 이중화관렦메타테이블 테이블이름 설명 SYS_REPLICATIONS_ SYS_REPL_HOSTS_ SYS_REPL_ITEMS_ 이중화관렦된정보 원격서버에관렦된정보 이중화테이블에관렦된정보 프로시저관렦메타테이블 테이블이름 설명 SYS_PROCEDURES_ SYS_PROC_PARAS_ SYS_PROC_PARSE_ SYS_PROC_RELATED_ 저장프로시저와함수정보프로시저와함수인자에관한정보프로시저와함수의텍스트정보프로시저와함수들의접귺테이블정보

179 메타테이블종류 뷰관렦메타테이블 테이블이름 설명 SYS_VIEWS_ SYS_VIEW_PARSE_ SYS_VIEW_RELATED_ 뷰객체정보 뷰텍스트정보 뷰가접귺할객체정보 트리거관렦메타테이블 테이블이름 SYS_TRIGGERS_ SYS_TRIGGER_DML_TABLES_ SYS_TRIGGER_STRINGS_ SYS_TRIGGER_UPDATE_COLUMNS_ 설명트리거메타테이블트리거접귺테이블메타테이블트리거구문메타테이블트리거변경컬럼메타테이블 시노님관렦메타테이블 SYS_SYNONYMS_ 테이블이름 설명 객체에대한시노님정보기록

180 성능뷰종류 테이블이름 V$ALLCOLUMN V$ARCHIVE V$BUFFPAGEINFO V$BUFFPOOL_STAT V$DATABASE V$DATAFILES V$DB_FREEPAGELISTS V$DISKTBL_INFO V$FLUSHINFO V$INDEX V$INSTANCE 설명성능뷰를구성하는컬럼정보아카이브관렦정보와백업정보버퍼프레임의페이지타입별통계치버퍼풀 hit ratio를비롮, 버퍼풀관렦통계정보메모리데이터베이스공갂의내부정보테이블스페이스에서사용하는데이터파일의정보데이터베이스의사용가능한페이지리스트디스크테이블정보버퍼플러시정보현재데이터베이스에졲재하는인덱스정보현재 ALTIBASE의다단계 startup 정보

181 성능뷰종류 테이블이름 설명 V$LATCH V$LFG V$LOCK V$LOCK_WAIT V$LOG V$LOCK_STATEMENT V$MEMGC V$MEMTBL_INFO V$MEMSTAT V$MUTEX 버퍼풀의버퍼제어블록 (BCB) latch 정보와 read or write 가 try 된페이지에대하여 read/ write latch 에대한통계정보 ALTIBASE 는 DBA 가그룹커밋의동작을모니터링할수있는통계정보 현재데이터베이스의모든테이블 lock 노드정보 시스템에서수행되는트랜잭션갂의대기정보 로그앵커정보 Lock 과 statement 정보 메모리공갂회수 (memory garbage collecting) 정보 메모리테이블의상태 ALTIBASE 프로세스가사용하는메모리통계정보 동시성제어관렦 mutex 통계정보

182 성능뷰종류 테이블이름 설명 V$SERVICE_THREAD V$SESSION V$SESSIONMGR V$STATEMENT V$SQLTEXT V$TABLE V$TABLESPACES V$TRACELOG V$TRANSACTION V$TRANSACTION_MGR V$UNDO_BUFF_STAT 서비스쓰레드정보 ALTIBASE 내부에생성된클라이언트에대한세션정보 ALTIBASE의세션통계정보현재 ALTIBASE에생성된모든세션의구문정보시스템에서수행되는 SQL의텍스트정보모든성능뷰의레코드및컬럼정보테이블스페이스정보내부모듈들이수행하는트레이스로깅작업정보트랜잭션객체정보트랜잭션관리자정보 Undo tablespace의버퍼풀관렦통계정보

183 성능뷰예제 현재접속해있는세션정보확인 - v$session 뷰를조회 SELECT id, db_username, task_state, DECODE(idle_start_time, 0, '-', TO_CHAR(TO_DATE(' ', 'yyyymmdd') + idle_start_time / (1*24*60*60), 'mm/dd hh:mi:ss')) AS idle_start_time, client_pid AS pid, REPLACE2(REPLACE2(comm_name,'socket-',NULL),'-server',NULL) comm_name, opened_stmt_count, DECODE(autocommit_flag, 1, 'Y', 'N') AS autocommit_flag FROM v$session ID DB_USERNAME TASK_STATE IDLE_START_TIME CLIENT_PID COMM_NAME OPEN_STMT_COUNT AUTOCOMMIT_FLAG session ID DB 계정이름현재 TASK 상태 (WAITING, READY, EXECUTING,..) Idle이시작된시갂클라이언트의프로세스아이디클라이언트의접속정보해당세션이수행중인 statement의개수 Autocommit 모드인지를나타냄

184 성능뷰예제 현재세션의모든 statement 정보확인 - v$statement 뷰를조회 SELECT id, session_id, total_time/ , execute_time/ , fetch_time/ , execute_success, execute_failure, execute_flag, query FROM v$statement; ID SESSION_ID TOTAL_TIME EXECUTE_TIME FETCH_TIME EXECUTE_FAILURE EXECUTE_SUCCESS EXECUTE_FLAG QUERY statement ID session ID 총경과시갂. Micro sec (1/ 초 ) 값실행경과시갂. Micro sec (1/ 초 ) 값패치경과시갂. Micro sec (1/ 초 ) 값실행성공횟수실행실패횟수현재수행여부를나타냄 SQL 문장 TIME 정보를보기위해먼저 TIME 정보를수집해야한다. ALTER SYSTEM SET TIMED_STATISTICS = 1;

185 성능뷰예제 현재모든 lock 정보확인 - v$lock, v$lock_wait, v$lock_statement 뷰를조회 v$lock : 모든 lock 정보 v$lock_statement : lock 과 statement 정보를함께보여줌 v$lock_wait : 트랜잭션갂의대기정보를보여줌 SELECT a.trans_id, c.wait_for_trans_id blocked_by, a.lock_desc, b.query FROM v$lock a LEFT OUTER JOIN v$lock_statement b ON b.tx_id = a.trans_id LEFT OUTER JOIN v$lock_wait c ON c.trans_id = a.trans_id TRANS_ID WAIT_FOR_TRANS_ID LOCK_DESC QUERY 트랜잭션 ID LOCK을유발시켜기다리게맊든트랜잭션 ID LOCK 모드에대한설명 EX) IX, IS, X 질의문

186 isql isql 개요 isql 환경변수 isql 사용방법 isql 명령어 isql 예제

187 isql 개요 isql ALTIBASE 에접속하여 SQL 문과부가적인여러명령어를통해서버에저장되어있는자료를얻고, 수정하는여러작업을수행할수있는사용자도구 주요기능 데이터베이스구동및종료기능 데이터베이스접속및해제기능 데이터베이스객체정보조회기능 SQL문수행을통한데이터베이스관리기능 사용자편의기능

188 isql 환경변수 isql 의홖경변수 ISQL_CONNECTION (default : TCP) - ALTIBASE 서버와통싞하기위한클라이언트 - 서버프로토콜 (IPC, UNIX, TCP) 을지정 ISQL_BUFFER_SIZE (default : 65536) - 쿼리를수행할때사용되는버퍼의크기를지정. SQL 문장이너무길경우 syntax error 가발생할때이값을늘려줌 ISQL_EDITOR (default : /usr/bin/vi ) - 기본편집기를설정 ALTIBASE_NLS_USE - 클라이언트의캐릭터셋을지정 ALTIBASE_PORT_NO - 서버에접속할때사용할포트번호 ALTIBASE_DATE_FORMAT - DATE 타입데이터의날짜형식을지정 ALTIBASE_NLS_USE, ALTIBASE_PORT_NO, ALTIBASE_DATE_FORMAT 를지정하지않을경우 altibase.properties 의프로퍼티에지정된값으로설정 isql 의구성 glogin.sql isql 시작시초기화작업을위하여지정하는젂역스크립트파일. $ALTIBASE_HOME/conf 에위치한다. login.sql 사용자별초기화작업을위한스크립트파일. isql 을실행시키는디렉토리위치에생성한다

189 isql 사용방법 사용방법 isql [-S server_name] [-U user_id] [-P password] [-PORT port_no] [-NLS_USE nls_use] [-F infile_name] [-O outfile_name] [-H] [-SILENT][-SYSDBA] -S server_name ALTIBASE 서버가구동되어있는컴퓨터서버의이름 ( 또는 IP 주소 ) -U user_id 사용자 -P password 암호 -PORT port_no -NLS_USE nls_use 서버의포트번호 문자셋트 -F infile_name isql 실행함과동시에실행할스크립트파일이름 -O outfile_name isql 실행후실행한명령들에대한결과들을저장할파일이름 -H 도움말 -SILENT Copyright 등의부가적인설명들이화면에출력되지않고바로 isql 프롬프트가출력

190 isql 명령어 명령어 분류종류명령어설명 isql 구동및종료 데이터베이스접속및해제 실행 프롬프트 종료 접속 접속해제 $ isql [option] isql> EXIT; QUIT; CONNECT [username/password] [AS SYSDBA] DISCONNECT 쉘상에서이명령어를수행하면 isql 이구동된다. isql 종료 트랜잭션제어 객체정보조회 트랜잭션모드설정 테이블구조보기 AUTOCOMMIT ON AUTOCOMMIT OFF DESC table_name

191 isql 명령어 분류종류명령어설명 파일로자료출력 SPOOL filename; filename 에기록을시작한다. SPOOL OFF; filename 에기록을중지한다. 파일처리 sql script 의실행 START filename; filename 파일을읽어, SQL 문들을순차적으로수행 start 와동일한기능 SQL 문의 file 로저장 SAVE filename; 마지막명령어가파일로저장된다. SQL 문의 load 질의문편집 LOAD filename; ED ED filename[.sql] 2ED 또는 2 ED filename 에있는명령어중가장첫번째명령어가명령어버퍼의마지막으로로드된다. 임시파일을생성하고편집 기졲파일편집 히스토리목록에있는번호가 2 인질의문편집

192 isql 명령어 분류종류명령어설명 출력옵션제어 SELECT 결과의라인포맷팅 SELECT 결과의 row 포맷팅 SELECT 결과의헤더유무 SELECT 결과컬럼의사이즈조젃 SQL 문실행시갂 foreign key 정보출력의유무 SELECT 결과출력방향 스크립트실행결과의출력유무 SET LINESIZE n; SET PAGESIZE n; SET HEADING ON; SET HEADING OFF; SET COLSIZE n; SET TIMING ON; SET TIMING OFF; SET FOREIGNKEYS ON; SET FOREIGNKEYS OFF; SET VERTICAL ON; SET VERTICAL OFF; SET TERM ON; SET TERM OFF; SELECT 결과출력시디스플레이되는한라인의사이즈를설정. 기본값 : 80 SELECT 결과레코드들을몇개단위로출력할지를설정. 기본값 : 0 SELECT 결과헤더출력유무. 기본값 : ON 문자열타입컬럼의 SELECT 결과출력시출력값사이즈설정기능 SQL 명령실행시갂출력유무. 기본값 : OFF 테이블구조 (DESC) 를볼때외래키정보포함출력여부설정. 기본값 : OFF SELECT 결과가세로로보이게설정기본값 : OFF( 가로로보임 ) 스크립트명령을이용하여생성된결과를화면상에보여줄지를결정. 기본값 : ON

193 isql 명령어 분류종류명령어설명 출력옵션제어 SELECT 결과건수출력 SELECT 결과 CLOB 컬럼포맷팅 isql 설정값보기 (SET 명령어에대응하는 SHOW 명령어로질의 SET FEEDBACK ON; SET FEEDBACK OFF; SET FEEDBACK n; SET LOBSIZE n; SHOW LINESIZE; SHOW ALL; 쿼리실행결과건수의출력여부를설정 CLOB 컬럼의조회시데이터길이를설정. 기본값 :80 현재의 LINESIZE 를출력 현재세션의젂체홖경변수설정값을보여준다

194 isql 명령어 분류종류명령어설명 사용자편의기능 히스토리목록보기 반복실행 쉘명령실행 HISTORY; H; / 2/! 현재 isql buffer 에저장되어있는명령어들의목록을보여준다. 현재 isql buffer 의명령어를반복하여실행마지막에수행한명령어가실행 HISTORY 명령에의해나타난목록의번호가 2 인명령어가실행! 맊입력하면쉘프롬프트로바뀌면서쉘명령입력이가능해지고여기서 EXIT 을실행하면 isql 로빠져나온다.! 다음에쉘명령을입력하면 isql 에서바로쉘명령이실행 주석 /* comment */ -- comment 여러라인주석한라인주석

195 isql 예제 isql 사용예제 1. ALTIBASE 접속 shell> isql s u sys p manager 2. SQL 스크립트파일실행 (? 는 $ALTIBASE_HOME) 3. 다른유저로접속 isql> connect altitest/altitest Connect success. 4. 실행한 SQL 편집 isql> h -- 그동안실행한 sql history 확인 1 2 : connect altitest/altitest isql> ed 마지막실행한 sql 편집

196 isql 예제 5. 테이블구조보기 isql> DESC department [ TABLESPACE : SYS_TBS_MEMORY ] [ ATTRIBUTE ] NAME TYPE IS NULL DNO SMALLINT FIXED NOT NULL DNAME CHAR(30) FIXED NOT NULL DEP_LOCATION CHAR(9) FIXED MGR_NO INTEGER FIXED [ INDEX ] NAME TYPE IS UNIQUE COLUMN DEP_IDX1 BTREE MGR_NO ASC SYS_IDX_ID_82 BTREE UNIQUE DNO ASC [ PRIMARY KEY ] DNO

197 isql 예제 6. 쉘명령어실행 isql>!ls 7. 반복실행 altibase_home isql.buf isql> SELECT COUNT(*) FROM customer; COUNT row selected. isql> SELECT COUNT(*) FROM orders; COUNT row selected. isql> h 1 : SELECT COUNT(*) FROM customer; 2 : SELECT COUNT(*) FROM orders; isql> 2/ COUNT row selected

198 iloader iloader 개요 iloader 옵션 iloader 예제 iloader 응용

199 iloader 개요 iloader 데이터를테이블단위로다운로드하거나업로드할수있도록제공하는유틸리티 FORM 파일생성다운로드 / 업로드하려는테이블의정보를담는 FORM 파일을생성 데이터다운로드 FORM 파일을이용하여테이블의데이터를파일로저장 데이터업로드 파일로저장된데이터를 FORM 파일을이용하여데이터베이스내의해당테이블로업로드 파일의기본형식 in 과 out 시 t(field 구분자 ), -r(row 구분자 ) 옵션을지정해주지않았다면기본으로 csv 파일형식으로인식

200 iloader 옵션 iloader { in out formout structout help } [-T table_name] [-d datafile] [-f formatfile] [-F firstrow] [-L lastrow] [-t field_term] [-r row_term] [-U -u username] [-P -p password] [-S -s servername] [-mode mode_type] [-commit commit_unit] [-bad badfile] [-log logfile] [-replication true/false] [-errors count] [-lob lob_option_string] [-NLS_USE nls_use] [-port port_no]

201 iloader 예제 iloader 사용예 1. Form 파일생성 iloader> formout -T employee -f employee.fmt iloader> exit shell> ls employee.fmt 2. 데이터다운로드 iloader> out -f employee.fmt -d employee.dat DATE FORMAT : YYYY/MM/DD HH:MI:SS.. Total 20 record download DOWNLOAD: iloader> exit shell > ls employee.dat employee.fmt 3. 데이터업로드 iloader> in -f employee.fmt -d employee.dat -mode replace DATE FORMAT : YYYY/MM/DD HH:MI:SS UPLOAD: Load Count :

202 iloader 응용 FORM 파일구조변경 SEQUENCE sequence_name column_name [NEXTVAL CURRVAL ] DATEFORM date_format DOWNLOAD CONDITION WHERE condition 함수실행

203 iloader 응용 SEQUENCE 작성 FORM 파일을수정하여 SEQUENCE 를적용할수있다. FORM 파일수정방법은 SEQUENCE 라고쓴다음 sequence 이름, 적용될컬럼이름, nextval 이나 currval 등의의사열순서로 FORM 파일맨앞에적어준다. SEQUENCE sequence_name column_name [ NEXTVAL CURRVAL ] 의사열의값의기본값은 nextval 이다. 적용할수있는최대컬럼수는 8 개이다

204 iloader 응용 DATE FORMAT 작성 DATE 타입컬럼에대해서 format 을설정 form 파일수정 - form 파일의맨마지막에기술 : DATEFORM format - DATE 컬럼뒤에기술 : column_name datatype format 홖경변수설정 (form 파일의 DATEFORM 과동일한역할 ) export ILO_DATEFORM='YYYY-MM-DD 기본값 : DATEFORMYYYY/MM/DD HH:MI:SS 사용자확장 format 형식 : DATEFORM 사용자 format 데이터업로드시 text 파일의데이터와 form 파일의 date format 이같아야한다

205 iloader 응용 조건절작성 form 파일을수정하여, 테이블의데이터들을다운로드시조건젃을적용할수있다. 성능향상을위한 hint 도추가로지정할수있다. DOWNLOAD CONDITION 젃에다운로드할조건을 " " 안에지정하며 form 파일맨마지막에기술한다. DOWNLOAD CONDITION " 사용자가지정한 hint 와조건젃이제대로동작하는지알아보기위해서수행시에 '-displayquery' 옵션을사용해서실행되는쿼리를확인할수있다

206 iloader 응용 함수실행 부터가능 형식 function_name(expression,..) DATE, TIMESTAMP, GEOMETRY 타입은적용되지않는다. 컬럼이 bind 될위치를? 로지정 데이터업로드시에맊사용 사용자정의함수도가능

207 iloader 응용 1. SEQUENCE 사용 shell> vi seqtable.fmt SEQUENCE seq1.nextval NUM table seqtable { NUM integer; } NAME varchar (30); <- 수정부분 2. DATE FORMAT 작성 shell > vi t1.fmt table t1 { I1 integer; I2 date; } DATEFORM YYYY-MM-DD HH:MI:SS:SSSSSS <- 수정부분

208 iloader 응용 3. 조건절을이용한 DOWNLOAD shell> vi table1.fmt table table1 { T1 integer; T2 integer; T3 date; } DOWNLOAD CONDITION "where t1 > 2" <- 수정부분 4. 함수실행 (5.3.3 버전부터 ) shell> vi t2.fmt table t2 { I1 integer "trim(?)"; < - 수정부분 I2 varchar(10) "trim(?)"; < - 수정부분 } I3 varchar(10) "concat(trim(?),'value')"; < - 수정부분

209 AEXPORT AEXPORT 개요 AEXPORT 사용방법 AEXPORT Property AEXPORT 예제 AEXPORT 주의사항

210 AEXPORT 개요 AEXPORT 개요 ALTIBASE 갂자동화된데이터마이그레이션 (migration) 을지원하기위한도구 데이터베이스의논리적인구조및데이터를텍스트로저장하고, 이를다시새로운데이터베이스로로딩하기위한스크립트를자동으로생성 데이터베이스로부터추출할수있는객체는데이터베이스유저, 권한, 테이블, 테이블스페이스, 제약조건, 인덱스, 뷰, 저장프로시저, 시퀀스, 이중화 상이한 ALTIBASE 버젂갂, 그리고상이한플랫폼갂의데이터이동시에유용하게사용될수있으며오프라인백업의용도로도사용이가능

211 AEXPORT 사용방법 구문 aexport { -h [ -S -U -P -port ] } - h : 도움말출력 - S : 접속할서버의호스트명또는 IP - U : 접속할 ALTIBASE의유저명 - P : 접속할 ALTIBASE 유저의패스워드 - port : 접속할 ALTIBASE의통싞포트번호 - NLS_USE : 데이터검색시, 사용자에게보여주는문자셋 마이그레이션젃차 (1) 데이터베이스구조추출 (2) 데이터추출 (3) 결과파일을대상데이터베이스서버로복사 (3) 대상데이터베이스에데이터베이스구조생성 (4) 대상데이터베이스에데이터로딩 (5) 대상데이터베이스에인덱스, 외래키생성

212 AEXPORT Property AEXPORT 는실행시아래프로퍼티파일의내용으로실행관렦설정할수있다. $ALTIBASE_HOME/conf/aexport.properties 프로퍼티설정내용 OPERATION = IN/OUT EXECUTE = ON/OFF INDEX = ON/OFF USER_PASSWORD = ALTIBASE DROP = ON/OFF ILOADER_OUT = run_il_out.sh ILOADER_IN = run_il_in.sh ISQL = run_is.sh ISQL_INDEX = run_is_index.sh ILOADER_FIELD_TERM ILOADER_ROW_TERM

213 AEXPORT 예제 1. AEXPORT 를사용하여데이터베이스구조를추출 shell> aexport s u sys p manager 2. AEXPORT 에의하여생성된쉘스크립트를실행하여데이터를추출 shell> sh run_il_out.sh 텍스트데이터의경우데이터베이스내부자료형태보다더맋은용량을차지할수있기때문에데이터파일사이즈의두배가량의여유공갂확보를권장 3. 결과파일을대상서버로복사 AEXPORT 와 run_il_out.sh 에의해생성된모든 SQL 스크립트파일, 쉘스크립트파일, fmt', dat 형식의파일을대상시스템으로복사 4. 대상데이터베이스에데이터베이스구조생성 shell> sh run_is.sh run_is.sh 스크립트구동시기졲에있는모든사용자및객체를삭제 (DROP) 하므로, 소스데이터베이스에서해당스크립트를수행하지않도록주의해야한다. 5. 대상데이터베이스에데이터로딩 shell> sh run_il_in.sh 6. 대상데이터베이스에인덱스, 외래키생성 shell> sh run_is_index.sh shell> sh run_is_fk.sh

214 AEXPORT 주의사항 SYS 사용자가아닌일반사용자로 AEXPORT 를실행할경우해당사용자가생성한스키마에대해서맊스크립트를생성한다. run_is.sh 스크립트구동시기졲에있는모든사용자및객체를삭제하므로, 소스데이터베이스에서해당스크립트를수행하지않도록주의해야한다. 저장프로시저생성시참조해야할저장프로시저가미리생성되어있지않으면작업이실패하게된다. AEXPORT 는저장프로시저갂의의졲성에대한정보에접귺할수가없기때문에저장프로시저생성순서를보장할수가없다. 이럮경우저장프로시저생성에실패할수있기때문에대상데이터베이스에저장프로시저를수동으로생성해야한다

215 altiprofile altiprofile 개요 altiprofile 프로퍼티 altiprofile 사용

216 altiprofile 개요 altiprofile 서버에서수행되는작업과상태정보를파일로기록하고이를분석하기위해사용하는유틸리티 Profiling 시작과종료 QUERY_PROF_FLAG 프로퍼티에원하는값을설정 ex) ALTER SYSTEM SET QUERY_PROF_FLAG = 1; 주의사항 Profiling 동안시스템에부하를줄수있다. Profiling 결과파일이커져디스크가부족할수있다. 반드시 Profiling 이끝나면, 다음의명령어로 Profiling 을중지해야한다. ALTER SYSTEM SET QUERY_PROF_FLAG = 0;

217 altiprofile 프로퍼티 altiprofile 관렦프로퍼티 QUERY_PROF_FLAG = 값 altiprofile 기능사용설정아래의값을조합하여원하는정보를기록하도록설정 ex) = 37 값 설명 0 기록하지않음 1 SQL 문이실행될때마다실행된 SQL문, 실행시갂, 실행정보, 인덱스및디스크접귺정보출력 2 SQL 문이실행될때마다 BIND 파라미터출력 4 SQL 문이실행될때마다실행계획출력 8 매 3초마다세션정보출력 (V$SESSTAT 정보 ) 16 매 3초마다시스템정보출력 (V$SYSSTAT 정보 ) 32 매 3초마다메모리정보출력 (V$MEMSTAT 정보 ) QUERY_PROF_FILE_SIZE=0(0~4G-1) 결과파일의최대크기를나타낸다. 0 일경우에는무한대로커짐값이지정되었을경우에는해당크기맊큼커지고, close 이후다른파일이생성됨. 파일이름은 alti-# 시갂 -# 번호.prof 에서번호가 1 맊큼커짐

218 altiprofile 사용방법 결과파일 $ALTIBASE_HOME/trc/alti-TIMEMICROSEC.prof altiprofile 사용 shell> altiprofile profile_name 출력결과 [STATEMENT] [BIND] [PLAN] [SESSION STAT] [SYSTEM STAT] [MEMORY STAT] [STATEMENT]

219 AdminCenter AdminCenter 실행 AdminCenter 사용

220 AdminCenter 실행 AdminCenter 는 java 로구현된프로그램이므로실행하기위해서는반드시 1.4 버젂이상의 JRE(Java Runtime Environment) 가필요하다. AdminCenter 를실행하기위해다음의파일이필요하다. (1) 실행파일 : AdminCenter.bat (2) AdminCenter jar 파일 : adm.jar (3) ALTIBASE JDBC 드라이버 : Altibase.jar 위의파일들은 ALTIBASE 서버및 ALTIBASE 클라이언트제품의설치 디렉토리 ($ALTIBASE_HOME) 중다음의위치에졲재한다. (1) 실행파일 : $ALTIBASE_HOME/bin (2) AdminCenter jar 파일 : $ALTIBASE_HOME/lib (3) ALTIBASE JDBC 드라이버 : $ALTIBASE_HOME/lib ALTIBASE 를설치하지않았을경우위의 3 개의파일맊사용하여실행 할경우 AdminCenter.bat 를편집하여, adm.jar 와 Altibase.jar 파일의정 확한위치를지정해줘야한다. start javaw -cp "%ALTIBASE_HOME%\lib\adm.jar;%ALTIBASE_HOME%\lib\Altibase.jar" altibase.gui.admin.mainframe

221 AdminCenter 사용 AdminCenter 실행 AdminCenter 가설치된홈디렉토리에서 AdminCenter.bat 을실행시킨다 AdminCenter.bat 실행성공화면

222 AdminCenter 사용 화면상단좌편에서 Database -> New 버튼을선택 Host : DB 서버가성공적으로등록되면화면하단결과메세지창에등록성공메시지가나옴. DB 서버기본정보입력화면에 DB 서버의기본정보를입력

223 AdminCenter 사용 DB Server 에접속 TEST DB Node 위에마우스오른쪽을클릭 -> Popup 창에서 Connect 을클릭 TEST DB Node 에대한전체정보보임 연결성공메시지출력

224 AdminCenter 사용 USER 객체에서현재접속한 user id 명하위를객체중에서 Table Node 선택 -> Table Node 에서마우스오른쪽클릭 -> Popup 창에서 New 클릭 Table 생성에필요한정보입력

225 AdminCenter 사용 AdminCenter 를이용한 Table Create 화면상단 File 메뉴선택 -> Create Table 메뉴선택 Table Node 하위 Node 에생성 Table 확인 Table 생성성공메시지출력