1 / 68

PL/SQL and Chapter 10 Selected Single-Row Functions

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@jepson.gonzaga.edu. Objectives.

candy
Download Presentation

PL/SQL and Chapter 10 Selected Single-Row 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. PL/SQL andChapter 10Selected Single-Row Functions Jason C. H. Chen, Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu

  2. 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

  3. 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 date 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

  4. Refresh the Database • 1. Run the following script file • Start c:\oradata\chapter10\JLDB_Build_10.sql • 2. Download data file, Ch10queries.sql

  5. 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, RTRIM, 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

  6. Refresh the Database • 1. Go to Blackboard and download (at least three) data files from Oracle chapter10 and save under c:\oradata\chapter10\ • Please note that there is a PL/SQL data file of “Ch10 Queries for PL SQL.sql” • 2. Run the following script file • Start c:\oradata\chapter10\JLDB_Build_10.sql

  7. 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);

  8. 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

  9. LOWER Function • Used to convert characters to lowercase letters Figure 10-1 LOWER function in the WHERE clause

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

  11. 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

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

  13. 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

  14. Character Manipulation Functions • Character manipulation functions manipulate data by extracting substrings, counting the number of characters, replacing strings, etc.

  15. SUBSTR Function 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 • Used to return a substring, or portion of a string Figure 10-6 Comparison of SUBSTR arguments -1 -3 -2

  16. 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 Figure 10-7 Comparison of INSTR arguments Why? 10

  17. 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

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

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

  20. Fundamentals of PL/SQL • Full-featured programming language • Execute using Oracle 11g utilities • SQL*Plus • Forms Builder • An interpreted language • Semicolon ends each command • Reserved words • Type in editor, execute in SQL*Plus

  21. 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 VARCHAR2, NUMBER SQL commands Uppercase SELECT, INSERT Database objects Lowercase student, fid Variable names s_first_name, faculty_id Lowercase Student_F_Name, Faculty_ID Mixed case

  22. 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)

  23. 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: • variable_name data_type_declaration; • e.g., current_s_id NUMBER(6);

  24. Language Elements:PL/SQL Data Types • Scalar • 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) (we will focus on this type)

  25. Scalar Data Types • Represent a single value • Database scalar data types:

  26. Scalar Data Types (cont.) • Represent a single value • Non-database scalar data types:

  27. Tables: PL/SQL data types Data Type Usage Sample Declaration VARCHAR2 Variable-length character strings student_name VARCHAR2(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.customer_address%; TYPE %ROWTYPE Assumes the data type of a database record (row) cust_order_record cust_order% ROWTYPE;

  28. 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. -- Figure 4-1; p.195 DECLARE /”HEADER” <variable declarations>; BEGIN <body executable code>; EXCEPTION <exception/error handling code>; END; Comments: Not executed by interpreter Enclosed between /* and */ On one line beginning with --

  29. 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;

  30. Assignment Statements • Assigns a value to a variable • 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;

  31. Executing a PL/SQL Program in SQL*Plus -- 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; • Create program in text editor (using Notepad) • Paste into SQL*Plus window • Press Enter, type/ then enter to execute • SET SERVEROUTPUT ON SIZE 4000 • SET SERVEROUTPUT ON

  32. -- PL/SQL #3a (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#3b 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?

  33. Data Type Conversion Functions Function Description Example TO_DATE Converts a character string TO_DATE(‘07/14/01’,’MM/DD/YY’); to a date TO_NUMBER Converts a character string TO_NUMBER(‘2’); to a number TO_CHAR Converts either a number or TO_CHAR(2); a date to a character string TO_CHAR(SYSDATE); if today is: return ‘October’ TO_CHAR(SYSDATE, ‘MONTH’); Tuesday, return ‘Tuesday’ TO_CHAR(SYSDATE, ‘DAY’); October 15, return ‘15’ TO_CHAR(SYSDATE, ‘DD’); 2013 return ‘11:30 AM’ TO_CHAR(curr_time, ‘HH:MI AM’);

  34. 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  RUN-TIME error

  35. -- PL/SQL#5a Program with a logic error and debugging statements DECLARE curr_call_id VARCHAR2(30) := 'MIS 101'; blank_space NUMBER(2); curr_dept VARCHAR2(30); curr_number VARCHAR2(30); BEGIN 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; Call ID department is: MIS Original string value: MIS 101 Start position: 4 Number of characters: 3 Course Number is: 10 PL/SQL procedure successfully completed.

  36. -- PL/SQL#5b Program with a correct logic DECLARE curr_call_id VARCHAR2(30) := 'MIS 101'; blank_space NUMBER(2); curr_dept VARCHAR2(30); curr_number VARCHAR2(30); BEGIN 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; Call ID department is: MIS Original string value: MIS 101 Start position: 4 Number of characters: 3 Course Number is: 101 PL/SQL procedure successfully completed.

  37. 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)

  38. Types of Functions Table 10-1 Functions Covered in This Chapter

  39. Practice all the examples in the text. • A Script file is available on the Bb (file name: Ch10Queries.sql) • After completing all examples, do the HW.

  40. Homework - Hands-On Assignments Email me with one attachment (Oracle_ch10_Spool_Lname_Fname.) to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch10 (or Bmis441-02_Oracle_ch10) Read and Practice all examples on Chapters 10 • 1. Run the script files (in the folder \oradata\chapter10\): JLDB_Build_10.sql and download other data files • 2. Read Oracle assignment and create a script file Oracle_ch10_Lname_Fname.sql for questions (#3,4,10; p.381) 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 email the file (Oracle_ch10_Spool_Lname_Fname.txt) to me by the midnight before the next class.

  41. How to Spool your Script and Output Files After you tested the script file of Oracle_ch10_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:\oradata\chapter10\JLDB_Build_10.sql • 1. type the following on SQL> • Spool c:\oradata\Oracle_ch10_Spool_Lname_Fname.txt (make sure your name is entered) • 2. open Oracle_ch10_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) Email me with the spooled file (.txt) with attachment to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch10 (or Bmis441-02_Oracle_ch10)

  42. Your Turn … • PART II • Study yourself • “Learning to learn and learning to change”

  43. Nesting Functions Figure 10-8 INSTR nested inside SUBSTR

  44. LENGTHFunction • Used to determine the number of characters in a string Figure 10-10 Checking data width with the LENGTH function

  45. LTRIM and RTRIM Functions • Used to remove a specific string of characters Figure 10-12 Using the LTRIM function

  46. REPLACEFunction • Substitutes a string with another specified string Figure 10-13 Using the REPLACE function

  47. TRANSLATE Function Figure 10-14 Using TRANSLATE to substitute character values

  48. CONCATFunction • Used to concatenate two character strings Figure 10-15 Using the CONCAT function

  49. Number Functions • Allow for manipulation of numeric data • ROUND • TRUNC • MOD • ABS

  50. ROUNDFunction • Used to round numeric columns to a stated precision Figure 10-16 Using the ROUND function to round numbers to various places

More Related