Oracle9i: Program with PL/SQL

529 trang hoanguyen 4420

Download

Bạn đang xem 20 trang mẫu của tài liệu "Oracle9i: Program with PL/SQL", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

oracle9i_program_with_plsql.pdf

Nội dung text: Oracle9i: Program with PL/SQL

Oracle9i: Program with PL/SQL Electronic Presentation 40054GC11 Production 1.1 October 2001 D34010
Authors Copyright © Oracle Corporation, 1999, 2000, 2001. All rights reserved. Nagavalli Pataballa This documentation contains proprietary information of Oracle Corporation. It is provided under a Priya Nathan license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Technical Contributors Restricted Rights Legend and Reviewers Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in Anna Atkinson subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software Bryan Roberts (October 1988). Caroline Pereda This material or any portion of it may not be copied in any formor by any means without the express Cesljas Zarco prior written permission of the Education Products group of Orac le Corporation. Any other copying is Coley William a violation of copyright law and may result in civil and/or criminal penalties. Daniel Gabel Dr. Christoph Burandt If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data- Hakan Lindfors General, including Alternate III (June 1987). Helen Robertson John Hoff The information in this document is subject to change without notice. If you find any problems in the Lachlan Williams documentation, please report them in writing to Worldwide Education Services, Oracle Corporation, 500Oracle Parkway, Box SB-6, Redwood Shores, CA 94065. Oracle Corporation does not warrant Laszlo Czinkoczki that this document is error-free. Laura Pezzini Linda Boldt Oracle and all references to Oracle Products are trademarks or registered trademarks of Oracle Marco Verbeek Corporation. Natarajan Senthil All other products or company names are used for identification purposes only, and may be Priya Vennapusa trademarks of their respective owners. Roger Abuzalaf Ruediger Steffan Sarah Jones Stefan Lindblad Susan Dee Publisher Sheryl Domingue
Languages Curriculum for Oracle9i Introduction to Oracle9Oracle9ii::SQLSQL forfor Oracle9i: SQL or Introduction to Oracle9i for EndEnd UsersUsers Introduction Oracle9i: Experienced SQL Users inClassinClass to Oracle9i: Advanced SQL Basics SQL inClass inClass Oracle9i: Program with PL/SQL Oracle9i: PL/SQL Oracle9Oracle9ii:: DevelopDevelop PL/SQLPL/SQL Fundamentals ProgramProgram UnitsUnits inClass Oracle9Oracle9ii::AdvancedAdvanced PL/SQLPL/SQL inClassinClass Copyright © Oracle Corporation, 2001. All rights reserved.
CourseCourse ObjectivesObjectives AfterAfter completingcompleting thisthis course,course, youyou shouldshould bebe ableable toto dodo thethe following:following: •• DescribeDescribe thethe purposepurpose ofof PL/SQLPL/SQL •• DescribeDescribe thethe useuse ofof PL/SQLPL/SQL forfor thethe developerdeveloper asas wellwell asas thethe DBADBA •• ExplainExplain thethe benefitsbenefits ofof PL/SQLPL/SQL •• Create,Create, execute,execute, andand maintainmaintain procedures,procedures, functions,functions, packages,packages, andand databasedatabase triggerstriggers •• ManageManage PL/SQLPL/SQL subprogramssubprograms andand triggerstriggers •• DescribeDescribe OracleOracle suppliedsupplied packagespackages •• ManipulateManipulate largelarge objectsobjects ((LOBsLOBs)) I-2 Copyright © Oracle Corporation, 2001. All rights reserved.
AboutAbout PL/SQLPL/SQL •• PL/SQLPL/SQL isis thethe proceduralprocedural extensionextension toto SQLSQL withwith designdesign featuresfeatures ofof programmingprogramming languages.languages. •• DataData manipulationmanipulation andand queryquery statementsstatements ofof SQLSQL areare includedincluded withinwithin proceduralprocedural unitsunits ofof code.code. I-3 Copyright © Oracle Corporation, 2001. All rights reserved.
PL/SQLPL/SQL EnvironmentEnvironment PL/SQL engine PL/SQL Procedural PL/SQL PL/SQL statement block block SQL executor SQL statement executor Oracle server I-4 Copyright © Oracle Corporation, 2001. All rights reserved.
BenefitsBenefits ofof PL/SQLPL/SQL ImprovedImproved performanceperformance SQL SQL Application Other DBMSs SQL SQL SQL IF THEN SQL Oracle with Application ELSE PL/SQL SQL END IF; SQL I-6 Copyright © Oracle Corporation, 2001. All rights reserved.
BenefitsBenefits ofof PL/SQLPL/SQL •• YouYou cancan programprogram withwith proceduralprocedural languagelanguage controlcontrol structures.structures. •• PL/SQLPL/SQL cancan handlehandle errors.errors. I-9 Copyright © Oracle Corporation, 2001. All rights reserved.
Invoking Stored Procedures and Functions Scott LOG_EXECUTION procedure 1 xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx 2 vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx 3 xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv Oracle Oracle Oracle vvvvvvvvvvvvvv xxxxxxxxxxxxxx Portal Discoverer Forms vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv Developer xxxxxxxxxxxxxx vvvvvvvvvvvvvv 4 Scott I-11 Copyright © Oracle Corporation, 2001. All rights reserved.
SummarySummary •• PL/SQLPL/SQL isis anan extensionextension toto SQL.SQL. •• BlocksBlocks ofof PL/SQLPL/SQL codecode areare passedpassed toto andand processedprocessed byby aa PL/SQLPL/SQL engine.engine. •• BenefitsBenefits ofof PL/SQL:PL/SQL: –– IntegrationIntegration –– ImprovedImproved performanceperformance –– PortabilityPortability –– ModularityModularity ofof programprogram developmentdevelopment •• SubprogramsSubprograms areare namednamed PL/SQLPL/SQL blocks,blocks, declareddeclared asas eithereither proceduresprocedures oror functions.functions. •• YouYou cancan invokeinvoke subprogramssubprograms fromfrom differentdifferent environments.environments. I-12 Copyright © Oracle Corporation, 2001. All rights reserved.
ObjectivesObjectives AfterAfter completingcompleting thisthis lesson,lesson, youyou shouldshould bebe ableable toto dodo thethe following:following: •• RecognizeRecognize thethe basicbasic PL/SQLPL/SQL blockblock andand itsits sectionssections •• DescribeDescribe thethe significancesignificance ofof variablesvariables inin PL/SQLPL/SQL •• DeclareDeclare PL/SQLPL/SQL variablesvariables •• ExecuteExecute aa PL/SQLPL/SQL blockblock 1-2 Copyright © Oracle Corporation, 2001. All rights reserved.
PL/SQLPL/SQL BlockBlock StructureStructure DECLARE (Optional) Variables, cursors, user-defined exceptions BEGIN (Mandatory) – SQL statements – PL/SQL statements EXCEPTION (Optional) Actions to perform when errors occur END; (Mandatory) DECLARE BEGIN EXCEPTION END; 1-3 Copyright © Oracle Corporation, 2001. All rights reserved.
ExecutingExecuting StatementsStatements andand PL/SQLPL/SQL BlocksBlocks DECLARE v_variable VARCHAR2(5); BEGIN SELECT column_name INTO v_variable FROM table_name; EXCEPTION WHEN exception_name THEN END; DECLARE BEGIN EXCEPTION END; 1-4 Copyright © Oracle Corporation, 2001. All rights reserved.
BlockBlock TypesTypes Anonymous Procedure Function [DECLARE] PROCEDURE name FUNCTION name IS RETURN datatype IS BEGIN BEGIN BEGIN statements statements statements RETURN value; [EXCEPTION] [EXCEPTION] [EXCEPTION] END; END; END; 1-5 Copyright © Oracle Corporation, 2001. All rights reserved.
ProgramProgram ConstructsConstructs DECLARE BEGIN EXCEPTION END; Database Server Tools Constructs Anonymous blocks Constructs Application procedures or Anonymous blocks functions Stored procedures or Application packages functions Application triggers Stored packages Object types Database triggers Object types 1-6 Copyright © Oracle Corporation, 2001. All rights reserved.
UseUse ofof VariablesVariables VariablesVariables cancan bebe usedused for:for: •• TemporaryTemporary storagestorage ofof datadata •• ManipulationManipulation ofof storedstored valuesvalues •• ReusabilityReusability •• EaseEase ofof maintenancemaintenance 1-7 Copyright © Oracle Corporation, 2001. All rights reserved.
HandlingHandling VariablesVariables inin PL/SQLPL/SQL •• DeclareDeclare andand initializeinitialize variablesvariables inin thethe declarationdeclaration section.section. •• AssignAssign newnew valuesvalues toto variablesvariables inin thethe executableexecutable section.section. •• PassPass valuesvalues intointo PL/SQLPL/SQL blocksblocks throughthrough parameters.parameters. •• ViewView resultsresults throughthrough outputoutput variables.variables. 1-8 Copyright © Oracle Corporation, 2001. All rights reserved.
TypesTypes ofof VariablesVariables •• PL/SQLPL/SQL variables:variables: –– ScalarScalar –– CompositeComposite –– ReferenceReference –– LOBLOB (large(large objects)objects) •• NonNon PL/SQLPL/SQL variables:variables: BindBind andand hosthost variablesvariables 1-9 Copyright © Oracle Corporation, 2001. All rights reserved.
UsingUsing iiSQL*PlusSQL*Plus VariablesVariables WithinWithin PL/SQLPL/SQL BlocksBlocks •• PL/SQLPL/SQL doesdoes notnot havehave inputinput oror outputoutput capabilitycapability ofof itsits own.own. •• YouYou cancan referencereference substitutionsubstitution variablesvariables withinwithin aa PL/SQLPL/SQL blockblock withwith aa precedingpreceding ampersand.ampersand. •• iiSQL*PlusSQL*Plus hosthost (or(or “bind”)“bind”) variablesvariables cancan bebe usedused toto passpass runrun timetime valuesvalues outout ofof thethe PL/SQLPL/SQL blockblock backback toto thethe iiSQL*PlusSQL*Plus environment.environment. 1-10 Copyright © Oracle Corporation, 2001. All rights reserved.
TypesTypes ofof VariablesVariables 2525 JANJAN 0101 TRUETRUE “Four score and seven years ago our fathers brought forth upon this continent, a new nation, conceived in LIBERTY, and dedicated 256120.08256120.08 to the proposition that all men are created equal.” AtlantaAtlanta 1-11 Copyright © Oracle Corporation, 2001. All rights reserved.
DeclaringDeclaring PL/SQLPL/SQL VariablesVariables Syntax:Syntax: identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; Examples:Examples: DECLARE v_hiredate DATE; v_deptno NUMBER(2) NOT NULL := 10; v_location VARCHAR2(13) := 'Atlanta'; c_comm CONSTANT NUMBER := 1400; 1-12 Copyright © Oracle Corporation, 2001. All rights reserved.
GuidelinesGuidelines forfor DeclaringDeclaring PL/SQLPL/SQL VariablesVariables •• FollowFollow namingnaming conventions.conventions. •• InitializeInitialize variablesvariables designateddesignated asas NOTNOT NULLNULL andand CONSTANTCONSTANT •• DeclareDeclare oneone identifieridentifier perper line.line. •• InitializeInitialize identifiersidentifiers byby usingusing thethe assignmentassignment operatoroperator ((:=:=)) oror thethe DEFAULTDEFAULTreservedreserved word.word. identifier := expr; 1-13 Copyright © Oracle Corporation, 2001. All rights reserved.
NamingNaming RulesRules •• TwoTwo variablesvariables cancan havehave thethe samesame name,name, providedprovided theythey areare inin differentdifferent blocks.blocks. •• TheThe variablevariable namename (identifier)(identifier) shouldshould notnot bebe thethe samesame asas thethe namename ofof tabletable columnscolumns usedused inin thethe block.block. DECLARE employee_id NUMBER(6); Adopt a naming BEGIN SELECT employee_id convention for INTO employee_id PL/SQL identifiers: FROM employees for example, WHERE last_name = 'Kochhar'; END; v_employee_id / 1-14 Copyright © Oracle Corporation, 2001. All rights reserved.
VariableVariable InitializationInitialization andand KeywordsKeywords •• AssignmentAssignment operatoroperator ((:=:=)) •• DEFAULTDEFAULTkeywordkeyword •• NOTNOT NULLNULL constraintconstraint Syntax:Syntax: identifier := expr; Examples:Examples: v_hiredate := '01-JAN-2001'; v_ename := 'Maduro'; 1-15 Copyright © Oracle Corporation, 2001. All rights reserved.
ScalarScalar DataData TypesTypes •• HoldHold aa singlesingle valuevalue •• HaveHave nono internalinternal componentscomponents 2525 OCTOCT 9999 “Four score and seven years ago our fathers broughtTRUETRUE forth upon this continent, a new nation, conceived in 256120.08256120.08 LIBERTY, and dedicated to the proposition that all men are created equal.”AtlantaAtlanta 1-17 Copyright © Oracle Corporation, 2001. All rights reserved.
BaseBase ScalarScalar DataData TypesTypes •• CHARCHAR [([(maximum_lengthmaximum_length)])] •• VARCHAR2VARCHAR2 ((maximum_lengthmaximum_length)) •• LONGLONG •• LONGLONG RAWRAW •• NUMBERNUMBER [([(precision,precision, scalescale)])] •• BINARY_INTEGERBINARY_INTEGER •• PLS_INTEGERPLS_INTEGER •• BOOLEANBOOLEAN 1-18 Copyright © Oracle Corporation, 2001. All rights reserved.
BaseBase ScalarScalar DataData TypesTypes •• DATEDATE •• TIMESTAMPTIMESTAMP •• TIMESTAMPTIMESTAMP WITHWITH TIMETIME ZONEZONE •• TIMESTAMPTIMESTAMP WITHWITH LOCALLOCAL TIMETIME ZONEZONE •• INTERVALINTERVAL YEARYEAR TOTO MONTHMONTH •• INTERVALINTERVAL DAYDAY TOTO SECONDSECOND 1-20 Copyright © Oracle Corporation, 2001. All rights reserved.
ScalarScalar VariableVariable DeclarationsDeclarations Examples:Examples: DECLARE v_job VARCHAR2(9); v_count BINARY_INTEGER := 0; v_total_sal NUMBER(9,2) := 0; v_orderdate DATE := SYSDATE + 7; c_tax_rate CONSTANT NUMBER(3,2) := 8.25; v_valid BOOLEAN NOT NULL := TRUE; 1-22 Copyright © Oracle Corporation, 2001. All rights reserved.
TheThe %TYPE%TYPE AttributeAttribute •• DeclareDeclare aa variablevariable accordingaccording to:to: –– AA databasedatabase columncolumn definitiondefinition –– AnotherAnother previouslypreviously declareddeclared variablevariable •• PrefixPrefix %TYPE%TYPEwith:with: –– TheThe databasedatabase tabletable andand columncolumn –– TheThe previouslypreviously declareddeclared variablevariable namename 1-23 Copyright © Oracle Corporation, 2001. All rights reserved.
DeclaringDeclaring VariablesVariables withwith thethe %TYPE%TYPE AttributeAttribute Syntax:Syntax: identifier Table.column_name%TYPE; Examples:Examples: v_name employees.last_name%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10; 1-24 Copyright © Oracle Corporation, 2001. All rights reserved.
DeclaringDeclaring BooleanBoolean VariablesVariables •• OnlyOnly thethe valuesvalues TRUETRUE,, FALSEFALSE,, andand NULLNULL cancan bebe assignedassigned toto aa BooleanBoolean variable.variable. •• TheThe variablesvariables areare comparedcompared byby thethe logicallogical operatorsoperators ANDAND,, OROR,, andand NOTNOT •• TheThe variablesvariables alwaysalways yieldyield TRUETRUE,, FALSEFALSE,, oror NULLNULL •• Arithmetic,Arithmetic, character,character, andand datedate expressionsexpressions cancan bebe usedused toto returnreturn aa BooleanBoolean value.value. 1-25 Copyright © Oracle Corporation, 2001. All rights reserved.
CompositeComposite DataData TypesTypes TRUE 23-DEC-98 ATLANTA PL/SQL table structure PL/SQL table structure 1 SMITH 1 5000 2 JONES 2 2345 3 NANCY 3 12 4 TIM 4 3456 VARCHAR2 NUMBER BINARY_INTEGER BINARY_INTEGER 1-26 Copyright © Oracle Corporation, 2001. All rights reserved.
UsingUsing BindBind VariablesVariables ToTo referencereference aa bindbind variablevariable inin PL/SQL,PL/SQL, youyou mustmust prefixprefix itsits namename withwith aa coloncolon (:).(:). Example:Example: VARIABLE g_salary NUMBER BEGIN SELECT salary INTO :g_salary FROM employees WHERE employee_id = 178; END; / PRINT g_salary 1-30 Copyright © Oracle Corporation, 2001. All rights reserved.
ReferencingReferencing NonNon PL/SQLPL/SQL VariablesVariables StoreStore thethe annualannual salarysalary intointo aa iiSQL*PlusSQL*Plus hosthost variable.variable. :g_monthly_sal := v_sal / 12; •• ReferenceReference nonnon PL/SQLPL/SQL variablesvariables asas hosthost variables.variables. •• PrefixPrefix thethe referencesreferences withwith aa coloncolon (:).(:). 1-31 Copyright © Oracle Corporation, 2001. All rights reserved.
DBMS_OUTPUT.PUT_LINEDBMS_OUTPUT.PUT_LINE •• AnAn OracleOracle suppliedsupplied packagedpackaged procedureprocedure •• AnAn alternativealternative forfor displayingdisplaying datadata fromfrom aa PL/SQLPL/SQL blockblock •• MustMust bebe enabledenabled inin iiSQL*PlusSQL*Plus withwith SETSET SERVEROUTPUTSERVEROUTPUT ONON SET SERVEROUTPUT ON DEFINE p_annual_sal = 60000 DECLARE v_sal NUMBER(9,2) := &p_annual_sal; BEGIN v_sal := v_sal/12; DBMS_OUTPUT.PUT_LINE ('The monthly salary is ' || TO_CHAR(v_sal)); END; / 1-32 Copyright © Oracle Corporation, 2001. All rights reserved.
SummarySummary InIn thisthis lessonlesson youyou shouldshould havehave learnedlearned that:that: •• PL/SQLPL/SQL blocksblocks areare composedcomposed ofof thethe followingfollowing sections:sections: –– DeclarativeDeclarative (optional)(optional) –– ExecutableExecutable (required)(required) –– ExceptionException handlinghandling (optional)(optional) DECLARE •• AA PL/SQLPL/SQL blockblock cancan bebe anan anonymousanonymous block,block, procedure,procedure, oror function.function. BEGIN EXCEPTION END; 1-33 Copyright © Oracle Corporation, 2001. All rights reserved.
SummarySummary InIn thisthis lessonlesson youyou shouldshould havehave learnedlearned that:that: •• PL/SQLPL/SQL identifiers:identifiers: –– AreAre defineddefined inin thethe declarativedeclarative sectionsection –– CanCan bebe ofof scalar,scalar, composite,composite, reference,reference, oror LOBLOBdatadata typetype –– CanCan bebe basedbased onon thethe structurestructure ofof anotheranother variablevariable oror databasedatabase objectobject –– CanCan bebe initializedinitialized •• VariablesVariables declareddeclared inin anan externalexternal environmentenvironment suchsuch asas iiSQL*PlusSQL*Plus areare calledcalled hosthost variables.variables. •• UseUse DBMS_OUTPUT.PUT_LINEDBMS_OUTPUT.PUT_LINE toto displaydisplay datadata fromfrom aa PL/SQLPL/SQL block.block. 1-34 Copyright © Oracle Corporation, 2001. All rights reserved.
PracticePractice 11 OverviewOverview ThisThis practicepractice coverscovers thethe followingfollowing topics:topics: •• DeterminingDetermining validityvalidity ofof declarationsdeclarations •• DeclaringDeclaring aa simplesimple PL/SQLPL/SQL blockblock •• ExecutingExecuting aa simplesimple PL/SQLPL/SQL blockblock 1-35 Copyright © Oracle Corporation, 2001. All rights reserved.
ObjectivesObjectives AfterAfter completingcompleting thisthis lesson,lesson, youyou shouldshould bebe ableable toto dodo thethe following:following: •• DescribeDescribe thethe significancesignificance ofof thethe executableexecutable sectionsection •• UseUse identifiersidentifiers correctlycorrectly •• WriteWrite statementsstatements inin thethe executableexecutable sectionsection •• DescribeDescribe thethe rulesrules ofof nestednested blocksblocks •• ExecuteExecute andand testtest aa PL/SQLPL/SQL blockblock •• UseUse codingcoding conventionsconventions 2-2 Copyright © Oracle Corporation, 2001. All rights reserved.
PL/SQL Block Syntax and Guidelines • Statements can continue over several lines. • Lexical units can be classified as: – Delimiters – Identifiers – Literals – Comments 2-3 Copyright © Oracle Corporation, 2001. All rights reserved.
IdentifiersIdentifiers •• CanCan containcontain upup toto 3030 characterscharacters •• MustMust beginbegin withwith anan alphabeticalphabetic charactercharacter •• CanCan containcontain numerals,numerals, dollardollar signs,signs, underscores,underscores, andand numbernumber signssigns •• CannotCannot containcontain characterscharacters suchsuch asas hyphens,hyphens, slashes,slashes, andand spacesspaces •• ShouldShould notnot havehave thethe samesame namename asas aa databasedatabase tabletable columncolumn namename •• ShouldShould notnot bebe reservedreserved wordswords 2-5 Copyright © Oracle Corporation, 2001. All rights reserved.
PL/SQLPL/SQL BlockBlock SyntaxSyntax andand GuidelinesGuidelines •• LiteralsLiterals –– CharacterCharacter andand datedate literalsliterals mustmust bebe enclosedenclosed inin singlesingle quotationquotation marks.marks. v_name := 'Henderson'; –– NumbersNumbers cancan bebe simplesimple valuesvalues oror scientificscientific notation.notation. •• AA slashslash (( // )) runsruns thethe PL/SQLPL/SQL blockblock inin aa scriptscript filefile oror inin somesome toolstools suchsuch asas iiSQL*PLUS.SQL*PLUS. 2-6 Copyright © Oracle Corporation, 2001. All rights reserved.
CommentingCommenting CodeCode •• PrefixPrefix singlesingle lineline commentscomments withwith twotwo dashesdashes (( ).). •• PlacePlace multiplemultiple lineline commentscomments betweenbetween thethe symbolssymbols /*/* andand */*/ Example:Example: DECLARE v_sal NUMBER (9,2); BEGIN /* Compute the annual salary based on the monthly salary input from the user */ v_sal := :g_monthly_sal * 12; END; This is the end of the block 2-7 Copyright © Oracle Corporation, 2001. All rights reserved.
SQLSQL FunctionsFunctions inin PL/SQLPL/SQL •• AvailableAvailable inin proceduralprocedural statements:statements: –– SingleSingle rowrow numbernumber –– SingleSingle rowrow charactercharacter – Data type conversion – Data type conversion Same as in SQL –– DateDate – Timestamp } – Timestamp } –– GREATESTGREATEST andand LEASTLEAST –– MiscellaneousMiscellaneous functionsfunctions •• NotNot availableavailable inin proceduralprocedural statements:statements: –– DECODEDECODE –– GroupGroup functionsfunctions 2-8 Copyright © Oracle Corporation, 2001. All rights reserved.
SQLSQL FunctionsFunctions inin PL/SQL:PL/SQL: ExamplesExamples •• BuildBuild thethe mailingmailing listlist forfor aa company.company. v_mailing_address := v_name||CHR(10)|| v_address||CHR(10)||v_state|| CHR(10)||v_zip; •• ConvertConvert thethe employeeemployee namename toto lowercase.lowercase. v_ename := LOWER(v_ename); 2-9 Copyright © Oracle Corporation, 2001. All rights reserved.
DataData TypeType ConversionConversion •• ConvertConvert datadata toto comparablecomparable datadata types.types. •• MixedMixed datadata typestypes cancan resultresult inin anan errorerror andand affectaffect performance.performance. •• ConversionConversion functions:functions: –– TO_CHARTO_CHAR –– TO_DATETO_DATE –– TO_NUMBERTO_NUMBER DECLARE v_date DATE := TO_DATE('12-JAN-2001', 'DD-MON-YYYY'); BEGIN . . . 2-10 Copyright © Oracle Corporation, 2001. All rights reserved.
DataData TypeType ConversionConversion ThisThis statementstatement producesproduces aa compilationcompilation errorerror ifif thethe variablevariable v_datev_dateisis declareddeclared asas aa DATEDATEdatadata type.type. v_date := 'January 13, 2001'; 2-11 Copyright © Oracle Corporation, 2001. All rights reserved.
DataData TypeType ConversionConversion ToTo correctcorrect thethe error,error, useuse thethe TO_DATETO_DATEconversionconversion function.function. v_date := TO_DATE ('January 13, 2001', 'Month DD, YYYY'); 2-12 Copyright © Oracle Corporation, 2001. All rights reserved.
NestedNested BlocksBlocks andand VariableVariable ScopeScope •• PL/SQLPL/SQL blocksblocks cancan bebe nestednested whereverwherever anan executableexecutable statementstatement isis allowed.allowed. •• AA nestednested blockblock becomesbecomes aa statement.statement. •• AnAn exceptionexception sectionsection cancan containcontain nestednested blocks.blocks. •• TheThe scopescope ofof anan identifieridentifier isis thatthat regionregion ofof aa programprogram unitunit (block,(block, subprogram,subprogram, oror package)package) fromfrom whichwhich youyou cancan referencereference thethe identifier.identifier. 2-13 Copyright © Oracle Corporation, 2001. All rights reserved.
NestedNested BlocksBlocks andand VariableVariable ScopeScope Example:Example: x BINARY_INTEGER; BEGIN Scope of x DECLARE y NUMBER; BEGIN Scope of y y:= x; END; END; 2-14 Copyright © Oracle Corporation, 2001. All rights reserved.
IdentifierIdentifier ScopeScope AnAn identifieridentifier isis visiblevisible inin thethe regionsregions wherewhere youyou cancan referencereference thethe identifieridentifier withoutwithout havinghaving toto qualifyqualify it:it: •• AA blockblock cancan looklook upup toto thethe enclosingenclosing block.block. •• AA blockblock cannotcannot looklook downdown toto enclosedenclosed blocks.blocks. 2-15 Copyright © Oracle Corporation, 2001. All rights reserved.
QualifyQualify anan IdentifierIdentifier •• TheThe qualifierqualifier cancan bebe thethe labellabel ofof anan enclosingenclosing block.block. •• QualifyQualify anan identifieridentifier byby usingusing thethe blockblock labellabel prefix.prefix. > DECLARE birthdate DATE; BEGIN DECLARE birthdate DATE; BEGIN outer.birthdate := TO_DATE('03-AUG-1976', 'DD-MON-YYYY'); END; END; 2-16 Copyright © Oracle Corporation, 2001. All rights reserved.
DeterminingDetermining VariableVariable ScopeScope ClassClass ExerciseExercise > DECLARE v_sal NUMBER(7,2) := 60000; v_comm NUMBER(7,2) := v_sal * 0.20; v_message VARCHAR2(255) := ' eligible for commission'; BEGIN DECLARE v_sal NUMBER(7,2) := 50000; v_comm NUMBER(7,2) := 0; v_total_comp NUMBER(7,2) := v_sal + v_comm; BEGIN v_message := 'CLERK not'||v_message; outer.v_comm := v_sal * 0.30; 1 END; v_message := 'SALESMAN'||v_message; 2 END; 2-17 Copyright © Oracle Corporation, 2001. All rights reserved.
OperatorsOperators inin PL/SQLPL/SQL •• LogicalLogical •• ArithmeticArithmetic •• ConcatenationConcatenation Same as in SQL •• ParenthesesParentheses toto controlcontrol orderorder ofof operationsoperations }} •• ExponentialExponential operatoroperator (( )) 2-18 Copyright © Oracle Corporation, 2001. All rights reserved.
OperatorsOperators inin PL/SQLPL/SQL Examples:Examples: •• IncrementIncrement thethe countercounter forfor aa loop.loop. v_count := v_count + 1; •• SetSet thethe valuevalue ofof aa BooleanBoolean flag.flag. v_equal := (v_n1 = v_n2); •• ValidateValidate whetherwhether anan employeeemployee numbernumber containscontains aa value.value. v_valid := (v_empno IS NOT NULL); 2-19 Copyright © Oracle Corporation, 2001. All rights reserved.
ProgrammingProgramming GuidelinesGuidelines MakeMake codecode maintenancemaintenance easiereasier by:by: •• DocumentingDocumenting codecode withwith commentscomments •• DevelopingDeveloping aa casecase conventionconvention forfor thethe codecode •• DevelopingDeveloping namingnaming conventionsconventions forfor identifiersidentifiers andand otherother objectsobjects •• EnhancingEnhancing readabilityreadability byby indentingindenting 2-20 Copyright © Oracle Corporation, 2001. All rights reserved.
IndentingIndenting CodeCode ForFor clarity,clarity, indentindent eacheach levellevel ofof code.code. Example:Example: DECLARE v_deptno NUMBER(4); BEGIN v_location_id NUMBER(4); IF x=0 THEN BEGIN y:=1; SELECT department_id, END IF; location_id END; INTO v_deptno, v_location_id FROM departments WHERE department_name = 'Sales'; END; / 2-21 Copyright © Oracle Corporation, 2001. All rights reserved.
SummarySummary InIn thisthis lessonlesson youyou shouldshould havehave learnedlearned that:that: •• PL/SQLPL/SQL blockblock syntaxsyntax andand guidelinesguidelines •• HowHow toto useuse identifiersidentifiers correctlycorrectly •• PL/SQLPL/SQL blockblock structure:structure: nestingnesting blocksblocks andand scopingscoping rulesrules •• PL/SQLPL/SQL programming:programming: DECLARE –– FunctionsFunctions BEGIN –– DataData typetype conversionsconversions –– OperatorsOperators EXCEPTION –– ConventionsConventions andand guidelinesguidelines END; 2-22 Copyright © Oracle Corporation, 2001. All rights reserved.
PracticePractice 22 OverviewOverview ThisThis practicepractice coverscovers thethe followingfollowing topics:topics: •• ReviewingReviewing scopingscoping andand nestingnesting rulesrules •• DevelopingDeveloping andand testingtesting PL/SQLPL/SQL blocksblocks 2-23 Copyright © Oracle Corporation, 2001. All rights reserved.
ObjectivesObjectives AfterAfter completingcompleting thisthis lesson,lesson, youyou shouldshould bebe ableable toto dodo thethe following:following: •• WriteWrite aa successfulsuccessful SELECTSELECTstatementstatement inin PL/SQLPL/SQL •• WriteWrite DMLDML statementsstatements inin PL/SQLPL/SQL •• ControlControl transactionstransactions inin PL/SQLPL/SQL •• DetermineDetermine thethe outcomeoutcome ofof SQLSQL datadata manipulationmanipulation languagelanguage (DML)(DML) statementsstatements 3-2 Copyright © Oracle Corporation, 2001. All rights reserved.
SQLSQL StatementsStatements inin PL/SQLPL/SQL •• ExtractExtract aa rowrow ofof datadata fromfrom thethe databasedatabase byby usingusing thethe SELECTSELECT command.command. •• MakeMake changeschanges toto rowsrows inin thethe databasedatabase byby usingusing DMLDML commands.commands. •• ControlControl aa transactiontransaction withwith thethe COMMITCOMMIT,, ROLLBACKROLLBACK,, oror SAVEPOINTSAVEPOINT command.command. •• DetermineDetermine DMLDML outcomeoutcome withwith implicitimplicit cursorcursor attributes.attributes. 3-3 Copyright © Oracle Corporation, 2001. All rights reserved.
SELECTSELECT StatementsStatements inin PL/SQLPL/SQL RetrieveRetrieve datadata fromfrom thethe databasedatabase withwith aa SELECTSELECT statement.statement. Syntax:Syntax: SELECT select_list INTO {variable_name[, variable_name] | record_name} FROM table [WHERE condition]; 3-4 Copyright © Oracle Corporation, 2001. All rights reserved.
SELECTSELECT StatementsStatements inin PL/SQLPL/SQL •• TheThe INTOINTO clauseclause isis required.required. •• QueriesQueries mustmust returnreturn oneone andand onlyonly oneone row.row. Example:Example: DECLARE v_deptno NUMBER(4); v_location_id NUMBER(4); BEGIN SELECT department_id, location_id INTO v_deptno, v_location_id FROM departments WHERE department_name = 'Sales'; END; / 3-6 Copyright © Oracle Corporation, 2001. All rights reserved.
Retrieving Data in PL/SQL Retrieve the hire date and the salary for the specified employee. Example: DECLARE v_hire_date employees.hire_date%TYPE; v_salary employees.salary%TYPE; BEGIN SELECT hire_date, salary INTO v_hire_date, v_salary FROM employees WHERE employee_id = 100; END; / 3-7 Copyright © Oracle Corporation, 2001. All rights reserved.
RetrievingRetrieving DataData inin PL/SQLPL/SQL ReturnReturn thethe sumsum ofof thethe salariessalaries forfor allall employeesemployees inin thethe specifiedspecified department.department. Example:Example: SET SERVEROUTPUT ON DECLARE v_sum_sal NUMBER(10,2); v_deptno NUMBER NOT NULL := 60; BEGIN SELECT SUM(salary) group function INTO v_sum_sal FROM employees WHERE department_id = v_deptno; DBMS_OUTPUT.PUT_LINE ('The sum salary is ' || TO_CHAR(v_sum_sal)); END; / 3-8 Copyright © Oracle Corporation, 2001. All rights reserved.
NamingNaming ConventionsConventions DECLARE hire_date employees.hire_date%TYPE; sysdate hire_date%TYPE; employee_id employees.employee_id%TYPE := 176; BEGIN SELECT hire_date, sysdate INTO hire_date, sysdate FROM employees WHERE employee_id = employee_id; END; / 3-9 Copyright © Oracle Corporation, 2001. All rights reserved.
ManipulatingManipulating DataData UsingUsing PL/SQLPL/SQL MakeMake changeschanges toto databasedatabase tablestables byby usingusing DMLDML commands:commands: •• INSERTINSERT •• UPDATEUPDATE INSERT •• DELETEDELETE •• MERGEMERGE MERGE UPDATE DELETE 3-10 Copyright © Oracle Corporation, 2001. All rights reserved.
InsertingInserting DataData AddAdd newnew employeeemployee informationinformation toto thethe EMPLOYEESEMPLOYEES table.table. Example:Example: BEGIN INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (employees_seq.NEXTVAL, 'Ruth', 'Cores', 'RCORES', sysdate, 'AD_ASST', 4000); END; / 3-11 Copyright © Oracle Corporation, 2001. All rights reserved.
UpdatingUpdating DataData IncreaseIncrease thethe salarysalary ofof allall employeesemployees whowho areare stockstock clerks.clerks. Example:Example: DECLARE v_sal_increase employees.salary%TYPE := 800; BEGIN UPDATE employees SET salary = salary + v_sal_increase WHERE job_id = 'ST_CLERK'; END; / 3-12 Copyright © Oracle Corporation, 2001. All rights reserved.
DeletingDeleting DataData DeleteDelete rowsrows thatthat belongbelong toto departmentdepartment 1010 fromfrom thethe EMPLOYEESEMPLOYEEStable.table. Example:Example: DECLARE v_deptno employees.department_id%TYPE := 10; BEGIN DELETE FROM employees WHERE department_id = v_deptno; END; / 3-13 Copyright © Oracle Corporation, 2001. All rights reserved.
Merging Rows Insert or update rows in the COPY_EMP table to match the EMPLOYEES table. DECLARE v_empno employees.employee_id%TYPE := 100; BEGIN MERGE INTO copy_emp c USING employees e ON (e.employee_id = v_empno) WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, c.email = e.email, . . . WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name, . . .,e.department_id); END; 3-14 Copyright © Oracle Corporation, 2001. All rights reserved.
NamingNaming ConventionsConventions •• UseUse aa namingnaming conventionconvention toto avoidavoid ambiguityambiguity inin thethe WHEREWHERE clause.clause. •• DatabaseDatabase columnscolumns andand identifiersidentifiers shouldshould havehave distinctdistinct names.names. •• SyntaxSyntax errorserrors cancan arisearise becausebecause PL/SQLPL/SQL checkschecks thethe databasedatabase firstfirst forfor aa columncolumn inin thethe table.table. •• TheThe namesnames ofof locallocal variablesvariables andand formalformal parametersparameters taketake precedenceprecedence overover thethe namesnames ofof databasedatabase tables.tables. •• TheThe namesnames ofof databasedatabase tabletable columnscolumns taketake precedenceprecedence overover thethe namesnames ofof locallocal variables.variables. 3-16 Copyright © Oracle Corporation, 2001. All rights reserved.
SQLSQL CursorCursor •• AA cursorcursor isis aa privateprivate SQLSQL workwork area.area. •• ThereThere areare twotwo typestypes ofof cursors:cursors: –– ImplicitImplicit cursorscursors –– ExplicitExplicit cursorscursors •• TheThe OracleOracle serverserver usesuses implicitimplicit cursorscursors toto parseparse andand executeexecute youryour SQLSQL statements.statements. •• ExplicitExplicit cursorscursors areare explicitlyexplicitly declareddeclared byby thethe programmer.programmer. 3-18 Copyright © Oracle Corporation, 2001. All rights reserved.
SQLSQL CursorCursor AttributesAttributes UsingUsing SQLSQL cursorcursor attributes,attributes, youyou cancan testtest thethe outcomeoutcome ofof youryour SQLSQL statements.statements. SQL%ROWCOUNT Number of rows affected by the most recent SQL statement (an integer value) SQL%FOUND Boolean attribute that evaluates to TRUE if the most recent SQL statement affects one or more rows SQL%NOTFOUND Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any rows SQL%ISOPEN Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed 3-19 Copyright © Oracle Corporation, 2001. All rights reserved.
SQLSQL CursorCursor AttributesAttributes DeleteDelete rowsrows thatthat havehave thethe specifiedspecified employeeemployee IDID fromfrom thethe EMPLOYEESEMPLOYEES table.table. PrintPrint thethe numbernumber ofof rowsrows deleted.deleted. Example:Example: VARIABLE rows_deleted VARCHAR2(30) DECLARE v_employee_id employees.employee_id%TYPE := 176; BEGIN DELETE FROM employees WHERE employee_id = v_employee_id; :rows_deleted := (SQL%ROWCOUNT || ' row deleted.'); END; / PRINT rows_deleted 3-20 Copyright © Oracle Corporation, 2001. All rights reserved.
TransactionTransaction ControlControl StatementsStatements •• InitiateInitiate aa transactiontransaction withwith thethe firstfirst DMLDML commandcommand toto followfollow aa COMMITCOMMIToror ROLLBACKROLLBACK •• UseUse COMMITCOMMITandand ROLLBACKROLLBACK SQLSQL statementsstatements toto terminateterminate aa transactiontransaction explicitly.explicitly. 3-21 Copyright © Oracle Corporation, 2001. All rights reserved.
SummarySummary InIn thisthis lessonlesson youyou shouldshould havehave learnedlearned howhow to:to: •• EmbedEmbed SQLSQL inin thethe PL/SQLPL/SQL blockblock usingusing SELECTSELECT,, INSERTINSERT,, UPDATEUPDATE,, DELETEDELETE,, andand MERGEMERGE •• EmbedEmbed transactiontransaction controlcontrol statementsstatements inin aa PL/SQLPL/SQL blockblock COMMITCOMMIT,, ROLLBACKROLLBACK,, andand SAVEPOINTSAVEPOINT 3-22 Copyright © Oracle Corporation, 2001. All rights reserved.
SummarySummary InIn thisthis lessonlesson youyou shouldshould havehave learnedlearned that:that: •• ThereThere areare twotwo cursorcursor types:types: implicitimplicit andand explicit.explicit. •• ImplicitImplicit cursorcursor attributesattributes areare usedused toto verifyverify thethe outcomeoutcome ofof DMLDML statements:statements: –– SQL%ROWCOUNTSQL%ROWCOUNT –– SQL%FOUNDSQL%FOUND –– SQL%NOTFOUNDSQL%NOTFOUND –– SQL%ISOPENSQL%ISOPEN •• ExplicitExplicit cursorscursors areare defineddefined byby thethe programmer.programmer. 3-23 Copyright © Oracle Corporation, 2001. All rights reserved.
PracticePractice 33 OverviewOverview ThisThis practicepractice coverscovers creatingcreating aa PL/SQLPL/SQL blockblock to:to: •• SelectSelect datadata fromfrom aa tabletable •• InsertInsert datadata intointo aa tabletable •• UpdateUpdate datadata inin aa tabletable •• DeleteDelete aa recordrecord fromfrom aa tabletable 3-24 Copyright © Oracle Corporation, 2001. All rights reserved.
Objectives After completing this lesson, you should be able to do the following: • Identify the uses and types of control structures • Construct an IF statement • Use CASE expressions • Construct and identify different loop statements • Use logic tables • Control block flow using nested loops and labels 4-2 Copyright © Oracle Corporation, 2001. All rights reserved.
Controlling PL/SQL Flow of Execution • You can change the logical execution of statements using conditional IF statements and loop control structures. • Conditional IF statements: – IF-THEN-END IF – IF-THEN-ELSE-END IF – IF-THEN-ELSIF-END IF 4-3 Copyright © Oracle Corporation, 2001. All rights reserved.
IF Statements Syntax:Syntax: IF condition THEN statements; [ELSIF condition THEN statements;] [ELSE statements;] END IF; IfIf thethe employeeemployee namename isis Gietz,Gietz, setset thethe ManagerManager IDID toto 102.102. IF UPPER(v_last_name) = 'GIETZ' THEN v_mgr := 102; END IF; 4-4 Copyright © Oracle Corporation, 2001. All rights reserved.
Simple IF Statements If the last name is Vargas: • Set job ID to SA_REP • Set department number to 80 . . . IF v_ename = 'Vargas' THEN v_job := 'SA_REP'; v_deptno := 80; END IF; . . . 4-5 Copyright © Oracle Corporation, 2001. All rights reserved.
Compound IF Statements If the last name is Vargas and the salary is more than 6500: Set department number to 60. . . . IF v_ename = 'Vargas' AND salary > 6500 THEN v_deptno := 60; END IF; . . . 4-6 Copyright © Oracle Corporation, 2001. All rights reserved.
IF-THEN-ELSE Statement Execution Flow TRUE NOT TRUE IF condition THEN actions ELSE actions (including further IF (including further IF statements) statements) 4-7 Copyright © Oracle Corporation, 2001. All rights reserved.
IF-THEN-ELSE Statements Set a Boolean flag to TRUE if the hire date is greater than five years; otherwise, set the Boolean flag to FALSE. DECLARE v_hire_date DATE := '12-Dec-1990'; v_five_years BOOLEAN; BEGIN . . . IF MONTHS_BETWEEN(SYSDATE,v_hire_date)/12 > 5 THEN v_five_years := TRUE; ELSE v_five_years := FALSE; END IF; 4-8 Copyright © Oracle Corporation, 2001. All rights reserved.
IF-THEN-ELSIF Statements For a given value, calculate a percentage of that value based on a condition. Example: . . . IF v_start > 100 THEN v_start := 0.2 * v_start; ELSIF v_start >= 50 THEN v_start := 0.5 * v_start; ELSE v_start := 0.1 * v_start; END IF; . . . 4-11 Copyright © Oracle Corporation, 2001. All rights reserved.
CASE Expressions • A CASE expression selects a result and returns it. • To select the result, the CASE expression uses an expression whose value is used to select one of several alternatives. CASE selector WHEN expression1 THEN result1 WHEN expression2 THEN result2 WHEN expressionN THEN resultN [ELSE resultN+1;] END; 4-12 Copyright © Oracle Corporation, 2001. All rights reserved.
CASE Expressions: Example SET SERVEROUTPUT ON DECLARE v_grade CHAR(1) := UPPER('&p_grade'); v_appraisal VARCHAR2(20); BEGIN v_appraisal := CASE v_grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' ELSE 'No such grade' END; DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade || ' Appraisal ' || v_appraisal); END; / 4-13 Copyright © Oracle Corporation, 2001. All rights reserved.
Handling Nulls When working with nulls, you can avoid some common mistakes by keeping in mind the following rules: • Simple comparisons involving nulls always yield NULL. • Applying the logical operator NOT to a null yields NULL. • In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed. 4-15 Copyright © Oracle Corporation, 2001. All rights reserved.
Logic Tables Build a simple Boolean condition with a comparison operator. AND TRUE FALSE NULL OR TRUE FALSE NULL NOT TRUE TRUE FALSE NULL TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE NULL FALSE TRUE NULL NULL FALSE NULL NULL TRUE NULL NULL NULL NULL 4-16 Copyright © Oracle Corporation, 2001. All rights reserved.
Boolean Conditions What is the value of V_FLAG in each case? v_flag := v_reorder_flag AND v_available_flag; V_REORDER_FLAG V_AVAILABLE_FLAG V_FLAG TRUE TRUE ? TRUE FALSE ? NULL TRUE ? NULL FALSE ? 4-17 Copyright © Oracle Corporation, 2001. All rights reserved.
Iterative Control: LOOP Statements • Loops repeat a statement or sequence of statements multiple times. • There are three loop types: – Basic loop – FOR loop – WHILE loop 4-18 Copyright © Oracle Corporation, 2001. All rights reserved.
Basic Loops Syntax: LOOP delimiter statement1; statements . . . EXIT [WHEN condition]; EXIT statement END LOOP; delimiter condition is a Boolean variable or expression (TRUE, FALSE, or NULL); 4-19 Copyright © Oracle Corporation, 2001. All rights reserved.
Basic Loops Example: DECLARE v_country_id locations.country_id%TYPE := 'CA'; v_location_id locations.location_id%TYPE; v_counter NUMBER(2) := 1; v_city locations.city%TYPE := 'Montreal'; BEGIN SELECT MAX(location_id) INTO v_location_id FROM locations WHERE country_id = v_country_id; LOOP INSERT INTO locations(location_id, city, country_id) VALUES((v_location_id + v_counter),v_city, v_country_id); v_counter := v_counter + 1; EXIT WHEN v_counter > 3; END LOOP; END; / 4-20 Copyright © Oracle Corporation, 2001. All rights reserved.
WHILE Loops Syntax: WHILE condition LOOP Condition is statement1; evaluated at the statement2; beginning of . . . each iteration. END LOOP; Use the WHILE loop to repeat statements while a condition is TRUE. 4-21 Copyright © Oracle Corporation, 2001. All rights reserved.
WHILE Loops Example: DECLARE v_country_id locations.country_id%TYPE := 'CA'; v_location_id locations.location_id%TYPE; v_city locations.city%TYPE := 'Montreal'; v_counter NUMBER := 1; BEGIN SELECT MAX(location_id) INTO v_location_id FROM locations WHERE country_id = v_country_id; WHILE v_counter <= 3 LOOP INSERT INTO locations(location_id, city, country_id) VALUES((v_location_id + v_counter), v_city, v_country_id); v_counter := v_counter + 1; END LOOP; END; / 4-22 Copyright © Oracle Corporation, 2001. All rights reserved.
FOR Loops Syntax: FOR counter IN [REVERSE] lower_bound upper_bound LOOP statement1; statement2; . . . END LOOP; • Use a FOR loop to shortcut the test for the number of iterations. • Do not declare the counter; it is declared implicitly. • 'lower_bound upper_bound' is required syntax. 4-23 Copyright © Oracle Corporation, 2001. All rights reserved.
FOR Loops Insert three new locations IDs for the country code of CA and the city of Montreal. DECLARE v_country_id locations.country_id%TYPE := 'CA'; v_location_id locations.location_id%TYPE; v_city locations.city%TYPE := 'Montreal'; BEGIN SELECT MAX(location_id) INTO v_location_id FROM locations WHERE country_id = v_country_id; FOR i IN 1 3 LOOP INSERT INTO locations(location_id, city, country_id) VALUES((v_location_id + i), v_city, v_country_id ); END LOOP; END; / 4-24 Copyright © Oracle Corporation, 2001. All rights reserved.
FOR Loops Guidelines • Reference the counter within the loop only; it is undefined outside the loop. • Do not reference the counter as the target of an assignment. 4-25 Copyright © Oracle Corporation, 2001. All rights reserved.
Guidelines While Using Loops • Use the basic loop when the statements inside the loop must execute at least once. • Use the WHILE loop if the condition has to be evaluated at the start of each iteration. • Use a FOR loop if the number of iterations is known. 4-26 Copyright © Oracle Corporation, 2001. All rights reserved.
Nested Loops and Labels • Nest loops to multiple levels. • Use labels to distinguish between blocks and loops. • Exit the outer loop with the EXIT statement that references the label. 4-27 Copyright © Oracle Corporation, 2001. All rights reserved.
Nested Loops and Labels BEGIN > LOOP v_counter := v_counter+1; EXIT WHEN v_counter>10; > LOOP EXIT Outer_loop WHEN total_done = 'YES'; Leave both loops EXIT WHEN inner_done = 'YES'; Leave inner loop only END LOOP Inner_loop; END LOOP Outer_loop; END; 4-28 Copyright © Oracle Corporation, 2001. All rights reserved.
Summary In this lesson you should have learned to: Change the logical flow of statements by using control structures. • Conditional (IF statement) • CASE Expressions • Loops: – Basic loop – FOR loop – WHILE loop • EXIT statements 4-29 Copyright © Oracle Corporation, 2001. All rights reserved.
Practice 4 Overview This practice covers the following topics: • Performing conditional actions using the IF statement • Performing iterative steps using the loop structure 4-30 Copyright © Oracle Corporation, 2001. All rights reserved.
Objectives After completing this lesson, you should be able to do the following: • Create user-defined PL/SQL records • Create a record with the %ROWTYPE attribute • Create an INDEX BY table • Create an INDEX BY table of records • Describe the difference between records, tables, and tables of records 5-2 Copyright © Oracle Corporation, 2001. All rights reserved.
Composite Data Types • Are of two types: – PL/SQL RECORDs – PL/SQL Collections – INDEX BY Table – Nested Table – VARRAY • Contain internal components • Are reusable 5-3 Copyright © Oracle Corporation, 2001. All rights reserved.
PL/SQL Records • Must contain one or more components of any scalar, RECORD, or INDEX BY table data type, called fields • Are similar in structure to records in a third generation language (3GL) • Are not the same as rows in a database table • Treat a collection of fields as a logical unit • Are convenient for fetching a row of data from a table for processing 5-4 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating a PL/SQL Record Syntax: TYPE type_name IS RECORD (field_declaration[, field_declaration] ); identifier type_name; Where field_declaration is: field_name {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE} [[NOT NULL] {:= | DEFAULT} expr] 5-5 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating a PL/SQL Record Declare variables to store the name, job, and salary of a new employee. Example: TYPE emp_record_type IS RECORD (last_name VARCHAR2(25), job_id VARCHAR2(10), salary NUMBER(8,2)); emp_record emp_record_type; 5-6 Copyright © Oracle Corporation, 2001. All rights reserved.
PL/SQL Record Structure Field1 (data type) Field2 (data type) Field3 (data type) Example:Example: Field1 (data type) Field2 (data type) Field3 (data type) employee_id number(6) last_name varchar2(25) job_id varchar2(10) 100 King AD_PRES 5-7 Copyright © Oracle Corporation, 2001. All rights reserved.
The %ROWTYPE Attribute • Declare a variable according to a collection of columns in a database table or view. • Prefix %ROWTYPE with the database table. • Fields in the record take their names and data types from the columns of the table or view. 5-8 Copyright © Oracle Corporation, 2001. All rights reserved.
Advantages of Using %ROWTYPE • The number and data types of the underlying database columns need not be known. • The number and data types of the underlying database column may change at run time. • The attribute is useful when retrieving a row with the SELECT * statement. 5-10 Copyright © Oracle Corporation, 2001. All rights reserved.
The %ROWTYPE Attribute Examples: Declare a variable to store the information about a department from the DEPARTMENTS table. dept_record departments%ROWTYPE; Declare a variable to store the information about an employee from the EMPLOYEES table. emp_record employees%ROWTYPE; 5-11 Copyright © Oracle Corporation, 2001. All rights reserved.
INDEX BY Tables • Are composed of two components: – Primary key of data type BINARY_INTEGER – Column of scalar or record data type • Can increase in size dynamically because they are unconstrained 5-13 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating an INDEX BY Table Syntax: TYPE type_name IS TABLE OF {column_type | variable%TYPE | table.column%TYPE} [NOT NULL] | table.%ROWTYPE [INDEX BY BINARY_INTEGER]; identifier type_name; Declare an INDEX BY table to store names. Example: TYPE ename_table_type IS TABLE OF employees.last_name%TYPE INDEX BY BINARY_INTEGER; ename_table ename_table_type; 5-14 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating an INDEX BY Table DECLARE TYPE ename_table_type IS TABLE OF employees.last_name%TYPE INDEX BY BINARY_INTEGER; TYPE hiredate_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER; ename_table ename_table_type; hiredate_table hiredate_table_type; BEGIN ename_table(1) := 'CAMERON'; hiredate_table(8) := SYSDATE + 7; IF ename_table.EXISTS(1) THEN INSERT INTO END; / 5-16 Copyright © Oracle Corporation, 2001. All rights reserved.
Using INDEX BY Table Methods The following methods make INDEX BY tables easier to use: – EXISTS – NEXT – COUNT – TRIM – FIRST and LAST – DELETE – PRIOR 5-17 Copyright © Oracle Corporation, 2001. All rights reserved.
INDEX BY Table of Records •• DefineDefine aa TABLETABLEvariablevariable withwith aa permittedpermitted PL/SQLPL/SQL datadata type.type. •• DeclareDeclare aa PL/SQLPL/SQL variablevariable toto holdhold departmentdepartment information.information. Example:Example: DECLARE TYPE dept_table_type IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER; dept_table dept_table_type; Each element of dept_table is a record 5-18 Copyright © Oracle Corporation, 2001. All rights reserved.
Example of INDEX BY Table of Records SET SERVEROUTPUT ON DECLARE TYPE emp_table_type is table of employees%ROWTYPE INDEX BY BINARY_INTEGER; my_emp_table emp_table_type; v_count NUMBER(3):= 104; BEGIN FOR i IN 100 v_count LOOP SELECT * INTO my_emp_table(i) FROM employees WHERE employee_id = i; END LOOP; FOR i IN my_emp_table.FIRST my_emp_table.LAST LOOP DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name); END LOOP; END; 5-19 Copyright © Oracle Corporation, 2001. All rights reserved.
Summary In this lesson, you should have learned to: • Define and reference PL/SQL variables of composite data types: – PL/SQL records – INDEX BY tables – INDEX BY table of records • Define a PL/SQL record by using the %ROWTYPE attribute 5-20 Copyright © Oracle Corporation, 2001. All rights reserved.
Practice 5 Overview This practice covers the following topics: • Declaring INDEX BY tables • Processing data by using INDEX BY tables • Declaring a PL/SQL record • Processing data by using a PL/SQL record 5-21 Copyright © Oracle Corporation, 2001. All rights reserved.
ObjectivesObjectives AfterAfter completingcompleting thisthis lesson,lesson, youyou shouldshould bebe ableable toto dodo thethe following:following: •• DistinguishDistinguish betweenbetween anan implicitimplicit andand anan explicitexplicit cursorcursor •• DiscussDiscuss whenwhen andand whywhy toto useuse anan explicitexplicit cursorcursor •• UseUse aa PL/SQLPL/SQL recordrecord variablevariable •• WriteWrite aa cursorcursor FORFOR looploop 6-2 Copyright © Oracle Corporation, 2001. All rights reserved.
About Cursors Every SQL statement executed by the Oracle Server has an individual cursor associated with it: • Implicit cursors: Declared for all DML and PL/SQL SELECT statements • Explicit cursors: Declared and named by the programmer 6-3 Copyright © Oracle Corporation, 2001. All rights reserved.
ExplicitExplicit CursorCursor FunctionsFunctions Table 100 King AD_PRES 101 Kochhar AD_VP Active set 102 De Haan AD_VP Cursor . . . . . . . . . 139 Seo ST_CLERK 140 Patel ST_CLERK . . . 6-4 Copyright © Oracle Corporation, 2001. All rights reserved.
ControllingControlling ExplicitExplicit CursorsCursors No Yes DECLARE OPEN FETCH EMPTY? CLOSE • Create a • Identify • Load the • Test for • Release named the active current existing the active SQL area set row into rows set variables • Return to FETCH if rows are found 6-5 Copyright © Oracle Corporation, 2001. All rights reserved.
ControllingControlling ExplicitExplicit CursorsCursors 1. Open the cursor 2. Fetch a row 3. Close the Cursor 1. Open the cursor. Cursor pointer 6-6 Copyright © Oracle Corporation, 2001. All rights reserved.
ControllingControlling ExplicitExplicit CursorsCursors 1. Open the cursor 2. Fetch a row 3. Close the Cursor 2. Fetch a row using the cursor. Cursor pointer Continue until empty. 6-7 Copyright © Oracle Corporation, 2001. All rights reserved.
ControllingControlling ExplicitExplicit CursorsCursors 1. Open the cursor 2. Fetch a row 3. Close the Cursor 3. Close the cursor. Cursor pointer 6-8 Copyright © Oracle Corporation, 2001. All rights reserved.
DeclaringDeclaring thethe CursorCursor Syntax:Syntax: CURSOR cursor_name IS select_statement; •• DoDo notnot includeinclude thethe INTOINTO clauseclause inin thethe cursorcursor declaration.declaration. •• IfIf processingprocessing rowsrows inin aa specificspecific sequencesequence isis required,required, useuse thethe ORDERORDER BYBY clauseclause inin thethe query.query. 6-9 Copyright © Oracle Corporation, 2001. All rights reserved.
DeclaringDeclaring thethe CursorCursor Example:Example: DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees; CURSOR dept_cursor IS SELECT * FROM departments WHERE location_id = 170; BEGIN 6-10 Copyright © Oracle Corporation, 2001. All rights reserved.
OpeningOpening thethe CursorCursor Syntax:Syntax: OPEN cursor_name; •• OpenOpen thethe cursorcursor toto executeexecute thethe queryquery andand identifyidentify thethe activeactive set.set. •• IfIf thethe queryquery returnsreturns nono rows,rows, nono exceptionexception isis raised.raised. •• UseUse cursorcursor attributesattributes toto testtest thethe outcomeoutcome afterafter aa fetch.fetch. 6-11 Copyright © Oracle Corporation, 2001. All rights reserved.
FetchingFetching DataData fromfrom thethe CursorCursor Syntax:Syntax: FETCH cursor_name INTO [variable1, variable2, ] | record_name]; •• RetrieveRetrieve thethe currentcurrent rowrow valuesvalues intointo variables.variables. •• IncludeInclude thethe samesame numbernumber ofof variables.variables. •• MatchMatch eacheach variablevariable toto correspondcorrespond toto thethe columnscolumns positionally.positionally. •• TestTest toto seesee whetherwhether thethe cursorcursor containscontains rows.rows. 6-12 Copyright © Oracle Corporation, 2001. All rights reserved.
FetchingFetching DataData fromfrom thethe CursorCursor Example:Example: LOOP FETCH emp_cursor INTO v_empno,v_ename; EXIT WHEN ; Process the retrieved data END LOOP; 6-13 Copyright © Oracle Corporation, 2001. All rights reserved.
ClosingClosing thethe CursorCursor Syntax:Syntax: CLOSE cursor_name; •• CloseClose thethe cursorcursor afterafter completingcompleting thethe processingprocessing ofof thethe rows.rows. •• ReopenReopen thethe cursor,cursor, ifif required.required. •• DoDo notnot attemptattempt toto fetchfetch datadata fromfrom aa cursorcursor afterafter itit hashas beenbeen closed.closed. 6-14 Copyright © Oracle Corporation, 2001. All rights reserved.
ExplicitExplicit CursorCursor AttributesAttributes ObtainObtain statusstatus informationinformation aboutabout aa cursor.cursor. Attribute Type Description %ISOPEN Boolean Evaluates to TRUE if the cursor is open %NOTFOUND Boolean Evaluates to TRUE if the most recent fetch does not return a row %FOUND Boolean Evaluates to TRUE if the most recent fetch returns a row; complement of %NOTFOUND %ROWCOUNT Number Evaluates to the total number of rows returned so far 6-15 Copyright © Oracle Corporation, 2001. All rights reserved.
TThehe %ISOPEN%ISOPEN AttributeAttribute •• FetchFetch rowsrows onlyonly whenwhen thethe cursorcursor isis open.open. •• UseUse thethe %ISOPEN%ISOPENcursorcursor attributeattribute beforebefore performingperforming aa fetchfetch toto testtest whetherwhether thethe cursorcursor isis open.open. Example:Example: IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor; END IF; LOOP FETCH emp_cursor 6-16 Copyright © Oracle Corporation, 2001. All rights reserved.
ControllingControlling MultipleMultiple FetchesFetches •• ProcessProcess severalseveral rowsrows fromfrom anan explicitexplicit cursorcursor usingusing aa loop.loop. •• FetchFetch aa rowrow withwith eacheach iteration.iteration. •• UseUse explicitexplicit cursorcursor attributesattributes toto testtest thethe successsuccess ofof eacheach fetch.fetch. 6-17 Copyright © Oracle Corporation, 2001. All rights reserved.
TheThe %NOTFOUND%NOTFOUND andand %ROWCOUNT%ROWCOUNT AttributesAttributes •• UseUse thethe %ROWCOUNT%ROWCOUNT cursorcursor attributeattribute toto retrieveretrieve anan exactexact numbernumber ofof rows.rows. •• UseUse thethe %NOTFOUND%NOTFOUND cursorcursor attributeattribute toto determinedetermine whenwhen toto exitexit thethe loop.loop. 6-18 Copyright © Oracle Corporation, 2001. All rights reserved.
ExampleExample DECLARE v_empno employees.employee_id%TYPE; v_ename employees.last_name%TYPE; CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_empno, v_ename; EXIT WHEN emp_cursor%ROWCOUNT > 10 OR emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno) ||' '|| v_ename); END LOOP; CLOSE emp_cursor; END ; 6-20 Copyright © Oracle Corporation, 2001. All rights reserved.
CursorsCursors andand RecordsRecords ProcessProcess thethe rowsrows ofof thethe activeactive setset byby fetchingfetching valuesvalues intointo aa PL/SQLPL/SQL RECORDRECORD DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees; emp_record emp_cursor%ROWTYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_record; emp_record employee_id last_name 100 King 6-21 Copyright © Oracle Corporation, 2001. All rights reserved.
CursorCursor FORFOR LoopsLoops Syntax:Syntax: FOR record_name IN cursor_name LOOP statement1; statement2; . . . END LOOP; •• TheThe cursorcursor FORFORlooploop isis aa shortcutshortcut toto processprocess explicitexplicit cursors.cursors. •• ImplicitImplicit open,open, fetch,fetch, exit,exit, andand closeclose occur.occur. •• TheThe recordrecord isis implicitlyimplicitly declared.declared. 6-22 Copyright © Oracle Corporation, 2001. All rights reserved.
CursorCursor FORFOR LoopsLoops PrintPrint aa listlist ofof thethe employeesemployees whowho workwork forfor thethe salessales department.department. DECLARE CURSOR emp_cursor IS SELECT last_name, department_id FROM employees; BEGIN FOR emp_record IN emp_cursor LOOP implicit open and implicit fetch occur IF emp_record.department_id = 80 THEN END LOOP; implicit close occurs END; / 6-23 Copyright © Oracle Corporation, 2001. All rights reserved.
CursorCursor FORFOR LoopsLoops UsingUsing SubqueriesSubqueries NoNo needneed toto declaredeclare thethe cursor.cursor. Example:Example: BEGIN FOR emp_record IN (SELECT last_name, department_id FROM employees) LOOP implicit open and implicit fetch occur IF emp_record.department_id = 80 THEN END LOOP; implicit close occurs END; 6-24 Copyright © Oracle Corporation, 2001. All rights reserved.
SummarySummary InIn thisthis lessonlesson youyou shouldshould havehave learnedlearned to:to: •• DistinguishDistinguish cursorcursor types:types: –– ImplicitImplicit cursors:cursors: usedused forfor allall DMLDMLstatementsstatements andand singlesingle rowrow queriesqueries –– ExplicitExplicit cursors:cursors: usedused forfor queriesqueries ofof zero,zero, one,one, oror moremore rowsrows •• ManipulateManipulate explicitexplicit cursorscursors •• EvaluateEvaluate thethe cursorcursor statusstatus byby usingusing cursorcursor attributesattributes •• UseUse cursorcursor FORFOR loopsloops 6-26 Copyright © Oracle Corporation, 2001. All rights reserved.
PracticePractice 66 OverviewOverview ThisThis practicepractice coverscovers thethe followingfollowing topics:topics: •• DeclaringDeclaring andand usingusing explicitexplicit cursorscursors toto queryquery rowsrows ofof aa tabletable •• UsingUsing aa cursorcursor FORFORlooploop •• ApplyingApplying cursorcursor attributesattributes toto testtest thethe cursorcursor statusstatus 6-27 Copyright © Oracle Corporation, 2001. All rights reserved.
Objectives After completing this lesson, you should be able to do the following: • Write a cursor that uses parameters • Determine when a FOR UPDATE clause in a cursor is required • Determine when to use the WHERE CURRENT OF clause • Write a cursor that uses a subquery 7-2 Copyright © Oracle Corporation, 2001. All rights reserved.
Cursors with Parameters Syntax: CURSOR cursor_name [(parameter_name datatype, )] IS select_statement; • Pass parameter values to a cursor when the cursor is opened and the query is executed. • Open an explicit cursor several times with a different active set each time. OPEN cursor_name(parameter_value, ) ; 7-3 Copyright © Oracle Corporation, 2001. All rights reserved.
Cursors with Parameters Pass the department number and job title to the WHERE clause, in the cursor SELECT statement. DECLARE CURSOR emp_cursor (p_deptno NUMBER, p_job VARCHAR2) IS SELECT employee_id, last_name FROM employees WHERE department_id = p_deptno AND job_id = p_job; BEGIN OPEN emp_cursor (80, 'SA_REP'); . . . CLOSE emp_cursor; OPEN emp_cursor (60, 'IT_PROG'); . . . END; 7-4 Copyright © Oracle Corporation, 2001. All rights reserved.
The FOR UPDATE Clause Syntax: SELECT FROM FOR UPDATE [OF column_reference][NOWAIT]; • Use explicit locking to deny access for the duration of a transaction. • Lock the rows before the update or delete. 7-5 Copyright © Oracle Corporation, 2001. All rights reserved.
The FOR UPDATE Clause Retrieve the employees who work in department 80 and update their salary. DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name, department_name FROM employees,departments WHERE employees.department_id = departments.department_id AND employees.department_id = 80 FOR UPDATE OF salary NOWAIT; 7-6 Copyright © Oracle Corporation, 2001. All rights reserved.
The WHERE CURRENT OF Clause Syntax: WHERE CURRENT OF cursor ; • Use cursors to update or delete the current row. • Include the FOR UPDATE clause in the cursor query to lock the rows first. • Use the WHERE CURRENT OF clause to reference the current row from an explicit cursor. 7-7 Copyright © Oracle Corporation, 2001. All rights reserved.
The WHERE CURRENT OF Clause DECLARE CURSOR sal_cursor IS SELECT e.department_id, employee_id, last_name, salary FROM employees e, departments d WHERE d.department_id = e.department_id and d.department_id = 60 FOR UPDATE OF salary NOWAIT; BEGIN FOR emp_record IN sal_cursor LOOP IF emp_record.salary < 5000 THEN UPDATE employees SET salary = emp_record.salary * 1.10 WHERE CURRENT OF sal_cursor; END IF; END LOOP; END; / 7-8 Copyright © Oracle Corporation, 2001. All rights reserved.
Cursors with Subqueries Example:Example: DECLARE CURSOR my_cursor IS SELECT t1.department_id, t1.department_name, t2.staff FROM departments t1, (SELECT department_id, COUNT(*) AS STAFF FROM employees GROUP BY department_id) t2 WHERE t1.department_id = t2.department_id AND t2.staff >= 3; 7-9 Copyright © Oracle Corporation, 2001. All rights reserved.
Summary In this lesson, you should have learned to: • Return different active sets using cursors with parameters. • Define cursors with subqueries and correlated subqueries. • Manipulate explicit cursors with commands using the: – FOR UPDATE clause – WHERE CURRENT OF clause 7-10 Copyright © Oracle Corporation, 2001. All rights reserved.
Practice 7 Overview This practice covers the following topics: • Declaring and using explicit cursors with parameters • Using a FOR UPDATE cursor 7-11 Copyright © Oracle Corporation, 2001. All rights reserved.
Objectives After completing this lesson, you should be able to do the following: • Define PL/SQL exceptions • Recognize unhandled exceptions • List and use different types of PL/SQL exception handlers • Trap unanticipated errors • Describe the effect of exception propagation in nested blocks • Customize PL/SQL exception messages 8-2 Copyright © Oracle Corporation, 2001. All rights reserved.
Handling Exceptions with PL/SQL • An exception is an identifier in PL/SQL that is raised during execution. • How is it raised? – An Oracle error occurs. – You raise it explicitly. • How do you handle it? – Trap it with a handler. – Propagate it to the calling environment. 8-3 Copyright © Oracle Corporation, 2001. All rights reserved.
Handling Exceptions Trap the exception Propagate the exception DECLARE DECLARE BEGIN BEGIN Exception Exception is raised is raised EXCEPTION EXCEPTION Exception Exception is trapped END; END; is not trapped Exception propagates to calling environment 8-4 Copyright © Oracle Corporation, 2001. All rights reserved.
Trapping Exceptions Syntax: EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; . . . [WHEN exception3 [OR exception4 . . .] THEN statement1; statement2; . . .] [WHEN OTHERS THEN statement1; statement2; . . .] 8-6 Copyright © Oracle Corporation, 2001. All rights reserved.
Trapping Exceptions Guidelines • The EXCEPTION keyword starts exception-handling section. • Several exception handlers are allowed. • Only one handler is processed before leaving the block. • WHEN OTHERS is the last clause. 8-7 Copyright © Oracle Corporation, 2001. All rights reserved.
Trapping Predefined Oracle Server Errors • Reference the standard name in the exception- handling routine. • Sample predefined exceptions: – NO_DATA_FOUND – TOO_MANY_ROWS – INVALID_CURSOR – ZERO_DIVIDE – DUP_VAL_ON_INDEX 8-8 Copyright © Oracle Corporation, 2001. All rights reserved.
Predefined Exceptions Syntax: BEGIN . . . EXCEPTION WHEN NO_DATA_FOUND THEN statement1; statement2; WHEN TOO_MANY_ROWS THEN statement1; WHEN OTHERS THEN statement1; statement2; statement3; END; 8-11 Copyright © Oracle Corporation, 2001. All rights reserved.
Trapping Nonpredefined Oracle Server Errors Declare Associate Reference Declarative section Exception-handling section Name the Code the PRAGMA Handle the raised exception EXCEPTION_INIT exception 8-12 Copyright © Oracle Corporation, 2001. All rights reserved.
Nonpredefined Error Trap for Oracle server error number –2292, an integrity constraint violation. DEFINE p_deptno = 10 DECLARE 1 e_emps_remaining EXCEPTION; PRAGMA EXCEPTION_INIT 2 (e_emps_remaining, -2292); BEGIN DELETE FROM departments WHERE department_id = &p_deptno; COMMIT; EXCEPTION WHEN e_emps_remaining THEN 3 DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' || TO_CHAR(&p_deptno) || '. Employees exist. '); END; 8-13 Copyright © Oracle Corporation, 2001. All rights reserved.
Functions for Trapping Exceptions • SQLCODE: Returns the numeric value for the error code • SQLERRM: Returns the message associated with the error number 8-14 Copyright © Oracle Corporation, 2001. All rights reserved.
Functions for Trapping Exceptions Example: DECLARE v_error_code NUMBER; v_error_message VARCHAR2(255); BEGIN EXCEPTION WHEN OTHERS THEN ROLLBACK; v_error_code := SQLCODE ; v_error_message := SQLERRM ; INSERT INTO errors VALUES(v_error_code, v_error_message); END; 8-15 Copyright © Oracle Corporation, 2001. All rights reserved.
Trapping User-Defined Exceptions Declare Raise Reference Declarative Executable Exception-handling section section section Name the Explicitly raise the Handle the raised exception. exception by using the exception. RAISE statement. 8-16 Copyright © Oracle Corporation, 2001. All rights reserved.
User-Defined Exceptions Example:Example: DEFINE p_department_desc = 'Information Technology ' DEFINE P_department_number = 300 DECLARE e_invalid_department EXCEPTION; 1 BEGIN UPDATE departments SET department_name = '&p_department_desc' WHERE department_id = &p_department_number; IF SQL%NOTFOUND THEN RAISE e_invalid_department; 2 END IF; COMMIT; EXCEPTION WHEN e_invalid_department THEN 3 DBMS_OUTPUT.PUT_LINE('No such department id.'); END; 8-17 Copyright © Oracle Corporation, 2001. All rights reserved.
Calling Environments iSQL*Plus Displays error number and message to screen Procedure Builder Displays error number and message to screen Oracle Developer Accesses error number and message Forms in a trigger by means of the ERROR_CODE and ERROR_TEXT packaged functions Precompiler Accesses exception number through application the SQLCA data structure An enclosing Traps exception in exception- PL/SQL block handling routine of enclosing block 8-18 Copyright © Oracle Corporation, 2001. All rights reserved.
Propagating Exceptions DECLARE . . . e_no_rows exception; e_integrity exception; PRAGMA EXCEPTION_INIT (e_integrity, -2292); BEGIN FOR c_record IN emp_cursor LOOP BEGIN Subblocks can handle SELECT UPDATE an exception or pass IF SQL%NOTFOUND THEN the exception to the RAISE e_no_rows; enclosing block. END IF; END; END LOOP; EXCEPTION WHEN e_integrity THEN WHEN e_no_rows THEN END; 8-19 Copyright © Oracle Corporation, 2001. All rights reserved.
The RAISE_APPLICATION_ERROR Procedure Syntax: raise_application_error (error_number, message[, {TRUE | FALSE}]); • You can use this procedure to issue user-defined error messages from stored subprograms. • You can report errors to your application and avoid returning unhandled exceptions. 8-20 Copyright © Oracle Corporation, 2001. All rights reserved.
The RAISE_APPLICATION_ERROR Procedure • Used in two different places: – Executable section – Exception section • Returns error conditions to the user in a manner consistent with other Oracle server errors 8-21 Copyright © Oracle Corporation, 2001. All rights reserved.
RAISE_APPLICATION_ERROR Executable section: BEGIN DELETE FROM employees WHERE manager_id = v_mgr; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20202, 'This is not a valid manager'); END IF; Exception section: EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201, 'Manager is not a valid employee.'); END; 8-22 Copyright © Oracle Corporation, 2001. All rights reserved.
Summary InIn thisthis lesson,lesson, youyou shouldshould havehave learnedlearned that:that: • Exception types: – Predefined Oracle server error – Nonpredefined Oracle server error – User-defined error • Exception trapping • Exception handling: – Trap the exception within the PL/SQL block. – Propagate the exception. 8-23 Copyright © Oracle Corporation, 2001. All rights reserved.
Practice 8 Overview This practice covers the following topics: • Handling named exceptions • Creating and invoking user-defined exceptions 8-24 Copyright © Oracle Corporation, 2001. All rights reserved.
Objectives After completing this lesson, you should be able to do the following: • Distinguish anonymous PL/SQL blocks from named PL/SQL blocks (subprograms) • Describe subprograms • List the benefits of using subprograms • List the different environments from which subprograms can be invoked 9-2 Copyright © Oracle Corporation, 2001. All rights reserved.
Objectives After completing this lesson, you should be able to do the following: • Describe PL/SQL blocks and subprograms • Describe the uses of procedures • Create procedures • Differentiate between formal and actual parameters • List the features of different parameter modes • Create procedures with parameters • Invoke a procedure • Handle exceptions in procedures • Remove a procedure 9-3 Copyright © Oracle Corporation, 2001. All rights reserved.
PL/SQL Program Constructs IS|AS or DECLARE BEGIN EXCEPTION Database Server Tools Constructs Constructs Anonymous blocks END; Anonymous blocks Application procedures or Stored procedures or functions functions Application packages Stored packages Application triggers Database triggers Object types Object types 9-4 Copyright © Oracle Corporation, 2001. All rights reserved.
Overview of Subprograms A subprogram: • Is a named PL/SQL block that can accept parameters and be invoked from a calling environment • Is of two types: – A procedure that performs an action – A function that computes a value • Is based on standard PL/SQL block structure • Provides modularity, reusability, extensibility, and maintainability • Provides easy maintenance, improved data security and integrity, improved performance, and improved code clarity 9-5 Copyright © Oracle Corporation, 2001. All rights reserved.
Block Structure for Anonymous PL/SQL Blocks DECLARE (optional) Declare PL/SQL objects to be used within this block BEGIN (mandatory) Define the executable statements EXCEPTION (optional) Define the actions that take place if an error or exception arises END; (mandatory) 9-6 Copyright © Oracle Corporation, 2001. All rights reserved.
Block Structure for PL/SQL Subprograms Subprogram Specification IS | AS Declaration section BEGIN Executable section EXCEPTION (optional) Subprogram Body Exception section END; 9-7 Copyright © Oracle Corporation, 2001. All rights reserved.
PL/SQL Subprograms xxx xxx xxx xxx xxx xxx P xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx P Subprogram P, xxx xxx xxx which contains the xxx xxx xxx P repeated code PL/SQL program invoking the subprogram at multiple Code repeated more than locations once in a PL/SQL program 9-8 Copyright © Oracle Corporation, 2001. All rights reserved.
Invoking Stored Procedures and Functions Scott LOG_EXECUTION procedure 1 xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx 2 vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx 3 xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv Oracle Oracle Oracle vvvvvvvvvvvvvv xxxxxxxxxxxxxx Portal Discoverer Forms vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv Developer xxxxxxxxxxxxxx vvvvvvvvvvvvvv 4 Scott 9-11 Copyright © Oracle Corporation, 2001. All rights reserved.
What Is a Procedure? • A procedure is a type of subprogram that performs an action. • A procedure can be stored in the database, as a schema object, for repeated execution. 9-12 Copyright © Oracle Corporation, 2001. All rights reserved.
Syntax for Creating Procedures CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] IS|AS PL/SQL Block; • The REPLACE option indicates that if the procedure exists, it will be dropped and replaced with the new version created by the statement. • PL/SQL block starts with either BEGIN or the declaration of local variables and ends with either END or END procedure_name. 9-13 Copyright © Oracle Corporation, 2001. All rights reserved.
Developing Procedures Editor Code to create 1 file.sql procedure iSQL*Plus 2 Load and execute file.sql Oracle Source code Use SHOW ERRORS Compile to view compilation errors P code Procedure created Execute 3 9-14 Copyright © Oracle Corporation, 2001. All rights reserved.
FormalFormal VersusVersus ActualActual ParametersParameters •• FormalFormal parameters:parameters: variablesvariables declareddeclared inin thethe parameterparameter listlist ofof aa subprogramsubprogram specificationspecification Example:Example: CREATECREATE PROCEDUREPROCEDURE raise_sal(raise_sal(p_idp_id NUMBER,NUMBER, p_amountp_amount NUMBER)NUMBER) ENDEND raise_sal;raise_sal; •• ActualActual parameters:parameters: variablesvariables oror expressionsexpressions referencedreferenced inin thethe parameterparameter listlist ofof aa subprogramsubprogram callcall Example:Example: raise_sal(raise_sal(v_id,v_id, 20002000)) 9-15 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating Procedures with Parameters IN OUT IN OUT Default mode Must be specified Must be specified Value is passed into Returned to Passed into subprogram calling subprogram; environment returned to calling environment Formal parameter acts as Uninitialized Initialized variable a constant variable Actual parameter can be a Must be a variable Must be a variable literal, expression, constant, or initialized variable Can be assigned a default Cannot be Cannot be value assigned assigned a default value a default value 9-17 Copyright © Oracle Corporation, 2001. All rights reserved.
IN Parameters: Example 176 p_id CREATE OR REPLACE PROCEDURE raise_salary (p_id IN employees.employee_id%TYPE) IS BEGIN UPDATE employees SET salary = salary * 1.10 WHERE employee_id = p_id; END raise_salary; / 9-18 Copyright © Oracle Corporation, 2001. All rights reserved.
OUTOUT Parameters:Parameters: ExampleExample emp_query.sql CREATE OR REPLACE PROCEDURE query_emp (p_id IN employees.employee_id%TYPE, p_name OUT employees.last_name%TYPE, p_salary OUT employees.salary%TYPE, p_comm OUT employees.commission_pct%TYPE) IS BEGIN SELECT last_name, salary, commission_pct INTO p_name, p_salary, p_comm FROM employees WHERE employee_id = p_id; END query_emp; / 9-20 Copyright © Oracle Corporation, 2001. All rights reserved.
Viewing OUT Parameters • Load and run the emp_query.sql script file to create the QUERY_EMP procedure. • Declare host variables, execute the QUERY_EMP procedure, and print the value of the global G_NAME variable. VARIABLE g_name VARCHAR2(25) VARIABLE g_sal NUMBER VARIABLE g_comm NUMBER EXECUTE query_emp(171, :g_name, :g_sal, :g_comm) PRINT g_name 9-21 Copyright © Oracle Corporation, 2001. All rights reserved.
IN OUT Parameters Calling environment FORMAT_PHONE procedure '8006330575' '(800)633-0575' p_phone_no CREATE OR REPLACE PROCEDURE format_phone (p_phone_no IN OUT VARCHAR2) IS BEGIN p_phone_no := '(' || SUBSTR(p_phone_no,1,3) || ')' || SUBSTR(p_phone_no,4,3) || '-' || SUBSTR(p_phone_no,7); END format_phone; / 9-22 Copyright © Oracle Corporation, 2001. All rights reserved.
Viewing IN OUT Parameters VARIABLE g_phone_no VARCHAR2(15) BEGIN :g_phone_no := '8006330575'; END; / PRINT g_phone_no EXECUTE format_phone (:g_phone_no) PRINT g_phone_no 9-23 Copyright © Oracle Corporation, 2001. All rights reserved.
Methods for Passing Parameters • Positional: List actual parameters in the same order as formal parameters. • Named: List actual parameters in arbitrary order by associating each with its corresponding formal parameter. • Combination: List some of the actual parameters as positional and some as named. 9-24 Copyright © Oracle Corporation, 2001. All rights reserved.
DEFAULT Option for Parameters CREATE OR REPLACE PROCEDURE add_dept (p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 1700) IS BEGIN INSERT INTO departments(department_id, department_name, location_id) VALUES (departments_seq.NEXTVAL, p_name, p_loc); END add_dept; / 9-25 Copyright © Oracle Corporation, 2001. All rights reserved.
Examples of Passing Parameters BEGIN add_dept; add_dept ('TRAINING', 2500); add_dept ( p_loc => 2400, p_name =>'EDUCATION'); add_dept ( p_loc => 1200) ; END; / SELECT department_id, department_name, location_id FROM departments; 9-26 Copyright © Oracle Corporation, 2001. All rights reserved.
Declaring Subprograms leave_emp2.sql CREATE OR REPLACE PROCEDURE leave_emp2 (p_id IN employees.employee_id%TYPE) IS PROCEDURE log_exec IS BEGIN INSERT INTO log_table (user_id, log_date) VALUES (USER, SYSDATE); END log_exec; BEGIN DELETE FROM employees WHERE employee_id = p_id; log_exec; END leave_emp2; / 9-27 Copyright © Oracle Corporation, 2001. All rights reserved.
InvokingInvoking aa ProcedureProcedure fromfrom anan AnonymousAnonymous PL/SQLPL/SQL BlockBlock DECLARE v_id NUMBER := 163; BEGIN raise_salary(v_id); invoke procedure COMMIT; END; 9-28 Copyright © Oracle Corporation, 2001. All rights reserved.
InvokingInvoking aa ProcedureProcedure fromfrom AnotherAnother ProcedureProcedure process_emps.sql CREATE OR REPLACE PROCEDURE process_emps IS CURSOR emp_cursor IS SELECT employee_id FROM employees; BEGIN FOR emp_rec IN emp_cursor LOOP raise_salary(emp_rec.employee_id); END LOOP; COMMIT; END process_emps; / 9-29 Copyright © Oracle Corporation, 2001. All rights reserved.
Handled Exceptions Called procedure Calling procedure PROCEDURE PROC2 PROCEDURE IS PROC1 IS BEGIN Exception raised BEGIN EXCEPTION Exception handled PROC2(arg1); END PROC2; EXCEPTION Control returns to END PROC1; calling procedure 9-30 Copyright © Oracle Corporation, 2001. All rights reserved.
Handled Exceptions CREATE PROCEDURE p2_ins_dept(p_locid NUMBER) IS v_did NUMBER(4); BEGIN DBMS_OUTPUT.PUT_LINE('Procedure p2_ins_dept started'); INSERT INTO departments VALUES (5, 'Dept 5', 145, p_locid); SELECT department_id INTO v_did FROM employees WHERE employee_id = 999; END; CREATE PROCEDURE p1_ins_loc(p_lid NUMBER, p_city VARCHAR2) IS v_city VARCHAR2(30); v_dname VARCHAR2(30); BEGIN DBMS_OUTPUT.PUT_LINE('Main Procedure p1_ins_loc'); INSERT INTO locations (location_id, city) VALUES (p_lid, p_city); SELECT city INTO v_city FROM locations WHERE location_id = p_lid; DBMS_OUTPUT.PUT_LINE('Inserted city '||v_city); DBMS_OUTPUT.PUT_LINE('Invoking the procedure p2_ins_dept '); p2_ins_dept(p_lid); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No such dept/loc for any employee'); END; 9-31 Copyright © Oracle Corporation, 2001. All rights reserved.
Unhandled Exceptions Called procedure Calling procedure PROCEDURE PROCEDURE PROC2 PROC1 IS IS BEGIN Exception raised BEGIN EXCEPTION Exception unhandled PROC2(arg1); END PROC2; EXCEPTION END PROC1; Control returned to exception section of calling procedure 9-32 Copyright © Oracle Corporation, 2001. All rights reserved.
Unhandled Exceptions CREATE PROCEDURE p2_noexcep(p_locid NUMBER) IS v_did NUMBER(4); BEGIN DBMS_OUTPUT.PUT_LINE('Procedure p2_noexcep started'); INSERT INTO departments VALUES (6, 'Dept 6', 145, p_locid); SELECT department_id INTO v_did FROM employees WHERE employee_id = 999; END; CREATE PROCEDURE p1_noexcep(p_lid NUMBER, p_city VARCHAR2) IS v_city VARCHAR2(30); v_dname VARCHAR2(30); BEGIN DBMS_OUTPUT.PUT_LINE(' Main Procedure p1_noexcep'); INSERT INTO locations (location_id, city) VALUES (p_lid, p_city); SELECT city INTO v_city FROM locations WHERE location_id = p_lid; DBMS_OUTPUT.PUT_LINE('Inserted new city '||v_city); DBMS_OUTPUT.PUT_LINE('Invoking the procedure p2_noexcep '); p2_noexcep(p_lid); END; 9-33 Copyright © Oracle Corporation, 2001. All rights reserved.
RemovingRemoving ProceduresProcedures DropDrop aa procedureprocedure storedstored inin thethe database.database. Syntax:Syntax: DROP PROCEDURE procedure_name Example:Example: DROP PROCEDURE raise_salary; 9-34 Copyright © Oracle Corporation, 2001. All rights reserved.
Summary In this lesson, you should have learned that: • A procedure is a subprogram that performs an action. • You create procedures by using the CREATE PROCEDURE command. • You can compile and save a procedure in the database. • Parameters are used to pass data from the calling environment to the procedure. • There are three parameter modes: IN, OUT, and IN OUT. 9-35 Copyright © Oracle Corporation, 2001. All rights reserved.
Summary • Local subprograms are programs that are defined within the declaration section of another program. • Procedures can be invoked from any tool or language that supports PL/SQL. • You should be aware of the effect of handled and unhandled exceptions on transactions and calling procedures. • You can remove procedures from the database by using the DROP PROCEDURE command. • Procedures can serve as building blocks for an application. 9-36 Copyright © Oracle Corporation, 2001. All rights reserved.
Practice 9 Overview This practice covers the following topics: • Creating stored procedures to: – Insert new rows into a table, using the supplied parameter values – Update data in a table for rows matching with the supplied parameter values – Delete rows from a table that match the supplied parameter values – Query a table and retrieve data based on supplied parameter values • Handling exceptions in procedures • Compiling and invoking procedures 9-37 Copyright © Oracle Corporation, 2001. All rights reserved.
Objectives After completing this lesson, you should be able to do the following: • Describe the uses of functions • Create stored functions • Invoke a function • Remove a function • Differentiate between a procedure and a function 10-2 Copyright © Oracle Corporation, 2001. All rights reserved.
Overview of Stored Functions • A function is a named PL/SQL block that returns a value. • A function can be stored in the database as a schema object for repeated execution. • A function is called as part of an expression. 10-3 Copyright © Oracle Corporation, 2001. All rights reserved.
Syntax for Creating Functions CREATE [OR REPLACE] FUNCTION function_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] RETURN datatype IS|AS PL/SQL Block; The PL/SQL block must have at least one RETURN statement. 10-4 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating a Function Editor Code to create 1 file.sql function iSQL*Plus 2 Load and execute file.sql Oracle Source code Compile P code Function created Invoke 3 10-5 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating a Stored Function by Using iSQL*Plus 1. Enter the text of the CREATE FUNCTION statement in an editor and save it as a SQL script file. 2. Run the script file to store the source code and compile the function. 3. Use SHOW ERRORS to see compilation errors. 4. When successfully compiled, invoke the function. 10-6 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating a Stored Function by Using iSQL*Plus: Example get_salary.sql CREATE OR REPLACE FUNCTION get_sal (p_id IN employees.employee_id%TYPE) RETURN NUMBER IS v_salary employees.salary%TYPE :=0; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_id; RETURN v_salary; END get_sal; / 10-7 Copyright © Oracle Corporation, 2001. All rights reserved.
Executing Functions • Invoke a function as part of a PL/SQL expression. • Create a variable to hold the returned value. • Execute the function. The variable will be populated by the value returned through a RETURN statement. 10-8 Copyright © Oracle Corporation, 2001. All rights reserved.
Executing Functions: Example Calling environment GET_SAL function 117 p_id RETURN v_salary 1. Load and run the get_salary.sql file to create the function 2 VARIABLE g_salary NUMBER 3 EXECUTE :g_salary := get_sal(117) 4 PRINT g_salary 10-9 Copyright © Oracle Corporation, 2001. All rights reserved.
Advantages of User-Defined Functions in SQL Expressions • Extend SQL where activities are too complex, too awkward, or unavailable with SQL • Can increase efficiency when used in the WHERE clause to filter data, as opposed to filtering the data in the application • Can manipulate character strings 10-10 Copyright © Oracle Corporation, 2001. All rights reserved.
InvokingInvoking FunctionsFunctions inin SQLSQL Expressions:Expressions: ExampleExample CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER) RETURN NUMBER IS BEGIN RETURN (p_value * 0.08); END tax; / SELECT employee_id, last_name, salary, tax(salary) FROM employees WHERE department_id = 100; 10-11 Copyright © Oracle Corporation, 2001. All rights reserved.
Locations to Call User-Defined Functions • Select list of a SELECT command • Condition of the WHERE and HAVING clauses • CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses • VALUES clause of the INSERT command • SET clause of the UPDATE command 10-12 Copyright © Oracle Corporation, 2001. All rights reserved.
Restrictions on Calling Functions from SQL Expressions To be callable from SQL expressions, a user-defined function must: • Be a stored function • Accept only IN parameters • Accept only valid SQL data types, not PL/SQL specific types, as parameters • Return data types that are valid SQL data types, not PL/SQL specific types 10-13 Copyright © Oracle Corporation, 2001. All rights reserved.
Restrictions on Calling Functions from SQL Expressions • Functions called from SQL expressions cannot contain DML statements. • Functions called from UPDATE/DELETE statements on a table T cannot contain DML on the same table T. • Functions called from an UPDATE or a DELETE statement on a table T cannot query the same table. • Functions called from SQL statements cannot contain statements that end the transactions. • Calls to subprograms that break the previous restriction are not allowed in the function. 10-14 Copyright © Oracle Corporation, 2001. All rights reserved.
Restrictions on Calling from SQL CREATE OR REPLACE FUNCTION dml_call_sql (p_sal NUMBER) RETURN NUMBER IS BEGIN INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary) VALUES(1, 'employee 1', 'emp1@company.com', SYSDATE, 'SA_MAN', 1000); RETURN (p_sal + 100); END; / UPDATE employees SET salary = dml_call_sql(2000) WHERE employee_id = 170; 10-15 Copyright © Oracle Corporation, 2001. All rights reserved.
RemovingRemoving FunctionsFunctions DropDrop aa storedstored function.function. Syntax:Syntax: DROP FUNCTION function_name Example:Example: DROP FUNCTION get_sal; •• AllAll thethe privilegesprivileges grantedgranted onon aa functionfunction areare revokedrevoked whenwhen thethe functionfunction isis dropped.dropped. •• TheThe CREATECREATE OROR REPLACEREPLACEsyntaxsyntax isis equivalentequivalent toto droppingdropping aa functionfunction andand recreatingrecreating it.it. PrivilegesPrivileges grantedgranted onon thethe functionfunction remainremain thethe samesame whenwhen thisthis syntaxsyntax isis used.used. 10-16 Copyright © Oracle Corporation, 2001. All rights reserved.