What does mean in oracle

What does mean in oracle DEFAULT

2
Fundamentals of PL/SQL

There are six essentials in painting. The first is called spirit; the second, rhythm; the third, thought; the fourth, scenery; the fifth, the brush; and the last is the ink. --Ching Hao

The previous chapter provided an overview of PL/SQL. This chapter focuses on the small-scale aspects of the language. Like every other programming language, PL/SQL has a character set, reserved words, punctuation, datatypes, rigid syntax, and fixed rules of usage and statement formation. You use these basic elements of PL/SQL to represent real-world objects and operations.

This chapter discusses the following topics:

Character Set
Lexical Units
Declarations
PL/SQL Naming Conventions
Scope and Visibility of PL/SQL Identifiers
Variable Assignment
PL/SQL Expressions and Comparisons
Built-In Functions

Character Set

You write a PL/SQL program as lines of text using a specific set of characters. The PL/SQL character set includes

the upper- and lower-case letters .. and ..
the numerals ..
the symbols
tabs, spaces, and carriage returns

PL/SQL is not case sensitive, so lower-case letters are equivalent to corresponding upper-case letters except within string and character literals.

Lexical Units

A line of PL/SQL text contains groups of characters known as lexical units, which can be classified as follows:

delimiters (simple and compound symbols)
identifiers, which include reserved words
literals
comments

To improve readability, you can separate lexical units by spaces. In fact, you must separate adjacent identifiers by a space or punctuation. The following line is not allowed because the reserved words and are joined:

IF x > y THEN high := x; ENDIF; -- not allowed

However, you cannot embed spaces in lexical units except for string literals and comments. For example, the following line is not allowed because the compound symbol for assignment () is split:

count : = count + 1; -- not allowed

To show structure, you can divide lines using carriage returns and indent lines using spaces or tabs. Compare these statements for readability:

IF x>y THEN max:=x;ELSE max:=y;END IF; | IF x > y THEN | max := x; | ELSE | max := y; | END IF;

Delimiters

A delimiter is a simple or compound symbol that has a special meaning to PL/SQL. For example, you use delimiters to represent arithmetic operations such as addition and subtraction. Simple symbols consist of one character. A list follows:

SymbolMeaning

addition operator

attribute indicator

character string delimiter

component selector

division operator

expression or list delimiter

expression or list delimiter

host variable indicator

item separator

multiplication operator

quoted identifier delimiter

relational operator

relational operator

relational operator

remote access indicator

statement terminator

subtraction/negation operator

Compound symbols consist of two characters. A list follows:

SymbolMeaning

assignment operator

association operator

concatenation operator

exponentiation operator

label delimiter (begin)

label delimiter (end)

multi-line comment delimiter (begin)

multi-line comment delimiter (end)

range operator

relational operator

relational operator

relational operator

relational operator

relational operator

relational operator

single-line comment indicator

Identifiers

You use identifiers to name PL/SQL program items and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms, and packages. Some examples of identifiers follow:

X t2 phone# credit_limit LastName oracle$number

An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Other characters such as hyphens, slashes, and spaces are not allowed, as the following examples show:

mine&yours -- not allowed because of ampersand debit-amount -- not allowed because of hyphen on/off -- not allowed because of slash user id -- not allowed because of space

The next examples show that adjoining and trailing dollar signs, underscores, and number signs are allowed:

money$$$tree SN## try_again_

You can use upper, lower, or mixed case to write identifiers. PL/SQL is not case sensitive except within string and character literals. So, if the only difference between identifiers is the case of corresponding letters, PL/SQL considers the identifiers to be the same, as the following example shows:

lastname LastName -- same as lastname LASTNAME -- same as lastname and LastName

The size of an identifier cannot exceed 30 characters. But, every character, including dollar signs, underscores, and number signs, is significant. For example, PL/SQL considers the following identifiers to be different:

lastname last_name

Identifiers should be descriptive. So, avoid obscure names such as . Instead, use meaningful names such as .

Reserved Words

Some identifiers, called reserved words, have a special syntactic meaning to PL/SQL and so should not be redefined. For example, the words and , which bracket the executable part of a block or subprogram, are reserved. As the next example shows, if you try to redefine a reserved word, you get a compilation error:

DECLARE end BOOLEAN; -- not allowed; causes compilation error

However, you can embed reserved words in an identifier, as the following example shows:

DECLARE end_of_game BOOLEAN; -- allowed

Often, reserved words are written in upper case to promote readability. However, like other PL/SQL identifiers, reserved words can be written in lower or mixed case. For a list of reserved words, see Appendix F.

Predefined Identifiers

Identifiers globally declared in package , such as the exception , can be redeclared. However, redeclaring predefined identifiers is error prone because your local declaration overrides the global declaration.

Quoted Identifiers

For flexibility, PL/SQL lets you enclose identifiers within double quotes. Quoted identifiers are seldom needed, but occasionally they can be useful. They can contain any sequence of printable characters including spaces but excluding double quotes. Thus, the following identifiers are valid:

"X+Y" "last name" "on/off switch" "employee(s)" "*** header info ***"

The maximum size of a quoted identifier is 30 characters not counting the double quotes. Though allowed, using PL/SQL reserved words as quoted identifiers is a poor programming practice.

Some PL/SQL reserved words are not reserved by SQL. For example, you can use the PL/SQL reserved word in a statement to name a database column. But, if a SQL statement in your program refers to that column, you get a compilation error, as the following example shows:

SELECT acct, type, bal INTO ... -- causes compilation error

To prevent the error, enclose the uppercase column name in double quotes, as follows:

SELECT acct, "TYPE", bal INTO ...

The column name cannot appear in lower or mixed case (unless it was defined that way in the statement). For example, the following statement is invalid:

SELECT acct, "type", bal INTO ... -- causes compilation error

Alternatively, you can create a view that renames the troublesome column, then use the view instead of the base table in SQL statements.

Literals

A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. The numeric literal and the Boolean literal are examples.

Numeric Literals

Two kinds of numeric literals can be used in arithmetic expressions: integers and reals. An integer literal is an optionally signed whole number without a decimal point. Some examples follow:

030 6 -14 0 +32767

A real literal is an optionally signed whole or fractional number with a decimal point. Several examples follow:

6.6667 0.0 -12.0 3.14159 +8300.00 .5 25.

PL/SQL considers numbers such as and to be reals even though they have integral values.

Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Simply suffix the number with an (or ) followed by an optionally signed integer. A few examples follow:

2E5 1.0E-7 3.14159e0 -1E38 -9.5e-3

stands for "times ten to the power of." As the next example shows, the number after is the power of ten by which the number before must be multiplied (the double asterisk () is the exponentiation operator):

5E3 = 5 * 10**3 = 5 * 1000 = 5000

The number after also corresponds to the number of places the decimal point shifts. In the last example, the implicit decimal point shifted three places to the right. In this example, it shifts three places to the left:

5E-3 = 5 * 10**-3 = 5 * 0.001 = 0.005

As the following example shows, if the value of a numeric literal falls outside the range .. , you get a compilation error:

DECLARE n NUMBER; BEGIN n := 10E127; -- causes a 'numeric overflow or underflow' error

Character Literals

A character literal is an individual character enclosed by single quotes (apostrophes). Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. Some examples follow:

'Z' '%' '7' ' ' 'z' '('

PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals and to be different. Also, the character literals .. are not equivalent to integer literals but can be used in arithmetic expressions because they are implicitly convertible to integers.

String Literals

A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotes. Several examples follow:

'Hello, world!' 'XYZ Corporation' '10-NOV-91' 'He said "Life is like licking honey from a thorn."' '$1,000,000'

All string literals except the null string ('') have datatype .

Given that apostrophes (single quotes) delimit string literals, how do you represent an apostrophe within a string? As the next example shows, you write two single quotes, which is not the same as writing a double quote:

'Don''t leave without saving your work.'

PL/SQL is case sensitive within string literals. For example, PL/SQL considers the following literals to be different:

'baker' 'Baker'

Boolean Literals

Boolean literals are the predefined values , , and (which stands for a missing, unknown, or inapplicable value). Remember, Boolean literals are values, not strings. For example, is no less a value than the number .

Datetime Literals

Datetime literals have various formats depending on the datatype. For example:

DECLARE d1 DATE := DATE '1998-12-25'; t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01'; t2 TIMESTAMP WITH TIME ZONE := TIMESTAMP '1997-01-31 09:26:56.66 +02:00'; -- Three years and two months -- (For greater precision, we would use the day-to-second interval) i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH; -- Five days, four hours, three minutes, two and 1/100 seconds i2 INTERVAL DAY TO SECOND := INTERVAL '5 04:03:02.01' DAY TO SECOND; ...

You can also specify whether a given interval value is or . For example, produces a value of type by default. You can specify the type of the interval using the formats:

For details on the syntax for the date and time types, see the Oracle9i SQL Reference. For examples of performing date/time arithmetic, see Oracle9i Application Developer's Guide - Fundamentals.

Comments

The PL/SQL compiler ignores comments, but you should not. Adding comments to your program promotes readability and aids understanding. Generally, you use comments to describe the purpose and use of each code segment. PL/SQL supports two comment styles: single-line and multi-line.

Single-Line Comments

Single-line comments begin with a double hyphen () anywhere on a line and extend to the end of the line. A few examples follow:

-- begin processing SELECT sal INTO salary FROM emp -- get current salary WHERE empno = emp_id; bonus := salary * 0.15; -- compute bonus amount

Notice that comments can appear within a statement at the end of a line.

While testing or debugging a program, you might want to disable a line of code. The following example shows how you can "comment-out" the line:

-- DELETE FROM emp WHERE comm IS NULL;

Multi-line Comments

Multi-line comments begin with a slash-asterisk (), end with an asterisk-slash (), and can span multiple lines. Some examples follow:

BEGIN ... /* Compute a 15% bonus for top-rated employees. */ IF rating > 90 THEN bonus := salary * 0.15 /* bonus is based on salary */ ELSE bonus := 0; END IF; ... /* The following line computes the area of a circle using pi, which is the ratio between the circumference and diameter. */ area := pi * radius**2; END;

You can use multi-line comment delimiters to comment-out whole sections of code, as the following example shows:

/* LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; ... END LOOP; */

Restrictions on Comments

You cannot nest comments. Also, you cannot use single-line comments in a PL/SQL block that will be processed dynamically by an Oracle Precompiler program because end-of-line characters are ignored. As a result, single-line comments extend to the end of the block, not just to the end of a line. So, use multi-line comments instead.

Declarations

Your program stores values in variables and constants. As the program executes, the values of variables can change, but the values of constants cannot.

You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it.

A couple of examples follow:

birthday DATE; emp_count SMALLINT := 0;

The first declaration names a variable of type . The second declaration names a variable of type and uses the assignment operator to assign an initial value of zero to the variable.

The next examples show that the expression following the assignment operator can be arbitrarily complex and can refer to previously initialized variables:

pi REAL := 3.14159; radius REAL := 1; area REAL := pi * radius**2;

By default, variables are initialized to . So, these declarations are equivalent:

birthday DATE; birthday DATE := NULL;

In the declaration of a constant, the keyword must precede the type specifier, as the following example shows:

credit_limit CONSTANT REAL := 5000.00;

This declaration names a constant of type and assigns an initial (also final) value of 5000 to the constant. A constant must be initialized in its declaration. Otherwise, you get a compilation error when the declaration is elaborated. (The processing of a declaration by the PL/SQL compiler is called elaboration.)

Using DEFAULT

You can use the keyword instead of the assignment operator to initialize variables. For example, the declaration

blood_type CHAR := 'O';

can be rewritten as follows:

blood_type CHAR DEFAULT 'O';

Use for variables that have a typical value. Use the assignment operator for variables (such as counters and accumulators) that have no typical value. A couple of examples follow:

hours_worked INTEGER DEFAULT 40; employee_count INTEGER := 0;

You can also use to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

Using NOT NULL

Besides assigning an initial value, declarations can impose the constraint, as the following example shows:

acct_id INTEGER(4) NOT NULL := 9999;

You cannot assign nulls to a variable defined as . If you try, PL/SQL raises the predefined exception . The constraint must be followed by an initialization clause. For example, the following declaration is not allowed:

acct_id INTEGER(5) NOT NULL; -- not allowed; not initialized

PL/SQL provide subtypes and that are predefined as . For instance, the following declarations are equivalent:

emp_count NATURAL NOT NULL := 0; emp_count NATURALN := 0;

In and declarations, the type specifier must be followed by an initialization clause. Otherwise, you get a compilation error. For example, the following declaration is not allowed:

line_items POSITIVEN; -- not allowed; not initialized

Using %TYPE

The attribute provides the datatype of a variable or database column. In the following example, provides the datatype of a variable:

credit REAL(7,2); debit credit%TYPE;

Variables declared using are treated like those declared using a datatype specifier. For example, given the previous declarations, PL/SQL treats like a variable. The next example shows that a declaration can include an initialization clause:

balance NUMBER(7,2); minimum_balance balance%TYPE := 10.00;

The attribute is particularly useful when declaring variables that refer to database columns. You can reference a table and column, or you can reference an owner, table, and column, as in

my_dname scott.dept.dname%TYPE;

Using to declare has two advantages. First, you need not know the exact datatype of . Second, if the database definition of changes, the datatype of changes accordingly at run time.

However, variables do not inherit the column constraint. In the next example, even though the database column is defined as , you can assign a null to the variable :

DECLARE my_empno emp.empno%TYPE; ... BEGIN my_empno := NULL; -- this works

Using %ROWTYPE

The attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table or fetched from a cursor or strongly typed cursor variable. In the example below, you declare two records. The first record stores a row selected from the table. The second record stores a row fetched from cursor .

DECLARE emp_rec emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec c1%ROWTYPE;

Columns in a row and corresponding fields in a record have the same names and datatypes. However, fields in a record do not inherit the column constraint.

In the following example, you select column values into record :

BEGIN SELECT * INTO emp_rec FROM emp WHERE ...

The column values returned by the statement are stored in fields. To reference a field, you use dot notation. For example, you might reference the field as follows:

IF emp_rec.deptno = 20 THEN ...

Also, you can assign the value of an expression to a specific field, as the following examples show:

emp_rec.ename := 'JOHNSON'; emp_rec.sal := emp_rec.sal * 1.15;

In the final example, you use to define a packaged cursor:

CREATE PACKAGE emp_actions AS CURSOR c1 RETURN emp%ROWTYPE; -- declare cursor specification ... END emp_actions; CREATE PACKAGE BODY emp_actions AS CURSOR c1 RETURN emp%ROWTYPE IS -- define cursor body SELECT * FROM emp WHERE sal > 3000; ... END emp_actions;

Aggregate Assignment

A declaration cannot include an initialization clause. However, there are two ways to assign values to all fields in a record at once. First, PL/SQL allows aggregate assignment between entire records if their declarations refer to the same table or cursor. For example, the following assignment is allowed:

DECLARE dept_rec1 dept%ROWTYPE; dept_rec2 dept%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec3 c1%ROWTYPE; BEGIN ... dept_rec1 := dept_rec2;

However, because is based on a table and is based on a cursor, the following assignment is not allowed:

dept_rec2 := dept_rec3; -- not allowed

Second, you can assign a list of column values to a record by using the or statement, as the example below shows. The column names must appear in the order in which they were defined by the or statement.

DECLARE dept_rec dept%ROWTYPE; ... BEGIN SELECT * INTO dept_rec FROM dept WHERE deptno = 30; ... END;

However, you cannot assign a list of column values to a record by using an assignment statement. So, the following syntax is not allowed:

record_name := (value1, value2, value3, ...); -- not allowed

Using Aliases

Select-list items fetched from a cursor associated with must have simple names or, if they are expressions, must have aliases. In the following example, you use an alias called :

-- available online in file 'examp4' DECLARE CURSOR my_cursor IS SELECT sal + NVL(comm, 0) wages, ename FROM emp; my_rec my_cursor%ROWTYPE; BEGIN OPEN my_cursor; LOOP FETCH my_cursor INTO my_rec; EXIT WHEN my_cursor%NOTFOUND; IF my_rec.wages > 2000 THEN INSERT INTO temp VALUES (NULL, my_rec.wages, my_rec.ename); END IF; END LOOP; CLOSE my_cursor; END;

Restrictions on Declarations

PL/SQL does not allow forward references. You must declare a variable or constant before referencing it in other statements, including other declarative statements. For example, the following declaration of is not allowed:

maxi INTEGER := 2 * mini; -- not allowed mini INTEGER := 15;

However, PL/SQL does allow the forward declaration of subprograms. For more information, see "Declaring PL/SQL Subprograms".

Some languages allow you to declare a list of variables that have the same datatype. PL/SQL does not allow this. For example, the following declaration is not allowed:

i, j, k SMALLINT; -- not allowed

You must declare each variable separately:

i SMALLINT; j SMALLINT; k SMALLINT;

PL/SQL Naming Conventions

The same naming conventions apply to all PL/SQL program items and units including constants, variables, cursors, cursor variables, exceptions, procedures, functions, and packages. Names can be simple, qualified, remote, or both qualified and remote. For example, you might use the procedure name in any of the following ways:

raise_salary(...); -- simple emp_actions.raise_salary(...); -- qualified [email protected](...); -- remote [email protected](...); -- qualified and remote

In the first case, you simply use the procedure name. In the second case, you must qualify the name using dot notation because the procedure is stored in a package called . In the third case, using the remote access indicator (), you reference the database link because the procedure is stored in a remote database. In the fourth case, you qualify the procedure name and reference a database link.

Synonyms

You can create synonyms to provide location transparency for remote schema objects such as tables, sequences, views, standalone subprograms, packages, and object types. However, you cannot create synonyms for items declared within subprograms or packages. That includes constants, variables, cursors, cursor variables, exceptions, and packaged subprograms.

Scoping

Within the same scope, all declared identifiers must be unique. So, even if their datatypes differ, variables and parameters cannot share the same name. In the following example, the second declaration is not allowed:

DECLARE valid_id BOOLEAN; valid_id VARCHAR2(5); -- not allowed duplicate identifier

For the scoping rules that apply to identifiers, see "Scope and Visibility of PL/SQL Identifiers".

Case Sensitivity

Like all identifiers, the names of constants, variables, and parameters are not case sensitive. For instance, PL/SQL considers the following names to be the same:

DECLARE zip_code INTEGER; Zip_Code INTEGER; -- same as zip_code

Name Resolution

In potentially ambiguous SQL statements, the names of database columns take precedence over the names of local variables and formal parameters. For example, the following statement removes all employees from the table, not just ', because Oracle assumes that both in the clause refer to the database column:

DECLARE ename VARCHAR2(10) := 'KING'; BEGIN DELETE FROM emp WHERE ename = ename; ...

In such cases, to avoid ambiguity, prefix the names of local variables and formal parameters with , as follows:

DECLARE my_ename VARCHAR2(10);

Or, use a block label to qualify references, as in

<<main>> DECLARE ename VARCHAR2(10) := 'KING'; BEGIN DELETE FROM emp WHERE ename = main.ename; ...

The next example shows that you can use a subprogram name to qualify references to local variables and formal parameters:

FUNCTION bonus (deptno IN NUMBER, ...) RETURN REAL IS job CHAR(10); BEGIN SELECT ... WHERE deptno = bonus.deptno AND job = bonus.job; ...

For a full discussion of name resolution, see Appendix D.

Scope and Visibility of PL/SQL Identifiers

References to an identifier are resolved according to its scope and visibility. The scope of an identifier is that region of a program unit (block, subprogram, or package) from which you can reference the identifier. An identifier is visible only in the regions from which you can reference the identifier using an unqualified name. Figure 2-1 shows the scope and visibility of a variable named , which is declared in an enclosing block, then redeclared in a sub-block.

Identifiers declared in a PL/SQL block are considered local to that block and global to all its sub-blocks. If a global identifier is redeclared in a sub-block, both identifiers remain in scope. Within the sub-block, however, only the local identifier is visible because you must use a qualified name to reference the global identifier.

Although you cannot declare an identifier twice in the same block, you can declare the same identifier in two different blocks. The two items represented by the identifier are distinct, and any change in one does not affect the other. However, a block cannot reference identifiers declared in other blocks at the same level because those identifiers are neither local nor global to the block.

Figure 2-1 Scope and Visibility

Text description of pls81007_scope_and_visibility.gif follows
Text description of the illustration pls81007_scope_and_visibility.gif

The example below illustrates the scope rules. Notice that the identifiers declared in one sub-block cannot be referenced in the other sub-block. That is because a block cannot reference identifiers declared in other blocks nested at the same level.

DECLARE a CHAR; b REAL; BEGIN -- identifiers available here: a (CHAR), b DECLARE a INTEGER; c REAL; BEGIN -- identifiers available here: a (INTEGER), b, c END; DECLARE d REAL; BEGIN -- identifiers available here: a (CHAR), b, d END; -- identifiers available here: a (CHAR), b END;

Recall that global identifiers can be redeclared in a sub-block, in which case the local declaration prevails and the sub-block cannot reference the global identifier unless you use a qualified name. The qualifier can be the label of an enclosing block, as the following example shows:

<<outer>> DECLARE birthdate DATE; BEGIN DECLARE birthdate DATE; BEGIN ... IF birthdate = outer.birthdate THEN ... END; ... END;

As the next example shows, the qualifier can also be the name of an enclosing subprogram:

PROCEDURE check_credit (...) IS rating NUMBER; FUNCTION valid (...) RETURN BOOLEAN IS rating NUMBER; BEGIN ... IF check_credit.rating < 3 THEN ... END; BEGIN ... END;

However, within the same scope, a label and a subprogram cannot have the same name.

Variable Assignment

Variables and constants are initialized every time a block or subprogram is entered. By default, variables are initialized to . Unless you expressly initialize a variable, its value is undefined:

DECLARE count INTEGER; BEGIN -- COUNT began with a value of NULL. -- Thus the expression 'COUNT + 1' is also null. -- So after this assignment, COUNT is still NULL. count := count + 1;

To avoid unexpected results, never reference a variable before you assign it a value.

You can use assignment statements to assign values to a variable. For example, the following statement assigns a new value to the variable , overwriting its old value:

bonus := salary * 0.15;

The expression following the assignment operator can be arbitrarily complex, but it must yield a datatype that is the same as or convertible to the datatype of the variable.

Assigning Boolean Values

Only the values , , and can be assigned to a Boolean variable. For example, given the declaration

DECLARE done BOOLEAN;

the following statements are allowed:

BEGIN done := FALSE; WHILE NOT done LOOP ... END LOOP;

When applied to an expression, the relational operators return a Boolean value. So, the following assignment is allowed:

done := (count > 500);

Assigning a SQL Query Result to a PL/SQL Variable

You can use the statement to have Oracle assign values to a variable. For each item in the select list, there must be a corresponding, type-compatible variable in the list. An example follows:

DECLARE emp_id emp.empno%TYPE; emp_name emp.ename%TYPE; wages NUMBER(7,2); BEGIN -- assign a value to emp_id here SELECT ename, sal + comm INTO emp_name, wages FROM emp WHERE empno = emp_id; ... END;

However, you cannot select column values into a Boolean variable.

PL/SQL Expressions and Comparisons

Expressions are constructed using operands and operators. An operand is a variable, constant, literal, or function call that contributes a value to an expression. An example of a simple arithmetic expression follows:

-X / 2 + 3

Unary operators such as the negation operator () operate on one operand; binary operators such as the division operator () operate on two operands. PL/SQL has no ternary operators.

The simplest expressions consist of a single variable, which yields a value directly. PL/SQL evaluates (finds the current value of) an expression by combining the values of the operands in ways specified by the operators. This always yields a single value and datatype. PL/SQL determines the datatype by examining the expression and the context in which it appears.

Operator Precedence

The operations within an expression are done in a particular order depending on their precedence (priority). Table 2-1 shows the default order of operations from first to last (top to bottom).

Table 2-1 Order of Operations
OperatorOperation

exponentiation

,

identity, negation

,

multiplication, division

, ,

addition, subtraction, concatenation

, , , , , , , , , , , ,

comparison

logical negation

conjunction

inclusion



Operators with higher precedence are applied first. In the example below, both expressions yield 8 because division has a higher precedence than addition. Operators with the same precedence are applied in no particular order.

5 + 12 / 4 12 / 4 + 5

You can use parentheses to control the order of evaluation. For example, the following expression yields 7, not 11, because parentheses override the default operator precedence:

(8 + 6) / 2

In the next example, the subtraction is done before the division because the most deeply nested subexpression is always evaluated first:

100 + (20 / 5 + (7 - 3))

The following example shows that you can always use parentheses to improve readability, even when they are not needed:

(salary * 0.05) + (commission * 0.25)

Logical Operators

The logical operators , , and follow the tri-state logic shown in Table 2-2. and are binary operators; is a unary operator.

Table 2-2 Logic Truth Table
xyx AND yx OR yNOT x


As the truth table shows, returns only if both its operands are true. On the other hand, returns if either of its operands is true. returns the opposite value (logical negation) of its operand. For example, returns .

returns because nulls are indeterminate. It follows that if you apply the operator to a null, the result is also indeterminate. Be careful. Nulls can cause unexpected results; see "Handling Null Values in Comparisons and Conditional Statements".

Order of Evaluation

When you do not use parentheses to specify the order of evaluation, operator precedence determines the order. Compare the following expressions:

NOT (valid AND done) | NOT valid AND done

If the Boolean variables and have the value , the first expression yields . However, the second expression yields because has a higher precedence than . Therefore, the second expression is equivalent to:

(NOT valid) AND done

In the following example, notice that when has the value , the whole expression yields regardless of the value of :

valid AND done

Likewise, in the next example, when has the value , the whole expression yields regardless of the value of :

valid OR done

Short-Circuit Evaluation

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined. This lets you write expressions that might otherwise cause an error. Consider the following expression:

DECLARE ... on_hand INTEGER; on_order INTEGER; BEGIN .. IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN ... END IF; END;

When the value of is zero, the left operand yields , so PL/SQL need not evaluate the right operand. If PL/SQL were to evaluate both operands before applying the operator, the right operand would cause a division by zero error. In any case, it is a poor programming practice to rely on short-circuit evaluation.

Comparison Operators

Comparison operators compare one expression to another. The result is always true, false, or null. Typically, you use comparison operators in conditional control statements and in the clause of SQL data manipulation statements. Here are a couple of examples:

IF quantity_on_hand > 0 THEN UPDATE inventory SET quantity = quantity - 1 WHERE part_number = item_number; ELSE ... END IF;

Relational Operators

The relational operators allow you to compare arbitrarily complex expressions. The following list gives the meaning of each operator:

OperatorMeaning

equal to

, , ,

not equal to

less than

greater than

less than or equal to

greater than or equal to

IS NULL Operator

The operator returns the Boolean value if its operand is null or if it is not null. Comparisons involving nulls always yield . So, test for nullity (the state of being null), as follows:

IF variable IS NULL THEN ...
LIKE Operator

You use the operator to compare a character, string, or value to a pattern. Case is significant. returns the Boolean value if the patterns match or if they do not match.

The patterns matched by can include two special-purpose characters called wildcards. An underscore () matches exactly one character; a percent sign () matches zero or more characters. For example, if the value of is , the following expression is true:

ename LIKE 'J%SON'
BETWEEN Operator

The operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value." For example, the following expression is false:

45 BETWEEN 38 AND 44
IN Operator

The operator tests set membership. It means "equal to any member of." The set can contain nulls, but they are ignored. For example, the following statement does not delete rows in which the column is null:

DELETE FROM emp WHERE ename IN (NULL, 'KING', 'FORD');

Furthermore, expressions of the form

value NOT IN set

yield if the set contains a null. For example, instead of deleting rows in which the column is not null and not , the following statement deletes no rows:

DELETE FROM emp WHERE ename NOT IN (NULL, 'KING');

Concatenation Operator

Double vertical bars () serve as the concatenation operator, which appends one string (, , , or the equivalent Unicode-enabled type) to another. For example, the expression

'suit' || 'case'

returns the following value:

'suitcase'

If both operands have datatype , the concatenation operator returns a value. If either operand is a value, the operator returns a temporary CLOB. Otherwise, it returns a value.

Boolean Expressions

PL/SQL lets you compare variables and constants in both SQL and procedural statements. These comparisons, called Boolean expressions, consist of simple or complex expressions separated by relational operators. Often, Boolean expressions are connected by the logical operators , , and . A Boolean expression always yields , , or .

In a SQL statement, Boolean expressions let you specify the rows in a table that are affected by the statement. In a procedural statement, Boolean expressions are the basis for conditional control. There are three kinds of Boolean expressions: arithmetic, character, and date.

Boolean Arithmetic Expressions

You can use the relational operators to compare numbers for equality or inequality. Comparisons are quantitative; that is, one number is greater than another if it represents a larger quantity. For example, given the assignments

number1 := 75; number2 := 70;

the following expression is true:

number1 > number2

Boolean Character Expressions

You can compare character values for equality or inequality. By default, comparisons are based on the binary values of each byte in the string.

For example, given the assignments

string1 := 'Kathy'; string2 := 'Kathleen';

the following expression is true:

string1 > string2

By setting the initialization parameter , you can make comparisons use use the collating sequence identified by the NLS_SORT initialization parameter. A collating sequence is an internal ordering of the character set in which a range of numeric codes represents the individual characters. One character value is greater than another if its internal numeric value is larger. Each language might have different rules about where such characters occur in the collating sequence. For example, an accented letter might be sorted differently depending on the database character set, even though the binary value is the same in each case.

There are semantic differences between the and base types that come into play when you compare character values. For more information, see Appendix B.

Many types can be converted to character types. For example, you can compare, assign, and do other character operations using variables. For details on the possible conversions, see "Character Types".

Boolean Date Expressions

You can also compare dates. Comparisons are chronological; that is, one date is greater than another if it is more recent. For example, given the assignments

date1 := '01-JAN-91'; date2 := '31-DEC-90';

the following expression is true:

date1 > date2
Guidelines for PL/SQL Boolean Expressions
  • In general, do not compare real numbers for exact equality or inequality. Real numbers are stored as approximate values. So, for example, the following condition might not yield : count := 1; IF count = 1.0 THEN ... END IF;
  • It is a good idea to use parentheses when doing comparisons. For example, the following expression is not allowed because yields a Boolean value, which cannot be compared with the number 500: 100 < tax < 500 -- not allowed

    The debugged version follows:

    (100 < tax) AND (tax < 500)
  • A Boolean variable is itself either true or false. So, comparisons with the Boolean values and are redundant. For example, assuming the variable is of type , the statement WHILE NOT (done = TRUE) LOOP ... END LOOP;

    can be simplified as follows:

    WHILE NOT done LOOP ... END LOOP;
  • Using values with comparison operators, or functions such as and , can result in creation of temporary LOBs. You might need to make sure your temporary tablespace is large enough to handle these temporary LOBs. For details, see the "Modeling and Design" chapter in Oracle9i Application Developer's Guide - Large Objects (LOBs).

CASE Expressions

A expression selects a result from one or more alternatives, and returns the result. The expression uses a selector, an expression whose value determines which alternative to return. A expression has the following form:

CASE selector WHEN expression1 THEN result1 WHEN expression2 THEN result2 ... WHEN expressionN THEN resultN [ELSE resultN+1] END;

The selector is followed by one or more clauses, which are checked sequentially. The value of the selector determines which clause is executed. The first clause that matches the value of the selector determines the result value, and subsequent clauses are not evaluated. An example follows:

DECLARE grade CHAR(1) := 'B'; appraisal VARCHAR2(20); BEGIN appraisal := CASE grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' WHEN 'D' THEN 'Fair' WHEN 'F' THEN 'Poor' ELSE 'No such grade' END; END;

The optional clause works similarly to the clause in an statement. If the value of the selector is not one of the choices covered by a clause, the clause is executed. If no clause is provided and none of the clauses are matched, the expression returns .

An alternative to the expression is the statement, where each clause can be an entire PL/SQL block. For details, see "CASE Statement".

Searched CASE Expression

PL/SQL also provides a searched expression, which has the form:

CASE WHEN search_condition1 THEN result1 WHEN search_condition2 THEN result2 ... WHEN search_conditionN THEN resultN [ELSE resultN+1] END;

A searched expression has no selector. Each clause contains a search condition that yields a Boolean value, which lets you test different variables or multiple conditions in a single clause. An example follows:

DECLARE grade CHAR(1); appraisal VARCHAR2(20); BEGIN ... appraisal := CASE WHEN grade = 'A' THEN 'Excellent' WHEN grade = 'B' THEN 'Very Good' WHEN grade = 'C' THEN 'Good' WHEN grade = 'D' THEN 'Fair' WHEN grade = 'F' THEN 'Poor' ELSE 'No such grade' END; ... END;

The search conditions are evaluated sequentially. The Boolean value of each search condition determines which clause is executed. If a search condition yields , its clause is executed. After any clause is executed, subsequent search conditions are not evaluated. If none of the search conditions yields , the optional clause is executed. If no clause is executed and no clause is supplied, the value of the expression is .

Handling Null Values in Comparisons and Conditional Statements

When working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

  • Comparisons involving nulls always yield
  • Applying the logical operator to a null yields
  • In conditional control statements, if the condition yields , its associated sequence of statements is not executed
  • If the expression in a simple statement or expression yields , it cannot be matched by using . In this case, you would need to use the searched case syntax and test .

In the example below, you might expect the sequence of statements to execute because and seem unequal. But, nulls are indeterminate. Whether or not is equal to is unknown. Therefore, the condition yields and the sequence of statements is bypassed.

x := 5; y := NULL; ... IF x != y THEN -- yields NULL, not TRUE sequence_of_statements; -- not executed END IF;

In the next example, you might expect the sequence of statements to execute because and seem equal. But, again, that is unknown, so the condition yields and the sequence of statements is bypassed.

a := NULL; b := NULL; ... IF a = b THEN -- yields NULL, not TRUE sequence_of_statements; -- not executed END IF;

NOT Operator

Recall that applying the logical operator to a null yields . Thus, the following two statements are not always equivalent:

IF x > y THEN | IF NOT x > y THEN high := x; | high := y; ELSE | ELSE high := y; | high := x; END IF; | END IF;

The sequence of statements in the clause is executed when the condition yields or . If neither nor is null, both statements assign the same value to . However, if either or is null, the first statement assigns the value of to , but the second statement assigns the value of to .

Zero-Length Strings

PL/SQL treats any zero-length string like a null. This includes values returned by character functions and Boolean expressions. For example, the following statements assign nulls to the target variables:

null_string := TO_CHAR(''); zip_code := SUBSTR(address, 25, 0); valid := (name != '');

So, use the operator to test for null strings, as follows:

IF my_string IS NULL THEN ...

Concatenation Operator

The concatenation operator ignores null operands. For example, the expression

'apple' || NULL || NULL || 'sauce'

returns the following value:

'applesauce'

Functions

If a null argument is passed to a built-in function, a null is returned except in the following cases.

The function compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column is null, returns the value 1000:

SELECT DECODE(rating, NULL, 1000, 'C', 2000, 'B', 4000, 'A', 5000) INTO credit_limit FROM accts WHERE acctno = my_acctno;

The function returns the value of its second argument if its first argument is null. In the example below, if is null, returns the value of . Otherwise, returns the value of :

start_date := NVL(hire_date, SYSDATE);

The function returns the value of its first argument if its second argument is null, whether the optional third argument is present or not. For instance, after the assignment

new_string := REPLACE(old_string, NULL, my_string);

the values of and are the same.

If its third argument is null, returns its first argument with every occurrence of its second argument removed. For example, after the assignments

syllabified_name := 'Gold-i-locks'; name := REPLACE(syllabified_name, '-', NULL);

the value of is

If its second and third arguments are null, simply returns its first argument.

Built-In Functions

PL/SQL provides many powerful functions to help you manipulate data. These built-in functions fall into the following categories:

error reporting
number
character
datatype conversion
date
object reference
miscellaneous

Table 2-3 shows the functions in each category. For descriptions of the error-reporting functions, see Chapter 13. For descriptions of the other functions, see Oracle9i SQL Reference.

Except for the error-reporting functions and , you can use all the functions in SQL statements. Also, except for the object-reference functions , , and and the miscellaneous functions , , and , you can use all the functions in procedural statements.

Although the SQL aggregate functions (such as and ) and the SQL analytic functions (such as and ) are not built into PL/SQL, you can use them in SQL statements (but not in procedural statements).

Table 2-3 Built-In Functions
ErrorNumberCharacterConversionDateObj RefMisc

FROM_TZ

Sours: https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/02_funds.htm

This chapter describes methods of manipulating individual data items. Standard arithmetic operators such as addition and subtraction are discussed, as well as less common functions such as absolute value and string length. Topics include:

An operator manipulates individual data items and returns a result. The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL. Tables in this section list SQL operators.

There are two general classes of operators:

unary  

A unary operator operates on only one operand. A unary operator typically appears with its operand in this format:  

 

 

binary  

A binary operator operates on two operands. A binary operator appears with its operands in this format:  

 

 

Other operators with special formats accept more than two operands. If an operator is given a null operand, the result is always null. The only operator that does not follow this rule is concatenation (||).

Precedence is the order in which Oracle evaluates different operators in the same expression. When evaluating an expression containing multiple operators, Oracle evaluates operators with higher precedence before evaluating those with lower precedence. Oracle evaluates operators with equal precedence from left to right within an expression.

Table 3-1 lists the levels of precedence among SQL operators from high to low. Operators listed on the same line have the same precedence.

   

+, -  

identity, negation  

*, /  

multiplication, division  

+, -, ||  

addition, subtraction, concatenation  

=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN  

comparison  

NOT  

exponentiation, logical negation  

AND  

conjunction  

OR  

disjunction  

In the following expression multiplication has a higher precedence than addition, so Oracle first multiplies 2 by 3 and then adds the result to 1.

1+2*3

You can use parentheses in an expression to override operator precedence. Oracle evaluates expressions inside parentheses before evaluating those outside.

SQL also supports set operators (UNION, UNION ALL, INTERSECT, and MINUS), which combine sets of rows returned by queries, rather than individual data items. All set operators have equal precedence.

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value. Some of these operators are also used in date arithmetic. Table 3-2 lists arithmetic operators.

     

+ -  

Denotes a positive or negative expression. These are unary operators.  

 

* /  

Multiplies, divides. These are binary operators.  

 

+ -  

Adds, subtracts. These are binary operators.  

 

Do not use two consecutive minus signs with no separation (--) in arithmetic expressions to indicate double negation or the subtraction of a negative value. The characters -- are used to begin comments within SQL statements. You should separate consecutive minus signs with a space or a parenthesis. For more information on comments within SQL statements, see "Comments".

The concatenation operator manipulates character strings. Table 3-3 describes the concatenation operator.

     

||  

Concatenates character strings.  

 

The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to 2000 characters. If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 4000 characters. Trailing blanks in character strings are preserved by concatenation, regardless of the strings' datatypes. For more information on the differences between the CHAR and VARCHAR2 datatypes, see "Character Datatypes".

On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.

Although Oracle treats zero-length character strings as nulls, concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings. However, this may not continue to be true in future versions of Oracle. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length string.

This example creates a table with both CHAR and VARCHAR2 columns, inserts values both with and without trailing blanks, and then selects these values, concatenating them. Note that for both CHAR and VARCHAR2 columns, the trailing blanks are preserved.

CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6), col3 VARCHAR2(6), col4 CHAR(6) ); Table created. INSERT INTO tab1 (col1, col2, col3, col4) VALUES ('abc', 'def ', 'ghi ', 'jkl'); 1 row created. SELECT col1||col2||col3||col4 "Concatenation" FROM tab1; Concatenation ------------------------ abcdef ghi jkl

Comparison operators compare one expression with another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN. For information on conditions, see "Conditions". Table 3-4 lists comparison operators.

     
 

Equality test.  

 
 

Inequality test. Some forms of the inequality operator may be unavailable on some platforms.  

 
 

"Greater than" and "less than" tests.  

 
 

"Greater than or equal to" and "less than or equal to" tests.  

 
 

"Equal to any member of" test. Equivalent to "".  

 
 

Equivalent to "!=ALL". Evaluates to FALSE if any member of the set is NULL.  

 
 

Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=.

Evaluates to FALSE if the query returns no rows.  

 
 

Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=.

Evaluates to TRUE if the query returns no rows.  

 
 

[Not] greater than or equal to x and less than or equal to y.  

 
 

TRUE if a subquery returns at least one row.  

 
 

TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character, excepting percent (%) and underbar (_) may follow ESCAPE; a wildcard character is treated as a literal if preceded by the character designated as the escape character.  

See "LIKE Operator".

 
 

Tests for nulls. This is the only operator that you should use to test for nulls. See "Nulls".  

 

Additional information on the NOT IN and LIKE operators appears in the sections that follow.

If any item in the list following a NOT IN operation is null, all rows evaluate to UNKNOWN (and no rows are returned). For example, the following statement returns the string 'TRUE' for each row:

SELECT 'TRUE' FROM emp WHERE deptno NOT IN (5,15);

However, the following statement returns no rows:

SELECT 'TRUE' FROM emp WHERE deptno NOT IN (5,15,null);

The above example returns no rows because the WHERE clause condition evaluates to:

deptno != 5 AND deptno != 15 AND deptno != null

Because all conditions that compare a null result in a null, the entire expression results in a null. This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.

The LIKE operator is used in character string comparisons with pattern matching. The syntax for a condition using the LIKE operator is shown in this diagram:

where:

char1  

is a value to be compared with a pattern. This value can have datatype CHAR or VARCHAR2.  

NOT  

logically inverts the result of the condition, returning FALSE if the condition evaluates to TRUE and TRUE if it evaluates to FALSE.  

char2  

is the pattern to which char1 is compared. The pattern is a value of datatype CHAR or VARCHAR2 and can contain the special pattern matching characters % and _.  

ESCAPE  

identifies a single character as the escape character. The escape character can be used to cause Oracle to interpret % or _ literally, rather than as a special character, in the pattern.

If you wish to search for strings containing an escape character, you must specify this character twice. For example, if the escape character is '/', to search for the string 'client/server', you must specify, 'client//server'.  

While the equal (=) operator exactly matches one character value to another, the LIKE operator matches a portion of one character value to another by searching the first value for the pattern specified by the second. Note that blank padding is not used for LIKE comparisons.

With the LIKE operator, you can compare a value to a pattern rather than to a constant. The pattern can only appear after the LIKE keyword. For example, you can issue the following query to find the salaries of all employees with names beginning with 'SM':

SELECT sal FROM emp WHERE ename LIKE 'SM%';

The following query uses the = operator, rather than the LIKE operator, to find the salaries of all employees with the name 'SM%':

SELECT sal FROM emp WHERE ename = 'SM%';

The following query finds the salaries of all employees with the name 'SM%'. Oracle interprets 'SM%' as a text literal, rather than as a pattern, because it precedes the LIKE operator:

SELECT sal FROM emp WHERE 'SM%' LIKE ename;

Patterns usually use special characters that Oracle matches with different characters in the value:

  • An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.
  • A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. Note that the pattern '%' cannot match a null.

Case is significant in all conditions comparing character expressions including the LIKE and equality (=) operators. You can use the UPPER() function to perform a case-insensitive match, as in this condition:

UPPER(ename) LIKE 'SM%'

When LIKE is used to search an indexed column for a pattern, Oracle can use the index to improve the statement's performance if the leading character in the pattern is not "%" or "_". In this case, Oracle can scan the index by this leading character. If the first character in the pattern is "%" or "_", the index cannot improve the query's performance because Oracle cannot scan the index.

This condition is true for all ENAME values beginning with "MA":

ename LIKE 'MA%'

All of these ENAME values make the condition TRUE:

MARTIN, MA, MARK, MARY

Case is significant, so ENAME values beginning with "Ma," "ma," and "mA" make the condition FALSE.

Consider this condition:

ename LIKE 'SMITH_'

This condition is true for these ENAME values:

SMITHE, SMITHY, SMITHS

This condition is false for 'SMITH', since the special character "_" must match exactly one character of the ENAME value.

You can include the actual characters "%" or "_" in the pattern by using the ESCAPE option. The ESCAPE option identifies the escape character. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character.

To search for any employees with the pattern 'A_B' in their name:

SELECT ename FROM emp WHERE ename LIKE '%A\_B%' ESCAPE '\';

The ESCAPE option identifies the backslash (\) as the escape character. In the pattern, the escape character precedes the underscore (_). This causes Oracle to interpret the underscore literally, rather than as a special pattern matching character.

If a pattern does not contain the "%" character, the condition can be TRUE only if both operands have the same length.

Consider the definition of this table and the values inserted into it:

CREATE TABLE freds (f CHAR(6), v VARCHAR2(6)); INSERT INTO freds VALUES ('FRED', 'FRED');

Because Oracle blank-pads CHAR values, the value of F is blank-padded to 6 bytes. V is not blank-padded and has length 4.

A logical operator combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table 3-5 lists logical operators.

     

 

Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.  

 

 

Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN.  

 

 

Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.  

 

For example, in the WHERE clause of the following SELECT statement, the AND logical operator is used to ensure that only those hired before 1984 and earning more than $1000 a month are returned:

SELECT * FROM emp WHERE hiredate < TO_DATE('01-JAN-1984', 'DD-MON-YYYY') AND sal > 1000;

Table 3-6 shows the result of applying the NOT operator to a condition.

NOT  

TRUE  

FALSE  

UNKNOWN  

 

 

 

 

Table 3-7 shows the results of combining two expressions with AND.

AND  

TRUE  

FALSE  

UNKNOWN  

TRUE  

 

 

 

FALSE  

 

 

 

UNKNOWN  

 

 

 

Table 3-8 shows the results of combining two expressions with OR.

OR  

TRUE  

FALSE  

UNKNOWN  

TRUE  

 

 

 

FALSE  

 

 

 

UNKNOWN  

 

 

 

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. Table 3-9 lists SQL set operators.

   

UNION  

All rows selected by either query.  

UNION ALL  

All rows selected by either query, including all duplicates.  

INTERSECT  

All distinct rows selected by both queries.  

MINUS  

All distinct rows selected by the first query but not the second.  

All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order. To comply with emerging SQL standards, a future release of Oracle will give the INTERSECT operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the INTERSECT operator with other set operators.

The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype. If component queries select character data, the datatype of the return values are determined as follows:

  • If both queries select values of datatype CHAR, the returned values have datatype CHAR.
  • If either or both of the queries select values of datatype VARCHAR2, the returned values have datatype VARCHAR2.

Consider these two queries and their results:

SELECT part FROM orders_list1; PART ---------- SPARKPLUG FUEL PUMP FUEL PUMP TAILPIPE SELECT part FROM orders_list2; PART ---------- CRANKSHAFT TAILPIPE TAILPIPE

The following examples combine the two query results with each of the set operators.

The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows how datatype must match when columns do not exist in one or the other table:

SELECT part, partnum, to_date(null) date_in FROM orders_list1 UNION SELECT part, to_null(null), date_in FROM orders_list2; PART PARTNUM DATE_IN ---------- ------- -------- SPARKPLUG 3323165 SPARKPLUG 10/24/98 FUEL PUMP 3323162 FUEL PUMP 12/24/99 TAILPIPE 1332999 TAILPIPE 01/01/01 CRANKSHAFT 9394991 CRANKSHAFT 09/12/02 SELECT part FROM orders_list1 UNION SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP TAILPIPE CRANKSHAFT

The following statement combines the results with the UNION ALL operator, which does not eliminate duplicate selected rows:

SELECT part FROM orders_list1 UNION ALL SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP FUEL PUMP TAILPIPE CRANKSHAFT TAILPIPE TAILPIPE

Note that the UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. A PART value that appears multiple times in either or both queries (such as 'FUEL PUMP') is returned only once by the UNION operator, but multiple times by the UNION ALL operator.

The following statement combines the results with the INTERSECT operator which returns only those rows returned by both queries:

SELECT part FROM orders_list1 INTERSECT SELECT part FROM orders_list2; PART ---------- TAILPIPE

The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

SELECT part FROM orders_list1 MINUS SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP

Table 3-10 lists other SQL operators.

     

(+)  

Indicates that the preceding column is the outer join column in a join. See "Outer Joins".  

 

PRIOR  

Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured, query. In such a query, you must use this operator in the CONNECT BY clause to define the relationship between parent and child rows. You can also use this operator in other parts of a SELECT statement that performs a hierarchical query. The PRIOR operator is a unary operator and has the same precedence as the unary + and - arithmetic operators. See "Hierarchical Queries".  

 

A SQL function is similar to an operator in that it manipulates data items and returns a result. SQL functions differ from operators in the format in which they appear with their arguments. This format allows them to operate on zero, one, two, or more arguments:

function(argument, argument, ...)

If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, Oracle implicitly converts the argument to the expected datatype before performing the SQL function. See "Data Conversion".

If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not follow this rule are CONCAT, DECODE, DUMP, NVL, and REPLACE.

SQL functions should not be confused with user functions written in PL/SQL. User functions are described in "User Functions".

In the syntax diagrams for SQL functions, arguments are indicated with their datatypes following the conventions described in "Syntax Diagrams and Notation" in the Preface of this reference.

SQL functions are of these general types:

  • single-row (or scalar) functions
  • group (or aggregate) functions

The two types of SQL functions differ in the number of rows upon which they act. A single-row function returns a single result row for every row of a queried table or view; a group function returns a single result row for a group of queried rows.

Single-row functions can appear in select lists (if the SELECT statement does not contain a GROUP BY clause), WHERE clauses, START WITH clauses, and CONNECT BY clauses.

Group functions can appear in select lists and HAVING clauses. If you use the GROUP BY clause in a SELECT statement, Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, all elements of the select list must be expressions from the GROUP BY clause, expressions containing group functions, or constants. Oracle applies the group functions in the select list to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, Oracle applies group functions in the select list to all the rows in the queried table or view. You use group functions in the HAVING clause to eliminate groups from the output based on the results of the group functions, rather than on the values of the individual rows of the queried table or view. For more information on the GROUP BY and HAVING clauses, see the GROUP BY Clause and the HAVING Clause.

In the sections that follow, functions are grouped by the datatypes of their arguments and return values.

Number functions accept numeric input and return numeric values. This section lists the SQL number functions. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits.

Purpose  

Returns the absolute value of n.

 

Example  

 

Purpose  

Returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to and are expressed in radians.

 

Example  

 

Purpose  

Returns the arc sine of n. Inputs are in the range of -1 to 1, and outputs are in the range of -/2 to /2 and are expressed in radians.

 

Example  

 

Purpose  

Returns the arc tangent of n. Inputs are in an unbounded range, and outputs are in the range of -/2 to /2 and are expressed in radians.

 

Example  

 

Purpose  

Returns the arc tangent of n and m. Inputs are in an unbounded range, and outputs are in the range of - to , depending on the signs of n and m, and are expressed in radians. Atan2(n,m) is the same as atan2(n/m)

 

Example  

 

Purpose  

Returns smallest integer greater than or equal to n.

 

Example  

 

Purpose  

Returns the cosine of n (an angle expressed in radians).

 

Example  

 

Purpose  

Returns the hyperbolic cosine of n.

 

Example  

 

Purpose  

Returns e raised to the nth power; e = 2.71828183 ...

 

Example  

 

Purpose  

Returns largest integer equal to or less than n.

 

Example  

 

Purpose  

Returns the natural logarithm of n, where n is greater than 0.

 

Example  

 

Purpose  

Returns the logarithm, base m, of n. The base m can be any positive number other than 0 or 1 and n can be any positive number.

 

Example  

 

Syntax  

MOD(m,n)  

Purpose  

Returns remainder of m divided by n. Returns m if n is 0.

 

Example  

 

 

This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:

 

 

The following statement illustrates the difference between the MOD function and the classical modulus:

 

Purpose  

Returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, n must be an integer.

 

Example  

 
   
 

Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.

 

Example 1

 
 

Example 2

 

 

Syntax  

 

Purpose  

If n<0, the function returns -1; if n=0, the function returns 0; if n>0, the function returns 1.

 

Example  

 

Purpose  

Returns the sine of n (an angle expressed in radians).

 

Example  

 

Purpose  

Returns the hyperbolic sine of n.

 

Example  

 

Purpose  

Returns square root of n. The value n cannot be negative. SQRT returns a "real" result.

 

Example  

 

Purpose  

Returns the tangent of n (an angle expressed in radians).

 

Example  

 

Purpose  

Returns the hyperbolic tangent of n.

 

Example  

 

Purpose  

Returns n truncated to m decimal places; if m is omitted, to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.

 

Examples  

 

 

 

Single-row character functions accept character input and can return either character or number values.

This section lists character functions that return character values. Unless otherwise noted, these functions all return values with the datatype VARCHAR2 and are limited in length to 4000 bytes. Functions that return values of datatype CHAR are limited in length to 2000 bytes. If the length of the return value exceeds the limit, Oracle truncates it and returns the result without an error message.

Syntax  

 

Purpose  

Returns the character having the binary equivalent to n in either the database character set or the national character set.

If the USING NCHAR_CS clause is not specified, this function returns the character having the binary equivalent to n as a VARCHAR2 value in the database character set.

If the USING NCHAR_CS clause is specified, this function returns the character having the binary equivalent to n as a NVARCHAR2 value in the national character set.

 

Example 1  

 

Example 2  

 

Syntax  

 

Purpose  

Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). For information on this operator, see "Concatenation Operator".

 

Example  

This example uses nesting to concatenate three character strings:

S
 

Purpose  

Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

 

Example  

S  

Purpose  

Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2).

 

Example  

 

Purpose  

Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

 

Example  

 

Syntax  

 

Purpose  

Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.

 

Example  

"  

Purpose  

Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. The value of 'nlsparams' can have this form:

where sort is either a linguistic sort sequence or BINARY. The linguistic sort sequence handles special linguistic requirements for case conversions. Note that these requirements can result in a return value of a different length than the char. If you omit 'nlsparams', this function uses the default sort sequence for your session. For information on sort sequences, see Oracle8 Reference.

 

Example  

 

Syntax  

 

Purpose  

Returns char, with all letters lowercase. The 'nlsparams' can have the same form and serve the same purpose as in the NLS_INITCAP function.

 

Example  

 

Syntax  

 

Purpose  

Returns char, with all letters uppercase. The 'nlsparams' can have the same form and serve the same purpose as in the NLS_INITCAP function.

 

Example  

 

Syntax  

 

Purpose  

Returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE allows you to substitute one string for another as well as to remove character strings.

 

Example  

 

Syntax  

 

Purpose  

Returns char1, right-padded to length n with char2, replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

 

Example  

 

Syntax  

 

Purpose  

Returns char, with all the rightmost characters that appear in set removed; set defaults to a single blank. RTRIM works similarly to LTRIM.

 

Example  

 

Syntax  

 

Purpose  

Returns a character string containing the phonetic representation of char. This function allows you to compare words that are spelled differently, but sound alike in English.

 

The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:

 

 

 

  • Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
 

 

 

  • Assign numbers to the remaining letters (after the first) as follows:
 

 

 

  • If two or more letters with the same assigned number are adjacent, remove all but the first.
 

 

 

  • Return the first four bytes padded with 0.
 

Example  

 

Syntax  

 

Purpose  

Returns a portion of char, beginning at character m, n characters long. If m is 0, it is treated as 1. If m is positive, Oracle counts from the beginning of char to find the first character. If m is negative, Oracle counts backwards from the end of char. If n is omitted, Oracle returns all characters to the end of char. If n is less than 1, a null is returned.

Floating-point numbers passed as arguments to substr are automatically converted to integers.

 

Example 1  

 

Example 2  

 

Syntax  

 

Purpose  

The same as SUBSTR, except that the arguments m and n are expressed in bytes, rather than in characters. For a single-byte database character set, SUBSTRB is equivalent to SUBSTR.

Floating-point numbers passed as arguments to substrb are automatically converted to integers.

 

Example  

Assume a double-byte database character set:

 

Syntax  

 

Purpose  

Returns char with all occurrences of each character in from replaced by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use an empty string for to to remove all characters in from from the return value. Oracle interprets the empty string as null, and if this function has a null argument, it returns null.

 

Example 1  

The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':

 

 

 

Example 2  

The following statement returns a license number with the characters removed and the digits remaining:

 

 

 

Syntax  

 

Purpose  

Returns char, with all letters uppercase. The return value has the same datatype as the argument char.

 

Example  

S  

This section lists character functions that return number values.

ASCII

Syntax  

 

Purpose  

Returns the decimal representation in the database character set of the first character of char. If your database character set is 7-bit ASCII, this function returns an ASCII value. If your database character set is EBCDIC Code Page 500, this function returns an EBCDIC value. Note that there is no similar EBCDIC character function.

 

Example  

 

Syntax  

 

Purpose  

Searches char1 beginning with its nth character for the mth occurrence of char2 and returns the position of the character in char1 that is the first character of this occurrence. If n is negative, Oracle counts and searches backward from the end of char1. The value of m must be positive. The default values of both n and m are 1, meaning Oracle begins searching at the first character of char1 for the first occurrence of char2. The return value is relative to the beginning of char1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if char2 does not appear m times after the nth character of char1) the return value is 0.

 

Example 1  

 

Example 2  

 

Syntax  

 

Purpose  

The same as INSTR, except that n and the return value are expressed in bytes, rather than in characters. For a single-byte database character set, INSTRB is equivalent to INSTR.

 

Example  

This example assumes a double-byte database character set.

 

Syntax  

 

Purpose  

Returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null.

 

Example  

 

Syntax  

 

Purpose  

Returns the length of char in bytes. If char is null, this function returns null. For a single-byte database character set, LENGTHB is equivalent to LENGTH.

 

Example  

This example assumes a double-byte database character set.

 

Syntax  

 

Purpose  

Returns the string of bytes used to sort char. The value of 'nlsparams' can have the form

where sort is a linguistic sort sequence or BINARY. If you omit 'nlsparams', this function uses the default sort sequence for your session. If you specify BINARY, this function returns char. For information on sort sequences, see the discussions of national language support in Oracle8 Reference

.  

Example  

This function can be used to specify comparisons based on a linguistic sort sequence rather on the binary value of a string:

 

Date functions operate on values of the DATE datatype. All date functions return a value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number.

Syntax  

 

Purpose  

Returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.

 

Example  

 

Syntax  

 

Purpose  

Returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.

 

Example 1  

 

Example 2  

 

Syntax  

 

Purpose  

Returns number of months between dates d1 and d2. If d1 is later than d2, result is positive; if earlier, negative. If d1 and d2 are either the same days of the month or both last days of months, the result is always an integer; otherwise Oracle calculates the fractional portion of the result based on a 31-day month and considers the difference in time components of d1 and d2.

 

Example  

 

Syntax  

 

Purpose  

Returns the date and time in time zone z2 when date and time in time zone z1 are d. The arguments z1 and z2 can be any of these text strings:

 
 
 

Atlantic Standard or Daylight Time  

 
 

Bering Standard or Daylight Time  

 
 

Central Standard or Daylight Time  

 
 

Eastern Standard or Daylight Time  

 
 

Greenwich Mean Time  

 
 

Alaska-Hawaii Standard Time or Daylight Time.  

 
 

Mountain Standard or Daylight Time  

 
 

Newfoundland Standard Time  

 
 

Pacific Standard or Daylight Time  

 
 

Yukon Standard or Daylight Time  

Syntax  

 

Purpose  

Returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in your session's date language-either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version; any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument d.  

Example  

This example returns the date of the next Tuesday after March 15, 1992.

 

Syntax  

 

Purpose  

Returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day. See "ROUND and TRUNC" for the permitted format models to use in fmt.

 

Example  

 

Syntax  

 

Purpose  

Returns the current date and time. Requires no arguments. In distributed SQL statements, this function returns the date and time on your local database. You cannot use this function in the condition of a CHECK constraint.

 

Example  

 

Syntax  

 

Purpose  

Returns d with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, d is truncated to the nearest day. See "ROUND and TRUNC" for the permitted format models to use in fmt.

 

Example  

 

Table 3-11 lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.

   
CC SCC  

One greater than the first two digits of a four-digit year.  

SYYYY YYYY YEAR SYEAR YYY YY Y  

Year (rounds up on July 1)  

IYYY IY IY I  

ISO Year  

Q  

Quarter (rounds up on the sixteenth day of the second month of the quarter)  

MONTH MON MM RM  

Month (rounds up on the sixteenth day)  

WW  

Same day of the week as the first day of the year.  

IW  

Same day of the week as the first day of the ISO year.  

W  

Same day of the week as the first day of the month.  

DDD DD J  

Day  

DAY DY D  

Starting day of the week  

HH HH12 HH24  

Hour  

MI  

Minute  

The starting day of the week used by the format models DAY, DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY. For information on this parameter, see Oracle8 Reference.

Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype; the last datatype is the output datatype. This section lists the SQL conversion functions.

Syntax  

 

Purpose  

Converts a value from CHAR or VARCHAR2 datatype to ROWID datatype.

 

Example  

 

Syntax  

CONVERT(char, dest_char_set [,source_char_set] )  

Purpose  

Converts a character string from one character set to another.

The char argument is the value to be converted.

The dest_char_set argument is the name of the character set to which char is converted.

The source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set.

 

 

Both the destination and source character set arguments can be either literals or columns containing the name of the character set.

For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.

 

Example  

 

 

Common character sets include:

 

 

 

US 7-bit ASCII character set

DEC West European 8-bit character set

HP West European Laserjet 8-bit character set

DEC French 7-bit character set

IBM West European EBCDIC Code Page 500

IBM PC Code Page 850

ISO 8859-1 West European 8-bit character set  

Syntax  

 

Purpose  

Converts char containing hexadecimal digits to a raw value.

 

Example  

 

Syntax  

 

Purpose  

Converts raw to a character value containing its hexadecimal equivalent.

 

Example  

 

Syntax  

 

Purpose  

Converts a ROWID value to VARCHAR2 datatype. The result of this conversion is always 18 characters long.

 

Example  

 

Syntax  

 

Purpose  

Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format. For information on date formats, see "Format Models".

 

 

The 'nlsparams' specifies the language in which month and day names and abbreviations are returned. This argument can have this form:

If you omit nlsparams, this function uses the default date language for your session.

 

Example  

 

Syntax  

 

Purpose

 

Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. For information on number formats, see "Format Models".

 

The 'nlsparams' specifies these characters that are returned by number format elements:

- decimal character

- group separator

- local currency symbol

- international currency symbol

This argument can have this form:

'
 

 

The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Note that within the quoted string, you must use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.

 

 

If you omit 'nlsparams' or any one of the parameters, this function uses the default parameter values for your session.

 

Example 1  

In this example, the output is blank padded to the left of the currency symbol.

 
Example 2    

Notes:

  • In the optional number format fmt, L designates local currency symbol and MI designates a trailing minus sign. See Table 3-13 for a complete listing of number format elements.
 
  • During a conversion of Oracle numbers to string, if a rounding operation occurs that overflows or underflows the Oracle NUMBER range, then ~ or -~ may be returned, representing infinity and negative infinity, respectively. This event typically occurs when you are using TO_CHAR() with a restrictive number format string, causing a rounding operation.
 

Syntax  

 

Purpose  

Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer. For information on date formats, see "Format Models".

The 'nlsparams' has the same purpose in this function as in the TO_CHAR function for date conversion.

Do not use the TO_DATE function with a DATE value for the char argument. The returned DATE value can have a different century value than the original char, depending on fmt or the default date format.

For information on date formats, see "Date Format Models".

 

Example  

 

Syntax  

 

Purpose  

Returns char with all of its single-byte characters converted to their corresponding multibyte characters. Any single-byte characters in char that have no multibyte equivalents appear in the output string as single-byte characters. This function is only useful if your database character set contains both single-byte and multibyte characters.

 

Syntax  

 

Purpose  

Converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.

 

Example 1  

 

 

The 'nlsparams' string in this function has the same purpose as it does in the TO_CHAR function for number conversions.

 

Example 2  

 

Syntax  

 

Purpose  

Returns char with all of its multibyte character converted to their corresponding single-byte characters. Any multibyte characters in char that have no single-byte equivalents appear in the output as multibyte characters. This function is only useful if your database character set contains both single-byte and multibyte characters.

 

Syntax  

 

Purpose  

Converts text into the character set specified for conversions between the database character set and the national character set.

The text argument is the expression to be converted.

Specifying the USING CHAR_CS argument converts text into the database character set. The output datatype is VARCHAR2.

Specifying the USING NCHAR_CS argument converts text into the national character set. The output datatype is NVARCHAR2.

This function is similar to the Oracle CONVERT function, but must be used instead of CONVERT if either the input or the output datatype is being used as NCHAR or NVARCHAR2.

 

Example 1  

 

Example 2  

 

Example 3  

 

Syntax  

 

Purpose  

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set. For the datatype corresponding to each code, see Table 2-1.

The argument return_format specifies the format of the return value and can have any of the values listed below.

By default, the return value contains no character set information. To retrieve the character set name of expr, specify any of the format values below, plus 1000. For example, a return_format of 1008 returns the result in octal, plus provides the character set name of expr.

 

 

8

 

returns result in octal notation.  

 

10

 

returns result in decimal notation.  

 

16

 

returns result in hexadecimal notation.  

 

17

 

returns result as single characters.  

 

The arguments start_position and length combine to determine which portion of the internal representation to return. The default is to return the entire internal representation in decimal notation.

If expr is null, this function returns 'NULL'.

 

Example 1  

 

Example 2  

 

Example 3  

 

Syntax  

 

Purpose  

Returns an empty LOB locator that can be used to initialize a LOB variable or in an INSERT or UPDATE statement to initialize a LOB column or attribute to EMPTY. EMPTY means that the LOB is initialized, but not populated with data.

You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the OCI.

 

Examples  

 

Syntax  

 

Purpose  

Returns a BFILE locator that is associated with a physical LOB binary file on the server's file system. A directory is an alias for a full pathname on the server's file system where the files are actually located; 'filename' is the name of the file in the server's file system.

Neither 'directory' nor 'filename' need to point to an existing object on the file system at the time you specify BFILENAME. However, you must associate a BFILE value with a physical file before performing subsequent SQL, PL/SQL, DBMS_LOB package, or OCI operations. For more information, see CREATE DIRECTORY.

 

 

Note: This function does not verify that either the directory or file specified actually exists. Therefore, you can call the CREATE DIRECTORY command after BFILENAME. However, the object must exist by the time you actually use the BFILE locator (for example, as a parameter to one of the OCILob or DBMS_LOB operations such as OCILobFileOpen() or DBMS_LOB.FILEOPEN()).

For more information about LOBs, see Oracle8 Application Developer's Guide and Oracle Call Interface Programmer's Guide.

 

Example  

 

Syntax  

 

Purpose  

Returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first exprs before the comparison. Oracle compares the exprs using nonpadded comparison semantics. Character comparison is based on the value of the character in the database character set. One character is greater than another if it has a higher value. If the value returned by this function is character data, its datatype is always VARCHAR2.

 

Example  

 
Sours: https://docs.oracle.com/cd/A58617_01/server.804/a58225/ch3all.htm
  1. Modern salon decor
  2. Petco tv commercial
  3. Vinyl fencing rails
  4. Zara salary
  5. Gta 6 e3 2019

Oracle outer join operator (+) allows you to perform outer joins on two or more tables. Quick Example: -- Select all rows from cities table even if there is no matching row in counties table SELECT cities.


Click to see full answer.

Likewise, what is the use of (+) in SQL?

The SQL IN condition (sometimes called the IN operator) allows you to easily test if an expression matches any value in a list of values. It is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Additionally, what does a * mean in SQL? In SQL * means All record, not only in SQL in other programming languages * is called as wild card character which means all present record. In SQL we use * with SELECT query to select all records forma desired table.

Additionally, what does <> mean in Oracle?

It means 'not equal to'.

What is outer join in Oracle?

Oracle Outer JOIN. Oracle Inner JOINJoining data items from tables, based on values common to both tables. Outer JOINJoining data items from tables, based on values common to both tables, while displaying all data from one table regardless of if there is a match on the second table.

Sours: https://everythingwhat.com/what-does-mean-in-oracle-sql
WHAT IS AN ORACLE?

Oracle meaning

(Oracle Corporation, Redwood Shores, CA, www.oracle.com) The world's largest database and enterprise software vendor founded in 1977 by Larry Ellison. The Oracle Database has been Oracle's flagship product, which was the first DBMS to incorporate the SQL query language. It became very popular due to its robustness and huge variety of platforms that it ran on.In the mid-1990s, Oracle was a major promoter of the network computer, forming subsidiary Network Computer, Inc. to define the specifications for the platform. Although the network computer did not take off, the principles it embodied are widely used in today's thin client architectures and are ever increasing. See network computer and Liberate.After the turn of the century, the company greatly enhanced its application offerings by acquiring world class software companies such as PeopleSoft in 2004 and Siebel Systems in 2005. In 2010, Oracle acquired Sun Microsystems to become a full-fledged computer systems company as well as master of Java, one of the Internet's most widely used software platforms. See Oracle Database, Oracle Fusion, Sun, Java, PeopleSoft and Siebel software.

Sours: https://www.yourdictionary.com/oracle

In oracle what does mean

or·​a·​cle|\ ˈȯr-ə-kəlHow to pronounce oracle (audio), ˈär-\

1in ancient Greece : a person (such as a priestess) through whom a god was believed to speakconsulting an oracle

2: the place (such as a shrine) where people went to ask questions of an oracle

3: an answer or message given by an oracle

1a: a person (such as a priestess of ancient Greece) through whom a deity is believed to speakthe prophecies of the Delphic oracle— D. F. Marks

b: a shrine in which a deity reveals hidden knowledge or the divine purpose through such a person

c: an answer or decision given by an oracleambiguous oracles

2a: a person giving wise or authoritative decisions or opinionsShe became an oracle of pop culture.

b: an authoritative or wise expression or answer

Or·​a·​cle|\ ˈȯr-ə-kəlHow to pronounce Oracle (audio), ˈär-\

unincorporated settlement north-northeast of Tucson in southern Arizona population 3686; site of a biome research facility

Sours: https://www.merriam-webster.com/dictionary/oracle
WHAT IS AN ORACLE?

Left and Right Joins Using the Plus (+) Sign in Oracle

Like virtually all relational databases, Oracle allows queries to be generated that combine or rows from two or more tables to create the final result set. While there are numerous types of joins that can be performed, the most common are the and the .

In this tutorial, we’ll briefly explore the difference between the and and then examine the shorthand method Oracle provides for performing specifically using the operator symbol.

What is an Inner Join?

An in a relational database is simply the joining of two or more tables in which the result will only contain data which satisfied all join conditions.

For example, here we have a basic schema with two tables: and . The table is just a list of possible language names and a unique language :

Meanwhile, our table has a row which for most, but not all, books simply contains the associated with the original published language of the book:

In many cases, we may wish to perform an of the and tables so rather than viewing the meaningless value of each book, we can actually see the instead.

What’s critical to note here is that our result set was slightly different in the above two queries. In the first, we simply listed the first books, but in the query we’re only returning results which meet all conditions from both tables. For this reason, the record of (which has a value of or empty) is ignored and not returned in the result of our .

What is an Outer Join?

Instead of exclusively returning results which satisfy all join conditions of an , an returns not only results which satisfy all conditions, but also returns rows from one table which did not satisfy the condition. The table that is chosen for this “bypass” of conditional requirements is determined by the directionality or “side” of the join, typically referred to as or outer joins.

When defining a side to your , you are specifying which table will always return its row even if the opposing table on the other side of the join has missing or values as part of the joining condition.

Therefore, if we perform the same basic as above to retrieve and , we know that our table should always return data, so our side should “point toward” our table, thereby making the table the table we’re attaching to it.

To accomplish this, we simply change:

…to this:

Thus, the entire query and result set looks almost identical to the except that minor alteration:

As expected, by using a instead of the previous , we’re getting the best of both worlds: We’re not skipping any records (such as ) simply because the value is for that record, yet for all records where exists, we get the nicely formatted obtained from our table.

Performing Outer Joins Using the (+) Symbol

As indicated in the official documentation, Oracle provides a special (the symbol) that is shorthand for performing .

In practice, the symbol is placed directly in the conditional statement and on the side of the optional table (the one which is allowed to contain empty or values within the conditional).

Therefore, we can once again rewrite our above statement using the operator like so:

The results are the same as the standard example above, so we won’t include them here. However, there’s one critical aspect to notice about the syntax using the operator for .

The operator must be on the left side of the conditional (left of the equals sign). Therefore, in this case, because we want to ensure that our table is the optional table that can return values during this comparison, we swapped the order of the tables in this conditional, so is on the left (and is optional) while is on the right.

Lastly, because of this reordering of the table sides in the conditional when using the operator, it’s important to realize that the above is simply shorthand for a . This means that this snippet of the query:

…is effectively identical to this:

Sours: https://chartio.com/resources/tutorials/left-and-right-joins-using-the-plus-sign-in-oracle/

You will also like:

oracle

This shows grade level based on the word's complexity.

[ awr-uh-kuhl, or- ]

/ ˈɔr ə kəl, ˈɒr- /


noun

(especially in ancient Greece) an utterance, often ambiguous or obscure, given by a priest or priestess at a shrine as the response of a god to an inquiry.

the agency or medium giving such responses.

a shrine or place at which such responses were given: the oracle of Apollo at Delphi.

a person who delivers authoritative, wise, or highly regarded and influential pronouncements.

a divine communication or revelation.

any person or thing serving as an agency of divine communication.

any utterance made or received as authoritative, extremely wise, or infallible.

oracles,the Scriptures.

the holy of holies of the Temple built by Solomon in Jerusalem. 1 Kings 6:16, 19–23.

QUIZ

ARE YOU A TRUE BLUE CHAMPION OF THESE "BLUE" SYNONYMS?

We could talk until we're blue in the face about this quiz on words for the color "blue," but we think you should take the quiz and find out if you're a whiz at these colorful terms.

Question 1 of 8

Which of the following words describes “sky blue”?

Origin of oracle

First recorded in 1350–1400; Middle English, from Old French, from Latin ōrāculum, equivalent to ōrā(re) “to plead” + -culum diminutive noun suffix; see oration, -cle2

WORDS THAT MAY BE CONFUSED WITH oracle

auricle, oracle

Words nearby oracle

or, ora, ORAC, orach, orache, oracle, oracle bones, oracles, oracular, oracy, orad

Dictionary.com Unabridged Based on the Random House Unabridged Dictionary, © Random House, Inc. 2021

Words related to oracle

edict, apocalypse, fortune, revelation, augury, canon, prediction, commandment, prognostication, answer, law, divination, vision

How to use oracle in a sentence

  • Vents built into the stone structure, in addition to providing fresh air, may have given the oracle at Chavín a highly engineered voice “worthy of the Wizard of Oz” when the shell trumpets were played inside them.

    The human history found inside a seashell|Katharine Norbury|July 30, 2021|Washington Post

  • Her very first sculpture, a metallic chrome unicorn aptly titled “Space Oracle,” sits on a pedestal directly in front.

    The Tiniest Jackson Pollock|Justin Jones|November 5, 2014|DAILY BEAST

  • And, anyway, what would it take to be a Samuel Gompers at Microsoft, Facebook, Oracle, Amazon, or Google?

    Up To a Point: Robber Barons Make Way For Robber Nerds|P. J. O’Rourke|August 9, 2014|DAILY BEAST

  • A Brit by birth, the eight-armed oracle was born in Weymouth, England, in 2008 at the Sea Life Centre.

    The Amazing Tale of Paul the Psychic Octopus: Germany’s World Cup Soothsayer|Emily Shire|July 12, 2014|DAILY BEAST

  • Just this week, Oracle CEO Larry Ellison said Apple was adrift without Steve Jobs.

    Carl Icahn’s Tweets Send Apple Stock Above $500|William O’Connor|August 14, 2013|DAILY BEAST

  • The move will take place on July 15 as software giant Oracle leaves for the New York Stock Exchange.

    Tesla Stock Added to NASDAQ 100 Index|Edward Ferguson|July 9, 2013|DAILY BEAST

  • The latter is a square-faced practical man, who is looked up to as a species of oracle by all his friends.

    Hunting the Lions|R.M. Ballantyne

  • The League Oracle admits that "a repeal would injure the farmer, but not so much as he fears."

    Blackwood's Edinburgh Magazine, No. CCCXXXIX. January, 1844. Vol. LV.|Various

  • An oracle said that he would not succeed in its erection before a man voluntarily offered himself as a sacrifice.

    A Woman's Journey Round the World|Ida Pfeiffer

  • There were in Greece two young rakes, who were told by the oracle to beware of the melampygos or sable posteriors.

    A Philosophical Dictionary, Volume 1 (of 10)|Franois-Marie Arouet (AKA Voltaire)

  • It is plain that there are more ways than one of explaining such an oracle.

    A Philosophical Dictionary, Volume 1 (of 10)|Franois-Marie Arouet (AKA Voltaire)

British Dictionary definitions for oracle


noun

a prophecy, often obscure or allegorical, revealed through the medium of a priest or priestess at the shrine of a god

a shrine at which an oracular god is consulted

an agency through which a prophecy is transmitted

any person or thing believed to indicate future action with infallible authority

a statement believed to be infallible and authoritative

Bible
  1. a message from God
  2. the holy of holies in the Israelite temple

See also oracles

Word Origin for oracle

C14: via Old French from Latin ōrāculum, from ōrāre to request

Collins English Dictionary - Complete & Unabridged 2012 Digital Edition © William Collins Sons & Co. Ltd. 1979, 1986 © HarperCollins Publishers 1998, 2000, 2003, 2005, 2006, 2007, 2009, 2012

Sours: https://www.dictionary.com/browse/oracle


1090 1091 1092 1093 1094