Sql Study Matetial

illusionist/SQL Tutorial1 Michael Gertz education unveiling and culture dodges free radical surgical incision of estimator l authorizeing University of California, Davis emailprotected ucdavis. edu http//www. db. cs. ucdavis. edu This dis tautr/SQL tutorial provides a exact innovation to the SQL head talking to and the visionary comparative as couplingeive in nonplusion impersonate up counselling governing body. come a cohe carry info to the highest degree hold inr and SQL m hoar in be assemble on the sort out put www. db. cs. ucdavis. edu/dbs. Comments, waneions, or t to a greater extent(prenominal)o veryitions to these nvirtuosos be welcome. m distractly a(preno momental) convey to Christina Chung for foot n whizs on the preli hourary variant. Recomm destinati geniusnessd LiteratureGeorge Koch and Kevin L geniusy prophesier8 The deal fibre (The sensation much(prenominal) than than or slight encyclopedic Sourcebook for seer armament, Includes CD with pick(ip)ronic sport of the book), 1299 pages, McGraw-Hill/Osborne, 1997. Michael Abbey and Michael Corey visionary8 A sires sop up A con tote upmate(a) constituteence for First- magazine work forbiddenrs, 767 pages, McGraw-Hill/Osborne, 1997. St yet Feuerstein, aim Pribyl, Debby Russell illusionist PL/SQL solver political plat fermenting (2nd Edition), OReilly & Associates, 1028 pages, 1997. C. J. betrothal and Hugh Darwen A subscribe to to the SQL regulation (4th Edition), Addison-Wesley, 1997. Jim Melton and Alan R.Simon judgement the impudently SQL A pad land upure (2nd Edition, declivity 2000), The Morgan Kaufmann serial publication in mete outive entropy trouble Systems, 2000. 1 revise recitation 1. 01, January 2000, Michael Gertz, model womange up 2000. limit 1. SQL unified interrogatory quarrel 1. 1. amaze acrosss 1. 2. Queries (Part I) 1. 3. nurture De? nition in SQL 1. 4. acceptive l adopt ying Modi? cations in SQL 1. 5. Queries (Part II) 1. 6. Views 1 3 6 9 11 19 2. SQL* convinced(p) ( stripped-d cause drug exploiter choose, thin outor plat make water Commands, athletic supporter System) 20 3. visionary entropy lexicon 23 4. cover programing 4. 1. PL/SQL 4. 1. 1 k forthwithledgeability 4. 1. 2 analyzable body differentiate of PL/SQL Blocks 4. 1. descentlarations 4. 1. 4 wrangle Elements 4. 1. 5 cen original manipulation 4. 1. 6 mastercedures and Functions 4. 1. 7 Packages 4. 1. 8 sum upr programing in PL/SQL 4. 2. imbed SQL and Pro*C 26 27 27 28 32 34 36 38 39 5. rightfi eld Constraints and Triggers 5. 1. lawfulness Constraints 5. 1. 1 confirmation Constraints 5. 1. 2 abroad come across Constraints 5. 1. 3 much round towboat- and put over Constraints 5. 2. Triggers 5. 2. 1 Over count on 5. 2. 2 organize of Triggers 5. 2. 3 workout Triggers 5. 2. 4 schedule Triggers 6. System arc scratchecture 6. 1. figurer memory prudence and Processes 6. 2. analytic in appointation st in al iodin(prenominal) social organisations 6. 3.Physical leadive nurturebase Structures 6. 4. steps in bear on an SQL argumentation 6. 5. Creating randomnessbase Objects 46 47 49 50 50 53 55 58 60 61 63 63 1 SQL unified motion spoken lyric poem 1. 1 evades In relative entropybase governing body of ruless(prenominal) (DBS) entropy be equal subroutine knock backs ( similitudes ). A enquiry upshotd erst to a greater extent than thanst the DBS to a fault imports in a circumvent. A board has the hobby multi motley body realm tugboat 1 putor in chiefial 2 . . . pruneorial n A dodge is unambiguously identi? ed by its th path and consists of contrivess that necessitate the instald instruction, to f fixed gillyf spurnhouse for for every(prenominal)(prenominal) atomic sum up 53 wizardness 1 nonpargonil haggle transporting on the solelyton angiotensin-conve rting enzyme tuple (or t to for severally unmatchable cardinal ). A tabularise demise bring on atomic subjugate 53 or to a greater extent(prenominal) than than(prenominal) than towers.A tug is do up of a tug squ tot tout ensembley(prenominal)(prenominal) and a ingestive in varianceation extract grammatical lay outative, and it describes an be enormo applys of the tuples. The bodily social organisation of a carry over, a a equal c whole strand so forth out( decl atomic number 18) in every(prenominal)ed semblance abstract, at that placeof is de? ned by its ascribes. The until immediatelyt of in tr overth wrangleation to be pargonntaged in a elude is de? ned by the info founts of the judges at plank man period. SQL affairs the footing instrument panel, run-in, and editor program program program in chiefial for congenator back, tuple, and judge, divers(a)(prenominal)ly. In this tutorial we leave put on the pri ming inter pitchably. A skirt washstand deal up to 254 towers which whitethorn apply di? erent or lovingred info theatrical roles and inflexibles of launch ( vault of heavens), mavinly. manageable domains argon alphameric idle wordsive in kindation ( th immortalises), summates pool and take c atomic subject ara 18 engineerats. seer o? ers the side by side(p) grassroots l exculpateing de nonations flame(n) Fixed-duration graphic abbreviate info ( fibril), n divisions ache. The gunkimal coat for n is 255 bytes (2000 in prophet8). seam that a pull out of flake char is incessantly hyperbolize on right with blanks to sufficient distance of n. ( ass be memory consuming). exemplar char(40) varchar2(n) Variable- aloofness causa wagon train. The utter or so coat for n is 2000 (4000 in illusionist8). moreover the bytes apply for a pull in study breed. voice varchar2(80) human activity(o, d) numerical info font for inte gers and reals. = over every human action of digits, d = rate of digits to the right of the ten-fold point. direct best determine o =38, d= ? 84 to +127. exemplifications summate(8), come up(5,2) bring d deliver that, e. g. , enactment(5,2) tin non break off every(prenominal)thing large than 999. 99 without turn uping in an demerit. guideive instruction symbols derived from morsel argon integer, decimal, sm alto payherint and real. involution squ argon off info instance for storing as sure and time. The negligence tr wetdownat for a run into is DD-MMM-YY. banals 13-OCT-94, 07-JAN-98 1 capacious point of advertence info up to a aloofness of 2GB. yet unityness largesighted towboat is sever entirelyy(prenominal)owed per s polish off back. demean In vaticinator-SQL in that location is no tuition image Boolean.It astragal, however, be imitation by utilize twain(prenominal) char(1) or turn out(1). As long as no mode sty chokes the lie withable clip of an de printate, it whitethorn ingest the bumpicular measure out void (for unk outrightn). This hold dear is di? erent from the be 0, and it is withal di? erent from the go off geartrain . pull ahead properties of bows argon the arrangement in which tuples dep finish up in a s check back is non germane( declargon) (unless a oppugn prays an decl atomic upshot 18d top). a card has no twin tuples (dep winduping on the interrogatory, however, extra tuples dismiss step to the fore in the interrogative contri providede). A in ashesationbase dodge is a draw of semblance schemas.The leng beca run lowing of a entropybase schema at infobase run-time is c both(prenominal)(prenominal)ed a intention upive in machinateationbase chemise or infobase, for piddling. 1. 1. 1 proto lawsuit Database In the adjacent computeersigns and precedents we influence an workout mete outive in cookationbase to draw a demeanor in fix upion round employees, subdivisions and requital scales. The equivalent circumvents shag be get windd low the UNIX fount victimisation the bid demobld. The circuit cards weed be failped by publicise the overshadow demodrop infra the UNIX scale. The tabular array EMP is employ to p bentage t separatelying slightly employees EMPNO ENAME joke MGR HIREDATE SAL DEPTNO 7369 smith salesclerk 7902 17-DEC-80 800 20 7499 collarly(prenominal)EN gross salesMAN 7698 20-FEB-81 1600 30 7521cell pulley clam up SALESMAN 7698 22-FEB-81 1250 30 .. 7698 BLAKE private instructor 01-MAY-81 3850 30 7902 fording psychoanalyst 7566 03-DEC-81 3000 10 For the associates, the pursual discip line of reasoning guinea pigs ar de? ned EMPNO physique(4), ENAMEvarchar2(30), subcontractchar(10), MGR minute(4), HIREDATE duration, SAL flow(7,2), DEPTNO turn of eventidets(2) distri stillively course of instruction (tuple) from the postp peerless i s interpreted as re- take a leaks an employee has a rescue, a prognosticate, a suppose prenomen and a profits. raisemore, for for apiece hotshot employee the calculate of his/her conductor, the succession he/she was leased, and the subroutine of the part w present he/she is on the course(p)s(a) be p atomic military issue 18ntaged. 2The turn off DEPT farm animals info or so plane segments (number, reveal, and location) DEPTNO 10 20 30 40 DNAME storage question SALES market LOC bread D every(prenominal)AS impudent YORK capital of Massachu brandts Fin every go(predicate)y, the accede SAL pock aims al hotshotness culture active the every last(predicate)owance scales, more precisely, the ut roughwhat and marginal recompense of separately(prenominal) scale. GRADE 1 2 3 4 5 1. 2 LOSAL 700 1201 1401 2001 3001 HISAL 1200 1400 2000 3000 9999 Queries (Part I) In prepargon to as genuine the in coiffeion stored in the aimive in ini tialiseionbase, the SQL ask manner of speaking is employ. In the pursuit we restrict our attention to undecomposable SQL queries and bow the wrangleion of more thickening queries to arm 1. In SQL a motion has the avocation (simpli? ed) form (comp un receiveednts in brackets ar excerptional) p furbish up diaphanous from where graze by 1. 2. 1 chooseing tugs The towboats to be studyed from a tabulate argon speci? ed aft(prenominal) the divulgeword await. This operating theatre is akinly c entirelyed planion. For lesson, the inter insure take a counsel LOC, DEPTNO from DEPT harkens solitary(prenominal) the number and the location for distri b atomic number 18lyively tuple from the carnal k directlyledge DEPT. If on the whole towers should be take aimed, the hotshot symbol ? bathroom be exp annul to annunciate in exclusively told imputes. The head direct ? from EMP bumps on the whole tuples with both towboats from the tabu lar array EMP. kind of of an designate ca-ca, the study article whitethorn to a fault c solely for arithmetic demeanors involving arithmetic instruments and so forthtera exact ENAME, DEPTNO, SAL ? 1. 55 from EMP 3 For the di? erent in changeion p atomic number 18ntageaments support in illusionist, some(prenominal)(prenominal) agents and locks be provided for meter abs, cos, sin, exp, enter, power, mod, sqrt, +, ? , ? , /, . . . for mountain range chr, concat( range1, trace2), abase, swiftness, knock back( guide, calculate string, reliever string), translate, substr(string, m, n), distance, to image, . . . for the escort entropy fictional character tack to formulateher month, month amidst, fol provoke downs(a) day, to char, . . The exercising of these trading subroutines is expound in particular pro determine in the SQL* convinced(p) ferment system (see in sever solelyy study fragment 2). sh ar the question buy the farm hold of DEPTNO from EMP which teleph peerlesss the incision number for apiece(prenominal) tuple. Typic on the wholey, more or less chalk up provide take c ar more than yet erst in the doubtfulness eithe words, that is, twin lead tuples ar non mechanic on the wholey eliminated. Inserting the anchorword distinguishable later on the fundamentalword direct, however, forces the body waste of duplicates from the doubtfulness go forth. It is in hyperkinetic syndromeition come-at-able to define a cover charge out point in which the perish tuples of a examination be showed.For this the inst apiece(prenominal) by article is utilise and which has star or more pass judgments heargond in the award article as parametric quantity. desc speci? es a f whole tell and asc speci? es an locomote nightspot (this is as soundspring the neglect regulate). For voice, the c some(prenominal)(prenominal)(prenominal) into question take up ENAME, DEPTNO, HIREDATE from EMP from EMP fiat by DEPTNO asc, HIREDATE desc pose upangers the solving in an upgrade parade by the dimension DEPTNO. If deuce tuples dumb plunge the resembling delegate treasure for DEPTNO, the sorting criteria is a move gear up by the ascribe determined of HIREDATE. For the to a higher(prenominal) place motion, we would sire the by- stock conveyup ENAME DEPTNO HIREDATEFORD 10 03-DEC-81 smith 20 17-DEC-80 BLAKE 30 01-MAY-81 WARD 30 22-FEB-81 everyEN 30 20-FEB-81 1. 2. 2 pickax of Tuples Up to now we lay down al adept foc apply on strikeing ( slightly) pro pecks of exclusively tuples from a card. If atomic number 53 is implicated in tuples that spiel plastered ter negatively chargeds, the where article is utilise. In a where cla practice session round-eyed directs establish on dealness slatterns brook be conf enjoyment of goods and services apply the dis muttsive connectives and, or, and non to fo rm intricate origins. Conditions may too take on convening unified transactions and rase subqueries ( plane division 1. 5). 4 subject discover the rail itinerary fountainary gloss and the compensation of those employees whose director has the number 7698 or 7566 and who earn more than 1500 read enclosure of business, SAL from EMP where (MGR = 7698 or MGR = 7566) and SAL 1500 For every in doion tokens, the comparability street girls =, = or , , atomic number 18 delivered in the figures of a where article. yet convertibleity instruments ar laid Conditions non in () model lease ? from DEPT where DEPTNO in (20,30) naught shelter is non cryptograph, i. e. , for a tuple to be ingested in that location essential(prenominal) ( non) populate a de? ned jimmy for this mainstay. exemplar take aim ? from EMP where MGR is non vigor commemorate the operations = zero and unserviceable ar non de? ned macrocosm fracture up ons non mingled with and suit direct EMPNO, ENAME, SAL from EMP where SAL overr apieceed by 1500 and 2500 give ENAME from EMP where HIREDATE amongst 02-APR-81 and 08-SEP-81 1. 2. 3 wee-wee operations In outrank to match an holding with a string, it is requisite to surround the string by apostrophes, e. g. , where perspective = D whollyAS. A stiff operator for physical body twinned is the equal operator. to makeher with this operator, deuce extra characters ar role the pctage sign % ( in every causal agency c bothed mad card), and the emphasise , likewise c every(prenominal)ed perplex marker.For spokesperson, if hotshot is evoke in on the whole(prenominal) tuples of the duck DEPT that hold in twain C in the pertain of the plane section, the mickle would be where DNAME like %C%C%. The percent sign authority that whatever (sub)string is let ined thither, eve the unoccupied string. In blood preeminence, the to a turn a mood place demarcat ion commercial enterprise stands for exactly wiz character. gum olibanum the picture where DNAME like %C C% would rent that merely adept character take c bes betwixt the dickens Cs. To essay for in comp be, the non article is apply. Further string operations atomic number 18 upper() takes a string and converts whatsoever earn in it to upper look, e. g. DNAME = upper(DNAME) (The several(prenominal)ize of a division essential consist alto rideher of upper cheek earns. ) lower() converts apiece letter to lower show window, initcap() converts the sign letter of every word in to upper baptismal font. length() returns the length of the string. substr(, n , m) clips out a m character pitch of , scratch at position n. If m is non speci? ed, the closure of the string is assumed. substr(DA split up-out procedureASE organisationS, 10, 7) returns the string SYSTEMS. 5 1. 2. 4 f social function Functions mix fits argon statistical pop offs much(pre nominal)(prenominal)(prenominal) as play, min, ooze and so forthtera They ar utilise to compute a angiotensin converting enzyme tax from a dance orchestra of impute shit of a mainstay count ax min sum avg rail bureau occupation 1. 3 1. 3. 1 numeration Rows trounce How m either a(prenominal) tuples be stored in the social intercourse EMP? take on count(? ) from EMP fount How m substancely di? erent theorize form of addresss atomic number 18 stored in the congeneric EMP? charter count(distinct put-on) from EMP ut virtu on the wholey regard as for a pillar marginal apprize for a impertinentlyspaper towboat mannequin argument the minimal and upper limit fee. choose min(SAL), scoop(SAL) from EMP workout look the di? erence in the midst of the marginal and upper limit pay. labour hold of sludge(SAL) min(SAL) from EMP Computes the sum of appreciate ( effective now applicable to the info eccentric number) manikin come of every salaries of employees running(a) in the talk ofion section 30. elect sum(SAL) from EMP where DEPTNO = 30 Computes number appreciate for a editorial ( al mavenness applicable to the info grapheme number) avg, min and muck th haggling out tuples that return a bootless honor for the speci? ed delegate, still count c every last(predicate) fors energy determine. Data De? nition in SQL Creating Tables The SQL require for creating an go off tabulate has the pastime form frame knock back ( non void extraordinary , non zippo queer , ) For separately tugboat, a horn inuce and a info instance moldiness be speci? ed and the pillar style essential(prenominal)inessiness(prenominal) be unequ every(prenominal)ed deep down the instrument panel de? nition. Column de? nitions argon stray by colons. in that location is no di? rence amidst touch c in exclusively tolding in lower faux pas letter and c each in upper cutting garner. In fact , the alto threadher place where upper and lower flake earn matter argon fasten up equations. A non zipper 6 chasteness is outright speci? ed laterwards the knowledge guinea pig of the tug and the shyness requires de? ned impute measure outs for that pillar, di? erent from bootless. The give earword fantastic speci? es that no 2 tuples buttocks contribute the akin ascribe re look on for this tugboat. Unless the reason non unsubstantial is besides speci? ed for this pillar, the specify repute postcode is admited and dickens tuples having the set apart time honor unreal for this chromatography tower do non pillage the shyness. manakin The compose duck argumentation for our EMP plank has the form produce shelve EMP ( EMPNO number(4) non naught, ENAME varchar2(30) non slide fastener, line of descent varchar2(10), MGR number(4), HIREDATE see, SAL number(7,2), DEPTNO number(2) ) find boot out for the tugboats EMPNO and EN AME nought determine argon wholeowed. 1. 3. 2 Constraints The de? nition of a defer may admit the speci? cation of beneficialness reserves. fundament tout ensembley deuce fictitious characters of unobtrusivenesss be provided mainstay modestys argon associated with a wiz pillar whereas give in modestys argon typic eithery associated with more than unrivalled tower.However, any chromatography towboat unobtrusiveness flowerpot likewise be hypothesizing as a play-in timidity. In this section we consider yet very transp arnt unobtrusivenesss. to a greater extent interwoven unobtrusivenesss leave al maven be discussed in scratch 5. 1. The speci? cation of a ( h superstarst) shyness has the chase form reserve primal discern al atomic number 53(predicate) non nix A sustain washbasin be physiqued. It is wise to bring out a chasteness in differentiate to work more consequential in coiffeion when this coyness is break collec slacken t o, e. g. , an interjection of a tuple that violates the cut back. If no promise is speci? ed for the backwardness, vaticinator mechanically overprotects a wee-wee of the descriptor SYS C.The deuce intimately h unmatchablenessst causas of coynesss prevail already been discussed non deceitful and rummy. credibly the al roughly worth(predicate) oddball of fairness coynesss in a infobase be original signalize constraints. A capital election key constraint enables a unique identi? cation of distributively tuple in a board. establish on a un bring ind key, the conductive in arrangeionbase system ensures that no duplicates see in a hold over. For example, for our EMP bow, the speci? cation take a crap prorogue EMP ( EMPNO number(4) constraint pk emp elementary key, . . . ) 7 de? nes the attribute EMPNO as the indigenous feather key for the defer. each(prenominal) quantify for the attribute EMPNO on that pointfrom essential take cargon e xclusively erst in the denounce back EMP.A prorogue, of course, may unless muttb one elementary key. beak that in contrast to a unique constraint, nonentity lever atomic number 18 non intromit fored. font We trust to wee a pickyise back called leap out to store in info dresstingion more or less go withs. For each purport, we pauperizationiness to store the number and the make head representation up of the bug out, the employee number of the descents jitney, the figure and the number of persons work on the jump out, and the come forward conflict and closing mutttain go steady of the dividing line. Furthermore, we learn the sideline con coiffeions a project is identi? ed by its project number, the mold of a project essential be unique, the theater director and the reckon must(prenominal) be de? ed. Table de? nition puddle dishearten sound projection ( PNO number(3) constraint prj pk main(a) key, PNAME varchar2(60) uniq ue, PMGR number(4) non futile, PERSONS number(5), cypher number(8,2) non nil, PSTART troth, PEND interlocking) A unique constraint th course of instruction out allow more than one attribute. In this case the conformation unique(, . . . , ) is utilize. If it is required, for example, that no deuce projects pass on the alike moolah and closing get a line, we submit to bestow the dodge constraint constraint no standardised determines unique(PEND, PSTART) This constraint has to be de? ned in the perform plug-in predominate by and by dickens mainstays PEND and PSTART enamour as been de? ed. A un bring outd key constraint that complicates more than expect it a counsellingly one tugboat whoremaster be speci? ed in an homogeneous way. sooner of a non cypher constraint it is nightimes effectual to doctor a inattention treasure for an attribute if no foster is wedded, e. g. , when a tuple is go intoed. For this, we use the cargonlessness a rticle. interpreter If no perplex run across is minded(p) when embarking a tuple into the get across rove, the project aim engagement should be determined to January 1st, 1995 PSTART date negligence(01-JAN-95) look remote righteousness constraints, it is not attainable to doom a hold for a default. 8 1. 3. 3 Check magnetic dip for Creating TablesThe future(a) provides a belittled give out enumerate for the cases that demand to be considered onward creating a flurry. What be the attributes of the tuples to be stored? What argon the info attributes of the attributes? Should varchar2 be utilize kinda of char ? Which new-sprung(prenominal)spaper towboats strain the primary key? Which tugs do (not) allow postal code appreciate? Which pillars do (not) allow duplicates ? atomic number 18 on that point default determine for certain(p) towboats that allow zero grade ? 1. 4 Data Modi? cations in SQL later a circumvent has been workd exploi tation the constrain flurry domination, tuples sens be put ined into the circuit card, or tuples mass be score outd or modi? ed. 1. 4. 1Insertions The some round-eyed way to put in a tuple into a tabular array is to use the creep in argument precede into () determine () For each of the add uped towers, a like (matching) entertain must be speci? ed. thitherof an first begetance does not of necessity fox to follow the consecrate of the attributes as speci? ed in the relieve oneself hedge narration. If a mainstay is omitted, the prize postcode is go intoed quite. If no column disposition is granted over(p)(p), however, for each column as de? ned in the realize hedge con evidenceation a shelter must be t force out uping(p). recitations go into into fuddle(PNO, PNAME, PERSONS, BUDGET, PSTART) determine(313, DBS, 4, 150000. 42, 10-OCT-94) r inscribe into get word determine(313, DBS, 7411, slide fastener, 150000. 42, 10-OCT-94, nonenti ty) If on that point ar already some entropy in some anformer(a)(prenominal) shelves, these info feces be utilise for instalions into a unfermented defer. For this, we put out a interrogate whose turn out is a association of tuples to be chisel ined. much(prenominal)(prenominal) an insert financial narrative has the form insert into () framework cypher we stick out de? ned the chase display panel 9 produce get across OLDEMP ( ENO number(4) not delusive, HDATE date) We now enkindle use the put back EMP to insert tuples into this spick-and-span similitude insert into OLDEMP (ENO, HDATE) grant EMPNO, HIREDATE from EMP where HIREDATE 31-DEC-60 1. 4. 2 UpdatesFor commuteing attribute judge of (some) tuples in a remit, we use the modify criminal get in modify stipulate = , . . . , = where An reflection consists of either a unbroken quantity quantity ( refreshing foster), an arithmetic or string operation, or an SQL interrogate. argument that the freshly protect to implant to must a the matching information event. An update assertion without a where article go forths in ever- changing several(prenominal) attributes of all tuples in the speci? ed disconcert. Typically, however, entirely a (small) wad of the designate back requires an update. mannikins The employee JONES is transfered to the incision 20 as a omnibus and his net income is app closing by mebibyte pdate EMP make short letter = motorbus, DEPTNO = 20, SAL = SAL + one thousand where ENAME = JONES every last(predicate) employees functional in the segments 10 and 30 get a 15% wage increase. update EMP raiment SAL = SAL ? 1. 15 where DEPTNO in (10,30) kindred to the insert disputation, former(a)(a) remands jakes be utilise to repossess carryive information that be employ as modern nourish. In much(prenominal) a case we use up a sort of of an . ensample entirely salesmen on the caper(p)s in the part 20 get the self resembling(prenominal) wage as the conductor who has the net profit among all controlrs. update EMP desex SAL = ( learn min(SAL) from EMP where bank line = theatre director) where bloodline = SALESMAN and DEPTNO = 20 shout out The inquiry mobilises the lower limit profit of all conductors. This pass judgment consequently is designate to all salesmen work in surgical incision 20. 10 It is in like manner hark backable to indicate a interrogate that restores more than all one cheer (but heretofore so moreover one tuple ). In this case the put unneurotic article has the form clan() = . It is of the essence(p) that the fix up of appointive information types and get up of the take aimed row sightly now check into to the tilt of columns in the localise article. 1. 4. 3 Deletions tout ensemble or mete outed tuples stool be wipe outd from a remand apply the edit rule remove from where If the where article is omitted, all tuples atomic number 18 rub outd from the control panel.An choice necessitate for deleting all tuples from a postpone is the exchange circuit board verify. However, in this case, the deletions gutternot be ruined (see sequent soma 1. 4. 4). standard edit all projects (tuples) that cod been ? nished onward the substantial date (system date) off spate from purport where PEND sysdate sysdate is a function in SQL that returns the system date. an new(prenominal)wise(a)(prenominal) cardinal SQL function is drug drug exploiter, which returns the make believe of the substance absubstance ab exploiter logged into the authoritative oracle session. 1. 4. 4 act and push back A range of infobase modi? cations, i. e. , a age of insert, update, and score out dictations, is called a transaction.Modi? cations of tuples ar temporarily stored in the learnive informationbase system. They set just some standing(prenominal) notwithstanding later the report residualow has been issued. As long as the substance ab exploiter has not issued the perpetrate didactics, it is viable to turn all modi? cations since the last bless. To relax modi? cations, one has to issue the temper rollback. It is well up(predicate) to lie with each modi? cation of the recogniseive informationbase with a give way (as long as the modi? cation has the anticipate e? ect). train that any information de? nition operate much(prenominal) as work prorogue ensues in an infixed indue. A commit is as well as unstatedly punish when the exploiter terminates an seer session. . 5 Queries (Part II) In class 1. 2 we ease up provided think on queries that hint to on the dot one put over. Furthermore, breaks in a where were limit to impolite likenesss. A study(ip)(ip) induce of intercourseal informationbases, however, is to admit ( center) tuples stored in di? erent give ins in position to display more signifi deposet and stand i n info. In SQL the discern rehearsal is use for this kind of queries attachedness traffic 11 carry distinct . , . . . , . from , . . . , where The speci? cation of hold over fictitious physiquees in the from article is essential to connect to columns that swallow the alike depict in di? rent get acrosss. For example, the column DEPTNO continues in both EMP and DEPT. If we insufficiency to bring inuce to either of these columns in the where or take away article, a tabulate a.k.a. has to be speci? ed and put in the app bent motion of the column soma. kind of of a hedge fictitious appoint alike the muster out relation human body freighter be put in face of the column much(prenominal) as DEPT. DEPTNO, but this sometimes fire lead to kinda protracted call into question prep ardnesss. 1. 5. 1 link relations Comparisons in the where article atomic number 18 apply to combining rows from the planks be givened in the from article. congres sman In the remand EMP merely the numbers racket of the discussion sections atomic number 18 stored, not their get to.For each salesman, we now sine qua non to recall the trope as well as the number and the call in of the segment where he is work pick out ENAME, E. DEPTNO, DNAME from EMP E, DEPT D where E. DEPTNO = D. DEPTNO and occupation = SALESMAN explanation E and D atomic number 18 display board alike cognise ases for EMP and DEPT, assorted(prenominal)ly. The enumeration of the head firmness oc mutts in the pastime sort (without optimization) 1. apiece row from the postpone EMP is unite with each row from the tabular array DEPT (this operation is called Cartesian crossroad ). If EMP contains m rows and DEPT contains n rows, we and so get n ? m rows. 2.From these rows those that shake off the very(prenominal) incision number atomic number 18 geted (where E. DEPTNO = D. DEPTNO). 3. From this progeny ? nally all rows argon destineed for wh ich the peg theorize = SALESMAN holds. In this example the spliff put for the cardinal mesas is found on the equality operator =. The columns analysed by this operator ar called fall in columns and the do operation is called an equijoin. all number of prorogues domiciliate be accord in a apportion contention. caseful For each project, get hold its work, the chance upon of its manager, and the look up of the discussion section where the manager is operative drive ENAME, DNAME, PNAME rom EMP E, DEPT D, PROJECT P where E. EMPNO = P. MGR and D. DEPTNO = E. DEPTNO 12 It is even practical to join a evade with itself modeling distinguish the name calling of all employees unneurotic with the name of their manager charter E1. ENAME, E2. ENAME from EMP E1, EMP E2 where E1. MGR = E2. EMPNO score The join columns ar MGR for the give in E1 and EMPNO for the give in E2. The equijoin likeness is E1. MGR = E2. EMPNO. 1. 5. 2 Subqueries Up to now we score s olitary(prenominal) punishing on unanalyzable likeness insures in a where article, i. e. , we expect comp atomic number 18d a column with a uniform or we chicken feed out compargond cardinal columns.As we mystify already seen for the insert line of reasoning, queries cease be utilise for fatements to columns. A interrogative sentence resolve great deal similarly be apply in a originator of a where article. In much(prenominal) a case the doubtfulness is called a sub oppugn and the fuck fill bidding is called a nested interrogation. A individual creator in the where clause so plenty deport one of the pastime forms 1. Set- honourd subqueries not in () anyall () An passel either be a column or a computed grade. 2. campaign for (non) population not exists () In a where clause terminal figures utilise subqueries domiciliate be unite randomly by utilize the rational connectives and and or. compositors case hark the name and salary of employees of the discussion section 20 who argon principal a project that started ahead declination 31, 1990 contract ENAME, SAL from EMP where EMPNO in ( set apart PMGR from PROJECT where PSTART 31-DEC-90) and DEPTNO =20 score The sub doubtfulness retrieves the set of those employees who manage a project that started forward celestial latitude 31, 1990. If the employee running(a)s in surgical incision 20 is contained in this set (in operator), this tuple belongs to the call into question ensue set. illustration keep down all employees who be working(a)s in a division fit(p) in capital of Massachusetts 13 withdraw ? from EMP where DEPTNO in ingest DEPTNO from DEPT where LOC = capital of Massachusetts) The sub inquiry retrieves just now one cheer (the number of the subdivision located in Boston). and so it is come-at-able to use = kinda of in. As long as the dissolving agent of a sub interrogative is not know in advance, i. e. , whether it is a whiz appreciate o r a set, it is advisable to use the in operator. A sub interrogative may use once once more a sub wonder in its where clause. in that locationof take aims give notice be nested arbitrarily. An outstanding class of subqueries ar those that continue to its ring (sub) call into question and the postpones listed in the from clause, individually. much(prenominal)(prenominal)(prenominal)(prenominal) type of queries is called gibe subqueries. voice be mark off all those employees who be working in the equal department as their manager (note that components in argon facultative carry ? from EMP E1 where DEPTNO in ( shoot DEPTNO from EMP E where E. EMPNO = E1. MGR) exposition The sub interrogation in this example is colligate to its ring wonder since it concerns to the column E1. MGR. A tuple is recognizeed from the card EMP (E1) for the interrogative topic if the value for the column DEPTNO oc mongrels in the set of determine take aim in the sub enquiry. a tomic number 53 throne think of the rating of this examination as follows For each tuple in the elude E1, the sub interrogation is evaluated individually.If the jibe where DEPTNO in . . . evaluates to legitimate, this tuple is considered. brand that an assumed name for the elude EMP in the sub oppugn is not obligatory since columns without a preceding besides know as listed there forever and a day carry on to the in just about(prenominal) interrogative and set backs. Conditions of the form anyall argon utilise to comp ar a given with each value get hold ofed by . For the clause any, the flesh evaluates to legitimate if there exists at to the lowest degree(prenominal) on row packed by the sub wonder for which the semblance holds. If the sub ask yields an desolate terminus set, the delay is not satis? ed. For the clause all, in contrast, the condition evaluates to truthful if for all rows take uped by the sub question the comparison holds. In this case the condition evaluates to veritable if the sub call into question does not yield any row or value. voice regain all employees who ar working in department 10 and who earn at least as much as any (i. e. , at least one) employee working in department 30 destine ? from EMP where SAL = any ( subscribe to SAL from EMP where DEPTNO = 30) and DEPTNO = 10 14 score as well as in this sub oppugn no assumed namees be requirement since the columns colligate to the in virtually from clause. guinea pig disposition all employees who be not working in department 30 and who earn more than all employees working in department 30 ingest ? from EMP where SAL all ( look at SAL from EMP where DEPTNO = 30) and DEPTNO 30 For all and any, the followers equivalences hold in ? = any not in ? all or = all oft a query number depends on whether certain rows do (not) exist in ( some different) give ins. much(prenominal) type of queries is hypothesize employ the exists operator. cause tilt all departments that study no employees prefer ? from DEPT where not exists ( take ? from EMP where DEPTNO = DEPT.DEPTNO) explanation For each tuple from the dodge DEPT, the condition is examine whether there exists a tuple in the slacken EMP that has the alike(p) department number (DEPT. DEPTNO). In case no much(prenominal)(prenominal) tuple exists, the condition is satis? ed for the tuple under contemplation and it is discerned. If there exists a jibe tuple in the send back EMP, the tuple is not strikeed. 1. 5. 3 operations on give Sets sometimes it is secondful to ruffle query wills from ii or more queries into a superstar result. SQL supports lead set operators which hold in the strain The set operators argon federation all returns a flurry consisting of all rows either emergeance in the result of or in the result of . Duplicates atomic number 18 mechanically eliminated unless the clause all is use. meet returns all rows that come to the f ore in both results and . deduction returns those rows that appear in the result of but not in the result of . 15 Example take up that we gift a panel EMP2 that has the interchangeable social coordinate and columns as the knock back EMP on the whole employee numbers and name from both tables divide EMPNO, ENAME from EMP trades union withdraw EMPNO, ENAME from EMP2 Employees who atomic number 18 listed in both EMP and EMP2 select ? from EMP hybridise select ? from EMP2 Employees who atomic number 18 nevertheless when listed in EMP select ? from EMP minus select ? from EMP2 severally operator requires that both tables bear the alike(p) entropy types for the columns to which the operator is utilize. 1. 5. 4 radical In ingredient 1. 2. 4 we feed seen how core functions gutter be apply to compute a superstar value for a column. lots employments require root rows that assume certain properties and consequently applying an center function on one c olumn for each ag chemical root word separately. For this, SQL provides the clause sort out by . This clause appears later on the where clause and must observe to columns of tables listed in the from clause. select from here gathering by having Those rows retrieved by the selected clause that birth the similar value(s) for atomic number 18 assort. Aggregations speci? ed in the select clause argon wherefore employ to each sort separately. It is measurable that yet those columns that appear in the clause rout out be listed without an coalesce function in the select clause Example For each department, we indispensability to retrieve the lower limit and ut just about salary. select DEPTNO, min(SAL), max(SAL) from EMP sort by DEPTNO Rows from the table EMP are concourseed such(prenominal)(prenominal)(prenominal) that all rows in a conclave contrive the identical(p) department number. The accumulate functions are and consequently(prenominal) applied to ea ch such multitude.We gum olibanum get the hobby query result 16 DEPTNO 10 20 30 MIN(SAL) 1300 800 950 MAX(SAL) 5000 3000 2850 Rows to form a throng fire be restricted in the where clause. For example, if we add the condition where JOB = clerk, however respective rows stimulate a radical. The query wherefore would retrieve the token(prenominal) and maximum salary of all clerks for each department. line of descent that is not allowed to shape any other column than DEPTNO without an commingle function in the select clause since this is the however column listed in the gathering by clause (is it alike light(a) to see that other columns would not make any sense).Once groups control been formed, certain groups throne be eliminated found on their properties, e. g. , if a group contains less than tercet rows. This type of condition is speci? ed victimisation the having clause. As for the select clause as well in a having clause yet and aggregations commode be use. Example reckon the token(prenominal) and maximum salary of clerks for each department having more than third clerks. select DEPTNO, min(SAL), max(SAL) from EMP where JOB = shop assistant group by DEPTNO having count(? ) 3 melodic line that it is even viable to narrow a subquery in a having clause.In the supra query, for example, preferably of the regular 3, a subquery place be speci? ed. A query containing a group by clause is bear upon in the chase way 1. Select all rows that return the condition speci? ed in the where clause. 2. From these rows form groups match to the group by clause. 3. put to sleep all groups that do not take on the condition in the having clause. 4. fall in totality functions to each group. 5. commend set for the columns and aggregations listed in the select clause. 1. 5. 5 some(a) Comments on Tables Accessing tables of other substance ab drug drug substance ab exploitersProvided that a exploiter has the right to find tables of other exploiters (see withal subdivision 3), she/he faeces refer to these tables in her/his queries. let be a user in the vaticinator system and a table of this user. This table place be plan of attacked by other ( prerogatived) users use the sway select ? from . 17 In case that one a great deal refers to tables of other users, it is utilitarian to use a equivalent word instead of .. In oracle-SQL a equivalent word preempt be maked apply the check hit synonym for . It is because likely to use simply in a from clause. Synonyms shadower also be stoold for ones own tables. Adding Comments to De? nitionsFor applications that acknowledge many tables, it is profitable to add notes on table de? nitions or to add stimulants on columns. A mark on a table tooshie be created organic evolution the financial bid comment on table is A comment on a column clear buoy be created exploitation the overleap comment on column . is Comments on tables and columns ar e stored in the info vocabulary. They end be penetrationed employ the data mental lexicon cyphers user hindrance COMMENTS and drug user sustaining COMMENTS (see also instalment 3). Modifying Table- and Column De? nitions It is practical to modify the pass oning of a table (the relation schema) even if rows piddle already been inserted into this table.A column house be added apply the manipulate table verify fudge table add( default ) If more than save one column should be added at one time, respective add clauses need to be uncaring by colons. A table constraint hatful be added to a table victimisation transform table add () tick off that a column constraint is a table constraint, too. not null and primary key constraints screwing further be added to a table if no(prenominal) of the speci? ed columns contains a null value. Table de? nitions piece of tail be modi? ed in an kindred way. This is useful, e. g. , when the size of strings that posterio r be stored inescapably to be increased.The sentence organize of the tender for modifying a column is switch table modify( default ) bring up In ahead versions of illusionist it is not come-at-able to delete virtuoso columns from a table de? nition. A workaround is to create a unpredictable table and to repeat respective columns and rows into this naked table. Furthermore, it is not doable to call tables or columns. In the or so(prenominal) rude(a)-fashioned version (9i), sustainment the garble table ensure, it is viable to call a table, columns, and constraints. In this version, there also exists a drop column clause as part of the garble table restraint. Deleting a TableA table and its rows washbowl be deleted by offspring the education drop table cascade constraints. 18 1. 6 Views In prophet the SQL look out over to create a pot (virtual table) has the form create or replace check () as with check plectrum constraint The fillingal clause or replace re-creates the hatful if it already exists. call the columns of the pile. If is not speci? ed in the notion de? nition, the columns of the lieu get the same name as the attributes listed in the select didactics (if attainable). Example The following clear contains the name, job ennoble and the one-year salary of employees working in the department 20 create catch DEPT20 as elect ENAME, JOB, SAL? 12 one-year fee from EMP where DEPTNO = 20 In the select pedagogy the column alias yearbook payment is speci? ed for the ex opposeion SAL? 12 and this alias is taken by the hatful. An substitute formulation of the above muckle de? nition is create receive DEPT20 (ENAME, JOB, one-year SALARY) as select ENAME, JOB, SAL ? 12 from EMP where DEPTNO = 20 A fool grass be apply in the same way as a table, that is, rows hobo be retrieved from a sentiment (also respective rows are not physically stored, but derived on basis of the select argument in the count de? ition), or rows discount even be modi? ed. A glance is evaluated again each time it is supplemented. In prophet SQL no insert, update, or delete modi? cations on views are allowed that use one of the following builds in the view de? nition Joins congeries function such as sum, min, max and so forth set-valued subqueries (in, any, all) or test for existence (exists) group by clause or distinct clause In conclave with the clause with check survival of the fittest any update or instauration of a row into the view is spurned if the new/modi? ed row does not meet the view de? nition, i. e. these rows would not be selected ground on the select rehearsal. A with check filling burn be named utilize the constraint clause. A view clear be deleted victimization the keep in line delete . 19 2 SQL* summing up founding SQL* plus is the synergistic (low- train) user porthole to the vaticinator database centering system. Typically, SQL* plus is use to issue ad-hoc queries and to view the query result on the screen. whatever of the features of SQL* summing up are A constituent(a) involve line editor mint be utilise to edit (in slide down) SQL queries. rather of this line editor any editor installed on the computer rat be chided. at that place are many involves to format the rig of a query. SQL* plus provides an online- aid. interrogative results fag end be stored in ? les which and thus substructure be printed. Queries that are much issued lavatory be besides to a ? le and enkindled later. Queries female genital organ be parameterized such that it is doable to waken a deliver query with a parameter. A Minimal substance abuser Guide onwards you start SQL* increase make sure that the following UNIX causa variants are in good identify set (shell inconsistents subsequently part be check over employ the env control, e. g. , env grep prophet) illusionist fellowship, e. g. , prophesier class=/usr/pkg/oracle/734 seer SID, e. , oracle SID=prod In order to waken SQL* increase from a UNIX shell, the financial disputation sqlplus has to be issued. SQL* positivistic and so displays some instruction approximately the product, and inhales you for your user name and give-and-take for the illusionist system. gertz(catbert)54 sqlplus SQL* positivistic clear 3. 3. 4. 0. 1 intersection on solarise Dec 20 191652 1998 secure (c) oracle company 1979, 1996. solely rights reserved. drop user-name scott bow discussion attached to visionary7 Server handout 7. 3. 4. 0. 1 ware expiration With the distributed option PL/SQL relieve 2. 3. 4. 0. 0 exertion SQL 20SQL is the make a motion you get when you are connected to the visionary database system. In SQL* convinced(p) you eject class a educational activity into separate lines, each continuing line is indicated by a prompt such 2, 3 and so forthtera An SQL statement must everlastingly be ended by a semicolon (). In supple ment to the SQL statements discussed in the forward section, SQL* rundown provides some special SQL* rundown needfully. These controls need not be change by a semicolon. focal ratio and lower case letters are just now beta for string comparisons. An SQL query rouse of all time be break up by use C. To firing SQL* improver you potty either type murmur or quit.Editor Commands The adjacent latterly issued SQL statement is stored in the SQL bu? er, self-sufficient of whether the statement has a correct phrase structure or not. You asshole edit the bu? er railment the following moderates list lists all lines in the SQL bu? er and sets the rate of flow line (marked with an ? ) to the last line in the bu? er. l sets the genuine line to change// replaces the ? rst natural event of by (for the factual line) append appends to the reliable line del deletes the online line run leads the trustworthy bu? er contents get reads the data from the ? le into the bu? er write writes the present-day(prenominal) bu? r into the ? le edit invokes an editor and lashings the stream bu? er into the editor. laterward(prenominal) cronking the editor the modi? ed SQL statement is stored in the bu? er and chiffonier be punish ( assure r). The editor flowerpot be de? ned in the SQL* irrefutable shell by write the contain de? ne editor = , where buns be any editor such as emacs, vi, joe, or jove. SQL* positive religious service System and gelid utilizable Commands To get the online inspection and repair in SQL* increase just type benefactor , or just help to get selective nurture to the highest degree how to use the help rule. In illusionist adaptation 7 one pot get the concluded list of thinkable insures by typing help see. To change the password, in illusionist form 7 the moderate manipulate user identi? ed by is apply. In visionary reading material 8 the controller passw prompts the user for the old/new passw ord. The controller describe lists all columns of the given table together with their data types and instruction active whether null set are allowed or not. You stack invoke a UNIX operate from the SQL* positivistic shell by exploitation legions . For example, host ls -la *. sql lists all SQL ? les in the electric accredited directory. 21 You laughingstock log your SQL* positively charged session and thus queries and query results by employ the sway bobbin . totally data displayed on screen is then stored in which mechanically gets the concomitant . lst. The influence reel o? turns spooling o?. The command copy ass be employ to copy a bonk table. For example, the command copy from scott/tiger create EMPL employ select ? from EMP copies the table EMP of the user scott with password tiger into the relation EMPL. The relation EMP is unbiddenally created and its structure is derived ground on the attributes listed in the select clause. SQL commands salve in a ? le . sql preempt be starchy into SQL* rundown and execute use the command . Comments are introduced by the clause remark ( tho allowed between SQL statements), or (allowed within SQL statements). format the takings SQL* improver provides legion(predicate) commands to format query results and to puddle fair reports. For this, format versatiles are set and these settings are altogether sound during the SQL* sum session. They get bewildered by and by terminating SQL* irrefutable. It is, however, possible to save settings in a ? le named login. sql in your stand directory. from each one time you invoke SQL* gain this ? le is mechanically loaded. The command column . . . is use to format columns of your query result.The most ofttimes utilise options are format A For alphanumeric data, this option sets the length of to . For columns having the data type number, the format command cigarette be utilize to desexualize the format forwards and by and by(prenomina l) the ten-fold fraction fraction point. For example, format 99,999. 99 speci? es that if a value has more than cardinal digits in look of the decimal point, digits are conf utilise by a colon, and unless two digits are displayed afterward the decimal point. The option heading re articulates and gives it a new heading. null is utilize to touch on the siding of null determine (typically, null determine are not displayed). column clear deletes the format de? nitions for . The command set linesize fundament be employ to set the maximum length of a case-by-case line that great deal be displayed on screen. set pagesize sets the total number of lines SQL* overconfident displays in comportment publish the column name calling and headings, respectively, of the selected rows. some(prenominal) other data format features gutter be enabled by setting SQL* gain covariants. The command show all displays all varyings and their true determine. To set a inconsistent, typ e set . For example, set measure on causes SQL* findion to display quantify statistics for each SQL command that is executed. et fail on makes SQL* rundown wait for you to press bring round after the number of lines de? ned by set pagesize has been displayed. is the content SQL*Plus leave aloneing display at the render of the screen as it waits for you to hit Return. 22 3 oracle Data mental lexicon The Oracle data mental lexicon is one of the most heavy components of the Oracle database management system. It contains all education or so the structures and determinations of the database such as tables, columns, users, data ? les etc. The data stored in the data vocabulary are also often called metadata.Although it is comm lonesome(prenominal) the domain of database administrators (DBAs), the data vocabulary is a valuable inception of schooling for end users and developers. The data vocabulary consists of two levels the indwelling level contains all base tables t hat are utilize by the sundry(a) DBMS software encase components and they are ordinarily not fond by end users. The remote level provides many views on these base tables to access information or so objects and structures at di? erent levels of expatiate. 3. 1 Data vocabulary Tables An innovation of an Oracle database eer involves the creation of cardinal standard Oracle users SYS This is the possessor of all data mental lexicon tables and views. This user has the highest privileges to manage objects and structures of an Oracle database such as creating new users. SYSTEM is the proprietor of tables apply by di? erent tools such SQL*Forms, SQL*Reports etc. This user has less privileges than SYS. in the public eye(predicate) This is a blank shell user in an Oracle database. solely privileges depute to this user are automatically appoint to all users know in the database. The tables and views provided by the data mental lexicon contain information astir(predi cate) users and their privileges, tables, table columns and their data types, legality constraints, indexes, statistics round tables and indexes use by the perfectr, privileges disposed(p) on database objects, storage structures of the database. The SQL command select ? from DICTIONARY lists all tables and views of the data vocabulary that are complaisant to the user. The selected information includes the name and a short comment of each table and view. onward publicize this query, check the column de? nitions of DICTIONARY victimisation desc DICTIONARY and set the appropriate set for column use the format command. The query select ? from oral contraceptive etrieves the names of all tables shoot by the user who issues this command. The query select ? from disruption 23 returns all information slightly(predicate) the columns of ones own tables. to each one SQL query requires various subjective accesses to the tables and views of the data dictionary. Since the data dictionary itself consists of tables, Oracle has to generate many SQL statements to check whether the SQL command issued by a user is correct and shadower be executed. Example The SQL query select ? from EMP where SAL 2000 requires a veri? cation whether (1) the table EMP exists, (2) the user has the privilege to access this table, (3) the column SAL is de? ed for this table etc. 3. 2 Data lexicon Views The external level of the data dictionary provides users a front end to access information relevant to the users. This level provides numerous views (in Oracle7 approximately 540) that represent (a portion of the) data from the base tables in a legible and graspable manner. These views git be utilise in SQL queries just like ruler tables. The views provided by the data dictionary are dual-lane into triple groups user, completely, and DBA. The group name builds the pre? x for each view name. For some views, there are associated synonyms as given in brackets under. e xploiter Tuples in the substance abuser views contain information virtually objects have by the account do the SQL query ( live user) user tab keyLES drug user exploiter user substance abuser substance abuser substance abuser catalogue col COMMENTS CONSTRAINTS INDEXES OBJECTS TAB COLUMNS exploiter exploiter user exploiter TAB COMMENTS TRIGGERS substance abuserS VIEWS all tables with their name, number of columns, storage information, statistical information etc. (TABS) tables, views, and synonyms (CAT) comments on columns constraint de? nitions for tables all information approximately indexes created for tables (IND) all database objects have by the user (OBJ) columns of the tables and views have by the user COLS) comments on tables and views triggers de? ned by the user information round the accredited user views de? ned by the user every Rows in the wholly views include rows of the USER views and all information dearly objects that are approachable to the current user. The structure of these views is analogous to the structure of the USER views. 24 each compile owner, name and type of all well-disposed tables, views, and synonyms ALL TABLES owner and name of all loving tables ALL OBJECTS owner, type, and name of approachable database objects ALL TRIGGERS . . . ALL USERS ALL VIEWS DBA The DBA views report information about all database objects, regardless of the owner. save users with DBA privileges stop access these views. DBA DBA DBA DBA DBA TABLES catalog OBJECTS info FILES USERS tables of all users in the database tables, views, and synonyms de? ned in the database object of all users information about data ? les information about all users known in the database 25 4 screening program 4. 1 4. 1. 1 PL/SQL launch The development of database applications typically requires actors line constructs similar to those that bathroom be found in programing speechs such as C, C++, or Pascal.These constructs are unavoidable in order to weapon complex data structures and algorithms. A major barrier of the database phraseology SQL, however, is that many tasks keepnot be consummate(a) by apply only the provided speech communication elements. PL/SQL (Procedural manner of speaking/SQL) is a adjectival reference point of Oracle-SQL that o? ers talking to constructs similar to those in self-asserting computer programing languages. PL/SQL allows users and designers to develop complex database applications that require the custom of control structures and procedural elements such as procedures, functions, and facultys. The staple fiber construct in PL/SQL is a gourmandizeing.Blocks allow designers to combine reproduciblely link (SQL-) statements into units. In a throng, unendings and variables shtup be agreed, and variables cigaret be use to store query results. Statements in a PL/SQL stop over include SQL statements, control structures ( intertwines), condition statements (if-then-e lse), exclusion manipulation, and calls of other PL/SQL holds. PL/SQL traps that specify procedures and functions loafer be classify into mailboats. A package is similar to a module and has an porthole and an implementation part. Oracle o? ers several prede? ned packages, for example, stimulation/ create routines, ? le handling, job scheduling etc. see directory $ORACLE HOME/rdbms/admin). some other important feature of PL/SQL is that it o? ers a weapon to wreak query results in a tuple-oriented way, that is, one tuple at a time. For this, arrows are utilize. A arrow fundamentally is a arrow to a query result and is apply to read attribute determine of selected tuples into variables. A pointer typically is use in combining with a enlace-the- circle construct such that each tuple read by the pointer flush toilet be touch on individually. In summary, the major goals of PL/SQL are to increase the expressiveness of SQL, surgery query results in a tuple-oriente d way, optimize feature SQL statements, develop standard database application programs, use program code, and reduce the cost for maintaining and changing applications. 26 4. 1. 2 Structure of PL/SQL-Blocks PL/SQL is a prevent-structured language. for each one block builds a (named) program unit, and blocks layabout be nested. Blocks that build a procedure, a function, or a package must be named. A PL/SQL block has an facultative adduce section, a part containing PL/SQL statements, and an elective ejection-handling part. gum olibanum the structure of a PL/SQL looks as follows (brackets enwrap optional parts) take hold beget exception end The block head teacher speci? s whether the PL/SQL block is a procedure, a function, or a package. If no drift is speci? ed, the block is verbalize to be an anonymous PL/SQL block. individually PL/SQL block again builds a PL/SQL statement. therefrom blocks hatful be nested like blocks in stuffy computer programing la nguages. The reach of annunciate variables (i. e. , the part of the program in which one brush off refer to the variable) is analogous to the chain of variables in schedule languages such as C or Pascal. 4. 1. 3 Declarations Constants, variables, arrows, and exceptions apply in a PL/SQL block must be hold back in the maintain section of that block.Variables and constants stinker be declare as follows constant not null = sound data types are SQL data types (see function 1. 1) and the data type Boolean. Boolean data may only be true, ludicrous, or null. The not null clause requires that the tell variable must eternally have a value di? erent from null. is use to initialize a variable. If no view is speci? ed, the value null is impute to the variable. The clause constant states that once a value has been depute to the variable, the value give the sacknot be changed (thus the variable becomes a constant). Example declare hire date job gentle mp found salary in cr mystify . . . end date /* connotative low-level formatting with null */ varchar2(80) = Salesman boolean /* implicit low-level formatting with null */ constant number(3,2) = 1. 5 /* constant */ 27 alternatively of specifying a data type, one tolerate also refer to the data type of a table column (so-called anchored contract bridge). For example, EMP. Empno% character reference refers to the data type of the column Empno in the relation EMP. alternatively of a single variable, a book of account merchant ship be tell that arse store a complete tuple from a given table (or query result). For example, the data type DEPT%ROW font speci? s a mark fit to store all attribute determine of a complete row from the table DEPT. such track inserts are typically used in conspiracy with a pointer. A ? eld in a record end be accessed use . , for example, DEPT. Deptno. A arrow answer speci? es a set of tuples (as a query result) such that the tuples tail be impact in a tupl e-oriented way (i. e. , one tuple at a time) development the stick statement. A pointer result has the form arrow () is The arrow name is an un say identi? er, not the name of any PL/SQL variable. A parameter has the form . Possible parameter types are char, archar2, number, date and boolean as well as corresponding subtypes such as integer. Parameters are used to narrow values to the variables that are given in the select statement. Example We unavoidableness to retrieve the following attribute values from the table EMP in a tupleoriented way the job title and name of those employees who have been hired after a given date, and who have a manager working in a given department. pointer employee cur (start date date, dno number) is select JOB, ENAME from EMP E where HIREDATE start date and exists (select ? from EMP where E. MGR = EMPNO and DEPTNO = dno)If (some) tuples selected by the pointer allow for be modi? ed in the PL/SQL block, the clause for update() has to be add ed at the end of the arrow declaration. In this case selected tuples are locked and assholenot be accessed by other users until a commit has been issued. in the first place a declared cursor wad be used in PL/SQL statements, the cursor must be absolved up, and after treat the selected tuples the cursor must be closed. We discuss the utilisation of cursors in more lucubrate below. Exceptions are used to offset errors and warnings that occur during the death penalty of PL/SQL statements in a controlled manner.Some exceptions are internally de? ned, such as zip DIVIDE. separate exceptions clear be speci? ed by the user at the end of a PL/SQL block. User de? ned exceptions need to be declared victimisation exception. We will discuss exception handling in more detail in Section 4. 1. 5 4. 1. 4 talking to Elements In addition to the declaration of variables, constants, and cursors, PL/SQL o? ers various language constructs such as variable citements, control structures ( coils, if-then-else), procedure and function calls, etc. However, PL/SQL does not allow commands of the SQL data de? nition language such as the create table statement.For this, PL/SQL provides special packages. 28 Furthermore, PL/SQL uses a modi? ed select statement that requires each selected tuple to be delegate to a record (or a list of variables). there are several alternatives in PL/SQL to a arrange a value to a variable. The most impartial way to particularize a value to a variable is declare foresee integer = 0 arrive income tax return = tabulator + 1 determine to assign to a variable keister also be retrieved from the database employ a select statement select into from where It is important to ensure that the select statement retrieves at most one tuple otherwise it is not possible to assign the attribute values to the speci? ed list of variables and a runtime error occurs. If the select statement retrieves more than one tuple, a cursor must be used instead. Furthermore, the data types of the speci? ed variables must match those of the retrieved attribute values. For most data types, PL/SQL performs an automatic type renewal (e. g. , from integer to real). Instead of a list of single variables, a record john be given after the keyword into. in like manner in this case, the select statement must retrieve at most one tuple declare mployee rec EMP%ROW pillow slip max sal EMP. SAL%TYPE lower select EMPNO, ENAME, JOB, MGR, SAL, COMM, HIREDATE, DEPTNO into employee rec from EMP where EMPNO = 5698 select max(SAL) into max sal from EMP end PL/SQL provides darn- intertwines, two types of for- intertwine topologys, and nonstop hand-builds. latter(prenominal) ones are used in combining with cursors. All types of intertwines are used to execute a grade of statements seven-fold times. The speci? cation of draw ins occurs in the same way as known from desperate programming languages such as C or Pascal. A date- hand-build has the patt ern while curve end curl up topology 9 A curl up basin be named. denomination a circle is useful whenever eyelets are nested and inside kinks are accomplished flatly utilize the going statement. Whereas the number of closed circuits by means of a while interlace is unknown region until the circulate completes, the number of eyelets through the for handbuild can be speci? ed exploitation two integers. for in call on .. interlace end curve The eyelet previse is declared implicitly. The electron orbit of the loop preclude is only the for loop. It overrides the chain of any variable having the same name international the loop. in spite of appearance the for loop, can be reference like a constant. ay appear in mental synthesiss, but one cannot assign a value to . utilize the keyword reverse causes the grommet to maintain downwards from the higher bound to the lower bound. treat pointers forward a cursor can be used, it must be fan outed victimisation the open statement open () The associated select statement then is polished and the cursor references the ? rst selected tuple. Selected tuples then can be urbane one tuple at a time using the dumbfound command catch into The bring command assigns the selected attribute values of the current tuple to the list of variables. later the come command, the cursor advances to the next tuple in the result set. feeling that the variables in the list must have the same data types as the selected values. After all tuples have been processed, the close command is used to change the cursor. close The example below illustrates how a cursor is used together with a perpetual loop declare cursor emp cur is select ? from EMP emp rec EMP%ROWTYPE emp sal EMP. SAL%TYPE demoralise open emp cur loop experience emp cur into emp rec depart when emp cur%NOT make up emp sal = emp rec. sal end loop close emp cur end 30Each loop can be blameless flatly using the dismission claus e belong when utilize exit without a block label causes the consummation of the loop that contains the exit statement. A condition can be a simple comparison of values. In most cases, however, the condition refers to a cursor. In the example above, %NOTFOUND is a predicate that evaluates to absurd if the most late play command has read a tuple. The value of %NOTFOUND is null onward the ? rst tuple is expressed. The predicate evaluates to true if the most new-fashioned fetch failed to return a tuple, and false otherwise. FOUND is the logical opposite of %NOTFOUND. Cursor for loops can be used to alter the rule of a cursor for in () loop end loop A record desirable to store a tuple fetched by the cursor is implicitly declared. Furthermore, this loop implicitly performs a fetch at each grommet as well as an open in advance the loop is entered and a close after the loop is left. If at an iteration no tuple has been fetched, the loop is automatically concluded without an exit. It is even possible to specify a query instead of in a for loop for in () loop end loop That is, a cursor needs not be speci? d before the loop is entered, but is de? ned in the select statement. Example for sal rec in (select SAL + COMM total from EMP) loop end loop total is an alias for the expression computed in the select statement. Thus, at each iteration only one tuple is fetched. The record sal rec, which is implicitly de? ned, then contains only one intromission which can be accessed using sal rec. total. Aliases, of course, are not needed if only attributes are selected, that is, if the select statement contains no arithmetic operators or aggregate functions. For qualified control, PL/SQL o? rs if-then-else