|
   
10.
Stored Commands
 10.1
Creating Stored Commands
10.2
Executing a Stored Command
10.3
Dropping a Stored Command
10.4
Stored Command Security
10.5
Lifecycle of a Stored Command
10. Stored Commands
A stored
command is a compiled SQL DML statement stored in the database. Since
a stored command is precompiled in an executable form, you can execute
the same command without compiling and optimizing again and again. It
is possible to create a stored command for any frequently used SQL statement.
You can achieve better performance using a stored command to execute
an SQL statement that is repeatedly executed. You can also view stored
commands as a subset of stored procedures that only contain one SQL
statement without any program logic.
10.1 Creating Stored Commands
You use
the CREATE COMMAND command to create a stored command.
CREATE COMMAND Syntax
The syntax
for CREATE COMMAND is:
Input Parameters
You can
use input parameters in the SQL statement when creating a stored command.
The actual value of these input parameters of stored command can be
assigned at the execution time. For example, if there is a table with
the definition t1 (c1 INT, c2 INT, c3 CHAR(32)), we can create
a stored command sc1 for the SQL DML statement:
INSERT INTO t1 VALUES (1, ?, ?)
|
Example
CREATE COMMAND sc1 AS INSERT INTO t1 VALUES (1, ?, ?)
|
After creating
the command sc1, you can execute it directly in dmSQL or in an
application program. If you execute a stored command which has input
parameters, you can decide its value using parameter mark, constant,
NULL, DEFAULT, or built-in function (built-in functions can't have arguments)
when executing the stored command. When you execute a stored command,
number of input parameters should be equal to number of input parameters
in the stored command.
10.2 Executing a Stored Command
You use
the EXECUTE COMMAND command to execute a stored command.
EXECUTE COMMAND Syntax
The syntax
of EXECUTE COMMAND is:
Examples
EXECUTE COMMAND sc1 (200, 'john')
EXECUTE COMMAND sc1 (DEFAULT, ?)
EXECUTE COMMAND sc1 (?, NULL)
EXECUTE COMMAND sc1 (?, ?)
|
You can
drop a stored command, when it's no longer useful.
10.3 Dropping a Stored Command
You can
use the DROP COMMAND command to drop a stored command.
DROP COMMAND Syntax
The syntax
of DROP COMMAND is:
Example
10.4 Stored Command Security
Stored
commands are treated similarly to other database schema objects, and
as a result you must consider security and object privileges when creating
or using a stored command.
Who Can Create a Stored Command?
A stored
command can only be created by users that have RESOURCE privilege. You
can only create a stored command from an SQL DML statement if you have
privilege to execute the SQL DML statement. You can only create your
own stored commands.
Who Can Execute a Stored Command?
You must
have execute privilege for a stored command to execute it. In order
to allow a stored command to be used by others, you can grant execute
privilege on a stored command to other database users. If you wish others
to execute a stored command at a later time, you can revoke execute
privilege of the stored command from them. However, you can only grant
or revoke execute privilege of stored commands if you have the privilege
to grant or revoke the stored commands.
You have
execute privilege on all stored commands in database if you are a DBA.
If you are the owner of a stored command, you have execute, grant, and
revoke privileges on the stored command. In the following examples,
we will use a database user usr1 as example.
Who Can Drop a Stored Command?
Only owner
of stored command can drop the stored command.
Granting execute privilege
The syntax
of GRANT EXECUTE privilege of COMMAND is:
Example
GRANT EXECUTE ON COMMAND sc1 TO usr1
|
Revoking execute privilege
The syntax
of REVOKE EXECUTE privilege of COMMAND is:
Example
REVOKE EXECUTE ON COMMAND sc1 FROM usr1
|
10.5 Lifecycle of a Stored Command
A stored
command will be invalid if one of the related tables in the stored command
is dropped or altered. This type of stored command cannot be used again.
If you have any program which was written previously using old column
information, it may cause unpredictable results at execution time.
|
