PL-SQL Functions Comparison Operators
Select Operators
Single Row Functions for Numbers, Chars and Dates Conversion Functions
Miscellaneous Single Row Functions Aggregate Functions
Analytical Functions
Object Reference Functions Date Format Models
Date Prefixes and Suffixes Number Format Models Comparison Operators
Table 1-1. Comparison Operators
Operator What it does
= true if two expressions are equal
!= ^= -= <> logically equivalent—true if two expressions are not equal > True if left expression is greater than right expression
>= True if left expression is greater than or equal to right expression < True if left expression is less than right expression
<= True if left expression is less than or equal to right expression IN Is equal to any member of a set or subquery
NOT IN Does NOT equal any member of a set or subquery
ANY, SOME True if one or more of the values in the list of expressions or subquery satisfies the condition
ALL True if all of the values in the list of expressions or subquery satisfies the condition BETWEEN x
AND y True if greater than or equal to x and less than or equal to y (can be reversed in meaning with NOT) EXISTS True if the subquery returns at least one row (can be reversed in meaning with NOT) LIKE pattern
[ESCAPE 'c'] 'True if expression or subquery matches pattern. '%' matches any sequence of characters, '_' matches any single character. If ESCAPE is used, the character 'c' causes the character following to be taken literally (can be reversed in meaning with NOT).
IS NULL TRUE if the value is NULL (can be reversed in meaning with NOT)
Select Operators
Table 1-2. Select Operators (Sets)
Operator What it does
UNION This combines the results of two queries and returns the set of distinct rows returned by either query
UNION ALL This combines the results of two queries and returns all rows returned by either query, including duplicates
INTERSECT This combines the results of two queries and returns the set of distinct rows returned by both queries
MINUS This combines the results of two queries and returns the distinct rows that were in the first query, but not in the second
Table 1-3. Other Select Operators
Operator What it does
(+) Denotes that the preceding column is an outer join
* Wildcard operator. Equals all columns in a select statement PRIOR Denotes a parent-child relationship in a tree-structured query ALL Include all duplicate rows in a query (the default)
DISTINCT Eliminate duplicates in a result set
Single Row Functions Number Functions
Table 1-6. Single Row Number Functions
Function What it does
ABS(n) Returns absolute value of n ACOS(n) Returns arc cosine of n in radians ASIN(n) Returns arc sine of n in radians ATAN(n) Returns arc tangent of n, in radians
ATAN2(n,m) Returns the arc tangent of n and m, in radians
BITAND(n,m) Computes the bitwise logical AND of the bits of n and m, where n and m are nonnegative integers. Returns an integer.
CEIL(n) Ceiling function—returns the smallest integer >= n COS(n) Returns the cosine of n where n is in radians
COSH(n) Returns the hyperbolic cosine of n where n is in radians
EXP(n) Returns en
FLOOR(n) Returns the largest integer <= n LN(n) Returns the natural log of n LOG(m,n) Returns the base m log of n
Function What it does
MOD(m,n) Returns the modulus of m, n—the remainder of m divided by n. (Returns m when n=0)
POWER(m,n) Returns m raised to the nth power
ROUND (m[,n]) Rounds m to the nearest n places. Where n is omitted, default is zero. n must be an integer
SIGN(n) For n < 0, returns –1, for n > 0, returns 1, for n = 0, returns 0
SIN(n) Returns sine(n) where n is in radians
SINH(n) Returns the hyperbolic sine(n) where n is in radians SQRT(n) Returns the square root of n
TAN(n) Returns the tangent(n) where n is in radians
TANH(n) Returns the hyperbolic tangent(n) where n is in radians TRUNC (m[,n]) Truncate. Returns m truncated to n places. Where n is
omitted, it returns the integer value of m. WIDTH_BUCKET
(exp,min,max,num) Returns the “bucket” in which exp belongs, where min is the minimum value, max is the maximum value, and num is the number of divisions (buckets) to use
Character Functions
Table 1-7. Character Single Row Functions
Function What it does
CHR (n) Returns the character whose binary value is n. Accepts USING NCHAR_CS clause
CONCAT (char1,char2) Combines two strings, char1 and char2 INITCAP(char) Returns char with the first character of each
word in char capitalized
LOWER(char) Returns char with all characters converted to lowercase
LPAD(char1,n[,char2]) Returns char1 padded on the left to width n with character sequence in char2. Default padding is a single blank (space).
LTRIM(char[,set]) Returns char with initial characters in set removed from the left. Default set is a blank character (space).
NLS_INITCAP(char[,nlsparam]) Returns char with the first character of each word in char capitalized. Accepts an NLS parameter.
NLS_LOWER(char[,nlsparam]) Returns char with all characters converted to lowercase. Accepts an NLS parameter.
NLSSORT(char[,nlsparam]) Returns language specific sort of char. Accepts an NLS parameter.
NLS_UPPER(char[,nlsparam]) Returns char with all characters converted to uppercase. Accepts an NLS parameter.
Function What it does
REPLACE(char[,searchstring[,replacestring]]) Returns char with searchstring replaced by replacestring. Where replacestring is omitted or null, all instances of searchstring are removed. Where searchstring is omitted or null, char is returned.
RPAD(char1,n[,char2]) Returns char1 padded on the right to width n with character sequence in char2. Default padding is a single blank (space).
RTRIM(char[,set]) Returns char with initial characters in set removed from the right. Default set is a blank character (space).
SOUNDEX(char) Returns the phonetic equivalent of char. Allows for searches for words that sound alike but are spelled differently.
SUBSTR(string,n[,m]) also:
SUBSTRB - bytes SUBSTRC - unicode
SUBSTR2 - UCS2 codepoints SUBSTR4 - UCS4 codepoints
Returns the substring of string, starting at position n, for a length of m (or to the end of string if m is not present)
TRANSLATE(char,from,to) Returns char, with all occurrences of characters in the from string replaced with the
corresponding character in the to string. If to is shorter than from, then from characters
without a corresponding to character will be removed. Empty to returns NULL, not an empty string.
TREAT(exp AS [[REF] [schema.]] type) Changes the declared type of exp to type TRIM([[LEADING|TRAILING|BOTH]
[trimchar]FROM]source) Returns source with leading and/or trailing trimchars removed. Default trimchar is a blank space, default action is to remove both leading and trailing blank spaces.
UPPER (char) Returns char with all characters converted to uppercase
ASCII (char) Returns the number value of the first character of char
INSTR(str,substr[,pos[,occur]]) also:
INSTRB - bytes INSTRC - unicode
“In string” function. Returns the position of the occurrence occur of substr in str, starting at pos. Default for pos and occur is 1. If pos is negative, search works backwards from the end of str.
Function What it does
INSTR2 - UCS2 codepoints INSTR4 - UCS4 codepoints LENGTH (char)
also:
LENGTHB - bytes LENGTHC - unicode
LENGTH2 - UCS2 codepoints LENGTH4 - UCS4 codepoints
Returns the length of char
Date Functions
Table 1-8. Date Single Row Functions
Function What it does
ADD_MONTHS(d,n) Returns the date d plus n months. If d is the last day of the month, or d+n would be past the end of the month,
returns the last day of the month.
CURRENT_DATE Returns the current Gregorian date as datatype DATE, in the session specific time zone
CURRENT_TIMESTAMP
[(precision)] Returns the current date and time as datatype TIMESTAMP WITH TIME ZONE, in the session specific time zone. Precision defaults to 6 places.
DBTIMEZONE Returns the time zone of the database
EXTRACT (datetime FROM expr) datetime can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE,
TIMEZONE_REGION, or TIMEZONE_ABBR, and expr can be either an internal value or datetime value expression
FROM_TZ(timestamp,
time_zone) Returns timestamp converted to a TIMESTAMP WITH TIME ZONE value, using time_zone LAST_DAY(date) Returns the date of the last day of the month containing
date
LOCALTIMESTAMP [(precision)] Returns the current date and time of the session in datatype TIMESTAMP of precision
MONTHS_BETWEEN(date1,
date2) Returns the number of months between date1 and date2 NEW_TIME(date,zone1,zone2) Returns date converted from time zone zone1 to zone2.
NLS_DATE_FORMAT must be set to a 24-hour format. NEXT_DAY(date,weekday) Returns the next weekday later than date where weekday
is the day of the week or its abbreviation
Function What it does
literal. char can be 'DAY,' 'HOUR,' 'MINUTE,' or 'SECOND,' or an expression that resolves to one of those
NUMTOYMINTERVAL (n, char) Returns n converted to an INTERVAL YEAR TO MONTH literal. char can be 'MONTH' or 'YEAR' or an expression that resolves to one of those
ROUND (date[,fmt]) Returns date rounded to the nearest unit specified by the format model fmt. Defaults to the nearest day.
SESSIONTIMEZONE Returns the time zone of the current session, either as a time zone offset or a time zone region name, depending on the format used for the most recent ALTER SESSION statement
SYS_EXTRACT_UTC (datetz) Extracts the UTC value of datetz where datetz is a datetime with time zone displacement
SYSDATE Returns the current date and time
SYSTIMESTAMP Returns the system timestamp in TIMESTAMP WITH TIME ZONE datatype
TO_DSINTERVAL(char [nlsparm]) Converts char to an INTERVAL DAY TO SECOND type TO_TIMESTAMP
(char[,fmt[nlsparm]]) Converts char to datatype of TIMESTAMP. fmt specifies the format of char if other than the default for datatype TIMESTAMP
TO_TIMESTAMP_TZ
(char[,fmt[nlsparm]]) Converts char to datatype of TIMESTAMP WITH TIME ZONE. fmt specifies the format of char if other than the default for datatype TIMESTAMP WITH TIME ZONE. TO_YMINTERVA(char) Converts char to an INTERVAL YEAR TO MONTH type TRUNC (date[,fmt]) Returns date truncated to the time unit specified by fmt. If
fmt is omitted, date is truncated to the nearest day. TZ_OFFSET(tzname |
SESSIONTIMEZONE | DBTIMEZONE | '+|-hh:mi')
Returns the timezone offset
Conversion Functions
Table 1-9. Conversion Single Row Functions
Function What it does
ASCIISTR(string) Returns the ASCII string in the database language of string which can be in any character set. Non-ASCII characters are converted to their UTF-16 binary values. BIN_TO_NUM(expr[,expr…]) Converts the binary bits of expr,expr,… to a number.
Example: BIN_TO_NUM(1,1,0,1) returns 13. CAST(expr | [MULTISET] (subquery)
AS type) Converts from one built in datatype or collection type to another CHARTOROWID(char) Converts char to type ROWID
COMPOSE('string') Converts string to its Unicode string equivalent in the same character set
Function What it does
[,source_set]) dest_set character set. If source_set is not specified, the database character set is assumed.
DECOMPOSE(string [CANONICAL | COMPATIBILITY])
Returns a unicode string decomposed from its fully normalized form. If CANONICAL(the default) is used, the result can be recomposed with COMPOSE.
HEXTORAW (char) Returns hexadecimal digits of char as RAW
NUMTODSINTERVAL (n, char) Converts number n to an INTERVAL DAY TO SECOND literal. char can be 'DAY,' 'HOUR,' 'MINUTE,' or 'SECOND' NUMTOYMINTERVAL (n, char) Converts number n to an INTERVAL YEAR TO MONTH
literal. char can be 'YEAR or 'MONTH'
RAWTOHEX(raw) Converts raw to its hexadecimal equivalent character value
RAWTONHEX(raw) Converts raw to its hexadecimal equivalent NVARCHAR2 character value
ROWIDTOCHAR(rowid) Converts rowid to a VARCHAR2 18 characters long ROWIDTONCHAR(rowid) Converts rowid to a NVARCHAR2 18 characters long TO_CHAR (nchar | clob | nclob) Converts an NCHAR, NVARCHAR2, CLOB or NCLOB value
to the underlying database character set
TO_CHAR (date [,fmt[nlsparm]]) Converts date to VARCHAR2, using format fmt and any nlsparm
TO_CHAR (num [,fmt[nlsparm]]) Converts num to VARCHAR2, using format fmt and any nlsparm
TO_CLOB (lob_col|char) Converts lob_col or char to CLOB value
TO_DATE char [,fmt[nlsparm]] Converts char to a date, using the format fmt and any nlsparm. If fmt is not specified, then the default date format is used.
TO_DSINTERVAL (char [nlsparm]) Converts char to an INTERVAL DAY TO SECOND literal TO_LOB(long_col) Converts the LONG or LONG RAW value of long_col to
LOB values
TO_MULTI_BYTE(char) Converts single byte char to multibyte characters
TO_NCHAR(char [,fmt[nlsparm]]) Converts a string from the database character set to the national character set
TO_NCHAR (datetime |
interval[,fmt[nlsparm]]) Converts a date, time, or interval value from the database character set to the national character set TO_NCHAR (n [,fmt[nlsparm]]) Converts a number to a string in the NVARCHAR2
character set
TO_NCLOB (lob_column | char) Converts char or lob_column to NCLOB data, using the national character set
TO_NUMBER(char[,fmt[nlsparm]]) Converts char to a number, using fmt as the format specifier
TO_SINGLE_BYTE(char) Returns char with any multibyte characters converted to the corresponding single byte characters
TO_YMINTERVAL(char [nlsparm]) Converts char to an INTERVAL YEAR TO MONTH literal TRANSLATE (text USING CHAR_CS | Returns text translated into the database character set
Function What it does
NCHAR_CS) (USING CHAR_CS) or the national character set (USING NCHAR_CS)
UNISTR(string) Returns string in Unicode using the database Unicode character set
Miscellaneous Single Row Functions
Table 1-10. Miscellaneous Single Row Functions
Function What it does
BFILENAME('dir','fname') Returns a locator for an LOB binary file on the filesystem. dir is the database object that is an alias for the full pathname of the file directory, fname is the actual file name.
COALESCE(expr[,expr,...]) Returns the first nonnull expression in a list of expressions
DECODE(expr,search ,result [ ,search,result...][,default])
Searches expr for search, returning the specific result for each search. Returns default if search is not found.
DEPTH(correlation_int) Returns the number of levels in the path specified by an UNDER_PATH condition
DUMP(expr[,return_fmt
[,start[,length]]]) Returns a VARCHAR2 value with the datatype, length, and internal representation of expr, using the format of return_fmt. Returns entire internal representation unless start and optionally length are specified.
EMPTY_BLOB() Returns a locator for a BLOB, allowing you to initialize the BLOB
EMPTY_CLOB() Returns a locator for a CLOB, allowing you to initialize the CLOB
EXISTSNODE(XML_Instance, path
[expr]) Walks the XML tree and returns success if a node is found that matches the specified path EXTRACT (XML_Instance, path
[expr]) Walks the XML tree and, if nodes are found which match the specified path, returns those nodes EXTRACTVALUE(XML_Instance, path
[expr]) Walks the XML tree and, if nodes are found that match the specified path, returns the scalar value of those nodes
GREATEST(expr[,expr,...]) Returns the expression in the list with greatest value. All data types are implicitly converted to the data type of the first expression. Character comparisons use the database character set.
LEAST(expr[,expr,...]) Returns the expression in the list with least value. All data types are implicitly converted to the data type of the first expression. Character comparisons use the database character set.
NLS_CHARSET_DECL_LEN (bytes,set_id)
Returns the declaration width of the NCHAR column of width bytes and a character set ID of set_id
NLS_CHARSET_ID(text) Returns the number of a character set ID with a character set name of text
Function What it does
NLS_CHARSET_NAME(num) Returns the character set name of the character set with ID num
NULLIF(expr1,expr2) Returns null if expr1and expr2 are equal, else returns expr1
NVL(expr1,expr2) Returns expr2 if expr1 is NULL, else returns expr1 NVL2(expr1,expr2,expr3) Returns expr2 if expr1 is NOT NULL, else returns expr3 PATH (correlation_int) Returns the relative path to the resource specified in an
UNDER_PATH or EQUALS_PATH condition SYS_CONNECT_BY_PATH
(column,char)
Returns the path of a column value from root to node in an hierarchical query. Column values are separated by char.
SYS_CONTEXT('namespace', 'param'[,len])
Returns a VARCHAR2 with the value of param of
namespace. Return is 256 bytes unless overridden by len. SYS_DBURIGEN(col|attr
[rowid][,col|attr [rowid],...] [,'text()'])
Generates a URL that can be used to retrieve an XML document from one or more columns col or attributes attr with or without a rowid
SYS_EXTRACT_UTC(time) Returns the UTC from time where time is a datetime with time zone displacement
SYS_GUID() Generates and then returns a Globally Unique IDentifier (GUID) of 16 RAW bytes
SYS_TYPEID(obj_val) Returns the typeid of an object type operand
SYS_XMLAGG(expr [fmt]) Creates a single well-formed XML document from multiple documents
SYS_XMLGEN(expr [fmt]) Creates a well-formed XML document from a database row/column expression
UID Returns the UID of the current session user UPDATEXML(XML_instance, path,
expr) Updates an XML document by searching for the node specified in the path, then replaces either the node or the scalar value of the node, depending on argument types USER Returns the username of the current session user USERENV(param) Returns a variety of information about the current
session. While deprecated in favor of SYS_CONTEXT, this is retained for backward compatibility.
VSIZE(expr) Returns the number of bytes used by the value represented by expr
XMLAGG(XML_instance [ORDER BY
sortlist]) Returns a well-formed XML document by aggregating a series of XML fragments. The returned document is a simple aggregate and no formatting is supported. XMLCOLATTVAL Creates an XML fragment for one or more columns of a
single row. The format of the fragment is fixed as <column name=”column name”>column
value</column>. XMLCONCAT(XML_instance [,
XML_instance,...]) Returns an XML fragment created by concatenating a series of XML fragments or elements XMLFOREST Creates an XML fragment for one or more columns of a
Function What it does
<column name>column value</column name>.
XMLSEQUENCE Used to “unroll” a stored XMLType into multiple rows for further processing as individual elements
XMLTRANSFORM Applies an XSL style sheet to an XML document and returns the resulting new XML document
Aggregate Functions
All of the aggregate functions described below can have an analytical clause appended to them using the OVER (analytical_clause) syntax. For space considerations, we've omitted this from the Function column. Table 1-11. Aggregate Functions
Function What it does
AVG([DISTINCT|ALL] expr) Computes the average of the rows returned by expr. If the DISTINCT keyword is used,
duplicate rows will be excluded from the calculation.
CORR( expr1 , expr2 ) Calculates the coefficient of correlation between expr1 and expr2
COUNT(* | [DISTINCT|ALL] expr) Returns the number of [DISTINCT] rows in the expr that are not null, or if * is specified, the total number of rows, including duplicates and nulls
COVAR_POP( expr1, expr2 ) Given a set of pairs, expr1 and expr2, where nulls are excluded, returns the population covariance
COVAR_SAMP( expr1, expr2 ) Given a set of pairs, expr1 and expr2, where nulls are excluded, returns the sample covariance
CUME_DIST(expr[,expr...]) WITHIN GROUP (ORDER BY expr [DESC|ASC] [NULLS [FIRST| LAST])
Given a list of values, finds and returns the cumulative distribution of a single value within that list
DENSE_RANK(expr[,expr...]) WITHIN GROUP
(ORDER BY expr) Given an ordered group of rows, finds and returns the rank of a single value within that group
FIRST ORDER BY expr [DESC|ASC] [NULLS
[FIRST|LAST]) Returns the first row or rows from a set based on the specified sort order. If multiple rows tie as “first” then all tied rows will be returned. Used in an aggregate function.
GROUP_ID() Used in GROUP BY specification to distinguish duplicate groups
GROUPING(expr) Used to distinguish superaggregate rows from regular grouped rows when ROLLUP and CUBE are used
GROUPING_ID(expr[,expr...]) Returns the number of the GROUPING bit vector for a row
Function What it does
[FIRST|LAST]) on the specified sort order. If multiple rows tie as “last” then all tied rows will be returned. Used in an aggregate function.
MAX([DISTINCT|ALL] expr) Returns the maximum value of expr. If the DISTINCT keyword is used, duplicate rows will be excluded from the calculation.
MIN([DISTINCT|ALL] expr) Returns the minimum value of expr. If the DISTINCT keyword is used, duplicate rows will be excluded from the calculation.
PERCENTILE_CONT(expr) WITHIN GROUP
(ORDER BY expr [DESC|ASC]) Given a list of values and a specified percentile ranking, returns the interpolated value of that percentile by assuming a continuous
distribution of data in the list PERCENTILE_DISC(expr) WITHIN GROUP
(ORDER BY expr [DESC|ASC]) Given a list of values and a specified percentile ranking, returns the smallest value that meets or exceeds that percentile rank by assuming a discrete distribution of data in the list
PERCENT_RANK(expr) WITHIN GROUP (ORDER BY expr [DESC|ASC][NULLS FIRST|LAST])
Given a list of values, calculates the
hypothetical rank of a single value within that list
RANK(expr) WITHIN GROUP (ORDER BY expr [DESC|ASC][NULLS FIRST|LAST])
Returns the rank (ordering) of expr in the group of values returned by the order by expression
STDDEV([DISTINCT|ALL] expr) Returns the standard deviation of expr STDDEV_POP([DISTINCT|ALL] expr) Returns the square root of the population
variance from computing the standard deviation of expr
STDDEV_SAMP([DISTINCT|ALL] expr) Returns the square root of the cumulative sample standard deviation of expr
SUM([DISTINCT|ALL] expr) Returns the sum of expr. Distinct eliminates duplicates from the set of values being summed.
VAR_POP(expr) Returns the population variance of expr. Nulls are removed from the calculation.
VAR_SAMP(expr) Returns the sample variance of expr. Nulls are removed from the calculation.
VARIANCE([DISTINCT|ALL] expr) The variance of expr, with duplicates removed if DISTINCT is specified
Table 1-12. Regression Functions
Function What it does
REGR_SLOPE(expr,expr2) Returns the slope of a least squares regression line of the set of number pairs defined by (expr,expr2)
Function What it does
REGR_INTERCEPT(expr,expr2) Returns the Y intercept of a least squares regression line of the set of number pairs defined by (expr,expr2)
REGR_COUNT(expr,expr2) Returns the number of NOT NULL pairs used to fit the least squares regression line of the set of number pairs defined by (expr,expr2) REGR_R2(expr,expr2) Returns the R2 value (coefficient of determination) of a least
squares regression line of the set of number pairs defined by (expr,expr2)
REGR_AVGX(expr,expr2) Returns the average value of expr2 of a least squares regression line of the set of number pairs defined by (expr,expr2) after removing nulls from the calculation
REGR_AVGY(expr,expr2) Returns the average value of expr of a least squares regression line of the set of number pairs defined by (expr,expr2) after removing nulls from the calculation
REGR_SXX(expr,expr2) Returns the value of calculating REGR_COUNT(expr, expr2) * VAR_POP(expr2) with nulls removed from the calculation REGR_SYY(expr,expr2) Returns the value of calculating REGR_COUNT(expr, expr2) *
VAR_POP(expr) with nulls removed from the calculation REGR_SXY(expr,expr2) Returns the value of calculating REGR_COUNT(expr, expr2) *
COVAR_POP(expr,expr2) with nulls removed from the calculation
Analytical Functions
All of the aggregate functions described above can also have analytic functionality, using the OVER (analytical_clause) syntax. For space considerations, we've declined to list them twice. Note that you cannot nest analytic functions.
Table 1-13. Analytical Functions
Function What it does
FIRST_VALUE(expr) OVER (analytical_clause) Returns the first in the ordered set of expr LAG(expr[,offset][,default]) OVER
(analytical_clause) Provides access at a point offset prior to the cursor in a series of rows returned by expr LAST_VALUE(expr) OVER (analytical_clause) Returns the last in the ordered set of expr LEAD(expr[,offset][,default]) OVER
(analytical_clause) Provides access at a point offset beyond the cursor in a series of rows returned by expr NTILE(expr) OVER (analytical_clause) Divides the ordered dataset into expr
Function What it does number of buckets
RATIO_TO_REPORT(expr) OVER
(analytical_clause) Returns the ratio of expr to the sum returned by analytical_clause ROW_NUMBER(expr) OVER
([partition_clause]order_by_clause) Assigns a unique number to each row
Object Reference Functions
Table 1-14. Object Reference Functions
Function What it does
DEREF(expr) Returns the object reference of expr. Without this, an the object ID of the reference would be returned.
MAKE_REF(table|view,key
[,key...]) Returns a REF to a row of an object view or table REF(correlation_var) Returns the REF value of correlation_var
REFTOHEX(expr) Converts expr to its hexadecimal equivalent where expr is a REF
VALUE(correlation_var) Returns the value associated with the correlation_var
Date Format Models
Table 1-15. Date Format Models
Element Value Returned
- / , . ; “text” Quoted text and punctuation are reproduced in the result AD A.D. Indicates date that is AD. Periods optional
AM A.M. PM
P.M. Before or after noon. Periods optional BC B.C. Indicates date that is BC. Periods optional CC SCC Century (SCC precedes BC century with -) D The day of week (1–7)
DAY The name of the day of the week (Monday, Tuesday, etc.). Padded to 9 characters.
Element Value Returned DDD The number of the day of year (1–366)
DY The name of the day of the week, abbreviated
E Abbreviated era name (for Japanese Imperial, ROC Official, and Thai Buddha calendars)
EE Full era name
FF [1–9] Fractional seconds. 1–9 specifies the number of digits HH Hour of day(12-hour clock)
HH12 Hour of day (12-hour clock) HH24 Hour of day (24-hour clock) IW Number of Week of the year IYY IY I Last 3, 2, or 1 digit(s) of ISO year IYYY 4-digit ISO year
J Julian day(number of days since January 1, 4712 BC) MI Minute (0–59)
MM Month (01–12)
MON JAN, FEB, MAR, etc.
MONTH Full month name, padded to 9 characters Q Quarter of year where JAN–MAR = 1 RM Month in Roman numerals (I–XII; JAN = I)
RR Last two digits of the year, for years in previous or next century (where previous if current year is <=50, next if current year >50) RRRR Round year. Accepts 4 or 2 digit input, 2 digit returns as RR. SS Seconds (0–59)
SSSSS Seconds past midnight (0–86399)
TZD Abbreviated Time Zone String with Daylight Savings TZH Time zone hour
TZM Time zone minute
WW The week of the year (1–53) W The week of the month X Local radix character
Y, YYY Year, with comma as shown YEAR
SYEAR
Year, fully spelled out. For SYEAR, BC dates use “-”
Y YY YYY
Final one, two, or three digits of the year
The following prefixes can be added to date formats:
FM The fill mode toggle. Suppresses blank padding of MONTH or DAY FX Specifies that the format of TO_DATE functions must be an exact match
The following suffixes may be added to date formats:
TH converts to an ordinal number ("5TH") SP Spells out the number ("FIVE")
SPTH or THSP Spells out the ordinal number ("FIFTH")
Number Format Models
Table 1-16. Number Format Models
Element Example Value Returned
, 9,999 Returns a comma at the position specified
. 99.99 Returns a period (decimal point) at the position specified $ $9999 Leading dollar sign
0 0999 Returns value with leading zeros 0 9990 Returns value with trailing zeros
9 9999 Returns value with the specified number of digits. Leading space if positive, – if negative. Leading zeros are blank, except when integer portion is zero, then a single leading zero is returned. B B9999 As in 9, above, but returns a blank in all cases for leading zeros C C999 Returns the ISO currency symbol
D 99D99 Returns the NLS decimal character in the specified position EEEE 9.9EEEE Returns value in scientific notation
FM FM90.9 Returns a value without leading or trailing blanks
G 9G999 Returns the value with the NLS group separator in the specified position
L L999 Returns the value with the NLS Local Currency Symbol in the specified position. Negative values have a trailing minus sign (–), positive values with a trailing blank.
PR 9999PR Returns negative values in <angle brackets>, positives have leading and trailing blanks
RN rn RN rn Returns the value as Roman numerals, in the case-specified
S S9999
9999S Returns the value with a + or – sign denoting positive or negative value in the position shown (can only be first or last position).
TM TM “Text minimum.” Returns the smallest number of characters possible and is case-insensitive. Default is TM9 that uses fixed notation up to 64 characters, then scientific notation.
U U9999 Returns the “Euro” (or other) NLS dual currency symbol in the specified position
Element Example Value Returned
V 999V99 Returns a value multiplied by 10 times the number of 9s specified after the V
X XXXX Returns the Hexadecimal value. Precede with a 0 to have leading zeros, or FM to remove the leading blank.
Advanced cursors Introduction to Oracle SQL & PL/SQL
Updated 03/20/2005 • Cursor FOR Loops
• Parameterized Cursors (passing parameters to cusors) • Ref Cursor
• BULK COLLECT INTO Cursor FOR Loops
The cursor FOR loop provides an elegant, simple syntax to to iterate over a result set. To underscore the advantages of cursor FOR loops, consider the following PL/SQL block which uses a basic loop.
SET SERVEROUTPUT ON DECLARE
-- EMP_CURSOR will retrieve all columns and all rows from the EMP table CURSOR emp_cursor IS
SELECT * FROM emp;
emp_record emp_cursor%ROWTYPE; BEGIN
OPEN emp_cursor; LOOP
--Advance the pointer in the result set, assign row values to EMP_RECORD FETCH emp_cursor INTO emp_record;
--Test to see if no more results EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_record.ename||' [' ||emp_record.empno||']'); END LOOP;
CLOSE emp_cursor; END;
/
• Now examine the same query using a cursor FOR loop:
DECLARE
-- EMP_CURSOR will retrieve all columns and all rows from the EMP table CURSOR emp_cursor IS
SELECT * FROM emp; BEGIN
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.ename||' ['||emp_record.empno||']'); END LOOP;
END; /
• You can use a cursor for loop without a declared cursor by including a query in the FOR statement. This can enable very compact code.
BEGIN
FOR emp_record IN (SELECT * FROM emp) LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.ename||' ['||emp_record.empno||']'); END LOOP;
END; /
• While you can use EXIT statement within a FOR cursor loop, you should not use a cursor FOR loop if you may need to exit the LOOP prematurely. Use a basic or WHILE loop instead.
Parameterized Cursors (passong parameters to cursors)
• Cursors can use variables to adjust which rows they select when opened. Instead of hard-coding a value into the WHERE clause of a query, you can use a variable as a placeholder for a literal value. The variable placeholder will substituted with the value of the variable when the cursor is opened. This makes a query more flexible.
DECLARE
v_deptno NUMBER; v_job VARCHAR2(15); v_sum_sal NUMBER;
/* Since v_deptno and v_job are declared above, they are in scope, * and can be referenced in the cursor body. They will be used as * placeholders until the cursor is opened, at which
* point the values of the variables will be substituted (bound) * into the query as literals.
*/
CURSOR emp_stats_cursor IS SELECT SUM(sal) sum_sal FROM emp WHERE deptno=v_deptno AND job=v_job; BEGIN v_deptno:=10; v_job:='MANAGER'; OPEN emp_stats_cursor;
/* When the cursor is opened, the values of the PL/SQL * variables are bound into the query.
* In this example, the cursor would return the * result set using the following query:
SELECT SUM(sal) sum_sal FROM emp
WHERE deptno=10 --current value of v_deptno is 10
AND job='MANAGER'; --current value of v_job is 'MANAGER' */
FETCH emp_stats_cursor INTO v_sum_sal; CLOSE emp_stats_cursor;
DBMS_OUTPUT.PUT_LINE(v_deptno||' : '||v_job||' : '||v_sum_sal); v_deptno:=30;
v_job:='SALESMAN'; OPEN emp_stats_cursor;
/* In this example, the cursor would
* return the result set using the following query: SELECT SUM(sal) sum_sal
FROM emp
WHERE deptno=30 --current value of v_deptno is 30
AND job='SALESMAN'; --current value of v_job is 'SALESMAN' */
FETCH emp_stats_cursor INTO v_sum_sal; CLOSE emp_stats_cursor;
DBMS_OUTPUT.PUT_LINE(v_deptno||' : '||v_job||' : '||v_sum_sal); END;
/
• This method works, but there is a better way. You can declare a cursor using parameters; then whenever you open the cursor, you pass in appropriate parameters. This technique is just as flexible, but is easier to maintain and debug. The above example adapted to use a parameterized cursor:
DECLARE
v_sum_sal NUMBER;
/* The parameters are declared in the cursor declaration. * Parameters have a datatype, but NO SIZE; that is, you
* can declare a parameter of datatype VARCHAR2, but never * VARCHAR2(20).
* As above, the parameters will be placeholders (that is, * formal parameters) until the cursor is opened, at which * point the actual parameter values will be substituted * (bound) into the query as literals.
* The parameters are in scope (that is, they can be referenced) * only inside the cursor body.
*/
CURSOR emp_stats_cursor(cp_deptno NUMBER, cp_job VARCHAR2) IS SELECT SUM(sal) sum_sal
FROM emp
WHERE deptno=cp_deptno AND job=cp_job; BEGIN
OPEN emp_stats_cursor(10,'MANAGER');
/* When the cursor is opened, the values of the parameters
* are bound into the query. In other words, the actual parameters * (values) will replace the formal parameters (placeholders). * In this example, the cursor would return the result set using * the following query:
SELECT SUM(sal) sum_sal FROM emp
WHERE deptno=10 AND job='MANAGER'; */
FETCH emp_stats_cursor INTO v_sum_sal; CLOSE emp_stats_cursor;
DBMS_OUTPUT.PUT_LINE('10 : MANAGER : '||v_sum_sal); OPEN emp_stats_cursor(30,'SALESMAN');
/* In this example, the cursor would return the result set * using the following query:
SELECT SUM(sal) sum_sal FROM emp
WHERE deptno=30 AND job='SALESMAN'; */
FETCH emp_stats_cursor INTO v_sum_sal; CLOSE emp_stats_cursor;
DBMS_OUTPUT.PUT_LINE('30 : SALESMAN : '||v_sum_sal); END;
/
• Parameterized cursors are open easier to debug in larger PL/SQL blocks. This is because the the declaration of the cursor body is often far from where the cursor is opened, but processing of the cursor's result set is usually close to where the cursor is opened.
o When opening a cursor which uses variables, you must assign appropriate values to those variables before opening the cursor. So when you are debugging how the cursor is opened,
you must confirm the appropriate variable names where the cursor is declared . This is often inconvenient.
o When using PL/SQL variables, it's difficult to confirm the values of the variables when the cursor is opened because the values could be set at an any point from the declaration on. • Parameterized cursors eliminate both these problems because the values used in the cursor can be
determined in one place, the OPEN statement. And you don't need to know the names of the cursor parameters. (Though you do need to know the order of the parameters.)
• An example which combines a cursor FOR loop with a parameterized query:
DECLARE
v_sum_sal NUMBER;
CURSOR emp_stats_cursor(cp_deptno NUMBER, cp_job VARCHAR2) IS SELECT SUM(sal) sum_sal
FROM emp
WHERE deptno=cp_deptno AND job=cp_job; BEGIN
FOR dept_job_rec IN (SELECT DISTINCT deptno,job FROM emp) LOOP OPEN emp_stats_cursor(dept_job_rec.deptno, dept_job_rec.job); FETCH emp_stats_cursor INTO v_sum_sal;
CLOSE emp_stats_cursor;
DBMS_OUTPUT.PUT_LINE(dept_job_rec.deptno ||' : '||dept_job_rec.job||' : '||v_sum_sal); END LOOP;
END; /
Ref Cursor
Ref Cursor is THE method to returns result sets to client applications (like C, VB, etc).
You cannot define ref cursors outside of a procedure or function in a package specification or body. Ref cursors can only be processed in the defining procedure or returned to a client application. Also, a ref cursor can be passed from subroutine to subroutine and a cursor cannot. To share a static cursor like that, you would have to define it globally in a package specification or body. Because using global variables is not a very good coding practice in general, Ref cursors can be used to share a cursor in PL/SQL without having global variables getting into the mix.
Last, using static cursors—with static SQL (and not using a ref cursor) —is much more efficient than using ref cursors, and the use of ref cursors should be limited to
• Returning result sets to clients
• Sharing cursors across many subroutines (very similar to the above point, actually) • Achieving your goal when there is no other efficient/effective means of doing so, such as
when dynamic SQL must be used
In short, you want to use static SQL first and use a ref cursor only when you absolutely have to.An Example of Ref cursor is here:
create or replace function sp_ListEmp return types.cursortype as
l_cursor types.cursorType; begin
open l_cursor for select ename, empno from emp order by ename; return l_cursor;
end; /
Or like this for a procedure:
create or replace procedure getemps( p_cursor in out types.cursorType ) as
begin
open p_cursor for select ename, empno from emp order by ename; end;
Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them). The cursor variables are opened with the privelegs of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. It uses the inputs to decide what database it will run a query on. Here is an example:
create or replace package types as
type cursorType is ref cursor; end;
/
create or replace function sp_ListEmp return types.cursortype as
l_cursor types.cursorType; begin
open l_cursor for select ename, empno from emp order by ename; return l_cursor;
end; /
create or replace procedure getemps( p_cursor in out types.cursorType ) as
begin
open p_cursor for select ename, empno from emp order by ename; end;
/
BULK COLLECT INTO
Introduced in Oracle8i, BULK COLLECT allows you to retrieve multiple rows of data directly into PL/SQL Collections. It will raise NO_DATA_FOUND if it doesn't find any rows, but it certainly doesn't raise TOO_MANY_ROWS if it finds more than one!
More information can be obtained HERE. DECLARE
TYPE title_aat IS TABLE OF magazine.title%TYPE INDEX BY BINARY_INTEGER;
l_titles title_aat; BEGIN
SELECT title
BULK COLLECT INTO l_titles FROM magazine;
END;
My advice regarding the kinds of cursors to use when fetching data from Oracle in PL/SQL programs it to whenever possible, use BULK COLLECT—it offers dramatic performance gains. In Oracle9i Release 2, you can even use BULK COLLECT to fetch multiple rows directly into a collection of records. Of course, when you want/need to fetch just a single row, BULK COLLECT doesn't make sense.
Explicit Cursors Introduction to Oracle SQL & PL/SQL Updated 03/20/2005 • Concepts
• Working with explicit cursors • Cursor Attributes
• Records and %ROWTYPE
Concepts of Implicit (Static) Cursors
Oracle uses cursors to process all SQL statements. From SQL*Plus, you issue a command and Oracle takes care of creating a cursor and processing the command. These types of cursors are called implicit cursors, because you (the user) cannot not name or control the cursor directly. In PL/SQL you also use implicit cursors for DML statements and single select statements.
Implicit (static) cursor: commonly refers to the good old SELECT INTO, in which Oracle implicitly opens, executes and closes the cursor for you, depositing any selected values of a single row INTO program data structures.
CREATE OR REPLACE PROCEDURE show_title (author_in IN magazine.author%TYPE) IS
l_title magazine.title%TYPE; BEGIN
SELECT title INTO l_title FROM magazine
WHERE author = author_in; END;
The single select is a simple solution, but insufficient to solve the following problems.
• You may need to process more general result sets which return more or less than one row • You may need to process rows in a specific order
• You may need to control the execution of your program depending on the result set. To address these problems you need to use explicit cursors.
Working with explicit cursors
Explicit cursors work the same way implicit cursors work, but you control the execution explicitly.
DECLARE
v_ename VARCHAR2(12); v_empno NUMBER:=7839;
CURSOR ename_cursor IS SELECT ename FROM emp WHERE empno=v_empno; BEGIN OPEN ename_cursor;
FETCH ename_cursor INTO v_ename; CLOSE ename_cursor;
END; /
Examining each statement in turn:
DECLARE ... CURSOR ename_cursor IS SELECT ename FROM emp WHERE empno=v_empno;
Oracle allocates memory and processes the query
BEGIN
OPEN ename_cursor;
Oracle binds variables, and executes query identifying the active set.
FETCH ename_cursor INTO v_ename; Oracle fetches a row from the active set, sets the value of v_ename, and advances the pointer to the active set.
FETCH ename_cursor INTO v_ename; Oracle fetches a row from the active set (as above). CLOSE ename_cursor;
END;
/ Oracle releases memory area.
Cursor Attributes
Use cursor attributes to determine whether the row was found and what number the row is.
Cursor Attributes
Attribute Description
cur%ISOPEN Returns TRUE if cursor is open.
cur%NOTFOUND Returns FALSE if the last FETCH found a row.
cur%FOUND Returns TRUE if the last FETCH found a row.. (Logical inverse of %NOTFOUND). cur%ROWCOUNT Returns the number of rows modified by the DML statement.
SQL%BULK_ROWCOUNT Returns the number of rows processed for each execution of the bulk DML operation.
Example using cursor attributes:
DECLARE
v_empno emp.empno%TYPE; v_ename emp.ename%TYPE;
CURSOR emp_cursor IS SELECT empno, ename FROM emp;
BEGIN
OPEN emp_cursor; LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%ROWCOUNT>10 or emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(INITCAP(v_ename)||' ['||v_empno||']'); END LOOP;
CLOSE emp_cursor; END;
/
Records and %ROWTYPE
Instead of fetching values into a collection of variables, you could fetch the entire row into a record like so.
DECLARE
CURSOR emp_cursor IS
SELECT empno, ename, sal, job, deptno FROM emp
WHERE deptno=30;
-- This creates a record named emp_row -- based on the structure of the cursor emp_cur emp_row emp_cursor%ROWTYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_row;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_row.ename||' ['
||emp_row.empno||'] makes '||TO_CHAR(emp_row.sal*12,'$99,990.00')); END LOOP;
END; /
You can reference the fields of a record using the syntax record_name.field_name.
In addition to basing a record on a cursor, you can also define records based on tables like so. DECLARE
CURSOR emp_cursor IS SELECT *
FROM emp
WHERE deptno=30;
emp_row emp%ROWTYPE; -- This creates a record named EMP_ROW -- based on the structure of the EMP table
BEGIN
OPEN emp_cursor; LOOP
FETCH emp_cursor INTO emp_row;
EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_row.ename||' ['||emp_row.empno ||'] makes '||TO_CHAR(emp_row.sal*12,'$99,990.00')); END LOOP; END; /
Oracle data types
====== DDL ======
Type Storage Range/Length Comments
--- --- --- ---NUMBER 16 40 digit floating point
FLOAT 16 40 digit floating point SMALLINT 16 40 digit floating point NUMBER(a,b) varies a digits, b precision FLOAT(a,b) varies a digits, b precision DECIMAL 16 40 digit
INTEGER 16 40 digits INTEGER(a) varies a digits CHAR(a) a a=(1-255) VARCHAR(a) varies 1 - 255 VARCHAR2(a) varies 1 - 2000
DATE 8 1/1/4217BC - 12/31/4712AD precision to minutes
LONG varies 0 - 2 GB stored inline, obsolete * LONG RAW varies 0 - 2 GB stored inline, obsolete * LONG VARCHAR varies 0 - 2 GB stored inline, obsolete *
BLOB varies 0 - 4 GB stored separate from table CLOB varies 0 - 4 GB stored separate from table NCLOB varies 0 - 4 GB stored separate from table BFILE ?? ?? pointer to O/S file
ROWID 8 n/a row identifier within block
* Long datatypes are discouraged in Oracle 8. Note that are long and blob datatypes are incompatible.
====== PL-SQL data types (differences) ====== Type Storage Range/Length Comments
--- --- --- ---NUMERIC
VARCHAR VARCHAR2
BLOB must be read in 32k chunks CLOB
NCLOB
Creating a table
PCTFREE = Amount of space to leave in block during insert operations. Allows room for records to grow within the same area.
PCUSED = The threshold at which the block is placed back on the free block list. INITIAL/NEXT = The initial disk allocated, and the next extent size.
LOGGING = Indicates whether operations are written to the redo logs. CREATE TABLE EMPLOYEE (
EMP_ID NUMBER(8), LNAME VARCHAR2(30), FNAME VARCHAR2(15), HIRE_DT DATE, SALARY NUMBER(8,2) ) PCTFREE 20 PCTUSED 50 STORAGE ( INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50 ) TABLESPACE ts01 LOGGING ;
/* Free table unallocated table blocks */
Creating indexes
CREATE UNIQUE INDEX EMP_IDX ON EMPLOYEE (EMP_ID) ;
/* index create - table has sorted data */
CREATE UNIQUE INDEX IDX_INVOICE_ITEMS ON INVOICE_ITEMS
(INVOICE_ID,YEAR,FREQ_CODE,FREQ_NUMBER,FIELD_NUMBER,RECORD_SEQ) TABLESPACE TS_07
NOLOGGING NOSORT ;
/* create index - constraint */ ALTER TABLE INVOICE_FORMAT
ADD CONSTRAINT PK_INVOICE_FORMAT PRIMARY KEY(INVOICE_ID) USING INDEX TABLESPACE PROD_IDX_01;
Creating constraints
/* primary key constraint */ ALTER TABLE EMPLOYEE ( CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID) );
ALTER TABLE REPORT_FORMAT
ADD CONSTRAINT PK_REPORT_FORMAT PRIMARY KEY(REPORT_ID) USING INDEX TABLESPACE PROD_IDX_01;
/* foreign key constraint */ ALTER TABLE EMPLOYEE ADD (
CONSTRAINT EMP_LOC_ASSIGN_FK FOREIGN KEY (EMP_ID,
LOC_CD)
REFERENCES LOC_REGISTRY ( EMP_ID,
LOC_CD) );
Creating and using a sequence
CREATE SEQUENCE EMP_ID_SEQ INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE CACHE 20 NOORDER ;
/ * use the next emp id, and increment the sequence */ INSERT INTO EMPLOYEE(EMP_ID, LNAME, FNAME) VALUES (EMP_ID_SEQ.NEXTVAL, 'SMITH', 'JIM') ;
/* get the current value of the sequence */
INSERT INTO EMPLOYEE(EMP_ID, LNAME, FNAME) VALUES (EMP_ID_SEQ.CURRVAL, 'SMITH', 'JIM') ;
Creating triggers
The example below illustrates versioning of the EMP_RESUME table, which contains a blob field. CREATE OR REPLACE TRIGGER EMP_RES_INS_TR
AFTER INSERT ON EMP_RES FOR EACH ROW
DECLARE VER1 NUMBER ; EBLOB BLOB ; VBLOB BLOB ; BEGIN EBLOB := EMPTY_BLOB();
SELECT (COUNT(*) + 1) INTO VER1 FROM VEMP_RES
WHERE EMP_ID =:NEW.EMP_ID ; VBLOB := :NEW.RESUME ;
INSERT INTO VEMP_RES ( EMP_ID, DOC_URL,
A_USERID, D_MODIFIED, VER_NO, RESUME) VALUES (
:NEW.EMP_ID, :NEW.DOC_URL, USER, SYSDATE, VER1, EBLOB ) ; SELECT RESUME
INTO EBLOB FROM VEMP_RES
WHERE EMP_ID =:NEW.EMP_ID AND VER_NO = VER1
FOR UPDATE ; UPDATE VEMP_RES SET RESUME = VBLOB
WHERE EMP_ID =:NEW.EMP_ID AND VER_NO = VER1 ;
END;
Renaming a table
RENAME COMPANY TO CORPORATION ;
Synonyms and Database Links
-- Synonym Creation
GRANT SELECT ON USER5.COMPANY TO USER6 ;
CREATE SYNONYM USER6.COMPANY5 FOR USER5.COMPANY ;
-- Database Link
CREATE DATABASE LINK ARCHIVE_DATA CONNECT TO USER5 IDENTIFIED BY TIGER USING 'SERVER5' ; /* user within this system can now reference tables using ARCHIVE_DATA.tablename */
Changing a column's type
ALTER TABLE CORPORATION MODIFY (COMPANY_NM VARCHAR2(100)); Moving a table
ALTER TABLE COMPANY MOVE STORAGE ( INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50 ) TABLESPACE TS_01 ; Using SQL-Plus
SQL-Plus is a query / command line utility which has some powerful formatting capabilities. Getting Started
; Command line terminator
/ Execute the current batch of commands
SET SERVEROUTPUT ON Allow messages from PL-SQL to be displayed SHOW ERRORS Show errors from last batch
EDIT Run editor, and load buffer CLEAR BUFFER Clear buffer commands & Prompt for value
@ Run commands in @filename
/**** Examples ****/
/* prompt for process id, and kill */ alter system kill session '&Victim' /
Creating a stored procedure
Below is a simple stored procedure which deletes an invoice. Note:
- variable declaration placement - the syntax for comments /* --- */
- ALL select statements must have an into statement for the result set. Oracle stored procedures must use "out" variables to return results to client programs.
- the declaration of INV_ID1 uses the column def as a prototype CREATE OR REPLACE PROCEDURE PROC_DELETE_INVOICE ( USERID1 VARCHAR2, INV_ID1 INVOICE.INV_ID%TYPE ) AS
INV_COUNT NUMBER ; BEGIN
INV_COUNT := 0;
/* check if invoice exists */ SELECT COUNT(*)
INTO INV_COUNT FROM INVOICE
WHERE INV_ID = INV_ID1 ; IF INV_COUNT > 0 THEN
DELETE FROM INVOICE WHERE INV_ID = INV_ID1 ; COMMIT ;
END IF ; END ;
All SELECT statements in PL-SQL must have an INTO clause; therefore another method is needed to display output to the console.
DBMS_OUTPUT.PUT_LINE('TEST OUTPUT'); salary := 24000;
dbms_output.put_line(salary);
Output variables
Output variables are used to return data to another procedure, or to an external application which has invoked the stored procedure.
/* sample procedure header using output variables */ TYPE INV_ARRAY IS TABLE OF NUMBER(8)
INDEX BY BINARY_INTEGER ;
CREATE OR REPLACE PROCEDURE PROC_GET_INV_NOS ( USERID1 IN VARCHAR2, INV_IDS OUT INV_ARRAY) AS
...
Arrays and structures
Arrays and structures are implemented thought the use of "tables" and "records" in PL-SQL. /* EXAMPLE OF A SIMPLE RECORD TYPE */
TYPE INVOICE_REC_TYPE IS RECORD (INV_ID INVOICE.INV_ID%TYPE, INV_DT INVOICE.INV_DT%TYPE ) ; /* ARRAY DECLARATION */
TYPE NAME_TABLE_TYPE IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER ; NAME_TABLE NAME_TABLE_TYPE ; /* ARRAY SUBSCRIPTING */ I := I + 1; NAME_TABLE(I) := 'JSMITH'; Conditionals
Sample formats of conditional branching are given below: IF <condition> THEN <statement> ;
IF <condition> THEN <statements> ; END IF;
/* sample statement, note the pipes for concatenation */ IF (COUNT1 = 0) AND (COUNT2 > 0) THEN
RETMSG := 'Security attributes have not been assigned, ' || 'you are restricted.';
ELSE
RETMSG := 'You are OK'; END IF; Looping WHILE (I < 10) LOOP /* ... SOME CMDS ... */ I = I + 1; END LOOP; Cursors
The first example depicts dbase-style row processing ; the second a more traditional "fetch" approach. PROCEDURE PROC_SCAN_INVOICES (EXPIRE_DT IN DATE)
IS
CURSOR INVOICE_CUR IS
SELECT INV_ID, INV_DT FROM INVOICE ; TYPE INVOICE_REC_TYPE IS RECORD
(INV_ID INVOICE.INV_ID%TYPE, INV_DT INVOICE.INV_DT%TYPE ) ; INVOICE_REC INVOICE_REC_TYPE ; BEGIN
FOR INVOICE_REC1 IN INVOICE_CUR LOOP
IF INVOICE_REC.INV_DT < EXPIRE_DT THEN DELETE FROM INVOICE
WHERE INV_ID = INV_REC.INV_ID ;
DBMS_OUTPUT.PUT_LINE('INVOICE DELETETED:'); DBMS_OUTPUT.PUT_LINE(INV_REC.INV_ID); END
END LOOP; END;
/* ======================================= */ CREATE OR REPLACE PROCEDURE PROC_DOCEXPIRE_RPT
( RPT_BODY OUT LONG RAW ) IS RPT_LINE VARCHAR2(1900); RPT_PART VARCHAR2(1900); RPT_LEAD VARCHAR2(200); GLIB_ID1 NUMBER ; GLIB_ID2 VARCHAR(12); ORIG_LOC_CD1 VARCHAR2(12); AUTHOR_ID1 VARCHAR2(30); CONTRIBUTORS1 VARCHAR2(80); TOPIC1 VARCHAR2(80); NBR_ACCESS1 NUMBER ; NBR_ACCESS2 VARCHAR2(12); TOT_EXPIRED1 NUMBER ; TOT_EXPIRED2 VARCHAR2(12); COUNT1 NUMBER ; RPT_BODY_PART LONG ; CURSOR CUR1 IS
SELECT GLIB_ID, ORIG_LOC_CD, AUTHOR_ID, CONTRIBUTORS, TOPIC, NBR_ACCESS FROM GEN_DOC
WHERE EXPIRE_DT < (SYSDATE + 30) ORDER BY ORIG_LOC_CD, GLIB_ID ; BEGIN SELECT COUNT(*) INTO TOT_EXPIRED1 FROM GEN_DOC WHERE STAT_CD='90'; TOT_EXPIRED2 := TO_CHAR(TOT_EXPIRED1);
RPT_LEAD := '<H5>TOTAL EXPIRED DOCUMENT COUNT TO DATE: ... ' || TOT_EXPIRED2 || '</H5><HR>' ;
RPT_LINE := '<HTML><BODY BGCOLOR=#FFFFFF>' || '<H6>ABC Corporation</H6>' ||
'<H2>Gen Doc System - Documents Expiring Within 30 Days</H2><HR>' || RPT_LEAD ;
COUNT1 := 0; OPEN CUR1;
RPT_LINE := RPT_LINE || '<TABLE>' || '<TD><U>No. Accesses</U></TD>' || '<TD><U>Document #</U></TD>' || '<TD><U>Topic</U></TD>' || '<TD><U>Author</U></TD>' ; RPT_BODY := UTL_RAW.CAST_TO_RAW(RPT_LINE); RPT_LINE := ''; LOOP COUNT1 := COUNT1 + 1; EXIT WHEN (COUNT1 > 500);
EXIT WHEN (UTL_RAW.LENGTH(RPT_BODY) > 32000); FETCH CUR1 INTO
GLIB_ID1, ORIG_LOC_CD1, AUTHOR_ID1, CONTRIBUTORS1, TOPIC1, NBR_ACCESS1 ; EXIT WHEN CUR1%NOTFOUND ;
RPT_PART := '<TR><TD>';
NBR_ACCESS2 := TO_CHAR(NBR_ACCESS1); RPT_PART := CONCAT(RPT_PART,NBR_ACCESS2); RPT_PART := CONCAT(RPT_PART,'</TD><TD>'); GLIB_ID2 := TO_CHAR(GLIB_ID1);
RPT_PART := RPT_PART || ORIG_LOC_CD1 || '-' || GLIB_ID2 || '</TD><TD>' || TOPIC1 || '</TD><TD>' ||
AUTHOR_ID1 || '</TD><TR>' ;
RPT_LINE := CONCAT(RPT_LINE, RPT_PART);
RPT_BODY_PART := UTL_RAW.CAST_TO_RAW(RPT_LINE);
RPT_BODY := UTL_RAW.CONCAT(RPT_BODY,RPT_BODY_PART); -- RPT_BODY := RPT_BODY || RPT_LINE;
RPT_LINE := ''; END LOOP;
CLOSE CUR1 ;
RPT_LINE := '</TABLE></BODY></HTML>';
RPT_BODY_PART := UTL_RAW.CAST_TO_RAW(RPT_LINE);
RPT_BODY := UTL_RAW.CONCAT(RPT_BODY, RPT_BODY_PART); EXCEPTION
WHEN OTHERS THEN BEGIN DBMS_OUTPUT.PUT_LINE('ERROR: PROC_DOCSTAT_RPT'); GLIB_ID1 := UTL_RAW.LENGTH(RPT_BODY); DBMS_OUTPUT.PUT_LINE(GLIB_ID1); END; END;
Packages
A package is a construct which bounds related procedures and functions together. Variables declared in the declaration section of a package can be shared among the procedures/functions in the body of the package.
/* package */
CREATE OR REPLACE PACKAGE INVPACK IS
FUNCTION COUNTINV (SALESREP IN VARCHAR2) RETURN INTEGER; PROCEDURE PURGEINV (INV_ID IN INTEGER) ;
END INVPACK;
/* package body */
CREATE OR REPLACE PACKAGE BODY INVPACK IS
COUNT1 NUMBER;
FUNCTION COUNTINV (SALESREP IN VARCHAR2) RETURN INTEGER IS
BEGIN
SELECT COUNT(*) INTO COUNT1 FROM INVOICE
WHERE SALES_REP_ID = SALESREP ; RETURN COUNT1 ;
END COUNTINV;
PROCEDURE PURGEINV (INV_ID1 IN INTEGER) IS
BEGIN
DELETE FROM INVOICE WHERE INV_ID = INV_ID1
END PURGEINV;
/* initialization section for package */ BEGIN
COUNT1 := 0 ; END INVPACK;
Exception Handling
The following block could appear at the end of a stored procedure: EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('End of data !!); WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('OTHER CONDITION OCCURRED !'); END;
Using Blobs
Blob variables require special handling in PL-SQL. When reading from a file to a blob, only one statement is required. When reading from a blob field to a PL-SQL variable, only 32k blocks can be processed, thus necessitating a loop construct.
/*---*/ /* Read a blob from a file, and write */ /* it to the database. */ /*---*/ set serveroutput on size 500000 ; truncate table image_test ;
create or replace directory image_dir as '/apps/temp/images' ;
create or replace procedure proc_imp_jpg (fname1 in varchar2, image_id1 in numeric) is file1 bfile ; lblob blob ; len int ; e_blob blob ; begin file1 := bfilename('IMAGE_DIR',fname1); e_blob := empty_blob();
insert into image_test (image_id, image_data) values (image_id1, e_blob )
returning image_data into lblob ; dbms_lob.fileopen(file1); len := dbms_lob.getlength(file1) ; dbms_lob.loadfromfile(lblob,file1,len); dbms_lob.filecloseall(); commit; exception
when others then begin dbms_output.put_line(sqlerrm); dbms_lob.filecloseall(); commit; end; end ; / call proc_imp_jpg('jada.jpg',101) / /*---*/ /* determine the length of */ /* a blob field */ /* by reading it */ /*---*/
CREATE OR REPLACE PROCEDURE PROC_BLOB_LENGTH (PART_ID1 NUMBER) IS SRC_LOB BLOB; BUFFER RAW(100); AMT BINARY_INTEGER := 100; POS INTEGER := 1; COUNTER INTEGER :=0; BEGIN
SELECT PART_PHOTO INTO SCR_LOB FROM PARTS
WHERE PART_ID=PART_ID1 ;
IF (SRC_LOB IS NOT NULL) THEN LOOP
DBMS_LOB.READ (SRC_LOB, AMT, POS, BUFFER); POS := POS + AMT;
COUNTER:=COUNTER+1; END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('** Source is null'); END IF;
EXCEPTION
DBMS_OUTPUT.PUT_LINE('End of data, total bytes:'); DBMS_OUTPUT.PUT_LINE(POS);
END;
/* ============ Other blob length examples ====== */
/**** Note ! These functions may return null, if the column is null ... an Oracle bug */ X := UTL_RAW.LENGTH(RPT_BODY) ;
Y := DBMS_LOB.GETLENGTH(LONG_RAW_COL); Using the Context cartridge
Managing Context is an arduous task. The best approach is to use the command line utility as much as possible. Below is a code sample which creates a policy (a policy is a construct which informs context which column on what table to scan during a search operation). The next example illustrates how to perform a search, and stored the result keys in a table.
/* create a policy, on the emp_resume table */ ctx_svc.clear_all_errors; dbms_output.put_line('Creating Policy ...'); ctx_ddl.create_policy( POLICY_NAME => 'EMP_RES_POLICY', COLSPEC => 'EMP_RES.RESUME', SOURCE_POLICY => 'CTXSYS.DEFAULT_POLICY', DESCRIPTION => 'EMP Policy',
TEXTKEY => 'EMP_ID', DSTORE_PREF => 'CTXSYS.DEFAULT_DIRECT_DATASTORE', FILTER_PREF => 'CTXSYS.HTML_FILTER', LEXER_PREF => 'CTXSYS.DEFAULT_LEXER' ); dbms_output.put_line('Indexing Policy ...'); ctx_ddl.create_index('EMP_POLICY');
/* Run a Context query, place the result key values in a table */ /* first, this table needs to be created */
CREATE TABLE EMP_CTX_RESULTS( TEXTKEY VARCHAR2(64),
TEXTKEY2 VARCHAR2(64), SCORE NUMBER,
CONID NUMBER );
/* this code can go in a stored proc */ POLICY1 := 'EMP_POLICY';
TABLE1 := 'EMP_CTX_RESULTS'; ID1 := 100 ;
QUERY1 := 'COBOL|FORTRAN';
CTX_QUERY.CONTAINS(POLICY1, QUERY1, TABLE1, 1, ID1); /* the table will contain records with a CONID of 100 */ /* ... you can use ampersand or pipe as the conditional */
Sleep and Wait
Sometimes it is necessary to delay the execution of commands, for debugging, or batch runs. /* Sleep 60 seconds */
execute dbms_lock.sleep(60); /* Sleep one hour */
execute dbms_lock.sleep(3600); Date Manipulation
/* display current time */
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "SYSDATE"
from dual;
/* insert specific date/time into table */ insert into game_schedule
( sched_id, location, game_date )
Version information
SELECT * FROM product_component_version ;
List free and used space in database SELECT sum(bytes)/1024 "free space in KB" FROM dba_free_space;
SELECT sum(bytes)/1024 "used space in KB" FROM dba_segments;
List session information SELECT * FROM V$SESSION ;
List names and default storage parameters for all tablespaces
SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, PCT_INCREASE, MIN_EXTLEN
FROM DBA_TABLESPACES;
Tablespace types, and availability of data files SELECT TABLESPACE_NAME, CONTENTS, STATUS FROM DBA_TABLESPACES;
List information about tablespace to which datafiles belong SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE, MAXBYTES,INCREMENT_BY
FROM DBA_DATA_FILES;
SELECT FILE#,T1.NAME,STATUS,ENABLED,BYTES,CREATE_BYTES,T2.NAME FROM V$DATAFILE T1, V$TABLESPACE T2
WHERE T1.TS# = T2.TS# ;
List tablespace fragmentation information SELECT tablespace_name,COUNT(*) AS fragments, SUM(bytes) AS total,
MAX(bytes) AS largest FROM dba_free_space
GROUP BY tablespace_name;
Check the current number of extents and blocks allocated to a segment SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS;
Check the extents for a given segment
SELECT TABLESPACE_NAME, COUNT(*), MAX(BLOCKS), SUM(BLOCKS) FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NUMBER ;
Extent information
SELECT segment_name, extent_id, blocks, bytes FROM dba_extents
WHERE segment_name = TNAME ;
Extent information for a table
SELECT segment_name, extent_id, blocks, bytes FROM dba_extents
WHERE segment_name = TNAME ;
List segments with fewer than 5 extents remaining SELECT segment_name,segment_type,
max_extents, extents FROM dba_segments
WHERE extents+5 > max_extents AND segment_type<>'CACHE';
List segments reaching extent limits
SELECT s.segment_name,s.segment_type,s.tablespace_name,s.next_extent FROM dba_segments s
WHERE NOT EXISTS (SELECT 1 FROM dba_free_space f
WHERE s.tablespace_name=f.tablespace_name HAVING max(f.bytes) > s.next_extent);
List table blocks, empty blocks, extent count, and chain block count SELECT blocks as BLOCKS_USED, empty_blocks
FROM dba_tables
WHERE table_name=TNAME;
SELECT chain_cnt AS CHAINED_BLOCKS FROM dba_tables
WHERE table_name=TNAME;
SELECT COUNT(*) AS EXTENT_COUNT FROM dba_extents
WHERE segment_name=TNAME;
Information about all rollback segments in the database SELECT SEGMENT_NAME,TABLESPACE_NAME,OWNER,STATUS FROM DBA_ROLLBACK_SEGS;
/* General Rollback Segment Information */
SELECT t1.name, t2.extents, t2.rssize, t2.optsize, t2.hwmsize, t2.xacts, t2.status FROM v$rollname t1, v$rollstat t2
WHERE t2.usn = t1.usn ;
/* Rollback Segment Information - Active Sessions */
select t2.username, t1.xidusn, t1.ubafil, t1.ubablk, t2.used_ublk from v$session t2, v$transaction t1
where t2.saddr = t1.ses_addr
Statistics of the rollback segments currently used by instance SELECT T1.NAME, T2.EXTENTS, T2.RSSIZE, T2.OPTSIZE, T2.HWMSIZE, T2.XACTS, T2.STATUS
FROM V$ROLLNAME T1, V$ROLLSTAT T2 WHERE T1.USN = T2.USN AND
