〈中国 申請書 東京・名古屋・大阪・福岡〉P. 1 - …修正液使用:可(サイン以外)(名古屋・大阪)不可 申請書のコピー使用:可
Oracle SQL introduction. 使用聲明 請尊重智慧財產權...
Transcript of Oracle SQL introduction. 使用聲明 請尊重智慧財產權...
![Page 1: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/1.jpg)
Oracle SQL introduction
![Page 2: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/2.jpg)
使用聲明
請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用
![Page 3: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/3.jpg)
Agenda
Database Structure Overview Oracle SQL* Net (Networking) SQL
– Data Retrieval– Data Manipulation Language (DML)– Data Definition Language (DDL)– Transaction Control
Database Objects– Table, Constraint , Sequence , View , Index
![Page 4: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/4.jpg)
DAY 1
![Page 5: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/5.jpg)
Terminology
Instance (SGA + background process) Database (control file + data file + redo files) Tablespace Schema Object
– Table,Index,Sequence,View,Package,Procedure,Function,Cluster…etc
![Page 6: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/6.jpg)
Database Structure Overview
Control Control FileFile
Redo log FilesRedo log Files
Data Data FilesFiles
ParameterParameter FileFile
Archived Archived Log FileLog File
InstanceInstance DatabaseDatabase
SGASGA
SharedSharedPoolPool
LibraryLibraryCacheCache
Data Dict.Data Dict.CacheCache
DatabaseDatabaseBufferBufferCacheCache
Redo logRedo logBufferBuffer
SMONSMON
DBW0DBW0
PMONPMON
CKPTCKPT
LGWRLGWR
ARC0ARC0
UserUserProcessProcess
ServerServerProcessProcess
![Page 7: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/7.jpg)
Stages in Startup and Shutdown
OPENOPEN
MOUNTMOUNT
NOMOUNTNOMOUNT
SHUTDOWNSHUTDOWN
STARTUPSTARTUP
SHUTDOWNSHUTDOWN
![Page 8: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/8.jpg)
How to determine Database up or down ? (Windows)
![Page 9: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/9.jpg)
How to determine Database up or down ? (UNIX)
![Page 10: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/10.jpg)
Database & Tablespace
![Page 11: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/11.jpg)
Tablespace & Database object
![Page 12: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/12.jpg)
Oracle E-Business Suite Tablespace Design
SYSTEM
TEMP
RBS
FNDD FNDX
GLD GLX
OED OEX
![Page 13: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/13.jpg)
Establishing Connections to Oracle Servers
![Page 14: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/14.jpg)
How to determine Listener up or down?(Windows)
![Page 15: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/15.jpg)
How to determine Listener up or down?(Unix)
![Page 16: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/16.jpg)
Starting and using the Net Configuration Assistant
![Page 17: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/17.jpg)
tnsnames.ora
SEMPROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.64.32.53 )(PORT = 1520))) (CONNECT_DATA = (SERVICE_NAME = PROD)) )SEMUAT2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.64.32.54 )(PORT = 1529))) (CONNECT_DATA = (SERVICE_NAME = UAT2)) )
![Page 18: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/18.jpg)
SQL*Plus
![Page 19: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/19.jpg)
Logging into Database
開始 程式集 Oracle 9i Application Development SQL Plus
![Page 20: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/20.jpg)
Logging into Database
![Page 21: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/21.jpg)
SQL*Plus Command Summary (I)
@ & get edit execute host list /
![Page 22: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/22.jpg)
SQL*Plus Command Summary (II)
spool connect describe column set show all exit help
![Page 23: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/23.jpg)
3rd Party Tools
TOAD (Quest Software) SQL Navigator (Quest Software)
![Page 24: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/24.jpg)
Exercise 1
Installation Oracle 9i Database Create Database Connect to Database by SQL* Net (using SQL* Plus) show user
![Page 25: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/25.jpg)
Sample Schema Diagrams
![Page 26: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/26.jpg)
SQL Statement
Data Retrieval– Select
Data Manipulation Language (DML)– Insert,Update,Delete
Data Definition Language (DDL)– Create,alter,drop,rename,truncate
Transaction Control– Commit,rollback,savepoint
Data Control Language (DCL)– Grant,revoke
![Page 27: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/27.jpg)
SQL
![Page 28: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/28.jpg)
Displaying Table Structure
![Page 29: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/29.jpg)
Basic SELECT Statement
SELECT column [alias]FROM table [table alias]WHERE column = ‘xxx’
SELECT column [alias]FROM table [table alias]WHERE column = ‘xxx’
Not Case Sensitive Can be one or more lines Keywords can not be abbreviated or split across
lines
![Page 30: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/30.jpg)
Selecting All Columns, All Rows
![Page 31: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/31.jpg)
Selecting Specific Columns
![Page 32: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/32.jpg)
Arithmetic Expressions
![Page 33: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/33.jpg)
Concatenation Operator
Concatenation Operator
![Page 34: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/34.jpg)
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 NUL
L
![Page 35: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/35.jpg)
Conditions containing NULL
![Page 36: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/36.jpg)
Conditions containing NULL (example)
![Page 37: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/37.jpg)
NVL function
Converts null to an actual value Datatype that can be used are date,character,and nu
mber Datatype must match
– NVL(comm,0)– NVL(hiredate,’01-JAN-97’)– NVL(job,’Not Job Yet’)
![Page 38: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/38.jpg)
NVL Function (example)
![Page 39: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/39.jpg)
DECODE Function
Facilitates conditional inqueries by doing the work of a CASE or IF-THEN-ELSE statement
DECODE (col/express, search1, result1 [ ,search2,result2,……] [, default ])
DECODE (col/express, search1, result1 [ ,search2,result2,……] [, default ])
![Page 40: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/40.jpg)
Using the DECODE Function (example)
![Page 41: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/41.jpg)
Built-in Function Quick Ref.
![Page 42: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/42.jpg)
Preventing the Selection of Duplicate Rows
The default display of queries is all rows including duplicate rows
Eliminate duplicate rows by using DISTINCT in the SELECT clause
SQL>SELECT JOB_ID 2 FROM EMPLOYEES;
SQL>SELECT JOB_ID 2 FROM EMPLOYEES;
SQL>SELECT DISTINCT JOB_ID 2 FROM EMPLOYEES;
SQL>SELECT DISTINCT JOB_ID 2 FROM EMPLOYEES;
![Page 43: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/43.jpg)
Preventing the Selection of Duplicate Rows(example)
SQL>SELECT JOB_ID 2 FROM EMPLOYEES;
JOB_ID--------------------SA_MANSA_REPSA_REPSA_REPSH_CLERKSH_CLERK
SQL>SELECT JOB_ID 2 FROM EMPLOYEES;
JOB_ID--------------------SA_MANSA_REPSA_REPSA_REPSH_CLERKSH_CLERK
Display All Rows
SQL>SELECT DISTINCT JOB_ID 2 FROM EMPLOYEES;
JOB_ID--------------------AC_ACCOUNTAC_MGRAD_ASSTAD_PRESAD_VPFI_ACCOUNTFI_MGRHR_REP
SQL>SELECT DISTINCT JOB_ID 2 FROM EMPLOYEES;
JOB_ID--------------------AC_ACCOUNTAC_MGRAD_ASSTAD_PRESAD_VPFI_ACCOUNTFI_MGRHR_REP
Display Unique Rows
Duplicate Rows
![Page 44: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/44.jpg)
Limiting Selected Rows
![Page 45: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/45.jpg)
Comparison and Logical Operations
Logical comparison operators= > >= < <=
SQL comparison operators– BETWEEN ... AND– IN (list)– LIKE– IS NULL
Logical operators– AND– OR – NOT
![Page 46: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/46.jpg)
The WHERE Clause
SELECT first_name,last_name,job_idFROM employeesWHERE first_name = 'John‘;
SELECT first_name,last_name,job_idFROM employeesWHERE first_name = 'John‘;
FIRST_NAME LAST_NAME JOB_ID--------------- ------------------------------ ----------------John Chen FI_ACCOUNTJohn Seo ST_CLERKJohn Russell SA_MAN
FIRST_NAME LAST_NAME JOB_ID--------------- ------------------------------ ----------------John Chen FI_ACCOUNTJohn Seo ST_CLERKJohn Russell SA_MAN
![Page 47: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/47.jpg)
Negating Expressions
Logical Operators– != <> ^=
SQL Operator– NOT BETWEEN ... AND ...– NOT IN– NOT LIKE– IS NOT NULL
![Page 48: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/48.jpg)
The ORDER BY Clause
![Page 49: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/49.jpg)
Exercise 2
Installing the Human Resources (HR) Schema 列出薪資超過 10000 之員工姓名 , 受雇日期 , 部門及薪資 列出所有銷售部門的員工編號 , 姓名及薪資 , , 並依受雇日
期做 sorting (descending)
![Page 50: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/50.jpg)
Oracle Date Format
Oracle stores dates in a internal numeric format(Valid date range from January 1, 4712 BC to December 31, 9999 AD)
– Century,year,month,day,hours,minutes,seconds Default date display is DD-MON-RR SYSDATE is a function returning date and time DUAL id a dummy table used to view SYSDATE
![Page 51: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/51.jpg)
RR Date Format
![Page 52: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/52.jpg)
RR Date Format (examples)
SELECT TO_CHAR(TO_DATE(’27-OCT-98’, ’DD-MON-RR’) ,’YYYY’) "Year"FROM DUAL;
Year----1998
SELECT TO_CHAR(TO_DATE(’27-OCT-98’, ’DD-MON-RR’) ,’YYYY’) "Year"FROM DUAL;
Year----1998
SELECT TO_CHAR(TO_DATE(’27-OCT-17’, ’DD-MON-RR’) ,’YYYY’) "Year"FROM DUAL;
Year----2017
SELECT TO_CHAR(TO_DATE(’27-OCT-17’, ’DD-MON-RR’) ,’YYYY’) "Year"FROM DUAL;
Year----2017
![Page 53: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/53.jpg)
YY Date Format (examples)
SELECT TO_CHAR(TO_DATE(’27-OCT-98’, ’DD-MON-YY’) ,’YYYY’) "Year"FROM DUAL;
Year----2098
SELECT TO_CHAR(TO_DATE(’27-OCT-98’, ’DD-MON-YY’) ,’YYYY’) "Year"FROM DUAL;
Year----2098
SELECT TO_CHAR(TO_DATE(’27-OCT-17’, ’DD-MON-RR’) ,’YYYY’) "Year"FROM DUAL;
Year----2017
SELECT TO_CHAR(TO_DATE(’27-OCT-17’, ’DD-MON-RR’) ,’YYYY’) "Year"FROM DUAL;
Year----2017
![Page 54: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/54.jpg)
Display the current date
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE----------------11-OCT-03
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE----------------11-OCT-03
SQL> SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD HH24:MI:SS’) FROM DUAL;
TODAY--------------------------------------2003-10-11 23:07:01
SQL> SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD HH24:MI:SS’) FROM DUAL;
TODAY--------------------------------------2003-10-11 23:07:01
![Page 55: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/55.jpg)
Date Function
MONTHS_BETWEEN(date1,date2) ADD_MONTHS(date,n) NEXT_DAY(date,’char’) LAST_DAY(date) ROUND(date,’fmt’) TRUNC(date,’fmt’)
![Page 56: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/56.jpg)
Exercise 3
列出年資超過 3 年的員工姓名 , 薪資 , 受雇日期
![Page 57: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/57.jpg)
Displaying Data from Multiple Tables
![Page 58: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/58.jpg)
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
![Page 59: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/59.jpg)
Simple Join Query : Syntax
Write the join condition in the WHERE clause Column names must be prefixed with the table name
when the same column name appears in more than one table
Precede each column name with the table name for clarity
SELECT table1.column,table.columnFROM table1,table2WHERE table1.column1=table2.column2;
SELECT table1.column,table.columnFROM table1,table2WHERE table1.column1=table2.column2;
![Page 60: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/60.jpg)
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
![Page 61: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/61.jpg)
Equijoin
SELECT last_name, job_id, departments.department_id, department_nameFROM employees , departments WHERE employees.department_id = departments.department_id
SELECT last_name, job_id, departments.department_id, department_nameFROM employees , departments WHERE employees.department_id = departments.department_id
![Page 62: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/62.jpg)
Table Aliases
SELECT e.last_name,e.job_id, d.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_id
SELECT e.last_name,e.job_id, d.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_id
![Page 63: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/63.jpg)
Non-Equijoin
SELECT e.ename,e.sal,s.grade,s.losal,s.hisalFROM emp e,salgrade sWHERE e.sal BETWEEN s.losal AND s.hisal;
SELECT e.ename,e.sal,s.grade,s.losal,s.hisalFROM emp e,salgrade sWHERE e.sal BETWEEN s.losal AND s.hisal;
![Page 64: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/64.jpg)
Outer Join
Employees Departments
SQL
![Page 65: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/65.jpg)
Outer Join
SELECT e.first_name,d.department_name,d.department_idFROM departments d,employees eWHERE d.department_id=e.department_id(+)
SELECT e.first_name,d.department_name,d.department_idFROM departments d,employees eWHERE d.department_id=e.department_id(+)
SELECT e.first_name,d.department_name,d.department_idFROM departments d left outer join employees eON d.department_id=e.department_id
SELECT e.first_name,d.department_name,d.department_idFROM departments d left outer join employees eON d.department_id=e.department_id
Standard SQL
Oracle SQL
![Page 66: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/66.jpg)
Self Join
Employees(worker) Employees(manager)
SQL
![Page 67: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/67.jpg)
Self Join
SELECT w.last_name worker , m.last_name managerFROM employees w, employees mWHERE w.manager_id = m.employee_idAND w.last_name LIKE 'R%'
SELECT w.last_name worker , m.last_name managerFROM employees w, employees mWHERE w.manager_id = m.employee_idAND w.last_name LIKE 'R%'
![Page 68: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/68.jpg)
Group Functions
Group function operate on sets of rows to give on result per group
Group function 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
![Page 69: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/69.jpg)
Group Functions
AVG COUNT MAX MIN STDDEV SUM VARIANCE
![Page 70: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/70.jpg)
GROUP BY and HAVING in the SELECT statement
![Page 71: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/71.jpg)
Group Functions (example)
SELECT count(*) FROM employees
SELECT count(*) FROM employees
![Page 72: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/72.jpg)
Group Function (example)
SELECT job_id,SUM(SALARY),AVG(SALARY)FROM employeesGROUP BY job_idHAVING SUM(salary) > 20000ORDER BY SUM(salary)
SELECT job_id,SUM(SALARY),AVG(SALARY)FROM employeesGROUP BY job_idHAVING SUM(salary) > 20000ORDER BY SUM(salary)
![Page 73: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/73.jpg)
Subquery
Subquery is a SELECT statement embedded in a clause of another SQL statement
SELECT ...
FROM ...
WHERE
SELECT ...
FROM ...
WHERE (SELECT ... FROM ... WHERE
MainQuery
Subquery
![Page 74: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/74.jpg)
Subquery (example)
SELECT last_name,email,job_idFROM employeesWHERE department_id IN(SELECT department_id FROM departments WHERE location_id=1700)AND hire_date < '01-JAN-1994'
SELECT last_name,email,job_idFROM employeesWHERE department_id IN(SELECT department_id FROM departments WHERE location_id=1700)AND hire_date < '01-JAN-1994'
![Page 75: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/75.jpg)
SQL Convention
SELECT DISTINCTfn.dept_id dept_id,fcoam.dp_acc_id acc_id,fn.cat,fn.sub_cat,fn.amount
FROM FET_ABM_FA_NBV fn,FET_ABM_FA_DP_RATE_MAP fdr,FET_ABM_FA_COC_ACC_MAP fcoam
WHERE fn.cat = fdr.catAND fn.sub_cat = fdr.sub_catAND fn.cat = fcoam.catAND fn.sub_cat || '-NON NETWORK' = fcoam.sub_cat AND fdr.cat_noNOT IN (1,2,3,4);
SELECT DISTINCTfn.dept_id dept_id,fcoam.dp_acc_id acc_id,fn.cat,fn.sub_cat,fn.amount
FROM FET_ABM_FA_NBV fn,FET_ABM_FA_DP_RATE_MAP fdr,FET_ABM_FA_COC_ACC_MAP fcoam
WHERE fn.cat = fdr.catAND fn.sub_cat = fdr.sub_catAND fn.cat = fcoam.catAND fn.sub_cat || '-NON NETWORK' = fcoam.sub_cat AND fdr.cat_noNOT IN (1,2,3,4);
Table name alias
UpperCase & Left Alignment
![Page 76: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/76.jpg)
Exercise 4
由於 Chen (Last Name) 表現優異 , 董事長決定親自表揚 , 請找出他的工作地點 (City)
在銷售 (Sales) 部門 , 每月員工薪資支出是多少 ( 請列出部門名稱及薪資支出 )
請用計算薪資大於全公司平均薪資的員工數 請列出平均薪資大於全公司平均薪資的部門
( 列出部門名稱)
![Page 77: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/77.jpg)
DAY 2
![Page 78: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/78.jpg)
ERD
![Page 79: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/79.jpg)
Data Models
Model of systemin client’s mind
Entity model
Table model of entity model
Database Server
Table on disk
![Page 80: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/80.jpg)
Create Table Model
Step1:Map Entities to Tables Step2:Map the Attributes to Columns Step3:Map the Unique Identifiers to Primary Keys Step4:Map Relationships to Foreign Keys
![Page 81: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/81.jpg)
Table Instance Chart
Column Name Data Type Key Type Fk Table Null
department_id Number(4) PK1 N
department_name Varchar2(30) N
manager_id Number(6) FK1 employees.emp_id
Y
location_id Number(4) FK2 location.location_id
Y
DEPARTMENTS Table
![Page 82: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/82.jpg)
Creating Tables
![Page 83: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/83.jpg)
Database objects
Object Description
Table Basic unit of storage composed of rows and columns
View Logically represents subsets of data from one or more tables
Sequence Generate primary key values
Index Improves the performance of some queries
Synonym Alternate name for an object
Program Unit Procedure, function or package of SQL and PL/SQL statement
![Page 84: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/84.jpg)
Datatypes
Datatype Description
VARCHAR2(size) Variable length character value ,max 4000 bytes
CHAR(size) Fixed length character value, max 2000 bytes
NUMBER(precision,scale)
Number Values
DATE Date & Time values, January 1, 4712 BC to
December 31, 9999 AD.
CLOB Character large object, max 4Gb
BLOB Binary large object, max 4Gb
![Page 85: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/85.jpg)
Number Type
![Page 86: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/86.jpg)
Data Integrity Constraints
Constraint Description
NOT NULL Specifies that this may not contain a null value
UNIQUE Specifies a column or combination of columns whose values must be unique for all rows in the table.
PRIMARY KEY Uniquely identifies each row of the table.
FOREIGN KEY Establishes and enforces a foreign key relationship
between the column and a column of the referenced
table.
CHECK Specifies a condition that must be true.
![Page 87: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/87.jpg)
Creating Table (example)
![Page 88: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/88.jpg)
Creating Table (example)
![Page 89: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/89.jpg)
Create Table (example)
![Page 90: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/90.jpg)
Data Dictionary ( I )
Create when database is created Updated and maintained by the Oracle Server Information stored in the data dictionary
– Names of Oracle Server user– Privileges granted to users– Database object names– Table constraints– Auditing Information
![Page 91: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/91.jpg)
Data Dictionary (II)
Four classes of views (prefixes)– USER Objects owned by user– ALL Objects user has access rights– DBA All database objects– V$ Performance Information
![Page 92: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/92.jpg)
Checking Constraint on a Table
SELECT CONSTRAINT_NAME,COLUMN_NAME,POSITIONFROM USER_CONS_COLUMNSWHERE TABLE_NAME =‘DEPARTMENTS'ORDER BY CONSTRAINT_NAME,POSITION
SELECT CONSTRAINT_NAME,COLUMN_NAME,POSITIONFROM USER_CONS_COLUMNSWHERE TABLE_NAME =‘DEPARTMENTS'ORDER BY CONSTRAINT_NAME,POSITION
![Page 93: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/93.jpg)
Manipulating Data
![Page 94: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/94.jpg)
DML & Transaction Control
Command Description
INSERT Add new rows to the table
UPDATE Modify existing rows in the table
DELETE Remove existing rows in the table
COMMIT Make all pending changes permanent
SAVEPOINT Allows a rollback to that savepoint maker
ROLLBACK Discards all pending data changes
![Page 95: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/95.jpg)
Adding a New Row to a Table
INSERT INTO DEPARTMENTS (department_id,department_name,manager_id,location_id)VALUES (271,'Outsoucing',NULL,1700);
INSERT INTO DEPARTMENTS (department_id,department_name,manager_id,location_id)VALUES (271,'Outsoucing',NULL,1700);
INSERT INTO table [ ( column [ , column ...] ) ]VALUES ( value [, value ....] );
INSERT INTO table [ ( column [ , column ...] ) ]VALUES ( value [, value ....] );
Example
Syntax
![Page 96: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/96.jpg)
Copy Rows from Another Table
Write INSERT command with subquery Do not use VALUES clause Match the number of columns in the INSERT
clause to those in subquery
INSERT INTO JOB_HISTORY (employee_id,start_date,end_date,job_id,department_id)SELECT employee_id,hire_date,SYSDATE,job_id,department_idFROM employeesWHERE job_id=‘SH_CLERK’
INSERT INTO JOB_HISTORY (employee_id,start_date,end_date,job_id,department_id)SELECT employee_id,hire_date,SYSDATE,job_id,department_idFROM employeesWHERE job_id=‘SH_CLERK’
![Page 97: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/97.jpg)
Update Rows in a Tables
UPDATE employeesSET salary = salary * 1.05WHERE job_id ='SA_REP'
UPDATE employeesSET salary = salary * 1.05WHERE job_id ='SA_REP'
Example
UPDATE tableSET column = value , [column = value ][ WHERE condition ] ;
UPDATE tableSET column = value , [column = value ][ WHERE condition ] ;
Syntax
![Page 98: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/98.jpg)
Deleting Rows from a Table
DELETE employeesWHERE job_id like ‘IT%’
DELETE employeesWHERE job_id like ‘IT%’
Example
DELETE table[ WHERE condition ] ;
DELETE table[ WHERE condition ] ;
Syntax
![Page 99: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/99.jpg)
Data Consistency (Transaction)
Atomicity ( 不可分割性 ) Durability ( 持續性 )
![Page 100: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/100.jpg)
COMMIT and ROLLBACK
Ensure data consistency Preview data changes before making changes
permanent Group logically related operations
![Page 101: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/101.jpg)
State of the Data Before COMMIT or ROLLBACK
The previous state of the data can be recovered The current user can review the results of the DML
operations by using the SELECT statement Other users cannot view the results of the DML
statements by the current user The affected rows are locked; other users cannot
change the data within the affected rows
![Page 102: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/102.jpg)
State of the Data After COMMIT
The previous data is permanently lost All users can view the results Locks on the affected rows are released
![Page 103: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/103.jpg)
Committing Data (example)
SQL> INSERT INTO DEPARTMENTS 2 (department_id,department_name,manager_id,location_id) 3 VALUES (271,'Outsoucing',NULL,1700);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO DEPARTMENTS 2 (department_id,department_name,manager_id,location_id) 3 VALUES (271,'Outsoucing',NULL,1700);
1 row created.
SQL> COMMIT;
Commit complete.
![Page 104: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/104.jpg)
State of the Data After ROLLBACK
Data change are undone Previous state of the data restored Locks on the affected rows are released
![Page 105: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/105.jpg)
Rollback Data (example)
SQL> DELETE JOB_HISTORY;
10 rows deleted.
SQL> SELECT COUNT(*) FROM JOB_HISTORY;
COUNT(*)---------- 0
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT COUNT(*) FROM JOB_HISTORY;
COUNT(*)---------- 10
SQL> DELETE JOB_HISTORY;
10 rows deleted.
SQL> SELECT COUNT(*) FROM JOB_HISTORY;
COUNT(*)---------- 0
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT COUNT(*) FROM JOB_HISTORY;
COUNT(*)---------- 10
![Page 106: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/106.jpg)
Creating Sequences
CREATE SEQUENCE sequence_name [INCREMENT BY n ] [START WITH n ] [ {MAXVALUE n | NOMAXVALUE } ] [ {MINVALUE n | NOMMINVALUE } ] [ { CYCLE | NOCYCLE } ] [ { CACHE n | NOCACHE } ]
CREATE SEQUENCE sequence_name [INCREMENT BY n ] [START WITH n ] [ {MAXVALUE n | NOMAXVALUE } ] [ {MINVALUE n | NOMMINVALUE } ] [ { CYCLE | NOCYCLE } ] [ { CACHE n | NOCACHE } ]
Syntax
![Page 107: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/107.jpg)
Creating Sequences
CREATE SEQUENCE S1 INCREMENT BY 2START WITH 5MINVALUE 1MAXVALUE 10NOCACHECYCLE
CREATE SEQUENCE S1 INCREMENT BY 2START WITH 5MINVALUE 1MAXVALUE 10NOCACHECYCLE
Example
5 8 1 4 7 10 1 4 7 10
![Page 108: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/108.jpg)
What Is a View ?
![Page 109: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/109.jpg)
Advantages of Views
Restrict database access Simplify queries Present the data in a different perspective from that
of the base table Isolate applications from changes in definitions of
base tables Provide groups of users access to data according to
their particular criteria.
![Page 110: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/110.jpg)
Creating a View
The subquery can contain complex SELECT syntax The subquery cannot an ORDER BY clause
CREATE VIEW view_name[ ( alias , [ alias ]...)]AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY]
CREATE VIEW view_name[ ( alias , [ alias ]...)]AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY]
Syntax
![Page 111: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/111.jpg)
Creating a View (example)
Create the STAFF_60_V,which contain employee number, last name ,job ,manager’s employee number and department id for the employees in department 60
CREATE VIEW STAFF_60_V(employee_id,last_name,job_id,manager_id,department_id)ASSELECT employee_id,last_name,job_id,manager_id,department_idFROM employeesWHERE department_id= 60
CREATE VIEW STAFF_60_V(employee_id,last_name,job_id,manager_id,department_id)ASSELECT employee_id,last_name,job_id,manager_id,department_idFROM employeesWHERE department_id= 60
![Page 112: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/112.jpg)
Creating a Complex View (example)
Creating a complex view that contain group function to display values from two tables
CREATE VIEW DEPT_SALARY_VIEWASSELECT d.department_name,SUM(salary) sum_salary ,MIN(SALARY) min_salary,MAX(SALARY) max_salaryFROM EMPLOYEES e,DEPARTMENTS dWHERE e.department_id = d.department_idGROUP BY d.department_name
CREATE VIEW DEPT_SALARY_VIEWASSELECT d.department_name,SUM(salary) sum_salary ,MIN(SALARY) min_salary,MAX(SALARY) max_salaryFROM EMPLOYEES e,DEPARTMENTS dWHERE e.department_id = d.department_idGROUP BY d.department_name
![Page 113: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/113.jpg)
Exercise 5
Create View 列出員工姓名及主管姓名 (last name & first name)
由於業務部門績效良好 , 董事長決定全部加薪 10%, 請更改他們的薪資 , 並列出薪資超出該職務( job_id) 上限的人員
公司決定自即日起將 IT 工作外包 , 請將 IT 人員自 employees Table 中移除,並將相關資料放至 job_history
![Page 114: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/114.jpg)
What Is a Index ?
Database Object Each index is composed of column values and
pointers (or ROWID) Indexes is maintained and used by RDBMS
![Page 115: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/115.jpg)
Index Type
By constraint– Unique– Non-unique
By constitution– Single Column– Concatenated or Composite
![Page 116: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/116.jpg)
How are Indexes Created ?
Automatically– A unique index are created when you define PRIMARY KE
Y or UNIQUE KEY constraint in a table defination
Manually– CREATE INDEX command
![Page 117: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/117.jpg)
Creating and Index
CREATE [UNIQUE ] INDEX index_name ON table (column [,column]...)
CREATE [UNIQUE ] INDEX index_name ON table (column [,column]...)
Syntax
CREATE UNIQUE INDEX job_id_pk ON jobs (job_id) ;
CREATE UNIQUE INDEX job_id_pk ON jobs (job_id) ;
Example
![Page 118: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/118.jpg)
Guideline to Create an Index
The columns are used frequently in WHERE clause or in join condition
The column contain a wide range of values The column contain a large number of null value
![Page 119: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/119.jpg)
Confirming Indexes
USER_INDEXES USER_IND_COLUMNS
![Page 120: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/120.jpg)
DAY 3
![Page 121: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/121.jpg)
PL/SQL
![Page 122: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/122.jpg)
PL/SQL Block Structure
DECLARE (Optional)– Variables,constants,cursors,user-defined exception
BEGIN (Mandatory)– SQL Statements– PL/SQL Control statement
EXCEPTION (Optional)– Action to perform when errors occurs
END; (Mandatory)
![Page 123: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/123.jpg)
Example PL/SQL Block
DECLAREEmp_name VARCHAR2(10);Emp_number INTEGER;
Empno_out_of_range EXCEPTION;BEGIN
Emp_number := 10001;IF Emp_number > 9999 OR Emp_number < 1000 THENRAISE Empno_out_of_range;ELSESELECT Ename INTO Emp_name FROM Emp_tabWHERE Empno = Emp_number;DBMS_OUTPUT.PUT_LINE('Employee name is' || Emp_name);END IF;
EXCEPTIONWHEN Empno_out_of_range THENDBMS_OUTPUT.PUT_LINE('Employee number' || Emp_number ||'is out of range.');
END;
![Page 124: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/124.jpg)
Block Types
Anonymous Procedure
[DECLARE]
BEGIN
-- Statement[EXCEPTION]
END;
[DECLARE]
BEGIN
-- Statement[EXCEPTION]
END;
PROCEDURE nameIS
BEGIN
-- Statement[EXCEPTION]
END;
PROCEDURE nameIS
BEGIN
-- Statement[EXCEPTION]
END;
Function
FUNCTION nameRETURN datatypeIS
BEGIN
-- StatementRETURN value;[EXCEPTION]
END;
FUNCTION nameRETURN datatypeIS
BEGIN
-- StatementRETURN value;[EXCEPTION]
END;
![Page 125: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/125.jpg)
Program Constructs
Stored Procedure and Function (Package) Application Procedure and Function (Package) Database Trigger Application Trigger (Form ,Report) Anonymous Block
![Page 126: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/126.jpg)
Declare Variables
PL/SQL variables– Scalar– Composite– Reference (Pointer)– LOB (Large Objects)
![Page 127: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/127.jpg)
Built-in Datatypes Quick Ref.
![Page 128: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/128.jpg)
Declaring PL/SQL Variable
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr ];
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr ];
Declarev_adjust_amount NUMBER NOT NULL:= 0;v_nbv_amount NUMBER := 0;v_dis_nbv_amount NUMBER := 0;
Declarev_adjust_amount NUMBER NOT NULL:= 0;v_nbv_amount NUMBER := 0;v_dis_nbv_amount NUMBER := 0;
Syntax
Example
![Page 129: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/129.jpg)
Declaring Variables with the %TYPE Attribute
Example
…DECLAREv_ename emp.ename%TYPE;v_balance NUMBER(7,2);…
…DECLAREv_ename emp.ename%TYPE;v_balance NUMBER(7,2);…
![Page 130: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/130.jpg)
Base Scalar Datatypes
VARCHAR2(maximun_length) NUMBER[(precision,scale)] DATE CHAR(maximun_length) ………
![Page 131: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/131.jpg)
Code Naming Convention
Identifier Naming Conversion 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
![Page 132: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/132.jpg)
SELECT statement in PL/SQL
SELECT select_listINTO { variable_name [,variable_name]....
| record_name }FROM tableWHERE condition;
SELECT select_listINTO { variable_name [,variable_name]....
| record_name }FROM tableWHERE condition;
Retrieve data from the database with SELECT
Syntax
![Page 133: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/133.jpg)
SELECT statement in PL/SQL
INTO clause Queries Must Return One and Only One Row
– NO_DATA_FOUND– TOO_MANY_ROWS
![Page 134: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/134.jpg)
Simple PL/SQL Block
DECLAREv_deptno NUMBER(2);v_loc VARCHAR2(15);
BEGINSELECT deptno
,locINTO v_deptno
,v_locFROM deptWHERE dname='SALES';
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_deptno)); DBMS_OUTPUT.PUT_LINE(v_loc);END;
DECLAREv_deptno NUMBER(2);v_loc VARCHAR2(15);
BEGINSELECT deptno
,locINTO v_deptno
,v_locFROM deptWHERE dname='SALES';
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_deptno)); DBMS_OUTPUT.PUT_LINE(v_loc);END;
![Page 135: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/135.jpg)
Exercise 6
1. Create a PL/SQL block that selects the maximum department number in the DEPT table and print the results to the screen.
2. Modified the PL/SQL block you created in previous exercise to insert a new department into DEPT table
– add 10 to the maxinum department number– Use SQL*Plus substitution variable for the department name– Leave the location null
![Page 136: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/136.jpg)
Determining Variable Scope
DECLARE v_sal NUMBER(7,2) := 6000; v_comm NUMBER(7,2) := v_sal * 0.2; v_message VARCHAR2(255) := 'eligible for commission';BEGIN DECLARE v_sal NUMBER(7,2) := 50000; v_comm NUMBER(7,2) := 0; v_total_comp NUMBER(7,2) := v_sal + v_comm; BEGIN v_message := 'CLERK not' || v_message; END; v_message := 'SALESMAN' || v_message;END;
DECLARE v_sal NUMBER(7,2) := 6000; v_comm NUMBER(7,2) := v_sal * 0.2; v_message VARCHAR2(255) := 'eligible for commission';BEGIN DECLARE v_sal NUMBER(7,2) := 50000; v_comm NUMBER(7,2) := 0; v_total_comp NUMBER(7,2) := v_sal + v_comm; BEGIN v_message := 'CLERK not' || v_message; END; v_message := 'SALESMAN' || v_message;END;
![Page 137: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/137.jpg)
Commenting Code
Prefix single-line comments with two dash(--) Place multi-line comments between the symbols
/* and */
... v_sal NUMBER(7,2);BEGIN /* Compute the annual salary based on the monthly salary input from the user */ v_sal := &p_monthly_sal * 12;END; -- This is the end of the block
... v_sal NUMBER(7,2);BEGIN /* Compute the annual salary based on the monthly salary input from the user */ v_sal := &p_monthly_sal * 12;END; -- This is the end of the block
![Page 138: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/138.jpg)
Writing Control Structures
IF statement LOOP GOTO
![Page 139: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/139.jpg)
IF Statement
Conditional IF statements– IF-THEN-END IF– IF-THEN-ELSE-END IF– IF-THEN-ELSIF-END IF
![Page 140: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/140.jpg)
Simple IF Statement
...IF v_ename = 'MILLER' THENTHEN v_job := 'SALESMAN'; v_new_comn := sal * 0.2;ELSIF v_ename = 'JONES' THEN v_job := 'MANAGER'; v_new_comn := sal * 0.2;ELSE ...........END IF;
...IF v_ename = 'MILLER' THENTHEN v_job := 'SALESMAN'; v_new_comn := sal * 0.2;ELSIF v_ename = 'JONES' THEN v_job := 'MANAGER'; v_new_comn := sal * 0.2;ELSE ...........END IF;
![Page 141: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/141.jpg)
Basic Loop
DECLARE v_ordid item.ordid%TYPE := 601; v_counter NUMBER(2) := 1;BEGIN LOOP INSERT INTO item(ordid,itemid) VALUES (v_ordid,v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP;END;
DECLARE v_ordid item.ordid%TYPE := 601; v_counter NUMBER(2) := 1;BEGIN LOOP INSERT INTO item(ordid,itemid) VALUES (v_ordid,v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP;END;
![Page 142: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/142.jpg)
FOR loop
BEGIN FOR i IN 1..10 LOOP INSERT INTO item(ordid,itemid) VALUES (i,v_counter); END LOOP;END;
BEGIN FOR i IN 1..10 LOOP INSERT INTO item(ordid,itemid) VALUES (i,v_counter); END LOOP;END;
Do not declare the counter,it is declared implicitly
![Page 143: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/143.jpg)
WHILE Loop
DECLARE v_ordid item.ordid%TYPE := 601; v_counter NUMBER(2) := 1;BEGIN
WHILE v_counter <= 10 LOOP INSERT INTO item(ordid,itemid) VALUES (v_ordid,v_counter); v_counter := v_counter + 1; END LOOP;END;
DECLARE v_ordid item.ordid%TYPE := 601; v_counter NUMBER(2) := 1;BEGIN
WHILE v_counter <= 10 LOOP INSERT INTO item(ordid,itemid) VALUES (v_ordid,v_counter); v_counter := v_counter + 1; END LOOP;END; exit
![Page 144: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/144.jpg)
Nested Loop and Labels
DECLARE v_ordid item.ordid%TYPE := 601; v_counter NUMBER(2) := 1; v_dummy NUMBER;BEGIN LOOP INSERT INTO item(ordid,itemid) VALUES (v_ordid,v_counter); v_counter := v_counter + 1; IF v_counter > 10 THEN GOTO end_loop; END IF; END LOOP;<<end_loop>>v_dummy := 1;END;
DECLARE v_ordid item.ordid%TYPE := 601; v_counter NUMBER(2) := 1; v_dummy NUMBER;BEGIN LOOP INSERT INTO item(ordid,itemid) VALUES (v_ordid,v_counter); v_counter := v_counter + 1; IF v_counter > 10 THEN GOTO end_loop; END IF; END LOOP;<<end_loop>>v_dummy := 1;END;
![Page 145: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/145.jpg)
Practice 7
CREATE TABLE MESSAGES(RESULT VARCHAR2(100));
– Insert the number 1 to 10 ,excluding 6 and 8
– Select from the MESSAGES table to verify that your PL/SQL block work
Add a new column called STARS of datatype VARCHAR2 and length 20 ,to the EMP table ,Update STARS column as the following result
![Page 146: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/146.jpg)
Composite Datatypes
PL/SQL RECORDS PL/SQL TABLES
![Page 147: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/147.jpg)
PL/SQL Record
DECLARETYPE emp_record_type IS RECORD(ename VARCHAR2(10),
job VARCHAR2(9), sal NUMBER(7,2));
emp_record emp_record_type;BEGINEND;
DECLARETYPE emp_record_type IS RECORD(ename VARCHAR2(10),
job VARCHAR2(9), sal NUMBER(7,2));
emp_record emp_record_type;BEGINEND;
![Page 148: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/148.jpg)
The %ROWTYPE Attribute
DECLARE emp_record emp%ROWTYPE;BEGIN ….END;
DECLARE emp_record emp%ROWTYPE;BEGIN ….END;
![Page 149: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/149.jpg)
PL/SQL Tables
TYPE ename_table_type IS TABLE of emp.ename%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
TYPE ename_table_type IS TABLE of emp.ename%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
![Page 150: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/150.jpg)
Writing Explicit Cursor
![Page 151: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/151.jpg)
About Cursors
– Implicit Cursors :Declared for all DML and PL/SQL SELECT statement
– Explicit Cursors:Declared and named by the programmer
Every SQL Statement executed by the Oracle Server has an indifidual cursor associated with it
![Page 152: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/152.jpg)
Explicit Cursor Function
7369 SMITH CLERK7499 ALLEN SALESMAN7521 WARD SALESMAN7566 JONES MANAGER7654 MARTIN SALESMAN7698 BLAKE MANAGER
cursor Current row
![Page 153: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/153.jpg)
Controlling Explicit Cursor
DECLARE FETCH CLOSEOPEN EMPTY?
NO
YES
![Page 154: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/154.jpg)
Explicit Cursor example
DECLARE CURSOR emp_cur IS SELECT ename,job FROM EMP; emp_record emp_cur%ROWTYPE;BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_record; EXIT WHEN emp_cur%NOTFOUND; END LOOP; CLOSE emp_cur;END;
DECLARE CURSOR emp_cur IS SELECT ename,job FROM EMP; emp_record emp_cur%ROWTYPE;BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_record; EXIT WHEN emp_cur%NOTFOUND; END LOOP; CLOSE emp_cur;END;
![Page 155: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/155.jpg)
Explicit Cursor Attribute
Attribute Type Description
%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 if the most recent fetch returns a row
%ROWCOUNT Number Evaluates to the total number of rows returned so far
![Page 156: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/156.jpg)
Cursor FOR Loops
DECLARE CURSOR emp_cur IS SELECT ename,job FROM EMP;BEGIN FOR emp_record IN emp_cur LOOP -- implicit open and implicit fetch occur IF emp_record.deptno = 30 THEN ......... END IF; END LOOP; -- implicit close occursEND;
DECLARE CURSOR emp_cur IS SELECT ename,job FROM EMP;BEGIN FOR emp_record IN emp_cur LOOP -- implicit open and implicit fetch occur IF emp_record.deptno = 30 THEN ......... END IF; END LOOP; -- implicit close occursEND;
![Page 157: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/157.jpg)
Cursors with Parameters
DECLARE CURSOR emp_cursor (p_deptno NUMBER,p_job VARCHAR2) IS SELECT empno,ename FROM emp WHERE deptno = p_deptno AND job = p_job;BEGIN OPEN emp_cursor (10,'CLERK')......
DECLARE CURSOR emp_cursor (p_deptno NUMBER,p_job VARCHAR2) IS SELECT empno,ename FROM emp WHERE deptno = p_deptno AND job = p_job;BEGIN OPEN emp_cursor (10,'CLERK')......
Pass the department number and job title to the WHERE clause do not give them sizes
![Page 158: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/158.jpg)
Cursor with Parameters
DECLARE CURSOR emp_cursor (p_deptno NUMBER,p_job VARCHAR2) IS SELECT empno,ename FROM emp WHERE deptno = p_deptno AND job = p_job;BEGIN FOR emp_record IN emp_cursor(10,'CLERK') LOOP ..... END LOOP;
DECLARE CURSOR emp_cursor (p_deptno NUMBER,p_job VARCHAR2) IS SELECT empno,ename FROM emp WHERE deptno = p_deptno AND job = p_job;BEGIN FOR emp_record IN emp_cursor(10,'CLERK') LOOP ..... END LOOP;
You can pass parameters to the cursor used in a cursor FOR loop
![Page 159: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/159.jpg)
The FOR UPDATE Clause
DECLARE CURSOR emp_cursor IS SELECT empno,ename FROM emp WHERE deptno = 30 FOR UPDATE [ OF sal ] NOWAIT;
DECLARE CURSOR emp_cursor IS SELECT empno,ename FROM emp WHERE deptno = 30 FOR UPDATE [ OF sal ] NOWAIT;
Optional
![Page 160: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/160.jpg)
The FOR UPDATE Clause
Lock the affected rows when the cursor is opened You should not commit across fetched from a explicit curs
or if FOR UPDATE is used(because the Oracle Server releases locks at the end fo the transaction)
FOR UPDATE clause is the last clause in a select statement
![Page 161: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/161.jpg)
The WHERE CURRENT OF Clause
Use cursors to update or delete the current row Include the FOR UPDATE clause in the cursor query to lock the rows
first Use the WHERE CURRENT OF clause to reference the current row
from an explicit cursor
Syntax:
WHERE CURRENT OF cursor;WHERE CURRENT OF cursor;
![Page 162: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/162.jpg)
The WHERE CURRENT OF Clause (example)
DECLARE CURSOR sal_cursor IS SELECT sal FROM emp WHERE deptno = 30 FOR UPDATE OF sal NOWAIT;BEGIN FOR emp_record IN sal_cursor LOOP
UPDATE empSET sal = sal * 1.10WHERE CURRENT OF sal_cursor;
END LOOP;END;
DECLARE CURSOR sal_cursor IS SELECT sal FROM emp WHERE deptno = 30 FOR UPDATE OF sal NOWAIT;BEGIN FOR emp_record IN sal_cursor LOOP
UPDATE empSET sal = sal * 1.10WHERE CURRENT OF sal_cursor;
END LOOP;END;
![Page 163: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/163.jpg)
Summary (Cursor)
Cursor type:– Implicit cursors:Used for all DML statements and single-row querie
s– Explicit cursors:Used for queries of zero,one,or more rows
You can manipulate explicit cursors You can evaluate the cursor status by using cursor attributes You can use cursor FOR loops
![Page 164: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/164.jpg)
Handling Exceptions
![Page 165: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/165.jpg)
Handling Exceptions with PL/SQL
What is an exception– In PL/SQL, a warning or error condition is called an exception.
How is it raised?– An Oracle error occurs– You raise it explicitly
How do you handle it?– Trap it with a handle it– Propagate it to the calling environment
![Page 166: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/166.jpg)
Handling Exception
Trap the exception Propagate the exception
DECLARE
BEGIN
EXCEPTION
END;
exception is raised
exception is trapped
DECLARE
BEGIN
EXCEPTION
END;
exception is raised
exception is not trapped
Excepiton propagates to calling environment
![Page 167: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/167.jpg)
Exception Types
Predefined Oracle Server Non-predefined Oracle Server User-defined
Implicitly raised
Explicitly raised
![Page 168: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/168.jpg)
Trapping Exceptions Guildlines
WHEN OTHERS is the last cluase EXCEPTION keyword start exception handling section Serveral exception handlers are allowed Only one handler is processed before leaving the block
![Page 169: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/169.jpg)
Predefined Exception (example)
BEGIN EXCEPTION WHEN NO_DATA_FOUND THEN statement1; statement2; WHEN TOO_MANY_ROWS THEN statement1; statement2; WHEN OTHERS THEN statement1; statement2; END;
BEGIN EXCEPTION WHEN NO_DATA_FOUND THEN statement1; statement2; WHEN TOO_MANY_ROWS THEN statement1; statement2; WHEN OTHERS THEN statement1; statement2; END;
![Page 170: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/170.jpg)
Predefined Oracle Server Errors
![Page 171: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/171.jpg)
Using SQLCODE & SQLERRM
![Page 172: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/172.jpg)
User-Defined Exception
![Page 173: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/173.jpg)
How Exception Propagate
![Page 174: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/174.jpg)
How Exception Propagate
![Page 175: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/175.jpg)
How Exception Propagate
![Page 176: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/176.jpg)
Practice 8
Write a PL/SQL block to select the name of the employee with a given salary,insert the result in MESSAGES table
RESULT--------------------------------------------------------------------------------SMITH -800More than one employee with a salary of 3000No employee with a salary 6000
![Page 177: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/177.jpg)
Stored Procedures & Functions
![Page 178: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/178.jpg)
Create Stored Procedure (example)
CREATE OR REPLACE PROCEDURE raise_salary (emp_id INTEGER, amount NUMBER) IScurrent_salary NUMBER;salary_missing EXCEPTION;
BEGINSELECT sal INTO current_salary FROM empWHERE empno = emp_id;IF current_salary IS NULL THENRAISE salary_missing;ELSEUPDATE emp SET sal = sal + amountWHERE empno = emp_id;END IF;
EXCEPTIONWHEN NO_DATA_FOUND THENINSERT INTO emp_audit VALUES (emp_id, 'No such number');WHEN salary_missing THENINSERT INTO emp_audit VALUES (emp_id, 'Salary is null');
END raise_salary;
CREATE OR REPLACE PROCEDURE raise_salary (emp_id INTEGER, amount NUMBER) IScurrent_salary NUMBER;salary_missing EXCEPTION;
BEGINSELECT sal INTO current_salary FROM empWHERE empno = emp_id;IF current_salary IS NULL THENRAISE salary_missing;ELSEUPDATE emp SET sal = sal + amountWHERE empno = emp_id;END IF;
EXCEPTIONWHEN NO_DATA_FOUND THENINSERT INTO emp_audit VALUES (emp_id, 'No such number');WHEN salary_missing THENINSERT INTO emp_audit VALUES (emp_id, 'Salary is null');
END raise_salary;
![Page 179: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/179.jpg)
Execute Stored Procedure
EXEC RAISE_SALARY(7900,300)EXEC RAISE_SALARY(7900,300)
BEGIN RAISE_SALARY(7900,300)END;
BEGIN RAISE_SALARY(7900,300)END;
BEGIN RAISE_SALARY (emp_id => 7900 amount => 300 );END;
BEGIN RAISE_SALARY (emp_id => 7900 amount => 300 );END;
![Page 180: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/180.jpg)
Create Stored Function (example)
CREATE OR REPLACE FUNCTION emp_sal_query (i_empno NUMBER) RETURN NUMBERIS v_sal NUMBER;BEGIN
SELECT sal INTO v_salFROM empWHERE empno = i_empno;
RETURN v_sal;EXCEPTION
WHEN NO_DATA_FOUND THENRETURN NULL;
END emp_sal_query;
CREATE OR REPLACE FUNCTION emp_sal_query (i_empno NUMBER) RETURN NUMBERIS v_sal NUMBER;BEGIN
SELECT sal INTO v_salFROM empWHERE empno = i_empno;
RETURN v_sal;EXCEPTION
WHEN NO_DATA_FOUND THENRETURN NULL;
END emp_sal_query;
![Page 181: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/181.jpg)
Execute Stored Function
SELECT emp_sal_query(7369) FROM dual;SELECT emp_sal_query(7369) FROM dual;
SELECT emp_sal_query(empno),ename FROM emp;SELECT emp_sal_query(empno),ename FROM emp;
DECLARE
v_sal NUMBER;
BEGIN
v_sal := emp_sal_query(7369) ;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_sal));
END;
DECLARE
v_sal NUMBER;
BEGIN
v_sal := emp_sal_query(7369) ;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_sal));
END;
![Page 182: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/182.jpg)
Practice 9
![Page 183: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/183.jpg)
SQL*Loader Overview
![Page 184: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/184.jpg)
Discarded and Rejected Records
![Page 185: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/185.jpg)
SQL*Loader Case Studies
$ORACLE_HOME/rdbms/demo/ulcase*.*
![Page 186: Oracle SQL introduction. 使用聲明 請尊重智慧財產權 本資料僅限高雄應用科技大學資管系教學使用 未經允許請勿擅自複製、散佈、使用.](https://reader037.fdocument.pub/reader037/viewer/2022102608/56649cab5503460f9496cf4f/html5/thumbnails/186.jpg)
Recommendable
Oracle Essentials:Oracle 9i,Oracle8i & Oracle8– O’RELLY
Oracle Design – O’REILLY Oracle SQL High-Performance Tuning—Prentice Hal
l Oracle 資料庫管理實務—旗標 Oracle 9i Java 程式設計– 金禾