Single Row Functions 3 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder3Ć2 Schedule: Timing Topic 55 minutes Lecture 40 minutes Practice 95 minutes Total Class Management Note: Files required for lesson are: Demonstration: l3hire.sql Practice: None Single Row Functions 3Ć3 Objectives Functions make the basic query block more powerful and are used to manipulate data values. This is the first of two lessons that explore functions. You will focus on single row character, number, and date functions, as well as those functions that convert data from one type to another, for example, character data to numeric. At the end of this lesson, you should be able to D Explain the various types of functions available in SQL. D Identify the basic concepts of using functions. D Use a variety of character, number, and date functions in SELECT statements. D Explain the conversion functions and how they might be used. Introduction to Oracle: SQL and PL/SQL Using Procedure Builder3Ć4 Single Row Functions 3Ć5 Overview Functions are a very powerful feature of SQL and can be used to D Perform calculations on data. D Modify individual data items. D Manipulate output for groups of rows. D Alter date formats for display. D Convert column datatypes. There are two distinct types of functions: D Single row functions. D Multiple row functions. Single Row Functions These functions operate on single rows only, and return one result per row. There are different types of single row functions. We will cover those listed below. D Character D Number D Date D Conversion Multiple Row Functions These functions manipulate groups of rows to give one result per group of rows. For more information, see Oracle7 Server SQL Reference, Release 7.3 for the complete list of available functions and syntax. Class Management Note: This lesson does not discuss all functions in great detail. Present the most common functions without a long explanation. Introduction to Oracle: SQL and PL/SQL Using Procedure Builder3Ć6 Single Row Functions 3Ć7 Single Row Functions Single row functions are used to manipulate data items. They accept one or more arguments and return one value for each row returned by the query. An argument may be one of the following: D A user-supplied constant D A variable value D A column name D An expression Features of Single Row Functions D They act on each row returned in the query. D They return one result per row. D They may return a data value of a different type than that referenced. D They may expect one or more user arguments. D You can nest them. D You can use them in SELECT, WHERE, and ORDER BY clauses. Syntax function_name (column|expression, [arg1, arg2, .]) where: function_name is the name of the function. column is any named database column. expression is any character string or calculated expression. arg1, arg2 is any argument to be used by the function. Introduction to Oracle: SQL and PL/SQL Using Procedure Builder3Ć8 Single Row Functions 3Ć9 Character Functions Single row character functions accept character data as input and can return both character and number values. Function Purpose LOWER(column|expression) Converts alpha character values to lowercase. UPPER(column|expression) Converts alpha character values to uppercase. INITCAP(column|expression) Converts alpha character values to uppercase for the first letter of each word, all other letters in lowercase. CONCAT(column1|expression1, column2|expression2) Concatenates the first character value to the second character value. Equivalent to concatenation operator (||). SUBSTR(column|expression,m[,n]) Returns specified characters from character value starting at character position m, n characters long. If m is negative, the count starts from the end of the character value. LENGTH(column|expression) Returns the number of characters in value. NVL(column|expression1,column|ex pression2) Converts the the first value if null to the second value. Note: This list is a subset of the available character functions. For more information, see Oracle7 Server SQL Reference, Release 7.3, “Character Functions.” Class Management Note: The NVL function was covered in Lesson 1 under the topic “Managing Null Values.” Introduction to Oracle: SQL and PL/SQL Using Procedure Builder3Ć10 [...]... last_name s_emp last_name = ’PATEL’; no rows returned SQL> SELECT 2 FROM 3 WHERE first_name, last_name s_emp UPPER(last_name) = ’PATEL’; FIRST_NAME -Vikram Radha LAST_NAME -Patel Patel Note: The name is displayed as it was stored in the database To display the name in uppercase, the UPPER function must be used in the SELECT clause as well Single Row Functions 3Ć13 3Ć14 Introduction to... 1400; LAST_NAME MOD(SALARY,COMMISSION_PCT) -Velasquez Ngao Quick-To-See Ropeburn Giljum 2.5 Sedeghi 5 Nguyen 10 Dumas 15 8 rows selected Class Management Note: The feedback from SQL*Plus is now showing This topic is covered in a later lesson Single Row Functions 3Ć21 Class Management Note: Stress that the default date displays in the format DD-MON-YY Functions are used to display other... accessed by all users It contains one column, DUMMY, and one row with the value “X.” The DUAL table is useful when you want to return a value once only, for instance, the value of a constant, pseudo-column, or expression that is not derived from a table with user data Example Display the current date using the DUAL table SQL> SELECT 2 FROM Single Row Functions SYSDATE SYS.DUAL; 3Ć23 3Ć24 Introduction to... 01-OCT-91 01-FEB-91 01-AUG-91 01-AUG-91 01-JUN-91 01-APR-91 01-MAY-91 TRUNC(STA 01-JUN-91 01-JAN-91 01-FEB-91 01-FEB-91 01-OCT-91 01-FEB-91 01-AUG-91 01-JUL-91 01-MAY-91 01-MAR-91 01-MAY-91 11 rows selected Single Row Functions 3Ć31 3Ć32 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Conversion Functions SQL provides three functions to convert a value from one datatype to another Function... string Single Row Functions 3Ć33 3Ć34 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder TO_CHAR Function with Date Formats Displaying a Date in a Specific Format Previously, all Oracle date values were displayed in the DD-MON-YY format The TO_CHAR function allows you to convert a date from this default format to one specified by you Guidelines D The format model must be enclosed in single. .. 3 4 FROM 5 WHERE LOWER(first_name||’ ’||last_name) VP, INITCAP(userid) USERID, UPPER(title) TITLE s_emp title LIKE ’VP%’; VP ladoris ngao midori nagayama mark quick-to-see audry ropeburn Single Row Functions USERID -Lngao Mnagayam Mquickto Aropebur TITLE VP, OPERATIONS VP, SALES VP, FINANCE VP, ADMINISTRATION 3Ć11 3Ć12 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder... products where the first three characters are Ace SQL> SELECT 2 FROM 3 WHERE name, LENGTH(name) s_product SUBSTR(name,1,3) = ’Ace’; NAME LENGTH(NAME) -Ace Ski Boot 12 Ace Ski Pole 12 Single Row Functions 3Ć15 3Ć16 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Number Functions Number functions accept numeric input and return numeric values This section describes some of... that returns the current date and time Your results may differ from the examples Class Management Note: If an older date is subtracted from a more current date, the difference is a negative number Single Row Functions 3Ć25 3Ć26 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Date Functions Date functions operate on Oracle dates All date functions return a value of DATE datatype except... date functions The format models are covered later in this chapter Examples of format models are month or year For more information, see Oracle7 Server SQL Reference, Release 7.3, “Date Functions.” Single Row Functions 3Ć27 3Ć28 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Date Functions continued Example For all employees employed less than 48 months, display the employee number,... (restock_date,’FRIDAY’), LAST_DAY (restock_date) FROM s_inventory WHERE restock_date IS NOT NULL ORDER BY restock_date; PRODUCT_ID -30433 40422 50532 NEXT_DAY( 11-SEP-92 12-FEB-93 16-APR-93 Single Row Functions LAST_DAY( 30-SEP-92 28-FEB-93 30-APR-93 3Ć29 3Ć30 Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Date Functions continued The ROUND and TRUNC functions can be used . functions. Single Row Functions These functions operate on single rows only, and return one result per row. There are different types of single row functions Oracle: SQL and PL/SQL Using Procedure Builder3Ć6 Single Row Functions 3Ć7 Single Row Functions Single row functions are used to manipulate data items.