Создание представлений и хранимых процедур посредством кода

Создание представлений и хранимых процедур из кода имеет те же преимущества, что и создание таблиц базы данных — всегда можно повторить операцию создания в случае потери этих объектов. Причем сами инструкции, которые необходимо передать из кода (на VB или другом языке) для создания представлений и процедур, ничем не отличаются от тех, которые используются вEnterprise Manager.

Например, код листинга 22.8 содержит две процедуры: Test_CreateView — создает представление п_Коды_Товары2 для базы данных Фирма (может быть выполнена в любой системе, поддерживающей Visual Basic), Test_UseView — использует представление для получения набора и помещает данные набора в Excel-лист.

Листинг 22.8 Создание и использование представления из VB-кода

1: Sub Test_CreateView()

2: 'создает новое представление

3:

4: Dim rs As ADODB.Recordset

5:

6: If ADODB_ConnectedSQL() Then

7: MsgBox "Похоже, соединение установлено..."

8: ' код, использующий установленное соединение:

9:

10: cn.Execute "USE Фирма"

11:

12: cn.Execute "CREATE VIEW п_Коды_Товары2 " & _

13: "AS " & _

14: " SELECT КодТовара, НаимТовара " & _

15: " FROM Товары"

16:

17: cn.Close ' закрыть соединение

18:

19: Else

20: MsgBox "Что-то не получается подключиться к базе данных!"

21: End If

22:

23: End Sub

24:

25: Sub Test_UseView()

26: 'использует новое представление

27:

28: Dim rs As ADODB.Recordset

29:

30: If ADODB_ConnectedSQL() Then

31: MsgBox "Похоже, соединение установлено..."

32: ' код, использующий установленное соединение:

33:

34: cn.Execute "USE Фирма"

35:

36: 'использование представления:

37:

38: Set rs = New ADODB.Recordset

39: rs.CursorType = adOpenDynamic

40: rs.Source = "п_Коды_Товары2"

41: Set rs.ActiveConnection = cn

42: rs.Open

43:

44: j = 1 'установить начальную строку таблицы

45:

46: 'записать заголовки полей в Excel-лист:

47: For i = 0 To rs.Fields.Count - 1

48: ActiveWorkbook.Sheets(1).Cells(j, i + 1).Value = _

49: rs.Fields(i).Name

50: Next

51:

52: 'записать содержимое полей в Excel-лист:

53: rs.MoveFirst

54: Do While Not rs.EOF

55: j = j + 1

56: For i = 0 To rs.Fields.Count - 1

57: ActiveWorkbook.Sheets(1).Cells(j, i + 1).Value = _

58: rs.Fields(i)

59: Next

60: rs.MoveNext

61: Loop

62:

63: cn.Close ' закрыть соединение

64:

65: Else

66: MsgBox "Что-то не получается подключиться к базе данных!"

67: End If

68:

69: End Sub

Код листинга 22.9 содержит две процедуры: Test_CreateProc — создает хранимую процедуру СпецифНакладной для базы данных Фирма (может быть выполнена в любой системе, поддерживающей Visual Basic), Test_UseProc — использует хранимую процедуру для получения набора и помещает данные набора в Excel-лист.

Процедура Test_CreateProc создает хранимую процедуру, предназначенную для формирования спецификации накладной. В качестве параметров хранимой процедуры используются (строки 16, 17): код подразделения (@pКодПодразд), номер накладной (@pНомерНакл) и дата накладной (@pДатаНакл). Это уникальный набор полей таблицы НаклСпецификации (см. главу 3, «Основы языка SQL»), по нему можно идентифицировать относящиеся к накладной товары.

Листинг 22.9 Создание и использование хранимой процедуры из VB-кода

1: Sub Test_CreateProc()

2: 'создает новую хранимую процедуру

3:

4: Dim rs As ADODB.Recordset

5:

6: If ADODB_ConnectedSQL() Then

7: MsgBox "Похоже, соединение установлено..."

8: ' код, использующий установленное соединение:

9:

10: cn.Execute "USE Фирма"

11:

12: 'накладная с указанными: кодом подразделения,

13: 'номером и датой

14: cn.Execute _

15: "CREATE PROC СпецифНакладной " & _

16: " @pКодПодразд char(4), @pНомерНакл char(7), " & _

17: " @pДатаНакл char(10) " & _

18: "AS " & _

19: "SELECT b.НаимТовара as 'Наименование', " & _

20: " a.Количество as 'Кол-во', " & _

21: " a.ЦенаОперации as 'Цена'" & _

22: "FROM НаклСпецификации a, Товары b " & _

23: "WHERE a.КодПодразделения = @pКодПодразд " & _

24: " and a.Номер =@pНомерНакл " & _

25: " and CONVERT(char(10), a.Дата, 104)=@pДатаНакл " & _

26: " and a.КодТовара=b.КодТовара ORDER BY b.НаимТовара"

27:

28: cn.Close ' закрыть соединение

29:

30: Else

31: MsgBox "Что-то не получается подключиться к базе данных!"

32: End If

33:

34: End Sub

35:

36: Sub Test_UseProc()

37: 'использует хранимую процедуру

38:

39: Dim rs As ADODB.Recordset

40:

41: If ADODB_ConnectedSQL() Then

42: MsgBox "Похоже, соединение установлено..."

43: ' код, использующий установленное соединение:

44:

45: cn.Execute "USE Фирма"

46:

47: 'использование процедуры:

48:

49: Set rs = New ADODB.Recordset

50: rs.CursorType = adOpenDynamic

51: rs.Source = "EXEC СпецифНакладной " & _

52: " @pКодПодразд='0429', @pНомерНакл ='4', " & _

53: " @pДатаНакл = '02.11.2002'"

54: Set rs.ActiveConnection = cn

55: rs.Open

56:

57: j = 1 'установить начальную строку таблицы

58:

59: 'записать заголовки полей в Excel-лист:

60: For i = 0 To rs.Fields.Count - 1

61: ActiveWorkbook.Sheets(1).Cells(j, i + 1).Value = _

62: rs.Fields(i).Name

63: Next

64:

65: 'записать содержимое полей в Excel-лист:

66: 'rs.MoveFirst

67: Do While Not rs.EOF

68: j = j + 1

69: For i = 0 To rs.Fields.Count - 1

70: ActiveWorkbook.Sheets(1).Cells(j, i + 1).Value = _

71: rs.Fields(i)

72: Next

73: rs.MoveNext

74: Loop

75:

76: cn.Close ' закрыть соединение

77:

78: Else

79: MsgBox "Что-то не получается подключиться к базе данных!"

80: End If

81:

82: End Sub

Инструкция (в общем, не сложная) для создания хранимой процедуры, имеет следующий вид (строки кода 15–26):

CREATE PROC СпецифНакладной

@pКодПодразд char(4), @pНомерНакл char(7),

@pДатаНакл char(10) " & _

AS

SELECT b.НаимТовара as 'Наименование',

a.Количество as 'Кол-во',

a.ЦенаОперации as 'Цена'

FROM НаклСпецификации a, Товары b

WHERE a.КодПодразделения = @pКодПодразд

and a.Номер =@pНомерНакл

and CONVERT(char(10), a.Дата, 104)=@pДатаНакл

and a.КодТовара=b.КодТовара ORDER BY b.НаимТовара

Обратите внимание на использование функции CONVERT. Это функция языка T-SQL, поскольку инструкция выполняется на сервере. Она предназначена для преобразования значения типа smalldatetime в строку длиной 10 символов [для этого в качестве первого аргумента используется выражение char(10) ] со значением даты. Это преобразование позволяет при обращении к хранимой процедуре в качестве параметра @pДатаНакл (дата накладной) использовать текстовую строку (строка кода 53) типа '02.11.2002'.

Поскольку результат выполнения процедуры помещается в Excel-лист, на рис. 22.13 приводится этот лист с данными. Обратите внимание на то, что здесь нет никакой информации о том, к чему относится этот список товаров. В таком виде, конечно, выдавать информацию нельзя даже для своих нужд. Обязательно следует сделать заголовок с указанием даты, номера накладной, места, где она была создана, и т.п.

Рис. 22.13

Результат выполнения процедуры Test_UseProc листинга 22.9

В коде листинга 22.10 имеются операторы для вывода в Excel-лист дополнительной информации о накладной (рис. 22.14).

Листинг 22.10 Использование хранимой процедуры из VB-кода

1: Sub Test_UseProc()

2: 'использует хранимую процедуру

3:

4: Dim rs As ADODB.Recordset

5: Dim НомерНак As Integer

6: Dim ДатаНак As String

7: Dim КодПодраз As String

8: Dim summ As Double 'итоговая сумма накладной

9:

10: summ = 0#

11:

12: 'здесь может быть более сложный код для определения

13: 'параметров хранимой процедуры:

14: НомерНак = 4

15: КодПодраз = "0429"

16: ДатаНак = "02.11.2002"

17:

18:

19:

20: If ADODB_ConnectedSQL() Then

21: MsgBox "Похоже, соединение установлено..."

22: ' код, использующий установленное соединение:

23:

24: cn.Execute "USE Фирма"

25:

26: 'использование хранимой процедуры:

27:

28: Set rs = New ADODB.Recordset

29: rs.CursorType = adOpenDynamic

30:

31: rs.Source = "EXEC СпецифНакладной " & _

32: " @pКодПодразд='" & КодПодраз & _

33: "', @pНомерНакл ='" & Trim(Str(4)) & "'," & _

34: " @pДатаНакл = '" & ДатаНак & "'"

35:

36:

37: Set rs.ActiveConnection = cn

38: rs.Open

39:

40: 'Заголовок накладной:

41: ActiveWorkbook.Sheets(1).Cells(1, 2).Value = _

42: "Накладная № " & Str(4) & " от " & ДатаНак

43:

44: 'для кода подразделения можно было бы найти наименование:

45: ActiveWorkbook.Sheets(1).Cells(2, 1).Value = _

46: "Подразделение " & КодПодраз

47:

48: j = 4 'установить начальную строку таблицы

49:

50: 'записать заголовки полей в Excel-лист:

51: For i = 0 To rs.Fields.Count - 1

52: ActiveWorkbook.Sheets(1).Cells(j, i + 1).Value = _

53: rs.Fields(i).Name

54: Next

55:

56: 'записать содержимое полей в Excel-лист:

57: 'rs.MoveFirst

58: Do While Not rs.EOF

59: j = j + 1

60: For i = 0 To rs.Fields.Count - 1

61: ActiveWorkbook.Sheets(1).Cells(j, i + 1).Value = _

62: rs.Fields(i)

63: Next

64:

65: summ = summ + rs.Fields(i - 1) 'накопление суммы

66:

67: rs.MoveNext

68: Loop

69:

70: 'запись суммы в Excel-лист:

71: ActiveWorkbook.Sheets(1).Cells(j + 1, 1).Value = "Сумма:"

72: ActiveWorkbook.Sheets(1).Cells(j + 1, i).Value = summ

73:

74:

75: cn.Close ' закрыть соединение

76:

77: Else

78: MsgBox "Что-то не получается подключиться к базе данных! "

79: End If

80:

81: End

 

 

Рис. 22.14

Результат выполнения процедуры Test_UseProc листинга 22.10

Следует отметить, что приведенные примеры создания баз данных и их объектов (таблиц, представлений и процедур) и примеры использования этих объектов в том виде, как они представлены здесь, могут применяться только на локальном компьютере. При этом неважно, где будет находиться SQL Server (на локальном компьютере или удаленном сервере) — у пользователя должны быть, по крайней мере, права на создание баз данных. Система безопасности SQL Server, для обсуждения которой в этой книге места не хватило, не разрешит одним пользователям без дополнительных усилий работать с базами данных (и их объектами), созданными другим пользователем. Хотя проблему безопасности всегда можно переложить на системного администратора (в нашей стране малые предприятия редко могут позволить себе роскошь иметь администратора баз данных, поэтому его или их задачи обычно выполняет системный администратор), в любом случае следует четко разделять свои проекты на те, которые предназначены для создания и модификации структур баз данных и их объектов, и на те, которые предназначены для обычной эксплуатации данных, содержащихся в базах. Т.е. для одной и той же базы данных необходимо создать, по крайней мере, два проекта: один «администраторский», при помощи которого вы всегда сможете создать базу, изменить ее структуру, добавить/удалить представление или хранимую процедуру, решить проблемы доступа отдельным пользователям к объектам базы; и другой — «пользовательский», при помощи которого к данным базы смогут обращаться или «все, кому не лень», или те, кому необходимо.