Hour 22. Advanced SQL Topics
What You’ll Learn in This Hour:
• What cursors are
• Using stored procedures
• What triggers are
• Basics of dynamic SQL
• Using SQL to generate SQL
• Direct SQL versus embedded SQL
• Call-level interface
In this hour, you are introduced to some advanced SQL topics that extend beyond the basic operations that you have learned so far, such as querying data from the database, building database structures, and manipulating data within the database. By the end of the hour, you should understand the concepts behind cursors, stored procedures, triggers, dynamic SQL, direct versus embedded SQL, and SQL generated from SQL. These advanced topics are features available in many implementations, all of which provide enhancements to the parts of SQL discussed so far.
By the Way: Some Topics Are Not ANSI SQL Related
Not all topics are ANSI SQL, so you must check your particular implementation for variations in syntax and rules. A few major vendors’ syntax is shown in this hour for comparison.
Cursors
Normally, database operations are commonly referred to as set-based operations. This means that the majority of ANSI SQL commands are geared toward working on a block of data. A cursor, on the other hand, is typically used to retrieve a subset of data from the database in a row-based operation. Thereby, each row in the cursor can be evaluated by a program, one row at a time. Cursors are normally used in SQL that is embedded in procedural-type programs. Some cursors are created implicitly by the database server, whereas others are defined by the SQL programmer. Each SQL implementation might define the use of cursors differently.
This section shows syntax examples from three popular implementations that we have tracked throughout the book: MySQL, Microsoft SQL Server, and Oracle.
The syntax to declare a cursor in MySQL is as follows:
DECLARE CURSOR_NAME CURSOR
FOR SELECT_STATEMENT
The syntax to declare a cursor for Microsoft SQL Server is as follows:
DECLARE CURSOR_NAME CURSOR
FOR SELECT_STATEMENT
[ FOR [READ ONLY | UPDATE {[ COLUMN_LIST ]}]
The syntax for Oracle is as follows:
DECLARE CURSOR CURSOR_NAME
IS {SELECT_STATEMENT}
The following cursor contains the result subset of all records from EMPLOYEE_TBL:
DECLARE CURSOR EMP_CURSOR IS
SELECT * FROM EMPLOYEE_TBL
{ OTHER PROGRAM STATEMENTS }
According to the ANSI standard, you use the following operations to access a cursor after it has been defined:
• OPEN: Opens a defined cursor
• FETCH: Fetches rows from a cursor into a program variable
• CLOSE: Closes the cursor when operations against the cursor are complete
Opening a Cursor
You cannot access a cursor until you have opened it. 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 in memory.
The syntax to open a cursor in MySQL and Microsoft SQL Server is as follows:
OPEN CURSOR_NAME
The syntax in Oracle is as follows:
OPEN CURSOR_NAME [ PARAMETER1 [, PARAMETER2 ]]
To open the EMP_CURSOR, use the following statement:
OPEN EMP_CURSOR
Fetching Data from a Cursor
You can retrieve the contents of the cursor (results from the query) through the FETCH statement after you have opened the cursor.
The syntax for the FETCH statement in Microsoft SQL Server is as follows:
FETCH NEXT FROM CURSOR_NAME [ INTO FETCH_LIST ]
The syntax for Oracle is as follows:
FETCH CURSOR_NAME {INTO : HOST_VARIABLE
[[ INDICATOR ] : INDICATOR_VARIABLE ]
[, : HOST_VARIABLE
[[ INDICATOR ] : INDICATOR_VARIABLE ]]
| USING DESCRIPTOR DESCRIPTOR ] }
The syntax for MySQL is as follows:
FETCH CURSOR_NAME into VARIABLE_NAME,[VARIABLE_NAME] ...
To fetch the contents of EMP_CURSOR into a variable called EMP_RECORD, your FETCH statement might appear as follows:
FETCH EMP_CURSOR INTO EMP_RECORD
When fetching data from a cursor, note that at some time you will come to the end of the cursor. Each implementation has a different way to set up handling of this so that you can gracefully close the cursor without receiving an error. Following are pseudocode examples from MySQL, Microsoft SQL Server, and Oracle on how to handle these situations. The syntax is meant to give you a feel for the process of handling cursors.
The syntax for MySQL is as follows:
BEGIN
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;
The syntax for Microsoft SQL Server is as follows:
BEGIN
DECLARE @custname VARCHAR(30);
DECLARE namecursor CURSOR FOR SELECT CUST_NAME FROM TBL_CUSTOMER;
OPEN namecursor;
FETCH NEXT FROM namecursor INTO @custname
WHILE (@@FETCH_STATUS<>-1)
BEGIN
IF (@@FETCH_STATUS<>-2)
BEGIN
-- Do something with the variable
END
FETCH NEXT FROM namecursor INTO @custname
END
CLOSE namecursor
DEALLOCATE namecursor
END;
The syntax for Oracle is as follows:
custname varchar(30);
CURSOR namecursor
IS
SELECT CUST_NAME FROM TBL_CUSTOMER;
BEGIN
OPEN namecursor;
FETCH namecursor INTO custname;
IF namecursor%notfound THEN
-- Do some handling as you are at the end of the cursor
END IF;
-- Do something with the variable
CLOSE namecursor;
END;
Closing a Cursor
You can obviously close a cursor if you can open one. After it’s closed, it is no longer available to user programs. Closing a cursor is quite simple.
The Microsoft SQL Server syntax for the closing of a cursor and the deallocation of a cursor is as follows:
CLOSE CURSOR_NAME
DEALLOCATE CURSOR CURSOR_NAME
When a cursor is closed in Oracle, the resources and name are released without the DEALLOCATE statement. The syntax for Oracle is as follows:
CLOSE CURSOR_NAME
The same is true for the MySQL cursor. There is no DEALLOCATE statement available because the resources are released when the cursor is closed. The syntax for MySQL is as follows:
CLOSE CURSOR_NAME
By the Way: More Variations Exist in Advanced Features
As you can see from the previous examples, variations among the implementations are extensive, especially with advanced features of and extensions to SQL, which are covered in Hour 24, “Extensions to Standard SQL.” You must check your particular implementation for the exact usage of a cursor.
Stored Procedures and Functions
By the Way: You Need to Deallocate a Cursor to Free Its Resources
Closing a cursor does not necessarily free the memory associated with the cursor. In some implementations, the memory used by a cursor must be deallocated by using the DEALLOCATE statement. When the cursor is deallocated, the associated memory is freed, and the name of the cursor can then be reused. In other implementations, memory is implicitly deallocated when the cursor is closed. Memory is available for other operations, such as opening another cursor, when space used by a cursor is reclaimed. If you do not deallocate the memory a cursor uses, the database could hold onto that memory even if other processes need it. This normally leads to poor performance as the system fights over limited computing resources.
Stored procedures are groupings of related SQL statements—commonly referred to as functions and subprograms—that allow ease and flexibility for a programmer. This ease and flexibility are derived from the fact that a stored procedure is often easier to execute than a number of individual SQL statements. Stored procedures can be nested within other stored procedures. That is, a stored procedure can call another stored procedure, which can call another stored procedure, and so on.
Stored procedures allow for procedural programming. The basic SQL DDL (Data Definition Language), DML (Data Manipulation Language), and DQL (Data Query Language) statements (CREATE TABLE, INSERT, UPDATE, SELECT, and so on) allow you the opportunity to tell the database what needs to be done, but not how to do it. By coding stored procedures, you tell the database engine how to go about processing the data.
A stored procedure is a group of one or more SQL statements or functions that are stored in the database, compiled, and ready to be executed by a database user. A stored function is the same as a stored procedure, but a function returns a value.
Functions are called by procedures. When a function is called by a procedure, parameters can be passed into a function like a procedure, a value is computed, and then the value is passed back to the calling procedure for further processing.
When a stored procedure is created, the various subprograms and functions that compose the stored procedure are actually stored in the database. These stored procedures are preparsed and are immediately ready to execute when the user invokes them.
The MySQL syntax for creating a stored procedure is as follows:
CREATE [ OR REPLACE ] PROCEDURE PROCEDURE_NAME
[ (ARGUMENT [{IN | OUT | IN OUT} ] TYPE,
ARGUMENT [{IN | OUT | IN OUT} ] TYPE) ] { AS}
PROCEDURE_BODY
The Microsoft SQL Server syntax for creating a stored procedure is as follows:
CREATE PROCEDURE PROCEDURE_NAME
[ [(] @PARAMETER_NAME
DATATYPE [(LENGTH) | (PRECISION] [, SCALE ])
[ = DEFAULT ][ OUTPUT ]]
[, @PARAMETER_NAME
DATATYPE [(LENGTH) | (PRECISION [, SCALE ])
[ = DEFAULT ][ OUTPUT ]] [)]]
[ WITH RECOMPILE ]
AS SQL_STATEMENTS
The syntax for Oracle is as follows:
CREATE [ OR REPLACE ] PROCEDURE PROCEDURE_NAME
[ (ARGUMENT [{IN | OUT | IN OUT} ] TYPE,
ARGUMENT [{IN | OUT | IN OUT} ] TYPE) ] {IS | AS}
PROCEDURE_BODY
An example of a simple stored procedure to insert new rows into the PRODUCTS_TBL table is as follows:
CREATE PROCEDURE NEW_PRODUCT
(PROD_ID IN VARCHAR2, PROD_DESC IN VARCHAR2, COST IN NUMBER)
AS
BEGIN
INSERT INTO PRODUCTS_TBL
VALUES (PROD_ID, PROD_DESC, COST);
COMMIT;
END;
Procedure created.
The syntax for executing a stored procedure in Microsoft SQL Server is as follows:
EXECUTE [ @RETURN_STATUS = ]
PROCEDURE_NAME
[[@PARAMETER_NAME = ] VALUE |
[@PARAMETER_NAME = ] @VARIABLE [ OUTPUT ]]
[WITH RECOMPILE]
The syntax for Oracle is as follows:
EXECUTE [ @RETURN STATUS =] PROCEDURE NAME
[[ @PARAMETER NAME = ] VALUE | [ @PARAMETER NAME = ] @VARIABLE [ OUTPUT ]]]
[ WITH RECOMPILE ]
The syntax for MySQL is as follows:
CALL PROCEDURE_NAME([PARAMETER[,.......]])
By the Way: Basic SQL Commands Are Often the Same
You might find distinct differences between the allowed syntax used to code procedures in different implementations of SQL. The basic SQL commands should be the same, but the programming constructs (variables, conditional statements, cursors, loops) might vary drastically among implementations.
Now execute the procedure you have created:
CALL NEW_PRODUCT ('9999','INDIAN CORN',1.99);
PL/SQL procedure successfully completed.
Stored procedures provide several distinct advantages over individual SQL statements executed in the database. Some of these advantages include the following:
• The statements are already stored in the database.
• The statements are already parsed and in an executable format.
• Stored procedures support modular programming.
• Stored procedures can call other procedures and functions.
• Stored procedures can be called by other types of programs.
• Overall response time is typically better with stored procedures.
• Stored procedures increase the overall ease of use.
Triggers
A trigger is a compiled SQL procedure in the database that performs actions based on other actions occurring within the database. A trigger is a form of a stored procedure that is executed when a specified DML action is performed on a table. The trigger can be executed before or after an INSERT, DELETE, or UPDATE statement. Triggers can also check data integrity before an INSERT, DELETE, or UPDATE statement. Triggers can roll back transactions, and they can modify data in one table and read from another table in another database.
Triggers, for the most part, are very good functions to use; they can, however, cause more I/O overhead. Triggers should not be used when a stored procedure or a program can accomplish the same results with less overhead.
The CREATE TRIGGER Statement
You can create a trigger using the CREATE TRIGGER statement.
The ANSI standard syntax is
CREATE TRIGGER TRIGGER NAME
[[BEFORE | AFTER] TRIGGER EVENT ON TABLE NAME]
[REFERENCING VALUES ALIAS LIST]
[TRIGGERED ACTION
TRIGGER EVENT::=
INSERT | UPDATE | DELETE [OF TRIGGER COLUMN LIST]
TRIGGER COLUMN LIST ::= COLUMN NAME [,COLUMN NAME]
VALUES ALIAS LIST ::=
VALUES ALIAS LIST ::=
OLD [ROW] ´ OLD VALUES CORRELATION NAME |
NEW [ROW] ´ NEW VALUES CORRELATION NAME |
OLD TABLE ´ OLD VALUES TABLE ALIAS |
NEW TABLE ´ NEW VALUES TABLE ALIAS
OLD VALUES TABLE ALIAS ::= IDENTIFIER
NEW VALUES TABLE ALIAS ::= IDENTIFIER
TRIGGERED ACTION ::=
[FOR EACH [ROW | STATEMENT] [WHEN SEARCH CONDITION]]
TRIGGERED SQL STATEMENT
TRIGGERED SQL STATEMENT ::=
SQL STATEMENT | BEGIN ATOMIC [SQL STATEMENT;]
END
The MySQL syntax to create a trigger is as follows:
CREATE [DEFINER={user | CURRENT_USER }]
TRIGGER TRIGGER_NAME
{BEFORE | AFTER }
{ INSERT | UPDATE | DELETE [, ..]}
ON TABLE_NAME
AS
SQL_STATEMENTS
The Microsoft SQL Server syntax to create a trigger is as follows:
CREATE TRIGGER TRIGGER_NAME
ON TABLE_NAME
FOR { INSERT | UPDATE | DELETE [, ..]}
AS
SQL_STATEMENTS
[ RETURN ]
The basic syntax for Oracle is as follows:
CREATE [ OR REPLACE ] TRIGGER TRIGGER_NAME
[ BEFORE | AFTER]
[ DELETE | INSERT | UPDATE]
ON [ USER.TABLE_NAME ]
[ FOR EACH ROW ]
[ WHEN CONDITION ]
[ PL/SQL BLOCK ]
The following is an example trigger written in the Oracle syntax:
CREATE TRIGGER EMP_PAY_TRIG
AFTER UPDATE ON EMPLOYEE_PAY_TBL
FOR EACH ROW
BEGIN
INSERT INTO EMPLOYEE_PAY_HISTORY
(EMP_ID, PREV_PAY_RATE, PAY_RATE, DATE_LAST_RAISE,
TRANSACTION_TYPE)
VALUES
(:NEW.EMP_ID, :OLD.PAY_RATE, :NEW.PAY_RATE,
:NEW.DATE_LAST_RAISE, 'PAY CHANGE'),
END;
/
Trigger created.
The preceding example shows the creation of a trigger called EMP_PAY_TRIG. This trigger inserts a row into the EMPLOYEE_PAY_HISTORY table, reflecting the changes made every time a row of data is updated in EMPLOYEE_PAY_TBL.
Did You Know?: Triggers Cannot Be Altered
You cannot alter the body of a trigger. You must either replace or re-create the trigger. Some implementations allow a trigger to be replaced (if the trigger with the same name already exists) as part of the CREATE TRIGGER statement.
The DROP TRIGGER Statement
You can drop a trigger using the DROP TRIGGER statement. The syntax for dropping a trigger is as follows:
DROP TRIGGER TRIGGER_NAME
The FOR EACH ROW Statement
Triggers in MySQL also have another piece of syntax that allows them to be scoped. The FOR EACH ROW syntax allows the developer to have the procedure fire for each row that is affected by the SQL statement or once for the statement as a whole. The syntax is as follows:
CREATE TRIGGER TRIGGER_NAME
ON TABLE_NAME FOR EACH ROW SQL_STATEMENT
The difference is how many times the trigger is executed. If you create a regular trigger and execute a statement against the table that affects 100 rows, the trigger is executed once. If instead you create the trigger with the FOR EACH ROW syntax and execute the statement again, the trigger is executed 100 times—once for each row that the statement affects.
Dynamic SQL
Dynamic SQL allows a programmer or end user to create an SQL statement’s specifics at runtime and pass the statement to the database. The database then returns data into the program variables, which are bound at SQL runtime.
To comprehend dynamic SQL, review static SQL. Static SQL is what this book has discussed thus far. A static SQL statement is written and not meant to be changed. Although static SQL statements can be stored as files ready to be executed later or as stored procedures in the database, static SQL does not quite offer the flexibility that is allowed with dynamic SQL.
The problem with static SQL is that even though numerous queries might be available to the end user, there is a good chance that none of these “canned” queries will satisfy the users’ needs on every occasion. Dynamic SQL is often used by ad hoc query tools, which allow an SQL statement to be created on-the-fly by a user to satisfy the particular query requirements for that particular situation. After the statement is customized according to the user’s needs, the statement is sent to the database, checked for syntax errors and privileges required to execute the statement, and compiled in the database where the database server carries out the statement. Dynamic SQL can be created by using a call-level interface, which is explained in the next section.
By the Way: Dynamic SQL Is Not Always the Most Performant
Although dynamic SQL provides more flexibility for the end user’s query needs, the performance might not compare to that of a stored procedure whose code has already been analyzed by the SQL optimizer.
Call-Level Interface
A call-level interface (CLI) embeds SQL code in a host program, such as ANSI C. Application programmers should be familiar with the concept of a CLI. It is one of the methods that allows a programmer to embed SQL in different procedural programming languages. When using a CLI, you simply pass the text of an SQL statement into a variable using the rules of the host programming language. You can execute the SQL statement in the host program through the use of the variable into which you passed the SQL text.
EXEC SQL is a common host programming language command that enables you to call an SQL statement (CLI) from within the program.
The following are examples of programming languages that support CLI:
• ANSI C
• C#
• VB.NET
• Java
• Fortran
By the Way: CLIs Are Platform Specific
Refer to the syntax of the host programming language with which you are using CLI options. The CLI programming language is always platform specific. So an Oracle CLI will not work with an SQL Server CLI.
Using SQL to Generate SQL
Using SQL to generate SQL is a valuable time-budgeting method of writing SQL statements. Assume you have 100 users in the database already. A new role, ENABLE (a user-defined object that is granted privileges), has been created and must be granted to those 100 users. Instead of manually creating 100 GRANT statements, the following SQL statement generates each of those statements for you:
SELECT 'GRANT ENABLE TO '|| USERNAME||';'
FROM SYS.DBA_USERS;
This example uses Oracle’s system catalog view (which contains information for users).
Notice the use of single quotation marks around GRANT ENABLE TO. The use of single quotation marks allows whatever is between the marks (including spaces) to be literal. Remember that literal values can be selected from tables, the same as columns from a table. USERNAME is the column in the system catalog table SYS.DBA_USERS. The double pipe signs (||) concatenate the columns. The use of double pipes followed by ; concatenates the semicolon to the end of the username, thus completing the statement.
The results of the SQL statement look like the following:
GRANT ENABLE TO RRPLEW;
GRANT ENABLE TO RKSTEP;
You should spool these results to a file, which can be sent to the database. The database, in turn, executes each SQL statement in the file, saving you many keystrokes and much time. The GRANT ENABLE TO USERNAME statement is repeated once for every user in the database.
The next time you are writing SQL statements and have repeated the same statement several times, allow your imagination to take hold, and let SQL do the work for you.
Direct Versus Embedded SQL
Direct SQL is where an SQL statement is executed from some form of an interactive terminal. The SQL results are returned directly to the terminal that issued the statement. Most of this book has focused on direct SQL. Direct SQL is also referred to as interactive invocation or direct invocation.
Embedded SQL is SQL code used within other programs, such as Pascal, Fortran, COBOL, and C. SQL code is actually embedded in a host programming language, as discussed previously, with a call-level interface. Embedded SQL statements in host programming language codes are commonly preceded by EXEC SQL and terminated by a semicolon. Other termination characters include END-EXEC and the right parenthesis.
The following is an example of embedded SQL in a host program, such as the ANSI C language:
{HOST PROGRAMMING COMMANDS}
EXEC SQL {SQL STATEMENT};
{MORE HOST PROGRAMMING COMMANDS}
Windowed Table Functions
Windowed table functions allow calculations to operate over a window of the table and return a value based upon that window. This allows for the calculation of values such as a running sum, ranks, and moving averages. The syntax for the table valued function follows:
ARGUMENT OVER ([PARTITION CLAUSE] [ORDER CLAUSE] [FRAME CLAUSE])
Almost all aggregate functions can act as windowed table functions. They provide five new windowed table functions:
• RANK() OVER
• DENSE_RANK() OVER
• PERCENT_RANK() OVER
• CUME_DIST() OVER
• ROW_NUMBER() OVER
Normally, it would be difficult to calculate something such as an individual’s ranking within his pay year. Windowed table function would make this calculation a little easier, as seen in the following example for Microsoft SQL Server:
SELECT EMP_ID, SALARY, RANK() OVER (PARTITION BY YEAR(DATE_HIRE)
ORDER BY SALARY DESC) AS RANK_IN_DEPT
FROM EMPLOYEE_PAY_TBL;
Not all RDBM implementations currently support windowed table functions, so it is best to check the documentation of your specific implementation.
Working with XML
The ANSI standard presented an XML-related features section in its 2003 version. Since then, most database implementations have tried to support at least part of the released feature set. For example, one part of the ANSI standard is to provide for the output of XML-formatted output from a query. SQL Server provides such a method by using the FOR XML statement, as shown in the following example:
SELECT EMP_ID, HIRE_DATE, SALARY FROM
EMPLOYEE_TBL FOR XML AUTO
Another important feature of the XML feature set is being able to retrieve information from an XML document or fragment. MySQL provides this functionality through the EXTRACTVALUE function. This function takes two arguments. The first is an XML fragment, and the second is the locator, which returns the first value of the tags matched by the string. The syntax is shown here:
ExtractValue([XML Fragment],[locator string])
The following is an example of using the function to extract the value in the node a:
SELECT EXTRACTVALUE('<a>Red<//a><b>Blue</b>','/a') as ColorValue;
ColorValue
Red
It is important to check with your individual database’s documentation to see exactly what XML support is provided. Some implementations, such as SQL Server and Oracle, have advanced functionality such as specific XML data types. For example, Oracle’s XMLTYPE provides a specific API to handle the most used functions with XML data, such as finding and extracting values. Microsoft SQL Server’s XML data type allows for the application of templates to ensure that the XML data input into the column is complete.
Summary
Some advanced SQL concepts were discussed in this hour. Although this hour did not go into a lot of detail, it did provide you with a basic understanding of how you can apply the fundamental concepts that you have learned up to this point. You start with cursors, which pass a data set selected by a query into a location in memory. After a cursor is declared in a program, you must open it for accessibility. Then the contents of the cursor are fetched into a variable, at which time the data can be used for program processing. The resultset for the cursor is contained in memory until the cursor is closed and the memory is deallocated.
Stored procedures and triggers were covered next. Stored procedures are basically SQL statements that are stored together in the database. These statements, along with other implementation-specific commands, are compiled in the database and are ready for a database user to execute at any given time. Stored procedures typically provide better performance benefits than individual SQL statements.
This chapter also discussed dynamic SQL, using SQL to generate other SQL statements, and the differences between direct SQL and embedded SQL. Dynamic SQL is SQL code that a user dynamically creates during runtime, unlike static SQL.
Lastly, we discussed Windowed Table Functions and XML. These features may not yet be supported in your database version because they are relatively new but are good to know for future reference. The concepts of some of the advanced topics discussed during this hour illustrate the application of SQL in an enterprise, covered in Hour 23, “Extending SQL to the Enterprise, the Internet, and the Intranet.”
Q&A
Q. Can a stored procedure call another stored procedure?
A. Yes. The stored procedure being called is referred to as being nested.
A. Simply use the OPEN CURSOR statement. This sends the results of the cursor to a staging area.
Workshop
The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
2. When a cursor is closed, can you reuse the name?
3. Which command retrieves the results after a cursor has been opened?
4. Are triggers executed before or after an INSERT, DELETE, or UPDATE statement?
5. Which MySQL function retrieves information from an XML fragment?
6. Why do Oracle and MySQL not support the DEALLOCATE syntax for cursors?
7. Why is a cursor not considered a set-based operation?
Exercises
1. Enter a command similar to the one that follows for MySQL to write out SQL statements to DESCRIBE each table in the database:
SELECT CONCAT('DESCRIBE ',TABLE_NAME,';') FROM TABLES_PRIV;
2. Write a SELECT statement that generates the SQL code to count all rows in each of your tables. (Hint: It is similar to Exercise 1.)
3. Write a series of SQL commands to create a cursor that prints each customer name and the customer’s total sales. Ensure that the cursor is properly closed and deallocated based on which implementation you are using.