THE RELATIONAL MODEL 3: ADVANCED TOPICS - Concepts of Database Management - page 142

NOTE
In MySQL, you use the SHOW TABLES command to produce a list of all tables in the current database. The SHOW INDEX
command produces a list of all indexes. The SHOW COLUMNS command, which consists of the words SHOW COLUMNS

FROM followed by the name of a table, produces details concerning all columns in the indicated table. Running the command
SHOW COLUMNS FROM CUSTOMER, for example, lists details concerning all the columns in the Customer table.

142

NOTE
In Oracle, the equivalent tables for SYSTABLES, SYSCOLUMNS, and SYSVIEWS are named DBA_TABLES,
DBA_TAB_COLUMNS, and DBA_VIEWS, respectively.

In other cases, special tools provide the desired documentation. For example, Access has a tool called the
Documenter, which allows you to print detailed documentation about any table, query, report, form, or other
object in the database. To document the objects in an Access database, click the Database Tools tab, and
then click the Database Documenter button in the Analyze group.

STORED PROCEDURES

In a client/server system, the database resides on a computer called the server and users access the database
through clients. A client is a computer that is connected to a network and has access through the server to
the database. Every time a user executes a query, the DBMS must determine the best way to process the
query and provide the results. For example, the DBMS must determine which indexes are available and
whether it can use those indexes to make the processing of the query more efficient.
If you anticipate running a particular query often, for example, you can improve overall performance by
saving the query in a special file called a stored procedure. The stored procedure is placed on the server.
The DBMS compiles the stored procedure (translating it into machine code) and creates an execution plan,
which is the most efficient way of obtaining the results. From that point on, users execute the compiled,
optimized code in the stored procedure.
Another reason for saving a query as a stored procedure, even when you are not working in a client/
server system, is convenience. Rather than retyping the entire query each time you need it, you can use the
stored procedure. For example, suppose you frequently execute a query to change a customer's credit limit.
You can use the same query to select the record using the customer s number and to change the credit limit.

Instead of running the query each time and changing the customer number and the credit limit, it would be
simpler to store the query in a stored procedure. When you run the stored procedure, you need to enter only
the appropriate customer number and the new credit limit.
Although it is easy to create and use a stored procedure, you need to be aware of one issue you will face
when creating a stored procedure in MySQL. The semicolon in a MySQL command marks the end of the
command. In this context, the semicolon is called a delimiter. When MySQL encounters a semicolon in a
command, it executes the command. Because a semicolon also indicates the end of a stored procedure, you
need to change the delimiter temporarily for the stored procedure. In the following example, the first line
temporarily changes the delimiter to a double dollar sign ($$). The last line in the command changes the
delimiter back to a semicolon. (Notice that there is a space between the word DELIMITER and the
semicolon.)

'

DELIMITER $$
CREATE PROCEDURE Change_Credit
(CNum CHAR(4), CLimit DECIMAL (8,2))
BEGIN
UPDATE Customer
SET CreditLimit ¼ CLimit
WHERE CustomerNum ¼ CNum ;
END
$$
DELIMITER ;