Есть много статей об ASP и хранимых процедурах (Stored Treatments), но я сомневаюсь, что авторы действительно их практиковали. Когда я был новичком, я просмотрел много соответствующей информации и обнаружил, что многие из предложенных методов на практике не совпадают. Для простых приложений эти материалы могут быть полезны, но этим они и ограничиваются, поскольку по сути они одинаковы и копируют друг друга. Для чуть более сложных приложений все они неясны.
Теперь я в основном получаю доступ к SQL Server, вызывая хранимые процедуры. Следующий текст представляет собой краткое изложение практики. Надеюсь, он будет полезен всем.
Хранимая процедура — это одна или несколько команд SQL, хранящихся в базе данных в виде исполняемых объектов.
Определения всегда абстрактны. Хранимая процедура на самом деле представляет собой набор операторов SQL, которые могут выполнять определенные операции, но этот набор операторов размещается в базе данных (здесь мы говорим только о SQL Server). Если мы создаем хранимые процедуры и вызываем хранимые процедуры в ASP, мы можем избежать смешивания операторов SQL с кодом ASP. У этого есть как минимум три преимущества.
Во-первых, это значительно повышает эффективность. Скорость выполнения самой хранимой процедуры очень высока, а вызов хранимой процедуры может значительно сократить количество взаимодействий с базой данных.
Во-вторых, повысить безопасность. Если вы смешиваете операторы SQL в коде ASP, то если код будет скомпрометирован, это также означает, что структура библиотеки будет скомпрометирована.
В-третьих, это способствует повторному использованию операторов SQL.
В ASP хранимые процедуры обычно вызываются через объект команды. В зависимости от ситуации в этой статье также представлены другие методы вызова. Для удобства объяснения на основе входных и выходных данных хранимой процедуры сделана следующая простая классификация:
1. Хранимая процедура, которая возвращает только один набор записей,
предполагает следующую хранимую процедуру (цель этой статьи не состоит в описании Синтаксис T-SQL, поэтому хранимая процедура выдает только код, без объяснений):
/*SP1*/
CREATE PROCEDURE dbo.getUserList
as
set nocount on
start
select * from dbo.[userinfo]
end
go
Вышеупомянутая хранимая процедура получает все записи в таблице userinfo и возвращает набор записей. Код ASP для вызова хранимой процедуры через объект команды следующий:
'**Вызов хранимой процедуры через объект команды**
DIM MyComm,MyRst
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr — это соединение с базой данных. String
MyComm.CommandText = "getUserList" 'Укажите имя хранимой процедуры
MyComm.CommandType = 4 'Укажите, что это хранимая процедура
MyComm.Prepared = true 'Требуйте, чтобы сначала была скомпилирована команда SQL
. Установите MyRst = MyComm.Execute
Set MyComm = Ничего
хранимая процедура Полученный набор записей присваивается MyRst. Далее MyRst можно использовать.
В приведенном выше коде атрибут CommandType указывает тип запроса. Значение и описание следующие:
-1 указывает, что тип параметра CommandText не может быть определен.
1 указывает, что параметр CommandText является общим типом команды.
2 указывает, что Параметр CommandText — это имя существующей таблицы.
4 указывает, что параметр CommandText — имя хранимой процедуры.
Можно также вызвать хранимую процедуру через объект Connection или объект набора записей. Методы следующие:
'**Вызов хранимой процедуры через Connection. object**
DIM MyConn,MyRst
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn .open MyConStr 'MyConStr — строка подключения к базе данных
Set MyRst = MyConn.Execute("getUserList",0,4) 'Последний параметр имеет то же значение, что и CommandType
Set MyConn = Nothing
'**Вызов хранимой процедуры через объект Recordset**
DIM MyRst
Set MyRst = Server.CreateObject("ADODB.Recordset")
MyRst.open "getUserList",MyConStr,0,1 ,4
'MyConStr — это строка подключения к базе данных, последний параметр имеет то же значение, что и CommandType
2. Нет хранилища ввода и вывода. См. следующую хранимую процедуру для процесса
:
/*SP2*/
CREATE PROCEDURE dbo.delUserAll
как
установлено nocount on
Begin
delete from dbo.[userinfo]
end
go
Эта хранимая процедура удаляет все записи в таблице userinfo без каких-либо входных или выходных данных. Вызов. Метод в основном аналогичен упомянутому выше, за исключением того, что нет необходимости получать набор записей. :
'**Вызов хранимой процедуры через объект Command**
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr — это строка подключения к базе данных
MyComm.CommandText = "delUserAll" 'Укажите имя хранимой процедуры
MyComm.CommandType = 4 'Указывает, что это хранимая процедура
MyComm.Prepared = true 'Требовать, чтобы сначала была скомпилирована команда SQL
MyComm.Execute 'Нет необходимости получать набор записей здесь
Set MyComm = Nothing
Конечно, такие хранимые процедуры также можно вызывать через объект Connection или объект Recordset, но объект Recordset создается для получения набора записей. Если набор записей не возвращается, лучше использовать объект Command.
3.При выполнении операций, аналогичных SP2,
хранимые процедуры с возвращаемыми значениями
должны в полной мере использовать мощные возможности обработки транзакций SQL Server для обеспечения согласованности данных.Более того, нам может понадобиться хранимая процедура для возврата статуса выполнения. Для этого измените SP2 следующим образом:
/*SP3*/
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
start
BEGIN TRANSACTION
delete from dbo.[userinfo]
IF error=. 0
начало
COMMIT TRANSACTION
return 1
конец
ELSE
начало
ROLLBACK TRANSACTION
return 0
end
return
end
go
Вышеупомянутая хранимая процедура возвращает 1, если удаление выполнено успешно, в противном случае она возвращает 0 и выполняет операцию отката. Чтобы получить возвращаемое значение в ASP, вам необходимо использовать коллекцию параметров для объявления параметров:
'**Вызовите хранимую процедуру с возвращаемым значением и получите возвращаемое значение**
DIM MyComm,MyPara
Set MyComm = Server.CreateObject («ADODB.Command»)
MyComm.ActiveConnection = MyConStr 'MyConStr — это строка подключения к базе данных
MyComm.CommandText = «delUserAll» 'Укажите имя хранимой процедуры
MyComm.CommandType = 4 'Указывает, что это хранимая процедура
MyComm.Prepared = true 'Требуется, чтобы сначала были скомпилированы команды SQL
'. Возвращаемое значение
Set Mypara = MyComm.CreateParameter("RETURN",2,4)
MyComm.Parameters.Append MyPara
MyComm.Execute
'Получите возвращаемое значение
DIM retValue
retValue = MyComm(0) ' или retValue = MyComm.Parameters(0)
Set MyComm = Nothing
В MyComm.CreateParameter("RETURN",2,4) значение каждого параметра следующее:
Первый параметр ("RETURE") — это имя параметра. Имя параметра можно задать произвольно, но обычно оно должно совпадать с именем параметра, объявленным в хранимой процедуре. Вот возвращаемое значение, которое я обычно устанавливаю как «RETURE»;
второй параметр (2) указывает тип данных параметра. Для получения информации о конкретных кодах типов обратитесь к справочнику по ADO. Ниже приведен часто используемый код типа.
adBigInt: 20 ;
adBoolean:
11 ; adDBTimeStamp
: 135
;
adInteger
: 3
;
adTinyInt: 16 ;
adVarChar: 200 ;
ценности
можно взять, а от -1 до -99 — зарезервированное значение,
третий параметр (4) указывает тип параметра, где 4 указывает, что это возвращаемое значение. Описание значения этого параметра следующее:
0: тип не может быть определен; 1: входной параметр; 2: входной параметр; 3: входной или выходной параметр; 4: возвращаемое значение.
Код ASP, указанный выше, должен быть указан. быть полным кодом. Это также самый сложный код. Фактически,
Set Mypara = MyComm.CreateParameter("RETURN",2,4)
MyComm.Parameters.Append MyPara
можно упростить до
MyComm.Parameters.Append MyComm.CreateParameter( «ВОЗВРАТ», 2,4)
или даже можно продолжать упрощать, о чем будет сказано позже.
Для хранимых процедур с параметрами их можно вызвать только с помощью объекта Command (есть также информация, что их можно вызвать через объект Connection или объект Recordset, но я не пробовал).
4. Возвращаемое значение хранимой процедуры с входными и выходными параметрами
на самом деле является специальным выходным параметром. В большинстве случаев мы используем хранимые процедуры, которые имеют как входные, так и выходные параметры. Например, мы хотим получить имя пользователя с определенным идентификатором в таблице информации о пользователе. В этот момент есть входной параметр. --user ID и выходной параметр ----имя пользователя. Хранимая процедура для реализации этой функции выглядит следующим образом:
/*SP4*/
CREATE PROCEDURE dbo.getUserName
@UserID int,
@UserName varchar(40) выводится
как
set nocount в
начале
, если @UserID имеет значение null return
select @UserName=имя пользователя
из dbo .[ userinfo]
где userid=@UserID
return
end
go
Код ASP для вызова хранимой процедуры следующий:
'**Вызов хранимой процедуры с входными и выходными параметрами**
DIM MyComm,UserID,UserName
UserID = 1
Set MyComm = Server.CreateObject(" ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr — это строка подключения к базе данных
MyComm.CommandText = "getUserName" 'Укажите имя хранимой процедуры
MyComm.CommandType = 4 'Указывает, что это хранимая процедура
MyComm. Readed = true 'Требуется SQL. Сначала скомпилируйте команду
и объявите параметры
MyComm.Parameters.append MyComm.CreateParameter("@UserID",3,1,4,UserID)
MyComm.Parameters.append MyComm.CreateParameter("@UserName", 200,2,40)
MyComm. Execute
'Получить параметры
UserName = MyComm(1)
Установить MyComm = Nothing
В приведенном выше коде видно, что в отличие от объявления возвращаемого значения при объявлении входных параметров требуется 5 параметров, а также 4. параметры необходимы при объявлении выходных параметров. При объявлении входных параметров пятью параметрами являются: имя параметра, тип данных параметра, тип параметра, длина данных и значение параметра. При объявлении выходного параметра нет последнего параметра: значения параметра.
Особое внимание следует обратить на то: при объявлении параметров порядок должен быть таким же, как определенный в хранимой процедуре, а тип данных и длина каждого параметра также должны быть такими же, как определенные в хранимой процедуре.
Если хранимая процедура имеет несколько параметров, код ASP будет выглядеть громоздким. Для упрощения кода можно использовать команду with:
'**Вызов хранимой процедуры с входными и выходными параметрами (упрощенный код)**
DIM MyComm,UserID,UserName.
UserID = 1
Установите MyComm = Server.CreateObject("ADODB.Command")
с MyComm
.ActiveConnection = MyConStr 'MyConStr — это строка подключения к базе данных.CommandText
= "getUserName" 'Укажите имя хранимой процедуры.CommandType
= 4 'Указывает, что это хранимая процедура.Prepared
= true 'Требовать, чтобы сначала была скомпилирована команда SQL.Parameters.append
.CreateParameter("@UserID",3,1,4,UserID)
.Parameters.append .CreateParameter("@UserName",200, 2,40)
.Выполнить
завершение с помощью
UserName = MyComm(1)
Установить MyComm = Nothing
Если мы хотим получить имена 10 пользователей с идентификаторами от 1 до 10, нужно ли нам создавать объект Command 10 раз? Нет. Если вам нужно вызвать одну и ту же хранимую процедуру несколько раз, просто измените входные параметры, и вы получите разные выходные данные:
'**Несколько вызовов одной и той же хранимой процедуры**
DIM MyComm,UserID,UserName
UserName = ""
Set MyComm = Server .CreateObject( "ADODB.Command")
для UserID = от 1 до 10
с MyComm
.ActiveConnection = MyConStr 'MyConStr — это строка подключения к базе данных.CommandText
= "getUserName" 'Укажите имя хранимой процедуры.CommandType
= 4 'Указывает, что это хранимая процедура.Prepared
= true 'Требуется, чтобы сначала скомпилировались команды SQL
, если UserID = 1, а затем
.Parameters.append .CreateParameter("@UserID",3,1,4,UserID)
.Parameters.append .CreateParameter("@UserName" ,200,2,40 )
.Execute
else
'Переназначить входные параметры (входные параметры и выходные параметры, значения параметров которых в данный момент не изменяются, не нужно переобъявлять)
.Parameters("@UserID") = UserID
.Execute
end if
end с
UserName = UserName + MyComm( 1) + "," 'Возможно, вам нравится использовать массив для хранения
следующего
Set MyComm = Nothing.
Каквы можете видеть из приведенного выше кода: при вызове одной и той же хранимой процедуры. повторно, вам нужно только переназначить входные параметры, значения которых изменились. Этот метод. Когда имеется несколько входных и выходных параметров, и значение только одного входного параметра изменяется при каждом его вызове, объем кода может быть равен. сильно уменьшено.
5. Хранимые процедуры, которые одновременно имеют возвращаемые значения, входные и выходные параметры. Как
упоминалось ранее, при вызове хранимой процедуры порядок объявления параметров должен быть таким же, как порядок, определенный в хранимой процедуре. . Еще один момент, на который следует обратить особое внимание: если хранимая процедура имеет как возвращаемое значение, так и входные и выходные параметры, то возвращаемое значение должно быть объявлено первым.
Чтобы продемонстрировать вызывающий метод в этом случае, давайте улучшим приведенный выше пример. По-прежнему получаем имя пользователя с идентификатором 1, но возможно, что пользователь не существует (пользователь удален, а userid — самоувеличяющееся поле). Хранимая процедура возвращает разные значения в зависимости от того, существует пользователь или нет. На данный момент хранимая процедура и код ASP выглядят следующим образом:
/*SP5*/
CREATE PROCEDURE dbo.getUserName
--Чтобы усилить впечатление «последовательности», измените порядок определения следующих двух параметров
@UserName varchar( 40) вывод,
@UserID int
as
set nocount on
start
, если @UserID равен нулю, return
select @UserName=username
from dbo.[userinfo]
где userid=@UserID,
если rowcount> 0
return 1
else
return 0
return
end
go
'**The вызов имеет как возвращаемое значение, так и входные. Хранимые процедуры для параметров и выходных параметров**
DIM MyComm,UserID,UserName
UserID = 1
Установите MyComm = Server.CreateObject("ADODB.Command")
с помощью MyComm
.ActiveConnection = MyConStr 'MyConStr — подключение к базе данных string.CommandText
= "getUserName" 'Укажите имя хранимой процедуры.CommandType
= 4 'Указывает, что это хранимая процедура
. Readed = true 'Требуется сначала скомпилировать команду SQL
' Сначала должно быть объявлено возвращаемое значение.
.CreateParameter("RETURN",2,4)
'Порядок объявления следующих двух параметров также соответственно меняется на обратный.Parameters.append
.CreateParameter("@UserName",200,2,40)
.Parameters.append .CreateParameter(" @UserID",3,1,4,UserID)
. Выполнение
завершается
if MyComm(0) = 1 then
UserName = MyComm(1)
else
UserName = "Этот пользователь не существует"
end if
Set MyComm = Nothing
6. Хранимые процедуры которые возвращают параметры и наборы записей одновременно
. Иногда нам нужны хранимые процедуры. Возвращайте параметры и наборы записей одновременно. Например, при использовании хранимых процедур для разбиения по страницам такие параметры, как наборы записей и общий объем данных, должны быть возвращены одновременно. время. Ниже приведена хранимая процедура для обработки страниц:
/*SP6*/
CREATE PROCEDURE dbo.getUserList
@iPageCount int OUTPUT, --Общее количество страниц
@iPage int, --Номер текущей страницы
@iPageSize int --Количество записей на странице
как
установить nocount в
начале
--Создать временную таблицу
создать таблицу #t (ID int IDENTITY, --автоматическое приращение поля
userid int,
имя пользователя varchar(40))
--записать данные во временную таблицу
вставить в #t
выбрать идентификатор пользователя, имя пользователя из dbo.[UserInfo]
упорядочить по идентификатору пользователя
--Получить общее количество записей.
Объявление @iRecordCount int
set @iRecordCount = rowcount
--Определить общее количество страниц
IF @iRecordCount%@iPageSize=0
SET @iPageCount=CEILING(@) iRecordCount/@iPageSize)
ELSE
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1
--Если номер запрошенной страницы превышает общее количество страниц, отобразить последнюю страницу
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount
--Определить начальную и конечную записи текущей страницы
DECLARE @iStart int --start Record
DECLARE @iEnd int --end Record
SELECT @iStart = (@iPage - 1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1
--Получить текущую запись страницы
select * from #t где ID> @iStart и ID <@iEnd
--Удалить временную таблицу
DROP TABLE #t
--Вернуть общее количество записей
return @iRecordCount
end
goIn
the выше хранимой процедуры, введите номер текущей страницы и количество записей на странице и верните текущую страницу. Набор записей, общее количество страниц и общее количество записей. Чтобы быть более типичным, общее количество записей возвращается в качестве возвращаемого значения. Ниже приведен код ASP, вызывающий хранимую процедуру (конкретная операция подкачки опущена):
'**Вызов хранимой процедуры подкачки**
DIM pagenow,pagesize,pagecount,recordcount
DIM MyComm,MyRst
pagenow = Request("pn")
'Пользовательская Функция используется для проверки натуральных чисел
, если CheckNar(pagenow) = false, то pagenow = 1
pagesize = 20
Установить MyComm = Server.CreateObject("ADODB.Command")
с помощью MyComm
.ActiveConnection = MyConStr 'MyConStr — строка подключения к базе данных .CommandText
= "getUserList" 'Укажите имя хранимой процедуры.CommandType
= 4 'Указывает, что это хранимая процедура
. Readed = true 'Требуется сначала скомпилировать команды SQL
' Возвращаемое значение (общее количество записей).
.Parameters.Append. CreateParameter("RETURN",2,4 )
'Выходные параметры (общее количество страниц)
.Parameters.Append .CreateParameter("@iPageCount",3,2)
'Входные параметры (текущий номер страницы)
.Parameters.append .CreateParameter( "@iPage",3,1,4 ,pagenow)
'Входные параметры (количество записей на странице)
.Parameters.append .CreateParameter("@iPageSize",3,1,4,pagesize)
Set MyRst = .Execute
end with
if MyRst.state = 0 then 'Данные не получены, MyRst close
Recordcount = -1
else
MyRst.close 'Примечание. Чтобы получить значение параметра, необходимо сначала закрыть объект набора записей
Recordcount = MyComm(0)
pagecount = MyComm( 1)
if cint(pagenow)> =cint(pagecount) then pagenow=pagecount
end if
Set MyComm = Nothing
'Следующее отображает запись,
если Recordcount = 0, то
Response.Напишите «Нет записи»,
elseif Recordcount > 0, тогда
MyRst.open
do до MyRst.EOF
......
цикл
'Следующее отображает информацию о подкачке
...
else 'recordcount=-1
Response.Write "Ошибка параметра"
end if
Что касается приведенного выше кода, есть только одна точка, которую необходимо Объяснено: при одновременном возврате набора записей и параметров, если вы хотите получить параметры, вам необходимо сначала закрыть набор записей, а затем открыть набор записей при его использовании.
7. Хранимые процедуры, возвращающие несколько наборов записей.
Первое, что рассказывается в этой статье, — это хранимые процедуры, возвращающие наборы записей. Иногда для возврата нескольких наборов записей требуется хранимая процедура. Как в ASP получить эти наборы записей одновременно? Чтобы проиллюстрировать эту проблему, добавьте в таблицу userinfo два поля: usertel и usermail и установите, чтобы только вошедшие в систему пользователи могли просматривать эти два содержимого.
/*SP7*/
CREATE PROCEDURE dbo.getUserInfo
@userid int,
бит @checklogin
as
set nocount on
start,
если @userid имеет значение null или @checklogin имеет значение null return
select username
from dbo.[usrinfo]
где userid=@userid
--if Log в user получите usertel и usermail,
если @checklogin=1
выберите usertel,usermail
из dbo.[userinfo]
где userid=@userid
return
end
go.
Ниже приведен код ASP:
'**Вызов хранимой процедуры, которая возвращает несколько наборов записей**
DIM checklg,UserID,UserName,UserTel,UserMail
DIM MyComm,MyRst
UserID = 1
'checklogin() — это пользовательская функция, определяющая, вошел ли посетитель в систему.
checklg = checklogin()
Set MyComm = Server.CreateObject("ADODB.Command" )
с MyComm
.ActiveConnection = MyConStr 'MyConStr — это строка подключения к базе данных.CommandText
= "getUserInfo" 'Укажите имя хранимой процедуры.CommandType
= 4 'Указывает, что это хранимая процедура.Prepared
= true 'Требуется скомпилировать команду SQL first.Parameters.append
.CreateParameter ("@userid",3,1,4,UserID)
.Parameters.append .CreateParameter("@checklogin",11,1,1,checklg)
Set MyRst = .Выполните
завершение с помощью
Set MyComm = Nothing
'Из первого. Получите значение из набора записей
UserName = MyRst(0)
'Получите значение из второго набора записей.
Если MyRst не равен Nothing, то
Set MyRst = MyRst.NextRecordset()
UserTel = MyRst(0)
UserMail = MyRst(1)
завершается, если
Set MyRst = Nothing
В приведенном выше коде метод NextRecordset объекта Recordset используется для получения нескольких наборов записей, возвращаемых хранимой процедурой.
До сих пор в этой статье давалось относительно полное объяснение различных ситуаций, в которых ASP вызывает хранимые процедуры. Наконец, давайте поговорим о различных методах вызова нескольких хранимых процедур в программе ASP.
В программе ASP возможно вызвать несколько хранимых процедур как минимум тремя следующими способами:
1. Создать несколько объектов Command
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
'Вызов хранимой процедуры один
... . ...
Set MyComm = Nothing
Set MyComm = Server.CreateObject("ADODB.Command")
'Вызов второй хранимой процедуры
...
Set MyComm = Nothing
...
2. Создайте только один объект Command. При завершении вызова очистите его параметры
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
'Вызов хранимой процедуры один
...
'Очистить параметры (при условии, что имеется три параметра)
MyComm.Parameters.delete 2
MyComm.delete 1
MyComm.Parameters. delete 0
'Вызов второй хранимой процедуры и очистка параметров
...
Set MyComm = Nothing
. Обратите внимание: порядок очистки параметров противоположен порядку объявления параметров. Причина в том, что я не знаю. .
3. Используйте метод Refresh коллекции данных параметров, чтобы сбросить объект параметров
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
'Вызов хранимой процедуры один
...
'Сброс всех объектов параметров, содержащихся в коллекции данных параметров.
MyComm.Parameters.Refresh
'Вызвать хранимую процедуру 2
...
Установить MyComm = Nothing
Обычно считается, что повторное создание объектов — менее эффективный метод, но после тестирования (инструмент тестирования — Microsoft Application Center Test) результаты оказались неожиданными:
Метод 2 > = Метод 1 >> Метод 3
Скорость работы метода 2 больше или равна скорости метода 1 (приблизительно на 4 %) выше, чем скорость метода 3 (до 130). %), поэтому рекомендуется: При наличии большого количества параметров используется метод 1, при небольшом количестве параметров — метод 2;
Мне потребовался день, чтобы наконец описать свой поверхностный опыт вызова хранимых процедур в ASP. Среди них у некоторых я знаю только следствия, но не причины, а некоторые могут быть неправильными, но все они получены на основе моей личной практики. Пожалуйста, отнеситесь к этому критически, читатели. Если у вас есть другое мнение, пожалуйста, дайте мне знать. Заранее спасибо.