PLSQL and Chapter 10 Selected SingleRow Functions Jason

PL/SQL and Chapter 10 Selected Single-Row Functions Jason C. H. Chen, Ph. D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@gonzaga. edu Dr. Chen, Oracle Database System (Oracle) 1

Objectives • Use the UPPER, LOWER, and INITCAP functions to change the case of field values and character strings • Manipulate character substrings with the SUBSTR and INSTR functions • Nest functions inside other functions • Determine the length of a character string using the LENGTH function • Use the LPAD and RPAD functions to pad a string to a certain width • Use the LTRIM and RTRIM functions to remove specific characters strings • Substitute character string values with the REPLACE and TRANSLATE functions • Round and truncate numeric data using the ROUND and TRUNC functions • Return the remainder only of a division operation using the MOD function • Use the ABS function to set numeric values as positive Dr. Chen, Oracle Database System (Oracle) 2

Objectives (continued) • Use the POWER function to raise a number to a specified power • Calculate the number of months between two dates using the MONTHS_BETWEEN function • Manipulate data using the ADD_MONTHS, NEXT_DAY, LAST_DAY, and TO_DATE functions • Differentiate between CURRENT_DATE and SYSDATE values • Extend pattern matching capabilities with regular expressions • Identify and correct problems associated with calculations involving NULL values using the NVL function • Display dates and numbers in a specific format with the TO_CHAR function • Perform condition processing similar to an IF statement with the DECODE function • Use the SOUNDEX function to identify character phonetics • Convert string values to numeric with the TO_NUMBER function • Use the DUAL table to test functions • Learn PL/SQL – Procedure Language / SQL Dr. Chen, Oracle Database System (Oracle) 3

Refresh the Database • 1. Run the following script file – Start c: oradatachapter 10JLDB_Build_10. sql • 2. Download data file, Ch 10 queries. sql Dr. Chen, Oracle Database System (Oracle) 4

Functions Must Study and Understand The following functions are the “minimum” to study and understand: • 1. Case conversion functions • 2. character manipulation functions – SUBSTR, INSTR, LENGTH, LTRIM, REPLACE, CONCAT • 3. ALL Number functions • 4. DATE functions – MONTH_BETWEEN, ADD_MONTHS, TO_DATE, SYSDATE • 5. Other functions – NVL, TO_CHAR, CASE, TO_NUMBER, DUAL table Dr. Chen, Oracle Database System (Oracle) 5

Refresh the Database • 1. Go to Blackboard and download (at least three) data files from Oracle chapter 10 and save under c: oradatachapter 10 – Please note that there is a PL/SQL data file of “Ch 10 Queries for PL SQL. sql” • 2. Run the following script file – Start c: oradatachapter 10JLDB_Build_10. sql Dr. Chen, Oracle Database System (Oracle) 6

Manipulating Character Strings with PL/SQL • To change case, use UPPER, LOWER, INITCAP • INSTR function searches a string for a specific substring and return the starting position start_position : = INSTR(original_string, substring, starting_pos, occurrence#); – /* the first two arguments are required, the last two arguments are optional */ • SUBSTR function extracts a specific number of characters from a character string, starting at a given point: extracted_string : = SUBSTR(string_variable, starting_point, number_of_characters); Dr. Chen, Oracle Database System (Oracle) 7

Case Conversion Functions • Case conversion functions alter the case of data stored in a column or character string – Used in a SELECT clause, they alter the appearance of the data in the results – Used in a WHERE clause, they alter the value for comparison Dr. Chen, Oracle Database System (Oracle) 8

LOWER Function • Used to convert characters to lowercase letters Figure 10 -1 LOWER function in the WHERE clause Dr. Chen, Oracle Database System (Oracle) 9

LOWER Function (cont. ) • Used to convert characters to mixed case Figure 10 -2 LOWER function in the SELECT and WHERE clauses Dr. Chen, Oracle Database System (Oracle) 10

UPPER Function • Used to convert characters to uppercase letters • It can be used in the same way as the LOWER function – To affect the display of characters, it is used in a SELECT clause – To modify the case of characters for a search condition, it is used in a WHERE clause • The syntax for the UPPER function is UPPER(c) – Where c is the character string or field to be converted into uppercase characters Dr. Chen, Oracle Database System (Oracle) 11

UPPER Function – manage user input -- chapter 10, Figure 10 -3; p. 335 SELECT firstname, lastname FROM customers WHERE lastname = UPPER('&Custval'); Dr. Chen, Oracle Database System (Oracle) Figure 10 -3 Using UPPER function to manage user input 12

INITCAP Function • Used to convert character strings to mixed case, which each word beginning with a capital letter. Figure 10 -4 INITCAP function in a SELECT clause modifies the display Dr. Chen, Oracle Database System (Oracle) 13

Character Manipulation Functions • Character manipulation functions manipulate data by extracting substrings, counting the number of characters, replacing strings, etc. Dr. Chen, Oracle Database System (Oracle) 14

SUBSTR Function • Used to return a substring, or portion of a string SUBSTR (c, p, l) Where c: the character string p: the beginning character position, l: the length of the string to return in the query result. -p: backward “p” position Figure 10 -6 Comparison of SUBSTR arguments -1 -3 -2 Dr. Chen, Oracle Database System (Oracle) 15

INSTR Function start_position : = INSTR(original_string, substring, starting_pos, occurrence#); /* the first two arguments are required, the last two arguments are optional */ 10 14 9 5 9 Dr. Chen, Oracle Database System (Oracle) 10 Why? Figure 10 -7 Comparison of INSTR arguments 16

What is PL/SQL? § Procedural programming Language § Uses detailed instructions § Processes statements sequentially § Combines SQL commands with procedural instructions § Used to perform sequential processing using an Oracle database Dr. Chen, Oracle Database System (Oracle) 17

Why PL/SQL? ORACLE (programming language components) SQL PL/SQL D. B. GUI Components (reports, forms, graphics etc. ) Dr. Chen, Oracle Database System (Oracle) 18

Using SQL Commands in PL/SQL Programs Category Purpose Date Definition Create/change Language (DDL) the database structure Examples of Commands Can Be Used in PL/SQL CREATE, ALTER, DROP, GRANT, REVOKE _____ Data Manipulation Language (DML) Query or change SELECT, INSERT, UPDATE, DELETE the data in the database tables Transaction control commands Organize DML commands into logical transactions Dr. Chen, Oracle Database System (Oracle) COMMIT, ROLLBACK, SAVEPOINT ______ 19

Fundamentals of PL/SQL • Full-featured programming language • Execute using Oracle 12 c utilities – SQL*Plus – Forms Builder • • An interpreted language Semicolon ends each command Reserved words Type in editor, execute in SQL*Plus Dr. Chen, Oracle Database System (Oracle) 20

Table: PL/SQL command capitalization styles Item Type Capitalization Example Reserved Words Uppercase BEGIN, DECLARE Built-in functions Uppercase COUNT, TO_DATE Predefined data types Uppercase VARCHAR 2, NUMBER SQL commands Uppercase SELECT, INSERT Database objects Lowercase student, fid Variable names Lowercase s_first_name, faculty_id Mixed case Student_F_Name, Faculty_ID Dr. Chen, Oracle Database System (Oracle) 21

Identifiers and Variables: Rules for Names and Properties • • • From 1 to 30 characters Only alphanumeric characters, and special characters ($ _ #) Must begin with a letter and can not contain blank spaces or hyphens And sure they cannot be reserved words (e. g. , BEGIN) Dr. Chen, Oracle Database System (Oracle) 22

Variables and Data Types • Variables – Used to store numbers, character strings, dates, and other data values – Avoid using keywords, table names and column names as variable names – Must be declared with data type before use: vvariable_name data_type_declaration; ve. g. , current_s_id NUMBER(6); Dr. Chen, Oracle Database System (Oracle) 23

Language Elements: PL/SQL Data Types • Scalar (we will focus on this type) – References a single value • Composite – References a data structure • Reference – References a specific database item (e. g. , %TYPE, %ROWTYPE) • LOB – References a large binary object (e. g. , graphs, video) Dr. Chen, Oracle Database System (Oracle) 24

Scalar Data Types • Represent a single value – Database scalar data types: Dr. Chen, Oracle Database System (Oracle) 25

Scalar Data Types (cont. ) • Represent a single value – Non-database scalar data types: Dr. Chen, Oracle Database System (Oracle) 26

Tables: PL/SQL data types Data Type Usage Sample Declaration VARCHAR 2 Variable-length character strings student_name VARCHAR 2(30); CHAR Fixed-length character strings student_gender CHAR(1); NUMBER Floating, fixed-point, or integer number current_price NUMBER(5, 2); LONG Text, up to 32, 760 bytes evaluation_summary LONG; DATE Dates todays_date DATE; BOOLEAN True/False values order_flag BOOLEAN; %TYPE Assumes the data type of a database field c_address customer_address% TYPE %ROWTYPE cust_order_record Assumes the data type of a database record (entire row) cust_order% ROWTYPE; Dr. Chen, Oracle Database System (Oracle) 27

Block Structure PL/SQL is a “block structured” language. That just means everything you do is done in terms of blocks of code. All PL/SQL blocks share the same basic structure. Comments: Not executed by interpreter Enclosed between /* and */ On one line beginning with -Dr. Chen, Oracle Database System (Oracle) -- Figure 4 -1; p. 195 ____ /”HEADER” <variable declarations>; ____ <body executable code>; EXCEPTION <exception/error handling code>; _______; 28

Comment Statements There are two types of comments in PL/SQL: Single-line and multi-line. Single Line: x : = 4; -- assign 4 to the variable x Multi-line: /* Assign 4 to the variable x */ x : = 4; Dr. Chen, Oracle Database System (Oracle) 29

Assignment Statements • Assigns a value to a variable_name : = value; • Value can be a literal: current_s_first_name : = 'John'; • Value can be another variable: current_s_first_name : = s_first_name; Dr. Chen, Oracle Database System (Oracle) 30

Executing a PL/SQL Program in SQL*Plus • Create program in text editor (using Notepad) • Paste into SQL*Plus window -- PL/SQL #1: *** with syntax error *** DECLARE todays_date DATE; • BEGIN todays_date = SYSDATE; DBMS_OUTPUT. PUT_LINE('Today''s date is '); DBMS_OUTPUT. PUT_LINE(todays_date); • END; -- PL/SQL #2 • DECLARE todays_date DATE; BEGIN todays_date : = SYSDATE; DBMS_OUTPUT. PUT_LINE('Today’’s date is '); DBMS_OUTPUT. PUT_LINE(todays_date); END; Dr. Chen, Oracle Database System (Oracle) Press Enter, type to execute / then enter SET SERVEROUTPUT ON SIZE 4000 SET SERVEROUTPUT ON 31

-- PL/SQL #3 a (ok for this version) --PL/SQL program to display the current date DECLARE todays_date DATE; BEGIN todays_date : = SYSDATE; DBMS_OUTPUT. PUT_LINE('Today''s date is ' || (todays_date)); END; --PL/SQL#3 b program to display the current date -- CURRENT_DATE returns the current date and time from the user session (from -- client software and it may be in a different location from the server database (p. 355) DECLARE todays_date DATE; BEGIN todays_date : = CURRENT_DATE; DBMS_OUTPUT. PUT_LINE('Today''s date is ' || TO_CHAR(todays_date)); END; What is the difference between these two outputs? How can we display all “Date” information (eg. , NOVEMBER 17, 12: 25: 58 P. M. 2020)? Dr. Chen, Oracle Database System (Oracle) 32

--PL/SQL#3 C program to display the current date --see Table 10 -4 (p. 366) for more details DECLARE todays_date DATE; BEGIN todays_date : = SYSDATE; DBMS_OUTPUT. PUT_LINE('Today''s date is ' || TO_CHAR(todays_date, 'MONTH DD, HH: MI: SS A. M. YYYY')); END; Today's date is NOVEMBER 17, 09: 25: 58 A. M. 2020 --PL/SQL#3 d program to display the current date DECLARE todays_date DATE; BEGIN todays_date : = SYSDATE; DBMS_OUTPUT. PUT_LINE('Today''s date is ' || TO_CHAR(todays_date, 'Day, MM-DD-YYYY, HH 24: MI: SS')); END; Today's date is Tuesday, 11 -17 -2020, 09: 42: 03 Dr. Chen, Oracle Database System (Oracle) 33

--PL/SQL#4 program to display the current date DECLARE todays_date DATE; current_day VARCHAR 2(9); current_day_length BINARY_INTEGER; BEGIN todays_date : = SYSDATE; -- extract day portion from current date, and trim trailing blank spaces current_day : = TO_CHAR(todays_date, 'DAY'); current_day : = RTRIM(current_day); -- convert day to mixed case letters with initial letter capitalized current_day : = INITCAP(current_day); -- determine length of day's character string current_day_length : = LENGTH(current_day); DBMS_OUTPUT. PUT_LINE('Today''s date is ' || current_day || ', ' || TO_CHAR(todays_date)); DBMS_OUTPUT. PUT_LINE('The length of the word ' || current_day || ' is ' || TO_CHAR(current_day_length) || ' characters. '); END; Dr. Chen, Oracle Database System (Oracle) 34

Data Type Conversion Functions Function TO_DATE Description Converts a character string to a date Example TO_DATE(‘ 07/14/06’, ’MM/DD/YY’); TO_NUMBER Converts a character string to a number TO_NUMBER(‘ 2’); TO_CHAR(2); TO_CHAR(SYSDATE, ‘MONTH’); TO_CHAR(SYSDATE, ‘DAY’); TO_CHAR(SYSDATE, ‘DD’); TO_CHAR(curr_time, ‘HH: MI AM’); Converts either a number or a date to a character string if today is: return ‘October’ Tuesday, return ‘Tuesday’ November 17, return ‘ 17’ 2020 return ‘ 11: 30 AM’ Dr. Chen, Oracle Database System (Oracle) 35

Debugging PL/SQL Programs • Syntax error: – Command does not follow the guidelines of the programming language – Generates compiler or interpreter error messages • Logic error: – Program runs but results in an incorrect result – Caused by mistake in program ______ error Dr. Chen, Oracle Database System (Oracle) 36

-- PL/SQL#5 a Program with a logic error and debugging statements DECLARE Call ID department is: MIS curr_call_id VARCHAR 2(30) : = 'MIS 101'; Original string value: MIS 101 blank_space NUMBER(2); Start position: 4 curr_dept VARCHAR 2(30); Number of characters: 3 Course Number is: 10 curr_number VARCHAR 2(30); BEGIN PL/SQL procedure successfully completed blank_space : = INSTR(curr_call_id, ' '); curr_dept : = SUBSTR(curr_call_id, 1, (blank_space - 1)); DBMS_OUTPUT. PUT_LINE('Call ID department is: ' || curr_dept); DBMS_OUTPUT. PUT_LINE('Original string value: ' || curr_call_id); DBMS_OUTPUT. PUT_LINE('Start position: ' || blank_space); DBMS_OUTPUT. PUT_LINE('Number of characters: ' || (LENGTH(curr_call_id) - blank_space)); curr_number : = SUBSTR(curr_call_id, blank_space, (LENGTH(curr_call_id) - blank_space)); DBMS_OUTPUT. PUT_LINE('Course Number is: ' || curr_number); END; Dr. Chen, Oracle Database System (Oracle) 37

-- PL/SQL#5 b Program with a correct logic Call ID department is: MIS DECLARE curr_call_id VARCHAR 2(30) : = 'MIS 101'; Original string value: MIS 101 Start position: 4 blank_space NUMBER(2); Number of characters: 3 curr_dept VARCHAR 2(30); Course Number is: 101 curr_number VARCHAR 2(30); BEGIN PL/SQL procedure successfully completed blank_space : = INSTR(curr_call_id, ' '); curr_dept : = SUBSTR(curr_call_id, 1, (blank_space - 1)); DBMS_OUTPUT. PUT_LINE('Call ID department is: ' || curr_dept); DBMS_OUTPUT. PUT_LINE('Original string value: ' || curr_call_id); DBMS_OUTPUT. PUT_LINE('Start position: ' || blank_space); DBMS_OUTPUT. PUT_LINE('Number of characters: ' || (LENGTH(curr_call_id) - blank_space)); curr_number : = SUBSTR(curr_call_id, (blank_space+1), (LENGTH(curr_call_id) - blank_space)); DBMS_OUTPUT. PUT_LINE('Course Number is: ' || curr_number); END; Dr. Chen, Oracle Database System (Oracle) 38

Other Functions • NVL 2 Dr. Chen, Oracle Database System (Oracle) 39

NVL and NVL 2 (p. 377) 1. Which function should be used to replace a NULL value with a numeric value for calculation purposes? NVL ANSWER: _______ NVL(x, y): y represents the value to substitute if x is NULL. In many cases, the substitute for a NULL value ina calculation is zero (0) 2. A NULL value can be replaced by a text message, while displaying a different message if there is not a NULL value, using which function? ANSWER: ______ NVL 2 Dr. Chen, Oracle Database System (Oracle) 40

-- chapter 10, Figure 10 -33; p. 378 SELECT title, retail, discount, retail-discount "Sales price“ FROM books; TITLE RETAIL DISCOUNT Sales price --------------- -----BODYBUILD IN 10 MINUTES A DAY 30. 95 REVENGE OF MICKEY 22 BUILDING A CAR WITH TOOTHPICKS 59. 95 3 56. 95 DATABASE IMPLEMENTATION 55. 95 COOKING WITH MUSHROOMS 19. 95 HOLY GRAIL OF ORACLE 75. 95 3. 8 72. 15 HANDCRANKED COMPUTERS 25 E-BUSINESS THE EASY WAY 54. 5 PAINLESS CHILD-REARING 89. 95 4. 5 85. 45 THE WOK WAY TO COOK 28. 75 BIG BEAR AND LITTLE DOVE 8. 95 HOW TO GET FASTER PIZZA 29. 95 1. 5 28. 45 HOW TO MANAGE THE MANAGER 31. 95 SHORTEST POEMS 39. 95 14 rows selected. Dr. Chen, Oracle Database System (Oracle) 41

-- chapter 10, Figure 10 -34; p. 361 SELECT title, retail, discount, retail-NVL(discount, 0) "Sales price“ FROM books; TITLE RETAIL DISCOUNT Sales price --------------- -----BODYBUILD IN 10 MINUTES A DAY 30. 95 REVENGE OF MICKEY 22 22 BUILDING A CAR WITH TOOTHPICKS 59. 95 3 56. 95 DATABASE IMPLEMENTATION 55. 95 COOKING WITH MUSHROOMS 19. 95 HOLY GRAIL OF ORACLE 75. 95 3. 8 72. 15 HANDCRANKED COMPUTERS 25 25 E-BUSINESS THE EASY WAY 54. 5 PAINLESS CHILD-REARING 89. 95 4. 5 85. 45 THE WOK WAY TO COOK 28. 75 BIG BEAR AND LITTLE DOVE 8. 95 HOW TO GET FASTER PIZZA 29. 95 1. 5 28. 45 HOW TO MANAGE THE MANAGER 31. 95 SHORTEST POEMS 39. 95 14 rows selected. What is the difference between these two versions? Dr. Chen, Oracle Database System (Oracle) 42

-- chapter 10, Figure 10 -35; p. 380 (version b) SELECT order#, orderdate, shipdate FROM orders WHERE orderdate >= '03 -APR-09'; ORDER# -----1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 ORDERDATE ----03 -APR-09 03 -APR-09 04 -APR-09 05 -APR-09 12 rows selected. Dr. Chen, Oracle Database System (Oracle) SHIPDATE ----05 -APR-09 04 -APR-09 05 -APR-09 NULL 05 -APR-09 43

-- chapter 10, Figure 10 -35; p. 380 (version c) SELECT order#, orderdate, shipdate, (shipdate - orderdate) "Ship Days" FROM orders WHERE orderdate >= '03 -APR-09'; ORDER# -----1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 ORDERDATE ----03 -APR-09 03 -APR-09 04 -APR-09 05 -APR-09 12 rows selected. Dr. Chen, Oracle Database System (Oracle) SHIPDATE Ship Days ---------05 -APR-09 2 04 -APR-09 1 05 -APR-09 2 04 -APR-09 05 -APR-09 1 1 05 -APR-09 1 44

-- chapter 10, Figure 10 -35; p. 380 SELECT order#, orderdate, shipdate, NVL(shipdate, '06 -APR-09') - orderdate "Ship Days" FROM orders WHERE orderdate >= '03 -APR-09'; ORDER# -----1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 ORDERDATE ----03 -APR-09 03 -APR-09 04 -APR-09 05 -APR-09 Substitutes a value for a NULL value SHIPDATE Ship Days ---------05 -APR-09 2 04 -APR-09 1 05 -APR-09 2 3 NULL 04 -APR-09 1 05 -APR-09 1 If shipdate is 2 2 NULL, 05 -APR-09 1 substitutes with 1 ’ 06 -APR-09’ 1 1 12 rows selected. Dr. Chen, Oracle Database System (Oracle) 45

NVL Function • Substitutes a value for a NULL value Figure 10 -35 Dr. Chen, Oracle Database System (Oracle) Date calculations involving a NULL value 46

-- chapter 10, Figure 10 -35; p. 380 (version b) SELECT order#, orderdate, shipdate FROM orders WHERE orderdate >= '03 -APR-09'; ORDER# -----1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 ORDERDATE ----03 -APR-09 03 -APR-09 04 -APR-09 05 -APR-09 12 rows selected. Dr. Chen, Oracle Database System (Oracle) SHIPDATE ----05 -APR-09 04 -APR-09 05 -APR-09 NULL 05 -APR-09 47

-- chapter 10, Figure 10 -36; p. 381 SELECT order#, orderdate, NVL 2(shipdate, 'Shipped', 'Not Shipped') "Status" FROM orders WHERE orderdate >= '03 -APR-09'; IF NULL ORDER# -----1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 ORDERDATE ----03 -APR-09 03 -APR-09 04 -APR-09 05 -APR-09 Dr. Chen, Oracle Database System (Oracle) 12 rows selected. Status -----Shipped Shipped Not Shipped Not Shipped IF NOT NULL 48

NVL 2 Function • Allows different actions based on whether a value is NULL Figure 10 -36 Dr. Chen, Oracle Database System (Oracle) Using NVL 2 to substitute values 49

Terminology • Function – predefined block of code that accepts arguments • Single-row function – returns one row of results for each record processed • Multiple-row function – returns one result per group of data processed (covered in the next chapter) Dr. Chen, Oracle Database System (Oracle) 50

Types of Functions Table 10 -1 Functions Covered in This Chapter Dr. Chen, Oracle Database System (Oracle) 51

• Practice all the examples in the text. • A Script file is available on the Bb (file name: Ch 10 Queries. sql) • After completing all examples, do the HW. Dr. Chen, Oracle Database System (Oracle) 52

Homework - Hands-On Assignments Upload the spooled file (*. txt) to the Bb (under “Assignments & Projects”) by the deadline. Read and Practice all examples on Chapters 10 • 1. Run the script files (in the folder oradatachapter 10): JLDB_Build_10. sql and download other data files • 2. Read Oracle assignment and create a script file Oracle_ch 10_Lname_Fname. sql for questions (#3, 4, 10; p. 399) on “Hands-on Assignments”. Use appropriate COLUMN statements to produce readable outputs • 3. Execute and test one problem at a time and make sure they are all running successfully. • 4. When you done, spool the script files (see next slide for spooling instructions) and UPLOAD the file (Oracle_ch 10_Spool_Lname_Fname. txt) to Bb by the midnight before the next class. Dr. Chen, Oracle Database System (Oracle) 53

How to Spool your Script and Output Files After you tested the script file of Oracle_ch 10_Lname_Fname. sql successfully, follow the instructions below to spool both script and output files: Step 0. Run the following script file from SQL*Plus (since you have created JLDB tables) – Start c: oradatachapter 10JLDB_Build_10. sql • 1. type the following on SQL> – Spool c: oradataOracle_ch 10_Spool_Lname_Fname. txt (make sure your name is entered) • 2. open Oracle_ch 10_Lname_Fname. sql that you already tested • 3. copy and paste all the SQL commands (including all comments) to the SQL*PLUS • 4. type Spool Off on the SQL> The output should contain your personal information, all SQL commands and their solution on the. txt file and saved in C: drive (oradata folder) Upload the spooled file (*. txt) to the Bb (under “Assignments & Projects”) by the deadline. Dr. Chen, Oracle Database System (Oracle) 54

Your Turn … • PART II – Study yourself – “Learning to learn and learning to change” Dr. Chen, Oracle Database System (Oracle) 55

Nesting Functions Figure 10 -8 INSTR nested inside SUBSTR Dr. Chen, Oracle Database System (Oracle) 56

LENGTH Function • Used to determine the number of characters in a string Figure 10 -10 Dr. Chen, Oracle Database System (Oracle) Checking data width with the LENGTH function 57

LTRIM and RTRIM Functions • Used to remove a specific string of characters Figure 10 -12 Dr. Chen, Oracle Database System (Oracle) Using the LTRIM function 58

REPLACE Function • Substitutes a string with another specified string Figure 10 -13 Dr. Chen, Oracle Database System (Oracle) Using the REPLACE function 59

TRANSLATE Function Figure 10 -14 Using TRANSLATE to substitute character values Dr. Chen, Oracle Database System (Oracle) 60

CONCAT Function • Used to concatenate two character strings Figure 10 -15 Dr. Chen, Oracle Database System (Oracle) Using the CONCAT function 61

Number Functions • Allow for manipulation of numeric data – ROUND – TRUNC – MOD – ABS Dr. Chen, Oracle Database System (Oracle) 62

ROUND Function • Used to round numeric columns to a stated precision Figure 10 -16 Using the ROUND function to round numbers to various places Dr. Chen, Oracle Database System (Oracle) 63

TRUNC Function • Used to truncate a numeric value to a specific position Figure 10 -17 Using the TRUNC function to truncate numbers at various places Dr. Chen, Oracle Database System (Oracle) 64

MOD Function Figure 10 -18 Dr. Chen, Oracle Database System (Oracle) Using the MOD functions to return the remainder 65

ABS Function Figure 10 -19 Dr. Chen, Oracle Database System (Oracle) The effect of using the ABS function 66

Date Functions • Used to perform date calculations or format date values • Subtract date for number of days difference Figure 10 -20 A calculation with date columns Dr. Chen, Oracle Database System (Oracle) 67

MONTHS_BETWEEN Function • Determines the number of months between two dates Figure 10 -21 Dr. Chen, Oracle Database System (Oracle) Using the MONTHS_BETWEEN function 68

ADD_MONTHS Function • Adds a specified number of months to a date Figure 10 -22 Dr. Chen, Oracle Database System (Oracle) Using the ADD_MONTHS function 69

NEXT_DAY Function • Determines the next occurrence of a specified day of the week after a given date Figure 10 -23 Dr. Chen, Oracle Database System (Oracle) Using the NEXT_DAY function 70

TO_DATE Function • Converts various date formats to the internal format (DD-MON-YY) used by Oracle 11 g Figure 10 -25 Dr. Chen, Oracle Database System (Oracle) Using the TO_DATE function 71

Format Model Elements - Dates Table 10 -2 Dr. Chen, Oracle Database System (Oracle) Format Arguments for Dates 72

ROUND Function Figure 10 -26 Rounding dates by month and year Dr. Chen, Oracle Database System (Oracle) 73

TRUNC Function Figure 10 -27 Using the TRUNC function on date calculation results Dr. Chen, Oracle Database System (Oracle) 74

TO_CHAR Function • Converts dates and numbers to a formatted character string Figure 10 -39 Dr. Chen, Oracle Database System (Oracle) Formatting values for display with TO_CHAR 75

TO_NUMBER Function Figure 10 -43 Using the TO_NUMBER function to convert a string to a numeric datatype Dr. Chen, Oracle Database System (Oracle) 76

DUAL Table • Dummy table • Consists of one column and one row • Can be used for table reference in the FROM clause Dr. Chen, Oracle Database System (Oracle) 77

Using DUAL Figure 10 -44 Dr. Chen, Oracle Database System (Oracle) Practicing functions by using a DUAL table 78

Summary • Single-row functions return a result for each row or record processed • Case conversion functions such as UPPER, LOWER, and INITCAP can be used to alter the case of character strings • Character manipulation functions can be used to extract substrings (portions of a string), identify the position of a substring in a string, replace occurrences of a string with another string, determine the length of a character string, and trim spaces or characters from strings • Nesting one function within another allows multiple operations to be performed on data Dr. Chen, Oracle Database System (Oracle) 79

Summary (continued) • Simple number functions such as ROUND and TRUNC can round or truncate a number on both the left and right side of a decimal • The MOD function is used to return the remainder of a division operation • Date functions can be used to perform calculations with dates or to change the format of dates entered by a user • Regular expressions enable complex pattern matching operations • The NVL, NVL 2, and NULLIF functions are used to address problems encountered with NULL values Dr. Chen, Oracle Database System (Oracle) 80

Summary (continued) • The TO_CHAR function lets a user present numeric data and dates in a specific format • The DECODE function allows an action to be taken to be determined by a specific value • The searched CASE expression enables you to evaluate conditions to determine the resulting value • The SOUNDEX function looks for records based on the phonetic representation of characters • The DUAL table can be helpful when testing functions Dr. Chen, Oracle Database System (Oracle) 81