* Cursor

- a pointer to private memory area (= context area)

- 모든 SQL 문은 커서를 생성



  * context area

- SQL 처리전 필요한 정보 저장

- SQL 처리후 결과 정보 저장



  * Implicit Cursor (= SQL cursor)

- PL/SQL 블록내에서 SQL 문이 수행될 때마다 자동적으로 생성됨

- SQL 문이 수행될 때 기존의 Implicit Cursor를 대체함

- Oracle Server가 관리하므로 직접 조작 불가능

- 대신 attribute를 이용해 상태를 확인 가능


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 5-3: Using PL/SQL Control Structures  (0) 2012.04.24
15일차 # 4-21: SQL Cursor Attributes  (0) 2012.04.24
15일차 # 4-17: Merge (Upsert)  (0) 2012.04.24
15일차 # 4-15: Update in PL/SQL  (0) 2012.04.24
15일차 # 4-12: Name Precedence  (0) 2012.04.24

   - deterministic statement: can NOT update the same row multiple times



drop table t1 purge;

drop table t2 purge;


create table t1

as select empno, ename, job, sal from emp

  where rownum <= 8;


create table t2

as select empno, ename, job, sal from emp;


select * from t1;

select * from t2;


update t2 set sal = sal * 1.3 where rownum <= 8;

commit;

select * from t2;

   ------------------------------

begin

 merge into t1

 using t2

 on (t1.empno = t2.empno)

 when matched then

   update set t1.sal = t2.sal

                     , t1.job = t2.job

 when not matched then

   insert (t1.empno, t1.ename, t1.sal)

   values (t2.empno, t2.ename, t2.sal*1.1);

end;

/

   ------------------------------


select * from t1;


   ------------------------------


* 10g부터는 when matched then delete도 가능

'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 4-21: SQL Cursor Attributes  (0) 2012.04.24
15일차 # 4-19: SQL Cursor = Implicit Cursor  (0) 2012.04.24
15일차 # 4-15: Update in PL/SQL  (0) 2012.04.24
15일차 # 4-12: Name Precedence  (0) 2012.04.24
15일차 # 4-5, 7: SELECT  (0) 2012.04.24

        drop table e1 purge;

create table e1 as select * from emp;

   ------------------------------


create or replace procedure e1_sal_update_proc (no e1.empno%type)

is

 v_ename   e1.ename%type;

 v_sal_inc number := 100;

begin

 update e1

 set sal = sal + v_sal_inc

 where empno = no;


 if sql%notfound then

   p('급여를 올려줄 사원 '||to_char(no)||'는 존재하지 않습니다.');

 end if;


end;

/

   ------------------------------


select empno, ename, sal from e1 where empno = 7788;

exec e1_sal_update_proc(7788)

select empno, ename, sal from e1 where empno = 7788;


exec e1_sal_update_proc(1234)


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 4-19: SQL Cursor = Implicit Cursor  (0) 2012.04.24
15일차 # 4-17: Merge (Upsert)  (0) 2012.04.24
15일차 # 4-12: Name Precedence  (0) 2012.04.24
15일차 # 4-5, 7: SELECT  (0) 2012.04.24
15일차 # 4-3: SQL Statements in PL/SQL  (0) 2012.04.24

  * No Possible Ambiguity: SELECT 절, INTO 절


  * Possible Ambiuity: WHERE 절

- 이름이 같다면 ==> column name > variable|formal parameter name > table name


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 4-17: Merge (Upsert)  (0) 2012.04.24
15일차 # 4-15: Update in PL/SQL  (0) 2012.04.24
15일차 # 4-5, 7: SELECT  (0) 2012.04.24
15일차 # 4-3: SQL Statements in PL/SQL  (0) 2012.04.24
15일차 # 3-20: Make code maintenance easier  (0) 2012.04.24

  - INTO 절 + 변수 (반드시)


  - 반드시 결과 하나만 리턴

: 반환되는 결과가 없거나 두 개 이상이면 에러 발생

 --> 예외(exception): NO_DATA_FOUND, TOO_MANY_ROWS


==> 극복하려면 1) cursor

      2) loop : basic, while, for

          + composite data type (associative array (INDEX BY table),

                                                 nested table,

                                                 varray)

3) 예외처리


declare

 v_ename varchar2(30);

 v_sal   number;

begin

 select ename, sal into v_ename, v_sal

 from emp

 where empno = 7788;


 p(v_ename || ' 사원의 급여는 ' || v_sal || '입니다.');

end;

/

   ------------------------------


declare

 v_ename varchar2(30);

 v_sal   number;

begin

 select ename, sal into v_ename, v_sal

 from emp

 where empno = 4456;


 p(v_ename || ' 사원의 급여는 ' || v_sal || '입니다.');

end;

/

--> ORA-01403: 데이터를 찾을 수 없습니다. (NO_DATA_FOUND)

   ------------------------------


declare

 v_ename varchar2(30);

 v_sal   number;

begin

 select ename, sal into v_ename, v_sal

 from emp;


 p(v_ename || ' 사원의 급여는 ' || v_sal || '입니다.');

end;

/

--> ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다 (TOO_MANY_ROWS)


  * PL/SQL Static (Embedded) SQL: PL/SQL 내에서 사용 가능

- DML, TCL


  * PL/SQL Dynamic SQL: PL/SQL 내에서 직접 사용 불가능

- DDL, DCL

- 간접 사용: 1) EXECUTE IMMEDIATE '...'

    2) DMBS_SQL package ==> http://goo.gl/yksD5


  * Binding

- Using DDL and Dynamic SQL ==> http://goo.gl/D7TyW

- 1) Resolve references to Oracle objects

 2) Allocate storage Memory

 3) Assign storage address to program variables


- binding at compile time vs. binding at run time

 early/static binding    vs. late/dynamic binding

 efficiency              vs. flexibility



  * PL/SQL compile


---------------

|    PARSE    |

---------------

    ↓ ↓

---------------

|    BIND     |   <== bind variables

---------------

    ↓ ↓

---------------

|   EXECUTE   |

---------------

    ↓ ↓

---------------

|    FETCH    |   <== select statement

---------------


- Static SQL  : Parse + Bind ==> Compile-Time

               Execute + Fetch ==> Run-Time


  - Dynamic SQL : All Phases ==> Run-Time



   ==============================



drop table t1 purge;

create table t1 (no number);

   ------------------------------


create or replace procedure dynamic_sql is

 v_cnt number;


begin

 execute immediate 'drop table t1 purge';

 execute immediate 'create table t1 (no number)'; --> ORA-01031: 권한이 불충분합니다

--  : CREATE ANY TABLE 권한이 없기 때문

 for i in 1..100 loop

   insert into t1 values (i);

 end loop;


 commit;


 select count(*) into v_cnt

 from t1;


 p(v_cnt);

end;

/

   ------------------------------


exec dynamic_sql

--> ORA-01031: 권한이 불충분합니다


   ------------------------------

  <해결방법>: DBA가 CREATE ANY TABLE 권한을 사용자에게 부여

conn system/????@211.183.3.52:1521/orcl

show user


grant create any table to top01, top02, top03, top04, top05

, top06, top07, top08, top09, top10

, top11, top12, top20;


conn top??/top??@211.183.3.52:1521/orcl


   ------------------------------


set serveroutput on


exec dynamic_sql

PL/SQL 처리가 정상적으로 완료되었습니다.


select count(*) from t1;




  * Multi-Tx in One Block, Multi-Block in One Tx


set serveroutput on

   ------------------------------


declare

 v_cnt     number;

 v_sum_no  number;


begin

 select count(*) into v_cnt

 from t1;


 p(v_cnt);


 begin

   insert into t1 values (101);

   select sum(no) into v_sum_no

   from t1;

 end;


 p(v_cnt);

 p(v_sum_no);


 begin

   begin

     v_sum_no := v_sum_no + v_cnt;

   end;

   commit;


   begin

     select count(*) into v_cnt

     from t1;

     p(v_cnt);

   end;

 end;


end;

/



  문제) 이름을 저장하는 T_NAME 테이블을 생성하고 자신의 이름을 INSERT 하는

        dynamic sql을 포함하는 프로시저를 작성하여 실행하고

select 문을 이용하여 확인해 보세요.



create table t_name(no number);


create or replace procedure create_t_name

is

begin

 -- 테이블 생성

 execute immediate 'drop table t_name purge';

 execute immediate 'create table t_name (irum varchar2(10))';


 -- 이름 삽입

 insert into t_name values ('이순신');

end;

/


select * from t_name;


  1) with comments

  2) case convention

  3) naming convention

  4) indenting


  * 논리 연산자

  * 산술 연산자 ┼> SQL과 동일

  * 연결 연산자 ┤ 

  * 괄호


  * 지수 연산자: ** , SQL엔 없음


   <<outer>>

DECLARE

 sal NUMBER(7,2) := 60000;

 comm NUMBER(7,2) := sal * 0.20;

 message VARCHAR2(255) := ' eligible for commission';

BEGIN

DECLARE

 sal NUMBER(7,2) := 50000;

 comm NUMBER(7,2) := 0;

 total_comp NUMBER(7,2) := sal + comm;

BEGIN

 message := 'CLERK not'||message;

 outer.comm := sal * 0.30;


 p(message); --> 1. CLERK not eligible for commission

 p(comm); --> 2. 0

 p(outer.comm); --> 3. 15000

END;


 message := 'SALESMAN and '||message;


         /*

          p(total_comm); --> 4. 에러, ORA-06550

         */


 p(comm); --> 5. 15000

 p(message); --> 6. SALESMAN and CLERK not eligible for commission

END;

/

  * Scope: Lifetime <= 변수는 선언된 블럭 내 존재하고 블럭을 빠져나가면 소멸


  * Visibility: Accessibility without help



<<amu>>      --> qualifier (한정어)

DECLARE

 father_name VARCHAR2(20) := 'Patrick';

 date_of_birth DATE := '20-Apr-1972';

BEGIN

DECLARE

 child_name VARCHAR2(20) :='Mike';

 date_of_birth DATE :='12-Dec-2002';


BEGIN



 DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name);

 DBMS_OUTPUT.PUT_LINE('Date of Birth:  '||amu.date_of_birth);

 DBMS_OUTPUT.PUT_LINE('Child''s Name:  '||child_name);

 DBMS_OUTPUT.PUT_LINE('Date of Birth:  '||date_of_birth);

END;



DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);

END;

/



- scope at ①: amu.father_name, amu.date_of_birth, chid_name, date_of_birth

  ②: amu.fatehr_name, amu_date_of_birth


- visibility at ①: amu.father_name, amu.date_of_birth, chid_name, date_of_birth

②: amu.father_name, amu.date_of_birth



  * scope를 벗어나면 변수는 메모리에서 제거됨

       declare


 procedure p1

 is

 begin

   null;

 end;


begin

 null;


 begin

   null;

   p1;

 end;


exception

 when others then


   begin

     null;

     p1;

   end;


end;

/

  * Implicit conversions

- characters <-> numbers

- characters <-> date


  * Explicit conversions


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 3-14: Scope and Visibility  (0) 2012.04.24
15일차 # 3-12: Nested Blocks  (0) 2012.04.24
15일차 # 3-7: SQL Functions in PL/SQL  (0) 2012.04.24
15일차 # 3-5: Guidelines  (0) 2012.04.24
15일차 # 3-3: Lexical Unit (어휘단위)  (0) 2012.04.24

  * PL/SQL 문에서 직접 사용할 수 있는 SQL 함수

    - 교재 참조



  * PL/SQL 문에서 직접 사용할 수 없는 SQL 함수

    1) DECODE

    2) Group Functions


declare

 v_sum_sal number;

 v_ret number;

begin

 v_sum_sal = sum(emp.sal); --> 에러: 그룹 함수 사용 불가

 v_ret = decode(v_sum_sal, 1000, 0, 1); --> 에러: DECODE 함수 사용 불가

end;

/

  

  * Character Literals

  * Number Literals

- simple value notation : 200,000 -32.5 0.002

- scientific notation : 2.0E+005 -3.25E+001 2.0E-003


select 200000d, -32.5f, 0.002f from dual;


  



  * 구분자: Delimiters (simple and compound symbols)

  * 식별자: Identifiers, which include reserved words

  * 리터럴: Literals

  * 주  석: Comments

+ Recent posts