1 / 115

ITBIS373 Database Development

ITBIS373 Database Development. Lecture 2 – Chapter 4B Introduction to PL/SQL. Objectives. Create PL/SQL decision control structures Use SQL queries in PL/SQL programs Create loops in PL/SQL programs Create PL/SQL tables and tables of records

chi
Download Presentation

ITBIS373 Database Development

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. ITBIS373 Database Development Lecture 2 – Chapter 4B Introduction to PL/SQL

  2. Objectives • Create PL/SQL decision control structures • Use SQL queries in PL/SQL programs • Create loops in PL/SQL programs • Create PL/SQL tables and tables of records • Use cursors to retrieve database data into PL/SQL programs • Use the exception section to handle errors in PL/SQL programs

  3. PL/SQL Decision Control Structures • So far the programs we have written use sequential processing, this is one statement after the other • Most programs do require decision control structures that will alter the order the statements execute in based on the values of certain variables • In PL/SQL you can create the following decision control structures: • IF/THEN • IF/THEN/ELSE • IF/ELSIF

  4. IF/THEN • The PL/SQL IF/THEN decision control structure has the following syntax: • IF condition THEN commands that execute if condition is TRUE; END IF; • The condition is an expression that PL/SQL has to be able to evaluate as either TRUE or FALSE • The condition can compare two values such as a variable and a literal or the condition can be a Boolean variable • The PL/SQL comparison operators are shown on the next slide

  5. IF/THEN

  6. IF/THEN • If the condition evaluates as TRUE one or more program statements execute • If the condition evaluates as FALSE or NULL the program skips the statements • It is good practice to format IF/THEN structures by indenting the program statements that execute if the condition is TRUE so the structure is easier to read, same applies to FALSE • If the condition evaluates as NULL it behaves the same as if the condition evaluated as FALSE • Can evaluate to NULL if a variable has not been assigned a value, of is of the variables in the condition have a NULL value

  7. IF/THEN The above PL/SQL script shows no output since this was not done on a Friday, notice SERVEROUTPUT is set to ON, so this is not the problem

  8. IF/THEN The above PL/SQL script shows output since it was modified with the != to reverse the logic

  9. IF/THEN/ELSE • The previous example suggests the need for a decision control structure that executes alternate program statements when the condition executes as FALSE • This is where the IF/THEN/ELSE structure is used • The IF/THEN/ELSE structure has the following syntax: • IF condition THEN commands that execute if condition is TRUE; ELSE commands that execute if condition is FALSE; END IF;

  10. IF/THEN/ELSE The above PL/SQL script shows output since it was modified with the to display output regardless if the condition is TRUE or FALSE

  11. Nested IF/THEN/ELSE • You can nest IF/THEN/ELSE structures by placing more than one IF/THEN/ELSE statements within the program that execute after the IF or the ELSE command • It is good coding to properly indent code to understand the logic of of the command and to spot syntax errors • The code will be modified again to show the nested IF/THEN/ELSE

  12. Nested IF/THEN/ELSE The above PL/SQL script shows output using a nested IF/THEN/ELSE statement, notice the two END IF statements one for each IF

  13. IF/ELSIF • The IF/ELSIF allows you to test for many different conditions, note spelling of ELSIF • The syntax for the IF/ELSIF structure is: • IF condition1 THEN commands that execute if condition1 is TRUE; ELSIF condition2 THEN commands that execute if condition2 is TRUE; ELSIF condition3 THEN commands that execute if condition3 is TRUE; ... ELSE commands that execute if none of the conditions are TRUE; END IF;

  14. IF/ELSIF • In the IF/ELSIF decision control structure, the interpreter evaluates the condition1, if it is TRUE the interpreter executes the associated program statement(s), then exists the IF/ELSIF structure • If condition1 is FALSE then the interpreter evaluates condition2, it will then evaluate condition2 to determine it is TRUE or FALSE • This continues through the IF/ELSIF structure in a similar fashion as mentioned for condition1

  15. IF/ELSIF The above PL/SQL script shows output using the IF/ELSIF statement, notice the single END IF statement and the ELSE to catch an invalid day

  16. Logical Operators AND, OR and NOT • The AND, OR and the NOT logical operators can be used in to create complex expressions for a decision control structure condition • Each of the individual expression’s TRUE and FALSE values will be combined into a single TRUE or FALSE result for the entire condition • The rules for AND and OR operators are identical to any other use of these in any programming language • Also be aware of which operator is evaluated first if both the AND and OR operators are used

  17. Logical Operators AND, OR and NOT The above PL/SQL script shows incorrect output, the current weather is cloudy, yet it is Sunday and the output says it is sunny????

  18. Complex Conditions • Created with logical operators AND, OR and NOT • AND is evaluated before OR, is this statement correct? • Use () to set precedence, evaluate the day first then weather

  19. Logical Operators AND, OR and NOT The above PL/SQL script shows correct output, the current weather is cloudy, so it is Sunday and the output says it is cloudy

  20. Using SQL Queries in PL/SQL Programs

  21. Using SQL Queries in PL/SQL Programs • To use a SQL query or DML command or a transaction control command in a PL/SQL command you simply put the query or command in the PL/SQL program using the same syntax you would use to execute the command in SQL*Plus • In PL/SQL you can use variables instead of literal values to specify data values

  22. Using SQL Queries in PL/SQL Programs • For example you could code the following: INSERT INTO student (s_first) VALUES (v_curr_first_name); • You could also perform the following: WHERE s_first = v_curr_first_name; • The value would have to assigned to the variable names that are used above first before they can be used • Next we will write a program that uses DML and transaction control commands to insert records into a table called TERM

  23. Using SQL Queries in PL/SQL Programs • Start with the SQL*Plus and run the script file provided called emptynorthwoods.sql • This will create the required tables for the Northwoods University database • The tables will be empty, check to verify the TERM table is indeed empty

  24. Download Student Data Files Use the browser to go to www.course.com, select Databases from list on left frame

  25. Download Student Data Files Select Oracle in the centre area

  26. Download Student Data Files Find the textbook for our course, and select that title

  27. Download Student Data Files Select Download Student Files at the bottom of the screen

  28. Download Student Data Files Select the link for Data Files for Students

  29. Download Student Data Files Select Open to allow you to unzip the file to your hard drive

  30. Download Student Data Files I choose to place mine in the same directory I had set up to store my PL/SQL work

  31. Download Student Data Files It will place each chapter into its own folder

  32. Download Student Data Files We need chapter 4 for the script that is needed for execution, EmptyNorthwoods.sql

  33. Using SQL Queries in PL/SQL Programs Run the emptynorthwoods script that is provide in the student data files for this textbook

  34. Using SQL Queries in PL/SQL Programs Check to see if the TERM table exists and that it is empty

  35. Using SQL Queries in PL/SQL Programs Write the above PL/SQL program in Notepad and execute in SQL*Plus

  36. Using SQL Queries in PL/SQL Programs Use a SELECT statement on the TERM table to see if the records exist

  37. Loops in PL/SQL • Five different types of loops in PL/SQL • LOOP … EXIT • LOOP … EXIT WHEN • WHILE … LOOP • Numeric FOR Loops • Cursor FOR Loops (look at later in chapter)

  38. Loops in PL/SQL • Loops can be classified as either a pretest loop or a posttest loop • If the program statements might never be executed use the pretest loop • If the program statements are always executed at least once use the posttest loop

  39. Loops • To illustrate the different types of loops a table is to be created called COUNT_TABLE • We will use the various types of loops to insert the numbers 1 through 5 into the table, into a column called COUNTER

  40. Loops Create table to demonstrate the various types of loops, records will be added and then deleted for each of the various loop types

  41. The LOOP … EXIT Loop • The basic syntax of the command: LOOP program statements for loop IF condition THEN EXIT; END IF; moreprogram statements for loop END LOOP; • Loop can either be a pretest or a posttest loop where the condition is tested either before or after the program statements are executed

  42. The LOOP … EXIT Loop • If the IF/THEN decision structure is the first code in the loop it is then a pretest loop • If the IF/THEN is the last code in the loop it is a posttest loop • Good programming practice to indent the program lines between the LOOP and END LOOP commands to make the loop structure easier to read • Next we will write a PL/SQL program to insert records into the COUNT_TABLE using the LOOP … EXIT loop

  43. The LOOP … EXIT Loop Use the LOOP … EXIT loop to insert the 5 records to the table, then verify to see if they are there

  44. The LOOP … EXIT WHEN Loop • The basic format of the command could look like this: LOOP program statements EXIT WHEN condition; END LOOP; • This loop executes the program statements then tests for the condition • This is a posttest loop the program statements are always executed at least once and tested after they have executed

  45. The LOOP … EXIT WHEN Loop • The basic format of the command can also appear as this: LOOP EXIT WHEN condition; program statements END LOOP; • This loop executes the program statements then tests for the condition • This is a pretest loop the program statements are always executed after the condition is tested

  46. The LOOP … EXIT WHEN Loop Use the LOOP … EXIT WHEN loop is used as a posttest loop to insert the five rows to the table, table records were first deleted

  47. The WHILE … LOOP • The basic format of the command: WHILE condition LOOP program statements END LOOP; • This a pretest loop, the condition is evaluated before any program statements are executed

  48. The WHILE … LOOP Table records were first deleted, then the WHLE … LOOP was executed to insert the five records to the table

  49. The Numeric FOR Loop • The basic syntax for the command is: FOR counter variable IN start value .. end value LOOP program statements END LOOP; • Start and End values must be integers • Do not have to declare a counter or increment the counter manually • Counter is defined in the start and end numbers in the FOR statement and automatically increments each time the loop repeats • DECLARE section is omitted because there is nothing to declare

  50. The Numeric FOR Loop Table records were first deleted, then the FOR … LOOP was executed to insert the five records to the table

More Related