Oracle/SQL Tutorial1 Michael Gertz Datadeep and Instruction Systems Assemblage Office of Computer Science University of California, Davis [email protected] ucdavis. edu http://www. db. cs. ucdavis. edu This Oracle/SQL tutorial provides a purposeed carrying to the SQL question speech and the Oracle Abstruse Datadeep Skill System. Further instruction encircling Oracle and SQL can be fix on the web situation www. db. cs. ucdavis. edu/dbs. Comments, rectifyions, or abstracteds to these musics are gratifying. Condensed felicitation to Christina Chung for expatiates on the foregoing rendering. Recommended Literature
George Koch and Kevin Loney: Oracle8 The Adequate Reference (The Sole Most Comprehensive Sourcedimensions for Oracle Server, Includes CD delay chosenronic rendering of the dimensions), 1299 pages, McGraw-Hill/Osborne, 1997. Michael Abbey and Michael Corey: Oracle8 : A Beginner’s Guide [A Thorough Superfluous for First-span Users], 767 pages, McGraw-Hill/Osborne, 1997. Stplain Feuerstein, Bill Pribyl, Debby Russell: Oracle PL/SQL Programming (2nd Edition), O’Reilly & Associates, 1028 pages, 1997. C. J. Limit and Hugh Darwen: A Guide to the SQL Measure (4th Edition), Addison-Wesley, 1997. Jim Melton and Alan R.
Simon: Understanding the New SQL: A Adequate Guide (2nd Edition, Dec 2000), The Morgan Kaufmann Series in Foundation Skill Systems, 2000. 1 revised Rendering 1. 01, January 2000, Michael Gertz, Copystraight 2000. Variation 1. SQL – Structured Question Speech 1. 1. Tables 1. 2. Queries (Part I) 1. 3. Foundation De? nition in SQL 1. 4. Foundation Modi? cations in SQL 1. 5. Queries (Part II) 1. 6. Views 1 3 6 9 11 19 2. SQL*Plus (Minimal User Guide, Editor Commands, Acceleration System) 20 3. Oracle Foundation Vocabulary 23 4. Contact Programming 4. 1. PL/SQL 4. 1. 1 Superfluous 4. 1. 2 Building of PL/SQL Blocks 4. 1. Declarations 4. 1. 4 Speech Elements 4. 1. 5 Qualification Handling 4. 1. 6 Procedures and Functions 4. 1. 7 Packages 4. 1. 8 Programming in PL/SQL 4. 2. Embedded SQL and Pro*C 26 27 27 28 32 34 36 38 39 5. Parity Constraints and Triggers 5. 1. Parity Constraints 5. 1. 1 Curb Constraints 5. 1. 2 Foreign Key Constraints 5. 1. 3 Further Encircling Column- and Board Constraints 5. 2. Triggers 5. 2. 1 Overend 5. 2. 2 Building of Triggers 5. 2. 3 Sample Triggers 5. 2. 4 Programming Triggers 6. Plan Architecture 6. 1. Storage Skill and Processes 6. 2. Obstruct Datadeep Structures 6. 3.
Physical Datadeep Structures 6. 4. Steps in Processing an SQL Affirmation 6. 5. Creating Datadeep Objects 46 47 49 50 50 53 55 58 60 61 63 63 1 SQL – Structured Question Speech 1. 1 Tables In abstruse rootationdeep plans (DBS) rootation are dramatizeed using boards (relations ). A question upshotd athwart the DBS to-boot outcomes in a board. A board has the behindcited uplifting: Shaft 1 Shaft 2 . . . Shaft n ... ... ... ... A board is rarely identi? ed by its indicate and depends of rows that comprehend the ammunitiond instruction, each row comprehending indisputably one tuple (or archives ). A board can possess one or further shafts.
A shaft is made up of a shaft indicate and a rootation husk, and it describes an mark of the tuples. The uplifting of a board, to-boot determined homogeneity schema, thus is de? ned by its marks. The husk of instruction to be ammunitiond in a board is de? ned by the rootation husks of the marks at board figment span. SQL uses the provisions board, row, and shaft for homogeneity, tuple, and mark, appertainingly. In this tutorial we deserve use the provisions interchangeably. A board can possess up to 254 shafts which may possess di? edivulsion or selfselfselfselfselfresembling rootation husks and sets of treasures (domains), appertainingly.
Possible estates are alphanumeric rootation (strings), quantity and limit fashionats. Oracle o? ers the behindcited basic rootation husks: • char(n): Fixed-elongation arrange rootation (string), n arranges hanker. The acme dimension for n is 255 bytes (2000 in Oracle8). Music that a string of husk char is invariablely pacquired on straight delay blanks to generous elongation of n. ( can be perpetuation consuming). Example: char(40) • varchar2(n): Variable-elongation arrange string. The acme dimension for n is 2000 (4000 in Oracle8). Singly the bytes used for a string insist-upon storage. Example: varchar2(80) • reckon(o, d): Numeric rootation husk for integers and motiveives. = overall reckon of digits, d = reckon of digits to the straight of the decimal top. Acme treasures: o =38, d= ? 84 to +127. Examples: reckon(8), reckon(5,2) Music that, e. g. , reckon(5,2) cannot comprehend anything larger than 999. 99 delayout outcomeing in an deception. Foundation husks segregateial from reckon are int[eger], dec[imal], diminutiveint and motiveive. • limit: Limit rootation husk for storing limit and span. The omission fashionat for a limit is: DD-MMM-YY. Examples : ’13-OCT-94’, ’07-JAN-98’ 1 • hanker: Arrange rootation up to a elongation of 2GB. Singly one hanker shaft is undisputed per board. Note: In Oracle-SQL tnear is no rootation husk boolean.
It can, at-last, be pretended by using either char(1) or reckon(1). As hanker as no bustle incarcerates the approvely treasures of an mark, it may possess the eeexceptional treastrong ineffectual (for unrecognized). This treastrong is di? edivulsion from the reckon 0, and it is to-boot di? edivulsion from the emptiness string ’’. Further properties of boards are: • the arrange in which tuples pretence in a board is not alienate (original a question insist-upons an patent sorting). • a board has no approveness tuples (depending on the question, at-last, approveness tuples can pretence in the question outcome). A rootationdeep schema is a set of homogeneity schemas.
The emanationion of a rootationdeep schema at rootationdeep run-span is determined a rootationdeep activeing or rootationbase, for abrupt. 1. 1. 1 Sample Datadeep In the behindcited discourses and samples we use an sample rootationdeep to train instruction encircling employees, offices and sanctionance flakes. The identical boards can be mouldd beneath the UNIX shell using the instruct demobld. The boards can be emanateped by issuing the instruct demoemanate beneath the UNIX shell. The board EMP is used to ammunition instruction encircling employees: EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30 7521
WARD SALESMAN 7698 22-FEB-81 1250 30 ........................................................... 7698 BLAKE MANAGER 01-MAY-81 3850 30 7902 FORD ANALYST 7566 03-DEC-81 3000 10 For the marks, the behindcited rootation husks are de? ned: EMPNO:number(4), ENAME:varchar2(30), JOB:char(10), MGR:number(4), HIREDATE:date, SAL:number(7,2), DEPTNO:number(2) Each row (tuple) from the board is interpreted as thrives: an employee has a reckon, a indicate, a job epithet and a sanctionance. Furthermore, for each employee the reckon of his/her trainr, the limit he/she was remunerated, and the reckon of the office wnear he/she is established are ammunitiond. 2
The board DEPT ammunitions instruction encircling offices (number, indicate, and colony): DEPTNO 10 20 30 40 DNAME STORE RESEARCH SALES MARKETING LOC CHICAGO DALLAS NEW YORK BOSTON Finally, the board SALGRADE comprehends all instruction encircling the sanctionance flakes, further indisputably, the acme and poverty sanctionance of each flake. GRADE 1 2 3 4 5 1. 2 LOSAL 700 1201 1401 2001 3001 HISAL 1200 1400 2000 3000 9999 Queries (Part I) In arrange to recover the instruction ammunitiond in the rootationbase, the SQL question speech is used. In the behindcited we incarcerate our notice to gonere SQL queries and prorogue the disseries of further tangled queries to Qualification 1. In SQL a question has the behindcited (simpli? ed) fashion (components in brackets [ ] are libertyal): excellenteded [distinct] from [ wnear ] [ arrange by ] 1. 2. 1 Selecting Columns The shafts to be excellenteded from a board are speci? ed behind the keyengagement excellented. This performance is to-boot determined propulsion. For sample, the question excellenteded LOC, DEPTNO from DEPT; inventorys singly the reckon and the colony for each tuple from the homogeneity DEPT. If all shafts should be excellenteded, the asterisk capacity “? ” can be used to play all marks. The question excellenteded ? from EMP; recovers all tuples delay all shafts from the board EMP.
Instead of an mark indicate, the excellenteded stipulation may to-boot comprehend arithmetic indications involving arithmetic operators etc. excellenteded ENAME, DEPTNO, SAL ? 1. 55 from EMP; 3 For the di? edivulsion rootation husks attended in Oracle, diverse operators and exercises are supposing: • for quantity: abs, cos, sin, exp, log, effectiveness, mod, sqrt, +, ? , ? , /, . . . • for strings: chr, concat(string1, string2), inferior, conspicuous, rearrange(string, inquiry string, rearrangement string), construe, substr(string, m, n), elongation, to limit, . . . • for the limit rootation husk: add month, month betwixt, contiguous day, to char, . . The action of these performances is illustrative in purpose in the SQL*Plus acceleration plan (see to-boot Qualification 2). Cogitate the question excellenteded DEPTNO from EMP; which recovers the office reckon for each tuple. Typically, some quantity deserve pretence further than singly unintermittently in the question outcome, that is, approveness outcome tuples are not effortlessally eliminated. Inserting the keyengagement refractory behind the keyengagement excellented, at-last, forces the conclusion of approvenesss from the question outcome. It is to-boot approvely to indicate a sorting arrange in which the outcome tuples of a question are spread-outed.
For this the arrange by stipulation is used and which has one or further marks inventoryed in the excellenteded stipulation as parameter. desc speci? es a descending arrange and asc speci? es an ascending arrange (this is to-boot the omission arrange). For sample, the question excellenteded ENAME, DEPTNO, HIREDATE from EMP; from EMP arrange by DEPTNO [asc], HIREDATE desc; spread-outs the outcome in an ascending arrange by the mark DEPTNO. If two tuples possess the selfselfselfselfselfresembling mark treastrong for DEPTNO, the sorting criteria is a descending arrange by the mark treasures of HIREDATE. For the over question, we would get the behindcited output: ENAME DEPTNO HIREDATE
FORD 10 03-DEC-81 SMITH 20 17-DEC-80 BLAKE 30 01-MAY-81 WARD 30 22-FEB-81 ALLEN 30 20-FEB-81 ........................... 1. 2. 2 Selection of Tuples Up to now we possess singly focused on excellenteding (some) marks of all tuples from a board. If one is careful in tuples that content established provisions, the wnear stipulation is used. In a wnear stipulation gonere provisions fixed on similitude operators can be adequately using the obstruct connectives and, or, and not to fashion tangled provisions. Provisions may to-boot embody shape contesting performances and plain subqueries (Section 1. 5). 4 Example:
List the job epithet and the sanctionance of those employees whose trainr has the reckon 7698 or 7566 and who deserve further than 1500: excellenteded JOB, SAL from EMP wnear (MGR = 7698 or MGR = 7566) and SAL > 1500; For all rootation husks, the similitude operators =, ! = or , , are undisputed in the provisions of a wnear stipulation. Further similitude operators are: • Set Conditions: [not] in () Example: excellenteded ? from DEPT wnear DEPTNO in (20,30); • Ineffectual treasure: is [not] ineffectual, i. e. , for a tuple to be excellenteded tnear must (not) remain a de? ned treastrong for this shaft. Example: excellenteded ? from EMP wnear MGR is not ineffectual; Note: the performances = ineffectual and ! ineffectual are not de? ned! • Estate provisions: [not] betwixt and Example: • excellenteded EMPNO, ENAME, SAL from EMP wnear SAL betwixt 1500 and 2500; • excellenteded ENAME from EMP wnear HIREDATE betwixt ’02-APR-81’ and ’08-SEP-81’; 1. 2. 3 String Operations In arrange to assimilate an mark delay a string, it is insist-upond to achieve the string by apostrophes, e. g. , wnear LOCATION = ’DALLAS’. A effectivenessful operator for shape contesting is the approve operator. Contemporaneously delay this operator, two eeexceptional arranges are used: the percent indication % (to-boot determined desert card), and the beneathcord , to-boot determined comcomposition marker.
For sample, if one is careful in all tuples of the board DEPT that comprehend two C in the indicate of the office, the situation would be wnear DNAME approve ’%C%C%’. The percent indication resources that any (sub)string is undisputed there, plain the emptiness string. In opposition, the beneathcord stands for indisputably one arrange. Thus the situation wnear DNAME approve ’%C C%’ would insist-upon that indisputably one arrange pretences betwixt the two Cs. To experiment for inadequacy, the not stipulation is used. Further string performances are: • conspicuous() takes a string and converts any lore in it to conspicuouscase, e. g. DNAME = conspicuous(DNAME) (The indicate of a office must depend singly of conspicuous deed lore. ) • inferior() converts any communication to inferiorcase, • initcap() converts the judicious communication of full engagement in to conspicuouscase. • elongation() income the elongation of the string. • substr(, n [, m]) clips out a m arrange fraction of , set-outing at comcomposition n. If m is not speci? ed, the end of the string is inconsequent. substr(’DATABASE SYSTEMS’, 10, 7) income the string ’SYSTEMS’. 5 1. 2. 4 Bearing Functions Bearing exercises are statistical exercises such as estimate, min, max etc. They are used to abuse a sole treastrong from a set of mark treasures of a shaft: estimate ax min sum avg Note: 1. 3 1. 3. 1 Counting Rows Example: How condensed tuples are ammunitiond in the homogeneity EMP? excellenteded estimate(? ) from EMP; Example: How condensed di? edivulsion job epithets are ammunitiond in the homogeneity EMP? excellenteded estimate(refractory JOB) from EMP; Acme treastrong for a shaft Poverty treastrong for a shaft Example: Inventory the poverty and acme sanctionance. excellenteded min(SAL), max(SAL) from EMP; Example: Abuse the di? erence betwixt the poverty and acme sanctionance. excellenteded max(SAL) - min(SAL) from EMP; Computes the sum of treasures (singly convenient to the rootation husk reckon) Example: Sum of all salaries of employees established in the office 30. chosen sum(SAL) from EMP wnear DEPTNO = 30; Computes mean treastrong for a shaft (singly convenient to the rootation husk reckon) avg, min and max overlook tuples that possess a ineffectual treastrong for the speci? ed mark, but estimate cogitates ineffectual treasures. Foundation De? nition in SQL Creating Tables The SQL instruct for creating an emptiness board has the behindcited fashion: mould board ( [not ineffectual] [unique] , ......... [not ineffectual] [unique] ,  ); For each shaft, a indicate and a rootation husk must be speci? ed and the shaft indicate must be rare delayin the board de? nition. Shaft de? nitions are multiplyd by colons. Tnear is no di? rence betwixt indicates in inferior deed lore and indicates in conspicuous deed lore. In deed, the singly locate wnear conspicuous and inferior deed lore substance are strings similitudes. A not ineffectual 6 bustle is straightway speci? ed behind the rootation husk of the shaft and the bustle insist-upons de? ned mark treasures for that shaft, di? edivulsion from ineffectual. The keyengagement rare speci? es that no two tuples can possess the selfselfselfselfselfresembling mark treastrong for this shaft. Original the situation not ineffectual is to-boot speci? ed for this shaft, the mark treastrong ineffectual is undisputed and two tuples having the mark treastrong ineffectual for this shaft do not abuse the bustle.
Example: The mould board affirmation for our EMP board has the fashion mould board EMP ( EMPNO reckon(4) not ineffectual, ENAME varchar2(30) not ineffectual, JOB varchar2(10), MGR reckon(4), HIREDATE limit, SAL reckon(7,2), DEPTNO reckon(2) ); Remark: Except for the shafts EMPNO and ENAME ineffectual treasures are undisputed. 1. 3. 2 Constraints The de? nition of a board may embody the speci? cation of parity bustles. Basically two husks of bustles are supposing: shaft bustles are associated delay a sole shaft seeing board bustles are typically associated delay further than one shaft.
However, any shaft bustle can to-boot be fashionulated as a board bustle. In this qualification we cogitate singly very gonere bustles. Further tangled bustles deserve be sift-canvassed in Qualification 5. 1. The speci? cation of a (simple) bustle has the behindcited fashion: [bustle ] elementary key | rare | not ineffectual A bustle can be involved. It is beneficial to indicate a bustle in arrange to get further meaningful instruction when this bustle is abused due to, e. g. , an introduction of a tuple that abuses the bustle. If no indicate is speci? ed for the bustle, Oracle effortlessally propagates a indicate of the shape SYS C.
The two most gonere husks of bustles possess already been sift-canvassed: not ineffectual and rare. Probably the most main husk of parity bustles in a rootationdeep are elementary key bustles. A elementary key bustle enables a rare identi? cation of each tuple in a board. Fixed on a elementary key, the rootationdeep plan fixs that no approvenesss pretence in a board. For sample, for our EMP board, the speci? cation mould board EMP ( EMPNO reckon(4) bustle pk emp elementary key, . . . ); 7 de? nes the mark EMPNO as the elementary key for the board. Each treastrong for the mark EMPNO thus must pretence singly unintermittently in the board EMP.
A board, of series, may singly possess one elementary key. Music that in opposition to a rare bustle, ineffectual treasures are not undisputed. Example: We omission to mould a board determined PROJECT to ammunition instruction encircling contrivances. For each contrivance, we omission to ammunition the reckon and the indicate of the contrivance, the employee reckon of the contrivance’s trainr, the budget and the reckon of people established on the contrivance, and the set-out limit and end limit of the contrivance. Furthermore, we possess the behindcited provisions: - a contrivance is identi? ed by its contrivance reckon, - the indicate of a contrivance must be rare, - the trainr and the budget must be de? ed. Board de? nition: mould board PROJECT ( PNO reckon(3) bustle prj pk elementary key, PNAME varchar2(60) rare, PMGR reckon(4) not ineffectual, PERSONS reckon(5), BUDGET reckon(8,2) not ineffectual, PSTART limit, PEND limit); A rare bustle can embody further than one mark. In this deed the shape rare(, . . . , ) is used. If it is insist-upond, for sample, that no two contrivances possess the selfselfselfselfselfresembling set-out and end limit, we possess to add the board bustle bustle no selfselfselfselfselfresembling limits rare(PEND, PSTART) This bustle has to be de? ned in the mould board instruct behind twain shafts PEND and PSTART possess been de? ed. A elementary key bustle that embodys further than singly one shaft can be speci? ed in an twin-fellow way. Instead of a not ineffectual bustle it is sometimes adapted to indicate a omission treastrong for an mark if no treastrong is attached, e. g. , when a tuple is suggested. For this, we use the omission stipulation. Sample : If no set-out limit is attached when suggesting a tuple into the board PROJECT, the contrivance set-out limit should be set to January 1st, 1995: PSTART limit omission(’01-JAN-95’) Note: Unapprove parity bustles, it is not approvely to indicate a indicate for a omission. 8 1. 3. 3 Checkinventory for Creating Tables
The behindcited provides a diminutive curbinventory for the upshots that deficiency to be cogitateed precedently creating a board. • What are the marks of the tuples to be ammunitiond? What are the rootation husks of the marks? Should varchar2 be used instead of char ? • Which shafts uplift the elementary key? • Which shafts do (not) sanction ineffectual treasures? Which shafts do (not) sanction approvenesss ? • Are tnear omission treasures for established shafts that sanction ineffectual treasures ? 1. 4 Foundation Modi? cations in SQL Behind a board has been mouldd using the mould board instruct, tuples can be suggested into the board, or tuples can be deleted or modi? ed. 1. 4. 1
Insertions The most gonere way to suggest a tuple into a board is to use the suggest affirmation suggest into [()] treasures (); For each of the inventoryed shafts, a identical (matching) treastrong must be speci? ed. Thus an introduction does not necessarily possess to thrive the arrange of the marks as speci? ed in the mould board affirmation. If a shaft is omitted, the treastrong ineffectual is suggested instead. If no shaft inventory is attached, at-last, for each shaft as de? ned in the mould board affirmation a treastrong must be attached. Examples: suggest into PROJECT(PNO, PNAME, PERSONS, BUDGET, PSTART) treasures(313, ’DBS’, 4, 150000. 42, ’10-OCT-94’); r suggest into PROJECT treasures(313, ’DBS’, 7411, ineffectual, 150000. 42, ’10-OCT-94’, ineffectual); If tnear are already some rootation in other boards, these rootation can be used for introductions into a new board. For this, we transcribe a question whose outcome is a set of tuples to be suggested. Such an suggest affirmation has the fashion suggest into [()] Example: Suppose we possess de? ned the behindcited board: 9 mould board OLDEMP ( ENO reckon(4) not ineffectual, HDATE limit); We now can use the board EMP to suggest tuples into this new homogeneity: suggest into OLDEMP (ENO, HDATE) excellenteded EMPNO, HIREDATE from EMP wnear HIREDATE < ’31-DEC-60’; 1. 4. 2 Updates
For dissimilateing mark treasures of (some) tuples in a board, we use the uplimit affirmation: uplimit set = , . . . , = [wnear ]; An indication depends of either a penny (new treasure), an arithmetic or string performance, or an SQL question. Music that the new treastrong to asindication to must a the contesting rootation husk. An uplimit affirmation delayout a wnear stipulation outcomes in changing appertaining marks of all tuples in the speci? ed board. Typically, at-last, singly a (small) faction of the board insist-upons an update. Examples: • The employee JONES is transfered to the office 20 as a trainr and his sanctionance is growthd by 1000: plimit EMP set JOB = ’MANAGER’, DEPTNO = 20, SAL = SAL +1000 wnear ENAME = ’JONES’; • All employees established in the offices 10 and 30 get a 15% sanctionance growth. uplimit EMP set SAL = SAL ? 1. 15 wnear DEPTNO in (10,30); Twin-fellow to the suggest affirmation, other boards can be used to recover rootation that are used as new treasures. In such a deed we possess a instead of an . Example: All salesmen established in the office 20 get the selfselfselfselfselfresembling sanctionance as the trainr who has the lowest sanctionance inadequate all trainrs. uplimit EMP set SAL = (separated min(SAL) from EMP wnear JOB = ’MANAGER’) wnear JOB = ’SALESMAN’ and DEPTNO = 20;
Explanation: The question recovers the poverty sanctionance of all trainrs. This treastrong then is consigned to all salesmen established in office 20. 10 It is to-boot approvely to indicate a question that recovers further than singly one treastrong (but stationary singly one tuple! ). In this deed the set stipulation has the fashion set() = . It is main that the arrange of rootation husks and treasures of the excellenteded row indisputably contest to the inventory of shafts in the set stipulation. 1. 4. 3 Deletions All or excellenteded tuples can be deleted from a board using the delete instruct: delete from [wnear ]; If the wnear stipulation is omitted, all tuples are deleted from the board.
An respring instruct for deleting all tuples from a board is the truncate board instruct. However, in this deed, the deletions cannot be destroyne (see posterior Qualification 1. 4. 4). Example: Delete all contrivances (tuples) that possess been ? nished precedently the motiveive limit (plan limit): delete from PROJECT wnear PEND < sysdate; syslimit is a exercise in SQL that income the plan limit. Another main SQL exercise is user, which income the indicate of the user logged into the general Oracle treaty. 1. 4. 4 Intrust and Rollback A succession of rootationdeep modi? cations, i. e. , a succession of suggest, update, and delete affirmations, is determined a action.
Modi? cations of tuples are temporarily ammunitiond in the rootationdeep plan. They beseem steady singly behind the affirmation intrust; has been upshotd. As hanker as the user has not upshotd the intrust affirmation, it is approvely to destroy all modi? cations gone the ultimate intrust. To destroy modi? cations, one has to upshot the affirmation rollback;. It is beneficial to adequate each modi? cation of the rootationdeep delay a intrust (as hanker as the modi? cation has the expected e? ect). Music that any rootation de? nition instruct such as mould board outcomes in an secret intrust. A intrust is to-boot impliedly manufactured when the user terminates an Oracle treaty. . 5 Queries (Part II) In Qualification 1. 2 we possess singly focused on queries that allude to indisputably one board. Furthermore, provisions in a wnear were incarcerateed to gonere similitudes. A superior characteristic of abstruse rootationbases, at-last, is to incorporate (join) tuples ammunitiond in di? edivulsion boards in arrange to spread-out further meaningful and adequate instruction. In SQL the excellenteded affirmation is used for this husk of queries adhereing homogeneitys: 11 excellenteded [distinct] [. ], . . . , [. ] from , . . . ,  [wnear ] The speci? cation of board aliases in the from stipulation is inevimotive to allude to shafts that possess the selfselfselfselfselfresembling indicate in di? divulsion boards. For sample, the shaft DEPTNO betides in twain EMP and DEPT. If we omission to allude to either of these shafts in the wnear or excellenteded stipulation, a board alias has to be speci? ed and put in the face of the shaft indicate. Instead of a board alias to-boot the adequate homogeneity indicate can be put in face of the shaft such as DEPT. DEPTNO, but this sometimes can carry to rather elongationy question fashionulations. 1. 5. 1 Alliance Relations Comparisons in the wnear stipulation are used to incorporate rows from the boards inventoryed in the from stipulation. Sample : In the board EMP singly the quantity of the offices are ammunitiond, not their indicate.
For each pedler, we now omission to recover the indicate as courteous as the reckon and the indicate of the office wnear he is established: excellenteded ENAME, E. DEPTNO, DNAME from EMP E, DEPT D wnear E. DEPTNO = D. DEPTNO and JOB = ’SALESMAN’; Explanation: E and D are board aliases for EMP and DEPT, appertainingly. The calculation of the question outcome betides in the behindcited method (extraneously optimization): 1. Each row from the board EMP is adequately delay each row from the board DEPT (this performance is determined Cartesian emanation ). If EMP comprehends m rows and DEPT comprehends n rows, we thus get n ? m rows. 2.
From these rows those that possess the selfselfselfselfselfresembling office reckon are excellenteded (wnear E. DEPTNO = D. DEPTNO). 3. From this outcome ? nally all rows are excellenteded for which the situation JOB = ’SALESMAN’ holds. In this sample the adhereing situation for the two boards is fixed on the balance operator “=”. The shafts assimilated by this operator are determined adnear shafts and the adnear performance is determined an equijoin. Any reckon of boards can be adequately in a excellenteded affirmation. Example: For each contrivance, recover its indicate, the indicate of its trainr, and the indicate of the office wnear the trainr is established: excellenteded ENAME, DNAME, PNAME rom EMP E, DEPT D, PROJECT P wnear E. EMPNO = P. MGR and D. DEPTNO = E. DEPTNO; 12 It is plain approvely to adnear a board delay itself: Example: Inventory the indicates of all employees contemporaneously delay the indicate of their trainr: excellenteded E1. ENAME, E2. ENAME from EMP E1, EMP E2 wnear E1. MGR = E2. EMPNO; Explanation: The adnear shafts are MGR for the board E1 and EMPNO for the board E2. The equiadnear similitude is E1. MGR = E2. EMPNO. 1. 5. 2 Subqueries Up to now we possess singly concentrated on gonere similitude provisions in a wnear stipulation, i. e. , we possess assimilated a shaft delay a penny or we possess assimilated two shafts.
As we possess already seen for the suggest affirmation, queries can be used for consignments to shafts. A question outcome can to-boot be used in a situation of a wnear stipulation. In such a deed the question is determined a subquestion and the adequate excellenteded affirmation is determined a nested question. A appertaining situation in the wnear stipulation then can possess one of the behindcited fashions: 1. Set-valued subqueries [not] in () [any|all] () An can either be a shaft or a abused treasure. 2. Experiment for (non)being [not] remains () In a wnear stipulation provisions using subqueries can be adequately arbitrarily by using the obstruct connectives and and or.
Example: Inventory the indicate and sanctionance of employees of the office 20 who are carrying a contrivance that set-outed precedently December 31, 1990: excellenteded ENAME, SAL from EMP wnear EMPNO in (separated PMGR from PROJECT wnear PSTART < ’31-DEC-90’) and DEPTNO =20; Explanation: The subquestion recovers the set of those employees who train a contrivance that set-outed precedently December 31, 1990. If the employee established in office 20 is comprehended in this set (in operator), this tuple belongs to the question outcome set. Example: Inventory all employees who are established in a office located in BOSTON: 13 excellenteded ? from EMP wnear DEPTNO in excellenteded DEPTNO from DEPT wnear LOC = ’BOSTON’); The subquestion recovers singly one treastrong (the reckon of the office located in Boston). Thus it is approvely to use “=” instead of in. As hanker as the outcome of a subquestion is not unconcealed in walk, i. e. , whether it is a sole treastrong or a set, it is beneficial to use the in operator. A subquestion may use invariablely a subquestion in its wnear stipulation. Thus provisions can be nested arbitrarily. An main class of subqueries are those that allude to its achieveing (sub)question and the boards inventoryed in the from stipulation, appertainingly. Such husk of queries is determined corcognate subqueries.
Example: Inventory all those employees who are established in the selfselfselfselfselfresembling office as their trainr (music that components in [ ] are libertyal: excellenteded ? from EMP E1 wnear DEPTNO in (separated DEPTNO from EMP [E] wnear [E. ]EMPNO = E1. MGR); Explanation: The subquestion in this sample is cognate to its achieveing question gone it alludes to the shaft E1. MGR. A tuple is excellenteded from the board EMP (E1) for the question outcome if the treastrong for the shaft DEPTNO betides in the set of treasures excellenteded in the subquery. One can judge of the evaluation of this question as thrives: For each tuple in the board E1, the subquestion is evaluated deal-outially.
If the situation wnear DEPTNO in . . . evaluates to penny, this tuple is excellenteded. Music that an alias for the board EMP in the subquestion is not inevimotive gone shafts delayout a conceiveer alias inventoryed tnear invariablely allude to the secretmost question and boards. Provisions of the fashion [any|all] are used to assimilate a attached delay each treastrong excellenteded by . • For the stipulation any, the situation evaluates to penny if tnear remains at smallest on row excellenteded by the subquestion for which the similitude holds. If the subquestion affords an emptiness outcome set, the situation is not satis? ed. For the stipulation all, in opposition, the situation evaluates to penny if for all rows excellenteded by the subquestion the similitude holds. In this deed the situation evaluates to penny if the subquestion does not afford any row or treasure. Example: Recover all employees who are established in office 10 and who deserve at smallest as greatly as any (i. e. , at smallest one) employee established in office 30: excellenteded ? from EMP wnear SAL >= any (separated SAL from EMP wnear DEPTNO = 30) and DEPTNO = 10; 14 Note: To-boot in this subquestion no aliases are inevimotive gone the shafts allude to the secretmost from stipulation.
Example: Inventory all employees who are not established in office 30 and who deserve further than all employees established in office 30: excellenteded ? from EMP wnear SAL > all (separated SAL from EMP wnear DEPTNO = 30) and DEPTNO 30; For all and any, the behindcited equivalences hold: in ? = any not in ? all or ! = all Repeatedly a question outcome depends on whether established rows do (not) remain in (other) boards. Such husk of queries is fashionulated using the remains operator. Example: Inventory all offices that possess no employees: excellenteded ? from DEPT wnear not remains (separated ? from EMP wnear DEPTNO = DEPT.
DEPTNO); Explanation: For each tuple from the board DEPT, the situation is curbed whether tnear remains a tuple in the board EMP that has the selfselfselfselfselfresembling office reckon (DEPT. DEPTNO). In deed no such tuple remains, the situation is satis? ed for the tuple beneath cogitateation and it is excellenteded. If tnear remains a identical tuple in the board EMP, the tuple is not excellenteded. 1. 5. 3 Operations on Outcome Sets Sometimes it is adapted to incorporate question outcomes from two or further queries into a sole outcome. SQL supports three set operators which possess the shape: The set operators are: connection [all] income a board depending of all rows either pretenceing in the outcome of or in the outcome of . Duplicates are effortlessally eliminated original the stipulation all is used. • blend income all rows that pretence in twain outcomes and . • minus income those rows that pretence in the outcome of but not in the outcome of . 15 Example: Assume that we possess a board EMP2 that has the selfselfselfselfselfresembling uplifting and shafts as the board EMP: • All employee quantity and indicates from twain boards: excellenteded EMPNO, ENAME from EMP connection excellenteded EMPNO, ENAME from EMP2; • Employees who are inventoryed in twain EMP and EMP2: excellenteded ? from EMP blend excellenteded ? from EMP2; Employees who are singly inventoryed in EMP: excellenteded ? from EMP minus excellenteded ? from EMP2; Each operator insist-upons that twain boards possess the selfselfselfselfselfresembling rootation husks for the shafts to which the operator is applied. 1. 5. 4 Grouping In Qualification 1. 2. 4 we possess seen how bearing exercises can be used to abuse a sole treastrong for a shaft. Repeatedly contacts insist-upon assemblageing rows that possess established properties and then applying an bearing exercise on one shaft for each assemblage deal-outially. For this, SQL provides the stipulation assemblage by . This stipulation pretences behind the wnear stipulation and must allude to shafts of boards inventoryed in the from stipulation. excellenteded from near assemblage by [having ]; Those rows recoverd by the excellenteded stipulation that possess the selfselfselfselfselfresembling treasure(s) for are assemblageed. Aggregations speci? ed in the excellenteded stipulation are then applied to each assemblage deal-outially. It is main that singly those shafts that pretence in the stipulation can be inventoryed delayout an bearing exercise in the excellenteded stipulation ! Example: For each office, we omission to recover the poverty and acme sanctionance. excellenteded DEPTNO, min(SAL), max(SAL) from EMP assemblage by DEPTNO; Rows from the board EMP are assemblageed such that all rows in a assemblage possess the selfselfselfselfselfresembling office reckon. The bearing exercises are then applied to each such assemblage.
We thus get the behindcited question outcome: 16 DEPTNO 10 20 30 MIN(SAL) 1300 800 950 MAX(SAL) 5000 3000 2850 Rows to fashion a assemblage can be incarcerateed in the wnear stipulation. For sample, if we add the situation wnear JOB = ’CLERK’, singly appertaining rows uplift a assemblage. The question then would recover the poverty and acme sanctionance of all clerks for each office. Music that is not undisputed to indicate any other shaft than DEPTNO delayout an bearing exercise in the excellenteded stipulation gone this is the singly shaft inventoryed in the assemblage by stipulation (is it to-boot unconcerned to see that other shafts would not perarrange any reason).
Once assemblages possess been fashioned, established assemblages can be eliminated fixed on their properties, e. g. , if a assemblage comprehends hither than three rows. This husk of situation is speci? ed using the having stipulation. As for the excellenteded stipulation to-boot in a having stipulation singly and aggregations can be used. Example: Recover the poverty and acme sanctionance of clerks for each office having further than three clerks. excellenteded DEPTNO, min(SAL), max(SAL) from EMP wnear JOB = ’CLERK’ assemblage by DEPTNO having estimate(? ) > 3; Music that it is plain approvely to indicate a subquestion in a having stipulation.
In the over question, for sample, instead of the penny 3, a subquestion can be speci? ed. A question comprehending a assemblage by stipulation is modeed in the behindcited way: 1. Separated all rows that content the situation speci? ed in the wnear stipulation. 2. From these rows fashion assemblages according to the assemblage by stipulation. 3. Discard all assemblages that do not content the situation in the having stipulation. 4. Apply bearing exercises to each assemblage. 5. Recover treasures for the shafts and aggregations inventoryed in the excellenteded stipulation. 1. 5. 5 Some Comments on Tables Accessing boards of other users
Provided that a user has the advantage to admission boards of other users (see to-boot Qualification 3), she/he can allude to these boards in her/his queries. Let be a user in the Oracle plan and a board of this user. This board can be admissioned by other (privileged) users using the instruct excellenteded ? from . ; 17 In deed that one repeatedly alludes to boards of other users, it is adapted to use a equivalent instead of .. In Oracle-SQL a equivalent can be mouldd using the instruct mould equivalent for . ; It is then approvely to use merely in a from stipulation. Synonyms can to-boot be mouldd for one’s own boards. Adding Comments to De? nitions
For contacts that embody condensed boards, it is adapted to add expatiates on board de? nitions or to add expatiates on shafts. A expatiate on a board can be mouldd using the instruct expatiate on board is ’’; A expatiate on a shaft can be mouldd using the instruct expatiate on shaft . is ’’; Comments on boards and shafts are ammunitiond in the rootation vocabulary. They can be admissioned using the rootation vocabulary ends USER TAB COMMENTS and USER COL COMMENTS (see to-boot Qualification 3). Modifying Table- and Shaft De? nitions It is approvely to dissimilate the uplifting of a board (the homogeneity schema) plain if rows possess already been suggested into this board.
A shaft can be acquired using the transmute board instruct transmute board add( [omission ] ); If further than singly one shaft should be acquired at one span, appertaining add stipulations deficiency to be multiplyd by colons. A board bustle can be acquired to a board using transmute board add (); Music that a shaft bustle is a board bustle, too. not ineffectual and elementary key bustles can singly be acquired to a board if none of the speci? ed shafts comprehends a ineffectual treasure. Board de? nitions can be modi? ed in an twin-fellow way. This is adapted, e. g. , when the dimension of strings that can be ammunitiond deficiencys to be growthd.
The syntax of the instruct for dissimilateing a shaft is transmute board dissimilate(  [omission ] ); Note: In precedent renderings of Oracle it is not approvely to delete sole shafts from a board de? nition. A workaround is to mould a present board and to dramatizeation appertaining shafts and rows into this new board. Furthermore, it is not approvely to reindicate boards or shafts. In the most new rendering (9i), using the transmute board instruct, it is approvely to reindicate a board, shafts, and bustles. In this rendering, tnear to-boot remains a emanate shaft stipulation as segregate of the transmute board affirmation. Deleting a Table
A board and its rows can be deleted by issuing the instruct emanate board [cascade bustles];. 18 1. 6 Views In Oracle the SQL instruct to mould a end (substantial board) has the fashion mould [or rearrange] end [()] as [delay curb liberty [bustle ]]; The libertyal stipulation or rearrange re-creates the end if it already remains. indicates the shafts of the end. If is not speci? ed in the end de? nition, the shafts of the end get the selfselfselfselfselfresembling indicates as the marks inventoryed in the excellenteded affirmation (if approvely). Example: The behindcited end comprehends the indicate, job epithet and the annual sanctionance of employees established in the office 20: mould end DEPT20 as chosen ENAME, JOB, SAL? 12 ANNUAL SALARY from EMP wnear DEPTNO = 20; In the excellenteded affirmation the shaft alias ANNUAL SALARY is speci? ed for the indication SAL? 12 and this alias is charmed by the end. An respring fashionulation of the over end de? nition is mould end DEPT20 (ENAME, JOB, ANNUAL SALARY) as excellenteded ENAME, JOB, SAL ? 12 from EMP wnear DEPTNO = 20; A end can be used in the selfselfselfselfselfresembling way as a board, that is, rows can be recoverd from a end (to-boot appertaining rows are not physically ammunitiond, but segregateial on rootation of the excellenteded affirmation in the end de? ition), or rows can plain be modi? ed. A end is evaluated invariablely each span it is admissioned. In Oracle SQL no suggest, update, or delete modi? cations on ends are undisputed that use one of the behindcited frames in the end de? nition: • Joins • Bearing exercise such as sum, min, max etc. • set-valued subqueries (in, any, all) or experiment for remainence (exists) • assemblage by stipulation or refractory stipulation In alliance delay the stipulation delay curb liberty any uplimit or introduction of a row into the end is uncommon if the new/modi? ed row does not conface the end de? nition, i. e. these rows would not be excellenteded fixed on the excellenteded affirmation. A delay curb liberty can be involved using the bustle stipulation. A end can be deleted using the instruct delete . 19 2 SQL*Plus Superfluous SQL*Plus is the interactive (low-level) user interface to the Oracle rootationdeep trainment plan. Typically, SQL*Plus is used to upshot ad-hoc queries and to end the question outcome on the shelter. Some of the characteristics of SQL*Plus are: • A built-in instruct cord editor can be used to edit (incorrect) SQL queries. Instead of this cord editor any editor inveterate on the abuser can be imprecated. Tnear are condensed instructs to fashionat the output of a question. • SQL*Plus provides an online-help. • Question outcomes can be ammunitiond in ? les which then can be printed. Queries that are invariablely upshotd can be secured to a ? le and imprecated succeeding. Queries can be parameterized such that it is approvely to imprecate a secured question delay a parameter. A Minimal User Guide Precedently you set-out SQL*Plus perarrange strong that the behindcited UNIX shell capriciouss are truly set (shell capriciouss can be curbed using the env instruct, e. g. , env | grep ORACLE): • ORACLE HOME, e. g. , ORACLE HOME=/usr/pkg/oracle/734 • ORACLE SID, e. , ORACLE SID=prod In arrange to imprecate SQL*Plus from a UNIX shell, the instruct sqlplus has to be upshotd. SQL*Plus then spread-outs some instruction encircling the emanation, and actives you for your user indicate and passengagement for the Oracle plan. gertz(catbert)54: sqlplus SQL*Plus: Release 3. 3. 4. 0. 1 - Production on Sun Dec 20 19:16:52 1998 Copystraight (c) Oracle Corporation 1979, 1996. All straights silent. Enter user-name: scott Enter password: Conjoined to: Oracle7 Server Release 7. 3. 4. 0. 1 - Production Release Delay the nice liberty PL/SQL Release 2. 3. 4. 0. 0 - Production SQL> 20
SQL> is the active you get when you are conjoined to the Oracle rootationdeep plan. In SQL*Plus you can multiply a affirmation into severed cords, each continuing cord is involved by a active such 2>, 3> etc. An SQL affirmation must invariablely be terminated by a semicolon (;). In abstracted to the SQL affirmations sift-canvassed in the foregoing qualification, SQL*Plus provides some eeexceptional SQL*Plus instructs. These instructs deficiency not be terminated by a semicolon. Conspicuous and inferior deed lore are singly main for string similitudes. An SQL question can invariablely be intermittent by using C. To egress SQL*Plus you can either husk egress or resign.
Editor Commands The most newly upshotd SQL affirmation is ammunitiond in the SQL bu? er, refractory of whether the affirmation has a rectify syntax or not. You can edit the bu? er using the behindcited instructs: • l[ist] inventorys all cords in the SQL bu? er and sets the general cord (remarkable delay an ”? ”) to the ultimate cord in the bu? er. • l sets the motiveive cord to • c[hange]// rearranges the ? rst affair of by (for the motiveive cord) • a[ppend] appends to the general cord • del deletes the general cord • r[un] enacts the general bu? er variation • get peruses the rootation from the ? le into the bu? er • secure transcribes the general bu? r into the ? le • edit imprecates an editor and loads the general bu? er into the editor. Behind egressing the editor the modi? ed SQL affirmation is ammunitiond in the bu? er and can be manufactured (instruct r). The editor can be de? ned in the SQL*Plus shell by typing the instruct de? ne editor = , wnear can be any editor such as emacs, vi, joe, or jove. SQL*Plus Acceleration Plan and Other Adapted Commands • To get the oncord acceleration in SQL*Plus fair husk acceleration , or fair acceleration to get instruction encircling how to use the acceleration instruct. In Oracle Rendering 7 one can get the adequate inventory of approvely instructs by typing acceleration instruct. To transmute the password, in Oracle Rendering 7 the instruct transmute user identi? ed by ; is used. In Oracle Rendering 8 the instruct passw actives the user for the old/new password. • The instruct desc[ribe] inventorys all shafts of the attached board contemporaneously delay their rootation husks and instruction encircling whether ineffectual treasures are undisputed or not. • You can imprecate a UNIX instruct from the SQL*Plus shell by using enumerate . For sample, enumerate ls -la *. sql inventorys all SQL ? les in the general directory. 21 • You can log your SQL*Plus treaty and thus queries and question outcomes by using the instruct spool .
All instruction spread-outed on shelter is then ammunitiond in which effortlessally gets the emanationion . lst. The instruct spool o? turns spooling o?. • The instruct dramatizeation can be used to dramatizeation a adequate board. For sample, the instruct dramatizeation from scott/tiger mould EMPL using excellenteded ? from EMP; copies the board EMP of the user scott delay passengagement tiger into the homogeneity EMPL. The homogeneity EMP is effortlessally mouldd and its uplifting is segregateial fixed on the marks inventoryed in the excellenteded stipulation. • SQL instructs secured in a ? le . sql can be loaded into SQL*Plus and manufactured using the instruct @. Comments are introduced by the stipulation rem[ark] (singly undisputed betwixt SQL affirmations), or - - (undisputed delayin SQL affirmations). Formatting the Output SQL*Plus provides condensed instructs to fashionat question outcomes and to uplift gonere reports. For this, fashionat capriciouss are set and these elucidations are singly efficient during the SQL*Plus treaty. They get obsolete behind terminating SQL*Plus. It is, at-last, approvely to secure elucidations in a ? le involved login. sql in your settlement directory. Each span you imprecate SQL*Plus this ? le is effortlessally loaded. The instruct shaft . . . is used to fashionat shafts of your question outcome.
The most invariablely used libertys are: • fashionat A For alphanumeric rootation, this liberty sets the elongation of to . For shafts having the rootation husk reckon, the fashionat instruct can be used to indicate the fashionat precedently and behind the decimal top. For sample, fashionat 99,999. 99 speci? es that if a treastrong has further than three digits in face of the decimal top, digits are multiplyd by a colon, and singly two digits are spread-outed behind the decimal top. • The liberty address relabels and gives it a new address. • ineffectual is used to indicate the output of ineffectual treasures (typically, ineffectual treasures are not spread-outed). shaft absolved deletes the fashionat de? nitions for . The instruct set corddimension can be used to set the acme elongation of a sole cord that can be spread-outed on shelter. set pagedimension sets the whole reckon of cords SQL*Plus spread-outs precedently printing the shaft indicates and addresss, appertainingly, of the excellenteded rows. Diverse other fashionatting characteristics can be enabled by elucidation SQL*Plus capriciouss. The instruct pretence all spread-outs all capriciouss and their general treasures. To set a capricious, husk set . For sample, set timing on causes SQL*Plus to spread-out timing statistics for each SQL instruct that is manufactured. et intermission on  performs SQL*Plus endure for you to imimpress Give-back behind the reckon of cords de? ned by set pagedimension has been spread-outed. is the communication SQL*Plus deserve spread-out at the depth of the shelter as it endures for you to hit Return. 22 3 Oracle Foundation Vocabulary The Oracle rootation vocabulary is one of the most main components of the Oracle DBMS. It comprehends all instruction encircling the upliftings and motives of the rootationdeep such as boards, shafts, users, rootation ? les etc. The rootation ammunitiond in the rootation vocabulary are to-boot repeatedly determined metadata.
Although it is usually the estate of rootationdeep administrators (DBAs), the rootation vocabulary is a precious spring of instruction for end users and educeers. The rootation vocabulary depends of two flattens: the secret flatten comprehends all deep boards that are used by the different DBMS software components and they are recognizedly not admissionible by end users. The visible flatten provides condensed ends on these deep boards to admission instruction encircling motives and upliftings at di? edivulsion flattens of purpose. 3. 1 Foundation Vocabulary Tables An installation of an Oracle rootationdeep invariablely embodys the figment of three measure Oracle users: SYS: This is the proprietor of all rootation vocabulary boards and ends. This user has the first advantages to train motives and upliftings of an Oracle rootationdeep such as creating new users. • SYSTEM: is the proprietor of boards used by di? edivulsion tools such SQL*Forms, SQL*Reports etc. This user has hither advantages than SYS. • PUBLIC: This is a “dummy” user in an Oracle rootationbase. All advantages consigned to this user are effortlessally consigned to all users unconcealed in the rootationbase. The boards and ends supposing by the rootation vocabulary comprehend instruction encircling • users and their advantages, boards, board shafts and their rootation husks, parity bustles, indexes, • statistics encircling boards and indexes used by the optimizer, • advantages granted on rootationdeep motives, • storage upliftings of the rootationbase. The SQL instruct excellenteded ? from DICT[IONARY]; inventorys all boards and ends of the rootation vocabulary that are admissionible to the user. The excellenteded instruction embodys the indicate and a abrupt designate of each board and end. Precedently issuing this question, curb the shaft de? nitions of DICT[IONARY] using desc DICT[IONARY] and set the alienate treasures for shaft using the fashionat instruct. The question excellenteded ? from TAB; etrieves the indicates of all boards owned by the user who upshots this instruct. The question excellenteded ? from COL; 23 income all instruction encircling the shafts of one’s own boards. Each SQL question insist-upons different secret admissiones to the boards and ends of the rootation vocabulary. Gone the rootation vocabulary itself depends of boards, Oracle has to propagate condensed SQL affirmations to curb whether the SQL instruct upshotd by a user is rectify and can be manufactured. Example: The SQL question excellenteded ? from EMP wnear SAL > 2000; insist-upons a veri? cation whether (1) the board EMP remains, (2) the user has the advantage to admission this board, (3) the shaft SAL is de? ed for this board etc. 3. 2 Foundation Vocabulary Views The visible flatten of the rootation vocabulary provides users a face end to admission instruction alienate to the users. This flatten provides condensed ends (in Oracle7 obstructly 540) that dramatize (a faction of the) rootation from the deep boards in a peruseable and beneathstandable method. These ends can be used in SQL queries fair approve recognized boards. The ends supposing by the rootation vocabulary are multiplyd into three assemblages: USER, ALL, and DBA. The assemblage indicate uplifts the pre? x for each end indicate. For some ends, tnear are associated equivalents as attached in brackets under. USER : Tuples in the USER ends comprehend instruction encircling motives owned by the acestimate performing the SQL question (general user) USER TABLES USER USER USER USER USER USER CATALOG COL COMMENTS CONSTRAINTS INDEXES OBJECTS TAB COLUMNS USER USER USER USER TAB COMMENTS TRIGGERS USERS VIEWS all boards delay their indicate, reckon of shafts, storage instruction, statistical instruction etc. (TABS) boards, ends, and equivalents (CAT) expatiates on shafts bustle de? nitions for boards all instruction encircling indexes mouldd for boards (IND) all rootationdeep motives owned by the user (OBJ) shafts of the boards and ends owned by the user COLS) expatiates on boards and ends triggers de? ned by the user instruction encircling the general user ends de? ned by the user • ALL : Rows in the ALL ends embody rows of the USER ends and all instruction encircling motives that are admissionible to the general user. The uplifting of these ends is twin-fellow to the uplifting of the USER ends. 24 ALL CATALOG proprietor, indicate and husk of all admissionible boards, ends, and equivalents ALL TABLES proprietor and indicate of all admissionible boards ALL OBJECTS proprietor, husk, and indicate of admissionible rootationdeep motives ALL TRIGGERS . . . ALL USERS ... ALL VIEWS ... DBA : The DBA ends surround instruction encircling all rootationdeep motives, regardhither of the proprietor. Singly users delay DBA advantages can admission these ends. DBA DBA DBA DBA DBA TABLES CATALOG OBJECTS DATA FILES USERS boards of all users in the rootationdeep boards, ends, and equivalents de? ned in the rootationdeep motive of all users instruction encircling rootation ? les instruction encircling all users unconcealed in the rootationdeep 25 4 Contact Programming 4. 1 4. 1. 1 PL/SQL Superfluous The fruit of rootationdeep contacts typically insist-upons speech frames resembling to those that can be fix in programming speechs such as C, C++, or Pascal.
These frames are inevimotive in arrange to utensil tangled rootation upliftings and algorithms. A superior incarcerateion of the rootationdeep speech SQL, at-last, is that condensed tasks cannot be polished by using singly the supposing speech elements. PL/SQL (Procedural Language/SQL) is a procedural emanationion of Oracle-SQL that o? ers speech frames resembling to those in compulsory programming speechs. PL/SQL sanctions users and designers to educe tangled rootationdeep contacts that insist-upon the action of moderate upliftings and procedural elements such as progresss, exercises, and modules. The basic frame in PL/SQL is a arrest.
Blocks sanction designers to incorporate obstructly cognate (SQL-) affirmations into deal-outs. In a arrest, pennys and capriciouss can be visible, and capriciouss can be used to ammunition question outcomes. Statements in a PL/SQL arrest embody SQL affirmations, moderate upliftings (loops), situation affirmations (if-then-else), qualification handling, and calls of other PL/SQL arrests. PL/SQL arrests that indicate progresss and exercises can be assemblageed into loads. A load is resembling to a module and has an interface and an utensilation segregate. Oracle o? ers diverse prede? ned loads, for sample, input/output routines, ? le handling, job scheduling etc. see directory $ORACLE HOME/rdbms/admin). Another main characteristic of PL/SQL is that it o? ers a mechanism to mode question outcomes in a tuple-oriented way, that is, one tuple at a span. For this, cursors are used. A cursor basically is a toper to a question outcome and is used to peruse mark treasures of excellenteded tuples into capriciouss. A cursor typically is used in alliance delay a loop frame such that each tuple peruse by the cursor can be modeed deal-outially. In resume, the superior goals of PL/SQL are to • growth the expressiveness of SQL, • mode question outcomes in a tuple-oriented way, • optimize adequately SQL affirmations, educe modular rootationdeep contact programs, • reuse program rule, and • subject the require for maintaining and changing contacts. 26 4. 1. 2 Building of PL/SQL-Blocks PL/SQL is a arrest-structured speech. Each arrest uplifts a (named) program deal-out, and arrests can be nested. Blocks that uplift a progress, a exercise, or a load must be involved. A PL/SQL arrest has an libertyal approve qualification, a segregate comprehending PL/SQL affirmations, and an libertyal qualification-handling segregate. Thus the uplifting of a PL/SQL looks as thrives (brackets [ ] environ libertyal segregates):  [approve ] initiate [qualification ] end; The arrest header speci? s whether the PL/SQL arrest is a progress, a exercise, or a load. If no header is speci? ed, the arrest is said to be an unattested PL/SQL arrest. Each PL/SQL arrest invariablely uplifts a PL/SQL affirmation. Thus arrests can be nested approve arrests in ordinary programming speechs. The occasion of visible capriciouss (i. e. , the segregate of the program in which one can allude to the capricious) is twin-fellow to the occasion of capriciouss in programming speechs such as C or Pascal. 4. 1. 3 Declarations Constants, capriciouss, cursors, and qualifications used in a PL/SQL arrest must be visible in the approve qualification of that arrest.
Variables and pennys can be visible as thrives: [constant] [not ineffectual] [:= ]; Efficient rootation husks are SQL rootation husks (see Qualification 1. 1) and the rootation husk boolean. Boolean rootation may singly be penny, spurious, or ineffectual. The not ineffectual stipulation insist-upons that the visible capricious must invariablely possess a treastrong di? edivulsion from ineffectual. is used to judicioudimension a capricious. If no indication is speci? ed, the treastrong ineffectual is consigned to the capricious. The stipulation penny states that unintermittently a treastrong has been consigned to the capricious, the treastrong cannot be transmuted (thus the capricious beseems a penny). Example: approve rent limit job epithet mp fix sanctionance incr ... initiate . . . end; limit; /* implied judiciousization delay ineffectual */ varchar2(80) := ’Salesman’; boolean; /* implied judiciousization delay ineffectual */ penny reckon(3,2) := 1. 5; /* penny */ 27 Instead of indicateing a rootation husk, one can to-boot allude to the rootation husk of a board shaft (so-determined anchored affirmation). For sample, EMP. Empno%TYPE alludes to the rootation husk of the shaft Empno in the homogeneity EMP. Instead of a sole capricious, a archives can be visible that can ammunition a adequate tuple from a attached board (or question outcome). For sample, the rootation husk DEPT%ROWTYPE speci? s a archives suiboard to ammunition all mark treasures of a adequate row from the board DEPT. Such archivess are typically used in alliance delay a cursor. A ? eld in a archives can be admissioned using . , for sample, DEPT. Deptno. A cursor affirmation speci? es a set of tuples (as a question outcome) such that the tuples can be modeed in a tuple-oriented way (i. e. , one tuple at a span) using the procure affirmation. A cursor affirmation has the fashion cursor [()] is ; The cursor indicate is an unvisible identi? er, not the indicate of any PL/SQL capricious. A parameter has the fashion . Likely parameter husks are char, archar2, reckon, limit and boolean as courteous as identical subtypes such as integer. Parameters are used to asindication treasures to the capriciouss that are attached in the excellenteded affirmation. Example: We omission to recover the behindcited mark treasures from the board EMP in a tupleoriented way: the job epithet and indicate of those employees who possess been remunerated behind a attached limit, and who possess a trainr established in a attached office. cursor employee cur (set-out limit limit, dno reckon) is excellenteded JOB, ENAME from EMP E wnear HIREDATE > set-out limit and remains (separated ? from EMP wnear E. MGR = EMPNO and DEPTNO = dno);
If (some) tuples excellenteded by the cursor deserve be modi? ed in the PL/SQL arrest, the stipulation for update[()] has to be acquired at the end of the cursor affirmation. In this deed excellenteded tuples are locked and cannot be admissioned by other users until a intrust has been upshotd. Precedently a visible cursor can be used in PL/SQL affirmations, the cursor must be publiced, and behind modeing the excellenteded tuples the cursor must be determined. We sift-canvass the action of cursors in further purpose under. Litigious are used to mode deceptions and warnings that betide during the action of PL/SQL affirmations in a moderateled method.
Some qualifications are secretly de? ned, such as ZERO DIVIDE. Other qualifications can be speci? ed by the user at the end of a PL/SQL arrest. User de? ned qualifications deficiency to be visible using qualification. We deserve sift-canvass qualification handling in further purpose in Qualification 4. 1. 5 4. 1. 4 Speech Elements In abstracted to the affirmation of capriciouss, pennys, and cursors, PL/SQL o? ers different speech frames such as capricious consignments, moderate upliftings (loops, if-then-else), progress and exercise calls, etc. However, PL/SQL does not sanction instructs of the SQL rootation de? nition speech such as the mould board affirmation.
For this, PL/SQL provides eeexceptional loads. 28 Furthermore, PL/SQL uses a modi? ed excellenteded affirmation that insist-upons each excellenteded tuple to be consigned to a archives (or a inventory of capriciouss). Tnear are diverse resources in PL/SQL to a asindication a treastrong to a capricious. The most gonere way to asindication a treastrong to a capricious is approve estimateer integer := 0; ... initiate estimateer := estimateer + 1; Values to asindication to a capricious can to-boot be recoverd from the rootationdeep using a excellenteded affirmation excellenteded into from wnear ; It is main to enstrong that the excellenteded affirmation recovers at most one tuple !
Otherwise it is not approvely to asindication the mark treasures to the speci? ed inventory of capriciouss and a runspan deception betides. If the excellenteded affirmation recovers further than one tuple, a cursor must be used instead. Furthermore, the rootation husks of the speci? ed capriciouss must contest those of the recoverd mark treasures. For most rootation husks, PL/SQL performs an effortless husk conrendering (e. g. , from integer to motiveive). Instead of a inventory of sole capriciouss, a archives can be attached behind the keyengagement into. To-boot in this deed, the excellenteded affirmation must recover at most one tuple ! approve mployee rec EMP%ROWTYPE; max sal EMP. SAL%TYPE; initiate excellenteded EMPNO, ENAME, JOB, MGR, SAL, COMM, HIREDATE, DEPTNO into employee rec from EMP wnear EMPNO = 5698; excellenteded max(SAL) into max sal from EMP; ... end; PL/SQL provides conjuncture-loops, two husks of for-loops, and rectilineal loops. Latter ones are used in alliance delay cursors. All husks of loops are used to enact a succession of affirmations multiple spans. The speci? cation of loops betides in the selfselfselfselfselfresembling way as unconcealed from compulsory programming speechs such as C or Pascal. A conjuncture-loop has the shape [>] conjuncture loop ; end loop  ; 9 A loop can be involved. Naming a loop is adapted whenever loops are nested and secret loops are adequated positively using the egress ; affirmation. Seeing the reckon of successions through a conjuncture loop is unrecognized until the loop adequates, the reckon of successions through the for loop can be speci? ed using two integers. [>] for in [reverse] .. loop end loop  ; The loop estimateer is visible impliedly. The occasion of the loop estimateer is singly the for loop. It overrides the occasion of any capricious having the selfselfselfselfselfresembling indicate beyond the loop. Inside the for loop, can be alludeenced approve a penny. ay pretence in indications, but one cannot asindication a treastrong to . Using the keyengagement evolution causes the succession to receipts beneath from the excellent spring to the inferior spring. Processing Cursors: Precedently a cursor can be used, it must be publiced using the public affirmation public [()] ; The associated excellenteded affirmation then is modeed and the cursor alludeences the ? rst excellenteded tuple. Separated tuples then can be modeed one tuple at a span using the procure instruct procure into ; The procure instruct consigns the excellenteded mark treasures of the general tuple to the inventory of capriciouss.
After the procure instruct, the cursor walks to the contiguous tuple in the outcome set. Music that the capriciouss in the inventory must possess the selfselfselfselfselfresembling rootation husks as the excellenteded treasures. Behind all tuples possess been modeed, the obstruct instruct is used to disentitle the cursor. obstruct ; The sample under illustrates how a cursor is used contemporaneously delay a rectilineal loop: approve cursor emp cur is excellenteded ? from EMP; emp rec EMP%ROWTYPE; emp sal EMP. SAL%TYPE; initiate public emp cur; loop procure emp cur into emp rec; egress when emp cur%NOTFOUND; emp sal := emp rec. sal; end loop; obstruct emp cur; ... end; 30
Each loop can be adequated positively using the egress stipulation: egress  [when ] Using egress delayout a arrest designate causes the tenor of the loop that comprehends the egress affirmation. A situation can be a gonere similitude of treasures. In most deeds, at-last, the situation alludes to a cursor. In the sample over, %NOTFOUND is a affirm that evaluates to spurious if the most new procure instruct has peruse a tuple. The treastrong of %NOTFOUND is ineffectual precedently the ? rst tuple is procureed. The affirm evaluates to penny if the most new procure failed to give-back a tuple, and spurious inadequately. FOUND is the obstruct opposituation of %NOTFOUND. Cursor for loops can be used to facilitate the action of a cursor: [>] for in [()] loop end loop ; A archives suiboard to ammunition a tuple procureed by the cursor is impliedly visible. Furthermore, this loop impliedly performs a procure at each succession as courteous as an public precedently the loop is entered and a obstruct behind the loop is left. If at an succession no tuple has been procureed, the loop is effortlessally terminated delayout an egress. It is plain approvely to indicate a question instead of in a for loop: for in () loop end loop; That is, a cursor deficiencys not be speci? d precedently the loop is entered, but is de? ned in the excellenteded affirmation. Example: for sal rec in (separated SAL + COMM whole from EMP) loop ... ; end loop; whole is an alias for the indication abused in the excellenteded affirmation. Thus, at each succession singly one tuple is procureed. The archives sal rec, which is impliedly de? ned, then comprehends singly one register which can be admissioned using sal rec. whole. Aliases, of series, are not inevimotive if singly marks are excellenteded, that is, if the excellenteded affirmation comprehends no arithmetic operators or bearing exercises. For situational moderate, PL/SQL o? rs if-then-else