database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 8827 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 8827
Oracle 8 - SQL & PL/SQL (2)
작성자
정재익(advance)
작성일
2001-12-11 22:55
조회수
15,399

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 ;

[Top]
No.
제목
작성자
작성일
조회
8858Oracle 8 - Network Administration
정재익
2001-12-12
11683
8854Oracle 8 - Database Administration (2)
정재익
2001-12-12
28245
8853Oracle 8 - Database Administration (1)
정재익
2001-12-12
14342
8827Oracle 8 - SQL &amp; PL/SQL (2)
정재익
2001-12-11
15399
8825Oracle 8 - SQL &amp; PL/SQL (1)
정재익
2001-12-11
14598
8772특정 TABLE을 다른 TABLESPACE로 옮기는 방법 [1]
정재익
2001-12-08
5231
8771DB 이름과 오라클 SID 를 변경하는 방법
정재익
2001-12-08
8393
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.043초, 이곳 서비스는
	PostgreSQL v13.3으로 자료를 관리합니다