Stored Procedure programming-common functions

How to Use escape characters in Oracle:

SeparCh  char  := chr(1 );

NVL( string1, replace_with): 

Function: If string1 is null, The nvl function returns the value of replace_with; otherwise, the value of string1 is returned. If both parameters are null, null is returned. Note: string1 and replace_with must be of the same data type, unless explicitly converted using the to_char function. For example, nvl (to_char (numeric_column), 'some string') Where numeric_column represents a numerical value.

Basic Type format conversion:

To_char converts a date or number to a string.

To_date converts a string to the date type in the database.
To_number converts a character to a number

To_char (sysdate, 'yyyymmddhh24miss ') -- format the date output in Oracle

Round ()--Return ValueThe value is the result of rounding according to the specified number of decimal places.

Select round (number, [decimal_places]) from dual --Parameter: (number: value to be processed) (decimal_places: rounding, decimal number (default: 0 ))

Sample:
Select round (123.456, 0) from dual; return 123
Select round (123.456, 1) from dual; return 123.5
Select round (123.456, 2) from dual; return 123.46

Partition assignment function: Decode ()

Decode Syntax: Decode (value, if1, then1, if2, then2, if3, then3 ,..., else), indicating that if the value is equal to if1, the result of the decode function returns then1 ,..., if it is not equal to any if value, else... is returned ....

If the salary is less than 8000 yuan, it will add 20%; if the salary is higher than 8000 yuan, it will add 15% as follows: Select decode ( Sign (salary-1, 8000), 1, salary * 1.15,-1, salary * 1.2, salary) from employee function Syntax: Sign (N)
Function Description: returns the number n. If the value is greater than 0, 1 is returned. If the value is less than 0,-1 is returned. If the value is equal to 0, 0 is returned.
Http://www.cnblogs.com/kafony/archive/2011/08/25/2153675.html Lower/upper case-sensitive ConversionAll lower-case lower ('abc') Result ABC
All uppercase upper ('abc') Result ABC

Length:Length ('abc') is equal to 3 String connector "| "(String ''single quotes) Result: = Ltrim (rtrim (to_char (interid) | ';' | ltrim (rtrim (rmchno) | '; '| ltrim (rtrim (rtrmno )); Space-removing functions ltrim/rtrim/trim Copy the substring substr ()App_mode: = substr (tmppara, 1, IPOs-1); Syntax: substr (string, start, length)
Start-required, specifying where the string starts. Positive number-start at the specified position of the string. Negative number: starts at the specified position from the end of the string. 0-Start at the first character in the string; Length-optional, specify the length of the string to be truncated. If the value of the character expression is missing, all characters before the end are returned. Find the sub-string instr ()IPOs: = instr (tmppara, ';', 1); -- where the colon appears, starting with the first character Returns the maximum value greatest () in a numeric value or string () Greatest (2, 5, 12, 3) wowould return 12
Greatest ('2', '5', '12', '3') wo'd return '5'
Greatest ('append', 'oranges', 'banas') wo'd return 'oranges'Max (): records that retrieve the maximum number of values in a column.

Stored Procedure programming-common functions