[ Pobierz całość w formacie PDF ]
. MolièreThis chapter is a quick reference guide to PL/SQL syntax and semantics.It showsyou how commands, parameters, and other language elements are sequenced toform PL/SQL statements.Also, to save you time and trouble, it provides usagenotes and short examples.Major TopicsAssignment StatementAUTONOMOUS_TRANSACTION PragmaBlocksCLOSE StatementCollection MethodsCollectionsCommentsCOMMIT StatementConstants and VariablesCursor AttributesCursor VariablesCursorsDELETE StatementEXCEPTION_INIT PragmaExceptionsEXECUTE IMMEDIATE StatementEXIT StatementExpressionsFETCH StatementFORALL StatementLanguage Elements 11-1 FunctionsGOTO StatementIF StatementINSERT StatementLiteralsLOCK TABLE StatementLOOP StatementsNULL StatementObject TypesOPEN StatementOPEN-FOR StatementOPEN-FOR-USING StatementPackagesProceduresRAISE StatementRecordsRESTRICT_REFERENCES PragmaRETURN StatementROLLBACK Statement%ROWTYPE AttributeSAVEPOINT StatementSELECT INTO StatementSERIALLY_REUSABLE PragmaSET TRANSACTION StatementSQL CursorSQLCODE FunctionSQLERRM Function%TYPE AttributeUPDATE StatementReading the Syntax DiagramsWhen you are unsure of the syntax to use in a PL/SQL statement, trace through itssyntax diagram, reading from left to right and top to bottom.You can verify orconstruct any PL/SQL statement that way.The diagrams are graphic representations of Bachus-Naur Form (BNF) productions.Within the diagrams, keywords are enclosed in boxes, delimiters in circles, andidentifiers in ovals.Each diagram defines a syntactic element.Every path through the diagramdescribes a possible form of that element.Follow in the direction of the arrows.If aline loops back on itself, you can repeat the element enclosed by the loop.11-2 PL/SQL User s Guide and Reference Assignment StatementAssignment StatementAn assignment statement sets the current value of a variable, field, parameter, orelement.The statement consists of an assignment target followed by the assignmentoperator and an expression.When the statement is executed, the expression isevaluated and the resulting value is stored in the target.For more information, see"Assignments" on page 2-41.Syntaxassignment_statement( index )collection_namecursor_variable_name: host_cursor_variable_name: indicator_name: host_variable_name:= expression ;.attribute_nameobject_nameparameter_name.field_namerecord_namevariable_nameKeyword and Parameter Descriptionattribute_nameThis identifies an attribute of an object type.The name must be unique within theobject type (but can be reused in other object types).You cannot initialize anattribute in its declaration using the assignment operator orDEFAULT clause.Also,you cannot impose theNOTNULL constraint on an attribute.Language Elements 11-3 Assignment Statementcollection_nameThis identifies a nested table, index-by table, or varray previously declared withinthe current scope.cursor_variable_nameThis identifies a PL/SQL cursor variable previously declared within the currentscope.Only the value of another cursor variable can be assigned to a cursorvariable.expressionThis is an arbitrarily complex combination of variables, constants, literals,operators, and function calls.The simplest expression consists of a single variable.For the syntax ofexpression, see "Expressions" on page 11-67.When theassignment statement is executed, the expression is evaluated and the resultingvalue is stored in the assignment target.The value and target must have compatibledatatypes.field_nameThis identifies a field in a user-defined or%ROWTYPE record.host_cursor_variable_nameThis identifies a cursor variable declared in a PL/SQL host environment and passedto PL/SQL as a bind variable.The datatype of the host cursor variable is compatiblewith the return type of any PL/SQL cursor variable.Host variables must beprefixed with a colon.host_variable_nameThis identifies a variable declared in a PL/SQL host environment and passed toPL/SQL as a bind variable.Host variables must be prefixed with a colon.indexThis is an expression that must yield (or convert implicitly to) an integer.For moreinformation, see "Datatype Conversion" on page 2-28.11-4 PL/SQL User s Guide and Reference Assignment Statementindicator_nameThis identifies an indicator variable declared in a PL/SQL host environment andpassed to PL/SQL.Indicator variables must be prefixed with a colon.An indicatorvariable "indicates" the value or condition of its associated host variable.Forexample, in the Oracle Precompiler environment, indicator variables let you detectnulls or truncated values in output host variables.object_nameThis identifies an object (instance of an object type) previously declared within thecurrent scope.parameter_nameThis identifies a formalOUT orINOUT parameter of the subprogram in which theassignment statement appears.record_nameThis identifies a user-defined or%ROWTYPE record previously declared within thecurrent scope.variable_nameThis identifies a PL/SQL variable previously declared within the current scope.Usage NotesBy default, unless a variable is initialized in its declaration, it is initialized toNULLevery time a block or subprogram is entered.So, never reference a variable beforeyou assign it a value.You cannot assign nulls to a variable defined asNOTNULL.If you try, PL/SQL raisesthe predefined exceptionVALUE_ERROR.Only the valuesTRUE,FALSE, andNULL can be assigned to a Boolean variable.When applied to an expression, the relational operators return a Boolean value.So,the following assignment is legal:DECLAREout_of_range BOOLEAN;.BEGIN.out_of_range := (salary maximum);Language Elements 11-5 Assignment StatementAs the next example shows, you can assign the value of an expression to a specificfield in a record:DECLAREemp_rec emp%ROWTYPE;BEGIN.emp_rec.sal := current_salary + increase;Moreover, you can assign values to all fields in a record at once.PL/SQL allowsaggregate assignment between entire records if their declarations refer to the samecursor or table.For example, the following assignment is legal:DECLAREemp_rec1 emp%ROWTYPE;emp_rec2 emp%ROWTYPE;dept_rec dept%ROWTYPE;BEGIN.emp_rec1 := emp_rec2;Using the following syntax, you can assign the value of an expression to a specificelement in a collection:collection_name(index) := expression;In the following example, you assign the uppercase value oflast_name to thethird row in nested tableename_tab:ename_tab(3) := UPPER(last_name);ExamplesSeveral examples of assignment statements follow:wages := hours_worked * hourly_salary;country := France ;costs := labor + supplies;done := (count > 100);dept_rec.loc := BOSTON ;comm_tab(5) := sales * 0
[ Pobierz całość w formacie PDF ]