Chapter 2 Fundamentals

PL/SQL User s

 

2.1    Lexical Units

 

          Character Set:

 

 The characters that PL/SQL uses include

          Alphanumerical characters

          Tabs, spaces and carriage returns

          The characters: (  ) +  - * / < > + ! ; ,   @ %   # $ ^ & _ | { } ? [  ]

 

          Lexical Units

 

A line of PL/SQL text is made of lexical units:

          delimiters (single or compound symbols)

          identifiers including reserved words.

          Literal

          Comments

 

The following line of PL/SQL text

Bonus := salary * 0.1 ;  -- calculate bonus.

 

Contains id, delimiters, literal and comments.

 

2.2    Delimiters:

 

A delimiter is simple or compound symbols which has special meaning.

 

(1)     Simple Symbols:

+, -                               addition operator, subtraction/negation operator

/, *                               division and multiplication operation operators.

=, <, >                          relational operators

(, )                                expression or list delimiters.

;                                    statement terminator

%                                 attribute operator for table and column.

,                                    item separator

.                                    component operator, member accessing operator.

@                                 remote access operator

                                     character or character string delimiter.

                                    quoted identifier.

:                                    host variable identifier.

(2)     Compound Symbols:

 

**                                                exponentiation operator

<>, !=, ~=, ^=, <=, >=, =>          : relational operators

=                                   assignment operator

..                                   range operator in for loop.

||                                    string concatenation operator

<<, >>                          label delimiters.

 - -                                single line comment delimiter

/*, */                            multiple line comment delimiter.

 

2.3     Identifiers

 

3         An identifier is used to name a variable, constants, program units such as functions, procedures packages.

 

4         An identifier characters string consists of alphanumerical letters, _, $, # and beginning with alphabetical letters.

5          Reserved words: reserved words are identifiers. Those include IF, END, FOR and etc.

6         Predefined identifiers: Identifiers defined in STANDARD package include NOTFOUD, INVALID_NUMBER and etc.

7         Quoted identifiers: For flexibility, PL/SQL allows you double quote the identifier so that characters such as space, / can be used in identifiers. The quoted identifiers must be all capital characters.

 

2.4    Literal

 

A literal is a constant of number, character, string or Boolean value:

 

                123,   a ,  This is a string , TRUE, FALSE

 

where the character and string are both quoted by single quotation marks.

 

2.5    Comments:

-- Single line comments

/* Multiple line comments

         

*/

 

2.6  Data Types

 

1          BINARY_INTTEGER:

 stored signed integer between  231   1 and 231  - 1.

 

    The subtypes of BINARY_NUMBER include

                NATUAL: 0,  , 2147483647

                POSITIVE: 1,  , 2147483647

2         NUMBER ( precision, scale ):

 

          The precision specifies the total number of digits for the number.

          The scale specifies where to round the number. A positive scale will round to the number scale digit to right the decimal point. A positive scale will round to scale digits to the left of decimal point. For example

        NUMBER (7, 2) will round the number at hundredth.

        NUMBER(7, -3) will round the number at thousand.

 

The precision ranges between 1 to 38, and scale ranges between  84 to127.

 

          Subtypes of NUMBER

 

         DEC, DECIAL, DOUBLE PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, SMALLINT

 

Those subtypes are for compatibility with other systems and more descriptive names.

 

3         PLS_INTEGER

 

          PLS_INTEGER has the same range as NUMBER and BINARY_INTEGER.

          PLS_INTEGER uses less space to store a number and uses machine operation, and therefore is faster than NUMBER and BINARY_INTEGER.

          Exception will be raised for PLS_INTEGER if overflow occurred. However, no exception is raised if  overflow occurred in NUMBER of BINARY NUMBER.

 

 

 

4.       CHAR [ (max_length) ]

 

          Specify a fixed length string variable. The maximum length is 32,767 and the default length is 1. The parameter must be literal (it cannot be constant or variable.  Even the CHAR(n) can have n as large as 32,767 bytes, but the column of database table can only have 255 characters. To store long string, use LONG column which can stored 3 gigabytes. Also you cannot select a column longer then 32,737 bytes into a CHAR variable.

          The subtypes of CHAR : CHARACTER, STRING are two subtypes of CHAR for compatibility with IBM/DS and IBM DB2 types or you need more descriptive names.

 

5.    VARCHAR( max_length )

 

          Specify a variable length character variable and parameter is required. The parameter like in CHAR specify the number of bytes reserved for the variable. In case of multiple bytes per characters, the number of characters stored in the VARCHAR(n) and CHAR(n) variable is less than n.

          The max_length of VARCHAR2 variable is 32,767.

          A VARCHAR2 column of a table has maximum length 2000. Therefore, you can not insert a VARCHAR(n) variable longer than 2000 into VARCHAR2 column.

          You can insert a VARCHAR2 variable into LONG column. However, you cannot select a LONG with more than 32,767 bytes into VARCHAR2 because of limit of VARCHAR2 variable.

          Subtypes STRING and VARCHAR are for compatible with ANSI/ISO and IBM types. You are not encouraged to use VARCHAR since VARCHAR may change to accommodate SQL standard.

 

6         LONG

 

          Specify variable-length character string with maximum length = 32,767.

          The LONG database column can stored 2 gigabytes data. So you can insert a LONG variable into a LONG column, but you cannot select a LONG column longer than 32,767 column into LONG variable.

          Long column can be used to stored text, array of characters, short document. It can be used in INSERT, DELETE, UPDATE, and SELECT statements. However, you cannot used it in expressions, function calls, SQL clauses like WHERE, group by, order by.

 

7.       RAW ( n )

 

          ROW variable are used to stored binary data or character string such as graphics and pictures. PL/SQL does not interpret RAW data, and Oracle does not transfer when RAW data is transfer from one system to another system.

           A row variable can be as long as 32,767 bytes. However, a RAW column can have only 255 bytes.

 

 

8.       LONG RAW

 

          A LONG RAW variable can have as many as  32,767 bytes while a LONG RAW data can be as long as 2 gigabytes.

          You can insert a LONG RAW variable data into LONG RAW column. However, you cannot select a LONG RAW column longer than 32,767 into a LONG RAW variable.

 

9.       ROWID

 

          When a table is created, a pseudo column is created for that table. Each row has a unique number after inserted. Accessing row with ROWID is the fastest way.

          ROWID variable can be used to store row identifier. A ROWID variable can be printed out with

ROWIDtoCHAR ( rowid_varible / rowid_column )

* When ROWID is printed out it has the following hexidecimal format:

 

BBBBBBBB.RRRR.FFFF

 

The first 8 hexadecimal digits is the block address and next 4 digit is the row number and the rightmost 4 digits are file number in a database.

 

* The following statements show how to use row id:

 

SELECT  ROWIDTOCHAR ( ROWID ), name from employee ;

SELECT * FROM employee where ROWID =  0000049F.0007.0004 

 

 

10.    Boolean

 

* Boolean variable is used to store boolean values or non-value (NULL).

 

11.    Date

 

          Store data and time. The valid date for the date variable is January 1, 4712 BC to Dec. 31, 4712.

* When date is stored in date column, the date and time (seconds from the midnight) are stored. The date part is default to the first day of current month and the time part is default to midnight.

 

-Defined Subtype

 

          A similar way to typedef in C allows you to define new subtype based on a base type. The base type will inherit all the operations of the base type. No new operator can be added to the new subtype.

          No length constraint can be used in the base type to define new subtype. However, %TYPE attribute can be used to work around that restriction.

          You define new subtype in the declaration part with following syntax:

 

SUBTYPE subtype_name  IS base_type ;

 

          Example: All subtype definition must be defined in DECLARATION part.

 

SUBTYPE  Counter IS  NATUAL ;   -- Counter can be used to define varaible

 

Tmp  VARCHAR2 ( 12 ) ;

SUBTYPE  phone_type  IS tmp%TYPE ; -- define a phone type with at most 12 chars.

 

 

2.7       Type Conversions

 

 Values among certain types can be converted either implicitly or explicitly. The implicit type conversion is done by Oracle. The explicit type conversion is done by calling some type conversion function.

 

          The following table shows which type can be converted to other type implicitly.

 

 

 

 

To

                                Binary_       Char     Date    Long     Number    Raw     RowID    Varchar2

                                Integer  

From

 

Binary_

Integer                                      X                           X         X                                             X                                         

 


Char                    X                                X           X         X            X           X                X

 


Date                                      X                           X                                                          X

 


Long                                       X                                                       X                             X

 


Number                X                X                       X                                                           X

 


Raw                                           X                       X                                                           X

 


RowID                                        X                                                                                      X

 


Varchar2                   X            X             X       X          X                X          X

 

 


          The following table shows the functions need for type Conversions:

 

To

                                   Char                  Date                       Number                    Raw          RowID   

From

 


Char                                               TO_DATE         TO_NUMBER     HEXTOROW  CHARTOROWID

 


Date                   TO_CHAR

 


Number             TO_CHAR           TO_DATE

 


Raw                 RAWTOHEX

 


RowID            ROWIDTOCHAR

 

 


2.8       Declarations

 

          Declarations declare variable, constants, cursors and user defined subtypes.

          DECLARE is used only in the PL/SQL blocks to begin the declaration part. The reserved word DECLARE is removed when a PL/SQL block becomes part of subprogram. Otherwise syntax error occurs.

 

1.       Variables Declarations

 

The full syntax of define variable is

 

identifier  TYPE [ [ NOT NULL ] := expression ] ;

 

where

(1)     identifier defines variable name

(2)     TYPE is either base data type or user-defined subtype, or a %TYPE attribute of a column, variables, or %ROWTYPE attribute of a table.

(3)     NOT NULL will not allow the variable contains NULL value.

(4)     The expression assigns a value into the variable. The initial value is a must if NOT NULL is specified. The expression can be variables, constants, function call and operators.

 

2         Examples:

 

Example 1: Use function call to initialize variable.

-- To debug a PL/SQL block, you can use server utility.

-- First set the server output on, then use DBMS_OUTPUT package to bebug the block. For detail discussion read Oracle Application Developer Guide.

 

SQL > SET SERVEROUTPUT ON

       

DECLARE

                S  VARCHAR ( 20 ) := TO_CHAR( sysdate,  MM/DD/YY HH:MM:SS  ) ;

BEGIN

                DBMS_OUTPUT.PUT_LINE ( s ) ;

                END ;

 

 SQL > RUN

 

 

Example 2: Use %ROWTYPE to define varaible.

declare

       cursor c IS select  deptno, count(*) CNT, sum(sal) Total

              from scott.emp group by deptno ;

       t c%ROWTYPE;  --cursor C  s type attribute is used to define var.

begin

       open c;

       loop

              fetch c into t;

              exit when c%NOTFOUND ;

              dbms_output.put_line(t.deptno || ', ' || t.CNT || ', ' || t.Total );

       end loop;

end ;

 

2.       Restrictions:

 

          Notice that columns in the select statement for a cursor must have alias. Otherwise syntax error will occur.

          Single variable can be defined per line.

          No forward variable declaration; variable must be defined before its reference.

 

                       

3.       Constant Declarations

 

identifier  CONSTANT TYPE := expression ;

 

 

4.       Naming Conventions

 

          When name (of procedure, table, column and etc) is referenced, it can be in the different formats:

identifier (   )                                               Simple format

object.identifier.(   )                                   Qualified format.

Identifer @network (   )                                            remote

object.identifier @network                                         qualifiered remote.

 

          Names in PL/SQL are not case-sensitive.

          Local constant and variable names overload the table name if the table name is not qualified/. That is the following will cause error:

 

DECLARE

        Emp varchar(10) ;

        CURSOR c IS  select * from emp ;       -- error will occur.

above will be fine if qualified emp with schema.

 

          Column names of table have higher precedence than local constants or variables.

 

DECLARE

        Ename varchar(10) :=  Robert  ;

        CURSOR c IS  select * from emp WHERE ename = ename ;

The condition will be true always since two enames in where clause will be taken as column name. It is good idea to pre-fixed the local name with my_.

 

 

2.9    Scope and Visibility

 

          PL/SQL blocks can be nested. That is subblocks can be defined in the excutable and exception handling parts.

          Same name can be used to define variable, constant, type, subprogram in block or subblock. The scope and visibility of a name in nested blocks are very similar to Pascal scope and visibility: Outer name is visible to all inner blocks. The inner name overwrite the outer names. Inner names are visible in blocks in which the names are defined.

          Example:

 

declare

       emp varchar(10) := 'John Doe' ;

       cursor c IS select * from scott.emp;

begin

       declare

              emp NUMBER(7, 2) := 3.1415 ;

       begin

       dbms_output.put_line( emp ); -- 3.14 is printed.

       end ;

       for t in C loop

              dbms_output.put_line( t.ename ); -- emp names are printed.

       end loop;

       dbms_output.put_line( emp );  --  John Doe  is printed.

end;

 

2.10 Assignment Statements

 

          Variable are initialized to NULL by default.

          Operation with NULL value will result NULL usually.

          TRUE or FALSE are global constant names and can be assigned to Boolean variables. Like most languages, an Boolean expression can be assigned to a Boolean variable. Alternatively, values in database can be assigned to variables by either SELECT INTO or FETCH INTO statements.

 

 

2.11  Operators and Expressions

 

2.11.1       Operator and their Precedence:

 

Operator                                       Operations

 

**, NOT                                         exponentiation, logical negation

 

+, -                                                  identity, negation

 

*, /                                                  multiplication, division

 

+, -, ||                                               addition, subtraction concatenation.

 

=, !=, <, <=, >, >=,

IS NULL,LIKE, BETWEEN, IN   comparison

 

AND                                               conjunction

 

OR                                                  inclusion.

 

* Examples

 

(1)     IN operator:  DELETE FROM emp where ename IN ( NULL,  Smith ,  Lynos ) ;

 

(2)     BETWEEN operator: UPDATE emp SET sal = sal * 1.05 WHERE sal BETWEEN 1000 and 1200

 

(3)     Date Comparison

DECLARE

                                D1 DATE :=  01-JAN-94  ;

                                D2 DATE :=  31-DEC-93  ;

                BEGIN    

                                If ( d1 > d2 ) Then

                                                DBMS_OUTPUT.PUT_LINE ( D1 ||   is larger than   || d2 ) ;

                                END IF;

                END ;     

(4)     Don t compare two real  number for exact equality or inequality.

 

2.11.2       Handling NULL

 

          When NULL is a operand of a unary operator, binary, ternary (BETWEEN) operator, NULL will be the result after the evaluating the operation (except v IS NULL and v contains NULL).

           Zero-length string is treated as NULL.

 

-- Testing NULL values. Any operator (except IS NULL) with

-- NULL operand will result NULL or FALSE.

declare

       i number(3) ;

       ok boolean ;

begin

       ok := i between 10 and 100; -- I has NULL, OK is false.

       if ok then

              dbms_output.put_line( 'TRUE' ) ;

       else

              dbms_output.put_line( 'FALSE') ;

       end if;

       i := 50;

       ok := i between 10 and 100 ;

       if ok then

              dbms_output.put_line( 'TRUE' ) ;

       else

              dbms_output.put_line( 'FALSE') ;

       end if;

       ok := '' < 'ABC' OR '' >= 'ABC';-- null string is treated as NULL.

       if ok then

              dbms_output.put_line( 'TRUE' ) ;

       else

              dbms_output.put_line( 'FALSE') ;

       end if;

end ;

/*

FALSE

TRUE

FALSE

Are the output

*/

 

          Concatenation operator ignores NULL value.

          NOT v results NULL if v contain NULL value.

          Variable is initialized to NULL when defined without initializatin.

          If a NULL argument is passed to a function, a NULL is return except in the following three cases:

(1)     DECODE function

SELECT  partname, DECODE( partcolor, 1,  Red , 2,  Yellow , 3,  Blue , NULL,  White ) ;

White color is printed out for all parts whose color code is NULL.

(2)     SELECT ename,  NVL( comm, 0.0) FROM emp ; All null commission column will be printed out as o.o

(3)     New_string := REPLACE ( old_string, old_substring, new_substring ) ;

 

The new string will be the same string if old_substring is NULL. Otherwise, new_string will be old_string with all old_substring in old_string replaced with new_substring.

 

 

2.12.3     Built-in Functions

 

Built-in functions can be called in either SQL statement or PL/SQL statement (except that DECODE function can only be called from SQL statement). Function calls can be nested. The built-in functions can be divided into groups: error-reporting, number functions, character functions, conversion functions, date functions and miscellaneous functions

 

2.12.1.1  Error Reporting Functions

 

Function SQLCODE return NUMBER

 

The function return code of an exception. The code is negative exception no data found exception which is 100. If is a user-defined exception the value return is +1 if the user-defined exception is not associated with an integer exception with EXCEPTION_INT pragma. Call to SQLCODE within exception handler is meaningful. Call to SQLCODE outside exception handler returns 0 always.

 

Function SQLERRM [ ( n NUMBER ) ] return  CHAR

 

The function returns error message associated the error code from anywhere the SQLERRM is called and supplied with argument n. If n is omitted, then call to SQLERRM outside exception handler will result  ORA-0000: normal , successful completion . If a user-defined exception is raised, the message from a the corresponding handler is  User-Defined Exception .

 

2.123..2  Number Functions

 

Number functions are usually the math function which take one or two arguments and return numeric values.

 

Function Prototype                                              Function

ABS ( n NUMBER )

CEIL( n NUMBER )

COS( n NUMBER )

COSH( n NUMBER )

EXP(  n NUMBER )                                                return e to the nth power

FLOOR( n NUMBER )

LN( n NUMBER )                                    return natural logarithm of n

LOG( m NUMBER,  n NUMBER )                           return m-based logarithm of  n

MOD( m NUMBER,  n NUMBER )         return reminder of m / n

POWER( m NUMBER,  n NUMBER )     return the number m raised to nth power.

ROUND( m NUMBER, [ n NUMBER ])  return m round at 0, n position left (right) to decimal ) point if

m is omitted or n is negative (positive).

SIGN( n NUMNER )                                                -1, 0 , +1 if n is negative, 0 or positive.

SIN( n NUMBER )

SINH( n NUMBER )

SQRT( n NUMBER )                                               n must be 0 or positive number.

TAN( n NUMBER )

TANH( n NUMBER )

TRUNC( m NUMBER, [ n NUMBER ])   return m truncated at 0, n position left (right) to decimal )

point if m is omitted or n is negative (positive).

 

2.12.3     Character Functions

 

Function Prototype                                                              Function

ASCII ( c  VARCHAR2) return NUMBER                     return the ascii code.

CHR ( n  NUMBER) return VARCHAR2                       return char in db char set that n represents

 

CONCAT( s1  VARCHAR2, s2  VARCHAR2) return VARCHAR2

                Attach s2 to s1 first and returns the combined string. If NULL argument is    treated as zero-length string. Neither s1 nor s2 will be changed by the concat function.

 

INITCAP ( s  VARCHAR2) return VARCHAR2          return string by converting  first letter of each word in s to upper case and all other letter in lower case. S will be the same as before after the call.

 

INSTR ( s1  VARCHAR2, s2  VARCHAR2 [, pos NUMBER [, n NUMBER] ] ) return NUMBER : search s1 starting a position pos for n s occurrence of s2. If pos is negative, the search is done backwards. The return value is the first letter of that occurrence. P and n are default to 1.

 

LENGTH ( s  VARCHAR2) return NUMBER

LENGTH ( s  CHAR) return NUMBER

Return the number of characters in s and including trailing blanks if s is of CHAR type.

 

LENGTHB ( s  VARCHAR2) return NUMBER

LENGTHB ( s  CHAR) return NUMBER

Return the number of bytes in s and including trailing bytes if s is of CHAR type.

 

LOWER ( s  VARCHAR2) return VARCHAR2

LOWER ( s  CHAR) return CHAR

Return string obtained by converting s to lowercase.

 

 

LPAD ( s  VARCHAR2,  len NUMBER [, pad VARCHAR ] ) return VARCHAR2

Return a  string with len characters from s if s more than len characters. If s has less than len character, pad( or blank character)  is used on the left of s to make a string with length = len.

 

 

LTRIM ( s  VARCHAR2,   set VARCHAR  ) return VARCHAR2

Remove from the first characters which appeared in set. If set is omitted, then it default to single space.

                DBMS_OUTPUT.put_line ( LTRIM (  This is a test ,  isTh ) )

Will print   is a test . There is a leading space.

 

 

REPLACE ( s1  VARCHAR2,  s2  VARCHAR2 [,  s3  VARCHAR2 ] ) return VARCHAR2

Return s1 with all occurrence of s2 replaced by s3. If s3 is omitted, all s2 occurrence in s1 are removed. If s2 is NULL, then s1 is returned.

 

REPLACE ( s  VARCHAR2,  len NUMBER [,  pad  VARCHAR2 ] ) return VARCHAR2

Returns the string s right-padded to length len, with the sequence of characters in pad replicated as many times as necessary. If pad is omitted, single blank is default. If s is longer than len characters, the first len characters are returned.

 

RTRIM ( s  VARCHAR2 [, set VARCHAR2] ) return VARCHAR2

Returns s with right characters appeared in set removed until the left most character which does not appear in set. If set is not specified, the set is default to a single blank character.

 

SOUNDEX  ( s  VARCHAR2) return VARCHAR2

Returns a string which is the phonetic representation of string s. The function allows you to compare English words that are spelled differently but sounds a like. The phonetic representation is defined in The Art of Computer Programming, Vol. 3 by Donald Knuth.

 

SUBSTR ( s  VARCHAR2,  pos NUMBER [, len NUMBER ] ) return VARCHAR2

Returns a substring of string s, starting at pos character position, and with length equal to len characters. If len is omitted, the characters between pos and end of s are returned. The pos can be negative and len must be positive. If pos is negative, the substring starts from the end of string s.

 

SUBSTRB ( s  VARCHAR2,  pos NUMBER [, len NUMBER ] ) return VARCHAR2

Similar to SUBSTR. The difference is that SUBSTRB starts at pos byte (not character) position and the len is length in byte (not in character). In language the length of a character is one byte, SUBSTR and SUBSTRB are the same.

 

TRANSLATE ( s  VARCHAR2 set1 VARCHAR2, set2 CHAR ) return VARCHAR2

Returns string s with all occurrences of set1 replaced set2.

 

UPPER ( s  VARCHAR2) return VARCHAR2

UPPER ( s  CHAR) return CHAR

Returns s with all letters in uppercase.

 

 

2.12.4     Conversion Functions

 

CHARTOROWID ( s CHAR ) return ROWID

CHARTOROWID ( s VARCHAR2 ) return ROWID

Convert a string of char or varchar2 type to rowid type value.

 

CONVERT (str VARCHAR2, set1 VARCHAR2 [, set2 VARCHAR2] ) return VARCHAR2

Converts the string from one character set to another character set. Set1 and set2 can be database column name or character set name such as US7ASCII, WE8DEC, WE8HP and etc.

 

HEXTORAW ( s  VARCHAR2) return RAW

HEXTORAW ( s  CHAR) return RAW

Converts the hexadecimal string of type CHAR or VARCHAR2 type to RAW type value.

 

HEXTORAW ( bin RAW ) return VARCHAR2

Converts the binary value bin from type RAW to a hexadecimal string of type VARCHAR2

 

CHARTOROWID ( bin ROWID ) return VARCHAR2

Convert the binary value bin of type ROWID to an 18-byte hexadecimal string of VARCHAR2.

 

TO_CHAR( dt DATE [, fmt [, nlsparam] ] ) return VARCHAR2

Converts a date to string. NLSPARAM specifies the language in which the month and abbreviation are used.

 

TO_DATE( s VARCHAR2 [, fmt [, nlsparam] ] ) return DATE

TO_DATE( s NUMBER [, fmt [, nlsparam] ] ) return DATE

Converts string or a number to a value of type DATE in the format specified by fmt. The format models are given in the following table:

 

Format Model       Description

CC, SCC century (S prefixes BC date with a minus sign)

YYYY, SYYYY      year (S prefixes BC with minus sign)

IYYY                       year based on ISO standard

YYY, YY, Y            last three, two, or one digit(s) of year.

IYY, IY, I                last three, two, or one digit(s) of ISO year.

YEAR, SYEAR      year spelled out (S has the same meaning as before)

RR                           last two digits of year in another century.

BC, AD                  BC or AD indicator

B.C., A.D.              BC or AD indicator with periods

Q                             quarter of year

MM                        month number (01-12)

RM                         Roman numeral month (I-XII; JAN=I)

MONTH                month name

MON                      abbreviation name of month

WW                       week of year (1-53)

IW                          week of year based on ISO standard

W                            Week of month (1-5)

DDD                       day of year (1-366)

DD                          day of month

D                             day of week

DAY                       name of day (in a week?)

DY                          abbreviation of name of day

J                              Julian day ( number of day from January 1, 4712 BC)

AM, PM                                Meridian indicator

A.M., P.M.            Meridian indicator with periods

HH, MM12            hour of day (1-12)

HH24                      hour of day (0-23)

MI                           minute (0-59)

SS                           seconds

SSSSS                    seconds past midnight (0-86399)

 

TO_CHAR ( num NUMBER [, fmt VARCHAR2 [, nlpparms ] ) return VARCHAR2

Convert a number to a string by the format specified. All format models for conversion between CHAR and NUMBER are given late.

 

TO_NUMBER ( s CHAR [, fmt VARCHAR2 [, nlpparms ] ) returN NUMBER

TO_NUMBER ( s CHAR [, fmt VARCHAR2 [, nlpparms ] ) returN NUMBER

Converts character string to number in the forma specified by fmt.

 

Format   Example Desciption

9              9999                        significant digit

0              0999                        leading zero

$              $999

B             B999                       leading blank

MI           999MI                     Trailing minus sign

S              S999                        leading sign

PR           999PR                     angle bracket for negative values

D             99D99                     decimal separator

G             9G99                       group separator

C             C999                       ISO currency symbol

L              L999                        local currency symbol

,               9,999                       comma

.               99.99                       period

V             999V99                   10n multiplier; n is the number of 9s after V

EEEE       9.99EEEE                scientific notation

RN, rn     RN                          upper or lower-case Roman numeral.

 

2.12.5     Date Functions

 

ADD_MONTHS (dt DATE,  m NUMBER ) return DATE

ADD_MONTHS (m NUMBER, dt DATE ) return DATE

Add or subtract  |m| month the dt. If dt  s day is more than the last day of  new date, the last day of the month will appear the resultant month.

 

LAST_DAY( dt DATE ) return DATE

Returns the last day of month containing dt.

 

MONTHS_BETWEEN ( dt1 DATE, dt2 DATE ) return NUMBER

Returns the month between dt1 and dt2. The result is positive if dt1 is later than dt2. Otherwise the result is negative. If dt1 and dt2 fall on the same days of month, the result is integer. Otherwise the result is a fraction number.

 

NEW_TIME ( dt DATE, zone1 VARCHAR2, zone2 VARCHAR2)

Convert date and time in dt in zone1 to date and time in zone2. The possible values for zone1 and zon2 are

 

                Value                     Description

                AST                        Atlantic standard time

                ADT                       Atlantic daylight time

                BST                        Bering standard time

                BDT                        Bering daylight time

                CST                        Central standard time

                CDT                        Center daylight time

                EST                         East standard time

                EDT                        East daylight time

                GMT                       Greenwich Mean time

                HST                        Alaska-Hawaii standard time

                HDT                       Alaska-Hawaii daylight time

                MST                       Mountain standard time

                MDT                      Mountain daylight time

                MST                       Atlantic standard time

                NST                        new-found land standard time

                PST                         Pacific standard time

                PDT                        Pacific daylight time

                YST                        Yukon standard time

                YDT                        Yukon daylight time

 

NEXT_DAY ( dt DATE, day VARCHAR2) return DATE

Returns the date of first day of the week named by day that is later than date dt. Day must be the seven day of a week.

 

ROUND (dt DATE [, fmt VARCHAR2] ) return DATE

Rounds dt to a time unit specified by fmt:

 

Format Model                                                       Description

CC, SCC                                                 century

SYEAR,  YYYY, SYEAR, YYY, YY, Y                year

Q                             quarter

MONTH, MON, MM                                          month

WW                                                                       first day of a week in a year

W                                                                            first day of week in a month

DDD, DD, J                                                           day

DAY, DY, D                                                          nearest Sunday

HH, HH12, HH24                                  hour

MI                                                                           minute

 

SYSDATE return DATE

Returns system date and time.

 

TRUNCT ( dt DATE [, fmt VARCHAR ] ) return DATE

Returns dt with time portion truncated. The format is the same as those used in round.

 

2.12.6     Miscellaneous Functions

 

DECODE ( expr, search1, result1 [, search2, result2]   [default] )

Compare expr with search i and return result if expr matches search i. If no match is found then, return default.

 

DUMP ( expr DATE [, fmt BINARY_INTEGER [, pos BINARY_INTEGER [, pos BINARY_INTEGER ]]] ) return VARCHAR2

 

DUMP ( expr NUMBER [, fmt BINARY_INTEGER [, pos BINARY_INTEGER [, pos BINARY_INTEGER ]]] ) return VARCHAR2

 

DUMP ( expr VARCHAR2 [, fmt BINARY_INTEGER [, pos BINARY_INTEGER [, pos BINARY_INTEGER ]]] ) return VARCHAR2

 

Returns internal representation of expression expr. The fmt can be 8 (octal), 10 (decial), 16 (hexadecimal) and 17(single character). Pos and len specifies which part is dumped. The default is the whole expression in decimal.

 

GREATEST ( e1, e2, d3 ,   )

Returns the largest value in a list of expressions.

 

LEAST ( e1, e2, d3 ,   )

Returns the smallest value in a list of expressions.

 

NVL(  s1 CHAR, s2 CHAR ) return CHAR

NVL(  dt1 DATE, dt2 DATE) return DATE

NVL(  b1 BOOLEAN, b2 BOOLEAN) return BOOLEAN

NVL( n1 NUMBER, n2 NUMBER) return NUMBER

NVL(  s1 VARCHAR2, s2 VARCHAR2 ) return VARCHAR2

Return first expression is it is NULL. Otherwise returns the second expression.

 

UID return VARCHAR2

Returns unique identification number assigned to the current Oracle user.

 

USER return VARCHAR2

Returns username of  the current Oracle user.

 

USERENV ( s VARCHAR2 ) return VARCHAR2

Returns information about current session. The s determine the information to retrieve:

 

                String                    Result from USERENV

                 ENTRYID             auditing entry identifier

                 LABEL                 returns the session label; available only in Trusted Oracle.

                 LANGUAGE        Returns language, territory, and database character set in use.

                 SESSIONID         Returns the auditing session identifier.

                 TERMINAL         Returns operating system identifier for the session terminal.

 

You cannot specify  ENTRYID , or  SESSIONID  option in SQL statement that access a remote database.

 

VSIZE ( e DATE ) return NUMBER

VSIZE ( e NUMBER ) return NUMBER

VSIZE ( e VARCHAR2 ) return NUMBER

Returns the number of bytes in internal representation of expression e. If e is NULL, then NULL is returned.