|
SQL & PL/SQL
09. 데이터 조작 (Manipulating Data)
[b]The INSERT Statement[/b]
- INSERT statement을 사용하여 새로운 행을 삽입할수 있다.
- 이 문장은 한번에 한행만을 삽입한다.
- INSERT의 리스트 값은 테이블내의 columns을 순서에 기한다.
- 사용자가 원하면 테이블내의 columns의 순서를 변경하여 사용할수 있다.
- 문자와 날짜값은 single quotation marks ( ' ' )을 사용한다.
ex) insert into dept (deptno, dname, loc)
values (50,'adbddsds','detroit');
[b]Inserting Rows with Null Values[/b]
- implicit method : 테이블내의 column을 리스트에서 제외하면 이부분은 행이 삽입될 때
null값으로 처리된다.(오라클 내부적 자동처리)
ex) insert into dept (deptno, dname)
values (60, 'mis');
- explicit methode : 테이블내의 column을 리스트에서 제외하면 이부분은 행이 삽입될 때
null값으로 처리된다.(사용자 임의)
ex) insert into dept
values (70. 'finance', null);
[b]Inserting Special Values[/b]
- 특수값을 입력할 수 있다. 현재날짜와 시간(sysdate function)을 사용한다
- 현재 사용자 이름(user function)을 사용한다.
ex) insert into emp (empno, hiredate)
values (12,SYSDATE);
[b]Inserting Values by Using Substitution Variables[/b]
- 치환변수를 사용하여 새로운 행을 삽입할수 있다.
ex) insert into dept (deptno, dname, loc)
values ('&dept1', '&dept2', '&dept3');
[b]Copying Rows from Another Table[/b]
- INSERT statement은 subquery을 사용하여 다른 테이블에서 원하는 행을 복사할수 있다.
- VALUES절은 사용할수 없다.
- INSERT절의 리스트수와 subquery의 SELECT된 수와 맞아야 한다.
ex) insert into managers ( id, name, salary, hiredate )
select empno, ename, sal, hiredate
from emp
where job = 'MANAGER';
[b]The UPDATE Statement[/b]
- UPDATE statement은 존재하는 행을 다른 값으로 바꾼다.
- 만약, 요구시에 한번에 한행 또는 더 이상의 행을 바꿀 수 있다.
ex) update emp
set deptno = 30
where empno = 5667;
[b]Updating Rows in a Table[/b]
- where절에 따라 특이한 행들을 바꿀수가 있다.
- where절을 사용하지 않으면 모든 행들을 바꿀수가 있으므로 사용시 주의를 요한다.
[b]Updating with Mulitple-Column Subquery[/b]
- subquery에 다중 columns들을 사용하여 update를 한다.
ex) update emp
set (job, depno) = ( select job, deptno
from emp
where empno = 7499 );
[b]The DELETE Satement[/b]
- 테이블에 존재하는 행을 삭제할 때 사용한다.
[b]Deleting Rows from a Table[/b]
- where절의 조건에 따라 테이블의 행을 선택적으로 삭제할수 있따.
- where절의 조건을 않하면 테이블전체의 행을 삭제한다. 주의를 요한다.
[b]Deleting Rows Based on Another Table[/b]
- subquery내의 다른 테이블로부터 조건을 받아 행을 삭제한다.
ex) delete from employee
where deptno = ( select detpno
from dept
where dname ='sales');
[b]State of the Data After COMMIT[/b]
- 데이터 바꿈은 데이타베이스에 다시 쓰는것이다.
- 데이터의 전단계는 일시적으로 데이터을 잃어 버린다.
- 모든 사용자는 transaction의 결과를 볼수 있다.
- affected(어패취된) 행의 lock을 풀어준다.
- 모든 savepoints는 지워진다.
[b]State of the Data After ROLLBACK[/b]
- 데이터 바꿈을 취소한다(원위치).
- 데이터의 전단계를 다시 저장한다.(원위치)
- affected(어패취된) 행의 lock을 풀어준다.
[b]Rolling Back Changes to a Marke[/b]
- ROLLBACK TO SAVEPOINT statement에 의해서 그 시점으로 원위치를 할수있다.
10. 테이블의 생성과 관리 (Creating and Managing Tables)[/color
시작은 문자로 시작해야 한다.
문자길이는 1-30자까지 가능하다.
오직 테이블명에는 오직 A-Z, a-z, 0-9, _, $을 사용해야 한다.
한 사용자가 소유하고 있는 object는 동일한 이름으로 중복될수 없다.
oracle server에서 지정한 단어는 쓸수 없다.
[b]The CREATE TABLE Statement[/b]
- 당신이 가져야만 하는것 :
* 테이블을 생성할수 있는 권한
* 저장할수 있는 공간
- 당신이 원하는데로 :
* 테이블 이름
* column명, column 데이터타입, column 크기
[b]Referencing Another User's Tables[/b]
- 사용자 스키마(schema)를 사용하여 다른 사용자가 사용할수 있도록 할수 있다.
- 테이블명 앞에 사용자의 이름을 사용하여야만 한다.
[b]Creating Tables[/b]
ex) create table dept
( deptno number(2),
dname varchar2(14),
loc varchar2(13));
[b]Querying the Data Dictionary[/b]
- 당신이 가지고 있는 여러 가지 데이터 objects를 data dictionary 테이블에서 조회할수 있다.
- USER_TABLES : 사용자가 소유하고 있는 테이블을 describe한다.
- USER_OBJECTS : 사용자가 소유하고 있는 object types을 보여준다.
- USER_CATALOG : 사용자가 소유하고 있는 tables, views, synonyms, sequences을 보여준다.
ex) SQL> select *
from user_tables;
SQL> select distinct object_type
from user_objects;
SQL> select *
from user_catalog;
[b]Creating a Table by Using a Subquery[/b]
- ceate table 문장과 as subquery을 사용하여 테이블을 만들면서 동시에 행을 삽입할수 있다.
- subquery의 columns의 수에 맡게 create table을 만들어야 한다.
- create table 문장에 column의 리스트를 정의하지 않으면 초기값으로 subquery의
column의 명과 데이터타입을 적용한다.
ex) create table dept30
as select empno, ename, sal*12 ANNSAL, hiredate
from emp
where deptno = 30;
[b]The ALTER TABLE Statement[/b]
- 새로운 column을 추가 한다.
- 존재하고 있는 column의 이름 이나 데이터타입을 바꿀수가 있다.
- 새로운 column의 초기값을 정의한다.
ex) alter table dpt40
add (job varchar2(9));
alter table dptt40
modify (job varchar2(20));
[b]Adding a Column[/b]
- add절을 이용하여 새로운 행을 삽입한다.
ex) alter table dept30
add (ename varchar2(15))
[b]Modifying a Column[/b]
- 초기값, 데이터타입,크기를 바꿀수 있다.
- 숫자값의 column 및 테이터값의 크기를 늘릴수 있다.
- 테이블내에 데이터값이 null값이거나 행이 없으면 길이를 줄일수 있다.
- 테이블내에 null값을 포함하는 column이라면 데이터타입을 바꿀수가 있다.
ex) alter table dept30
modify (ename varchar2(15));
[b]Changing the Name of an Object[/b]
- 테이블, 뷰, 시퀀스, 시노임의 이름을 바꿀수 있다.
- 단, 이 object들의 소유자이어야 한다.
ex) rename dept to deprt;
[b]Dropping a Table[/b]
- 테이블에 모든 데이터와 구조를 지울수 있다.
- 이명령은 바로 커밋된다고 생각하면 된다.
- 모든 인덱스(indexes)가 지워진다.
- 이명령은 복원(roll back) 할수 없다.
ex) drop table dept30;
[b]Truncating a Table[/b]
- 테이블로부터 모든 행을 삭제한다.
- 그 테이블이 사용하는 저장공간을 풀어준다.
- 이명령은 복원(roll back) 행을 할수 없다.
- 그러나, delete문장을 사용하면 복원할수 있다.
[b]Adding Comments to a Table[/b]
- comment 문장을 사용하여 테이블 또는 column에 comments를 첨부 할수 있다.
- data dictionary을 통해서 이를 확인할수 있다.
ex) comment on talbe emp is 'Employee information';
data dictionary views
* ALL_COL_COMMENTS
* USER_COL_COMMENTS
* ALL_TAB_COMMENTS
* USER_TAB_COMMENTS
- string (' ')을 이용하여 comments를 지울수 있다.
ex) comment on table emp is ' ';
11. 제약조건 포함 (Including Constraints)
[b]Constraint Guidelines[/b]
- 테이블이 생성될 때 같이 생성한다.
- 테이블이 생성된 후에 생성할 수 있다.
- column와 table level에서 정의할 수 있다.
- data dictionary에서 constraint을 볼 수 있다.
* USER_CONSTRAINTS
* USER_CONS_COLUMNS
[b]Defining Constraints[/b]
- column constraint level
- table constraint level
[b]The NOT NULL Constraint[/b]
- not null constraint은 null값이 column에서 허락하지 않음을 보증한다.
- not null constraint은 오직 column level에서만 사용하며 table level에서는 허용하지 않는다.
ex) create table emp (
empno number(4),
ename varchar2(10) not null,
job varchar2(9));
[b]The UNIQUE KEY Constraint[/b]
- table level과 the column level에 둘 다 사용할 수 있다.
ex) create table dept (
deptno number(2),
dname varchar2(10),
loc varchar2(10),
constraint dept_dname_uk unique (dname));
[b]The PRIMARY KEY Constraint[/b]
- table level과 the column level에 둘 다 사용할 수 있다.
ex) create table dept (
deptno number(2),
dname varchar2(10),
loc varchar2(10),
constraint dept_dname_uk unique (dname),
constraint dept_deptno_pk primary key (deptno));
[b]The FOREIGN KEY Constraint[/b]
- table level과 the column level에 둘 다 사용할 수 있다.
- 참조
* parent table에서 table과 column을 확인한다.
- on delete cascade
* parent table에서 삭제를 허락하고 child table에서 의존 행을 삭제한다.
[b]The CHECK Constraint[/b]
- 각 행이 만족해야 하는 조건을 정의한다
ex) constraint emp_deptno_ck
check ( deptno between 10 and 1000 )
[b]Adding a Constraint[/b]
- 첨가, 삭제는 할 수 있으나 변화는 하지 못한다.
- enable 또는 disable을 할 수 있다.
- null constraint는 modify절을 사용하여 변화를 할 수 있다.
[b]Adding, Dropping a Constraint[/b]
ex) alter table emp
add constraint emp_mgr_fk foreign key(mgr) references emp(empno);
alter table emp
drop constraint emp_mgr_fk;
alter table dept
drop primary key cascade;
[b]Disabling, Enabling Constraint[/b]
ex) alter table emp
disable constraint emp_empno_pk cascade;
alter table emp
enable constraint emp_empno_pk;
[b]Viewing Constraints[/b]
- USER_CONSTRAINTS : view all constraint definitions and names
- USER_CONS_COLUMNS : view the columns associated with the constraint names
12. 뷰생성 (Creating Views)
[b]Creating View[/b]
- create view statement를 사용할 때 subquery을 사용한다.
- subquery는 복잡한 select syntax을 포함하여 사용할수 있다.
- subquery는 order by 절을 사용할수 없다.
- subquery에서 column aliases을 사용하여 view를 생성한다.
- USER_VIEWS
- FORCE : 기초 테이블의 존재여부는 관계없이 view를 생성한다.
ex) create view emp30
as select *
from emp
where deptno = 10;
[b]Modifying a View[/b]
- create or replace view절을 사용하여 view를 바꿀 수 있다.
ex) create or replace view eeeee
(employee_numver, employee_name, job_title)
as select empno, ename, job
from emp
where deptno = 10;
[b]Rules for Performing DML Operations on a View[/b]
- 간단한 view들은 데이터조작(DML operations)문을 사용할 수 있다.
- 그러나 아래와 같은 경우를 포함하는 view는 행을 삭제할 수 없다.
* group functions
* a group by clause
* the distinct keyword
[b]Using the WITH CHECK OPTION Clause[/b]
- with check option절을 이용하여 view에 그 조건에 만족하도록 한다.
ex) create or replace view eeeee
as select *
from emp
where deptno =40
with check option constraint aaaaa_ck;
SQL>update eeeee
set deptno =9999
where empno = 7788
error at line 1:ora-01402: view with check option where-clause violation
[b]Removing a View[/b]
- view를 삭제할 때 사용한다.
ex) drop view emp30;
13. 다른 데이터베이스 오브젝트들 (Other Database Objects)
[b]Sequence[/b]
- 자동적으로 단일한 수를 가진다.
- 공유할 수 있는 object이다.
- 전형적으로 primary key 값을 생성할 때 사용한다.
- replaces application code
- 메로리에 캐시되어 있을 때 sequence을 이용하여 속도를 늘린다.
ex) create sequence dept_deptno
increment by 1
start with 91
maxvalue 100
nocash
nocycle;
[b]Confirming Sequences[/b]
- USER_SEQUENCES data dictionary table에 sequence값을 확인할수 있다.
ex) select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;
[b]NEXTVAL and CURRVAL Pseudocolumns[/b]
- NEXTVAL : 다음에 나올 sequence값을 리턴한다. 다른 사용자라도 이를 참조할 수
있으며 이는 유일한 값이다.
- CURRVAL : 현재의 sequence값을 리턴한다. nextval는 currval에 값이 대입되기 전에
그 sequence에 기한다.
[b]Using a Sequence[/b]
- 메모리에서 sequence값을 캐쉬할 때 그 값을 빠르게 엑세스 하기 위할 때
- sequence값의 gap은 발생할 수 있다. :
* a rollback occurs
* the system crashes
* a sequence is used in another table
- 다음 sequence값을 볼수 있다. ( nocache이어야하며 USER_SEQUENCES table에 의해서 볼 수 있다.)
ex) insert into dept(deptno, dname, lob)
values (dept_deptn.nextval, 'marketinggg','sna diego');
select dept_deptno.currval
from dual;
[b]Modifying a Sequence[/b]
- 증가값, 최대값, 최소값, cycle option, or cache option을 바꿀 수 있다.
- sequence에 대한 alter 권한가진 사용자 또는 sequence의 소유자이어야 한다.
-
ex) alter sequence dept_deptno
increment by 1
maxvalue 999999
nocache
nocycle;
[b]Removing a Sequence[/b]
ex) drop sequece dept_deptno;
[b]Creating an Index[/b]
- 하나 또는 여러개의 column에 index를 생성할 수 있다.
- 테이블에서 column을 엑세스할 때 속도를 증가할 수 있다.
ex) create index emp_dname_idx
on emp(ename);
[b]Guidelines to Creating an Index[/b]
- 아래와 같은 상황에서는 index를 만들자:
* where절 또는 조건에 자주 사용되는 column.
* column이 넓은 범위의 값을 가지고 null값의 큰 값을 포함하고 있을때
* table이 크고 행의 2-4%정도만 query에 이용하는 경우.
- 아래와 같은 상황에서는 index를 만들지 말자:
* 테이블이 작을 때
* column이 query에서 자주 나오지 않을 때
* 행의 2-4%이상 query를 이용해야 할 때
* update가 자주 일어나는경우
[b]Confirming Indexes[/b]
- USER_INDEXES : index의 이름을 볼 수 있다. 이것은 유일한것이다.
- USER_IND_COLUMNS : index의 이름, 테이블이름, column이름을 볼 수 있다.
[b]Removing an Index[/b]
- data dictionary에서 index을 삭제할 수 있다.
- index의 소유자 또는 index를 삭제할 수 있는 권한이 있는 사용자만이 가능하다.
ex) drop index emp_ename_idx;
[b]Creating and Removing Synonyms[/b]
- 또단른 object의 이름이다.
- 다른 사용자의 테이블을 소유할 수 있다.
- object명은 짧게 쓴다.
ex) create synonym d_sum
for dept_sum_vu;
drop synonym d_sum;
14. 사용자 접근 다루기 (Controlling User Access)
[b]System Privileges[/b]
- 80 privileges(권한) 보다 더 많이 이용할 수 있다.
- DBA는 high-level system privileges(권한)을 가지고 있다.
* CREATE USER : 다른 사용자를 생성할 수 있다.
* DROP USER : 사용자를 삭제할 수 있다.
* DROP ANY TABLE : 어떤 스키마에 어떤 테이블이라도 삭제할 수 있다.
* BACKUP ANY TABLE : export utility없이 어떤 스키마에 어떤 테이블이라도 backup할 수 있다.
[b]Creating Users[/b]
- DBA는 create user문장을 사용하여 사용자를 생성한다.
ex) create user scott indentified by tiger;
[b]User System Privileges[/b]
- CREATE SESSION : database에 연결할 수 있는 권한 부여
- CREATE TABLE : 사용자의 스키마(schema)에 테이블을 생성할 수 있는 권한 부여
- CREATE SEQUENCE : 사용자의 스키마(schema)에 sequence을 생성할 수 있는 권한 부여
- CREATE VIEW : 사용자의 스키마(schema)에 view을 생성할 수 있는 권한 부여
- CREATE PROCEDURE : 사용자의 스키마(schema)에 stored procedure,function,or package을
생성할 수 있는 권한 부여
[b]Granting System Privileges[/b]
- DBA는 사용자에 specific system privileges을 줄 수 있다.
ex) grant creat table, create sequence, create view
to scott;
[b]Creating and Granting Privileges to a Role[/b]
- rol : 사용자에 줄 수 있는 관계있는 권한의 집합체 이름이다.
- 이 rol을 이용하여 여러 사용자에게 권한을 부여할 수 있다.
ex) create role manager;
grant create table, create view to manager;
grant manager to blake, clark;
[b]Changing Your Password[/b]
- 사용자 계정이 생성되고 password가 초기화 되어 있다
- 사용자는 그 password를 alter user문장을 이용하여 바꿀 수 있다.
ex) alter user scott indentified by lion;
[b]Object Privileges[/b]
- 소유자는 object에 모든 권한을 가지고 있다.
- 소유자는 자신의 object에 특이한 권한을 줄 수 있다.
- ALTER
- DELETE
- EXECUTE
- INDEX
- INSERT
- REFERENCES
- SELECT
- UPDATE
[b]Granting Object Privileges[/b]
- emp 테이블에 조회(query) 할 수 있는 권한을 준다.
ex) grant select, insert
on emp
to sue, rich;
sue, rich라는 사용자에 emp테이블을 select, insert할 수 있는 권한을 준다.
- 사용자와 role에 특이한 column을 update할 수 있는 권한을 준다.
ex) grant update (dname, loc)
on dept
to scott, manager;
scott, manager라는 사용자에 dept테이블중 dname, loc라는 column을 update할 수 있는 권한을 준다.
[b]Using WITH GRANT OPTION and PUBLIC keywords[/b]
- 사용자에게 권한을 주고 이 권한을 다른 사용자에게 줄 수 있도록한다.
ex) grant selec, insert
on dept
to scott
with grant option;
- 모든 사용자에게 권한을 준다.
ex) grant select
on alice.dept
to PUBLIC;
[b]Confirming Privileges Granted[/b]
- ROLE_SYS_PRIVS : role에 부여된 system privileges을 보여 준다.
- ROLE_TAB_PRIVS : role에 부여된 table privileges을 보여 준다.
- USER_ROLE_PRIVS : 사용자의 접근가능한 roles
- USER_TAB_PRIVS_MADE : 사용자의 object에 준 object privileges
- USER_TAB_PRIVS_RECD : 사용자가 받은 object privileges
- USER_COL_PRIVS_MADE : 사용자의 objects의 column에 준 object privileges
- USER_COL_PRIVS_RECC : 사용자의 column에 받은 object privileges
[b]How to Revoke Object Privileges[/b]
- revoke문장을 사용하여 다른 사용자에 준 권한을 빼앗을 수 있다.
- revoke하면 with grant option도 함께 빼앗는다.
ex) revoke select, insert
on dept
from scott;
15. SQL Workshop
연습문제) 아래의 표를 보고 테이블을 생성하라.( 테이블명 : test1 )
column_name MEMBER_ID LAST_NAME FIRST_NAME ADDRESS CITY PHONE JOIN_DATE
key type PK
null/unique NN, U NN
default value SYSTEM DATE
check PARK,KIM,LEE
date type number varchar2 varchar2 varchar2 varchar2 varchar2 date
length 10 25 25 100 30 15
해답 :
create table test1
( member_id number(10)
constraint test1_member_id_pk primary key,
last_name varchar2(25) test1_last_name_nn not null,
first_name varchar2(25)
constraint test1_first_name_ck check
( first_name in ('PARK','KIM','LEE')),
address varchar2(100),
city varchar2(30),
phone varchar2(15),
join_date date default sysdate
constraint test1_join_date_nn not null ) ;
16. 변수선언 (Declaring Variables)
[b]PL/SQL Block Structure[/b]
- DECLARE = optional
* variables, cursors, uesr-defined exceptions
- BEGIN = mandatory
* sql statements
* pl/sql statements
- EXCEPTION = optional
* actions to perform when errors occur
- END ; = mandatory
[b]Block Type[/b]
- anonymous : 이름이 없는 blocks
ex) decalre
begin
-- 문장
exception
end;
- procedure : 이름이 있는 blocks
ex) procedure name is
begin
-- 문장
exception
end;
- function : 함수로 return 값이 있다.
ex) function name
return datatype is
begin
-- 문장
return value;
exception
end;
[b]Use of Variables[/b]
- 데이터의 임시 저장소
- 저장값을 조작할 수 있다.
- reusability ( 재사용)이 가능하다.
- 보존이 편하다.(ease of maintenance)
[b]Handling Variables in PL/SQL[/b]
- declaration section(선언부)에서 변수를 선언하고 초기값을 정의한다.
- executable section(실행부)에서 변수에 새로운 값을 저장한다.
- parameter들에 의해 PL/SQL block에서 값을 넘겨 준다.
- 출력변수를 통해 결과를 볼 수 있다.
[b]Types of Variables[/b]
- PL/SQL variables(변수)
* scalar
* composite
* reference
* lob ( large objects )
- Non-PL/SQL variables(변수)
* bind and host variables
[b]Declaring PL/SQL Variables[/b]
ex) declare
v_1 date;
v_2 number(2) not null := 10 ; <-- not null임으로 초기값을 정의해야 한다.
v_3 varchar2(13) := 'atlanta';
v_4 constant number := 1400;
[b]Naming Rules[/b]
- 변수는 서로 다른 block에서는 같은 이름을 사용할 수 있다.
- 변수이름을 테이블의 column명으로 사용할 수 없다.
- 변수명은 30자를 넘을수 없다.
- 첫 자는 문자로 시작하여야 한다.
ex) declare
v_hiredate date
g_deptno number(2) not null := 10 ;
[b]Assigning Values to Variables[/b]
- 변수에 값을 대입할 때 (:=)을 사용하여 대입한다.
ex) v_hiredate := '31-dec-99';
[b]Variable Initialization and Keywords[/b]
- := assignment operator
- default
- not null
ex) v_hiredate := to_date('15-sep-99','dd-mon-yy');
g_mgr number(4) default 7839;
v_location varchar2(13) not null := 'chicago';
[b]The %TYPE Attribute[/b]
- declare a variable according to :
* a database column definition
* another previously declared variable
- prefix %TYPE with :
* the database table and column
* the previously declared variable name
[b]Declaring Variables with the %TYPE Attribute[/b]
ex) v_ename emp.ename%TYPE;
v_balance number(7,2);
v_min_balance v_balance%TYPE := 10;
[b]Declaring BOOLEAN Variables[/b]
- BOOLEAN variable(변수)는 오직 값이 참, 거짓, null 값을 할당 받는다.
- 변수는 logical operators AND, OR, and NOT에 의해 연결된다.
- 변수는 항상 TRUE, FALSE, or NULL값이다.
- 숫자, 문자, 날짜값, 표현식은 boolean값을 가질 수 있다.
ex) v_comm_sal BOOLEAN := ( v_sal1 < v_sal2 );
17. 실행문작성 (Writing Executable Statement)
[b]PL/SQL Block Syntax and Guidelines[/b]
- 문장은 여러 줄에 걸쳐서 쓸 수 있다. 그러나, key word는 줄을 나누어 사용할 수 없다.
- 어휘(lexical) 단위로 space를 이용하여 나눌 수 있다.
* delimiters --> +, -, *, /, =, @, ;, <>, !=, ||, --, /* ~~ */, :=
* identitfiers --> 문자로 30자까지 사용할 수 있다. 그리고, 첫 자는 알파벳 문자로 시작하여야 한다.
* literals --> 문자와 날짜기호는 single quotation(' ')을 처리하여야 한다.
v_enmae := 'adegder';
* comments --> --은 단행에 comment할때, /* ~~ */은 여러행에 comment로 처리한다.
[b]SQL Functions in PL/SQL[/b]
- 가능한 것 :
* single-row number
* single-row character
* datatype conversion
* data
- 불가능한것 :
* greatest
* least
* decode
* group functions ( avg, min, max, count, sum, stddew, variance )
ex) v_total := sum(number_tables);
error : a compile error
[b]Commenting Code[/b]
- prefix single-line comments with two dashes ( - - )
- palce multi-line comments between the symbols ( /* and */ )
[b]Datatype Conversion[/b]
- 비교할수 있는 datatype으로 데이터를 변환한다.
- to_char
- to_date
- to_number
ex) begin
select to_char(hiredate,'mon.dd, yyyy')
from emp;
end;
v_comment := user||':'||sysdate;
결과는 error 수정하면
v_comment := user||':"||to_char(sysdate);
[b]Using Bind Variables[/b]
- 변수앞에 colon(:)을 쓴다.
ex) declare
v_sal emp.sal%TYPE;
begin
select sal
into v_sal
from emp
where empno = 7369;
:salary := v_sal;
end;
18. 오라클 서버와의 연동 (Interacting with the Oracle Server)
[b]SELECT Statement in PL/SQL[/b]
- INTO 절이 요구된다.
ex) declare
v_deptno number(2);
v_loc varchar2(15);
begin
select deptno, loc
into v_deptno, v_loc
from dept
where dname = 'sales';
end;
[b]Retrieving Data in PL/SQL[/b]
- order date와 ship date의 타입을 가져올 수 있다.
ex) declare
v_ordedate ord.orderdate%TYPE;
v_shipdate ord.shipdate%TYPE;
begin
select orderdate, shipdate
into v_orderdate, v_shipdate
from ord
where id = 157;
end;
[b]Inserting Data[/b]
- 새로운행을 삽입한다.
ex) declare
v_empno emp.empno%type;
begin
select empno_sequence.nextval
into v_empno
from dual;
insert into emp(empno, ename, job, deptno)
values(v_empno, 'hardrin', 'caelr',10);
end;
[b]Udating Data[/b]
- 행을 바꾼다.
ex) declare
v_sal_increase emp.sal%type := 2000 ;
begin
update emp
set sal = sal + v_sal_increase
where job = 'adgdgg';
end;
[b]Deleting Data[/b]
- 행을 삭제한다.
ex) declare
v_deptno emp.deptno%type := 10 ;
begin
delete from emp
where deptno = v_deptno;
end;
[b]SQL Cursor Attributes[/b]
- SQL%ROWCOUNT : SQL 문장에서 가장 최근에 작용한 행의 수를 나타낸다.
- SQL%FOUND : SQL 문장에서 가장 최근에 작용한 하나 또는 여러행이 있으면 값을
TRUE로 BOOLEAN에 리턴한다.
- SQL%NOTFOUND : SQL 문장에서 가장 최근에 작용한 행이 하나도 없으면 값을
TRUE로 BOOLEAN에 리턴한다.
- SQL%ISOPEN : 항상 false이다. 왜냐하면 PL/SQL가 종료된 후 바로 커서는 닫히기 때문이다
19. 조건문 (Conditional Statement)
[b]IF Statements[/b]
ex) if v_ename = 'osborne' then
v_mgr := 22;
end if;
[b]IF-THEN-ELSE Statement Execution Flow[/b]
ex) if v_ename = 'king' then
v_job := 'manager';
else
v_job := 'clerk';
end if;
[b] IF-THEN-ELSIF Statement Execution Flow[/b]
ex) if :dept.deptno = 10 then
v_comm := 5000;
elsif :dept.deptno = 20 then
v_comm := 7500;
else
v_comm := 2000;
end if;
[b]LOOP Statements[/b]
ex) loop
.........
.........
exit [ when condition ];
end loop;
[b]FOR LOOP[/b]
ex) declare
v_ordid item.ordid%TYPE := 101;
begin
for i in v_lower..v_upper loop
insert into item(ordid, i);
end loop;
end;
[b]WHILE LOOP[/b]
ex) while v_running_total < &p_itemtot loop
v_qty := v_qty + 1;
end loop;
20. 복합적인 데이터 타입 다루기 (Working with Composite Datatypes)
[b]Creating a PL/SQL Record[/b]
- 테이블에서 특정 column을 모아 변수로 선언한다.
ex) TYPE emp_record_type IS RECORD
(ename varchar2(10),
job varchar2(9),
sal number(7,2));
emp_record emp_record_type;
[b]The %ROWTYPE Attribute[/b]
- 테이블 또는 뷰의 columns들을 모아 선언한다.
- 테이블 또는 뷰의 columns들의 테이터 타입과 그들의 이름을 레코드로 만들어 변수로 선언한다.
ex) declare
emp_record emp%ROWTYPE;
[b]Creating a PL/SQL Table[/b]
- 테이블의 테이터 타입을 선언한다.
- 그 테이터타입을 변수로 선언한다.
ex) declare
TYPE date_table_type IS TABLE OF date
INDEX BY BINARY_INTEGER;
date_table date_table_type;
[b]Using PL/SQL Tables Methods[/b]
EXISTS(n) : n번째 요소에 pl/sql이 존재하면 true을 리턴한다.
COUNT : pl/sql테이블의 현재 위치를 리턴한다.
FIRST : pl/sql테이블의 첫 번째 index를 리턴한다.
LAST : pl/sql테이블의 마지막 index를 리턴한다.
PRIOR(n) : pl/sql테이블의 n보다 이전 index를 리턴한다.
NEXT(n) : pl/sql테이블의 n보다 이후 index를 리턴한다.
EXTEND(n,i) : pl/sql테이블의 size를 증가 시킨다.
TRIM : 마지막의 한 개의 요소를 제거한다.
DELETE : 모든 요소를 제거한다.
21. 명시적 커서 작성 (Writing Explicit Cursors)
[b]Declaring the Cursor[/b]
ex) declare
cursor c1 is
select empno, ename
from emp;
cursor c2 is
select *
from dept
where deptno = 10;
begin .....
[b]Fetching Data from the Cursor[/b]
ex) delcare
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
cursor c1 is
select empno, ename
from empl;
begin
open c1;
for I in 1..10 loop
fetch c1 into v_empno, v_ename;
end loop;
end;
[b]Closing the Cursor[/b]
ex) delcare
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
cursor c1 is
select empno, ename
from empl;
begin
open c1;
for I in 1..10 loop
fetch c1 into v_empno, v_ename;
end loop;
close c1;
end;
[b]Explicit Cursor Attributes[/b]
- %ISOPEN : cursor가 open이면 TRUE을 리턴한다.
- %NOTFOUND : 한행이라도 찾을수 없으면 TRUE을 리턴한다.
- %FOUND : 한행이라도 찾으면 TRUE을 리턴한다.
- %ROWCOUNT : 행의 수
%FOUND %ISOPEN %NOTFOUND %ROWCOUNT
OPEN BEGIN Error Error Error
AFTER Null True Null 0
1ST BEGIN Null True Null 0
FETCH
AFTER True True False 1
LAST BEGIN True True False 증가
AFTER False True True
CLOSE BEGIN False True True
AFTER Error False Error Error
22. 심화된 명시적 커서의 개념 (Advanced Explicit Cursor Concepts)
[b]Cursors with Parameters[/b]
ex) declare
cursor c1
( v_deptno number, v_job varchar2 ) is
select empno, ename
from emp
where deptno = v_deptno
and job = v_job;
begin
open c1(10, 'clerk');
declare
job_emp emp.job%type := 'clerk';
v_name emp.ename%type ;
cursor c1 ( v_deptno number, v_job varchar2 ) is
select ........
open c1 (10, job_emp);
open c1 (20,'adgsdga');
[b]The FOR UPDATE Clause[/b]
ex) declare
cursor c1 is
select empno, ename
from emp
for update nowait; <-- 어떤 사용자가 똑같이 lock을 걸고 있으면 끝내라.
The WHERE CURRENT OF Clause
- update 또는 delete시 where조건 없이 현재 fetch한 행을 update, delete 한다.
ex) declare
cursor c1 is
select .....
for update nowait ;
begin
......
for emp_record in c1 loop
update ....
where current of c1 ;
....
end loop ;
commit;
end:
[b]Cursors with Subqueries[/b]
ex) declare
cursor my_cursor is
select t1.deptno, dname, staff
from dept t1, ( select deptno, count(*) staff
from emp
group by deptno ) t2
where t1.deptno = t2.deptno
and staff >= 5 ;
23. 예외 다루기 (Handing Exceptions)
[b]Exception Types[/b]
- oracle server에서 미리 정의한 에러
- oracle server에서 정의 하지 않은 에러
- 사용자가 정의한 에러
[b]Trapping Predefined Oracle Server Errors[/b]
- NO_DATA_FOUND
- TOO_MANY_ROWS
- INVALID_CURSOR
- ZERO_DIVIDE
- DUP_VAL_ON_INDEX
ACCESS_INTO_NULL
초기화되지 않은 object의 속서에 값을 assign
COLLECTION_IS_NULL
초기화되지 않은 nested테이블에 exists 와 다른 방법을 적용
CURSOR_ALREADY_OPEN
open corsor가 이미 open됨
DUP_VAL_ON_INDEX
중복된 값을 입력하려고 한다.
INVALID_CURSOR
잘못된 cursor연산자를 발생했다.
INVALID_NUMBER
문자열을 number 타입으로 변화하는 것이 실패함.
LOGIN_DENIED
login시 username 또는 password를 잘못 입력함.
NO_DATA_FOUND
한행도 select되지 않음.
NOT_LOGGED_ON
database에 connect 되지 않음.
PROGRAM_ERROR
pl/sql내부에 에러가 남.
ROWTYPE_MISMATCH
host 와 pl/sql cursor의 변수가 일치하지 않음.
STORAGE_ERROR
pl/sql 실행중 memory를 손상시킴.
SUBSCRIPT_BEYOND_COUNT
nested테이블을 참조하는 요소가 너무 큰 index를 사용함.
SUBSCRIPT_OUTSIDE_LIMIT
nested테이블을 참조하는 요소가 정상적인 범위에서 벗어난 index 사용.
TIMEOUT_ON_RESOURCE
resource를 기다리는 동안 timeout이 발생
TOO_MANY_ROWS
single row select에서 다중행을 포함한다.
VALUE_ERROR
arithmetic, conversion, truncation, size constraint error
ZERO_DIVIDE
0 으로 나눔.
[b]Predefined Exception[/b]
ex) begin select ........ commit ;
exception
when no_data_found then 문장;
when too_many_rows then 문장;
when others then 문장;
end;
[b]Non-Predefined Error[/b]
ex) e_products_invalid EXCEPTION;
PRAGMA EXCEPTION_INIT( e_products_invalid,-2292 ) ;
v_message varchar2(50);
begin
.......
EXCEPTION WHEN e_products_invalid then
:g_message := 'Product code......!!!!';
.......
end;
-2992 error code를 e_products_invalid로 정의
[b]User-Defined Exception[/b]
ex) e_amtt_remain EXCEPTION;
......
BEGIN
.......
raise e_amtt_remain;
.......
EXCEPTION WHEN e_amtt_remain then
:g_message := 'Product code......!!!!';
.....
END;
[b]Functions for Trapping Exceptions[/b]
- SQLCODE : error code를 리턴한다.
- SQLERRM : error message를 리턴한다.
ex) declare
v_error_code number;
v_error_message varchar2(255);
begin
........
exception
........
when others then
rollback;
v_error_code = SQLCODE ;
v_error_message = SQLERRM ;
insert into errors values ( v_error_code, v_error_message );
[b]RAISE_APPLICATION_ERROR[/b]
- error message를 사용자가 정의하고 싶을 때 사용.
- error code범위는 (-20000 ~ 20999)까지 한다.
ex) .........
exception
when no_data_found then
raise_application_error ( -20001,'manager is not a valid employee.');
end ;
|
