1 / 18

Design & Implementation

Design & Implementation. SQL Functions and Procedures. SQL in a Programming Environment.

yama
Download Presentation

Design & Implementation

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. Design & Implementation • SQL Functions and Procedures

  2. SQL in a Programming Environment SQL is a non procedural language , and it allows for many tasks to be performed with a single command. When the tasks that need to be completed are outside of SQL capabilities, a procedural language needs to be used. Procedural languages are characterized by the need to provide the computer with a step by step process that will accomplish the task. PL/SQL was developed as an extension to SQL to embed SQL command in another language. These procedures can be saved and become available for use at any time

  3. SQL includes functions that can affect single records, allowing the user to affect character data, and manipulate numeric data types. Character Functions SELECT Rep_Num, UPPER(Last_Name) FROM REP; The UPPER() function displays a value in uppercase letters. This function does not change the data stored in the table, just the way it is displayed as a result MySQL also supports the LOWER function, and allows both to be used with the WHERE clause.

  4. Character Functions - Concatenation The CONCAT function allows character data strings to be concatenated together. SELECT Rep_Num, CONCAT(First_Name, ’ ‘, Last_Name) AS Full_Name FROM REP; NOTE: Numeric strings can also be concatenated together but they have to be converted to strings first.

  5. Character Functions - Replacement The REPLACE function replaces every occurrence of a character (or string) with another specified character (or string). SELECT Rep_Num, REPLACE(First_Name,’a’,‘z’) FROM REP; NOTE: The TRANSLATE() function is very similar to the REPLACE() function, but it will make note of the position of the characters before making replacements, (can’t be used for replacement of strings).

  6. Character Functions - Substring The SUBSTRING() function returns a substring of a specified length from the data value in the argument. This function has 3 arguments: • the value from which to retrieve the substring; • the starting position for the retrieval; • how many characters to return SELECT Part_Num, SUBSTRING(Part_Num,1,2) AS Part_T FROM PART;

  7. Character Functions - RPAD The RPAD() function adds characters or spaces to the right of a string. The RPAD() function takes 3 arguments: • the value where the padding will be added; • the total number of characters for the field; • what padding to use SELECT Part_Num, RPAD(Description,30,’.’) FROM PART; NOTE:MySQL also supports the LPAD() function.

  8. Character Functions Other character functions supported by MySQL: • INSTR: Searches a string of characters and returns the position of those characters; • LTRIM: Trims characters from the left of a string; • RTRIM: Trims characters from the right of a string; • LENGTH: Finds the length of a string, number, date or expression in bytes; • IFNULL: Allows a substitute value to be specified for NULLS. The substitute has to be of the same type as the field type where the NULL is.

  9. Conversion function transform a data type into another data type. There are 4 types of conversions supported by MySQL: • Character to numeric: allows for arithmetic expressions to be used although this is not a common transformation • Numeric to character: allows for character functions to be used on numeric types of data (more common) • Character to date; • Date to character • MySQL uses the CAST() and CONVERT() functions to convert between types of data Conversion Functions

  10. SQL also includes functions that affect numeric data. The ROUND() function which rounds values to a specified number of decimal places has 2 arguments: • the value to be rounded, and • the number of decimal places to include in the rounding Number Functions SELECT Part_Num, Price, ROUND(Price,0) AS Rounded_Price FROM PART; The FLOOR function will truncate the number at the decimal point. So including FLOOR (Price) : $24.95  $24

  11. Number Functions Other number functions supported by MySQL: • ABS: Returns the absolute value of the argument; • SQRT: Returns the square root of an argument; • SIGN: Returns the sign of the argument (+/-); • POWER: Returns the nth power of the argument; • EXP: Returns exponential values; • SIN / COS / TAN: Returns values for trigonometric functions of the argument

  12. SQL also uses functions specific to date fields. Each implementation of SQL uses its own representation and syntax for date fields. My SQL uses: • DATETIME: Stores both date and time information; • TIMESTAMP: Stores both date and time information; • DATE; Stores a date value; • TIME: Stores a time value; • YEAR: One byte type that represents the year Date Implementation in MySQL

  13. Some of the date functions supported by MySQL: • NOW(): Returns today’s date and time ; • DAYNAME(date): Returns the day of the week; • DAYOFMONTH(date): Returns the day of month; • MONTHNAME(date): Returns the month; • DAYOFWEEK(date): Returns the day of the week; • DAYOFYEAR: Returns the day of the year; Date Functions

  14. SQL also uses functions to manipulate date data types. The DATE_ADD() function can be used to add time to a date. This function also takes 2 arguments and uses the INTERVAL command: • the date to be manipulated, and • the number of intervals to add with the interval measure; Working with Dates SELECT Order_Num, Order_Date, DATE_ADD(Order_Date, INTERVAL 2 MONTH) AS Future_Date FROM ORDERS;

  15. Cursors are typically used to retrieve a subset of data from the database in a row based operation. Each row in the cursor can be evaluated by a program one row at a time. Cursor are normally used in SQL that is embedded in procedural type programs. My SQL uses the following syntax to declare a cursor: • DECLARECursor_NameCURSOR • FOR SELECT_STATEMENT • After the cursor has been defined it used with the OPEN, FETCH and CLOSE operations. Cursors

  16. Cursors Cursors need to be opened before they can be used. When a cursor is opened the specified cursor’s select statement is executed and the results of the query are stored in a staging area. My SQL uses the following syntax to open a cursor: • OPENCursor_Name

  17. The contents of the cursor can be retrieved through a FETCH statement after the cursor has been opened. My SQL uses the following syntax to open a cursor: • FETCHCursor_NameINTOVariable_Name • The cursor can be closed after which it will no longer become available to user programs. All the resources are automatically released when the cursor is closed. My SQL uses the following syntax to close a cursor: • CLOSECursor_Name Cursors

  18. DECLARE done INT DEFAULT 0; • DECLARE CustName VARCHAR (30); • DECLARE NameCursor CURSOR • FOR SELECT Cust_Name FROM TBL_CUSTOMER; • OPEN NameCursor; • read_loop: LOOP • FETCH NameCursor INTO CustName; • IF done THEN • LEAVE read_loop; • END IF; • *** Do something with the variable*** • END LOOP; • CLOSE NameCursor; • END; Cursors - Example Syntax (pseudo code)

More Related