Ch 4
"
Delimiter -- The quoted identifier delimiter is a double quote. It lets you access tables created in case-sensitive fashion from the database catalog. This is required when you have created database catalog objects in case-sensitive fashion. For example, you create a case-sensitive table or column by using quoted identifier delimiters: CREATE TABLE "Demo" ("Demo_ID" NUMBER , demo_value VARCHAR2(10)); You insert a row by using the following quote-delimited syntax: INSERT INTO "Demo1" VALUES (1,'One Line ONLY.'); Like the SQL syntax, PL/SQL requires you to use the quoted identifier delimiter to find the database catalog object, like BEGIN FOR i IN (SELECT "Demo_ID", demo_value FROM "Demo") LOOP dbms_output.put_line(i."Demo_ID"); dbms_output.put_line(i.demo_value); END LOOP; END; / Beyond the quoted identifier in embedded SQL statements, you must refer to any column names by using quote-delimited syntax. This is done in the first output line, where the loop index (i) is followed by the component selector (.) and then a quote-delimited identifier ("Demo_ID"). You should note that no quotes are required to access the case-insensitive column. If you forget to enclose a case-sensitive column name (identifier), your program returns a PLS-00302 error that says the identifier is not declared. You can also use the quoted identifier delimiter to build identifiers that include reserved symbols, like an "X+Y" identifier.
--
Delimiter -- r Two adjoining dashes are a single comment operator. Everything to the right of the single comment operator is treated as text and is not parsed as part of a PL/SQL program. An example of a single-line comment is -- This is a single-line comment.
Delimiters
' ( ) , << >> -- /* */ "
Math operators
+ / ** * -
Association symbols
: & % => . @
What are the three not-equal comparison operators.
<> != ^= They all perform exactly the same behaviors. You can use whichever suits your organizational needs.
Comparison operators
= - <> != ^= > < >= <= IS NULL IS EMPTY IS SET
: (Colon)
Association -- The host variable indicator precedes a valid identifier name and designates that identifier as a session variable.
&
Association -- The substitution indicator lets you pass actual parameters into anonymous block PL/SQL programs. You should never assign substitution variables inside declaration blocks because assignment errors don't raise an error that you can catch in your exception block. You should make substitution variable assignments in the execution block. The following demonstrates the assignment of a string substitution variable to a local variable in an execution block: a := '&string_in';
=>
Association-- The association operator is a combination of an equal sign and a greater-than symbol. It is used in name notation function and procedure calls.
%
Association-- The attribute indicator lets you link a database catalog column, row, or cursor attribute. You are anchoring a variable data type when you link a variable to a catalog object, like a table or column.
. (period)
Association-- The component selector is a period, and it glues references together; for example, a schema and a table, a package and a function, or an object and a member method. Component selectors are also used to link cursors and cursor attributes (columns). The following are some prototype examples: schema_name.table_name package_name.function_name object_name.member_method_name cursor_name.cursor_attr object_name.nested_object_name.object_attr
@
Association-- The remote access indicator lets you access a remote database through database links.
What data types are available for session variables?
CHAR, CLOB, NCHAR, NCLOB, NUMBER, NVARCHAR2, REFCURSOR, and VARCHAR2 data types
Literal types
Character, String, Numeric, Boolean, Date & Time
=
Comparison operator -- It tests for equality of value and implicitly does type conversion where possible. There is no identity comparison operator because PL/SQL is a strongly typed language. PL/SQL comparison operations are equivalent to identity comparisons because you can only compare like typed values.
IS EMPTY
Comparison operator -- The IS EMPTY comparison operator checks whether the left operand holds any elements, and only applies when the left operand is a varray or table collection data type.
IS NULL
Comparison operator -- The IS NULL comparison operator checks whether the left operand holds a null.
IS SET
Comparison operator -- The IS SET comparison operator checks whether the left operand holds a set of elements, and only applies when the left operand is a varray or table collection data type.
>
Comparison operator -- The greater-than operator is an inequality comparison operator. It compares whether the left operand is greater than the right operand.
>=
Comparison operator -- The greater-than or equal comparison operator is an inequality comparison operator. It compares whether the left operand is greater than or equal to the right operand.
<
Comparison operator -- The less-than operator is an inequality comparison operator. It compares whether the left operand is less than the right operand.
<=
Comparison operator -- The less-than or equal comparison operator is an inequality comparison operator. It compares whether the left operand is less than or equal to the right operand.
-
Comparison operator -- The negation operator symbol is a minus sign. It changes a number from its positive value to its negative value.
!=
Comparison operator -- not-equal comparison operator
<>
Comparison operator -- not-equal comparison operator
^=
Comparison operator -- not-equal comparison operator
||
Concatenation operator The concatenation operator is formed by combining two perpendicular vertical lines. You use it to glue strings together, as shown: a := 'Glued'||' '||'together. ';
||
Concatenation operator -- The concatenation operator is formed by combining two perpendicular vertical lines. You use it to glue strings together, as shown: a := 'Glued'||' '||'together. ';
/* */
Delimiter -- /* This is line one. This is line two. */
,
Delimiter -- The item separator is a comma and delimits items in lists.
( )
Delimiter -- The opening and closing expressions or list delimiters are an opening parenthesis symbol and closing parenthesis symbol, respectively. You can place a list of comma-delimited numeric or string literals, or identifiers, inside a set of parentheses. You use parentheses to enclose formal and actual parameters to subroutines or to produce lists for comparative evaluations.
<< >>
Delimiter -- The opening and closing guillemets are the opening and closing delimiters, respectively, for labels in PL/SQL. Labels are any valid identifiers in the programming language. Perl and PHP programmers should know these don't work as HERE document tags.
Session variables are also known as
bind variables.
You define a variable by
both declaring the variable and assigning it a value.
Identifiers are
bricks because they include reserved words and keywords as well as both subroutine and variable names.
You would typically use anonymous blocks when
building scripts to seed data or perform one-time processing activities.
Planned comments are straightforward
but you can introduce errors when you comment out code to test or debug your programs. The biggest problem occurs when you comment out all executable statements from a code block. This will raise various parsing errors because every coding block must have at a minimum one statement, as discussed in the "Block Structure" section of Chapter 3.The other problem frequently introduced with single-line comments arises from placing them before either a statement terminator (a semicolon) or an ending block keyword. This also raises a parsing error when you try to run or compile the program unit.
Develop lexical units
by combining valid characters and symbols.
Character literals are defined
by enclosing any character in a set of single quotes. The literal values are case sensitive, while the programming language is case insensitive. This mirrors the behavior of SQL and data stored in the database as character or string data (the VARCHAR2 data type is the most commonly used type). You assign a character literal to a variable using the following syntax: a := 'a';
You can define a session variable
by using a prototype, like VARIABLE variable_name data type_name This implements the prototype by creating a session-level variablelength string: SQL> VARIABLE my_string VARCHAR2(30) Then, you can assign a value using an anonymous block PL/SQL program, like BEGIN :my_string := 'A string literal.'; END; / You can then query the result from the DUAL pseudo table: SELECT :my_string FROM dual; Alternatively, you can reuse the variable in another PL/SQL block program because the variable enjoys a session-level scope. A subsequent anonymous block program in a script could then print the value in the session variable: BEGIN dbms_output.put_line(:my_string); END; / This is a flexible way to exchange variables between multiple statements and PL/SQL blocks in a single script file.
Lexical delimiters
can act as delimiters or provide other functions in programming languages.
Boolean literals
can be a Boolean variable or expression and can be true, false, or null. This three-valued state of Boolean variables makes it possible that your program can incorrectly handle a not true or not false condition any time the variable is null. Chapter 5 covers how to manage conditional statements to secure expected results. You can make any of the following assignments to a previously declared BOOLEAN variable: b := TRUE; -- This assigns a true state. b := FALSE; -- This assigns a false state. b := NULL; -- This assigns a null or default state. TIP It is a good practice to assign an initial value of TRUE or FALSE to all Boolean variables, which means you should always explicitly define their initial state. You should also consider declaring Boolean columns as not null constrained.
User-defined data types
can be defined in SQL as schema-level data types, or in PL/SQL blocks.
The declaration block lets you
declare data types, structures, variables, and named functions and procedures.
PL/SQL lets you
define types and declare variables.
curly braces do not
delimit blocks in PL/SQL
Lexical units can be
delimiters, identifiers, literals, or comments
Lexical units
enable you to build PL/SQL programs.
Named blocks are
functions, procedures, and packages with internal functions and procedures, and objects types that include functions and procedures.
Declaring a variable means that you
give it a name and a data type. You can also define a variable by giving it a name, a data type, and a value. You both declare and assign a value when defining a variable.
When you define identifiers in functions and procedures
they are accessible based on their implementation scope. You can access calling scope identifiers from within local functions and procedures but not through schema-level functions and procedures. Package specifications let you define package-level data types that are available in your schema. These package-level data types are also available in other schemas when you grant execute privilege on them to other schemas. Package bodies let you define local data types that are only available to functions and procedures defined within the package body or implementation. You reference package-level data types by using the component selector to connect the package and data type names.
Comments aren't bricks or mortar, but
they're important because they help you (or some other future developer) see what you're doing
A variable name is mapped
to a known data type and then added to the program's namespace as an identifier when you declare a variable.
Composite variables are
variables built from primitives or base types in a programming language
You create identifiers
when you define program components
You label a data type and designate how to manage the data type in memory when
you define a type.
Anonymous blocks are also effective when
you want to nest activity in another PL/SQL block's execution section. The basic anonymous block structure must contain an execution section. You can also put optional declaration and exception sections in anonymous blocks.
Script files are
text files that have SQL statements and/or PL/SQL anonymous blocks that perform a set of sequenced steps.
The exception block ends with
the END keyword, which also ends the program unit.
Subtypes inherit
the behavior of a data type but also typically have constrained behaviors.
The EXCEPTION keyword starts
the exception block
The BEGIN keyword starts
the execution block and ends the declaration block.
Reserved Words and Keywords
Both reserved words and keywords are lexical units that provide basic tools for building programs. For example, you use the NOT reserved word as a negation in comparison operations, and use the NULL keyword to represent a null value or statement. You cannot use these words when defining your own programs and data types.
Scalar variables contain
only one thing, such as a character, date, or number.
:=
It is the only assignment operator in the language. The assignment operator is a colon immediately followed by an equal sign. You assign a right operand to a left operand, like a := b + c; This adds the numbers in variables b and c and then assigns the result to variable a.
+
Math -- The addition operator lets you add left and right operands and returns a result.
/
Math -- The division operator lets you divide a left operand by a right operand and returns a result.
**
Math -- The exponential operator raises a left operand to the power designated by a right operand. The operator enjoys the highest precedence for math operators in the language. As a result of that, a fractional exponent must be enclosed in parentheses (also known as expression or list delimiters) to designate order of operation. Without parentheses, the left operand is raised to the power of the numerator and the result is divided by the denominator of a fractional exponent. You raise 3 to the third power and assign the result of 27 to variable a by using the following syntax: a := 3**3; You raise 8 to the fractional power of 1/3 and assign the result of 2 to variable a by using the following syntax: a := 8**(1/3); The parentheses ensures that the division operation occurs first. Exponential operations take precedence on other mathematical operations without parenthetical grouping. Please note that exponential calculations are scientific computing and you should use IEEE-754 data types.
*
Math -- The multiplication operator lets you multiply a left operand by a right operand and returns a result.
-
Math -- The subtraction operator lets you subtract the right operand from the left operand and returns a result.
PL/SQL supports two principal variable data types:
scalar variables and composite variables.
Quoted Identifiers
Oracle Database 11g forward enables you to use quoted identifier delimiters to build identifiers that would otherwise be disallowed because of symbol reuse. Quoted identifiers can include any printable characters, including spaces. However, you cannot embed double quotes inside identifiers. The maximum size of a quoted identifier is 30 characters. You can also use quoted identifiers to leverage reserved words and keywords. Although this is allowed, it is strongly discouraged by Oracle. For example, the following program creates a quoted identifier "End," which is a case-insensitive reserved word: SQL> DECLARE 2 "End" NUMBER := 1; 3 BEGIN 4 dbms_output.put_line('A quoted identifier End ['||"End"||']'); 5 END; 6 / Again, while this is possible, you should avoid it!
Predefined Identifiers
Oracle Database 12c (and some recent releases prior to it) provides a STANDARD package, and it globally grants access to the package through a public grant. The STANDARD package defines the built-in functions found in Appendix C. It also contains the definitions for standard data types and errors. You should be careful to not override any predefined identifiers by creating user-defined identifiers with the same names. This happens any time you define a variable that duplicates a component from the STANDARD package, just as you can define a variable in a nested PL/SQL block that overrides the containing block variable name.
The DECLARE keyword
starts the declaration section in anonymous block programs
Statement terminator
The semicolon (;) is used to indicate the end of a statement or block unit.
the function or procedure header, specification, or signature (name, parameter list, and return type)
starts the declaration section in named block programs.
User-defined identifiers
User-defined identifiers
PL/SQL introduces
a BOOLEAN data type and several subtypes derived from the SQL data types.
Literals are
a convenient way for you to introduce string and numeric constants into your programs.
You can also call any base data type
a supertype, because it is the model for subtypes.
Unconstrained subtypes are also called
aliases. An unconstrained subtype doesn't change a base type's behavior.
A literal is
an explicit character, string, number, or Boolean value. Literal values are not represented by identifiers. String literals can also represent date or time literals.
Unnamed blocks are known as
anonymous blocks
Unconstrained subtypes
are interchangeable with their base types, while only qualified values can be assigned to constrained subtypes from base types. You can extend these types by building your own subtypes.
Composite variables in Oracle Database
are records (structures), arrays, lists, system reference cursors, and object types.
Identifiers
are words. They can be reserved words, keywords, predefined identifiers, quoted identifiers, user-defined variables, subroutines, or user-defined types.
Other functions of lexical delimiters are
assignment, association, concatenation, comparison, math, and statement controls.
PL/SQL automatically
assigns most declared variables a null value. This means that variables are generally defined in the language.
you can have
static or instance functions and procedures, and specialized constructor functions that let you create instances of object types.
Lexical delimiters are
symbols or symbol sets.
Date literals
have an implicit conversion from a string literal that maps to the default format mask. The default format masks for dates are DD-MON-RR and DD-MON-YYYY, where DD represents a two-digit day, MON represents a three-character month, RR represents a two-digit relative year, and YYYY represents a four-digit absolute year. Relative years are calculated by counting 50 years forward or backward from the current system clock. You assign a relative or absolute date as follows to previously declared DATE data type variables: relative_date := '01-JUN-07'; -- This assigns 01-JUN-2007. absolute_date := '01-JUN-1907'; -- This assigns 01-JUN-1907. Implicit assignment fails when you attempt other format masks, like MON-DD-YYYY. You can explicitly assign date literals by using the TO_DATE or CAST functions. Only the Oracle proprietary TO_DATE function lets you use apply a format mask other than the default. The syntax variations for the TO_DATE function are date_1 := TO_DATE('01-JUN-07'); -- Default format mask. date_2 := TO_DATE('JUN-01-07','MON-DD-YY'); -- Override format mask. The CAST function can use either of the default format masks discussed earlier in the section, as shown: date_1 := CAST('01-JUN-07' AS DATE); -- Relative format mask. date_2 := CAST('01-JUN-2007' AS DATE); -- Absolute format mask. You can use the TO_CHAR(date_variable, 'MON-DD-YYYY') function to view the fully qualified date. These behaviors in PL/SQL mirror the behaviors in Oracle SQL.
You can implement a named block
in another program's declaration section, you can't implement named blocks anywhere else in a PL/SQL program.
Anonymous blocks can only be implemented
in another program's execution and exception sections.
Anonymous block programs are effective
in some situations, the more common practice is to develop reusable subroutines—functions, procedures, packages, and object types.
Anonymous block identifiers are only accessible
inside a block or nested block
String literals are defined
like character literals, using single quotes. String literals can contain any number of characters up to the maximum value for the data type. You typically use the VARCHAR2 data type, or one of its subtypes. You assign a string literal to a variable using the following syntax: :a := 'some string'; You can also assign a string literal with double quotes inside it by using the following syntax: a := 'some "quoted" string'; The double quotes are treated as normal characters when embedded in single quotes.
Numeric literals are defined
like numbers in most programming languages. The generic numeric literal assignment is done by using the following syntax: a := 2525; You can assign a large number with the following exponent syntax: n := 2525E8; -- This assigns 252,500,000,000 to the variable. You may attempt to assign a number beyond the range of a data type. The numeric overflow or underflow exception is raised when the number is outside the data type's range. You also can assign a float or a double by using the respective syntax: d := 2.0d; -- This assigns a double of 2. f := 2.0f; -- This assigns a float of 2 These assignments only work with their respective type. A d works with a BINARY_DOUBLE, while an f works with a BINARY_FLOAT.
Delimiters act like
mortar because they provide semantic elements like operators and string literal delimiters
Program units can be
named or unnamed blocks.
