390 likes | 571 Views
Chapter Nine Data Manipulation Language (DML) Functions. Objectives Single Row functions Character functions Number functions Date functions. Functions. Introduction Types of functions Single row Multiple rows. Single Row Functions:. FACTS: Act on each row
E N D
Chapter NineData Manipulation Language (DML)Functions Objectives Single Row functions Character functions Number functions Date functions
Functions • Introduction • Types of functions • Single row • Multiple rows
Single Row Functions: FACTS: Act on each row Return one result per row May modify the data type Can be nested
Single Row Functions • Character • Number • Date • Conversion • General
Character Manipulation: • LOWER(Col | Exp) LOWER(‘Database course’) • UPPER (Col | Exp) UPPER (‘Database course’) • INITCAP (Col | Exp) INITCAP (‘Database course’)
Character Manipulation • CONCAT (Col1 | Exp1, Col2 | Exp2) CONCAT(‘This ‘,’that’) • SUBSTR(Col | Exp,n[,m]) SUBSTR(‘This is it’,2,5) • LPAD(Col | Exp,n,’string’) LPAD(name,9,’.’)
Character Manipulation • LENGTH(Col | Exp) LENGTH(‘this is it’) • CHR(integer) CHR(97) • INSTR(‘Computer’,’m’)
List(Characters): • Example: • SELECT LOWER(name), UPPER(major), LENGTH(name) FROM student; • SELECT CONCAT(Name , Address) , GPA FROM Student; • SELECT Name || ‘--->‘ || Address , GPA FROM Student;
List(Characters): • SELECTRPAD (Name, 40, ‘.’), GPA FROM Student; JOHN............... 3.1 MARY ............. 3.2 • SELECTRPAD (Name, 20), GPA FROM Student; MARY 3.2 MOHAMMAD 3.3 • SELECTLPAD (Name, 20), GPA FROM Student; MARY 3.2 MOHAMMAD 3.3
List(Characters): • RTRIM(Col) RTRIM (Name) RTRIM (Name, ‘.’) LTRIM (Name, ‘ABC’) LTRIM ( RTRIM( Name, ‘.’ ), ‘A’) From Student;
List(Characters): • SELECT Name FROM Student WHERE LENGTH(Address)<20; • SELECT Name, SUBSTR (SSN, 5 ,2) FROM Student; • SELECT Name, SUBSTR (SSN,5) FROM Student;
List(Characters): • SELECT RPAD (INITCAP(LOWER(Name)),70,’.’), SUBSTR (SSN,5) FROM Student; • SELECT Name FROM Student WHERE SUBSTR (SSN,5,2)=’80’; • SELECT Name, SUBSTR (SSN,-4) FROM Student;
List(Characters): • SELECT Name, INSTR (Name,’R’) FROM Student; ------------------------------------------------ MARY 3 JOHN 0 ROBIN 1 • SELECT Name, INSTR (Name,’R’,1,2) FROM Student; • SELECT Name, INSTR(Address,’FROSTBURG’) FROM Student;
List(Characters): Character Manipulations • REPLACE(string, searchSt [,replace]) REPLACE(address,’21532’, ‘21211’) • TRANSLATE (string, fromSt, toSt) TRANSLATE(‘12345678’, ‘123’, ‘999’) • ASCII(string) ASCII(‘A’)
List(Characters): Character Manipulations • SOUNDEX SELECT name, GPA FROM student WHERE SOUNDEX (name)=SOUNDEX(‘LAVALE’);
Character Manipulation New Functions: REGEXP_SUBSTR REGEXP_INSTR REGEXP_LIKE REGEXP_REPLACE
LIST(Numbers) • ROUND (value, precision) ROUND(234.1161,2) • TRUNC(value, precision) TRUNC(234.1161,2) • POWER(value,exponent) POWER(3,2) • MOD(value1, value2) MOD(900,400)
LIST(Numbers) • SELECT ROUND(Salary,1) FROM Faculty; • SELECT TRUNC(234.111,2), FROM DUAL; TRUNC(234.567); TRUNC(234.5678,-2);
DATE: • Date is stored in an internal numeric format: century, year, month, day, hours, minutes, second • Default date is ‘DD-MON-YY’ • SYSDATE • CURRENT_DATE • STSTIMESTAMP 25-OCT-04 04.15.31.652000 PM
DATE: • Example: List the ages of students • SELECT name, SYSDATE - B_Date FROM student;
Date • Date + number • Date – number • Date – date • Date + number/24
DATE: • MONTHS_BETWEEN(day1,day2) • SELECT name, MONTHS_BETWEEN(SYSDATE , B_Date) age_in_month FROM Student;
DATE: • ADD_MONTHS (date,n) SELECT name, ADD_MONTHS(B_Date,5) age FROM Student; SELECT name, ADD_MONTHS(B_Date,-15) age FROM Student;
DATE: • ROUND(date [,fmt]) • SELECT name, ROUND (B_Date,’MONTH’) FROM Student; • SELECT name, ROUND(B_Date,’YEAR’) FROM Student;
DATE: • ROUND(date [,fmt]) • SELECT ’25-OCT-04’ - ROUND (SYSDATE) FROM DOAL; • SELECT name, ROUND(B_Date,’YEAR’) FROM Student;
DATE: • NEXT_DAY • SELECT cycledate FROM paydate; • SELECT NEXT_DAY(cycledate, ‘FRIDAY’) FROM paydate;
DATE: LAST_DAY (date) SELECT LAST_DAY (SYSDATE) “Last”, SYSDATE, LAST_DAY (SYSDATE) – SYSDATE “Left” FROM DUAL; Last SYSDATE Left -------------------------------------------------------- 31-MAY-05 28-MAY-05 4
Conversion Function: Implicit conversion (Automatic): • CHAR or VARCHAR2 to NUMBER • CHAR or VARCHAR2 to DATE • NUMBER to VARCHAR2 • DATE to VARCHAR2
Conversion Function: Explicit datatype conversion: • TO_CHAR (NUMBER [,‘fmt’] ) • TO_CHAR (DATE [,‘fmt’] ) • TO_DATE (CHAR [,‘fmt’] ) • TO_NUMBER (CHAR [,‘fmt’] )
Conversion Function: SELECT TO_CHAR(b_date,’MM/YY’) FROM student; Format: • YYYY • YYY • YY • RR • YEAR • MM • MONTH • DD • DY • DAY • HH HH12 HH24 • MI • SS
Conversion Function: EXTRACT (YEAR FROM B_Date) YEAR MONTH DAY HOUR MINUTE SECOND SELECT EXTRACT (YEAR FROM DATE ‘1999-01-08’) FROM DUAL; --1999 SELECT EXTRACT (YEAR FROM TO_DATE (B_Date, ‘DD-MON-YY’) FROM Student;
Conversion Function: • SELECT SUBSTR(TO_CHAR(111223333),1,3) ||‘-’ || SUBSTR (TO_CHAR(111223333),4,2) || ‘-’ || SUBSTR(TO_CHAR(111223333),6) FROM Student;
Conversion Function: • SELECT SUBSTR(ssn,1,3) || ‘-’ || SUBSTR(ssn,4,2) || ‘-’ || SUBSTR(ssn,6) FROM Student;
CAST • CAST (expression) AS (Type); CHAR NUMBER DATE ROW ROWID VARCHAR2 TIMESTAMP 1 X X X X X 2 X X 3 X X 4 X X 5 X X CAST (B_Date AS CHAR);
NULLIF NULLIF (Exp1, Exp2) IF Exp1 = Exp2 THEN RETURN NULL ELSE RETURN Exp1 SELECT Name, NULLIF (a.major, b.major) “oldMajor” FROM Student a, StudentHistory b WHERE a.ID = b.ID;
NVL NVL (Exp1,Exp2) SELECT Name, NVL (TO_CHAR (Commission_pct, ‘Not Applicable’)) commission FROM Sales;
NVL2 NVL2 (Exp1, Exp2, Exp3) SELECT Name, NVL2 (Commission_pct, Salary + Salary * Commission_pct, Salary) FROM Employee;
COALESCE • COALESCE (exp_List) Return the first non_null expression in the expression list: COALESCE (Email, Address, Phone_#, Phone_W) SELECT Product_ID, Product_Name, List_Price, Min_Price, COALESCE (List_Price, Min_Price, 10) ”Sale Price” FROM Product;
Use of DECODE: • DECODE: DECODE (col/exp, compare1, result1 [,compare2, result2, …] [,default] ) SELECT name, salary, DECODE (Dept, ‘COSC’, salary*2.2, ‘MATH’, salary*1.2, ‘ART’, salary*0.2, salary) FROM Faculty;