-
Создание процедур
-
Создайте хранимую процедуру, которая для указанной таблицы подсчитывает число строк, содержащихся в этой таблице. Эта процедура должна иметь один входной параметр (с помощью которого задается имя таблицы) и один выходной параметр.
/*Создание хранимой процедуры подсчёта числа строк в указанной таблице*/
CREATE PROCEDURE pr_count
@table VARCHAR(20),
@count INT OUTPUT
AS
DECLARE @sql NVARCHAR(255)
SET @sql = 'SELECT @count = COUNT(*) FROM ' + @table
exec sp_executesql @sql, N'@count int out', @count=@count out
GO
/*Проверка хранимой процедуры подсчёта числа строк в указанной таблице*/
DECLARE @Count INT, @Table VARCHAR(20)
SET @Table = 'студент'
EXECUTE pr_count @Table, @Count OUTPUT
SELECT @Table AS [Название таблицы],
@Count AS [Количество записей в таблице]
GO
Рисунок 4 – Результат работы
-
Создайте хранимую процедуру, которая изменяет все даты, имеющиеся в таблицах базы данных, добавляя к ним или отнимая от них некоторое число дней. Эта процедура должна иметь два входных параметра. Первый параметр определяет, нужно ли увеличивать или уменьшать все даты. Второй параметр указывает количество дней, которые должны добавляться или отниматься.
Примечание. Если в таблицах отсутствуют даты, то аналогичную операцию необходимо проделать применительно ко всем имеющимся ценам и стоимостям. При этом второй параметр будет задавать процент, на который должны измениться все цены и стоимости.
/*Создание хранимой процедуры изменения всех дат*/
CREATE PROCEDURE pr_date
@pom SMALLINT, --plus or minus
@date SMALLINT
AS
UPDATE студент SET ДатаРождения = ДатаРождения + @pom*@date
GO
/*Проверка хранимой процедуры изменения всех дат*/
SELECT * FROM студент
DECLARE @pom SMALLINT,
@date SMALLINT
SET @pom=1
SET @date=30
EXECUTE pr_date @pom, @date
SELECT * FROM студент
GO
Рисунок 5 – Результат работы
-
Создайте хранимую процедуру, которая для конкретной таблицы подсчитывает минимальное, среднее, максимальное и суммарное значения в столбце числового типа. Эта процедура должна иметь один входной параметр (с помощью которого указывается имя или номер столбца) и четыре выходных параметра.
/*Создание хранимой процедуры подсчёта минимального,
среднего, максимального и суммарного значения */
CREATE PROCEDURE pr_min_max_sr_sum
@column VARCHAR(20),
@min DECIMAL(10,2) OUTPUT,
@max DECIMAL(10,2) OUTPUT,
@sum DECIMAL(10,2) OUTPUT,
@avg DECIMAL(10,2) OUTPUT
AS
DECLARE @sql NVARCHAR(255)
SET @sql = 'SELECT @min = MIN(' + @column + ') FROM преподаватель'
exec sp_executesql @sql, N'@min DECIMAL(10,2) out', @min=@min out
SET @sql = 'SELECT @max = MAX(' + @column + ') FROM преподаватель'
exec sp_executesql @sql, N'@max DECIMAL(10,2) out', @max=@max out
SET @sql = 'SELECT @sum = SUM(' + @column + ') FROM преподаватель'
exec sp_executesql @sql, N'@sum DECIMAL(10,2) out', @sum=@sum out
SET @sql = 'SELECT @avg = AVG(' + @column + ') FROM преподаватель'
exec sp_executesql @sql, N'@avg DECIMAL(10,2) out', @avg=@avg out
GO
/*Проверка хранимой процедуры подсчёта минимального,
среднего, максимальногое и суммарного значения */
DECLARE @Column VARCHAR(20)
SET @Column = 'Стаж'
DECLARE @min DECIMAL(10,2)
DECLARE @max DECIMAL(10,2)
DECLARE @sum DECIMAL(10,2)
DECLARE @avg DECIMAL(10,2)
EXEC pr_min_max_sr_sum @Column, @min OUTPUT, @max OUTPUT, @sum OUTPUT, @avg OUTPUT
SELECT @Column AS [Название столбца], @min AS [Минимальное значение], @max AS [Максимальное значение],
@sum AS [Сумма значений], @avg AS [Среднее значение]
GO
Рисунок 6 – Результат работы
-
Создайте хранимую процедуру, которая из конкретной таблицы отбирает строки по условию, налагаемому на значения конкретных двух столбцов. Эта процедура должна иметь два входных параметра, задающих значения для отбора данных по каждому из этих столбцов. При этом значение NULL должно трактоваться как отсутствие какого-либо критерия отбора строк по данному столбцу (например, если оба параметра будут иметь значение NULL, то должны быть выбраны все строки таблицы).
/*Создание хранимой процедуры по отбору строк по условию*/
CREATE PROCEDURE pr_yslov
@date DATETIME,
@sr_b INT
AS
IF @date IS NOT NULL
IF @sr_b IS NOT NULL
SELECT * FROM студент WHERE ДатаРождения > @date AND СреднийБалл > @sr_b
ELSE
SELECT * FROM студент WHERE ДатаРождения > @date
ELSE
IF @sr_b IS NOT NULL
SELECT * FROM студент WHERE СреднийБалл > @sr_b
ELSE
SELECT * FROM студент
GO
/*Проверка хранимой процедуры по отбору строк по условию*/
SELECT * FROM студент
DECLARE @Date DATETIME, @Sr_b INT
SET @Date='1.1.1988' /*getdate()*/
SET @Sr_b=7.4
EXECUTE pr_yslov @Date, @Sr_b
GO
Рисунок 7 – Результат работы
-
Создайте хранимую процедуру, которая на основе текстовой строки, содержащей фамилию, имя и отчество, формирует текстовую строку, содержащую фамилию и инициалы, и при этом все строчные буквы должны быть заменены прописными. Эта процедура должна иметь один входной параметр (с помощью которого задается исходная текстовая строка) и один выходной параметр.
При этом необходимо расширить возможности процедуры таким образом, чтобы была допустима исходная строка (задаваемая входным параметром), содержащая не один, а несколько пробелов между фамилией и именем или между именем и отчеством, а также допускающая наличие лидирующих пробелов перед фамилией.
Примечание. Некоторые их указанный выше хранимых процедур можно, по желанию, оформить в виде соответствующих пользовательских функций.
/*Создание пользовательской функции по формированию текстовой строки*/
CREATE FUNCTION fio (@str VARCHAR(50))
RETURNS VARCHAR(30)
BEGIN
DECLARE @str_gen VARCHAR(35)
DECLARE @position TINYINT
SET @str = LTRIM(@str)
SET @str = RTRIM(@str)
SET @position = CHARINDEX(' ', @str, 0)
SET @str_gen = LEFT(@str, @position)
SET @str = STUFF(@str, 1, @position, '')
SET @str = LTRIM(@str)
SET @str_gen = @str_gen + LEFT(@str, 1) + '.'
SET @position = CHARINDEX(' ', @str, 0)
SET @str = STUFF(@str, 1, @position, '')
SET @str = LTRIM(@str)
SET @str_gen = @str_gen + LEFT(@str, 1) + '.'
RETURN @str_gen
END
GO
/*Проверка пользовательской функции по формированию текстовой строки*/
DECLARE @str VARCHAR(50)
SET @str = ' Никулина Татьяна Николаевна '
SELECT @str AS 'Первоначальные данные',
dbo.fio(@str) AS 'Конечные данные'
GO
Рисунок 8 – Результат работы