tutorial oracle.pdf

Oracle/SQLTutorial 1

MichaelGertz

DatabaseandInformationSystemsGroup

DepartmentofComputerScience

UniversityofCalifornia,Davis

gertz@cs.ucdavis.edu

http://www.db.cs.ucdavis.edu

ThisOracle/SQLtutorialprovidesadetailedintroductiontotheSQLquerylanguageandthe

OracleRelationalDatabaseManagementSystem.FurtherinformationaboutOracleandSQL

canbefoundonthewebsite www.db.cs.ucdavis.edu/dbs .

Comments,corrections,oradditionstothesenotesarewelcome.ManythankstoChristina

Chungforcommentsonthepreviousversion.

RecommendedLiterature

ThecompleteOracleDocumentationisavailableonlineat technet.oracle.com .Freesub-

scription!

OraclePresshasseveralgoodbooksonvariousOracletopics.See www.osborne.com/oracle/

O’Reillyhasabout30excellentOraclebooks,includingStevenFeuerstein’sOraclePL/SQL

Programming(3rdedition).See oracle.oreilly.com .

JimMeltonandAlanR.Simon:SQL:1999-UnderstandingRelationalLanguageComponents

(1stEdition,May2001),MorganKaufmann.

JimCelkohasacoupleofverygoodbooksthatcoveradvancedSQLqueriesandprogramming.

Checkanyofyourfavorite(online)bookstore.

Ifyouwanttoknowmoreaboutconstraintsandtriggers,youmightwanttocheckthefol-

lowingarticle:CanT¨urkerandMichaelGertz:SemanticIntegritySupportinSQL:1999and

Commercial(Object-)RelationalDatabaseManagementSystems.TheVLDBJournal,Volume

10,Number4,241-269.

Contents

1.SQL–StructuredQueryLanguage

1.1.Tables 1

1.2.Queries(PartI) 3

1.3.DataDeﬁnitioninSQL 6

1.4.DataModiﬁcationsinSQL 9

1.5.Queries(PartII) 11

1.6.Views

2.SQL*Plus(MinimalUserGuide,EditorCommands,HelpSystem) 20

3.OracleDataDictionary 23

4.ApplicationProgramming

4.1.PL/SQL

4.1.1Introduction 26

4.1.2StructureofPL/SQLBlocks 27

4.1.3Declarations 27

4.1.4LanguageElements 28

4.1.5ExceptionHandling 32

4.1.6ProceduresandFunctions 34

4.1.7Packages 36

4.1.8ProgramminginPL/SQL 38

4.2.EmbeddedSQLandPro*C 39

5.IntegrityConstraintsandTriggers

5.1.IntegrityConstraints

5.1.1CheckConstraints 46

5.1.2ForeignKeyConstraints 47

5.1.3MoreAboutColumn-andTableConstraints 49

5.2.Triggers

5.2.1Overview 50

5.2.2StructureofTriggers 50

5.2.3ExampleTriggers 53

5.2.4ProgrammingTriggers 55

6.SystemArchitecture

6.1.StorageManagementandProcesses 58

6.2.LogicalDatabaseStructures 60

6.3.PhysicalDatabaseStructures 61

6.4.StepsinProcessinganSQLStatement 63

6.5.CreatingDatabaseObjects 63

1SQL–StructuredQueryLanguage

1.1Tables

Inrelationaldatabasesystems(DBS)dataarerepresentedusingtables(relations).Aquery

issuedagainsttheDBSalsoresultsinatable.Atablehasthefollowingstructure:

Column1Column2... Columnn

−Tuple(orRecord)

... ... ... ...

Atableisuniquelyidentiﬁedbyitsnameandconsistsofrowsthatcontainthestoredinforma-

tion,eachrowcontainingexactlyonetuple(orrecord).Atablecanhaveoneormorecolumns.

Acolumnismadeupofacolumnnameandadatatype,anditdescribesanattributeofthe

tuples.Thestructureofatable,alsocalledrelationschema,thusisdeﬁnedbyitsattributes.

Thetypeofinformationtobestoredinatableisdeﬁnedbythedatatypesoftheattributes

attablecreationtime.

SQLusesthetermstable,row,andcolumnforrelation,tuple,andattribute,respectively.In

thistutorialwewillusethetermsinterchangeably.

Atablecanhaveupto254columnswhichmayhavedierentorsamedatatypesandsetsof

values(domains),respectively.Possibledomainsarealphanumericdata(strings),numbersand

dateformats.Oracleoersthefollowingbasicdatatypes:

•char(n):Fixed-lengthcharacterdata(string),ncharacterslong.Themaximumsizefor

nis255bytes(2000inOracle8).Notethatastringoftypecharisalwayspaddedon

rightwithblankstofulllengthofn.(+canbememoryconsuming).

Example:char(40)

•varchar2(n):Variable-lengthcharacterstring.Themaximumsizefornis2000(4000in

Oracle8).Onlythebytesusedforastringrequirestorage.Example:varchar2(80)

•number(o,d):Numericdatatypeforintegersandreals.o=overallnumberofdigits,d

=numberofdigitstotherightofthedecimalpoint.

Maximumvalues:o=38,d=−84to+127.Examples:number(8),number(5,2)

Notethat,e.g.,number(5,2)cannotcontainanythinglargerthan999.99withoutresult-

inginanerror.Datatypesderivedfromnumberareint[eger],dec[imal],smallint

andreal.

•date:Datedatatypeforstoringdateandtime.

Thedefaultformatforadateis:DD-MMM-YY.Examples:’13-OCT-94’,’07-JAN-98’

•long:Characterdatauptoalengthof2GB.Onlyonelongcolumnisallowedpertable.

Note:InOracle-SQLthereisnodatatypeboolean.Itcan,however,besimulatedbyusing

eitherchar(1)ornumber(1).

Aslongasnoconstraintrestrictsthepossiblevaluesofanattribute,itmayhavethespecial

valuenull(forunknown).Thisvalueisdierentfromthenumber0,anditisalsodierent

fromtheemptystring ’’ .

Furtherpropertiesoftablesare:

•theorderinwhichtuplesappearinatableisnotrelevant(unlessaqueryrequiresan

explicitsorting).

•atablehasnoduplicatetuples(dependingonthequery,however,duplicatetuplescan

appearinthequeryresult).

Adatabaseschemaisasetofrelationschemas.Theextensionofadatabaseschemaatdatabase

run-timeiscalledadatabaseinstanceordatabase,forshort.

1.1.1ExampleDatabase

Inthefollowingdiscussionsandexamplesweuseanexampledatabasetomanageinformation

aboutemployees,departmentsandsalaryscales.Thecorrespondingtablescanbecreated

undertheUNIXshellusingthecommanddemobld.Thetablescanbedroppedbyissuing

thecommanddemodropundertheUNIXshell.

Thetable EMP isusedtostoreinformationaboutemployees:

EMPNOENAMEJOB MGRHIREDATESALDEPTNO

7369SMITHCLERK 790217-DEC-8080020

7499ALLENSALESMAN769820-FEB-81160030

7521WARDSALESMAN769822-FEB-81125030

...........................................................

7698BLAKEMANAGER 01-MAY-81385030

7902FORDANALYST756603-DEC-81300010

Fortheattributes,thefollowingdatatypesaredeﬁned:

EMPNO :number(4), ENAME :varchar2(30), JOB :char(10), MGR :number(4),

HIREDATE :date, SAL :number(7,2), DEPTNO :number(2)

Eachrow(tuple)fromthetableisinterpretedasfollows:anemployeehasanumber,aname,

ajobtitleandasalary.Furthermore,foreachemployeethenumberofhis/hermanager,the

datehe/shewashired,andthenumberofthedepartmentwherehe/sheisworkingarestored.

Thetable DEPT storesinformationaboutdepartments(number,name,andlocation):

DEPTNODNAME LOC

10 STORE CHICAGO

20 RESEARCHDALLAS

30 SALES NEWYORK

40 MARKETINGBOSTON

Finally,thetable SALGRADE containsallinformationaboutthesalaryscales,moreprecisely,the

maximumandminimumsalaryofeachscale.

GRADELOSALHISAL

1 700 1200

2 12011400

3 14012000

4 20013000

5 30019999

1.2Queries(PartI)

Inordertoretrievetheinformationstoredinthedatabase,theSQLquerylanguageisused.In

thefollowingwerestrictourattentiontosimpleSQLqueriesanddeferthediscussionofmore

complexqueriestoSection1.5

InSQLaqueryhasthefollowing(simpliﬁed)form(componentsinbrackets[]areoptional):

select[distinct]<column(s)>

from<table>

[where<condition>]

[orderby<column(s)[asc|desc]>]

1.2.1SelectingColumns

Thecolumnstobeselectedfromatablearespeciﬁedafterthekeywordselect.Thisoperation

isalsocalledprojection.Forexample,thequery

select LOC,DEPTNO from DEPT ;

listsonlythenumberandthelocationforeachtuplefromtherelation DEPT .Ifallcolumns

shouldbeselected,theasterisksymbol“”canbeusedtodenoteallattributes.Thequery

selectfrom EMP ;

retrievesalltupleswithallcolumnsfromthetable EMP .Insteadofanattributename,theselect

clausemayalsocontainarithmeticexpressionsinvolvingarithmeticoperatorsetc.

select ENAME,DEPTNO,SAL 1.55fromEMP;

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: