A Guide to SQL, Seventh Edition

1. A Guide to SQL, Seventh Edition

2. Objectives • Embed SQL commands in PL/SQL programs • Retrieve single rows using embedded SQL • Update a table using embedded INSERT, UPDATE, and DELETE commands • Use cursors to retrieve multiple rows in embedded SQL A Guide to SQL, Seventh Edition

3. Objectives • Update a database using cursors • Manage errors in programs containing embedded SQL commands • Use SQL in a language that does not support embedded SQL commands A Guide to SQL, Seventh Edition

4. Introduction • A procedural language requires a step-by-step process to accomplish tasks • You can embed, or include, SQL commands in the programs using procedural languages • Useful when needed tasks are beyond the capabilities of SQL A Guide to SQL, Seventh Edition

5. Using Prompt Variables • Sources of input for SQL commands • Onscreen form • Passed as arguments from procedure • Prompt variables • Prompt variables are when user is prompted to enter a value when the program is run • Precede the variable with an “&” A Guide to SQL, Seventh Edition

6. A Guide to SQL, Seventh Edition

7. PL/SQL Programs • Embed SQL commands in PL/SQL programs • Create and save the programs as script files • Run the script files to run the programs A Guide to SQL, Seventh Edition

8. Retrieving a Single Row and Column • To place the results of a command in a variable, use the INTO clause • SELECT LAST_NAMEINTO I_LAST_NAMEFROM REPWHERE REP_NUM = ‘&I_REP_NUM;’ A Guide to SQL, Seventh Edition

9. Retrieving a Single Row and Column • When executed, user will be prompted for a value for I_REP_NUM • That value will be used to retrieve the last name of the sales rep whose number equals this value • The results will be placed in the variable I_LAST_NAME • This variable can be used in another program A Guide to SQL, Seventh Edition

10. Retrieving a Single Row and Column • Procedure uses one argument and produces a single line of text containing this argument output • To see the output, execute the command • SET SERVEROUTPUT ON • Include this command in the PL/SQL program to ensure it will be executed A Guide to SQL, Seventh Edition

11. A Guide to SQL, Seventh Edition

12. Retrieving a Single Row and Column • Procedural code are commands that specify exactly what program is to do • Procedural code located between BEGIN and END commands • Each variable declaration and command as well as the word END are followed by semicolons A Guide to SQL, Seventh Edition

13. Retrieving a Single Row and Column • The slash (/) at the end of the program appears on its own line • When the script is run, the slash causes commands in the program to be executed immediately • With the slash, commands are only loaded into memory • Typing the slash runs the program A Guide to SQL, Seventh Edition

14. A Guide to SQL, Seventh Edition

15. Using the %TYPE Attribute • The %TYPE attribute ensures the variable has the same type as a particular column in a table • Do not enter a data type  it is automatically assigned from the corresponding column • I_REP_NUM REP.REP_NUM%TYPE A Guide to SQL, Seventh Edition

16. Retrieving a Single Row from a Join • Use embedded SQL commands to join tables A Guide to SQL, Seventh Edition

17. Inserting a Row into a Table • When updating a database from the PL/SQL program, use appropriate SQL commands A Guide to SQL, Seventh Edition

18. A Guide to SQL, Seventh Edition

19. Changing a Single Row in a Table • Use the SQL command to insert rows in a database A Guide to SQL, Seventh Edition

20. Deleting Rows from a Table • SQL commands are used to delete rows from a table A Guide to SQL, Seventh Edition

21. Multiple-Row Select • PL/SQL can process only one record at a time • A cursor is a pointer to a row in the collection of rows retrieved by a SQL command • A cursor advances one row at a time to provide sequential one-record-at-a-time access to retrieved rows A Guide to SQL, Seventh Edition

22. Using Cursors • The first step is to declare the cursor and describe the associated query in the declaration section • CURSOR CUSTGROUP ISSELECT CUSTOMER_NUM, CUSTOMER_NAMEFROM CUSTOMERWHERE REP_NUM = ‘&I_REP_NUM’; • Three commands are needed • OPEN, FETCH, CLOSE A Guide to SQL, Seventh Edition

23. Opening a Cursor • OPEN command • Opens cursor • Causes query to be executed • Makes results available to the program • Prior to opening, there are no rows available to be fetched • OPEN CUSTGROUP A Guide to SQL, Seventh Edition

24. A Guide to SQL, Seventh Edition

25. Fetching Rows from a Cursor • FETCH command • Advances cursor to next row in set of retrieved rows • Places contents of row in indicated variables • FETCH CUSTGROUP I_CUSTOMER_NUM,I_CUSTOMER_NAME; • Execution of fetch command produces only a single row A Guide to SQL, Seventh Edition

26. A Guide to SQL, Seventh Edition

27. A Guide to SQL, Seventh Edition

28. Closing a Cursor • CLOSE command • Closes a cursor • Deactivates it • Data retrieved by execution of the query is no longer available A Guide to SQL, Seventh Edition

29. A Guide to SQL, Seventh Edition

30. A Guide to SQL, Seventh Edition

31. More Complex Cursors • Any SLQ query is legitimate in a cursor definition • More complicated retrieval requirements result in greater benefits A Guide to SQL, Seventh Edition

32. A Guide to SQL, Seventh Edition

33. A Guide to SQL, Seventh Edition

34. Advantages of Cursors • Simplified coding in the program • Progams with embedded SQL utilizes the optimizer • Programmer doesn’t worry about the best way to retrieve data • Program doesn’t have to change even if the underlying structure does • Cursor definition only changes; not procedural code A Guide to SQL, Seventh Edition

35. Updating Cursors • Update the rows encountered in processing cursors with FOR UPDATE OF A Guide to SQL, Seventh Edition

36. A Guide to SQL, Seventh Edition

37. Error Handling • Handle errors with the EXCEPTION command A Guide to SQL, Seventh Edition

38. Using SQL in Microsoft Access Programs • In Access, programs are written in Visual Basic • Does not allow inclusion of SQL commands in the code • If the SQL command is store in string variable, use the DoCmd.RunSQL command A Guide to SQL, Seventh Edition

39. Deleting Rows • Place the SQL command in the procedure, including arguments • Example • DELETE FROM REP WHERE REP_NUM = ’20’ A Guide to SQL, Seventh Edition

40. A Guide to SQL, Seventh Edition

41. Running the Code • Normally run by calling it from another procedure or by associating it with an event • Can be run by using the Immediate window • Normally for testing A Guide to SQL, Seventh Edition

42. A Guide to SQL, Seventh Edition

43. Updating Rows • Similar to the procedure to delete a sales rep, except • Need the UPDATE command • Two arguments rather than one • Two portions of the construction of the SQL command that involve variables A Guide to SQL, Seventh Edition

44. A Guide to SQL, Seventh Edition

45. Inserting Rows • Process is similar • Create the appropriate INSERT command in the strSQL variable • Multiple arguments • Once for each value inserted A Guide to SQL, Seventh Edition

46. Finding Rows • SELECT commands handled differently than in PL/SQL • No cursors • Handle results of query just as you would use a loop to process through the records on the table A Guide to SQL, Seventh Edition

47. A Guide to SQL, Seventh Edition

48. A Guide to SQL, Seventh Edition

49. Summary • How to embed SQL commands in PL • Created programs • Retrieved single rows • Inserted new rows • Changed and deleted existing rows • Cursors to update database • Error Handling • Using SQL command in Access A Guide to SQL, Seventh Edition