Procedures in Oracle

ORACLE PROCEDURE

A procedure is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database.it can be executed as a unit to solve a specific problem or perform a set of related tasks. Procedures permit the caller to provide parameters that can be input only, output only, or input and output values.

Procedures and functions in oracle are identical except that functions always return a single value to the caller but procedures can return many values

The syntax for a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

Eg :

create or replace procedure acc_bal

(

loancd in varchar2,

accno in number,

bal out number

)

is

ln_bal number;

tr_bal number;

begin

select nvl(sum(decode(type,'C',-1*amount,amount)),0) into ln_bal from loan_tran_dtl where loan_no=loancd and account_no=accno;

select nvl(sum(decode(type,'C',-1*amount,amount)),0) into tr_bal from tran_detail where ref_id=loancd and

account_no=accno;

bal:=ln_bal+tr_bal;

end;

Oracle Functions

ORACLE FUNCTIONS

·         SIGN(n)  :     positive = 1, zero = 0, negative = -1 

·         SIN(n)    :    Sine of n in Radians 

·         SINH(n)  :     Hyperbolic Sine of n in Radians

·         SOUNDEX(char)  : It returns a char value representing the sound of the words 

·         SQRT(n)   :   to find  Square Root of a number

                                  Eg : sqrt(4)=2 

·         STDDEV([DISTINCT] n) :  Standard deviation of n 

·         SUBSTR(char, s[,l]) : A substring of char, starting at character s, length l 

·         SUBSTRB(char, s[,l]) : A substring of char, starting at character s, length l. The same as SUBSTR, except that 's', 'l' and the return value are expressed in bytes, use for double-byte char sets 

·         SUM([DISTINCT] n) :  Sum of values of n, ignoring NULLs 

·         SYS_CONTEXT('namespace','attribute_name') :  Examine the package associated with the context namespace .

              Possible attributes are: NLS_TERRITORY, NLS_CURRENCY, NLS_CALENDAR ,              NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, SESSION_USER, CURRENT_USER, CURRENT SCHEMAID,SESSION_USERID, CURRENT_USERID 

·         SYS_CONTEXT ('USERENV','IP_ADDRESS') 

·         SYS_GUID()  :  Returns a globally unique identifier (16 byte RAW value) 

·         SYSDATE   :    The current system date & time

·         TAN(n)      :  Tangent of n in Radians

·         TANH(n)     :  Hyperbolic tangent of n in Radians

·         TO_CHAR  :     Convert to character String

                               Eg :to_char(to_date(‘1/sep/2009’),’MON’)  =sep 

·         TO_DATE  :     Convert to date value 

·         TO_LOB(long)  : Convert LONG values to CLOB or NCLOB values  or convert LONG RAW values to BLOB values.  Use only as part of an "INSERT INTO ... SELECT..." subquery.  

·         TO_MULTI_BYTE(char) :  Convert single-byte char to multi-byte char. 

·         TO_NUMBER   :  Convert to numeric format

·         TO_SINGLE_BYTE(char) :  Convert multi-byte char to single-byte char.

·         TRANSLATE('char','search_str','replace_str') :Replace every occurrence of search_str with replace_str  unlike REPLACE() if replace_str is NULL the function returns NULL 

·         TRANSLATE (text USING charset) :  Convert text into a specific character set. Use this instead of CONVERT() if either the input or output datatype  is NCHAR or NVARCHAR2. 

·         TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source) : Returns trim_source as a VARCHAR2 with leading/trailing items removed.  trim_char defaults to a space ' ' but may be numeric or char 'A' 

·         TRUNC(i,d)  :  i truncated to d decimal places (d defaults to 0)

·         TRUNC(date,fmt) :  date truncated to nearest fmt

·         UID  :         User id - unique number

·         UPPER(char)  :  Returns Chars in uppercase 

·         USER      :    Returns the current Username 

·         USERENV('option') : Can return any of the options: ENTRYID, SESSIONID, TERMINAL, LANGUAGE, ISDBA, LANG, INSTANCE, CLIENT_INFO 

·         VALUE(correlation_variable) : Return the object instance for a row of an object table as associated with the correlation_variable 

·         VARIANCE([DISTINCT] n) : Variance of n, ignoring NULLs

·         VSIZE(expr)  :  Value Size - returns the number of bytes used by each row of expr.

Oracle Functions

ORACLE FUNCTIONS
·         LAST_DAY(date) : Returns the last day of month in Date
                   Eg : last_day('23/sep/2009')  = 9/30/2009
 ·         LEAST(expression, expression...) :  Returns the smallest in a list of expressions
                   Eg : least (2,4,3)=2
 ·         LENGTH(char)  :  Returns the number of characters in char
                   Eg : length(‘abc’)=3
 ·         LENGTHB(char) :  Returns the number of bytes in char                
·         LIKE wildcard/value :    Wildcards are [% = any chars] [ _ = any one char]
                                                   Where 'x' LIKE 'ajith%' [will find 'Ajithraj']
                                                   Where 'x' LIKE 'ajith_' [will find 'Ajitha']
·         LN(n)      :    Natural Log of n, where n>0
·         LOG(b,n)    :  log of n, base b
            ·         LOWER(char)  :  Returns character string in lowercase
                                              Eg : lower(‘Lima’) = lima
·         LPAD(char, n[,PadChar]) :    Left Pad char with n spaces [or PadChars]
                                  Eg : lpad('abc',5,0) = 00abc
·         LTRIM(char[,set]) : Left Trim char - remove leading spaces [or char set]
·         MAKE_REF(table,key) :  Create a REF to a row of an OBJECT view/table
·         MAX([DISTINCT] expr) :  Maximum value returned by expr
·         MIN([DISTINCT] expr) :   Minimum value returned by expr
·         MOD(x,y)  :    Remainder of x divided by y
·         MONTHS_BETWEEN(end_date, start_date) : Number of months between the 2 dates (integer)
·         NEW_TIME(date, zone1, zone2) :   Convert between GMT and US time zones 
·         NEXT_DAY(date,day_of_week) :   '12-OCT-01','Monday' will return the next Mon after 12 Oct
·         NLS_CHARSET_DECL_LEN (bytecount,charset) :  Returns the declaration width (no of chars) of an NCHAR column
·         NLS_CHARSET_ID(varchars) :  Returns the char set ID given a charset name 
·         NLS_CHARSET_NAME(charset_id) :  Returns the char set name given a charset id
·         NLS_INITCAP(char[,'NLS_SORT = sort_sequence']) :  Returns char in Initial Caps, using an NLS sort_sequence  either the session default or specified directly
·         NLS_LOWER(char[,'NLS_SORT = sort_sequence']) : Returns char in lower case, using an NLS sort_sequence  either the session default or specified directly
·         NLSSORT(char[,'NLS_SORT = sort_sequence']) :  Return the string of bytes used to sort char, using an NLS sort_sequence  either the session default or specified directly 
·         NLS_UPPER(char[,'NLS_SORT = sort_sequence']) :  Returns char in UPPER case, using an NLS sort_sequence  either the session default or specified directly
·         NVL(expression, value_if_null) : If expression in the function is null, returns value_if_null; if expression is not null, returns expression.  
·         PERCENT_RANK : Calculate the percent rank of a value in a group.
·         POWER(m,n)  :  m raised to the nth power
·         RANK   :       Calculate the rank of a value in a group
·         RAWTOHEX(raw) : Convert  a raw to a character value containing its hex equivalent
·         REF(table_alias) : Returns a REF value for an object instance .The table alias (correlation variable) is associated with one row of an object table or an object view in an SQL statement. 
·         REFTOHEX(ref)  : Convert ref (object type) to a char value containing its hex equivalent. 
·         REPLACE(char, search_str[, replace_str]) :  ANSI alternative to decode() Replace every occurrence of search_str  with replace_str, replace_str defaults to null.
·         ROUND(n,d)  :  n rounded to d decimal places (d defaults to 0)
                                              Eg : round(3.5345,2)=3.53
·         ROUND(date,fmt) : date rounded to fmt
·         ROWIDTOCHAR(rowid) :  Convert a rowid value to VARCHAR2
·         ROW_NUMBER   : It assign a unique number to each row of the results.  
·         RPAD(char, n[,PadChar]) :   Right Pad char with n spaces [or PadChars]
·         RTRIM(char[,set]) :  Right Trim char - remove trailing spaces [or char set]

Oracle Functions

Oracle SQL Functions

·         ABS(n)  : It returns absolute value of a number

          Eg:          ABS(-15)=15, ABS(24)=24

·         ACOS(n) : It Returns arc cosine of number(n).’n’ must be in the   range of -1 and 1

                                              Eg:          ACOS(.3)= 1.26610367

·         ADD_MONTHS(date,num_months) : Returns date + num_months

                                  Eg: ADD_MONTHS('01-Aug-03', 3)= '01-Nov-03'

·         ASCII(char) : it Converts char into a decimal ascii code

                                  Eg:          ASCII(3)=51

·         ASCIISTR(char):argument of this function is a string ,or expression that resolves to a string ,in any character set.it returns an ASCII conversions of the string in the database character set.

                                  Eg:          ASCIISTR(ABCDE)= AB\00C4CDE

·         ASIN(n) : returns arc sine of n.

                                  Eg:          ASIN(.3)= 0.30469265

·         ATAN(n) : it returns arc tangent of n.

                                  Eg:          ATAN(.3)= 0.29145679

·         ATAN2(n.m) : it returns arc tangent of n and m. 

                                  Eg:          ATAN2(.3,.2)= 0.9827937232

·         AVG([DISTINCT]n): Averge value of 'n' ignoring NULLs

·         BETWEEN value AND value : Where 'x' between 25 AND 100

·         BFILENAME('directory','filename'): To Get the BFILE locator associated with a physical LOB binary file.

·         CASE : It is used to Group the data into sub-sets. The case statement is an alternative for the decode function. 

Eg: select id

, case (when status ='A' then 'Accepted'

when status ='R' then 'Rejected'

else 'Other') end

from employee_maser;

·         CEIL(n) :       Round n up to next whole number.

                    Eg: ceil(3.4)=4,        Ceil(3.9)=4

·         CHARTOROWID(char) : it Converts a Char into a rowid value.

                              Eg : SELECT ID from some_table 

                                              WHERE ROWID = CHARTOROWID('AAAAtmAAEAAAAFmAAB');     

·         CHR(n) : Character with value n

                                             Eg: chr(51)=3    

·         CONCAT(s1,s2) :  it Concatenate string1 and string2

                                       Eg : concat('dfq','ee')=dfqee

·         CONVERT (char_to_convert, new_char_set, old_char_set) : 

                               Convert a  string from one character set to another.

·         COS(n): Cosine of number

                  Eg : cos(.3)= 0.95533648

·         COSH(n)       Hyperbolic Cosine of number

                  Eg : cosh(.3)= 1.04533851

·         COUNT(*) :     Count the no of rows returned

·         COUNT([DISTINCT] expr) :Count the no of rows returned by expr

·         DECODE :        IF x THEN return y ELSE return z

                                                             Eg : select decode(id,1,’111’,’2’)

                                                    If id=1 then it return ‘111’ else returns ‘2’

·         DENSE_RANK  :  It is used to calculate the rank of a value in a group

·         DEREF(e)   :   Return the object reference of argument e. 

·         DUMP(expr,fmt[,start,length]) :   Convert to dec/hex/oct and display char set

·         EMPTY_BLOB  :   Return an empty LOB locator

·         EMPTY_CLOB  :  Return an empty LOB locator 

·         EXISTS  :     This function returns  TRUE if a subquery returns at least one row

·         EXP(n)      :  Exponential (e to 'n'th power)

·         FLOOR(n)    :   Round n down to the next whole number.  

                                              Eg : Floor(3.6)=4

·         GREATEST(expression, expression...) :  It returns the largest in a list of expressions.

·         GROUPING   :   Grouping for superaggregate rows=NULL

·         HEXTORAW(char) :  Convert char containing hex digits to a raw value.

·         IN (list of comma separated values) : it is used to check whether the parametercontains a required value.       

                                  Eg : emp_code in(10002,35254,43534) –return TRUE if list contains the                   requied emp_code

·         INITCAP(char) :  String with Initial Capitals

                   Eg : Initcap(‘lima’)=Lima

·         INSTR(str, chars[,s[,n]]) :  this function find the 'n'th occurence of 'chars' in 'str'  Starting at position 's'.  n and s default to 1

·         INSTRB (str, chars[,s[,n]]) :  It is same as INSTR, except that 's' and the return value are expressed in bytes,   use for double-byte char sets

·         IS [NOT] NULL  : It is used to check for NULL (empty) values  Where 'x' IS NULL;