Temel Oracle Fonksiyonları

Oracle'da en çok kullanacağınız temel oracle fonksiyonlarını aşağıda açıklama ve örnekleri ile bulabilrsiniz. Umarım işinize yarar;

PLSQL

Numeric Functions

ROUND(N , x) : Return the number N rounded to x decimal places. If x <0 it rounds N to x places to the left. Example: ROUND(1234.468,2)  returns 1234.5      ROUND(1234.468,-2)  returns 1200 Example: TRUNC(123.456,2) returns 123.45                 TRUNC(123.456,-2) returns 100 Example:  SIGN(-234) returns -1       SIGN(452) returns 1 Example: ABS(-541) returns 541. Example: MOD(10,4) returns 2. MOD(5,2) returns 1 Example: CEIL(123.456) returns 124. CEIL(-123.456) returns -123 Example: FLOOR (123.456) returns 123. FLOOR (-123.456) returns -124 TRUNC(N , x): Return the number with the specified x decimal places. If x<0 it zeroed the number at X positions to the left. SIGN (N): Return 1 if N is positive, 0 if N is 0 and -1 if N is negative. ABS(N): Returns the absolute value of number N. It basically removes the sign from the number and return the positive number. GREATEST(V1,V2,V3,…Vn): Return the largest of the n values in the list. Not to be confuse with MAX witch return the largest values of a set of rows LEAST(V1,V2,V3,…Vn): Return the smallest of the n values in the list. Not to be confuse with MIN witch return the smallest values of a set of rows MOD (N, x): Return the remainder of N divided by x. CEIL(N): Returns the smallest integer greater than or equal to N. FLOOR(N): returns largest integer that is less than or equal to N.

PLSQL	String Functions
SUBSTR(S,x,n): Return a portion of string S starting at position x of n characters. If x=0 it will be consider 1 as Oracle consider the first character to be at position 1. if x <0 the count will begin at the end of the string S. Example: SUBSTR(‘abcdefg’,3,3) will return ‘cde’. SUBSTR(‘abcdefg’,-3,3) will return ‘efg’ LOWER(S): Returns the string S converted to all lowercase characters. Example: LOWER(‘AbcDefg’) returns ‘abcdefg’ UPPER(S): Returns the string S converted to all uppercase characters. Example: UPPER(‘AbcDefg’) returns ‘ABCDEFG’ CONCAT(S1,S2): Returns a concatenated string value of S1, S2. could be replace by S1||S2. Example: CONCAT(‘Abc’,’DeF’) returns ‘AbcDeF’ INITCAP(S): Return the string S converted to the initial capital letters. Example: LENGTH(S): Return the number of characters in string S. Example: LENGTH(‘abcdefg’) returns 7. LPAD(S,N,X):  Returns the string S concatenate to the left by characters X to the total size of N. Example: LPAD(‘MyChar’, 10, ‘O’) returns ‘OOOOMyChar’ RPAD(S,N,X):  Returns the string S concatenate to the rigth by characters X to the total size of N. Example: RPAD(‘MyChar’, 10, ‘O’) returns ‘MyCharOOOO’ LTRIM(S): Returns the string S after cleaning all empty character to the left. Example: LTRIM(‘      MyChar     ’) returns  ‘MyChar     ’ RTRIM(S): Returns the string S after cleaning all empty character to the Rigth. Example: RTRIM(‘      MyChar     ’) returns  ‘      MyChar’ TRIM(S): Returns the string S after cleaning all empty character to the Rigth and Left. Example: TRIM(‘      MyChar     ’) returns  ‘MyChar’ REPLACE(S,S1,S2): Return string S with all occurrences of string S1 replaced by S2. Example: REPLACE(‘abcdefabcdefabcdef’,’de’,’A’) returns ‘abcAfabcAfabcAf’

PLSQL	Date Functions
ADD_MONTHS(D, m): Return the date D added with the specified number of months. If m<0 Oracle subtract the number of month by m. Example: ADD_MONTHS(‘12-JAN-2002’,9) returns ‘12-OCT-2002’.  ADD_MONTHS(‘12-JAN-2002’,-1) returns ‘12-DEC-2001’. LAST_DAY(D): Return the last day of the month for the date D. Example: LAST_DAY(‘12-DEC-2001’) returns ‘31-DEC-2001’ MONTHS_BETWEEN(D1,D2): Return the fractional difference in months between date D1 and date D2, if D1<D2 the difference is negative. This function takes in account days and hours. NEXT_DAY(D,DayName): Return the date of the very next day of DayName specified, after the date D. Example: NEXT_DAY(‘12-DEC-2005’,’SUNDAY’) returns ‘18-DEC-2005’ TRUNC(D): Return date D without its time component. ROUND (D, format): Return the date D rounded to the unit specified by the format, if no format if specified the date D is return rounded to the nearest day. Example: ROUND(‘12-DEC-2004’,’YEAR‘) returns ‘1-JAN-2005’ SYSDATE: Return the database server’s current datetime.

PLSQL	Grouping Functions
MIN(E): Return the smallest value of expression E from a set of rows. MAX(E): Return the largest  value of expression E from a set of rows. SUM(E): Return the summed value of expression E from a set of rows. AVG(E): Returns the average value of expression E from a set of rows. COUNT(E): Return the number of row from the set with the value of E not null. If E=* the function will simply return the number of rows in the set.

PLSQL	Conversion Functions
TO_DATE(S,format): Return a Date from the conversion of string S, in the format specify, to date time. The standard of date time format in Oracle is ‘DD-MON-YYYY’ TO_CHAR(N,format): Return a string from the conversion of number N, in the format specify, to string. TO_CHAR(D,format): Return a string from the conversion of date D, in the format specify, to string. TO_NUMBER(S, format): Return a number from the conversion of string S, in the format specify, to number.

Kaynak: http://www.samplecodepool.com/Forms/Oracle/BasicFunctionsOra.aspx