410013796724260
• Webmoney
R335386147728
Z369087728698
Create Procedure, FunctionХранимая процедура Stored Procedure — это объект базы данных, который представляет собой набор SQL-инструкций, компилируется один раз при создании и размещается на сервере. Хранимые процедуры имеют большое сходство с процедурами и функциями языков высокого уровня по следующим критериям :
В хранимых процедурах могут производиться числовые вычисления и операции над символьными данными, а также выполняться стандартные операции с базами данных типа DDL и DML. В зависимости от типа СУБД, хранимые процедуры могут быть реализованы на таких языках программирования, как SQL, Java, C/C++. Наибольшее распространение, безусловно, получил SQL. Для различных типов СУБД используются разные реализации SQL :
Хранимую процедуру можно использовать как представление View для чтения данных. В этом случае она будет возвращать набор данных (записей). Отдельные значения хранимая процедура может вернуть через параметры, если они определены с ключевым словом "out". Процедуру можно оформить как функцию, возвращающую либо простое значение типа INT, CHAR и т.п., либо значение типа RECORD (запись). Синтаксис создания процедуры, CREATE PROCEDURECREATE [OR REPLACE] PROCEDURE [schema.]name [( [IN | OUT | INOUT] param1 <datatype> [, [IN | OUT | INOUT] param2 <datatype> ...])] { AS | IS } [ -- Объявление локальных переменных DECLARE var1 <datatype>; [DECLARE var2 <datatype>; ...]] BEGIN { исполняемый код } [EXCEPTION обработчики исключений] END [name]; По умолчанию передаваемые процедуре параметры являются входными и ключевое слово "IN" можно не указывать. Если параметр является выходным типа [OUT, INOUT], то данные ключевые слова необходимо использовать. Тип параметров должен соответствовать принятым в СУБД типам. <datatype> = { {SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION} | {DECIMAL | NUMERIC} [(precision [, scale])] | DATE | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)] [CHARACTER SET charname] | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(int)]} В Oracle можно использовать также и "ссылку" на тип : -- Ссылка на тип поля birthday таблицы user birthday user.birthday%TYPE; Примечание : В СУБД PostgreSQL можно создать только функцию. Для создания процедуры в PostgreSQL необходимо чтобы функция возвращала значение типа "void". Пример CREATE PROCEDURE ORACLECREATE OR REPLACE PROCEDURE InsertCourse ( v_name IN varchar2 ) AS cnumber number; crs cursor for SELECT course_number FROM courses_tbl WHERE course_name = v_name; BEGIN open crs; fetch crs into cnumber; if crs%notfound then cnumber := 9999; end if; INSERT INTO student_courses ( course_name, course_number ) VALUES ( v_name, cnumber ); commit; close crs; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001, 'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM); END; Синтаксис создания функции, CREATE FUNCTIONСинтаксис создания функции CREATE FUNCTION, по сравнения с созданием хранимой процедуры, включает дополнительно оператор RETURNS после описания типов входных параметров и оператор return в теле функции. CREATE FUNCTION [schema.]name [( [IN | OUT | INOUT] param1 <datatype> [, [IN | OUT | INOUT] param2 <datatype> ...])] RETURNS <datatype> { AS | IS } [ -- Объявление локальных переменных DECLARE var1 <datatype>; [DECLARE var2 <datatype>; ...]] BEGIN { исполняемый код } return <datatype>; END [name]; Функция, возвращающая значения типа RECORDФункция может возвращать как простые значения, так записи типа RECORD. Пример создания функции CREATE FUNCTION ORACLE, возвращающей запись. CREATE FUNCTION getEmployee (p_empl_id employees.empl_id%type) returns employee%rowtype as employee employee%rowtype; begin select * into employee from employee where empl_no = p_empl_no; return employee; end; Вызов процедуры, CALLВызов процедуры согласно SQL 2003 включает оператор CALL и наименование процедуры с параметрами : CALL [schema.]имя_процедуры ([параметр [, …]]); Синтаксис CALL определяет значения входных параметров, необходимых для хранимой процедуры - параметры должны быть заключены в скобки и отделяться друг от друга запятыми. Следует отметить, что скобки необходимы даже в том случае, если параметры отсутствуют, то есть при отсутствии параметров необходимо писать CALL [schema.]имя_процедуры(). Примечание : Для вызова процедуры из приложения Java необходимо использовать CallableStatement. Пример вызова хранимой процедуры : CREATE PROCEDURE SALES.CUSTOMER_UPDATE (id NUMBER, comm NUMBER) IS BEGIN UPDATE CUSTOMERS SET commission = comm WHERE customer_id = id; END SALES.CUSTOMER_UPDATE; -- вызов процедуры с передачей параметров CALL SALES.CUSTOMER_UPDATE(213, 15); Некоторые платформы поддерживают также альтернативную команду EXECUTE, имеющую те же возможности. В некоторых случаях EXECUTE может оказаться предпочтительнее, чем CALL, поскольку ее можно использовать для запуска любого заранее подготовленного кода SQL, в том числе процедур и функций. Вызов хранимой процедуры MSSQLДля вызова хранимой процедуры СУБД MSSQL следует использовать EXECUTE или EXEC. Синтаксис вызова хранимой процедуры описан в MSDN Удаление процедуры, DROP PROCEDUREСинтаксис удаления процедуры : DROP PROCEDURE [schema.]name; |