CREATE PROCEDURE


Главная - Примеры разработки 1С - CREATE PROCEDURE

Краткий справочник по Transact SQL
Автор: Иван Фролков ifrol2001@mail.ru
ALTER DATABASE
ALTER DATABASE database_name
[ON [DEFAULT | database_device][=size]
[,database_device[=size]]...]
[FOR LOAD]
Позволят производить различные операции с базой данных после ее создания
Пример
Этот пример изменяет testing, расположенную на DEVICE1, добавляя 8-ми мегабайтный фрагмент:
ALTER DATABASE testing
ON DEVICE1 = 8
ALTER TABLE
ALTER TABLE [database.[owner].]table_name
[WITH NOCHECK]
[ADD
{col_name column_properties column_constraints]
|[[,]table_constraint]}
[,{next_col_name|next_table_constraint}]...]
| DROP [CONSTRAINT]
constraint_name[,constraint_name]
Позволят производить различные операции с таблицей после ее создания
Пример

Добавление PRIMARY KEY CONSTRAINT:
ALTER TABLE authors
ADD
CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED (au_id)
Добавление FOREIGN KEY CONSTRAINT:
ALTER TABLE titles
ADD
CONSTRAINT FK_pub_id FOREIGN KEY (pub_id) REFERENCES publishers(pub_id)
Добавление UNIQUE CONSTRAINT:
ALTER TABLE titles
ADD
CONSTRAINT UNC_name_city UNIQUE NONCLUSTERED (stor_name,city)
Добавление DEFAULT CONSTRAINT:
ALTER TABLE authors
ADD
DEFAULT UNKNOWN FOR phone
Будьте внимательны с default!
Добавление CHECK CONSTRAINT:
ALTER TABLE authors
ADD
CONSTRAINT CK_zip CHECK (zip LIKE [0-9][0-9][0-9][0-9][0-9] )
Добавление новой колонки
ALTER TABLE publishers
ADD
country varchar(30) NULL
DEFAULT( USA )
BATCHES
Batch - это набор операторов TSQL, передаваемых на выпонение и выполняющихся вместе, как одно целое. Batch компилируется целиком и оканчивается специальным символом-сигналом конца (go). Все последовательности операторов TSQL, набираемые в ISQL/w или в Enterprise Manager интерпретируются именно как batch и (интересно то, что при выделении некоторой части текста в окне выполняться будет именно она)
Пример
Несколько SELECT в одном batch е
SELECT COUNT(*) FROM titles
SELECT COUNT(*) FROM authors
ВЫРАЖЕНИЯ CASE
Простое CASE Expession
CASE expression
WHEN expression1 THEN exression1
[[WHEN expression2 THEN expression2[..]]
[ELSE expressionN]
END
CASE Expression с поиском
CASE
WHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2[..]]
[ELSE expressionN]
END
Функции, полезные для CASEобразия:
COALESCE(expression1,expression2,...)
NULLIF(expression1,expression2)
ISNULL(expression1, expression)
COALESCE возвращает первое не-NULL выражение из списка, NULLIF возвращает NULL, если два выражения равны, ISNULL возвращает expression2 в том случае, если expression1 is null
Примеры
SELECT Category=
CASE type
WHEN popular_comp THEN Popular Computing
WHEN mod_cook THEN Modern Cooking
WHEN business THEN Businness
WHEN psyhology THEN Psyhology
WHEN trad_cook THEN Traditional Cooking
ELSE Not yet categorized
END,
Shortended Tiitle = CONVERT(varchar(30), title),
Price = price
FROM titles
WHERE price IS NOT NULL
ORDER BY type
COMPUTE AVG(price) BY TYPE
go
Category Shortedned Title Price
---------------------- ------------------------------ -----------
Business Cooking with Computers: Surrep 11.95
Business Straight Talkk About Computers 19.99
Business The Busy Executive s Database 19.99
Business You Can Combat Computer Stress 2.99

avg
============
13.73
ОПЕРАТОР CHECKPOINT
CHECKPOINT
Записывает на диск все страницы, измененные с момента последнего выполнения оператора CHECKPOINT. Контрольные точки(Checkpoints), производимые оператором CHECKPOINT, являются дополнительными к тем контрольным точкам, которые берет сервер, исходя из заданного параметра recovery time
КОММЕНТАРИИ
/*Текст комментария*/
или
--Текст комментария
ПОТОК УПРАВЛЕНИЯ
Порядок выполнения запросов и хранимых процедур TSQL может изменяться с помощью определенных операторов (как бы перевести Control-of-Flow Language )
Оператор Описание
BEGIN...END Определяет блок.
BEGIN
{sql_statement|statement_block}
END

GOTO label Безусловный переход к метке label Метки описываются незамысловато:
:label
GOTO label

IF...ELSE Условный оператор. Тоже ничего неожиданного
IF Boolean_expression
{sql_statement|statement_block}
[ELSE [Boolean_expression]
{sql_statement|statement_block}

RETURN Безусловный выход. Синтаксис бесхитростен:
RETURN ([integer_expression])

Жалко только, что возвращаемое значение - это не значение функции, а некий код возврата, причем часть значений зарезервирована под специальные состояния (0 - все нормально):
Код Значение
0 Все нормально
-1 Объект не найден
-2 Ошибка типа данных
-3 Процесс стал жертвой дедлока
-4 Ошибка доступа
-5 Синтаксическая ошибка
-6 "Некоторая" ошибка
-7 Ошибка с ресурсами(н., нет места)
-8 Произошла исправимая внутренняя ошибка
-9 Системный лимит исчерпался
-10 Неисправимое нарушение внутренней целостности
-11 То же самое
-12 Разрушение таблицы или индекса
-13 Разрушение базы данных
-14 Ошибка оборудования
WAITFOR Ожидание определенного события.
WINTFOR {DELAY time |TIME time }

DELAY - определение задержки time, ну а TIME - ожидание до указанного времени. time задается аналогично значениям для datetime
WHILE Цикл с предусловием.
WHILE Boolean_condition
{sql_statement|statement_block}
[BREAK]
{sql_statement|statement_block}
[CONTINUE]

...BREAK Выход из цикла WHILE
...CONTINUE Продолжение цикла WHILE Кроме вышеуказанных операторов могут оказаться полезными при написании запросов и хранимых процедур следующие возможности TSQL:
Возможность Описание
CASE Позволяет выражениям принмать значение в зависимости от условий. Между прочим, CASE является стандартной возможностью ANSI SQL-92
Комментарии Очень даже полезно хотя бы изредка комментировать свой код. Для комметатриев можно использовать две формы - первая, аналогичная комментариям в С:
/*некий глубокомысленный комметарий*/,
и вторая, аналогичная комментариям в Аде:
--Еще один заумный комметарий
Оператор DECLARE Повзоляет объявлять локальные переменные К слову сказать, он может стоять не только в начале процедуры или batch а, но и где угодно в их теле. Удобно объявлять переменные там, где они используются, а не тремястами строками выше. Впрочем, на мой взгляд, злоупотреблять этим тоже не стоит...
Оператор PRINT Выдает заданное значение на экран.
Оператор RAISERROR Устанавливает ошибочное состояние
Пример

DECLARE @Name varchar(40)
DECLARE CT CURSOR FOR SELECT Name from Peoples
OPEN CT
WHILE 1=1 BEGIN
FETCH FROM CT INTO @Name
/*Так, на мой взгляд, удобнее обходиться с курсорами*/
IF @@fetch_status=-1
BREAK
IF @@fetch_status=-2
CONTINUE
PRINT @Name
END
DEALLOCATE CT
CREATE DATABASE
CREATE DATABASE database_name
[ON [DEFAULT|database_device][= size]
[,database_device][= size]]...]
[LOG ON database_device[= size]
[,database_device[= size]...]
[FOR LOAD]
Создает БД и, возможно, журнал транзакций на указанных devices, size - размер в мегабайтах. При создании новой базы данных используется как образец БД model.
Пример

CREATE DATABASE sales
ON DEVICE3 = 125
LOG ON DEVICE4 = 60
CREATE DEFAULT
CREATE DEFAULT [owner.]default_name
AS constant_expression
Создает объект, который впоследствии можно будет прикрепить к некоторой колонке или пользовательскому типу данных, причем при добавлении новых данных значение constant_expression будет использовано по умолчанию, т.е. тогда, когда не было указано значение для этой колонки или было использовано ключевое слово DFAULT или DEFAULT VALUES. Для привязки созданного DEFAULT следует пользоваться процедурой sp_bindefault
Следующая таблица показывает соотношение между определением колонки и добавляемым значением:
Определение колонки Нет данных, нет DEFAULT Нет данных, DEFAULT определен Вводится NULL, нет DEFAULT Вводится NULL, DEFAULT определен
NULL NULL DEFAULT NULL NULL
NOT NULL error default error error Обратите внимание - DEFAULT можно создать только в текущей БД.
Пример

CREATE DEFAULT phonedflt AS unknown
sp_bindefault phonedflt, authors.phone
CREATE INDEX
CREATE [UNIQUE][CLUSTERED|NONCLUSTERED]INDEX index_name
ON [[database.]owner.]table_name(column_name[,column_name]...)
[WITH]
[FILLFACTOR = x]
[[,] IGNORE_DUP_KEY]
[[,] {SORTED_DATA | SORTED_DATA_REORG}]
[[,] {IGNORE_DUP_ROW | ALLOW_DUP_ROW}]]
[ON segment_name]
Создает индекс для перечмсленных колонок на указанном сегоменте.
CLUSTERED - создавать кластеризованный индекс, т.е. такой индекс, при котором в листьях B-дерева, образующего индекс, находятся не ссылки на данные, а собственно страницы данных.
FILLFACTOR - позволяет управлять заполнением страниц B-дерева индекса, задается в процентах, 100% - полное заполнение.
Пример
CREATE UNIQUE CLUSTERED INDEX au_id_ind
ON authors (au_id)
ТИПЫ ДАННЫХ
Типы данных определяют представление колонок таблиц, параметров процедур и переменных. В SQL Server помимо предопределенных системеых типов данных можно создавать и пользовательские типы, основывающиеся на системных. Более подробно о создании пользовательских типов данных можно ознакомиться в разделе, посвященном хранимой процедуре sp_addtype. В SQL Server все типы данных регистонезависимые, в силу чего недопустимо использование различных пользовательских типов данных, отличающихся только регистром.

!
Обратите внимание: если уже существующий пользовательский тип данных конфликтует с другим пользовательским или системным типом данных, то он будет использоваться как базовый системный тип. Существуют следующие системные типы данных:
Вид данных Системное представление
Двоичные binary[(n)]
varbinary[(n)]
Символьные char[(n)]
varchar[(n)]
Дата и время datetime
smalldatetime
Точное представление чисел decimal[(p[, s])]
numeric[(p[, s])]
Представление чисел с плавающей точкой float[(n)]
real
Целочисленные типы int - 4 байта
smallint - 2 байта
tinyint -1 байт
Денежные типы money
smallmoney
Специальные bit
timestamp
типы, определяемые пользователем
Текст и картинки text
image
Синонимы binary varying для varbinary
character для char
character для char (1)
character (n) для char (n)
character varying (n) для varchar (n)
dec для decimal
integer для int
double precision для float
float [(n)] для n = 1-7 для real
float [(n)] для n = 8-15 для float
Типы данных даты и времени
Данные и время представляются алфвавитно-цифровыми данными, в виде строки. По умолчанию для показа даты используется формат Mon dd yyyy hh:mmAM, например, "Apr 15 1996 10:23AM". При вводе данных следует обращать внимание на порядок лет, месяцев, дней и т.п.
При вводе данных используйте один из нескольких форматов, заключая значение в одиночные кавычки - " ". Если требуется получить секунды или миллисекунды - для этого применяется функция CONVERT. Существуют следующие типы даты и времени:
datetime
Этот тип данных имеет размер в 8 байт, т.е. два четырехбайтных целых - 4 байта на количество дней, прошедших или еще не наступивших с 1 января 1900, и 4 байта на число миллисекунд, прошедших с полуночи.
datetime может содержать даты с 1 января 1753 года и по 31 декабря 9999 года, с точностью в три тысячных секунды. По умолчанию datetime имеет значение 1 января 1900 года, полдень.
smalldatetime
Тип данных, во многом аналогичный datetime, но менее точный. Размер его - 4 байта, два байта на число дней, прошедших с 1 января 1900 года, и два байта на число минут с полуночи. Даты могут быть представлены в диапазоне с 1 января 1900 года и по 6 июня 2079 года, с точностью в минуту
Для ввода дат и времени можно применять следующие форматы:
Apr[il] [15][,] 1996
Apr[il] 15[,] [19]96
Apr[il] 1996 [15]
Apr[il] [19]96 15
[15] Apr[il][,] 1996
15 Apr[il][,][19]96
15 [19]96 apr[il]
[15] 1996 apr[il]
1996 APR[IL] [15]
[19]96 APR[IL] 15
1996 [15] APR[IL]
[0]4/15/[19]96 (mdy)
[0]4-15-[19]96 (mdy)
[0]4.15.[19]96 (mdy)
[04]/[19]96/15 (myd)
15/[0]4/[19]96 (dmy)
15/[19]96/[0]4 (dym)
[19]96/15/[0]4 (ydm)
[19]96/[04]/15 (ymd)
но, наверное, самым удобным и безопасным является формат [19]960415 - строка из шести или восьми цифр, в формате ггггммдд или ггммдд. Строка из четырех цифр будет интерпретирована как год.
Денежные типы данных
Типы данных money и smallmoney предназначены в первую очередь для представления денег.
money
Тип данных money в состоянии представлять числа в диапазоне от -922,337,203,685,477.5808 до +922,337,203,685,477.5807 с точностью в одну десятитысячную, имеет размер в 8 байт.
smallmoney
Тип данных smallmoney в состоянии представлять числа в диапазоне от -214,748.3648 до +214,748.3647, размер 4 байта.
Специальные типы данных
Среди всех более или менее обычных типов данных, имеющихся в SQL Server, несколько выделяются два типа данных - bit и timestamp.
Первый - bit - предназначен для представления целых чисел в диапазоне от 0 до 1, причем при вводе числа, отличного от 1, принимается значение, равное 1. Тип данных bit имеет размер в один байт, но при наличии нескольких полей типа bit в таблице они все будут упакованы вместе, например, если у нас есть 7 полей типа bit, то суммарное занимаемое ими пространство будет равно одному байту.
!
Обратите внимание: Для полей, имеющих тип bit, невозможно построить индекс. Значение другого типа данных - timestamp - обновляется при каждом изменении записи.
!
Обратите внимание: Поля типа timestamp не имеют никакого отношения к системной дате или системному времени. Если при создании таблицы будет указана колнока с именем timestamp, и при этом не будет указан никакой тип, то эта колонка автоматически будет иметь тип timestamp.
timestamp имеет размер 8 байт и представлено как varbinary(8)
ОПЕРАТОР DBCC
DBCC {
CHECKALLOC [(database_name [, NOINDEX])] |
CHECKCATALOG [(database_name)] |
CHECKTABLE (table_name [, NOINDEX | index_id]) |
CHECKDB [(database_name [, NOINDEX])] |
CHECKIDENT [(table_name)] |
DBREPAIR (database_name, DROPDB [, NOINIT]) |
dllname (FREE) |
INPUTBUFFER (spid) |
MEMUSAGE |
NEWALLOC [(database_name [, NOINDEX])] |
OPENTRAN ({database_name} | {database_id})
[WITH TABLERESULTS] |
OUTPUTBUFFER (spid) |
PERFMON |
PINTABLE (database_id, table_id) |
SHOW_STATISTICS (table_name, index_name) |
SHOWCONTIG (table_id, [index_id]) |
SHRINKDB (database_name [, new_size [, MASTEROVERRIDE ]]) |
SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} |
{THREADS} | {LOGSPACE}) |
TEXTALL [({database_name | database_id}[, FULL | FAST])] |
TEXTALLOC [({table_name | table_id}[, FULL | FAST])] |
TRACEOFF (trace#) |
TRACEON (trace#) |
TRACESTATUS (trace# [, trace#...]) |
UNPINTABLE (database_id, table_id) |
UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]]) |
USEROPTIONS}
[WITH NO_INFOMSGS]
где
CHECKCATALOG [(database_name)]
Проверяет целостность системных таблиц и отношений между ними
CHECKTABLE (table_name [, NOINDEX | index_id])
Проверяет указанную таблицу, убеждаясь, что ее страницы и страницы указанного индекса корректно связаны. Если сегмент журнала расположен на отдельном устройстве, то запуск DBCC CHECKTABLE для таблицы syslogs позволит узнать размер использованного и свободного места в журнале.
CHECKDB[(table_name)]
Проводит проверку, аналогичную CHECKTABLE, но только для каждой таблицы и ее индексов в указанной базе данных, или в текущей, если никакая не указана.
CHECKIDENT[(table_name)]
Проверяет текущее значение IDENTITY для указанной таблицы и сравнивает его с маскимальным из фактически имеющихся, изменяя его в случае необходимости.
NOINDEX
Указывает, что необходимо проверять только кластеризованный индекс и его листья - то есть собственно страницы данных, если же кластеризованный индекс отсутсвует - проверяются только страницы данных.
DBREPAIR(database_name,DROPDB,NOINIT)
Удаляет указанную базу данных, обычно разрушенную, после чего уже невозможно получить к ней доступ. Если указана опция NOINIT, то тогда страницы данных, представляющих из себя базу данных, не модифицируются. DBREPAIR - это, собственно говоря, пережиток прошлого. Теперь для удаления разрушенной базы данных нормальные пацаны пользуются оператором DROP DATABASE. Если же и он не работает (во дают!), то следует воспользоваться хранимой процедурой sp_dbremove. Указаний, куда идти, если и это не помогает, Microsoft не дает, впрочем, большинство и так догадывается.
dllname(FREE)
Выгружает из памяти указанную библиотеку.
INPUTBUFFER(spid)
Возвращает первые 255 байт буфера SQL запроса для указанного spid.
MEMUASGE
Выдает данные об использовании памяти сервером.
NEWALLOC(database_name[,NOINDEX])
Проверяет корректность текущей или указанной бд, выдавая более подробный отчет, нежели чем CHECKALLOC, и не прерывая работы в случае нахождения ошибки.
!
Обратите внимание: CHECKALLOC или NEWALLOC могут сообщать об ошибке в том случае, если база данных испольщуется кем-то еще, так что старайтесь запускать проверку при минимальной загрузке или даже в однопользовательском режиме.
OPENTRAN({database_name|database_id}) WITH TABLERESULTS
Позволяет получить информацию об начатых, но незавершенных транзакциях, и о начатых, но не распространенных(distributed) транзакциях.
OUTPUTBUFFER(spid)
Позволяет просмотреть выходные данные для процесса spid
PERFMON
Позволяет получить статистику всех трех типов - IOSTATS, LRUSTATS и NETSTATS.
PINTABLE(database_id, table_id)
Помещает таблицу и связанные с ней индексы в оперативную память. Не следует считать, что PINTABLE прочитывает таблицу непосредственно в память; напротив, данные, к которым был доступ, помещаются в кеш, откуда уже не вытесняются до последующего вызова DBCC UNPINTABLE.
SHOW STATISTICS(table_name,index_name)
Позволяет получить статистику о распределении страниц в таблице
SHOWCONTIG(table_id,index_id)
Показывает фрагментированность таблицы
SHRINKDB (database_name [, new_size [, MASTEROVERRIDE ]])
Позволяет сократить по возможности размер, занимаемый базой данных.
SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} {THREADS} {LOGSPACE})
Позволяет просмотреть различного рода статистику
TEXTALL[({database_name|database_id})[,FULL|FAST])
Проверяет корректность размещения данных во всей базе данных в колонках типа text или image.
TEXTALLOC[({table_name|table_id},[FULL|FAST])]
Проверяет корректность размещения данных в колонках типа text или image.
TRACEOFF(trace#)
Выключает указанный флаг трассировки
TRACEON(trace#)
Включает указанный флаг трассировки
TRACESTATUS(trace#[,trace#...])
Показывает статус для указанных флагов трассировки
UNPINTABLE(database_id,table_id)
Объявляет указанную таблицу как нерезидентную в памяти сервера. Данные, однако, остаются в кеше до тех пор, пока они не будут сборошены на диск по тем или иным причинам.
UPDATEUASGE({0|database_name},table_name[,index_id])
Исправляет возможные неаккуратности в сообщениях о размере базы данных.
USEROPTIONS
Позволяет посмотреть список опций, установденных командой SET
WITH NO_INFOMSGS
Подавляет выдачу информационных сообщений (уровень с 1 по 10)
Пример

DBCC CHECKALLOC(pubs)
go
Checking pubs
Alloc page 0 (# of extent=32 used pages=58 ref pages=58)
Alloc page 256 (# of extent=26 used pages=37 ref pages=37)
Alloc page 512 (# of extent=14 used pages=40 ref pages=40)
Alloc page 768 (# of extent=1 used pages=8 ref pages=2)
Alloc page 1024 (# of extent=1 used pages=0 ref pages=0)
Alloc page 1280 (# of extent=1 used pages=0 ref pages=0)
Total (# of extent=75 used pages=143 ref pages=137) in this database
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
CREATE PROCEDURE
CREATE PROCedure [owner.]procedure_name[;number]
[(parameter1 [, parameter2]...[parameter255])]
[{FOR REPLICATION} | {WITH RECOMPILE}
[{[WITH] | [,]} ENCRYPTION]]
AS sql_statements
parameter=
@parameter_name datatype[=default][OUTPUT]
Создает процедуру с указанным именем. Процедура может быть создана только в текущей базе данных, за исключением временных процедур, которые создаются в tempdb. Для создания временных процедур следует начинать ее имя с # или ## . Длина имени хранимой процедуры вместе с ## не может превышать 20 символов. Одна процедура может вызывать другую процедуру, уровень вложенности не может превышать 16, текущий уровень вложенности можно узнать из глобоальной переменной @@NESTLEVEL
Пользователь может создавать свои системные процедуры; они начинаются с символов sp_. При попытке выполнения такой процедуры она сначала ищется в текущей базе данных, в случае же неудачи - в базе данных master. Таблицы, используемые в системной процедуре, определямой пользователем, также сначала отыскиваются в текущей базе данных, и если это не удалось - в базе данных master.
Пример

CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a, titles t, publishers p, titleauthor ta
WHERE a.au_id = ta.au_id
AND t.title_id = ta.title_id
AND t.pub_id = p.pub_id