Bohemian life

  - 바인드 변수 자동 출력

        declare

 v_ename emp.ename%type := 'A';

begin

 select ename into v_ename

 from emp

 where empno = &sv_empno;

 p(v_ename);

end;

/

Enter value for sv_empno: 7499

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

create or replace procedure p1 (a emp.empno%type)

is

 v_ename emp.ename%type default 'A';

begin

 select ename into v_ename

 from emp

 where empno = a;

 p(v_ename);

end;

/

show errors

exec p1(7499)

exec p1(7900)

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

create or replace procedure p2 (a emp.empno%type)

is

 emp_rec emp%rowtype;

begin

 select * into emp_rec

 from emp

 where empno = a;

 -- p(emp_rec.ename||' '||to_char(emp_rec.sal));

 p('Ename : '||emp_rec.ename);

 p('Sal   : '||to_char(emp_rec.sal));

end;

/

show errors

exec p2(7499)

exec p2(7900)

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

declare

 v_sal emp.sal%type;

 v_ret v_sal%type; --> 변수뒤에 %type 사용

begin

 select sal into v_sal

 from emp

 where empno = 7788;

 v_ret = v_sal + 10;

 p(v_sal);

 p(v_ret);

end;

/

  * %TYPE

    : 컬럼이나 변수의 정확한 데이터 타입을 구함

    : 변수 선언시 컬럼 또는 변수 이름 바로 뒤에 붙여 사용

  cf.) %ROWTYPE

  * %TYPE 장점

    1) 데이터 타입 불일치에 의한 에러 없음

    2) 데이터 타입에 대한 하드 코딩 불필요

    3) 컬럼 정의가 변해도 변수 선언 수정 불필요

  * bind variable vs. variable

ed type01_a.sql

declare

 v_ename emp.ename%type; --> v_ename  varchar2(10)과 동일

 v_sal   emp.sal%type; --> v_sal    number(7, 2)와 동일

begin

 select ename, sal into v_ename, v_sal

 from emp 

 where empno = &1;

 p(v_ename);

 p(v_sal);

end;

/

@type01_a 7788

구   7:                   where empno = &1;

신   7:                   where empno = 7788;

SCOTT

3000

@type01_a 7499

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

ed type01_b.sql

var b_ename varchar2(30)

var b_sal   number

begin

 select ename, sal into :b_ename, :b_sal

 from emp

 where empno = &1;

end;

/

print b_ename b_sal

@type01_b 7788

print b_ename b_sal

@type01_b 7499

print b_ename b_sal

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

ed type02_a.sql

declare

 v_sal     emp.sal%type;

 v_deptno  emp.deptno%type;

 v_avg_sal number;

begin

 select sal, deptno into v_sal, v_deptno

 from emp 

 where empno = &1;

 select avg(sal) into v_avg_sal

 from emp 

 where deptno = v_deptno;

 p('부서 평균 급여 : ' || to_char(v_avg_sal));

 p('사원 급여 : ' || v_sal);

 p(v_sal - v_avg_sal || ' 만큼 더 받습니다.');

end;

/

@ type02_a 7499

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

ed type02_b.sql

var b_sal     number;

var b_deptno  number;

var b_avg_sal number;

var b_diff    number;

begin

 select sal, deptno into :b_sal, :b_deptno

 from emp 

 where empno = &1;

 select avg(sal) into :b_avg_sal

 from emp 

 where deptno = :b_deptno;

 select :b_sal - :b_avg_sal into :b_diff

 from dual;

 :b_diff := :b_sal - :b_avg_sal + 10;

end;

/

print b_avg_sal b_sal b_diff

print b_avg_sal b_sal b_diff

@type02_b 7499

        var a varchar2(30)

declare

 v_empno number := &sv_empno;

begin

 select ename into :a

 from emp

 where empno = v_empno;

end;

/

print a

A

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

Enter value for sv_empno: 7788

print a

A

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

SCOTT

  - PL/SQL block이 아닌 환경(SQL*Plus, iSQL*Plus)에서 생성

  - PL/SQL block이 종료되어도 값을 읽을 수 있다

  - 서버와 연결이 끊어지면 값을 참조할 수 없다 (cf. 클라이언트(환경) 종료)

  * 바인드 변수 선언 및 확인: SQL*Plus Command

- 선언: VAR[IABLE] [variable [type]]

- 확인: VAR[IABLE] [varaiable]

  * 바인드 변수 사용

    : 바인드 변수 이름 앞에 :(콜론)을 붙임

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

var

SP2-0568: 바인드 변수가 정의되지 않았습니다.

var a number     -- NUMBER 형 바인드 변수에 (p, s) 지정할 수 없음 

var

변수   a

데이터유형   NUMBER

var a

데이터유형   NUMBER

print a

         A

----------

begin

 select avg(sal) into :a

 from emp;

end;

/

print a

        A

----------

2073.21429

disconnect

print a

SP2-0640: 연결되지 않았습니다.

SP2-0641: "PRINT"(이)가 서버로 연결하는데 필요합니다

var a

변수   a

데이터유형   NUMBER

connect ??/??@211.183.3.52:1521/orcl

연결되었습니다.

print a

        A

----------

2073.21429

select * from emp

where sal >= :a;

exit

sqlplus top20/top20@211.183.3.52:1521/orcl

print a

SP2-0552: 바인드 변수 "A" 가 정의되지 않았습니다.

var a

SP2-0552: 바인드 변수 "A" 가 정의되지 않았습니다.

        set serveroutput on

declare

 v1 boolean;

begin

 v1 := 100 > 5;

 if v1 then

   p('맞다');

 else

   p('아니다');

 end if;

end;

/ 

* SQL & PL/SQL type

    - floating-point number literals

> 25f, +6.34F, 0.5d, -1D

      > binary_float_nan, binary_float_infinity

      > binary_double_nan, binary_double_infinity

    - the special values: INF, -INF, NaN

    - condition: is [not] nan

is [not] infinite

  * closed arithmetic operations 

select 100/0  from dual;    --> ORA-01476: divisor is equal to zero

select 100/0f from dual;    --> Inf

  * transparent rounding: rounding to the nearist binary

select 10/3 from dual;

select 10/3f from dual;

select 10d/3 from dual;

# 앞으로 수업 편의를 위해 p 프로시저 정의

create or replace procedure p (a varchar2)

is

begin

 dbms_output.put_line(a);

end;

/

show errors

  * PL/SQL Datatypes <== http://goo.gl/ivCN5

  * PL/SQL LONG and LONG RAW Datatypes <== http://goo.gl/Rc32c

    - LONG : variable-length character string

    - LONG RAW : binary data, byte strings

PL/SQL type (변수) SQL type (컬럼)

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

LONG (32760)     ==> VARCHAR2 (4000) 4000 바이트까지 저장

    <== 모든 바이트 읽음

    LONG (32760)     ==> LONG (2G) 모든 바이트 저장

    <== 32760 바이트까지 읽음

    LONG RAW (32760)     ==> LONG RAW (2147483648) 모든 바이트 저장  

    <== 32760 바이트까지 읽음

   cf.) Oracle Built-in Datatypes (SQL) <== http://goo.gl/IcT4E

  * BINARY_INTEGER datatype is identical to PLS_INTEGER

<== http://goo.gl/Tu0bC

    : -2,147,483,647 ~ 2,147,483,648

    : less storage and faster than NUMBER

  * BOOLEAN type

    : SQL엔 없는, PL/SQL에 추가된 타입

- T, F, N 값 저장

  1) 이름 짓기 규칙

  2) 의미 있는 이름

  3) NOT NULL, CONSTANT 사용

  4) 상수 선언 - CONSTANT 키워드는 반드시 타입 앞에

- 선언시 초기화 (하지 않으면 컴파일 에러)

  * PL/SQL variables <== http://goo.gl/4QQZE

- Scalar   : Number, Character, Date, Boolean

- Composite : Record, Table, Array

- Reference : Pointer (ref, ref cursor)

- Large object (LOB) : Locator (blob, clob, nclob)

  * Non-PL/SQL variables: 

- Bind variables (Host variable) : variable, print

   o 클라이언트 메모리: 바인드 변수의 식별자(identifier)

   o 서버 메모리: 바인드 변수의 실제 값(variable for data)

==> 서버와 연결을 끊으면 바인드 변수의 내용을 볼 수 없습니다.

   o 서버 쪽 변수에 대한 일종의 포인터

   o 클라이언트 종료: 바인드 식별자와 변수 모두 제거됩니다

- Substitution variable : &, &&, define, undefine, accept, ...

        set serveroutput on

declare

 h_date date;

 e_sal number(6) not null := 10000;

 location varchar2(13) := 'Atlanta';

 c_comm constant number := 1400;

BEGIN

 h_date := sysdate();

 dbms_output.put_line(

to_char(h_date, 'YYYY"년" MM"월" DD"일입니다"'));

 e_sal := e_sal + c_comm + 10;

END;

/

  * 선언부 - 변수 선언

- 변수 선언과 동시에 초기화 (:=, default)

- 변수 사용

  * 실행부 - 변수 초기화 (:=)

- 변수에 값 저장(배정)

- 변수 사용

  * subprograms - 파라미터

- 결과값 저장

  - 반드시 문자로 시작

  - $, _, # : 가능한 특수 문자

  - 길이는 30자

  - 예약어 불가

  - Case Insensitive

  * What Is the Difference Between a Variable and an Identifier?

    - 변수 : 데이터 저장 공간

    - 식별자 : 변수 이름

  변수?

  : 데이터를 담는 그릇

  : 이름, 타입, 크기

  : 초기화 - 선언과 동시에

  - 선언 이후에

  : reusability, maintenance

        drop table t1 purge;

create table t1 as select * from emp;

set serveroutput on

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

declare

 v_ret      number;

 v_avg_sal  number;

 v_sum_sal1 number;

 v_sum_sal2 number;

begin

 select avg(sal), sum(sal) into v_avg_sal, v_sum_sal1

 from t1;

 dbms_output.put_line(v_sum_sal1);

 for emp_rec in (select empno from t1 where sal > v_avg_sal) loop

   update t1

   set sal = sal + 50

   where empno = emp_rec.empno;

 end loop;

 for emp_rec in (select empno from t1 where sal <= v_avg_sal) loop

   update t1

   set sal = sal + 100

   where empno = emp_rec.empno;

 end loop;

 select sum(sal) into v_sum_sal2

 from t1;

 dbms_output.put_line(v_sum_sal2);

 dbms_output.put_line(v_sum_sal2 - v_sum_sal1);

 select 1000/0 into v_ret from dual;

exception

 when others then

   null;

end;

/

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

begin

 update t1

 set sal = sal+1000

 where rownum = 1;

 commit;

 -- other tx

 delete from t1

 where sal <= 2000;

 -- other tx

 commit;

end;

/

↓↓

begin

 begin

   update t1

   set sal = sal+1000

   where rownum = 1;

   -- other tx

 end;

 commit;

 begin

   delete from t1

   where sal <= 2000;

   -- other tx

 end;

 commit;

end;

/

↓↓

create or replace procedure p1

is

begin

 update t1

 set sal = sal+1000

 where rownum = 1;

 -- other tx

end;

/

create or replace procedure p2

is

begin

 delete from t1

 where sal <= 2000;

 -- other tx

end;

/

begin

 p1;

 commit;

 p2;

 commit;

end;

/