|
PL/SQL 요약
원본출처 : http://sh.hanarotel.co.kr/~ggola/telecom/ocp/pl-sql.htm
>>Access data with PL/SQL (아래 표가 깨지면 왼쪽 메뉴바를 더 왼쪽으로 드래그 해서 최소화 해주시면 되겠슴다)
* access the oracle database from an application through one of two languages : SQL and PL/SQL
_________________________________________________________________________
| |sql statements | sql statement executor |
___________ | ------------------|---------------------------------------------------|
|Application|--| | [be sent to the ORACLE SERVER at runtime] |
----------- | ___________ ______|___________________________________________________|
|___|pl/sql block | pl/sql engine -- procedural statement executor |
------------------| (compile and execute pl/sql block) |
| |
|__ sql statement executor (execute sql statement) _|
------------------------------------------------
- PL/SQL Block :
DECLARE (optional)
>> variable, constant, cursors, user-defined exeception
procedural |
|----> Procedural statement executor
procedural |
BEGIN (mandatory)
>> sql and pl/sql statements
procedural |----> Procedural statement executor
SQL |----> SQL statement executor
EXCEPTION (optional)
=>> specified action for error or some condition arise within the Executable Section
END ;
=> 예상문제 : 결국 실질적인 P/G실행은 PL/SQL Block도 SQL statement executor 가 담당
=> 예상문제 : PL/SQL 의 동작은 어떻게 이루어지나? 정답은 ------------------ (2)
(1) 모든 문장을 procedure excutable area로 보낸다
(2) 각각을 분리하여(seperate) SQL excutable area로 보낸다
(3) 모든 문을 SQL excutable area로 보낸다
(4) 분리하여 procedure excutable area로 보낸다
- PL/SQL Program은 procedural option이 있어야 사용가능하다
>> Anonymous Block : unnamed PL/SQL Block (embeded within an application or ..)
>> Stored Procedure/Function : named PL/SQL Block (parameters, invoke)
>> Package : named PL/SQL module (groups together related procedures, functions ...)
>> Database Trigger : PL/SQL Block (associated with a database table)
>>Delvelope a simple PL/SQL block
1.DECLARE BEGIN (EXCEPTION) END
* ----------------------------
DECLARE
vb_valid boolean not null := TRUE ;
vc_input varchar2(10) := NULL ;
vc_string1, vc_string2, vc_string3 varchar2(10) := NULL ;
vd_idate date not null := SYSDATE ;
cc_spec constant varchar2(10) := 'ORACLE' ;
vn_balance number(7,3) ;
vn_min_balance vn_balance%type := 12.0 ;
vc_emp_name emp.emp_name%type ;
vn_emp_id emp.emp_id%type ;
TYPE vt_person_table is table of varchar2(10) [not null]
index by binary_integer ;
vt_person vt_person_table ;
TYPE vr_person_record
(last_name emp.last_name%type
first_name emp.first_name%type,
gender char(1) not null := 'M' ) ) ;
vr_person vr_person_record ;
vtr_emp emp%rowtype ;
....
...
..
BEGIN
....
...
..
DECLARE
vb_valid boolean not null := TRUE ;
vc_sub_input varchar2(10) := NULL ;
BEGIN
....
..
END ;
....
...
..
* ----------------------------
2.Variable, Assign, Convert Datatypes
- variables : identifier [constant] datatype [not null] [:= | default plsql_expression] ;
- PL/SQL Declare에서 NOT NULL column으로 변수 정의 하고 값을 할당 안하면 => not compile
그러나 ' vc_deptid dept.id%type not null ; ' 형식으로 data type 할당후
not null column constraint의 table type으로 값을 정의하고 default value를 할당안하면
=> NULL값을 초기값으로 갖는다. 즉, ' vc_deptid varchar2(10) not null; ' 처럼 compile error가
발생하지 않는다.
=> scalra types : binary_integer, positive, natural, number, float, decimal, real,
integer, smallint, char, rowid, long, varchar, varchar2, boolean, date,
raw, long raw
=> % type attribute : binary_integer, positive, natural, number, float, decimal, real
=> composite types : pl/sql table 사용 (to store an unconstrained array of values)
pl/sql record type 사용 (record type 변수 선언)
=> 예상문제 : record에 value 할당 방법은 ?
(record name 이 vr_emp, column name이 last_name이면)
vr_emp.last_name = 'JOHN'
=> %rowtype attribute : variable according to the data structure of a database table
=> sub block에서 declare된 variable은 그 sub block 에서만 유효하다
- assign values to variables :
=> operator : numeric(**,*,/,+,-), character(||),
date(+,-), comparison(=,!=,<,>,<=,>=,is null,like,between,in)
EX) vn_count := v_count + 1;
vc_name_addr := v_name || v_addr;
vd_start_date := v_start_date -7;
vb_equal boolean := TRUE;
vb_equal := (v_n1 = v_n2); --> boolean value setting (v_n1과 v_n2의 비교에 따른)
=> 예상문제 : TRUE 와 NULL의 or 비교는 TRUE다
=> function : round, trunc, sqrt, upper, lower, initcap, substr, add_months,
months_between, nvl, greatest, lease
EX) vc_valid := (vc_emp is not null);
vc_last_name := upper(v_last_name);
vn_total := sum(vt_amount);
(--> vt_amount는 vt_amount_table이라는 number type의 pl/sql table)
=> convert datatypes automatically : between character and number
EX) vc_string varchar2(10) ;
vn_number number(10) ;
vc_string := 10 + '10'; ----> 숫자 10 은 자동으로 문자 '10' 으로 = '20'
vn_number := '10' + 10 ; ----> 문자 '10' 은 자동으로 숫자 10 으로 = 20
vc_string := vn_number || 'concate' ---> compile error (자동으로 convert 될 수 없다)
3.Input and Output Varibles
=> SQL*PLUS
* ----------------------------
REM pl/sql block 밖에서 data input and output (-- , /* .. */ : comment (주석))
VARIABLE gvn_total number (-- output을 위한 pl/sql block밖 global variable선언)
...
ACCEPT ad_sal PROMPT 'Please enter your salary : ' (-- user가 input할 value)
DECLARE
vn_sal number(12) := &ad_sal ;
....
....
BEGIN
:gvn_total := 12 * vn_sal
...
END ;
/
PRINT gvn_total (-- sql*plus상에서 value output)
* ----------------------------
=> 예상문제 : Global Variable의 선언및 사용은 ?
Declare 절 위에서 선언하고 Block내에서 치환시 ':'를 prefix로 사용한다.
>>Manipulate data with PL/SQL
1.SQL statements available within PL/SQL
- supports the Oracle Server syntax : select, insert, update, delete, commit, rollback
- not support DDL (Data Definition Language : create table, alter table)
- not support DCL (Data Control Language : grant, revoke)
* ----------------------------
DECLARE
vc_name emp.emp_name%type ;
vn_deptid emp.emp_dep_id%type ;
....
....
BEGIN
select emp_name, emp_dept_id
into vc_name, vn_deptid
from emp
where emp_id = 33 ;
insert into emp ('11', emp_name, emp_dept_id + 10) ;
savepoint a ;
insert into emp ('12', emp_name, emp_dept_id + 11) ;
savepoint b ;
rollback to savepoint a ;
commit ;
...
END ;
/
* ----------------------------
>>Control PL/SQL Flow of Execution
1.Execute statements conditionally
- if : elseif : else : end if
- loop : loop는 언제든지 exit를 만나면 종료되고 다음 statement를 진행한다.
1. loop ..... end loop ;
2. for ..... end loop ;
3. while ..... loop ;
- goto : 지정된 label로 이동
=> 예상문제 : loop중 반드시 exit를 필요로 하는 것은 ?
loop ............ exit ....... end loop ;
* ----------------------------
DECLARE
vc_name emp.emp_name%type ;
vn_deptid emp.emp_dep_id%type ;
vn_counter number() ;
....
....
BEGIN
select emp_name, emp_dept_id
into vc_name, vn_deptid
from emp
where emp_id = 33 ;
if vc_name = 'JOHN' then
vn_deptid = '1'
elseif vc_name = 'JANG'
vn_deptid = '2'
else
vn_deptid = '3'
end if ;
Loop
vn_counter := vn_deptid + 1 ;
exit when vn_counter > 10 ;
End Loop ;
For vn_counter in 1..10 loop
vn_deptid = vn_deptid + vn_counter ;
End Loop ;
<>
While vn_counter < 10 loop
vn_counter := vn_deptid + 1 ;
End Loop ;
<>
While vn_counter < 10 loop
vn_counter := vn_deptid + 1 ;
<>
While vn_deptid < 12 loop
vn_deptid := vn_counter + 2 ;
exit out_loop when vn_counter = vn_deptid * 2 ;
End loop in_loop ;
End Loop out_loop ;
if vn_counter = 10 then
goto start_point ;
end if ;
...
END ;
/
* ----------------------------
=> 예상문제 : 다음은 procedure를 만드는 문장이다. 틀린곳은?
create or replace procedure proc_1 is
..
begin
for cur_...... in cur_1
...
loop ;
close cur_1 ;
end ;
(1)위 문장에서 주의할 점은 end로 끝나는 부분에서 무엇을 end하는 것인가를
기술해야 한다는 점이다. 즉, end proc_1 ; 이라고 기술해야 한다.
(2)For Loop의 성격은 implicit cursor를 사용한다는 점에 유의해야 한다.
즉, cursor close는 for loop가 끝나면 자동으로 이루어진다.
따라서 manually로 cursor operation을 하려면 for loop에서 사용하지 않는다.
=> 예상문제 : procedure and function 차이는
[procedure] [function]
excute as a pl/sql stmt called as part of an expression
no RETURN datatype must contain a RETURN datatype
can return a value must return a value
>>Control SQL Statement Processing
1.cursor operation
- declare cursor
- open cursor
- fetch data
- close cursor
* ----------------------------
DECLARE
vc_name emp.emp_name%type ;
vn_deptid emp.emp_dep_id%type ;
cursor cur_item is
select team_id from emp where team_id <> manager_id ;
....
....
BEGIN
open cur_item ;
Loop
fetch cur_item into vn_deptid ;
.. (varify using cursor attribute) ....
......
exit when cur_item%notfound;
End loop ;
close cur_item ;
commit ;
...
END ;
/
* ----------------------------
- cursor attribute : %isopen : cursor open 상태
%notfound : fetch data 존재여부
%found : fetch data 존재여부
%rowcount : fetched data count
=> 예상문제 : What is a Cursor?
private SQL area to execute SQL statment and store processing information
=> 예상문제 : each fetch causes
the cursor to move its pointer to the next row in the active set
=> 예상문제 : cursor open시
open the cursor to execute the query and identify the active set
the cursor will now point to the first row in the active set
Although the query returns no row, PL/SQL does not raise an exception
>>Handle runtime execptions
- PL/SQL Block중 execption은 data 처리중 특정 condition or error 에 부합된 결과가
나타날때 시스템이 지정하거나 작성자가 임의로 지정한 마무리를 짖고
Block을 빠져나간다. (goto 처럼 다른 처리를 하는것이 아니다)
- Raise가 발생 했으나 적절한 exception이 없으면 그 PL/SQL Block은 Failure로 종료된다.
- exception은 반드시 1 작업에 1회만 발생한다.
왜냐면 sub block이 아닌이상 exception은 그 PL/SQL Block을 종료하기 때문
==> 예를 들어 아래 문제의 경우 위 1회 발생의 의미를 알 수 있다.
What is the maximum number of handleres processed before PL/SQL block is exited
when an exception occurs ? --> 당연히 1회 겠죠!
- exception types
Predefined Oracle 7 Server error : about 20 errors : do not declare, implicitly raised
Non-Predefined Oracle 7 Server error : any other standard Oracle7 Server error : declare, implicitly raised
User-defined error : defined by user : declare, explicitly raised
=> 예상문제 : SQL code is retruned the numeric value for the error code
0 - no exception encountered
1 - user-defined exception
+100 - NO_DATA_FOUND exception
negative_number - another Oracle7 Server error number
* ----------------------------
DECLARE
vc_name emp.emp_name%type ;
vn_deptid emp.emp_dep_id%type ;
....
BEGIN
select team_id
into vn_deptid
from emp
where team_id <> manager_id ;
delete from emp_manager
where manager_id = team_id ;
commit ;
....
EXCEPTION --> nested blocks을 가질수 없다
WHEN NO_DATA_FOUND THEN >> data가 없음
........ ;
rollback ;
WHEN TOO_MANY_ROWS THEN >> 1개 이상의 data가 return됨
rollback ;
WHEN OTHERS THEN >> 그 밖의 errors
... error message ....
rollback ;
END ;
/
* ----------------------------
SQL and PL/SQL 빈도입니다.
|
