Transcript Example
.
Oracle Training
오라클 실습
1
Contents
Chapter 1
Server Concepts
Chapter 2
Retrieving data
2-1.
Simple retrieving
2-2.
Joins
2-3.
Subqueries
Oracle functions
Chapter 4
Manipulating data
Chapter 5
DDL
Chapter 6
Indexes
Chapter 7
PL/SQL
Chapter 8
Triggers
.
Chapter 3
오라클 실습
2
.
Chapter 1
Server Concepts
오라클 실습
3
Oracle 9i
Current versions
9.0.1.1
• Oracle9i Database Enterprise/Standard/Personal Edition for Windows NT/2000/XP Pro
• Oracle9i Personal Edition for Microsoft Windows 98
• Oracle9i Database Enterprise Edition for Sun SPARC Solaris (64-bit)
9.0.1
• Oracle9i Database Enterprise/Standard Edition for Sun SPARC Solaris (32-bit)
• Oracle9i Database Enterprise/Standard Edition for Compaq Tru64
• Oracle9i Database Enterprise/Standard Edition for AIX
• Oracle9i Database Enterprise/Standard Edition for HP-UX
• Oracle9i Database Enterprise/Standard Edition for Linux
Software Download
http://otn.oracle.co.kr/software/
.
오라클 실습
4
Installation
Requirements in the Linux
Memory: 32M
Swap space: 3 * size of memory
Disk space: 400M
OS: LINIX 2.0.34
System library: GNU C Library, version 2.0.7
Installation references
http://kldp.org/HOWTO/html/Oracle-8/Oracle-8-HOWTO-3.html
http://database.sarang.net/database/oracle/oracle-805/server.805/a66251/
.
1req.htm#926586
오라클 실습
5
Oracle Structure
PMON
LCKn
RECO
SMON
SGA
Shared SQL Area
Server
SNPn
Redo Log Buffer
Database Buffer Cache
DBWR
LGWR
ARCH
CKPT
User
Control Files
Redo Log Files Offline Storage
.
Datafiles
오라클 실습
6
Oracle Files (1)
Alert log
Contains informational and error messages concerning the Oracle system
and processes. It should be checked frequently, at least daily
Control files
Track databases structures and ensure synchronicity of all databases files
via the system change number (SCN)
Database file
Make up the physical side of the tablespaces
.
오라클 실습
7
Oracle Files (2)
Parameter file
Contains the initialization parameters that tell the Oracle server how to
configure memory and internal resources as well as external file locations
and process configurations
Redo log file
Contain the transaction journals; they are critical for database recovery.
Redo log files are copied to the archive logs if archive logging is enables.
Trace files
Generated by all background processes an. If tracing is enabled, by each
session. Trace files contain statistics for the process and log process
.
messages and errors
오라클 실습
8
Storage Structure and Datafiles
Logical structure
Tablespace
Segment
Extent
Block
Physical structure
Datafiles
.
오라클 실습
9
Oracle Instance (1)
SGA + Background processes
DBWn process (Database writer)
Writes dirty (used or changes) buffers from the SGA databases buffers to
the disk
The LGWR process (Log writer)
Writes redo log entries from the log buffers to the redo logs
The SMON process (System monitor)
Cleans up sort memory areas and recovers instances after instance failures
as well as coalesces contiguous chuncks of tree space in datafiles
The PMON process (Process monitor)
Cleans up after failed processes
.
오라클 실습
10
Oracle Instance (2)
The CKPT process (Check point)
Signals the DBWn at the checkpoints and updates all the datafiles and
control files with the curent checkpoint information.
The ARCH process (Archiver)
Copies the online redo log files the location where archives files are
written
The RECO process (Recoverer)
Recovers failed distributed transactions
An instance can have several optional processes
LCKn, SNPn, and so on
.
오라클 실습
11
The Shared Pool
Library cache
contains statement text, parsed code, and execution plan
Data dictionary cache
Contains table, column definitions, and privileges from the data dictionary
tables
UGA
Contains MTS users’ session information
.
오라클 실습
12
The Data Buffer Cache
The buffer cache holds copies of the data blocks from the data
files
Because the buffer cache is a part of the SGA, these blocks can
be shared by all users
The server processes read data from the data files into the
buffer cache
The DBWR process writes data from the buffer cache into the
.
data files
오라클 실습
13
The Redo Log Buffer
Redo Log Buffer Content
The Oracle server processes copy redo entries from the user’s memory
space to the redo log buffer for each DML or DDL statement
The redo entries contain the information necessary to reconstruct or redo
changes made to the database by INSERT, UPDATE, DELETE, CREATE,
ALTER, or DROP operations
Redo Entries and LGWR
The LGWR process writes the redo log buffer to the active online redo log
file on disk
The redo log buffer is a circular buffer
.
오라클 실습
14
Oracle Server Programming
PL/SQL
OCI Programming
Pro*C
.
ODBC / JDBC
오라클 실습
15
PL/SQL
PL/SQL is a procedural language
PL/SQL is a block-structured language
Control structures are the most important PL/SQL extension to
SQL
PL/SQL combines the data manipulating power of SQL with
the data processing power of procedural languages
PL/SQL can provide information hiding
Better Performance
.
Portability
오라클 실습
16
PL/SQL Engine
PL/SQL
Block
PL/SQL
Block
non-SQL
SQL
Procedural
Statement
Executor
SQL Statement Executor
.
Oracle Server
오라클 실습
17
The Oracle Call Interfaces
SQL is a non-procedural language
On the other hand, third generation programming languages such as C,
COBOL, FORTRAN are procedural
The Oracle Call Interfaces allow you to develop applications
that take advantage of the non-procedural capabilities of SQL
.
and the procedural capabilities of a third generation language
오라클 실습
18
What Are the OCIs?
A set of application programming interfaces that allow you to
manipulate data and schema in an Oracle database
You compile and link an OCI program in the same way that
you compile and link a non-database application
There is no need for a separate preprocessing or
precompilation step
The call interfaces support all SQL data definition, data
manipulation, query, and transaction control facilities that are
.
available through the Oracle Server
오라클 실습
19
The OCI Development Process
Source Program Files
Host Language
Compiler
OCI Library
Object Files
Host Linker
Oracle
RDBMS
.
Application
오라클 실습
20
Pro*C
An Oracle Precompiler is a programming tool that allows you
to embed SQL statements in a high-level source program
The precompiler
Accepts the source program as input
Translates the embedded SQL statements into standard Oracle rutime
library calls
Generates a modified source program that you can compile, link, and
.
execute in the ususal way
오라클 실습
21
Pro*C
System Editor
Source Program
With embedded SQL
statements
Pro*C Precompiler
Modified Source Program
With all SQL statements
replaced by library calls
Compiler
Object Program
Linker
To resolve calls
Oracle
Runtime
Library
(SQLLIB)
오라클 실습
.
Source
Program
22
ODBC / JDBC
Oracle supports ODBC and JDBC drivers
Window programmers can access Oracle servers via ODBC driver
JAVA programmers can access Oracle servers via JDBC driver
.
Programmers need to concern only ODBC/JDBC interfaces
오라클 실습
23
ODBC
Client
ODBC
Informix
DB
Oracle
DB
Sybase
DB
.
Window
Application
Program
Server
오라클 실습
24
Communication between JDBC and DBMS
Java application
JDBC API
JDBC Driver Manager
JDBC Driver API
JDBC/
ODBC
Bridge
ODBC
driver
Vendersupplied
JDBC
driver
Database
.
Database
오라클 실습
25
.
Chapter 2.
Retrieving Data
오라클 실습
26
.
2-1.
Simple Retrieving
오라클 실습
27
SQL, SQL*Plus, and Others
SQL
A command language for communications with the DBMS Server
SQL*Plus
An Oracle tool that recognizes and executes SQL and PL/SQL statements
PL/SQL
An Oracle procedural language that extends SQL by adding application
logic
.
Embedded SQL
오라클 실습
28
Sample Tables (1)
EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
JOB
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
MGR
--------7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
HIREDATESAL
-------80/12/17
81/02/20
81/02/22
81/04/02
81/09/28
81/05/01
81/06/09
87/04/19
81/11/17
81/09/08
87/05/23
81/12/03
81/12/03
82/01/23
COMM
--------800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
950
3000
1300
DEPTNO
----------------20
300
30
500
30
20
1400
30
30
10
20
10
0
30
20
30
20
10
.
EMPNO
--------7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
오라클 실습
29
Sample Tables (2)
DEPT (deptno, dname, loc)
DEPTNO
--------10
20
30
40
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
SALGRADE (grade, losal, hisal)
GRADE
--------1
2
3
4
5
LOSAL
--------700
1201
1401
2001
3001
HISAL
--------1200
1400
2000
3000
9999
.
BONUS (ename, job, sal, comm)
오라클 실습
30
Writing SQL Commands
Commands can be on one or many lines
Tabs and indents can be used for readability
Abbreviations and splitting of words are not allowed
Commands are not case sensitive
.
Commands are entered into the SQL buffer
오라클 실습
31
The Basic Query Block
Three basic clauses
SELECT identifies what columns
FROM identifies which table
.
WHERE restricts rows to meet a condition
오라클 실습
32
Selecting All Columns, All rows
Simplest SELECT statement contains the following two clauses:
SELECT clauses
• Asterisk (*) indicates all columns
FROM
Example
*
dept;
.
SQL> SELECT
2
FROM
오라클 실습
33
Selecting Specific Columns
Identify the column names by using the DESCRIBE command
List the columns in the SELECT clause
Separate columns by using a comma
Specify columns in the order you want them to appear
Example
empno, ename, job
emp;
.
SQL> SELECT
2
FROM
오라클 실습
34
Column Label Defaults
Label default justification
Left: date and character data
Right: numeric data
.
Label default display is uppercase
오라클 실습
35
Arithmetic Expressions
Create expressions on NUMBER and DATE data types by
using operators
Add
+
Subtract
-
Multiply
*
Divide
/
.
Override rules of precedence by using parentheses
오라클 실습
36
Arithmetic Expressions
Display the annual salary for all employees
SQL> SELECT
2
FROM
ename, sal * 12, job
emp;
Result
SAL*12
--------9600
19200
15000
35700
15000
34200
29400
36000
60000
18000
13200
11400
36000
15600
JOB
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
* Note that the new column (sal*12)
is from display only
.
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
오라클 실습
37
Operator Precedence
Multiplication and division take priority over addition and
subtraction
Parentheses can be used to force prioritized evaluation and to
clarify statements
.
Operators of the same priority are evaluated from left to right
오라클 실습
38
Operator Precedence
Parentheses change the order in which a statement is evaluated
Examples
SQL> SELECT ename, 12 * sal + 100
2
FROM emp;
SQL> SELECT ename, 12 * (sal + 100)
2
FROM emp;
ENAME
--------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
12*(SAL+100)
-----------10800
20400
16200
36900
16200
35400
30600
37200
61200
19200
14400
12600
37200
16800
.
12*SAL+100
---------9700
19300
15100
35800
15100
34300
29500
36100
60100
18100
13300
11500
36100
15700
오라클 실습
39
Column aliases
A column alias renames a column heading
Especially useful with calculations
Immediately follows column name
• Optional AS keyword between column name and alias
Example
ename, sal,
12 * (sal + 100) AS ANNUAL_SALARY
emp;
.
SQL> SELECT
2
3
FROM
오라클 실습
40
Column aliases
A column alias renames a column heading
Especially useful with calculations
Immediately follows column name
• Optional AS keyword between column name and alias
Double quotation make are required if an alias contains spaces, special
characters, or is case-sensitive
Example
ename, sal,
12 * (sal + 100) “ANNUAL SALARY”
emp;
.
SQL> SELECT
2
3
FROM
오라클 실습
41
Concatenation Operator
The concatenation operator
Is represented by two vertical bars (||)
Links columns or character strings to other columns
Creates a resultant column that is a character expression
.
오라클 실습
42
Concatenation Operator
Example
SQL> SELECT ename || ', ' || job "jobs"
2
FROM emp;
.
jobs
--------------------SMITH, CLERK
ALLEN, SALESMAN
WARD, SALESMAN
JONES, MANAGER
MARTIN, SALESMAN
BLAKE, MANAGER
CLARK, MANAGER
SCOTT, ANALYST
KING, PRESIDENT
TURNER, SALESMAN
ADAMS, CLERK
JAMES, CLERK
FORD, ANALYST
MILLER, CLERK
오라클 실습
43
Managing Null Values
NULL is a value that is unavailable, unassigned, unknown, or
inapplicable
NULL is not the same as zero or space
Arithmetic expressions containing a null value evaluate to
.
NULL
오라클 실습
44
Managing Null Values
Example
SQL> SELECT ename, job, sal * comm / 100 commission
2
FROM emp;
JOB
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
COMMISSION
---------4800
6250
17500
0
.
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
오라클 실습
45
NVL function
Convert NULL to an actual value with NVL
Datatypes to use are date, character, and number
Datatypes must match
• NVL(start_date,’01-JAN-95’)
• NVL(title,’No Title Yet’)
.
• NVL(salary, 1000)
오라클 실습
46
NVL function
Example
SQL> SELECT ename, job, sal * NVL(comm, 0) / 100 commission
2
FROM emp;
JOB
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
COMMISSION
---------0
4800
6250
0
17500
0
0
0
0
0
0
0
0
0
.
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
오라클 실습
47
Duplicate Rows
The default display of queries is all rows including duplicate
rows
SQL> SELECT
2
FROM
deptno
emp;
Eliminate duplicate rows by using DISTINCT in the SELECT
clause
DISTINCT deptno
emp;
.
SQL> SELECT
2
FROM
오라클 실습
48
DISTICT with Multiple Columns
DISTINCT applies to all columns in the SELECT list
SQL> SELECT
2
FROM
DISTINCT deptno, job
emp;
When DISTINCT is applied to multiple columns, the result
.
represents the distinct combination of the columns
오라클 실습
49
The ORDER BY Clause
Sort rows with the ORDER BY clause
ASC - ascending order, default
DESC - descending order
ORDER BY clause is last in SELECT command
Example
ename, job, hiredate
emp
ename;
.
SQL> SELECT
2
FROM
3
ORDER BY
오라클 실습
50
The ORDER BY Clause
The default sort order is ascending
the sort order can be reversed by using DESC
You can sort by expressions or aliases
SQL> SELECT
2
FROM
3
ORDER BY
ename name, job, hiredate
emp
name DESC;
NULL values are displayed
Last for ascending sequences
First for descending sequences
.
오라클 실습
51
Sorting by Multiple Columns
You can order by position to save time
SQL> SELECT
2
FROM
3
ORDER BY
ename, sal * 12 + 1000
emp
2;
You can sort by multiple columns
SQL> SELECT
2
FROM
3
ORDER BY
ename, empno, sal
emp
sal, ename desc;
The order of ORDER BY list is order of sort
.
You can sort by a column that is not in the SELECT list
오라클 실습
52
Limiting Rows Selected
Restrict the rows returned by using the WHERE clause
The WHERE clause follows the FROM clause
Conditions consist of the following:
• Column name, expression, constant
• Comparison operator
• Literal
Example
SQL> SELECT ename, deptno, sal
2
FROM emp
3
WHERE deptno = 10;
DEPTNO
--------10
10
10
SAL
--------2450
5000
1300
.
ENAME
---------CLARK
KING
MILLER
오라클 실습
53
The extended query block
SELECT
expr
FROM
table
[WHERE
condition]
[ORDER BY
expr];
condition is composed of column names, expressions, constants,
and comparison operators
Comparison operators are divided into two categories:
Logical
SQL
.
오라클 실습
54
Comparison and Logical Operators
Logical comparison operators
=
>
>=
<
<=
SQL comparison operators
BETWEEN … AND …
IN (list)
LIKE
IS NULL
오라클 실습
AND
OR
NOT
.
Logical operators
55
Negating Expressions
Sometimes it is easier to exclude rows you know you do not
want
Logical Operators
• !=, <>, ^=
SQL Operators
• NOT BETWEEN
• NOT IN
• NOT LIKE
.
• IS NOT NULL
오라클 실습
56
Negating Expressions
Example
SQL> SELECT ename, deptno, sal
2
FROM emp
3
WHERE deptno != 10;
DEPTNO
--------20
30
30
20
30
30
20
30
20
30
20
SAL
--------800
1600
1250
2975
1250
2850
3000
1500
1100
950
3000
.
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
SCOTT
TURNER
ADAMS
JAMES
FORD
오라클 실습
57
Character Strings and Dates
Character strings and dates are enclosed within single
quotation marks
Number values are not enclosed within quotation marks
Character values are case-sensitive
The default date format is ‘YY/MM/DD’
Example
ename, job
emp
hiredate = ‘1987/05/23’;
.
SQL> SELECT
2
FROM
3
WHERE
오라클 실습
58
BETWEEN Operators
Use the BETWEEN operator to test for values between, and
inclusive of, a range of values
SQL> SELECT
2
FROM
3
WHERE
4
JOB
--------SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
PRESIDENT
SALESMAN
CLERK
ANALYST
CLERK
HIREDATE
-------81/02/20
81/02/22
81/04/02
81/09/28
81/05/01
81/06/09
81/11/17
81/09/08
81/12/03
81/12/03
82/01/23
.
ENAME
---------ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD
MILLER
ename, job, hiredate
emp
hiredate BETWEEN '1981/01/01'
AND '1982/12/31';
오라클 실습
59
IN SQL Operators
Use IN to test for values in a list
SQL> SELECT deptno, dname
2 FROM dept
3 WHERE deptno IN (10,30);
DNAME
-------------SALES
ACCOUNTING
.
DEPTNO
--------30
10
오라클 실습
60
LIKE SQL Operator
You can use the LIKE operator to perform wildcard searches
of valid search string values
Search conditions can contain either literal characters or
numbers
“%” denotes none or many characters
“_” denotes one character
Example
ename, job
emp
ename LIKE ‘M%’;
.
SQL> SELECT
2
FROM
3
WHERE
오라클 실습
61
The “ESCAPE” Keyword
Display the names of companies whose name contains “X_Y”
name
s_customer
name LIKE ‘%X\_Y%’ ESCAPE ‘\’;
.
SQL> SELECT
2
FROM
3
WHERE
오라클 실습
62
IS NULL SQL Operator
Test for null values with the IS NULL operator
Do not use the = operator
SQL> SELECT empno, ename, mgr
2
FROM emp
3
WHERE mgr IS NULL;
EMPNO
--------7839
ENAME
---------KING
MGR
---------
SQL> SELECT empno, ename, mgr
2
FROM emp
3
WHERE mgr = ‘’;
선택된 레코드가 없습니다.
선택된 레코드가 없습니다.
.
SQL> SELECT empno, ename, mgr
2
FROM emp
3
WHERE mgr = NULL;
오라클 실습
63
Multiple Conditions
Use complex criteria
Combine conditions with AND OR operators
AND requires both conditions to be TRUE
SELECT
FROM
WHERE
AND
empno, ename, mgr
emp
deptno = 20
job = ‘CLERK’;
.
SQL>
2
3
4
오라클 실습
64
Multiple Conditions
OR requires either condition to be TRUE
SQL> SELECT empno, ename, mgr
2 FROM emp
3 WHERE deptno = 20 OR job = 'CLERK';
ENAME
---------SMITH
JONES
SCOTT
ADAMS
JAMES
FORD
MILLER
MGR
--------7902
7839
7566
7788
7698
7566
7782
.
EMPNO
--------7369
7566
7788
7876
7900
7902
7934
오라클 실습
65
Rules of Precedence
Override rules of precedence by using parentheses
Operator
All comparison operators
AND
OR
.
Order Evaluated
1
2
3
오라클 실습
66
SQL*Plus Editing Commands
A[PPEND] text
C[HANGE] /old / new
CL[EAR] BUFF[ER]
DEL
I[NPUT] text
L[IST] n
n text
.
RUN
오라클 실습
67
SQL*Plus File Commands
SAVE filename
GET filename
START filename
@ filename
EDIT filename
.
SPOOL [filename | ON | OFF]
오라클 실습
68
.
2-2.
Joins
오라클 실습
69
What Is a Join?
A join is used to query data from more than one table
Rows are joined using common values, typically primary and
foreign key values
Join methods
Equijoin
Non-equijoin
Outer join
Self join
.
오라클 실습
70
Cartesian Product
A Cartesian product is formed when
A join condition is omitted
A join condition is invalid
All rows in the first table are joined to all rows in the second table
To avoid a Cartesian product, always include a valid join
condition in a WHERE clause
ename, dname
emp, dept;
.
SQL> SELECT
2
FROM
오라클 실습
71
Simple Join Query: Syntax
SQL> SELECT
2
FROM
3
WHERE
table.column, table.column
table1, table2
table1.column1 = table2.column2
Write the join condition in the WHERE clause
Precede each column name with the table name for clarity
Column names must be prefixed with the table name when the
.
same column name appears in more than one table
오라클 실습
72
Equijoin
EMP
DEPTNO
------------20
30
10
20
30
30
30
30
30
20
20
DEPTNO
---------10
30
10
20
30
30
30
30
30
20
20
NAME
------------ACCOUNTING
SALES
ACCOUNTING
RESEARCH
SALES
SALES
SALES
SALES
SALES
RESEARCH
RESEARCH
.
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
DEPT
오라클 실습
73
Qualifying Ambiguous Column Names
Use table prefixes to qualify column names that are in multiple
tables
Improve performance by using table prefixes
Distinguish columns that have identical names but reside in
.
different tables by using column aliases
오라클 실습
74
Additional Search Conditions
using the AND Operator
EMP
DEPTNO
------------20
30
10
20
30
30
30
30
30
20
20
DEPTNO
---------10
30
10
20
30
30
30
30
30
20
20
ENAME
---------KING
오라클 실습
NAME
------------ACCOUNTING
SALES
ACCOUNTING
RESEARCH
SALES
SALES
SALES
SALES
SALES
RESEARCH
RESEARCH
DEPTNO
------------20
.
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
DEPT
75
Additional Search Conditions
using the AND Operator: Example
Question
SELECT
FROM
WHERE
AND
emp.ename, emp.deptno,
dept.name
emp, dept
emp.deptno = dept.deptno
INITCAP(emp.ename) = ‘King’;
.
SQL>
2
3
4
5
Display employee KING’s name, emp.deptno, and department name
오라클 실습
76
Table Aliases
Qualify columns with table aliases
Are valid only for that SELECT statement
Qualify a column reference with the table alias instead of the
table name once you create the alias
SELECT
FROM
WHERE
AND
e.ename, e.deptno, d.name
s_emp e, s_dept d
e.deptno = d.id
INITCAP(e.ename) = ‘KING’;
.
SQL>
2
3
4
오라클 실습
77
Table Aliases
Guidelines
Table aliases can be up to 30 characters in length, but the sorter they are
the better
If a table alias is used for a particular table name in the FROM clause,
then that table alias must be substituted for the table name throughout the
SELECT statement
Table aliases should be meaningful
the table alias is only valid for the current SELECT statement
.
오라클 실습
78
Non-Equijoins
Non-equijoins result when no column in one table corresponds
directly to a column in the second table
The join condition contains an operator other than equal (=)
EMP
SAL
------------5000
2850
2450
2975
1250
1600
1500
950
GRADE
---------1
2
3
4
5
LOSAL
--------700
1201
1401
2001
3001
HISAL
--------1200
1400
2000
3000
9999
“salary in the EMP table is between low salary
and high salary in the SALGRADE table”
.
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
…
SALGRADE
오라클 실습
79
Non-Equijoins
Example
SQL> SELECT e.ename, e.sal, s.grade
2 FROM emp e, salgrade s
3 WHERE e.sal
4 BETWEEN s.losal AND s.hisal;
SAL
--------800
1100
950
1250
1250
1300
1600
1500
2975
2850
2450
3000
3000
5000
GRADE
--------1
1
1
2
2
2
3
3
4
4
4
4
4
5
.
ENAME
---------SMITH
ADAMS
JAMES
WARD
MARTIN
MILLER
ALLEN
TURNER
JONES
BLAKE
CLARK
SCOTT
FORD
KING
오라클 실습
80
Outer Joins: Syntax
Use an outer join to see rows that do not normally meet the join
condition
Outer join operator is the plus sign (+)
Place the operator on the side of the join where there is no
value to join to
table.column, table.column
table1, table2
table1.column1 = table2.column2 (+);
SQL> SELECT
2
FROM
3
WHERE
table.column, table.column
table1, table2
table1.column1 (+) = table2.column2;
.
SQL> SELECT
2
FROM
3
WHERE
오라클 실습
81
Outer Joins
The outer join operator can only appear on one side of the
expression
A condition involving an outer join may not
use the IN operator
Be linked to another condition by the OR operator
.
오라클 실습
82
Outer Joins: Example
Display the department name and the employee name for all
departments even if the department has no employee
오라클 실습
SQL> SELECT dname, ename
2
FROM emp e, dept d
3
WHERE e.deptno = d.deptno;
SQL> SELECT dname, ename
2
FROM emp e, dept d
3
WHERE e.deptno (+) = d.deptno;
DNAME
-------------RESEARCH
SALES
SALES
RESEARCH
SALES
SALES
ACCOUNTING
RESEARCH
ACCOUNTING
SALES
RESEARCH
SALES
RESEARCH
ACCOUNTING
DNAME
-------------RESEARCH
SALES
SALES
RESEARCH
SALES
SALES
ACCOUNTING
RESEARCH
ACCOUNTING
SALES
RESEARCH
SALES
RESEARCH
ACCOUNTING
OPERATIONS
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
.
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
83
Self Joins
Join rows in a table to rows in the same table by using a self
join
Simulate two tables in the FROM clause by creating two aliases
for the table
Example
Display the names of employees and their respective managers
worker.ename || ‘ works for ‘ ||
manager.ename NOTE
emp worker, emp manager
worker.mgr = manager.empno;
.
SQL> SELECT
2
3
FROM
4
WHERE
오라클 실습
84
Self Joins: Example
emp “worker”
MGR
--------7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
EMPNO ENAME
--------- ---------7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
MGR
--------7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
.
EMPNO ENAME
--------- ---------7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
emp “manager”
오라클 실습
85
.
2-3.
Subqueries
오라클 실습
86
What Is a Subquery?
SELECT Syntax
Main query
SELECT …
FROM …
WHERE…
subquery
.
SELECT Syntax
(SELECT …
FROM …
WHERE…);
오라클 실습
87
Subqueries: Syntax
SELECT
FROM
WHERE
select_list
table
expr operator
(
SELECT
FROM
select_list
table);
A subquery is a SELECT statement embedded in a clause of
another SQL statement
The subquery executes once before the main query
.
The result of the subquery is used by the main outer query
오라클 실습
88
Subquery Guidelines
A subquery must be enclosed in parentheses
Two classes of comparison operators are used in subqueries:
single and multiple row
A subquery must appear on the right side of the operator
Subqueries can be used in many SQL commands
.
Subqueries cannot contain an ORDER BY clause
오라클 실습
89
Single Row Subqueries
Question
Retrieve the name and job of the employees in the same department as
SMITH
ename, job
emp
deptno =
(
select deptno
from emp
where upper(ename) = ‘SMITH’);
.
SQL> SELECT
2
FROM
3
WHERE
4
5
6
오라클 실습
90
Group function in a Subquery
Question
Display the name, job, and salary for all employees who make less than
the average salary
ename, job, sal
emp
sal =
(
select avg(sal)
from emp
);
.
SQL> SELECT
2
FROM
3
WHERE
4
5
오라클 실습
91
Subquery in the FROM Clause
Add a subquery in the FROM clause
Acts as a view
*
(
SELECT
empno, ename, job
FROM
emp
WHERE
deptno = 30 ) r
upper(r.job) LIKE ‘SAL%’;
.
SQL> SELECT
2
FROM
3
4
5
WHERE
오라클 실습
92
Errors with Subqueries
If ou write a subquery that returns more than one row and you
use a single row comparison operator, you will get an error
To correct the eror, change the comparison operator to IN, a
multiple row operator
.
SQL> SELECT
ename, job, deptno
2
FROM
emp
3
WHERE
ename =
4
(
SELECT
MIN(ename)
5
FROM
emp
6
GROUP BY deptno);
4행에 오류:
ORA-01427: 단일 행 부속 질의에 의해 2개 이상의 행이 리턴되었습니다
오라클 실습
93
Multiple Row Subqueries
A multiple row subquery returns many rows
You must use a multiple row operator in the WHERE clause,
for example the IN operator
This group function in the subquery returns man7 values
.
SQL> SELECT
ename, job, deptno
2
FROM
emp
3
WHERE
ename IN
4
(
SELECT
MIN(ename)
5
FROM
emp
6
GROUP BY deptno);
4행에 오류:
ORA-01427: 단일 행 부속 질의에 의해 2개 이상의 행이 리턴되었습니다
오라클 실습
94
Multiple Row Subqueries
Return more than one row
Use multiple-row comparison operators
Meaning
IN
Equal to any member in the list
ANY
Compare value to each value returned by the subquery
ALL
Compare value to every value returned by the subquery
.
Operator
오라클 실습
95
HAVING Clause with Subqueries
You can also use subqueries in the HAVING clause
The Oracle Server executes subqueries first
The Server returns results into the main query’s HAVING
clause
SQL> SELECT deptno, MIN(sal)
2 FROM emp
3 GROUP BY deptno
4 HAVING MIN(sal) >
5
(SELECT MIN(sal)
6
FROM emp
7
WHERE deptno = 20);
.
DEPTNO MIN(SAL)
--------- --------10
1300
30
950
오라클 실습
96
Correlated Subqueries
A main query and a subquery are referenced interactively
A subquery executes more than once while the main query
executes
Example
SQL> select * from emp e
2
where sal = (select max(sal) from emp where deptno = e.deptno);
ENAME
---------BLAKE
SCOTT
KING
FORD
JOB
--------MANAGER
ANALYST
PRESIDENT
ANALYST
MGR
--------- 7839
7566
7566
HIREDATE SAL
--------------81/05/01
2850
87/04/19
3000
81/11/17
5000
81/12/03
3000
COMM
---------
DEPTNO
--------30
20
10
20
.
EMPNO
--------7698
7788
7839
7902
오라클 실습
97
Correlated Subqueries
Example
SQL> select * from emp e
2
where sal = (select MAX(sal) from emp where emp.job = e.job);
ENAME
---------ALLEN
JONES
SCOTT
KING
FORD
MILLER
JOB
--------SALESMAN
MANAGER
ANALYST
PRESIDENT
ANALYST
CLERK
MGR
--------7698
7839
7566
7566
7782
HIREDATE SAL
---------------81/02/20 1600
81/04/02 2975
87/04/19 3000
81/11/17 5000
81/12/03 3000
82/01/23 1300
COMM
--------300
DEPTNO
--------30
20
20
10
20
10
.
EMPNO
--------7499
7566
7788
7839
7902
7934
오라클 실습
98
.
Chapter 3
Oracle Functions
오라클 실습
99
.
Single Row Function
오라클 실습
100
Two Types of SQL Functions
Single row functions
Character
Number
Date
Conversion
Multiple row functions
Group
SINGLE
ROW
MULTI
ROW
.
FUNCTION
오라클 실습
101
Single Row Functions: Syntax
Single row functions
Manipulate data items
Accept arguments and return one value
Act on each row returned
Return one result per row
Modify the data type
Can be nested
.
Function_name ( column | expression, [arg1, arg2, …])
오라클 실습
102
Character Functions
Convert to lowercase
UPPER
convert to uppercase
INITCAP
convert to initial capitalization
CONCAT
Concatenate values
SUBSTR
Return substring
LENGTH
Return number of characters
NVL
Converts a null value
.
LOWER
오라클 실습
103
Case Conversion Functions
Convert case for character string
LOWER(‘SQL Course’)
sql course
UPPER(‘SQL Course’)
SQL COURSE
INITCAP(‘SQL Course’)
Sql Course
.
오라클 실습
104
Case Conversion Functions
Example
SQL> SELECT LOWER(ename) name,
2
INITCAP(job || 'JOBS') job
3
FROM emp;
SQL> SELECT job
2
FROM emp
3
WHERE job = 'clerk';
NAME
---------smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
james
ford
miller
선택된 레코드가 없습니다.
SQL> SELECT job
2 FROM emp
3 WHERE lower(job) = 'clerk';
JOB
--------CLERK
CLERK
CLERK
CLERK
.
JOB
------------Clerkjobs
Salesmanjobs
Salesmanjobs
Managerjobs
Salesmanjobs
Managerjobs
Managerjobs
Analystjobs
Presidentjobs
Salesmanjobs
Clerkjobs
Clerkjobs
Analystjobs
Clerkjobs
오라클 실습
105
Character Manipulation Functions
Manipulate character strings
CONCAT(‘Good’,’String’)
GoodString
SUBSTR(‘string’,1,3)
Str
LENGTH(‘String’)
6
CONCAT (dname, loc) department
dept
deptno = 10;
SQL> SELECT
2
FROM
3
WHERE
ename, job, LENGTH(job)
emp
SUBSTR(job,1,3) = ‘SAL’;
.
SQL> SELECT
2
FROM
3
WHERE
오라클 실습
106
Number Functions
ROUND
Rounds value to specified decimal
TRUNC
Truncates value to specified decimal
MOD
Returns remainder of division
.
오라클 실습
107
ROUND and TRUNC Functions
45.92
ROUND(45.923,0)
46
ROUND(45.923,-1)
50
TRUNC(45.923,2)
45.92
TRUNC(45.923)
45
TRUNC(45.923,-1)
40
.
ROUND(45.923,2)
오라클 실습
108
MOD function
Find the remainder of one value divided by another value using
MOD
100
.
MOD(1600,300)
오라클 실습
109
Arithmetic Operators with Dates
Add or subtract a number to or from a date for a resultant date
value
Subtract two dates to find the number of days between those
dates
Add hours to a date by dividing the number of hours by 24
Result
date
date
Number of days
date
Description
Adds a number of days to a date
Subtracts a number of days from a date
Subtracts one date from another
Adds a number of hours to a date
.
Operation
date + number
date - number
date - date
date + number/24
오라클 실습
110
Arithmetic Operators with Dates
Question
For employees in department 10, display the employee name and number
of weeks employed
SYSDATE is a function returning date and time
Example
SQL> SELECT ename, (SYSDATE - hiredate) / 7 WEEKS
2
FROM emp
3
WHERE deptno = 10;
WEEKS
--------1081.6326
1058.6326
1049.0612
.
ENAME
---------CLARK
KING
MILLER
오라클 실습
111
Date Functions
MONTHS_BETWEEN (date1, date2)
Number of months between two dates
ADD_MONTHS (date, n)
Add calendar months to date
NEXT_DAY (date, n)
Next day of the date specified
LAST_DAY (date)
Last day of the month
ROUND (date[,’fmt’]) / TRUNC (date[,’fmt’])
Round to date at midnight
Removes time portion from date
.
오라클 실습
112
Oracle Date Format
Oracle stores dates in an internal numeric format
Century, year, month, day, hours, minutes, seconds
Default date display is YY/MM/DD
DUAL is a dummy table used to view SYSDATE
SYSDATE
SYS.DUAL;
.
SQL> SELECT
2
FROM
오라클 실습
113
Date Functions
MONTHS_BETWEEN(‘95/09/01’,’94/01/11’)
Result: 19.677419
ADD_MONTHS(‘94/01/11’,6)
Result: ‘94/07/11’
NEXT_DAY(‘95/09/01’, 4)
Result: ‘95/09/06’
LAST_DAY(‘95/09/01’)
.
Result: ‘95/09/30’
오라클 실습
114
Conversion Function: Overview
TO_CHAR converts a number or date string to a character
string
TO_NUMBER converts a character string containing digits to
a number
TO_DATE converts a character string of a date to a date value
conversion functions can use a format model composed of
.
many elements
오라클 실습
115
TO_CHAR Function with Dates: Syntax
TO_CHAR (date, ‘fmt’)
The format model
Must be enclosed in single quotation marks and is case-sensitive
Can include any valid date format element
has an fm element to remove padded blanks or suppress leading zeros
Is separated from the date value by a comma
.
오라클 실습
116
Date Format Model Elements
YYYY represents the full year in numbers
YEAR represents the year spelled out
MM represents the 2-digit value for month
MONTH represents the full name of the month
DY represents the 3-letter abbreviation of the day of the week
.
DAY represents the full name of the day
오라클 실습
117
Date Format Model Elements
Time elements format the time portion of the date
HH24:MI:SS AM
15:45:32 PM
Add character strings by enclosing them in double quotation
marks
DD “ of “ MONTH
12 of OCTOBER
Number suffixes spell out numbers
ddspth
fourteenth
.
오라클 실습
118
Date Format Model Elements
Example
SQL> SELECT ename,
2
TO_CHAR(hiredate, 'YYYY/MM/DD HH12:MI:SS') “H_DATE”
3
FROM emp;
H_DATE
------------------1980/12/17 12:00:00
1981/02/20 12:00:00
1981/02/22 12:00:00
1981/04/02 12:00:00
1981/09/28 12:00:00
1981/05/01 12:00:00
1981/06/09 12:00:00
1987/04/19 12:00:00
1981/11/17 12:00:00
1981/09/08 12:00:00
1987/05/23 12:00:00
1981/12/03 12:00:00
1981/12/03 12:00:00
1982/01/23 12:00:00
.
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
오라클 실습
119
Date Format Model Elements: Reference
Sample Valid Date Format Elements
Description
SCC or CC
Century; S prefixes BC date with
Years in dates YYYY or SYYYY
Year, S prefixes BC date with -.
YYY or YY or Y
Last 3,2, or 1 digit9s) of year.
Y,YYY
Year with comma in this position
IYYY,IYY,IY,I
4,3,2, or 1 digit year based on the ISO standard
SYEAR or YEAR
Year spelled out; S prefixes BC date with -.
BC or AD
BC/AD indicator.
B.C. or A.D.
BC/AD indicator with periods.
Q
Quarter of year.
MM
Month, 2-digit value
MONTH
Name of month padded with blanks to length of 9 char
MON
Name of month, 3-letter abbreviation
RM
Roman numeral month
.
Element
오라클 실습
120
Date Format Model Elements: Reference
Sample Valid Date Format Elements
Description
WW or W
Week of year or month.
DDD or DD or D
Name of day padded with blanks to length of 9 char
DAY
Name of day; 3-letter abbreviation
DY
Name of day; 3-letter abbreviation
J
Julian day; the number of days
since 31 December 4713 BC
.
Element
오라클 실습
121
Date Format Model Elements: Reference
Time Formats
Element
Description
AM or PM
Meridian indicator
A.M or P.M.
Meridian indicator with periods
H or HH12 or HH24
Hour of day or hour(1-12) or hour (0-23)
MI
Minute(0-59)
SS
Second(0-59)
SSSSS
Seconds past midnight(0-86399)
Other Formats
Description
/.,
Punctuation is reproduced in the result
“ of the “
Quoted string is reproduced in the result
.
Element
오라클 실습
122
Date Format Model Elements: Reference
Specifying Suffixes to Influence Number Display
Element
Description
TH
Ordinal number (for example, DDTH for 4 th
SP
Spelled-out number ( for example, DDSP for FOUR)
Spelled-out ordinal numbers
(for example, DDSPTH for FOURTH)
.
SPTH or THSP
오라클 실습
123
TO_CHAR Function with Numbers: Syntax
TO_CHAR (number, ‘fmt’)
use these formats with the TO_CHAR function to display a
character value as a number
9
represents a number
0
forces a zero to be displayed
$
places a floating dollar sign
L
uses the floating local currency symbol
.
Prints a decimal point
,
prints a thousand indicator
.
오라클 실습
124
Nesting Single Row Functions
Single row functions can be nested to any level
Nested functions are evaluated from deepest level to the least
deep level
F3 ( F2 ( F1 ( col, arg1 ), arg2 ), arg3 )
Step 1
Step 2
.
Step 3
오라클 실습
125
.
Group Functions
오라클 실습
126
Group functions
Group functions operate on sets of rows to give one result per
group
Group functions appear in both SELECT lists and HAVING
clauses
The GROUP BY clause in the SELECT statement
Divides rows into smaller groups
.
The HAVING clause restricts result groups
오라클 실습
127
GROUP BY and HAVING Clauses in the
SELECT Statement: Syntax
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
column, group_function
table
condition]
group_by_expression]
group_condition]
column];
GROUP BY divides rows into smaller groups
.
HAVING further restricts the result groups
오라클 실습
128
Group Functions
AVG
COUNT
MAX
MIN
STDDEV
SUM
.
VARIANCE
오라클 실습
129
Group Functions: Example
You can use MAX and MIN for any datatype
SQL> SELECT min(ename), max(ename)
2 FROM emp;
MIN(ENAME) MAX(ENAME)
---------- ---------ADAMS
WARD
You can use AVG and SUM against columns that can store
numeric data
SQL> SELECT avg(sal), max(sal), min(sal), sum(sal)
2 FROM emp
3 WHERE lower(job) LIKE 'sal%';
.
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)
--------------------------------1400
1600
1250
5600
오라클 실습
130
COUNT Function: Examples
COUNT(*) returns the number of rows in a table
SQL> SELECT count(*)
2
FROM emp
3
WHERE deptno = 30;
COUNT(*)
--------6
COUNT(expr) returns the number for non-null rows.
SQL> SELECT count(comm)
2
FROM emp
3
WHERE deptno = 30;
.
COUNT(COMM)
----------4
오라클 실습
131
The GROUP BY Clause: Syntax
SELECT
FROM
[WHERE
[GROUP BY
[ORDER BY
column, group_function
table
condition]
group_by_expression]
column];
Divide rows in a table into smaller groups by using the GROUP
BY clause
Include the column list in the GROUP BY clause if columns are
used in the SELECT clause
.
Override the default sort order by using the ORDER BY clause
오라클 실습
132
Without the GROUP BY Clause
SQL> SELECT
2
FROM
3
WHERE
ENAME
---------ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
DEPTNO
--------30
30
30
30
30
30
Department 30 displays
six times because it
appears as the
department number of
six employees
.
EMPNO
--------7499
7521
7654
7698
7844
7900
empno, ename, deptno
emp
deptno = 30;
오라클 실습
133
With the GROUP BY Clause
SELECT
FROM
WHERE
GROUP BY
DEPTNO
--------30
오라클 실습
Number
---------6
deptno, count(*) “Number”
emp
deptno = 30
deptno;
The GROUP BY clause
displays one line of data
for each department
retrieved in the WHERE
clause, and COUNT(*)
displays the number of
employees in each
department (group)
displayed
134
.
SQL>
2
3
4
The GROUP BY Clause: Examples
Job and monthly salary for each job title
SQL> SELECT job, sum(sal) PAYROLL
2
FROM emp
3
WHERE job NOT LIKE 'PRE%'
4
GROUP BY job
5
ORDER BY sum(sal);
PAYROLL
--------4150
5600
6000
8275
.
JOB
--------CLERK
SALESMAN
ANALYST
MANAGER
오라클 실습
135
The GROUP BY Clause
The GROUP BY column does not have to be in the SELECT
clause
All columns in the SELECT list that are not in group functions
must be in the GROUP BY clause
The results are more meaningful if the GROUP BY column is
.
in the SELECT clause
오라클 실습
136
Illegal Queries Using Group Functions
Any column or expression in the SELECT list that is not an
aggregate function must be in the GROUP BY clause
You will see an error message if you do not correctly create
your GROUP BY clause
.
SQL> SELECT loc, count(dname)
2
FROM dept;
SELECT loc, count(dname)
*
1행에 오류:
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
오라클 실습
137
Illegal Queries Using Group Functions
You cannot use the WHERE clause to restrict groups
Use the HAVING clause to restrict groups
.
SQL> SELECT deptno, AVG(sal)
2 FROM emp
3 WHERE avg(sal) > 2000
4 group by deptno;
WHERE avg(sal) > 2000
*
3행에 오류:
ORA-00934: 그룹 함수는 허가되지 않습니다
오라클 실습
138
Groups Within Groups: Example
Return summary results for groups and subgroups by listing
more than one GROUP BY column
Determine the default sort order of the results by the order of
the columns in the GROUP BY clause
deptno, job, COUNT(*)
emp
deptno,job;
SQL> SELECT
2
FROM
3
GROUP BY
job, deptno, COUNT(*)
emp
job, deptno;
.
SQL> SELECT
2
FROM
3
GROUP BY
오라클 실습
139
Display Specific Rows
by Using the WHERE Clause
SQL> SELECT
2
FROM
3
WHERE
JOB
------------PRESIDENT
WHERE clause
(restrict Rows)
Display a specific
employee as
restricted in the
WHERE clause
.
ENAME
-------------KING
ename, job
emp
job LIKE ‘PRE%’;
오라클 실습
140
Display Specific Groups
by Using the HAVING clause
SQL>
2
3
4
5
SELECT
FROM
WHERE
HAVING
job, 12 * AVG(sal) “ANN. SAL.”,
COUNT(*) “# of Emp.”
emp
job
COUNT(*) > 2;
HAVING clause (Restrict Groups)
JOB
--------CLERK
MANAGER
SALESMAN
ANN. SAL.
--------12450
33100
16800
# of Emp.
--------4
3
4
.
Display specific groups of job titles as
restricted in the HAVING clause
오라클 실습
141
The HAVING Clause: Syntax
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
column, group_function
table
condition]
group_by_expression]
group_condition]
column];
Use the HAVING clause to further restrict groups
Step 1: Rows are grouped
Step 2: the group function is applied to the group
Step 3: Groups matching the HAVING condition are displayed
.
오라클 실습
142
The HAVING Clause: Example
Question
Display the job and total monthly salary for each job with a total payroll
exceeding 5000. Do not include presidents, and sort the list by the total
monthly salary
SQL> SELECT job, sum(sal) PAYROLL
2 FROM emp
3 WHERE job NOT LIKE 'PRE%'
4 GROUP BY job
5 HAVING sum(sal) > 5000
6 ORDER BY sum(sal);
PAYROLL
--------5600
6000
8275
.
JOB
--------SALESMAN
ANALYST
MANAGER
오라클 실습
143
The HAVING Clause: Example
You can use the GROUP BY clause without using a group
function in the SELECT list
If you restrict rows based on the result of a group function,
then you must have a GROUP BY clause as well as the
.
HAVING clause
오라클 실습
144
.
Chapter 4
Manipulating Data
오라클 실습
145
Data Manipulation and
Transaction Control Commands
Adds a new row to the table
UPDATE
Modifies existing rows in the table
DELETE
Removes existing rows from the table
COMMIT
Makes all pending changes permanent
SAVEPOINT
Allows a rollback to that savepoint marker
ROLLBACK
Discards all pending data changes
.
INSERT
오라클 실습
146
Inserting New Rows into a Table: Syntax
Add new rows to a table by using the INSERT command
INSERT INTO
VALUES
table [ (column [, column …] ) ]
( value [, value …]);
.
Only one row is inserted at a time with this syntax
오라클 실습
147
Inserting New Rows: Example
Insert a new row containing values for each column
Optionally list the columns in the INSERT clause
SQL> INSERT INTO bonus
2
VALUES
( sjkim, clerk, 1000, 50 );
List values in the default order of the columns in the table
Enclose character and date values within single quotation
.
marks
오라클 실습
148
Inserting New Rows with Null Values
Implicit Method
Omit the column from the column list
SQL> INSERT INTO bonus( ename, job, bonus )
2
VALUES
( ‘kdan’, ‘sales’, 500 );
Explicit Method
Specify the NULL keyword or the empty string (‘’) in the VALUES list
.
SQL> INSERT INTO bonus
2
VALUES
( ‘swpark’, ‘teller’, 1500, NULL );
오라클 실습
149
Inserting Special Values
The USER function records the current user name
The SYSDATE function records the current date and time
SQL> INSERT INTO emp( empno, ename, hiredate, sal, comm )
2
VALUES
( 9000, user, sysdate, 500, NULL );
Verify that the row was added to the table
empno, ename, hiredate, sal, comm
emp
empno = 9000;
.
SQL> SELECT
2
FROM
3
WHERE
오라클 실습
150
Inserting Specific Data and Time Values
TO_DATE function
Override the default settings to insert a specific date and time value
SQL> INSERT INTO emp( empno, ename, hiredate, sal, comm )
2
VALUES
( 9001, user || ‘s’,
3
TO_DATE (‘01-JAN-96 08:00’,
4
‘DD-MON-YY HH:MI’),
5
NULL, 0);
Default values
Default century is the current century
Default time is midnight
.
오라클 실습
151
Copying Rows from Another Table
Write your INSERT command with a subquery
SQL>
2
3
4
INSERT INTO bonus( ename, job, sal )
SELECT
ename, job, sal
FROM
emp
WHERE
hiredate < ‘82/01/01’;
Do not use the VALUES clause
Match the number of columns in the INSERT clause to those in
.
the subquery
오라클 실습
152
Updating Rows in a Table: Syntax
Modify existing rows with the UPDATE command
UPDATE
SET
[WHERE
table
column = value [, column = value]
condition];
If you omit the WHERE clause, all the rows in the table will e
.
updated
오라클 실습
153
Updating All Rows in the Table
All rows in the table will be updated if you do not add the
WHERE clause
bonus
comm
= 10
.
SQL> UPDATE
2
SET
오라클 실습
154
Updating Rows: Examples
Transfer employee number 9000 to department 10
SQL> UPDATE
2
SET
3
WHERE
emp
deptno = 10
empno = 9000;
Transfer employee number 9001 to department 30 and change
the salary to 2550
emp
deptno = 30, sal = 2550
empno = 9001;
.
SQL> UPDATE
2
SET
3
WHERE
오라클 실습
155
Updating Rows: Integrity Constraint Error
An error occurs because department number 60 does not exist
in the department table
.
SQL> update emp
2 set deptno = 60
3 where deptno = 10;
update emp
*
1행에 오류:
ORA-02291: 무결성 제약조건(SCOTT.FK_DEPTNO)이
위배되었습니다- 부모 키가 없습니다
오라클 실습
156
Deleting Rows from a Table: Syntax
Remove existing rows by using the DELETE command
DELETE FROM
[WHERE
table
condition];
Remove all information about employees who started after
January 1, 1996
.
SQL> DELETE FROM
emp
2
WHERE
hiredate >
3
TO_DATE(‘01.01.1996’, ‘DD.MM.YYYY’);
오라클 실습
157
Deleting Rows: Example
Delete all the rows in the table by excluding the WHERE clause
SQL> DELETE FROM
bonus;
Confirm the deletions
*
bonus;
.
SQL> SELECT
2
FROM
오라클 실습
158
Deleting Rows: Integrity Constraint Error
If you try to delete a row that contains a primary key used as a
foreign key in another table, you will experience an integrity
constraint error (We will treat this situation later!!)
.
SQL> delete from dept
2 where dname = 'SALES';
delete from dept
*
1행에 오류:
ORA-02292: 무결성 제약조건(SCOTT.FK_DEPTNO)이
위배되었습니다- 자식 레코드가 발견되었습니다
오라클 실습
159
Database Transactions
Contain one of the following statements:
DML commands that make up one consistent change to the data
One DDL command
One DCL command
Begin when the first executable SQL command is executed
End with one of the following events:
COMMIT or ROLLBACK
DDL or DCL command executes (automatic commit)
Errors, exit, or system crash
.
오라클 실습
160
Advantages of COMMIT and ROLLBACK
Ensure data consistency
Preview data changes before making changes permanent
.
Group logically related operations
오라클 실습
161
Controlling Transactions
COMMIT
ROLLBACK
ROLLBACK to A ROLLBACK to B
.
ROLLBACK
오라클 실습
162
Implicit Transaction Processing
An automatic commit occurs under the following circumstances:
A DDL command is issued, such as CREATE
A DCL command is issued, such as GRANT
A normal exit from SQL*Plus, without explicitly issuing COMMIT or
ROLLBACK
An automatic rollback occurs under an abnormal termination
.
of SQL*Plus or a system failure
오라클 실습
163
State of the Data After COMMIT
Data changes are written to the database
The previous data is permanently lost
All users can view the results
Locks on the affected rows are released; those rows are
.
available for other users to manipulate
오라클 실습
164
Committing Data: Example
Create a new Education department with at least on employee
SQL> INSERT INTO dept(deptno, dname, loc)
2
VALUES
(50, ‘Education’, ‘Seoul’);
Add at least on employee
SQL> UPDATE
2
SET
3
WHERE
emp
deptno = 50
id = 7839;
Commit the changes
.
SQL> COMMIT;
오라클 실습
165
State of the Data After ROLLBACK
Discard all pending changes
Data changes are undone
Previous state of the data is restored
Locks on the affected rows are released
.
SQL> DELETE FROM salgrade;
SQL> ROLLBACK;
오라클 실습
166
Rolling Back Changes to a Marker
Create a marker within a curent transaction by using the
SAVEPOINT command
Rollback to that marker by using the ROLLBACK TO
오라클 실습
.
SAVEPOINT command
SQL> COMMIT;
SQL> DELETE FROM salgrade where grade = 5;
SQL> SAVEPOINT a;
SQL> UPDATE salgrade set losal = 500;
SQL> SELECT * FROM salgrade;
SQL> SAVEPOINT b;
SQL> DELETE from salgrade where grade in (3,4);
SQL> ROLLBACK TO b;
SQL> SELECT * from salgrade;
SQL> ROLLBACK TO a;
167
Cautions: TRUNCATE
The table, deleted by TRUNCATE TABLE, can not be rolled
back
TRUNCATE TABLE
table;
Example
COMMIT;
INSERT INTO bonus(ename) VALUES(‘sjkim’);
INSERT INTO bonus(ename) VALUES(‘kdan’);
COMMIT;
TRUNCATE TABLE bonus;
ROLLBACK;
SELECT * FROM bonus;
.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
오라클 실습
168
.
Managing Tables
오라클 실습
169
Data Structures
An Oracle database can contain multiple data structures
Table
Stores data
View
Subset of data from one or more tables
Sequence
Generates primary key values
Index
Improves the performance for some queries
.
Define the structures in the database design
오라클 실습
170
Oracle Limitation
Number of table: unlimited
Number of row in one table: unlimited
Number of column in one table: 254
Maximum bytes per one row: 130,306 byte
Maximum string size: 255
Maximum numeric size: 38
Range of Date type: BC 4712/1/1 ~ AD 4712/12/31
Number of index: unlimited
Join frequency: unlimited
.
Subquery level: 255
오라클 실습
171
Creating Tables: Syntax
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr] [column_constraint],
…
[table_constraint]);
You must have specific privileges:
CREATE TABLE
A storage area
.
오라클 실습
172
Data Types
String
char(n)
varchar2(n)
Number
number, number(n), number(p,s)
Date
year/month/day
.
오라클 실습
173
Data Types
Long
Can store long character string (2G byte)
Only one column should be defined in a table
Cannot use index
Cannot use constraints
Cannot use SQL functions, where condition, order by, and group by clause
.
Long Raw
오라클 실습
174
The DEFAULT Option
Specify a default value for a column during an insert
… state_date DATE DEFAULT SYSDATE ...
Legal values are literal value, expression, or SQL function such
as SYSDATE or USER
Illegal values are another column name or a pseudo column
.
the default value datatype must match the column datatype
오라클 실습
175
Naming Rules
Must begin with a letter
Can be 1-30 characters long
Must contain only A-Z, a-z, 0-9, _, $, and #
Must not duplicate the name of another object owned by the
same user
.
Must not be an Oracle Server reserved word
오라클 실습
176
Constraints
Enforce rules at the table levbel
Prevent the deletion of a table if there are dependencies
The following constraint types are valid in Orace:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
.
오라클 실습
177
Constraint Guidelines
Name a constraint or the Server can generate a name by using
the SYS_Cn format
Create a constraint
At the same time as the table si created
After the table has been created
.
Define a constraint at the column or table level
오라클 실습
178
Constraint: Syntax
Column-constraint level
column [CONSTRAINT constraint_name] constraint_type,
Table-constraint level
.
column, …
[CONSTRAINT constraint_name] constraint_type
(column, …),
오라클 실습
179
The NOT NULL Constraint
Ensures that null values are not permitted for the column
Is defined at the column-constraint level
Example
.
CREATE TABLE
friend …
phone
VARCHAR2(15) NOT NULL, …
fname
VARCHAR2(25)
CONSTRAINT friend_ename_nn NOT NULL, ...
오라클 실습
180
The UNIQUE Constraint
Designates a column or combination of columns so that no two
rows in the table can have the same value for this key
Allows null values if the UNIQUE key is based on a single
column
Is defined at either the table or column-constraint level
Automatically creates a UNIQUE index
VARCHAR2(10)
CONSTRAINT s_emp_phone_uk UNIQUE, ...
.
… phone
오라클 실습
181
The PRIMARY KEY Constraint
Creates a primary key for the table; only one primary key is
allowed for each table
Enforces uniqueness of column
Does not allow null values in any part of the primary key
Is defined at either the table or column constraint level
Automatically creates a UNIQUE index
NUMBER(7)
CONSTRAINT s_emp_id_pk PRIMARY KEY, ...
.
… id
오라클 실습
182
The FOREIGN KEY Constraint
Designates a column or combination of columns as a foreign
key
Establishes a relationship between the primary or unique key
in the same table or between tables
is defined at either the table or column constraint level
Must match an existing value in the parent table or be NULL
.
… dept_id
NUMBER(7)
CONSTRAINT s_emp_dept_id_fk
REFERENCES s_dept(id), ...
오라클 실습
183
FOREIGN KEY Constraint Keywords
FOREIGN KEY
Defines the column in the child table at the table constraint level
REFERENCES
identifies the table and column in the parent table
ON DELETE CASCADE
Allows deletion in the parent table and deletion of the dependent rows in
.
the child table
오라클 실습
184
The CHECK Constraint
Defines a condition that each row must satisfy
Expressions not allowed
References to pseudocolumns CURRVAL, NEXTVAL, LEVEL, OR
ROWNUM
Calls to SYSDATE, UID, USER, or USERENV functions
Queries that refer to other values in other rows
.
Is defined at either the table- or the column-constraint level
오라클 실습
185
Create Table: Example
.
SQL> CREATE TABLE s_dept
2
( id
NUMBER(7)
3
CONSTRAINT s_deptid_pk PRIMARY KEY,
4
name
VARCHAR2(25)
5
CONSTRAINT s_dept_name_nn
NOT NULL,
6
region_id
NUMBER(7)
7
CONSTRAINT s_dept_region_id_fk REFERENCES
8
dept (deptno),
9
CONSTRAINT s_dept_name_region_id_uk UNIQUE
10
(name, region_id)
);
오라클 실습
186
Creating a Table by Using a Subquery:
Syntax
Create a table and insert rows by conbining the CREATE
TABLE command and AS subquery option
CREATE TABLE table
[column (, column …)]
AS subquery;
Match number of specified columns to number of subquery
columns
Define columns with column names, default values, and interity
.
constraints only
오라클 실습
187
Creating a Table by Using a Subquery:
Example
Create a table containing all employees in department number
30 in the S_EMP table
CREATE TABLE emp_30
AS
SELECT
empno, ename, hiredate
FROM
emp
WHERE
deptno = 30;
Don’t forget that only the NOT NULL constraint is copied
Confirming Table Creation
.
SQL> DESCRIBE emp_30;
오라클 실습
188
ALTER TABLE
ALTER TABLE command allows you to
Add and modify columns
Add or remove constraints
Enable or disable constraints
DROP TABLE command removes the rows and table structure
Other commands affecting a table are RENAME, TRUNCATE,
COMMENT
An automatic commit occurs when you issue these DDL
.
commands
오라클 실습
189
Adding a Column: Syntax
Add an new column
Define a default value for the new column
Specify that the column must contain a value
ALTER TABLE table
ADD (column datatype [DEFAULT expr] [NOT NULL]
[, column datatype] …);
.
Columns cannot be drooped
오라클 실습
190
Adding a column: Example
Add a COMMENTS column to the S_REGION table
SQL> ALTER TABLE dept
2
ADD (comments CHAR(10));
.
The new column becomes the last column
오라클 실습
191
Modifying a Column: Syntax
Change a column’s datatype, size, default value, and NOT
NULL column constraint
ALTER TABLE table
MODIFY
(column datatype [DEFAULT expr] [NOT NULL]
[, column datatype] …);
Guidelines
Increase a number column’s width or precision
Decrease a column’s width if the column constrains null values or if the
table has no rows
Change the default value for subsequent additions
Define a NOT NULL constraint only if there are no values in the column
.
오라클 실습
192
Modifying a Column
Change the datatype if the column contains null values
Change the default value to affect only subsequent insertions
into the table
Example
Extend the maximum length of the COMMENTS column in the
S_REGION table to 255 characters
SQL> ALTER TABLE emp
2
MODIFY
(comments int);
.
Examples
오라클 실습
193
Adding a Constraint: Syntax
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
Add or drop, but not modify, a constraint
Enable or disable constrains
.
Add a NOT NULL constraint by using the MODIFY clause
오라클 실습
194
Adding a Constraint: Example
Add a foreign key constraint to the S_EMP table indicating
that a manager must already exist as a valid employee in the
S_EMP table
ALTER TABLE emp
ADD CONSTRAINT emp_comments_fk
FOREIGN KEY (commetns)
REFERENCES emp(empno);
.
SQL>
2
3
4
오라클 실습
195
Dropping a Constraint: Examples
Remove the manager constraint from the S_EMP table
SQL> ALTER TABLE
emp
2
DROP CONSTRAINT emp_comments_fk;
Remove the PRIMARY KEY constraint on the DEPT table and
drop the associated ROREIGN KEY constraint on the
EMP.DEPTNO column
dept
PRIARY KEY CASCADE;
.
SQL> ALTER TABLE
2
DROP
오라클 실습
196
Enabling Constraints
Activate an integrity constraint currently disabled in the table
definition by using the ENABLE clause
SQL> ALTER TABLE
emp
2
ENABLEDROP CONSTRAINT emp_comments_fk;
A UNIQUE or PRIMARY KEY index is automatically created
if you enable a UNIQUE or PRIMARY KEY constraint
emp
de;
.
SQL> ALTER TABLE
2
DISABLE CONSTRAINT
오라클 실습
197
Dropping a Table: Syntax
DROP TABLE table [CASCADE CONSTRAINT];
All data in the table is deleted
Any pending transactions are committed
All indexes are dropped
The CASCADE CONSTRAINTS option removes dependent
integrity constraints
.
You cannot rollback this command
오라클 실습
198
Changing the Name of an Object
Execute the RENAME command to change the name of a table,
view, sequence, or synonym
SQL> RENAME dept TO s_dept;
.
You must be the owner of the object
오라클 실습
199
Adding Comments to a Table
You can add comments to a table or column by using the
COMMENT command
SQL> COMMENT ON TABLE
emp
2
IS ‘Employee Information’;
To clear the comment, user the empty string
Comments can be viewed through the following data dictionary
views:
ALL_COL_COMMENTS / USER_COL_COMMENTS
ALL_TAB_COMMENTS / USER_TAB_COMMENTS
.
오라클 실습
200
.
Creating Sequences
오라클 실습
201
What Is a Sequence?
Automatically generates unique numbers
Is a sharable object
Is typically used to create a primary key value
Replaces application code
Speeds up the efficiency of accessing sequence values when
.
cached in memory
오라클 실습
202
Creating a Sequence: Syntax
Define a sequence to generate sequential numbers
automatically
SEQUENCE name
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE} ]
[{MIXVALUE n | NOMIXVALUE} ]
[ {CYCLE | NOCYCLE} ]
[ {CACHE n | NOCACHE} ]
.
CREATE
오라클 실습
203
Creating a Sequence: Example
Create a sequence named DEPT_ID to be used for the primary
key of the S_DEPT table
Do not use the CYCLE option
.
SQL> CREATE SEQUENCE dept_id
2
INCREMENT BY 1
3
START WITH 51
4
MAXVALUE 9999999
5
CYCLE
6
CACHE;
오라클 실습
204
Confirming Sequences
Verify your sequence values in the USER SEQUENCES data
dictionary table
SQL> SELECT
sequence_name, min_value,
2
max_value, increment_by,
3
last_number
4
FROM user_sequences;
The LAST_NUMBER column displays the next available
.
sequence number
오라클 실습
205
NEXTVAL and CURRVAL
NEXTVAL returns the next available sequence value
it returns a unique value every time it is referenced, even for diferent users
DURRVAL obtains the current sequence value
NEXTVAL must be issued for that sequence before CURRVAL contains a
value
.
Follow usage rules
오라클 실습
206
Using the Sequence: Examples
Insert a new department named “finance” in location “Seoul”
SQL> INSERT INTO
2
VALUES
3
dept(deptno, dname, loc)
(dept_id.NEXTVAL,
‘Finance’,’Pusan’);
View the current value for the S_DEPT_ID sequence
dept_id.CURRVAL
sys.dual;
.
SQL> INSERT INTO
2
FROM
오라클 실습
207
Using a Sequence
Caching sequence values in memory allows faster access to
those values
Gaps in sequence values can occur when
A rollback occurs
The system crashes
A sequence is used in another table
View the next available sequence, if it was created with
.
NOCACHE, by querying the USER_SEQUENCES table
오라클 실습
208
Modifying a Sequence: Syntax
Change the increment value, maximum value, minimum value,
cycle option, and cache option
.
ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE} ]
[{MIXVALUE n | NOMIXVALUE} ]
[ {CYCLE | NOCYCLE} ]
[ {CACHE n | NOCACHE} ]
오라클 실습
209
Modifying a Sequence: Guidelines
You must be the owner or have the ALTER privilege for the
sequence
Only future sequence numbers are afected
Some validation is performed
The sequence must be dropped and re-created to restart the
.
sequence at a different number
오라클 실습
210
Removing a Sequence
Remove a sequence from the data dictionary by using the
DROP SEQUENCE command
Once removed, the sequence can no longer be referenced
.
SQL> DROP SEQUENCE dept_id;
오라클 실습
211
.
Creating View
오라클 실습
212
Overview of View
What Is a View?
A view is a logical table based on a table or another view. A view contains
no data of its own, but is rather like a “window” through which data from
tables can be viewed or changed. The view is stored as a SELECT
statement in the data dictionary
Advantages of Views
Restrict database access
Simplify queries
Data independence
Different appearances for the same data
.
오라클 실습
213
Creating a View: Syntax
Embed a subquery within the CREATE VIEW statement
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias] …)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
The subquery can contain complex SELECT syntax
.
The subquery cannot contain an ORDER BY clause
오라클 실습
214
Creating a View: Parameters
FORCE / NO FORCE
FORCE: creates the view regardless of whether the view’s base tables or
the referenced object types exist or the owner of the schema containing the
view has privileges on them
NO FORCE: creates the view only if the base tables exist and the owner
of the schema containing the view has privileges on them
WITH READ ONLY
specifies that no delete, inserts, or updates can be performed through the
.
views
오라클 실습
215
Creating a View: Parameters
WITH CHECK OPTION
specifies that inserts and updates performed through the view must result
in rows that the view query can select.
CONSTRAINT
assigns the name of the CHECK OPTION constraint. If you omit this
identifier, Oracle automatically assigns the constraint a name of the form
SYS_Cn, where n is an integer that makes the constraint name unique
.
within the database
오라클 실습
216
Creating a View: Example
Create the EMPVU30 view, which contains the employee
number, name, and job for employees in department 30
SQL>
2
3
4
CREATE VIEW empvu30
AS SELECT
empno, ename, job
FROM
emp
WHERE
deptno = 30;
Describe the structure of the view by using the SQL*Plus DESCRIBE
command
Display data from the view by entering a SELECT statement against the
.
view
오라클 실습
217
Modifying a View: Example
Modify the EMPVU20 view by using CREATE OR REPLACE.
Add an alias for each column name
SQL>
2
3
4
5
CREATE OR REPLACE VIEW empvu20
(id_number, employee, job)
AS SELECT
empno, ename, job
FROM
emp
WHERE
deptno = 20;
Column aliases in the CREATE VIEW clause are listed in the
.
same order as the columns in the subquery
오라클 실습
218
Restrictions
If the view query uses an asterisk (*) to select all columns of a table,
and you later add new columns to the table, the view will not contain
those columns until you re-create the view
INSERT
Aggregation function, group by clause, distinct keyword
UPDATE
Aggregation function, group by clause, distinct keyword
ROWID, ROWNUM pseudocolumn
DELETE
Aggregation function, group by clause, distinct keyword
NOT NULL constraints on columns, which are not selected by a view, exist
.
오라클 실습
219
A Join View
A join view is one whose view query contains a join
If you want a join view to be updateable, all of the following
conditions must be true
The DML statement must affect only on table underlying the join
For an UPDATE statement, all columns updated must be extracted from a
key-preserved table. If the view has the CHECK OPTION, join columns
and columns taken from tables that are referenced more than once in the
.
view must be shielded from UPDATE
오라클 실습
220
Restrictions in a Join View
For a DELETE statement, the join can have one and only one keypreserved table. That table can appear more than once in the join, unless
the view has the CHECK OPTION
For an INSERT statement, all columns into which values are inserted must
come from a key-preserved table, and the view must not have the CHECK
.
OPTION
오라클 실습
221
Removing a View: Example
Remove a view without losing data because a view is based on
underlying tables in the database
.
SQL> DROP VIEW empvu20;
오라클 실습
222
.
Chapter 6
Indexes
오라클 실습
223
What Is an Index?
Database object
Used by the Oracle Server to speed up the retrieval of rows by
using a pointer
Reduces disk I/O by using rapid path access method to locate
the data quickly
Independent of the table it indexes
.
Automatically used and maintained by the Oracle Server
오라클 실습
224
How Are Indexes Created?
Automatically
A unique index is created automatically when you define a PRIMARY
KEY or UNIQUE constraint in a table definition
Manually
Users can create non-unique indexes on columns to speed up access time
.
to the rows
오라클 실습
225
Index Types
Logical (classification from an application perspective)
Single column or concatenated
Unique or nonunique
Function-based
Physical (classification from the way the indexes are stored)
B-tree, Bitmap
Normal or reverse key
Partitioned or nonpartitioned
Miscellaneous
Index organized table, clustered index
.
오라클 실습
226
Creating an Index: Syntax
Create an index on one or more columns
CREATE INDEX index
ON table (column[, column] … );
Example
Improve the speed of query access on the LAST_NAME column in the
EMP table
.
SQL> CREATE INDEX emp_ename_idx
2
ON emp(ename);
오라클 실습
227
Removing an Index
Remove an index from the data dictionary
SQL> DROP INDEX emp_ename_idx;
To drop an index, you must be the owner of the index or have
.
the DROP ANY INDEX privilege
오라클 실습
228
B-Tree Index (1)
Index entry header
ROWID
.
Key column value
Key column length
오라클 실습
229
B-Tree Index (2)
Index Leaf Entry Characteristics
Key values are repeated if there are multiple rows that have the same key
value
There is no index entry corresponding to a row that has all key columns
that are NULL
Restricted ROWID is used to point to the rows of the table, since all rows
belong to the same segment
Example
.
SQL> CREATE INDEX emp_ename_idx
2
ON emp(ename);
오라클 실습
230
Reverse Key Index (1)
It is useful when I/O bottlenecks can occur on the index
because all index updates occur at the same place in the index
tree
A reverse key index reverses the bytes of each column
indexed(except the ROWID) while keeping the column order
Example
.
SQL> CREATE INDEX emp_ename_idx
2
ON emp(ename) REVERSE;
오라클 실습
231
Reverse Key Index (2)
Index on EMP(EMPNO)
EMP table
KEY ROWID
EMPNO ENAME JOB
--------
-------------------------------
--------
--------- ---------------
1257
0000000F.0002.0001
7499
ALLEN
SALESMAN
2877
0000000F.0002.0001
7369
SMITH
CLERK
4567
0000000F.0002.0001
7521
WARD
SALESMAN
6657
0000000F.0002.0001
7566
JONES
MANAGER
8967
0000000F.0002.0001
7654
MARTIN SALESMAN
9637
0000000F.0002.0001
7698
BLAKE
MANAGER
9647
0000000F.0002.0001
7782
CLARK
MANAGER
.
EMPNO (BLOCK# ROW# FILE#)
오라클 실습
232
Bitmap Index (1)
Bitmap indexes are more advantageous than B-tree indexes in
certain situations
When a table has millions of rows and the key columns have low
cardinality
When queries often use a combination of multiple WHERE conditions
involving the OR operator
When there is read-only or low update activity on the key columns
Example
.
SQL> CREATE BITMAP INDEX emp_ename_idx
2
ON emp(ename);
오라클 실습
233
Bitmap Index (2)
Index entry header
Key column value
Start ROWID
End ROWID
.
Bitmap
오라클 실습
234
.
Bitmap Index (3)
오라클 실습
235
Index Organized Table (1)
Indexed access on table
Accessing indexorganized table
ROWID
.
Non-key columns
Key column
Row header
오라클 실습
236
Index Organized Table (2)
Index-Organized Tables Compared with Regular Tables
Regular Table
Index-Organized Table
Unique identifier: ROWID
Identified by PK
ROWID implicit
No ROWID
Supports several indexes
No secondary indexes
Full table scan returns rows in no specific
Full table scan returns rows in PK order
order
No unique constraints allowed
More storage
Less storage
Distribution and replication supported
Replication not supported
.
Unique constraints allowed
오라클 실습
237
Index Organized Table (3)
Row Overflow
Index-Organized Table
테이블 스페이스
PCTTHRESHOLD 보다 큰 행
.
block
PCTTHRESHOLD 내의 행
Overflow 테이블 스페이스
오라클 실습
238
Index Organized Table (4)
Example
.
SQL>CREATE TABLE scott.sales
2
(
office_cd
NUMBER(3),
3
qtr_end
DATE,
4
revenue
NUMBER(10,2),
5
review
VARCHAR2(1000),
6
CONSTRAINT sales_pk
7
PRIMARY KEY(office_cd, qtr_end) )
8
ORGANIZATION INDEX TABLESPACE indx
9
PCTTHRESHOLD 20
10 INCLUDING review
11
OVERFLOW TABLESPACE user_data;
오라클 실습
239
What is a Cluster? (1)
Clusters are an optional method of storing table data
A cluster is a group of tables that share the same data blocks
because they share common columns and are often used
together
Benefits:
Disk I/O is reduced for joins of clustered tables
Access time improves for joins of clustered tables
Less storage is required to store related table and index data in a cluster
.
than is necessary in nonclustered table format
오라클 실습
240
.
What is a Cluster? (2)
오라클 실습
241
Creating Clusters
Creating a cluster
CREATE CLUSTER emp_dept (deptno NUMBER(3));
Creating a cluster index
CREATE INDEX emp_dept_index
ON CLUSTER emp_dept;
Creating tables
.
CREATE TABLE dept (deptno NUMBER(3) PRIMARY KEY, . . . )
CLUSTER emp_dept (deptno);
CREATE TABLE emp (empno NUMBER(5) PRIMARY KEY,
...
, deptno NUMBER(3) REFERENCES dept)
CLUSTER emp_dept (deptno);
오라클 실습
242
Dropping Clusters
Dropping Clusters
DROP CLUSTER emp_dept;
DROP CLUSTER emp_dept INCLUDING TABLES;
DROP CLUSTER emp_dept INCLUDING TABLES CASCADE
ONSTRAINTS;
Dropping Clustered Tables
Clustered tables can be dropped individually without affecting the table’s
cluster, other clustered tables, or the cluster index
Dropping Cluster Indexes
A cluster index can be dropped without affecting the cluster or its
.
clustered tables
오라클 실습
243
Should You Use Hash Clusters?
Advantages of Hashing
Most queries are equality queries on the cluster key:
SELECT ... WHERE cluster_key = ...;
The tables in the hash cluster are primarily static in size size so that you can
determine the number of rows and amount of space required for the tables in the
cluster.
Disadvantages of Hashing
Most queries on the table retrieve rows over a range of cluster key values
SELECT . . . WHERE cluster_key < . . . ;
The table is not static and continually growing
Applications frequently perform full-table scans on the table and the table is
.
sparsely populated
오라클 실습
244
Creating Hash Clusters
Creating a hash cluster
CREATE CLUSTER trial_cluster (trialno NUMBER(5,0))
…
HASH IS trialno HASHKEYS 150;
Creating a table
.
CREATE TABLE trial ( trialno NUMBER(5,0) PRIMARY KEY, ...)
CLUSTER trial_cluster (trialno);
오라클 실습
245
Creating Hash Single Table Hash Clusters
Creating a single table hash clusters
Provide fast access to rows in a table
one-to-one mapping between hash keys and data rows
.
CREATE CLUSTER peanut (variety NUMBER)
SIZE 512 SINGLE TABLE HASHKEYS 500;
오라클 실습
246
Controlling Space Use Within a Hash
Cluster
Choosing the Key
HASH IS
A single column of the NUMBER datatype, and contains uniformly
distributed integers
SIZE
SIZE should be set to the average amount of space required to hold all
rows for any given hash key
HASHKEYS
For maximum distribution of rows in a hash cluster, Oracle rounds the
.
HASHKEYS value up to the nearest prime number
오라클 실습
247
Creating Hash Clusters
Dropping a hash Cluster
DROP CLUSTER emp_dept;
A table in a hash cluster is dropped using the DROP TABLE
statement
The implications of dropping hash clusters and tables in hash
.
clusters are the same for index clusters
오라클 실습
248
.
Chapter 7
PL/SQL
오라클 실습
249
Overview
What is PL/SQL?
PL/SQL is an extension to SQL with design features of programming
languages
Data manipulation and query statements are included within procedural
units of code
Benefits of PL/SQL
Modularize program development
Declare identifiers
Program with procedural language control structures
Portability / Integration
Improve performance
.
오라클 실습
250
PL/SQL Block Structure
DECLARE - Optional
Variables, constants, cursors, user-defined exceptions
BEGIN - Mandatory
SQL statements
PL/SQL control statements
EXCEPTION Optional
Actions to perform when errors occur
.
END; - Mandatory
오라클 실습
251
Block Types
Anonymous
Procedure
Function
PROCEDURE name
IS
FUNCTION name
RETURN datatype
IS
BEGIN
-- statements
BEGIN
-- statements
BEGIN
-- statements
[EXCEPTION]
[EXCEPTION]
[EXCEPTION]
END;
END;
END;
.
[DECLARE]
오라클 실습
252
PL/SQL Example
Anonymous PL/SQL Block
.
SQL> declare
2 a int;
3 begin
4 a :=3;
5 end;
6 /
SQL>
SQL>
SQL> print a
오라클 실습
253
SQL*Plus Commands
ACCEPT
Reads input from the user and stores the input into a variable
VARIABLE
Declares a bind, or host, variable
PRINT
Displays the current value of bind variables
EXECUTE
Executes a single PL/SQL statements
.
오라클 실습
254
.
Declare Section
오라클 실습
255
Declaring Variables and Constants: Syntax
identifier [CONSTANT] datatype [NOT NULL]
[ := | DEFAULT expr];
Guidelines
Follow naming conventions
Initialize constants and variables designated as NOT NULL
Initialize identifiers by using the assignment operator (:=) or by the
DEFAULT reserved word
Declare at most on one identifier per line
.
오라클 실습
256
PL/SQL Datatypes
Scalar types:
BOOLEAN
CHAR, VARCHAR2(n)
NUMBER, INTEGER, FLOAT, BINARY_INTEGERNATURAL,
POSITIVE
DATE
%TYPE
Composite Data Types:
PL.SQL TABLE
PL/SQL RECORD
%ROWTYPE
.
오라클 실습
257
Scalar Variable Declarations
Example
DECLARE
v_gender
v_comments
v_count
v_total_sal
v_order_date
c_tax_rate
v_valid
CHAR(1);
CHAR(30);
BINARY_INTEGER
:= 0;
NUMBER(9,2)
:= 0;
DATE
:= sysdate + 7;
CONSTANT NUMBER(3,2) := 8.25;
BOOLEAN NOT NULL
:= TRUE;
.
BEGIN
…
END;
오라클 실습
258
The %TYPE Attribute
Declare a variable according to
Another previously declared variable
A database column definition
Prefix %TYPE with
The database table and column
The previously declared variable name
.
PL/SQL determines the datatype and size of the variable
오라클 실습
259
The %TYPE Attribute: EXAMPLES
DECLARE
v_ename
v_sal
v_comm
emp.ename%TYPE;
emp.sal%TYPE;
emp.comm%TYPE := 100;
BEGIN
…
END;
Advantages of using the % TYPE attribute
The datatype of the underlying database column may be unknown
The datatype of the underlying database column may change at runtime
.
오라클 실습
260
PL/SQL Table
A PL/SQL TABLE datatype is not the same as a database table
A PL/SQL TABLE is similar to a none-dimensional array
A PL/SQL TABLE must contain two components:
A primary key of datatype BINARY_INTEGER that indexes the PL/SQL
TABLE
A column of a scalar datatype, which stores the PL/SQL TABLE elements
A PL/SQL TABLE can increase dynamically because it is
.
unconstrained
오라클 실습
261
PL/SQL Table
Syntax
DECLARE
TYPE type_name IS TABLE OF datatype
[NOT NULL] INDEX BY BINARY_INTEGER;
identifier type_name
Example
TYPE name_table_type IS TABLE OF varchar2(25)
INDEX BY BINARY_INTEGER;
ename_table name_table_type;
.
TYPE sal_table_type IS TABLE OF emp.sal%TYPE
INDEX BY BINARY_INTEGER;
sal_table
sal_table_type;
오라클 실습
262
PL/SQL Record
Must contain one or more components of any base, RECORD,
or PL/SQL TABLE datatype called fields
Are similar in structure to records in a 3GL
Are not the same as rows in a database table
Treat a collection of fields as a logical unit
Are convenient for fetching a row of data from a table for
.
processing
오라클 실습
263
Declaring a PL/SQL RECORD
Syntax
DECLARE
TYPE type_name IS RECORD
(field_name1 field_type [NOT NULL { := | DEFAULT} expr],
(field_name1 field_type [NOT NULL { := | DEFAULT} expr], …)
identifier type_name
Example
.
DELCARE
TYPE emp_record_type IS RECORD
(ename
VARCHAR2(25),
job
VARCHAR2(25),
gender
CHAR(1));
name_record emp_record_type;
오라클 실습
264
The %ROWTYPE Attribute
Declare a variable according to a collection of columns in a
database table or view
Prefix %ROWTYPE with the database table
Fields within the RECORD take their names and datatypes
.
from the columns of the table or view
오라클 실습
265
The %ROWTYPE Attribute: Advantages
The number and datatypes of the underlying database columns
may be unknown
The number and datatypes of the underlying database column
may change at runtime
Useful when retrieving
A row with the SELECT statement
Multiple rows with an explicit cursor
Examples
dept%ROWTYPE;
emp%ROWTYPE;
.
DELCARE
dept_record
emp_record
오라클 실습
266
.
Execution Section
오라클 실습
267
Execution Section Components
Assignment
Operation
Function
SQL statements
.
Control Flow
오라클 실습
268
Assigning Values to Variables: Syntax
Base type
identifier
:=
expr;
Table type
plsql_table_name (primary_key_value)
:=
expr;
:=
expr;
Record type
.
plsql_record-name.field_name
오라클 실습
269
Assigning Values to Variables: Examples
Set the maximum salary to the current salary
v_total_sal
:=
c_tax_rate;
Store the name “maduro” in the index indentifier of 3 in a
PL/SQL TABLE
ename_table(3)
:=
‘sjKim’;
Store basic information for a new employee in a PL/SQL
RECLOD
:= ‘sjKim’;
:= ‘Instructor’;
.
emp_record.ename
emp_record.job
오라클 실습
270
Operators in PL/SQL
Logical
Arithmetic
Concatenation
Exponential operator (**)
.
Parentheses to control order of operations
오라클 실습
271
Operators in PL/SQL: Examples
Increment the index for a loop
v_count
:=
v_count + 1;
Set the value of a Boolean flag
v_valid
:= (c_tax_rate = v_comm);
Validate an employee number if it contains a value
:= (v_emp_id IS NOT NULL);
.
v_valid
오라클 실습
272
Functions in PL/SQL
Available:
Single-row number
Single-row character
Datatype conversion
Date
Not available:
Group functions
.
오라클 실습
273
Functions in PL/SQL: Examples
Build the mailing list for a company
v_mailing_lists := v_name || CHR(10) ||
v_address || CHR(10) || v_country || CHR(10) ||
v_zip_code;
Convert the last name to uppercase
v_ename
:=
UPPER(v_ename);
Compute the sum of all numbers stored in the
NUMBER_TABLE PL/SQL table ( error? )
:=
SUM(number_table);
.
v_total
오라클 실습
274
Datatype conversion
Convert data to comparable datatypes
Mixed datatypes can result in an error and/or affect
performance
Conversion functions:
TO_CHAR
TO_DATE
TO_NUMBER
.
오라클 실습
275
Datatype conversion:Example
This statement produces a compile error
v_comment := USER || ‘: ‘ || sysdate;
To correct the error, the TO_CHAR conversion function is used
.
v_comment := USER || ‘: ‘ || to_char(sysdate);
오라클 실습
276
Referencing Non-PL/SQL Variables
Reference non-PL/SQL variables as host variables
Prefix the references with a colon (:)
Store the annual salary into a SQL *Plus global variable
:= v_salary * 12;
.
g_annual_salary
오라클 실습
277
Programming Guidelines
Easy to maintain
Document code with comments
Develop a case convention for the code
Develop naming conventions for identifiers and other objects
.
Enhance readability by indenting
오라클 실습
278
Commenting Code
Comment code by
Prefixing the comment with two dasses (- -)
Placing the comment between /* and */
Example
.
DECLARE
v_sal NUMBER (9,2);
BEGIN
/* Compute the annual salary based on the monthly
salary input from the user
*/
v_sal := v_sal * 12;
END;
오라클 실습
279
Code Conventions
Examples and Case
SQL Commands
SELECT, INSERT
PL/SQL Keywords
DECLARE, BEGIN, IF
Datatypes
VARCHAR2, BOOLEAN
Identifiers
and Parameters
v_sal, emp_cursor, g_sal
Database Tables
and Columns
s_emp, order_date, id
.
Conventions
오라클 실습
280
Code Naming Conventions
Naming convention
Example
Variable
v_name
v_sal
Constant
c_name
c_company_name
Cursor
name_cursor
emp_cursor
Exception
e_name
e_too_many
TABLE type
name_table_type
amount_table_type
TABLE
name_table
order_total_table
RECORD type
name_record_type
emp_record_type
RECORD
name_record
customer_record
.
Identifier
오라클 실습
281
.
Interacting with Oracle
오라클 실습
282
Creating a Procedure: Syntax
CREATE OR REPLACE PROCEDURE name
[(parameter, …) ]
IS
pl/sql_block
Where the parameter syntax is
Parameter_name [IN | OUT | IN OUT] datatype
[ { := | DEFAULT} expr]
.
Do not specify a constraint on the datatype
오라클 실습
283
Creating a Procedure: Guidelines
Use the CREATE OR REPLACE clause when building your
procedure in SQL*Plus
Enter any parameters
Start the PL/SQL block with IS
Enter a local variable declaration or the keyword BEGIN after
.
IS
오라클 실습
284
Creating a Procedure: Example
Code
CREATE OR REPLACE PROCEDURE change_salary
( v_empno
IN NUMBER,
v_new_sal IN NUMBER)
IS
BEGIN
UPDATE emp
SET sal = v_new_sal
WHERE
empno = v_empno;
-COMMIT;
END change_salary;
Run
change_salary(7902,2500);
.
EXECUTE
오라클 실습
285
Functions and Procedures
Procedure
Execute as a PL/SQL statement
No RETURN datatype
Can return a value
Function
Called as part of an expression
Must contain a RETURN datatype
Must return a value
.
오라클 실습
286
Creating a Function
Create a PL/SQL function to return a value to the calling
environment
Add a RETURN clause with the datatype in the declaration of
the function
.
Include at least on RETURN statement in the PL/SQL block
오라클 실습
287
Creating a Function: Syntax
CREATE OR REPLACE FUNCTION name
[(parameter, …) ]
RETURN datatype
IS
pl/sql_block
Remember to include at least one RETURN statement in the
.
PL/SQL block
오라클 실습
288
Creating a Function: Example
Code
CREATE OR REPLACE FUNCTION tax
( v_value
IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (v_value * .07);
END tax;
RUN
.
SQL> VARIABLE x NUMBER
SQL> EXECUTE :x := tax(100);
SQL> PRINT x
오라클 실습
289
Invoking Functions in SQL Statements:
Example
Enter a SQL statement with a function call at the command
prompt
ename, tax(sal)
emp
deptno = 30;
.
SQL> SELECT
2
FROM
3
WHERE
오라클 실습
290
Invoking Functions in SQL Statements
Advantages
Permit calculations otherwise not easily obtained in SQL
Increase efficiency of queries
Guidelines
Only use stored functions, not procedures
Single row functions
No DML statements
Formal parameters must be IN
EXECUTE privileges
.
오라클 실습
291
Retrieving Data: Syntax
Retrieve data from the database with SELECT
SELECT
INTO
FROM
WHERE
select_list
variable_name | record_name
table
condition;
INTO clause is required
Exactly on row must be returned
.
Full SELECT syntax is available
오라클 실습
292
Retrieving Data: Example
Retrieve the hiredate and the job for the specified empno
.
CREATE OR REPLACE PROCEDURE hire_date
( v_empno IN number)
IS
v_hiredate
emp.hiredate%TYPE;
v_job
emp.job%TYPE;
BEGIN
SELECT
hiredate, job
INTO
v_hiredate, v_job
FROM
emp
WHERE
empno = v_empno;
…
END hire_date;
오라클 실습
293
Retrieving Data: Example
Return the sum of the salaries for all employees in the specified
department
.
CREATE OR REPLACE FUNCTION sum_emp
( v_deptno IN number )
RETURN
NUMBER
IS
v_sum_sal
emp.sal%TYPE;
BEGIN
SELECT
SUM(sal)
-- group function
INTO
v_sum_sal
FROM
emp
WHERE
deptno = v_deptno;
RETURN
(v_sum_sal);
END sum_emp;
오라클 실습
294
Retrieving Data: Example
Retrieve all information about the specified department
.
CREATE OR REPLACE PROCEDURE all_dept
( v_deptno IN number )
IS
dept_record dept%TYPE;
BEGIN
SELECT
*
INTO
dept_record -- PL/SQL RECORD
FROM
dept
WHERE
deptno = v_deptno;
...
END all_dept;
오라클 실습
295
Naming Conventions: Example
Retrieve the order date and the ship date for the specified
order
.
CREATE OR REPLACE PROCEDURE hire_date
( empno IN number)
IS
v_hiredate
emp.hiredate%TYPE;
v_job
emp.job%TYPE;
BEGIN
SELECT
hiredate, job
INTO
v_hiredate, v_job
FROM
emp
WHERE
empno = empno; -- unhandled exception
…
-- TOO_MANY_ROWS
END hire_date;
오라클 실습
296
SELECT Exceptions
SELECT statements in PL/SQL must retrieve exactly one row
If zero or more than one row is retrieved, an exception is raised
SELECT exceptions:
TOO_MANY_ROWS
NO_DATA_FOUND
.
오라클 실습
297
Manipulating Data
Make changes to database tables by using DML commands
INSERT
UPDATE
DELETE
.
오라클 실습
298
Inserting Data:Example
Add a new order to the BONUS table for the specified job
.
CREATE OR REPLACE PROCEDURE bonus_add
(v_job IN varchar2)
IS
v_ename
bonus.ename%TYPE :=
user ;
v_sal
bonus.sal%TYPE
:=
2750 ;
v_comm
bonus.comm%TYPE :=
300 ;
BEGIN
INSERT INTO bonus (ename, job, sal, comm)
VALUES
(v_ename, v_job, v_sal, v_comm);
END bonus_add;
오라클 실습
299
Inserting Data:Example
Add a new order to the EMP table for the specified name
.
CREATE OR REPLACE PROCEDURE emp_add
(v_ename IN varchar2)
IS
v_job
emp.job%TYPE
:=
‘Instructor’ ;
v_sal
emp.sal%TYPE
:=
2750 ;
v_comm
emp.comm%TYPE :=
300 ;
BEGIN
INSERT INTO emp (empno, ename, job, sal, comm)
VALUES
(emp_id.NEXTVAL, v_ename,
v_job, v_sal, v_comm);
END emp_add;
오라클 실습
300
Updating Data: Example
Change the hiredate for the specified empno
.
CREATE OR REPLACE PROCEDURE new_hiredate
(v_empno
emp.empno%TYPE,
v_hiredate
emp.hiredate%TYPE)
IS
BEGIN
UPDATE
emp
SET hiredate = v_hiredate
WHERE
empno = v_empno;
END new_hiredate;
오라클 실습
301
Deleting Data: Example
Delete a specified order
.
CREATE OR REPLACE PROCEDURE del_emp
(v_empno
emp.empno%TYPE)
IS
BEGIN
DELETE FROM emp
WHERE
empno = v_empno;
END del_emp;
오라클 실습
302
.
Controlling Flow in
PL/SQL Blocks
오라클 실습
303
Controlling PL/SQL Flow of Execution
Change the logical flow of statements by using control
structures:
Conditional control structures (IF statement)
Loop control structures
• Basic loop
• FOR loop
• WHILE loop
.
• EXIT statement
오라클 실습
304
The IF Statement: Syntax
You can perform actions selectively based upon conditions
being met.
IF condition THEN
statements;
[ELSEIF condition THEN
statements;]
[ELSE
statements;
END IF;
ELSIF is one word
END IF is two words
.
At most, one ELSE clause is permitted.
오라클 실습
305
The IF Statement: Example
Simple IF Statement
declare
a int;
begin
a :=3;
if a = 2 THEN
insert into bonus(ename)
values('1input');
else
insert into bonus(ename)
values('2input');
end if;
end;
.
declare
a int;
begin
a :=3;
if a = 2 THEN
insert into bonus(ename)
values('1input');
end if;
end;
오라클 실습
306
The IF Statement: Example
Nested IF statement
Type 1
Type 2
.
declare
a int;
begin
a :=3;
if a = 2 THEN
insert into bonus(ename)
values('1input');
else if a = 3 then
insert into bonus(ename)
values('2input');
end if;
end;
오라클 실습
307
Logic Tables
Build a simple Boolean condition with a comparison operator
AND TRUE FALSE NULL
TRUE FALSE NULL
NOT
TRUE TRUE TRUE TRUE
TRUE FALSE
FALSE FALSE FALSE FALSE FALSE TRUE FALSE NULL
FALSE TRUE
NULL TRUE FALSE NULL
NULL NULL
NULL TRUE NULL NULL
.
TRUE TRUE FALSE NULL
OR
오라클 실습
308
LOOP statements
Loops repeat a statement or sequence of statements multiple
times
Three loop types:
Basic loop
FOR loop
WHILE loop
.
오라클 실습
309
Basic Loop: Syntax
Iterate through your statements with a basic loop
LOOP
statement1;
...
EXIT [WHEN
END LOOP;
condition];
.
Without the EXIT statement, the loop would be infinite
오라클 실습
310
Basic Loop: Example
Insert the first five new line items for department number 20
...
.
v_ename
emp.ename%TYPE :=
user;
v_deptno
emp.deptno%TYPE :=
20;
v_counter
number(2)
:=
1;
BEGIN
...
LOOP
INSERT INTO emp (empno, ename, deptno)
VALUES ( emp_id.nextval, v_ename, v_deptno );
v_counter
:=
v_counter + 1;
EXIT WHEN
v_counter
> 5;
END LOOP;
...
오라클 실습
311
FOR Loop: Syntax
Use a FOR loop to shortcut the test for the number of iterations
FOR
END
index in
[REVERSE]
lower_bound..upper_bound
statement1;
statement2;
...
LOOP;
LOOP
.
Do not declare the index; it is declared implicitly
오라클 실습
312
FOR Loop: Example
Print the number of times the loop is executed and the last
value for the index
Guidelines
Reference the index within the loop only; it is undefined outside the loop
Use an expression to reference the existing value of an index
Do not reference the index a s the target of an assignment
.
오라클 실습
313
For Loop: Example
Answer
0;
.
CREATE OR REPLACE PROCEDURE iterate
( v_lower
NUMBER,
v_upper
NUMBER )
IS
v_counter
NUMBER(10)
:=
v_output
NUMBER(10);
BEGIN
FOR i IN v_lower..v_upper LOOP
v_counter
:= v_counter + 1;
v_output
:= i;
END LOOP;
END iterate;
오라클 실습
314
WHILE Loop: Syntax
Use the WHILE loop to repeat statements while a condition is
TRUE
WHILE condition
statement1;
statement2;
...
END LOOP;
LOOP
.
Condition is evaluated at the beginning of each iteration
오라클 실습
315
WHILE loop: Example
Insert the first five new line items for department number 20
...
.
v_ename
emp.ename%TYPE :=
user;
v_deptno
emp.deptno%TYPE :=
20;
v_counter
number(2)
:=
0;
BEGIN
...
WHILE
v_counter < 5
LOOP
INSERT INTO emp (empno, ename, deptno)
VALUES ( emp_id.nextval, v_ename, v_deptno );
v_counter
:=
v_counter + 1;
END LOOP;
...
오라클 실습
316
.
Cursors
오라클 실습
317
What Is a Cursor?
A cursor is a private SQL work area
Every SQL statement executed by the Oracle Server has an
individual cursor associated with it
Two types:
Implicit cursors: Declared for all DML and PL/SQL SELECT statements
Explicit cursors: Declared and named by the programmer
.
오라클 실습
318
Explicit Cursor Functions
Process beyond the first row returned by the query, or by row
Keep track of which row is currently being processed
.
Control cursors manually in the PL/SQL block
오라클 실습
319
Controlling Explicit Cursors
Declare the cursor
Declare the cursor by naming it and defining the structure of the query to
be performed within it
Open the cursor
The OPEN statement executes the query and binds any variables that are
referenced.
Fetch data from the cursor
FETCH statement loads the current row from the cursor into variables
Close the cursor
The CLOSE statement releases the active set of rows
.
오라클 실습
320
Declaring the Cursor: Syntax
Define parameters to allow substitution of values into the
cursor query
DECLARE
CURSOR
cursor_name IS
select_statement;
.
Do not include the INTO clause within the cursor declaration
오라클 실습
321
Declaring the Cursor: Example
Retrieve the line items of an order one by one
.
DECLARE
...
v_dept_deptno
dept.deptno%TYPE;
v_dept_loc
dept.loc%TYPE;
v_emp_empno
emp.empno%TYPE;
v_emp_sal
emp.sal%TYPE;
CURSOR
emp_cursor IS
SELECT
empno, sal
FROM
emp
WHERE
deptno = v_dept_deptno;
BEGIN
...
오라클 실습
322
Opening the Cursor: Syntax
Open the cursor to execute the query and identify the active set
OPEN cursor_name;
If the query returns no rows, no exception is raised
Test the outcome after a fetch by using cursor attributes
Example
.
OPEN emp_cursor;
오라클 실습
323
Fetching Data From the Cursor: Syntax
Retrieve the current row values into output variables
FETCH
cursor_name into variable1, variable2;
Include the same number of variables
Match each variable to correspond to the columns positionally
Test to see if the cursor contains rows
Example: Retrieve the line items of an order one-by-one
emp_cursor INTO v_emp_empno, v_emp_sal;
.
FETCH
오라클 실습
324
Closing the Cursor
Close the cursor after completing the processing of the rows
CLOSE
cursor_name;
Reopen the cursor again, if required
Do not attempt to fetch data from a cursor once it has been
closed
Example
emp_cursor;
.
CLOSE
오라클 실습
325
Explicit Cursor Attributes
%ISOPEN
Boolean
Evaluates to TRUE if the cursor is open
%NOTFOUND Boolean
Evaluates to TRUE if the most recent fetch does not return a row
%FOUND
Boolean
Evaluates to TRUE until the most recent fetch does not return a row
%ROWCOUNT Number
Evaluates to the total number of rows returned so far
.
오라클 실습
326
Controlling Multiple Fetches
Process several rows from an explicit cursor using a loop
Fetch a row with each iteration
Write a test for an unsuccessful fetch by using
the %NOTFOUND attribute
.
Test success of each fetch using explicit cursor attributes
오라클 실습
327
The %ISOPEN Attribute: Example
Fetch rows only when the cursor is open
Test if the cursor is open using %ISOPEN cursor attribute
before performing a fetch
.
IF emp_cursor%ISOPEN THEN
insert into bonus(ename) values('true');
ELSE
insert into bonus(ename) values('false');
END IF;
오라클 실습
328
The %NOTFOUND and %ROWCOUNT
Attributes: Example
Retrieve an exact number of rows using the %ROWCOUNT
cursor attribute
Determine when to exit the loop using the %NOTFOUND
cursor attribute
...
LOOP
FETCH
emp_cursor
INTO v_emp_empno, v_emp_sal;
EXIT WHEN emp_cursor%ROWCOUNT > 5
OR emp_cursor%NOTFOUND;
v_total := v_total + v_emp_sal;
.
...
END LOOP;
오라클 실습
329
Cursors with Parameters: Syntax
Pass parameters values to a cursor when the cursor is opened
and the query is executed
CURSOR
cursor_name
[ (parameter_name
datatype, . . . )]
IS
select_state_statement;
Open an explicit cursor several times with a different active set
.
each time
오라클 실습
330
Cursors with Parameters: Example
Pass the department number and job title to the WHERE
clause
CURSOR
v_job VARCHAR2)
=
IS
v_dept
.
emp_cursor
(v_dept NUMBER,
SELECT
empno, sal
FROM
emp
WHERE
deptno
AND job = v_job;
오라클 실습
331
Cursors and Records: Example
Process the rows of the active set conveniently by fetching
values into a PL/SQL RECORD
CURSOR emp_cursor IS
SELECT
empno, sal
FROM
emp
WHERE
deptno = v_dept_deptno;
emp_record emp_cursor%ROWTYPE;
emp_cursor
:=
INTO emp_record;
v_total + emp_record.sal;
.
BEGIN
...
FETCH
...
v_total
...
오라클 실습
332
WHERE CURRENT OF Clause
Update or delete the current row using cursors
Lock the rows first by including the FOR UPDATE clause in
the cursor query
Reference the current row from an explicit cursor by using the
WHERE CURRENT OF clause
Do not commit across fetches from an explicit cursor if FOR
.
Update IS USED
오라클 실습
333
Cursor FOR Loops: Syntax
Shortcut to process explicit cursors
Implicit open, fetch, and close occur
FOR
END
record_name IN
statement1;
statement2;
...
LOOP;
cursor_name LOOP
.
Do not declare the record; it is implicitly declared
오라클 실습
334
WHERE CURRENT OF Clause: Example
Update rows based on criteria from a cursor
...
CURSOR
emp_cursor
SELECT
...
FOR UPDATE;
IS
BEGIN
...
FOR emp_record IN emp_cursor LOOP
UPDATE
emp
SET sal = 1000
WHERE CURRENT OF emp_cursor;
END LOOP;
...
.
END;
오라클 실습
335
.
Chapter 8
Triggers
오라클 실습
336
Triggers
What is a trigger?
Database triggers are procedures that are stored in the database and
activated (“fired”) when specific conditions occur, such as adding a row to
a table
Managed like other stored program units
Written in PL/SQL
Difference of triggers and stored procedures
Usage examples
Restrict DMS operations against a table
Log events / Audit / Gather statistics
.
오라클 실습
337
Triggering event
DML statements are executed against an associated table
INSERT, UPDATE, DELETE
Certain DDL statements are executed on objects within a
database or schema
ALTER, CREATE, DROP
A specified database event occurs
STARTUP, SHUTDOWN, SERVERERROR
.
오라클 실습
338
Creating Trigger: Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ dml_event_clause | ddl_event_clause | dml_event_clause }
[ referencing_clause ]
{pl/sql_block | call_procedure_statement}
BEFORE: causes Oracle to fire the trigger before executing the triggering event
AFTER: causes Oracle to fire the trigger after executing the triggering event
INSTEAD OF: causes Oracle to fire the trigger instead of executing the triggering
event
Restrictions
• You cannot specify a BEFORE and AFTER trigger on a view
.
• INSTEAD OF is a valid clause only for views
오라클 실습
339
Creating Trigger: dml_event_clause
{ DELETE | INSERT | UPDATE [OF column[, column, …] ] }
ON table
DELETE/INSERT keyword: causes Oracle to fire the trigger whenever a
statement removes/adds a row from a table
UPDATE keyword: causes Oracle to fire the rigger whenever an UPDATE
statement changes a value in one of the columns specified after OF
• If you omit OF, Oracle fires the trigger whenever an UPDATE statement
changes a value in any column of the table
• You cannot specify OF with UPDATE for an INSTEAD OF trigger. Oracle
fires INSTEAD OF triggers whenever an UPDATE changes a value in any
.
column of the view
오라클 실습
340
Creating Trigger: ddl_event_clause
ddl_event [ OR ddl_event [ …]]
ON {SCHEMA | DATABASE}
The following events are valid
CREATE / DROP / ALTER / TRUNCATE
• The trigger will not be fired by an ALTER DATABASE statement
ANALYZE
GRANT / REVOKE
RENAME
DDL
• Fires the trigger whenever any of the preceding DDL statements issued
• You cannot specify as a triggering event any DDL operation performed through a PL/SQL
.
procedure
오라클 실습
341
Creating Trigger: database_event_clause
database_event [ OR database_event [ …]]
ON {SCHEMA | DATABASE}
SERVERERROR
LOGON / LOGOFF
STARTUP / SHUTDOWN
.
오라클 실습
342
Trigger Example
CREATE OR REPLACE TRIGGER log_logon
AFTER LOGON ON DATABASE
WHEN (USER = ‘SYS’ OR USER LIKE ‘OPS$%’)
BEGIN
INSERT INTO sys.event_log
VALUES (‘Logon ‘ || USER || ‘ at ‘ ||
TO_CHAR(sysdate, ‘YYYY-MM-DD HH24:MI:SS’));
COMMIT;
END;
/
Trigger information is stored in the data dictionary view
.
DBA_TRIGGERS
오라클 실습
343
DML Trigger Example
CREATE TRIGGER emp_permit_changes
BEFORE
DELETE OR INSERT OR UPDATE
ON emp
pl/sql_block
This example creates a BEFORE statement trigger named
EMP_PERMIT_CHANGES. You would write such a trigger to place
.
restrictions on DML statements issued on this table
오라클 실습
344
DML Trigger Example with Restriction
CREATE TRIGGER salary_check
BEFORE
INSERT OR UPDATE OF sal, job ON emp
FOR EACH ROW
WHEN (new.job <> ‘PRESIDENT’ )
pl/sql_block
SALARY_CHECK is a BEFORE row trigger
SALARY_CHECK has a trigger restriction that prevents it
.
from checking the salary of the company president
오라클 실습
345
Calling a Procedure in a Trigger Body
Example
CREATE TRIGGER salary_check
BEFORE
INSERT OR UPDATE OF sal, job ON emp
FOR EACH ROW
WHEN (new.job <> ‘PRESIDENT’ )
CALL check_sal (:new.job, :new.sal, :new.ename)
Assume you have defined a procedure check_sal, which verifies
.
that an employee’s salary is in an appropriate range
오라클 실습
346
Enabling/Disabling Triggers
To enable a disabled trigger / To disable a enabled trigger
ALTER TIRGGER reorder [ENABLE|DISABLE];
To enable all triggers defined for a specific table
ALTER TABLE inventory
[ENABLE | DISABLE] ALL TIGGERS;
You may want to temporarily disable a trigger
An object that the trigger references is not available
You are loading data into the table to which the trigger applies
.
오라클 실습
347