Scribd
Upload
555 views

OReilly - SQL.In Nutshell

This document provides information about altering stored procedures in different database systems. It begins with an overview of how the ALTER PROCEDURE statement allows changes to existing stored procedures in various ways depending on the database. The document then provides a table that indicates the level of support for ALTER PROCEDURE in SQL Server, MySQL, Oracle, and PostgreSQL. The remainder of the document discusses the syntax and use of ALTER PROCEDURE in more detail for each database system. It also provides examples of how ALTER PROCEDURE can be used to modify parameters of an existing stored procedure.

Uploaded by

Tiến Triển Đinh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
555 views

OReilly - SQL.In Nutshell

This document provides information about altering stored procedures in different database systems. It begins with an overview of how the ALTER PROCEDURE statement allows changes to existing stored procedures in various ways depending on the database. The document then provides a table that indicates the level of support for ALTER PROCEDURE in SQL Server, MySQL, Oracle, and PostgreSQL. The remainder of the document discusses the syntax and use of ALTER PROCEDURE in more detail for each database system. It also provides examples of how ALTER PROCEDURE can be used to modify parameters of an existing stored procedure.

Uploaded by

Tiến Triển Đinh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 226

sql_titlepg.

qxd 10/13/00 5:27 PM Page 1

SOLL
SO
IN A NUTSHELL
A Desktop Quick Reference
sql_titlepg.qxd 10/13/00 5:27 PM Page 2
sql_titlepg.qxd 10/13/00 5:27 PM Page 3

SOLL
SO
IN A NUTSHELL
A Desktop Quick Reference

Kevin Kline with Daniel Kline


Chapter 3Statements

CHAPTER 3

Statements
This chapter is the heart of : it is an alphabetical listing of SQL
commands with detailed explanations and examples. Each command and function
is identified in a master table as being “supported,” “supported with variations,”
“supported with limitations,” or “not supported,” for each of the four SQL dialects
covered in this book: SQL Server, MySQL, Oracle, and PostgreSQL. After a brief
description of the SQL99 standard, each vendor application is discussed briefly but
thoroughly, with supporting examples and sample coding.

When researching a command in this chapter, first read the introductory para-
graph, vendor support table, and the section on SQL99 syntax and description.
The reason for this is that any common features between all the implementations
of the command are discussed once under the SQL99 topic. Thus, reading directly
about a vendor’s implementation of a particular command may not describe every
aspect of that command, since some of its details may be covered in the opening
comments.

The following list offers useful tips for reading Table 3-1, as well as what each
abbreviation stands for. The sections that follow describe the table’s commands in
detail:
1. The first column contains the alphabetized SQL commands.
2. The SQL statement class for each command is indicated in the second left-
hand column.
3. The command’s implementation in SQL99 is indicated in the next column.

27
4. The subsequent columns list the vendor’s level of support:

The vendor supports the SQL99 standard for the particular command.

The vendor supports the SQL99 standard for the particular command,
using vendor-specific code or syntax.

The vendor supports some but not all of the functions specified by the
SQL99 standard for the particular command.

The vendor does not support the particular command according to the
SQL99 standard.
5. Remember that even if a specific SQL99 command is listed as “Not
Supported,” the vendor usually has alternative coding or syntax to enact the
same command or function. Therefore, be sure read the discussion and exam-
ples for each command later in this chapter.

SQL- Yes SWV NS SWV NS


schema
SQL- Yes SWV SWL SWV SWV
schema
SQL- No SWV NS SWV NS
schema
SQL- No SWV NS SWV NS
schema
SQL- Yes NS NS S S
control
SQL-data Yes S S NS S
SQL-data Yes S NS NS S
SQL-data Yes S NS S S

SQL- Yes SWV NS S S


transaction
SQL-data Yes SWV SWV S S

SQL- Yes SWL NS S NS


connection
SQL- No SWV S S SWV
schema
SQL- Yes SWV SWV SWV SWV
schema
SQL- Yes SWV SWV SWV SWV
schema
SQL- Yes S NS S NS
schema
SQL- Yes NS NS SWV NS
schema
SQL- Yes S NS S NS
schema
SQL- Yes SWV SWV SWV SWV
schema
SQL- Yes SWV NS SWV SWV
schema
SQL- Yes SWV NS SWV SWV
schema

Statements
SQL-data Yes S NS S S

SQL-data Yes SWV SWV S S


SQL- Yes SWL NS SWV NS
connection
SQL- Yes SWV SWV NS SWV
schema
SQL- Yes SWV SWV SWV SWV
schema
SQL- Yes SWV SWV SWV SWV
schema
SQL- Yes S NS S NS
schema
SQL- Yes NS NS SWV NS
schema
SQL- Yes SWV SWV SWV SWV
schema
SQL- Yes SWV NS SWV SWV
schema
SQL- Yes S NS S S
schema
SQL-data Yes S NS S SWV
SQL- Yes SWV SWV SWV SWV
schema
SQL- Yes SWV SWV S S
schema
SQL-data Yes S SWL NS (theta SWV
joins (theta
supp- joins
orted) supp-
orted)
SQL- Yes SWV SWV SWV SWV
schema
SQL- Yes S NS S S
schema
SQL- Yes SWV SWV SWV SWV
schema
SQL- Yes S S S S
control
SQL- Yes SWV SWV SWV SWV
schema
SQL- Yes SWV NS S S
transaction
SQL- Yes SWV NS S NS
transaction
SQL-data Yes SWV SWV SWV SWV
SQL- Yes SWL NS NS NS
connection
SQL- Yes NS NS SWV NS
session
SQL- Yes NS NS SWV NS
session
SQL- Yes SWV NS SWL S
session
SQL- Yes NS NS NS NS
transaction (supports (supports

SQL-data Yes S NS SWV S

SQL-data Yes SWV SWV SWV S

The statement allows changes to be made to an existing


stored procedure. Depending on the vendor, the kind and degree of change varies
widely.
In SQL Server, this statement alters a previously created procedure (using the
statement) but doesn’t change permissions or affect depen-
dent stored procedures or triggers.
In Oracle, this command simply recompiles a PL/SQL stored procedure, but does
not allow the code to be changed. Instead, use the Oracle command
to achieve the same functionality.

SQL Server Supported, with variations


MySQL Not supported
Oracle Supported, with variations
PostgreSQL Not supported
ALTER PROCEDURE procedure_name {CASCADE | RESTRICT}
[LANGUAGE | PARAMETER STYLE | <SQL data access> | <null clause behavior>
| DYNAMIC RESULT SETS | NAME]
[parameter datatype [,...n]
As discussed under , the , ,
SQL data access method (i.e., , , etc.), null clause behavior
(e.g., ), , and the procedure all
may be altered.
The command also may be used to alter the number or type
of input parameters.

Statements
ALTER PROC[EDURE] procedure_name [;number]
[ {@parameter datatype } [VARYING] [= default] [OUTPUT] ][,...n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[FOR REPLICATION]
AS
T-SQL Block
In SQL Server, this command allows the change of any existing parameters for the
previously created stored procedure. In effect, this command is just a shortcut
around issuing a statement, followed by a modified
statement. Such grants or permissions to the stored procedure do not
have to be reestablished. Review the command for a full
explanation of the syntax. This command may be executed on SQL Server by the
owner of the stored procedure or a member of the db_owner and ddl_admin fixed
database roles.

ALTER PROCEDURE [user.]procedure_name COMPILE [DEBUG];


In Oracle, the procedure or package name that needs to be compiled must be
provided. The keyword is required. The option
regenerates PL/SQL information. This command may be executed only by the
owner of the stored procedure or by those who have specific privileges to
.

This example using Microsoft SQL Server creates a procedure called get_next_br
that generates a unique CHAR(22) output string. Then, when the procedure must
be changed to retrieve unique INT output value, is used to
redefine the stored procedure:
-- A Microsoft SQL Server stored procedure
CREATE PROCEDURE get_next_nbr
@next_nbr CHAR(22) OUTPUT
AS
BEGIN
DECLARE @random_nbr INT
SELECT @random_nbr = RAND() * 1000000
SELECT @next_nbr =
RIGHT('000000' + CAST(ROUND(RAND(@random_nbr)*1000000,0))AS CHAR(6), 6) +
RIGHT('0000' + CAST(DATEPART (yy, GETDATE() ) AS CHAR(4)), 2) +
RIGHT('000' + CAST(DATEPART (dy, GETDATE() ) AS CHAR(3)), 3) +
RIGHT('00' + CAST(DATEPART (hh, GETDATE() ) AS CHAR(2)), 2) +
RIGHT('00' + CAST(DATEPART (mi, GETDATE() ) AS CHAR(2)), 2) +
RIGHT('00' + CAST(DATEPART (ss, GETDATE() ) AS CHAR(2)), 2) +
RIGHT('000' + CAST(DATEPART (ms, GETDATE() ) AS CHAR(3)), 3)
END
GO

ALTER PROCEDURE get_next_nbr


@next_nbr INT OUTPUT
AS
BEGIN
DECLARE @convert_to_nbr CHAR(22)
DECLARE @random_nbr INT
SELECT @random_nbr = RAND() * 1000000

SELECT @convert_to_nbr =
RIGHT('000000' + CAST(ROUND(RAND(@random_nbr)*1000000,0))AS CHAR(6), 6) +
RIGHT('0000' + CAST(DATEPART (yy, GETDATE() ) AS CHAR(4)), 2) +
RIGHT('000' + CAST(DATEPART (dy, GETDATE() ) AS CHAR(3)), 3) +
RIGHT('00' + CAST(DATEPART (hh, GETDATE() ) AS CHAR(2)), 2) +
RIGHT('00' + CAST(DATEPART (mi, GETDATE() ) AS CHAR(2)), 2) +
RIGHT('00' + CAST(DATEPART (ss, GETDATE() ) AS CHAR(2)), 2) +
RIGHT('000' + CAST(DATEPART (ms, GETDATE() ) AS CHAR(3)), 3)

SELECT @next_nbr = CAST(@convert_to_nbr AS INT)

END
GO

The statement allows an existing table to be modified without drop-


ping the table or altering existing permissions on the table. In this way, certain
incremental changes are performed easily on an existing table.
Both Oracle and Microsoft SQL Server support this command with a number of
variations to service their differing physical file-allocation methods.

SQL Server Supported, with variations


MySQL Supported, with limitations
Oracle Supported, with variations
PostgreSQL Supported, with variations

You might also like