1.Что такое хранимая процедура?
Хранимая процедура компилируется один раз и может быть запущена несколько раз (хранимая процедура хранится на сервере). Это предварительно скомпилированная коллекция, которая выполняется быстро.
2. Часто используемые системные хранимые процедуры
код
-- Цель: часто используемые системные хранимые процедуры.
EXEC sp_databases — список баз данных в текущей системе.
EXEC sp_renamedb 'test','test1' — изменить имя базы данных (однопользовательский доступ)
ИСПОЛЬЗОВАТЬ базу данных
ИДТИ
EXEC sp_tables — список объектов, запрошенных в текущей базе данных.
EXEC sp_columns stuInfo — возвращает информацию о столбце таблицы.
EXEC sp_help stuInfo — просмотр информации о таблице stuInfo.
EXEC sp_helpconstraint stuInfo — просмотр ограничений таблицы stuInfo.
EXEC sp_helpindex stuMarks — просмотр индекса таблицы stuMarks.
EXEC sp_helptext 'view_stuInfo_stuMarks' — просмотр текста оператора представления.
EXEC sp_stored_procedures — возвращает список хранимых процедур в текущей базе данных.
использовать мастер
идти
exec xp_cmdshell 'mkdir D:bank',no_output — создать папку
3. Пользовательские хранимые процедуры
(1.) Хранимая процедура без параметров
код
использовать стадб
идти
если существует (выберите * из sysobjects, где name='proc_stu')
удалить процесс proc_stu
идти
создать процедуру proc_stu
КАК
DECLARE @writingAvg float, @labAvg float — переменная среднего балла письменного теста и компьютерного теста.
ВЫБЕРИТЕ @writingAvg=AVG(writeExam), @labAvg=AVG(labExam) ИЗ stuMarks
print 'Средний балл по письменному тесту:'+convert(varchar(5),@writingAvg)
print 'Средний балл компьютерного теста:'+convert(varchar(5),@labAvg)
ЕСЛИ (@writingAvg>70 И @labAvg>70)
распечатать «Результаты экзамена этого класса: отлично»
ЕЩЕ
print 'Результаты экзамена этого класса: плохие'
распечатать '------------------------------------------------ --'
print 'Студенты, не сдавшие этот экзамен:'
ВЫБЕРИТЕ stuName,stuInfo.stuNo,writingExam,labExam FROM stuInfo
ВНУТРЕННЕЕ СОЕДИНЕНИЕ stuMarks ON stuInfo.stuNo=stuMarks.stuNo
ГДЕ написаноЭкзамен<60 ИЛИ ЛабораторныйЭкзамен<60
ИДТИ
exec proc_stu --Выполнить хранимые процедуры (2.) Хранимые процедуры с входными параметрами
код
ИСПОЛЬЗОВАТЬ базу данных
ИДТИ
/*---Определить, существует ли она: хранимая процедура хранится в системной таблице sysobjects---*/
ЕСЛИ СУЩЕСТВУЕТ (ВЫБРАТЬ * ИЗ sysobjects WHERE name = 'proc_stu')
ПРОЦЕДУРА УДАЛЕНИЯ proc_stu
ИДТИ
/*---Создание хранимой процедуры----*/
СОЗДАТЬ ПРОЦЕДУРУ proc_stu
@writingPass int,
@labPass int — вы можете добавить значение по умолчанию, чтобы выполнение было похоже на это exec proc_stu без указания параметров.
КАК
print 'Письменный проходной балл:'+convert(varchar(5),@writingPass)
print 'Строка прохождения компьютерного теста:'+convert(varchar(5),@labPass)
распечатать '------------------------------------------------ --'
print 'Студенты, не сдавшие этот экзамен:'
ВЫБЕРИТЕ stuName,stuInfo.stuNo,writingExam,labExam FROM stuInfo
ВНУТРЕННЕЕ СОЕДИНЕНИЕ stuMarks ON stuInfo.stuNo=stuMarks.stuNo
ГДЕ writeExam<@writerPass ИЛИ labExam<@labPass
ИДТИ
exec proc_stu 60,55
exec proc_stu 60,@labPass=55
exec proc_stu @writingPass=60,@labPass=55
exec proc_stu @writingPass=60,55 — Эта строка сообщит об ошибке: необходимо передать параметр 2, а последующие параметры необходимо передать в форме «@name = value».
-- После использования формы @name = value все последующие параметры необходимо передавать в форме @name = value.
(3.) Хранимая процедура с выходными параметрами
код
ИСПОЛЬЗОВАТЬ базу данных
ИДТИ
/*---Определить, существует ли она: хранимая процедура хранится в системной таблице sysobjects---*/
ЕСЛИ СУЩЕСТВУЕТ (ВЫБРАТЬ * ИЗ sysobjects WHERE name = 'proc_stu')
ПРОЦЕДУРА УДАЛЕНИЯ proc_stu
ИДТИ
/*---Создание хранимой процедуры----*/
СОЗДАТЬ ПРОЦЕДУРУ proc_stu
@notpassSum int OUTPUT, ключевое слово --OUTPUT, в противном случае оно рассматривается как входной параметр
@writingPass int=60, --Параметры по умолчанию ставятся после
@labPass int=60 --Параметры по умолчанию помещаются после
КАК
print 'Письменный проходной балл:'+convert(varchar(5),@writingPass)
+ 'Линия прохождения компьютерного теста:'+convert(varchar(5),@labPass)
распечатать '------------------------------------------------ --'
print 'Студенты, не сдавшие этот экзамен:'
ВЫБЕРИТЕ stuName,stuInfo.stuNo,writingExam,labExam FROM stuInfo
ВНУТРЕННЕЕ СОЕДИНЕНИЕ stuMarks ON stuInfo.stuNo=stuMarks.stuNo
ГДЕ writeExam<@writerPass ИЛИ labExam<@labPass
/*--Подсчитаем и вернем количество студентов, проваливших экзамен--*/
SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
ГДЕ writeExam<@writerPass ИЛИ labExam<@labPass
ИДТИ
/*---Вызов хранимой процедуры----*/
ОБЪЯВИТЬ @sum int
EXEC proc_stu @sum OUTPUT,64 — Проходной балл компьютерного теста принимает значение по умолчанию: проходной балл письменного теста составляет 64 балла, а проходной балл компьютерного теста — 60 баллов.
распечатать '------------------------------------------------ --'
ЕСЛИ @сумма>=3
print 'Количество людей, не прошедших проверку:'+convert(varchar(5),@sum)+ 'Люди, более 60%, проходной балл следует снизить'
ЕЩЕ
print 'Количество людей, которые потерпели неудачу:'+convert(varchar(5),@sum)+ 'Люди, контрольный показатель ниже 60%, проходной балл средний'
ИДТИ