| Выборка |
|---|
Хранимые процедуры и функции. Типы параметров, синтаксис описания формальных параметров. |
SQL Server
Хранимая процедура - это подпрограмма, состоящая из SQL операторов и команд T-SQL. Хранимая процедура сохраняется на сервере и выполняется по команде пользователя или вызывается из блока T-SQL. План выполнения процедуры подготавливается во время запуска процедуры, поэтому собственно выполнение процедуры происходит очень быстро. Хранимая процедура может:
- Содержать параметры (аргументы);
- Вызывать другие процедуры;
- Возвращать свой статус вызывающей процедуре или модулю T_SQL, указывающий на успешное окончание или ошибку, и в случае ошибки на ее причину;
- Возвращать значения параметров вызывающей процедуре или модулю T-SQL;
Синтаксис для создания хранимой процедуры:
| Блок кода |
|---|
|
create procedure [владелец.]название_процедуры[;номер] [
[(] @название_параметра тип_данных [= default] [output]
[, @название_параметра тип_данных [= default] [output]]...[)]] [with
recompile]
as sql_операторы
|
Оператор execute:
| Блок кода |
|---|
|
[execute] [@return_status =]
[[[сервер.]база_данных.]владелец.]название_процедуры[;номер]
[[@название_параметра = ] значение |
[@название_параметра = ] @переменная [output]
[,[@название_параметра = ] значение |
[@название_параметра = ] @переменная [output]...]]
[with recompile]
|
Параметры
Параметр - это аргумент хранимой процедуры. Один или несколько параметров могут быть объявлены в операторе создания процедуры. Значение каждого параметра, объявленного в операторе create procedure, должно указываться пользователем в момент вызова процедуры.
Названиям параметров должен предшествовать символ "@", а сами эти названия должны соответствовать правилам, установленным для идентификаторов. Для всех параметров должен быть указан системный или пользовательский тип данных, и если необходимо длина этого типа данных. Названия параметров являются локальными по отношению к содержащей их процедуре; такие же названия можно использовать для параметров в другой процедуре.
В операторе create procedure для параметра можно указать значение, принимаемое по умолчанию. Это значение, которое может быть любой константой, используется в качестве аргумента процедуры, если для этого параметра не было указано никакого значения.
| Блок кода |
|---|
|
CREATE proc dbo.tran_Begin /*
* Инициализация транзакции
* Если процедура выполняется внутри транзакции, то создается savepoint
* Иначе инициализируется транзакция
* На выходе передается null для транзакции и имя точки сохранения для savepoint
*/
(@trName DObjectName output, @explicitSavepointNameFlag DLogical = 0)
as
declare @d Datetime
begin
-- return 0
if @@trancount = 0
begin
select @trName = null
begin transaction
end
else
begin
-- если не задано имя и не установлен флаг использования переданного имени, то генерим имя
if @explicitSavepointNameFlag = 0 or @trName is null
begin
select @d = getdate()
select @trName = 'tr_'+ISNULL(convert(varchar, @@nestlevel), '')+'_'+ISNULL(convert(varchar(50),
rand(datepart(mm, @d)*100000+datepart(ss, @d)*1000+datepart(ms, @d))), '')
end
save tran @trName
end
if @@error <> 0
begin
-- ошибка создания транзакции
return 5
end
return 0
end
CREATE proc dbo.tran_Commit /*
* Подтверждение транзакции
* Работает в паре с tran_Begin
* на вход передается имя точки сохранения (транзакции), возвращенное tran_Begin
* Если вызов идет из транзакции и @trName равно null, то выполняется commit,
* иначе ничего не делается
*/
(@trName DObjectName)
as
begin
if @@trancount > 0 and @trName is null
begin
commit
if @@error <> 0
begin
-- ошибка подтверждения транзакции
return 6
end
end
return 0
end
CREATE proc dbo.tran_Rollback /*
* Откат транзакции
* Работает в паре с tran_Begin
* на вход передается имя точки сохранения (транзакции), возвращенное tran_Begin
* Если вызов идет из транзакции и @trName равно null, то выполняется откат всей транзакции,
* иначе выполняется откат до точки сохранения @trName
*/
(@trName DObjectName)
as
begin
if @@trancount > 0
begin
if @trName is null
rollback tran
else
rollback tran @trName
if @@error <> 0
begin
-- ошибка отката транзакции
return 7
end
end
return 0
end
|
Возврат результатов
Хранимые процедуры сообщают свой «статус возврата», который указывает, была ли выполнена процедура полностью, или нет, а также причины неудачи. Это значение может храниться в переменной, которая передается процедуре при ее вызове, и использоваться в последующих операторах Transact-SQL. Другой способ возврата информации из хранимых процедур состоит в возврате значений через выходные параметры. Параметры, определенные как выходные, в операторе create procedure (создать процедуру) или execute (выполнить) используются для возврата значений в место вызова процедуры. Затем с помощью условных операторов можно проверить возвращаемое значение.
Код возврата и выходные параметры позволяют разделить хранимые процедуры на модули. Группа SQL операторов, которые используются несколькими хранимыми процедурами, могут быть объединены в одну процедуру, которая сообщает свой статус выполнения или значения своих параметров вызывающей процедуре. Например, многие системные процедуры, поставляемые с SQL Сервером, обращаются к процедуре, которая проверяет являются ли указанные параметры правильными идентификаторами.
Если в операторах create procedure и execute указывается опция output в названии параметра, то процедура возвращает значение этого параметра вызывающему объекту. Этим объектом может быть SQL пакет или другая хранимая процедура, которые используют возвращаемые значения в своей дальнейшей работе. Если возвращаемые параметры используются в операторе execute, который является частью пакета, то значения возвращаемых параметров вместе с заголовком выводятся на экран перед выполнением последующих операторов пакета.
| Блок кода |
|---|
|
declare @tranName DObjectName
declare @retCode integer
begin
execute tran_Begin @tranName output
select @tranName
.........................................
|
Oracle
Создание процедур
| Блок кода |
|---|
|
create or replace procedure my_proc(i in number := 123, j out number, k in out number /* error := 123 */)
as
begin
dbms_output.put_line('i = ' || i);
dbms_output.put_line('j = ' || j);
dbms_output.put_line('k = ' || k);
if i is null then
return;
end if;
-- error
-- i := 10;
j := 20;
k := 30;
end;
create or replace procedure my_proc_error(i number /* error (1) */, s varchar2 /* error (10) */)
as
begin
null;
end;
declare
a number;
b number;
c number;
begin
a := 1;
b := 2;
c := 3;
my_proc(a, b, c);
dbms_output.put_line('a = ' || a);
dbms_output.put_line('b = ' || b);
dbms_output.put_line('c = ' || c);
end;
|
Создание функций
| Блок кода |
|---|
|
create or replace function my_fun(i in number) return number
as
j number;
begin
j := i+10;
return j;
end;
declare
a number;
begin
a := 12+my_fun(10);
dbms_output.put_line('a = ' || a);
end;
create or replace function my_fun(i in number) return number
as
j number;
procedure p(n in out number)
as
begin
n := n+i+j;
end;
begin
j := 3;
p(j);
j := j+i+10;
return j;
end;
declare
a number;
begin
a := 12+my_fun(10);
dbms_output.put_line('a = ' || a);
end;
|
Вызов процедур и функций
| Блок кода |
|---|
|
create or replace procedure my_p(s varchar2, i number := 10, j number := 20)
as
begin
null;
end;
declare
a number;
b number;
s varchar2(100);
begin
my_p(s);
my_p(s, a);
my_p(s, a, b);
my_p(s=>s, j=>b);
my_p(s, j=>b);
end;
|
Передача параметров. Атрибут NOCOPY
| Блок кода |
|---|
|
create or replace procedure my_p2(s1 out varchar2, s2 in out nocopy varchar2)
as
begin
s1 := 'aaaaaaaaaaaaaaaaaaaaaaaaaa';
s2 := 'bbbbbbbbbbbbbbbbbbbbbbbbbb';
end;
|
Модули PL/SQL
| Блок кода |
|---|
|
create or replace package PackageTest
as
type point is record
(
x number,
y number
);
tpoint point;
n number;
procedure p(ii in number, ss out varchar2);
function f(n in point) return number;
function f(x in number, y in number) return number;
end PackageTest;
create or replace package body PackageTest
as
type t1 is record
(
i number,
j number
);
cnt number;
trow1 test%rowtype;
procedure p(ii in number, ss out varchar2)
is
begin
select s into ss from test where i = ii;
exception
when no_data_found then
ss := 'empty string';
end;
function f(n in point) return number
as
begin
return n.x+n.y;
end;
function f(x in number, y in number) return number
as
begin
return x+y;
end;
begin
trow1.i := 1;
trow1.s := 'null string';
n := 100;
cnt := -10;
end PackageTest;
create or replace procedure PackageTest_test
as
-- error
-- d1 PackageTest.tl;
s varchar2(100);
n PackageTest.point;
begin
n.x := 100;
n.y := 200;
PackageTest.p(1, s);
dbms_output.put_line('s = ' || s);
dbms_output.put_line('f((100, 100)) = ' || PackageTest.f(n));
dbms_output.put_line('f(100, 100) = ' || PackageTest.f(n));
dbms_output.put_line('n = ' || PackageTest.n);
-- error
-- dbms_output.put_line('cnt = ' || PackageTest.cnt);
end;
|
Просмотр объектов и компиляция
| Блок кода |
|---|
|
desc user_objects
select object_name, object_type, status from user_objects;
|
Перекомпиляция процедур и функций
| Блок кода |
|---|
|
create or replace function f(i in number)
return varchar2
as
begin
return to_char(i+1);
end;
create or replace procedure p(ii in number, ss out varchar2)
as
begin
select s into ss from test where i = ii;
exception
when no_data_found then
ss := 'no data';
end;
select substr(object_name, 1, 30), object_type, status from user_objects;
select substr(object_name, 1, 30), object_type, status from user_objects where object_type in
('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY') and (object_name like 'P%' or object_name like 'F%');
declare
i number;
s varchar2(100);
begin
i := 10;
s := f(i);
dbms_output.put_line(s);
i := 1;
p(i, s);
dbms_output.put_line(s);
end;
alter table test add h date;
alter table test drop column h;
alter procedure p compile;
select * from user_errors;
alter function f compile;
alter package packagetest compile specification;
alter package packagetest compile body;
alter package packagetest compile package;
select substr(object_name, 1, 30), object_type, status from user_objects where object_type in
('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY') and (object_name like 'P%' or object_name like 'F%');
select * from user_errors;
|
Скрипт для перекомпиляции процедур и функций
Необходимо поместить в файл, запуск @<file>.sql
| Блок кода |
|---|
|
----------------------------------------------------
set echo off
set linesize 1000
set pagesize 500
set heading off
set feedback off
spool c:\tmp\a.sql
select 'ALTER ' || object_type || ' ' || object_name || ' COMPILE;' from user_objects where object_type in
('PROCEDURE', 'FUNCTION', 'PACKAGE');
select 'SELECT * FROM USER_ERRORS;' from dual;
spool off
@c:\tmp\a.sql
host del c:\tmp\a.sql
-----------------------------------------------------
|