1 / 28

Functions

Functions. Oracle Labs 5 & 6. SQL Functions. Input. Function. Output. arg 1. arg 2. Resulting Value. . . . arg n. SQL Functions. Single-Row Functions Multiple-Row Functions . Single-Row Functions. Acts on each row Can accept multiple arguments Column name Variable name

jorryn
Download Presentation

Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Functions Oracle Labs 5 & 6

  2. SQL Functions Input Function Output arg 1 arg 2 Resulting Value . . . arg n Adapted from Introduction to Oracle: SQL and PL/SQL

  3. SQL Functions • Single-Row Functions • Multiple-Row Functions Adapted from Introduction to Oracle: SQL and PL/SQL

  4. Single-Row Functions • Acts on each row • Can accept multiple arguments • Column name • Variable name • Expression • Constant • Returns a single value for each row Adapted from Introduction to Oracle: SQL and PL/SQL

  5. Single-Row Function Use • Select • Where • Order By • Any where a column name can be used Adapted from Introduction to Oracle: SQL and PL/SQL

  6. Single-Row Function Types • Character • Number • Date • Conversion • General Adapted from Introduction to Oracle: SQL and PL/SQL

  7. LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD RPAD TRIM REPLACE Character functions Case conversion functions Character manipulation functions Character Functions Adapted from Introduction to Oracle: SQL and PL/SQL

  8. Character Functions Adapted from Introduction to Oracle: SQL and PL/SQL

  9. Character Functions Oracle Examples • Accepts character Input • Returns a single value • Value can be • Character • Numeric Function_name (column|expression, [arg1, arg2,…]) Adapted from Introduction to Oracle: SQL and PL/SQL

  10. Other Single Row Functions • Number functions • Date functions • Format functions • Conversion functions Adapted from Introduction to Oracle: SQL and PL/SQL

  11. NVL FunctionConverts Null to a Value • Syntax  NVL (expr1, expr2) • expr1 • Source value or expression • that may contain null • expr2 value to replace null Adapted from Introduction to Oracle: SQL and PL/SQL

  12. NVL Function • Datatypes could be • Date • Character • Number • Datatypes must match. • NVL(comm, 0) • NVL(hiredate, ’01-Jan-97’) • NVL(job, ‘No Job Yet’) Adapted from Introduction to Oracle: SQL and PL/SQL

  13. Good Things to Know • Whole number too large • For format model • Replaced by string of pound signs (#) • Decimal value too large • For format model • Replaced by rounded value Adapted from Introduction to Oracle: SQL and PL/SQL

  14. Multiple-Row Functions Or Group Functions

  15. Multiple Rows  Single Result ENAME SAL ---------- ---------- SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 MAX(SAL) ---------- 5000 Adapted from Introduction to Oracle: SQL and PL/SQL

  16. Types of Group Functions • AVG • COUNT • MAX • MIN • STDDEV • SUM • VARIANCE Adapted from Introduction to Oracle: SQL and PL/SQL

  17. SELECT [column,] groupfunction(column)FROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column] Adapted from Introduction to Oracle: SQL and PL/SQL

  18. Functions for Only Numeric Data • AVG • SUM • VARIANCE • STDDEV • MAX & MIN used for any datatype Adapted from Introduction to Oracle: SQL and PL/SQL

  19. Examples Adapted from Introduction to Oracle: SQL and PL/SQL

  20. The Count Function • COUNT(*)  • Number of rows in the table • Including • Duplicate rows • Null Values • Example Adapted from Introduction to Oracle: SQL and PL/SQL

  21. The Count Function • COUNT(expr)  • Number of nonnull rows in • The column • Identified by expr • Example Adapted from Introduction to Oracle: SQL and PL/SQL

  22. Group Functions & Null Values • Null values ignored in calculations • Use NVL function to ‘work-around’ • Examples Adapted from Introduction to Oracle: SQL and PL/SQL

  23. Adapted from Introduction to Oracle: SQL and PL/SQL

  24. DISTINCT Option • Consideration of only non-duplicate values • Default – ALL • Example Adapted from Introduction to Oracle: SQL and PL/SQL

  25. Groups Within Groups • Multiple columns in Group By clause • Top-level group listed first • Example Adapted from Introduction to Oracle: SQL and PL/SQL

  26. ProblemDisplay the deptno and average salary of all departments that have an average salary greater than 2000. Adapted from Introduction to Oracle: SQL and PL/SQL

  27. HAVING Clause • Restricts • groups of rows • Based on group conditions • Like Where clause • Filters • Based on single-row conditions • After GROUP BY • Before ORDER BY Adapted from Introduction to Oracle: SQL and PL/SQL

  28. Having Clause Oracle Server Steps • Group rows. • Apply group function. • Filter • by matching criteria • in HAVING clause EXAMPLE PROBLEM Adapted from Introduction to Oracle: SQL and PL/SQL

More Related