SQL Chapter 8
Embedded SQL

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

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

Introduction

SQL is a non-procedural language

Non-procedural language: many tasks can be accomplished using a single, relatively simple command

Procedural language: computer must be given the step-by-step process for accomplishing tasks

PL/SQL

l procedural language developed by Oracle

l an extension of SQL

This chapter uses PL/SQL to illustrate how to embed SQL commands in another language

To embed SQL commands means to include SQL commands directly within programs written in another language

Using Prompt Variables

There are many sources of input for SQL commands

In this text, input comes from prompt variables

Prompt variables: variables for which the user is prompted to enter a value

To designate a prompt variable, precede the name with an ampersand (&)

Using Prompt Variables

Problem:

l List the last name of the sales rep whose number is contained in the prompt variable I_REP_NUM.

Solution:

SQL> SELECT LAST_NAME
2 FROM REP
3 WHERE REP_NUM = '&I_REP_NUM';

PL/SQL Programs

SQL commands can be embedded in PL/SQL programs

The programs can be created and saved as script files

To run the programs, run the script files

Retrieving a Single Row and Column

Problem:

l Write a PL/SQL program to obtain the last name of the sales rep whose number is contained in the prompt variable I_REP_NUM, place it in the variable I_LAST_NAME, and then display the contents of I_LAST_NAME.

Retrieving a Single Row and Column

To place the results of a command in a variable, use the INTO clause

To see the contents of the line (the output), use:

SET SERVEROUTPUT ON

Retrieving a Single Row and Column

Solution:

SET SERVEROUTPUT ON
DECLARE
I_REP_NUM CHAR(2);
I_LAST_NAME CHAR(15);
BEGIN
SELECT LAST_NAME
INTO I_LAST_NAME
FROM REP
WHERE REP_NUM = '&I_REP_NUM';
DBMS_OUTPUT.PUT_LINE(I_LAST_NAME);
END;
/

Using the %Type Attribute

%TYPE attribute: ensures that a variable has the same type as a particular column

To assign the variable I_REP_NUM the same type as the REP_NUM column in the REP table, use the following: 

I_REP_NUM REP.REP_NUM%TYPE

Retrieving a Single Row from a Join

An embedded SQL command can be used to join tables

Problem:

l Obtain the name of the customer whose customer number is stored in the prompt variable I_CUSTOMER_NUM, and the last and first names of the sales rep who represents this customer.

Retrieving a Single Row from a Join

SET SERVEROUTPUT ON
DECLARE
I_LAST_NAME REP.LAST_NAME%TYPE;
I_FIRST_NAME REP.FIRST_NAME%TYPE;
I_CUSTOMER_NUM CUSTOMER.CUSTOMER_NUM%TYPE;
I_CUSTOMER_NAME CUSTOMER.CUSTOMER_NAME%TYPE;
BEGIN
SELECT LAST_NAME, FIRST_NAME, CUSTOMER_NAME
INTO I_LAST_NAME, I_FIRST_NAME, I_CUSTOMER_NAME
FROM REP, CUSTOMER
WHERE REP.REP_NUM = CUSTOMER.REP_NUM
AND CUSTOMER_NUM = '&I_CUSTOMER_NUM';
DBMS_OUTPUT.PUT_LINE(I_CUSTOMER_NAME);
DBMS_OUTPUT.PUT_LINE(I_LAST_NAME);
DBMS_OUTPUT.PUT_LINE(I_FIRST_NAME)
END;
/

Inserting a Row into a Table

To update databases from within PL/SQL programs, appropriate SQL commands are used

SQL INSERT: add a row to a table in the database

Problem:

l Add a row to the REP table. Use prompt variables to obtain values for the fields

Inserting a Row into a Table

The procedural code part of the solution program is:

BEGIN
INSERT INTO REP (REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, ZIP, COMMISSION, RATE)
VALUES
('&I_REP_NUM', '&I_LAST_NAME', '&I_FIRST_NAME', '&I_STREET', '&I_CITY', '&I_STATE', '&I_ZIP',
&I_COMMISSION, &I_RATE);
END;

Changing a Single Row in a Table

SQL commands can also be used to update the rows in a database

Problem:

l Change the last name of the sales rep whose number is stored in I_REP_NUM to the value currently stored in I_LAST_NAME.

Changing a Single Row in a Table

Solution:

SET SERVEROUTPUT ON
DECLARE
I_REP_NUM REP.REP_NUM%TYPE;
I_LAST_NAME REP.LAST_NAME%TYPE;

BEGIN
UPDATE REP
SET LAST_NAME = '&I_LAST_NAME‘
WHERE REP_NUM = '&I_REP_NUM';
END;
/

Deleting Rows from a Table

SQL commands can be used to delete rows

Problem:

l Delete the sales rep whose number currently is stored in I_REP_NUM from the REP table.

Deleting Rows from a Table

Solution:

SET SERVEROUTPUT ON

DECLARE
I_REP_NUM REP.REP_NUM%TYPE;

BEGIN
DELETE
FROM REP
WHERE REP_NUM = '&I_REP_NUM';

END;
/

Deleting Rows from Multiple Tables

SQL commands can be used to delete more than one row from a table

Problem:

l Delete the order whose number is stored in I_ORDER_NUM from the ORDERS table, and then delete each order line for the order whose order number is currently stored in the variable from the ORDER_LINE table.

Deleting Rows from Multiple Tables

Solution:

DECLARE
I_ORDER_NUM ORDERS.ORDER_NUM%TYPE;
W_ORDER_NUM CHAR(5);
BEGIN

I_ORDER_NUM := &W_ORDER_NUM;
DELETE
FROM ORDER_LINE
WHERE ORDER_NUM = I_ORDER_NUM;
DELETE
FROM ORDERS
WHERE ORDER_NUM = I_ORDER_NUM;
END;
/

Multiple-Row Select

A SELECT command that retrieves multiple rows poses a problem since PL/SQL can process only one record at a time

This problem can be solved by using a cursor

Using Cursors

Cursor: a pointer to a row in the collection of rows retrieved by a SQL command

The cursor advances one row at a time to provide sequential, one-record-at-a-time access to the retrieved rows

By using a cursor, PL/SQL can process the set of retrieved rows as though they were records in a sequential file

Using Cursors

Using a cursor in the procedural portion of the program involves three commands:

l OPEN: opens the cursor and causes the query to be executed

l FETCH: advances the cursor to the next row in the set of rows retrieved by the query and places the contents of the row in the indicated variables

l CLOSE: closes a cursor and deactivates it

Prior to opening the cursor, there are no rows available to be fetched

The OPEN command to open the CUSTGROUP cursor is:

OPEN CUSTGROUP;

Fetching Rows from a Cursor

FETCH: fetch (get) the next row from a cursor

The FETCH command is written as follows:

FETCH CUSTGROUP INTO I_CUSTOMER_NUM, I_CUSTOMER_NAME;

Closing a Cursor

The CLOSE command is written as follows:

CLOSE CUSTGROUP;

Advantages of Cursors

The three main advantages of using cursors:

l The coding in the program is greatly simplified

l In a program using embedded SQL, a special component of the DBMS called the optimizer determines the best way to access the data

l If the database structure changes in such a way that the necessary information is still obtainable using a different query, the only change required in the program is the cursor definition

Updating Cursors

Data can be updated through a cursor by using the FOR UPDATE OF clause

A WHERE clause can be used to restrict the scope of the update

Error Handling

Programs must be able to handle exceptional conditions that can arise when accessing the database

For example, a user might enter a value that would cause an error to occur

Errors can be handled by using the EXCEPTION command

Using SQL in Microsoft Access Programs

In Microsoft Access, programs are written in Visual Basic

Visual Basic does not allow the inclusion of SQL commands directly in the code

If the SQL command is stored in a string variable, the DoCmd.RunSQL command can be used to run the command

The procedure in which you place the SQL command can include arguments

Arguments are values that provide information to the procedure

Example:

l In a procedure to delete a sales rep, the argument would be the sales rep number

Deleting Rows

To delete the sales rep whose number is 20, the command is:

l DELETE FROM REP WHERE REP_NUM = '20';

Usually, the specific sales rep number to delete is not known in advance

It can be passed as an argument to the procedure containing the DELETE command

A procedure is entered in the Microsoft Visual Basic window

Running the Code

Normally, you run a function by:

l Calling it from another procedure

l Associating it with some event

Functions can also be run directly by using the Immediate window

Running the Code

To run a function procedure in the Immediate window:

l Type:

l A question mark

l The name of the procedure

l A set of parentheses

l Place the values for any arguments in the parentheses

l After typing the command, press the Enter key

Updating Rows

A procedure can update a table by using an UPDATE command

Such a procedure would be similar to the one used to delete a sales rep

Inserting Rows

The process for inserting rows is similar

The appropriate INSERT command is created in the strSQL variable

There will be multiple arguments in the procedure—one for each value to be inserted

Finding Rows

A SELECT command that returns several rows poses serious problems for record-at-a-time languages like PL/SQL and Visual Basic

For such SELECT commands, the results of a query are handled in the same way as a loop is used to process through the records in a table

Summary

To embed a SQL command in a PL/SQL program, type it in the procedural code

Variables in PL/SQL programs are declared after the word DECLARE

To assign variables the same type as a column, use the %TYPE attribute

You can request user input in SQL commands by using prompt variables

If a SELECT command is to retrieve more than one row, define a cursor to select one row at a time

Data in the tables on which a cursor is based can be updated with FOR UPDATE OF

To see whether an error has occurred, use the EXCEPTION command

To use SQL commands in Access, create the command in a string variable

To run the command stored in the string variable, use DoCmd.RunSQL

To process a collection of rows retrieved by a SELECT in Access, use a recordset

SQL Project Eight Completed

Good Luck

H. Zamanzadeh