CREATE COMMAND
Creates
a new stored command.
SYNTAX
| command_name |
Name
of the new stored command you want to create. |
| select_statement |
A valid
SELECT statement. |
| insert_statement |
A valid
INSERT statement. |
| update_statement |
A valid
UPDATE statement. |
| delete_statement |
A valid
DELETE statement. |
DESCRIPTION
The CREATE
COMMAND command creates a new stored command. You can use stored commands
to quickly and conveniently execute frequently used SQL data-manipulation
statements without entering them repeatedly. To execute the CREATE COMMAND
command, you must have RESOURCE or higher security privileges, and have
all security and object privileges necessary to execute the SQL statement
that defines the stored command.
A stored
command is an SQL data-manipulation statement that is compiled and permanently
stored in the database in executable format. This allows you to repeatedly
execute the stored command without waiting for DBMaker to compile and
optimize the command each time you use it. Stored commands are similar
to stored procedures, except they can only contain a single command and
cannot contain program logic.
When you
create a stored command you must specify the command name and a valid
SQL data-manipulation statement (SELECT, INSERT, UPDATE, or DELETE statement).
You can use host variables as placeholders for column values in the SQL
statement. This allows you to assign actual values to the column when
you execute the command, instead of when you create it. To use host variables
in a stored command, replace any data or column value with a question
mark symbol (?).
When you
execute a stored command that has host variables, you can use constants,
results from built-in functions, the NULL keyword, the DEFAULT keyword,
or another host variable. You can only use built-in functions that have
no argument, such as RAND(), PI(), CURDATE(), and NOW(), when providing
a value for a host variable. If you use a NULL value for the host variable,
the value represented by the host variable must be capable of accepting
NULL values. The number of parameters you provide when executing a stored
command must equal the number of host variables in the command definition.
If you drop
a table or a column that is referenced by a stored command, alter a table
and modify the column definition, or alter a table and add a column using
the BEFORE and AFTER keywords, the stored command becomes invalid and
cannot be used again. Altering a table and adding a column without using
the BEFORE and AFTER keywords will have no impact on a stored command.
You can drop an invalid stored command to remove it from the database.
Stored command
names must be unique in the database. Stored command names have a maximum
length of eighteen characters, and may contain numbers, letters, the underscore
character, and the symbols $ and #. The first character may not be a number.
EXAMPLE
The following
example creates a stored command named sc1. This stored command selects
all employees in the Employees table whose last name begins with the letter
'A'.
CREATE COMMAND sc1 AS SELECT * FROM Employees WHERE LastName LIKE 'A%'
|
The
following example creates a stored command named sc2 that uses host variables
to update the Manager column in the Employees table.
CREATE COMMAND sc2 AS UPDATE Employees SET Manager = ? WHERE Manager = ?
|
RELATED
COMMANDS
<
CONNECT | Contents
| CREATE DB >
|
