Статья призвана показать различия в разработке бизнес-логики в различных СУБД (Oracle, MS SQL, Firebird, MySQL, PostgreSQL) на примерах. Также это краткий справочник по синтаксису процедур, функций, триггеров с примерами кода - может быть полезным для начинающих.
Oracle является промышленным лидером на рынке, к нему приближается MS SQL, но только за счёт того, что это маркетинг Microsoft.
Установить MS SQL на Windows, и начать работать с ним достаточно просто.
И то, что это продукт Microsoft - играет большую роль при выборе СУБД для проектов. Он занимает большую нишу.
Но внутри он отличается от других СУБД на рынке. Сама идеология Microsoft другая.
Для тех, кто всё время с ним работал, он кажется верхом совершенства, но на самом деле другие СУБД удобнее, логичнее, понятнее в разработке.
Примеры.
Начнём с функций.
Для начала создадим тестовую таблицу:
CREATE TABLE STAFF (
ID INTEGER,
NAME VARCHAR(50),
SALARY int,
RATE float,
BONUS int
);
И заполним её тестовыми данными:
insert into staff (id, name, salary, rate) VALUES (1, 'Иванов', 100, 0.2);
insert into staff (id, name, salary, rate) VALUES (2, 'Петров', 200, 0.3);
insert into staff (id, name, salary, rate) VALUES (3, 'Сидоров', 300, 0.5);
commit;
Напишем функцию, которая будет возвращать оклад по id работника:
CREATE PROCEDURE GETSALARY ( ID TYPE OF COLUMN STAFF.ID )
RETURNS ( SALARY INT )
AS
BEGIN
SALARY = (select salary from STAFF where id = :ID);
SUSPEND;
END
Вызвать и получить данные очень просто:
select * from getsalary(2)
т.е. можно делать select как из таблицы, подставляя параметр. Количество входных и выходных параметров можно менять, могут быть параметры по умолчанию, внутри процедуры можно присваивать значения переменным просто "=" :
ALTER PROCEDURE GETSALARY ( ID TYPE OF COLUMN STAFF.ID, ratio float = NULL )
RETURNS ( SALARY INT, BONUS int )
AS
BEGIN
SALARY = (select salary from STAFF where id = :ID) ;
if (ratio is null ) then ratio = 0.1;
BONUS = SALARY*ratio;
SUSPEND;
END
Вызывать можно:
select * from getsalary(3)
Или:
select * from getsalary(3, 0.2)
DELIMITER //
CREATE FUNCTION getSalary( IDs int)
RETURNS INT
BEGIN
DECLARE SALARY_ INT;
select salary INTO SALARY_ from staff where id = IDs ;
RETURN SALARY_;
END //
DELIMITER ;
Вызов:
SELECT getSalary(1)
CREATE OR REPLACE FUNCTION getSalary (id_ INTEGER) RETURNS INTEGER AS $$
DECLARE SALARY_ integer;
BEGIN
SALARY_ = (select salary from STAFF where id = getSalary.id_) ;
RETURN SALARY_;
END;
$$ LANGUAGE plpgsql;
Вызов:
select * from getSalary(3);
ВОт так можно вернуть несколько переменных:
CREATE OR REPLACE FUNCTION getSalary (IN id_ INTEGER, OUT SALARY_ int, OUT Bonus int ) AS $$
DECLARE rate_ float;
BEGIN
select salary, rate into SALARY_, rate_ from STAFF where id = getSalary.id_ ;
Bonus := SALARY_*rate_;
END;
$$ LANGUAGE plpgsql;
Также можно вернуть таблицу. Вообще, по возможностям языка, PostgreSQL очень близок к Oracle.
CREATE FUNCTION GetSalary (@id INT)
RETURNS INT
BEGIN
DECLARE @Salary INT
SELECT @Salary = salary FROM Staff WHERE id = @id
RETURN @Salary
END;
Вызов:
SELECT dbo.GetSalary(1)
Видите разницу? Переменные начинаются с символа "@". Конец строки (атомарной инструкции) не обозначается символом ";"
Т.е. можно написать вот так:
CREATE FUNCTION GetSalary (@id INT)
RETURNS INT
BEGIN
DECLARE @Salary INT SELECT @Salary = salary FROM Staff WHERE id = @id RETURN @Salary
END;
С точки зрения логики, и в сравнени с другими СУБД - это выбивается из общей картины мира... :)
При этом другие атомарные инструкции ("Insert...." или "Update....", например) обязаны заканчиваться точкой с запятой ;
И в скрипте для разделения пакетов используется не символ ; а "GO"
И для вызова функции нужно указывать схему: dbo
Наконец мы подобрались к лидеру рынка. Здесь синтаксис не выбивается из общей массы:
CREATE OR REPLACE FUNCTION GetSalary (id_ Int)
RETURN int
AS
Salary int;
BEGIN
SELECT Salary INTO Salary FROM staff where id = id_;
RETURN Salary;
END;
Для проверки:
select GetSalary(1) from dual
-- или:
select name, GetSalary(id) from staff
В PL/SQL из функции тоже можно вернуть несколько переменных. Для этого надо создать объектный тип данных:
Create or Replace type rowdata as Object
(salary int, str varchar(50));
CREATE OR REPLACE FUNCTION GetSalary2 (id_ Int) RETURN rowdata
AS retval rowdata;
BEGIN
SELECT rowdata ( Salary, name ) INTO retval FROM staff where id = id_;
-- можно оперировать с данными:
retval.str := retval.str || ' _строка_';
RETURN retval;
END;
Обращаться к отдельным переменным:
select (GetSalary2 (2)).salary, (GetSalary2 (2)).str from dual
Напишем процедуру, которая, проставит bonus, в зависимости от коэффициента rate, либо переданным непосредственно в процедуру. В примерах я не рассматриваю переменные IN, OUT - это тема для отдельной статьи.., и много других ньюансов, которые есть в разных СУБД
create or alter procedure SETBONUS ( ID type of column STAFF.ID, RATIO float = null)
as
declare SALARY type of column STAFF.SALARY;
declare RATIOBASE float;
declare variable BONUS type of column STAFF.bonus;
BEGIN
select salary, rate from STAFF where id = :ID into SALARY, ratiobase;
if (RATIO is null ) then ratio = ratiobase;
bonus = SALARY*ratio;
update STAFF SET BONUS = :bonus where id = :id;
END
Вызов:
EXECUTE PROCEDURE SETBONUS(2);
-- или
EXECUTE PROCEDURE SETBONUS(1, 0.7); -- в этом случае bonus подсчитается, исходя из коэф-та 0.7, а не 0.2 из таблицы.
Здесь мы сталкиваемся с ограничениями/особенностями: нельзя сделать параметр у процедуры по умолчанию. Т.е. создав процедуру, например с двумя входными параметрами - надо передавать ей именно 2.
CREATE PROCEDURE SetBonus( IDs INT)
BEGIN
DECLARE SALARY_ INT;
DECLARE RATING Float;
select salary, rate INTO SALARY_, RATING from staff where id = IDs ;
UPDATE staff SET bonus = SALARY_*RATING WHERE id= IDs;
END //
DELIMITER ;
Вызов:
CALL SetBonus(3)
Здесь всё логично:
CREATE OR REPLACE PROCEDURE SetBonus (id_ INTEGER, RATE_ float DEFAULT NULL ) AS $$
DECLARE SALARY_ integer;
DECLARE Koeff float;
BEGIN
select salary, rate into SALARY_, Koeff from STAFF where id = SetBonus.id_ ;
IF (not RATE_ is null) THEN Koeff = RATE_; -- если передали коэф-т в параметре, возьмём его
END IF;
UPDATE staff set Bonus = SALARY_ * Koeff WHERE id = id_;
END;
$$ LANGUAGE plpgsql;
Вызов:
call SetBonus(2);
-- или передадим отдельный коэфф-т:
call SetBonus(1, 1.5);
Здесь НЕ КАК У ВСЕХ... Для разделения атомарных инструкций НЕ применяются ";"
Просто присвоить значение переменной - неа... Надо ставить SET имя_переменной = ....
Для тех, кто пришёл с других СУБД - впереди ещё есть сюрпризы.
CREATE PROCEDURE SetBonus (@id INT, @KOEFF FLOAT = NULL)
AS
BEGIN
DECLARE @Salary INT
DECLARE @RATING FLOAT
SELECT @Salary = salary, @RATING = rate FROM Staff WHERE id = @id
IF (NOT @KOEFF IS NULL) BEGIN
SET @RATING = @KOEFF
END
UPDATE STAFF SET Bonus = @Salary*@RATING WHERE id = @id
END
Вызов процедуры с параметрами - тоже не как у всех. Вы думаете, что как обычно - параметры в скобках? Вот и не угадали..
EXECUTE SetBonus 2
-- или
EXECUTE SetBonus 1 , 1.3
Здесь всё логично и предсказуемо. Синтаксисы PL/SQL и Pg/SQL схожи, функционал богат и там и там, поэтому переход с одной СУБД на другую проще чем между другими.
CREATE OR REPLACE PROCEDURE SetBonus (id_ Int, rating float DEFAULT NULL)
AS
SALARY_ integer;
Koeff float;
/* тип переменной можно указать такой-же как у конкретного поля:
или Koeff staff.rate%TYPE,
чтобы сделать тип данных такой-же как в поле rate, и в дальнейшем при смене типов данных не менять везде в процедурах/функциях */
BEGIN
SELECT Salary, rate INTO SALARY_, Koeff FROM staff where id = id_;
IF (not rating is null) THEN Koeff := rating;
END IF;
UPDATE staff set Bonus = (SALARY_ * Koeff) WHERE id = id_;
END;
Вызов:
EXECUTE SetBonus(1);
-- или:
EXECUTE SetBonus(1, 0,5);
Напишем триггер, который при вставке записи в staff будет автоматически проставлять поле rate в зависимости от указанного оклада. Здесь рассматриваются триггеры уровня строки (Будут вызываться для каждой обрабатываемой/вставляемой/обновляемой строки). Ещё для PostgreSQL, Oracle есть триггеры уровня оператора - будут вызваны ОДИН раз на каждую операцию INSERT/UPDATE/DELETE, независимо от того, сколько строк зацепил такой оператор. Ещё есть триггеры INSTEAD OF ("вместо вставки/обновления/удаления" в view) - для построения логики работы с вьюшками (views)
Здесь всё просто и предсказуемо. Доступ с вставляемым (новым значениям) NEW.__ Старые (существующие) значения: OLD.__
CREATE trigger staff_ins FOR staff
ACTIVE BEFORE INSERT
AS
BEGIN
if (NEW.rate is null) then NEW.rate = (NEW.salary*0.001);
END
И при вставке записи без указания rate, автоматически проставится в размере 0,001 от оклада. Также триггеры могут делать значительно бОльшую работу, вставлять данные в другие таблицы, вызывать процедуры, функции, и.т.д.
insert into staff (id, name, salary) VALUES (4, 'Тестовый', 500 )
Получаем проставленное значение rate:
Здесь синтаксис отличается, но в целом тоже всё понятно. Для присвоения значений надо использовать ключевое слово SET:
DELIMITER //
CREATE trigger staff_ins
BEFORE INSERT ON staff
FOR EACH ROW
BEGIN
IF (NEW.rate is null) then SET NEW.rate = (NEW.salary*0.001);
END IF;
END;
//
Делаем
insert into staff (id, name, salary) VALUES (4, 'Тестовый', 500 )
и получаем результат:
Здесь свои ньюансы/тараканы. Мы не можем в триггере написать BEGIN/END и в этом блоке сделать всё что нужно. Можно только запустить какую-либо триггерную процедуру. Т.е. нужно создать к триггеру функцию, в которой будут делаться действия. Это капец как неудобно, но вот так....
CREATE FUNCTION t_staff_before_ins () RETURNS trigger AS $$
BEGIN
if (NEW.rate is null) then NEW.rate = (NEW.salary*0.001);
end if;
return NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER staff_ins BEFORE INSERT
ON staff
FOR EACH ROW
EXECUTE FUNCTION t_staff_before_ins();
Вот здесь всё кардинально отличается от других СУБД. Начать с того, что здесь НЕТ триггеров BEFORE (т.е. ДО вставки/обновления/удаления). Есть только AFTER и INSTEAD OF. Во-вторых - для доступа к значениям НЕ ИСПОЛЬЗУЮТСЯ удобные OLD.__ , NEW.__. У нас есть виртуальные таблицы inserted и deleted, в которых содержатся новые значения и старые. И их надо оттуда ещё извлечь. Т.е. это ОГРОООмный косяк MS SQL (наследие sybase). Для программистов, привыкших к другим СУБД, идеология работы триггеров MS SQL кажется %"?%*той..
CREATE TRIGGER staff_ins
ON staff
AFTER INSERT
AS
BEGIN
UPDATE staff SET rate = salary * 0.001 WHERE Id = (SELECT Id FROM inserted)
END;
Здесь самые большие возможности у триггеров. Есть триггеры BEFORE, AFTER, INSTEAD OF, триггеры уровня строки, уровня транзакции, уровня базы... Но и есть одна проблема - мутирующие таблицы. Доступ к значениям полей - через :NEW и :OLD
CREATE OR REPLACE trigger t_staff_bi BEFORE INSERT ON STAFF
FOR EACH ROW
begin
IF (:NEW.rate is null) THEN :NEW.rate := (:NEW.salary*0.001);
END IF;
end;