Блог

Главная - Вопросы по программированию - Перенос хранимой процедуры MySQL в Oracle

Перенос хранимой процедуры MySQL в Oracle

#mysql #oracle #stored-procedures #plsql

#mysql #Oracle #хранимые процедуры #plsql

Вопрос:

Я пытаюсь перенести хранимую процедуру из MySQL в Oracle, и у меня много проблем. Я просмотрел документацию Oracle, и у меня возникли проблемы с выполнением очень простых вещей, таких как правильное объявление переменных. Я надеялся, что кто-нибудь сможет показать мне, как правильно объявлять и устанавливать переменные.

Моя хранимая процедура используется для добавления значений в две разные таблицы и обеспечения того, чтобы она отображалась правильно, а внешние ключи не нарушались.

Вот мой код MySQL:

  CREATE DEFINER=root@% PROCEDURE proc_add_entry(IN theName vARCHAR(50), IN theKey VARCHAR(50), IN theOtherData VARCHAR(50), IN theOtherData2 INT, IN theStartDate DATE, IN theEndDate DaTE, IN theReferenceDate DaTE)
     LANGUAGE SQL
     NOT DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
 BEGIN
 declare theNameID int ;
 declare theKeyID int ;
 declare theOtherDataID int default null;
 declare error bool default false;
 declare continue handler for SQLEXCEPTION
    set error = true;

    set theKeyID = (select KeyID from map_alias ma where ma.alias = trim(theKey));
    set theOtherDataID = (select theOtherDataID from map_otherdata mc where mc.otherdata = trim(theOtherData));

    set theNameID = (select max(nameID) from inserttable);
    set theNameID = theNameID   1;
    insert into inserttable values (theNameID , theKeyID , theOtherDataID , theOtherData2, theStartDate , 
    theEndDate , theReferenceDate);

    if error = true then
        insert into errors_inserttable values (theNameID , theKeyID , theOtherDataID , theOtherData2, theStartDate , 
    theEndDate , theReferenceDate);
    end if;

    set error = false;
    insert into map_inserttable (theNameID , datasourceid, theName) values (theNameID , 1, theName);
    if error = true then
        insert into errors_map_inserttable  (theNameID , datasourceid, theName) values (theNameID , 1, theName);
    end if;

 END

В Oracle мое последнее утверждение игнорируется (ORA-00922: отсутствует или недопустимый параметр). Это должна быть локальная переменная, поэтому я не уверен, почему я получаю именно эту ошибку.

Я также пытаюсь объявить обработчик продолжения. Я получаю сообщение об ошибке: 

  Error(16,27): PLS-00103: Encountered the symbol "FOR" when expecting one of the following:     := . ( @ % ; not null range default character.

Вот мой код Oracle на данный момент:

  CREATE OR REPLACE PROCEDURE PROC_ADD_ENTRY
 (
   THENAME IN VARCHAR2  
 , THEKEY IN VARCHAR2  
 , THEOTHERDATA IN VARCHAR2  
 , THEOTHERDATA2 IN NUMBER  
 , THEFIRSTDATE IN DATE  
 , THELASTDATE IN DATE  
 , THEREFERENCEDATE IN DATE  
 ) AS
  THENAMEID INT;
  THEKEYID INT;
  THEOTHERDATAID int;
  ERROR bool default false;
 BEGIN
 declare continue HANDLER FOR SQLEXCEPTION set error = true;



   set THEKEYID = (select KEYID from map_INSERTTABLE mc where mc.Key = trim(THEKEY));
 END PROC_ADD_ENTRY;

Я уверен, что это глупо просто для тех, кто использует oracle, но я читаю документацию и вижу противоречивую информацию о том, где и как объявлять переменные, продолжать обработчики и присваивать значения переменным. (это:= или = для присвоения значений? Должен ли я использовать слово declare после оператора begin для объявления переменных, или я делаю это так, как показано ниже?)

Если бы кто-нибудь мог показать мне:

а) где объявить локальную переменную

б) как присвоить ей значение (т.Е. 1 для int)

c) как присвоить переменной значение из базы данных (set var = select number из table_number tn, где tn.number = 1)

d) как правильно объявить обработчик продолжения

Я был бы очень признателен.

Ответ №1:

Вы отлично справились с базовой структурой.

 create or replace procedure <name> (<param list>) as
  <local variables>
begin
  <body>
end <name>;

Для решения ваших конкретных вопросов:

а) где объявить локальную переменную

Я отметил этот раздел выше.

б) как присвоить ей значение (т.Е. 1 для int)

Вы бы использовали := для назначения.

например. thenameid := 1;

Требуемый тип данных обычно будет соответствовать типам данных sql (например, NUMBER для приведенного выше), хотя существуют специфические для PL / SQL типы данных, такие как PLS_INTEGER . Более подробную информацию см. в документации по типам данных PL / SQL.

c) как присвоить переменной значение из базы данных (set var = select number из table_number tn, где tn.number = 1)

Вы бы использовали into ключевое слово с локально определенной переменной для хранения значения в. например.

 l_num_rows number;
select count(*) into l_num_rows from user_objects;

d) как правильно объявить обработчик продолжения

Если я правильно понимаю ваш код, вы хотите set error = true , чтобы он выполнялся каждый раз, когда возникает проблема с оператором sql, а затем вы хотите, чтобы хранимая процедура продолжалась.

Обработка исключений — это то, что вам нужно. Вы должны обернуть любые операторы or SQL PL / SQL, которые, по вашему мнению, могут содержать ошибки, в блок исключений, подобный этому, с таким количеством случаев исключения, сколько необходимо (например. NO_DATA_FOUND):

 begin
  <statements that may fail>
exception when <exception name> then
  <action>
...
exception when others then
  <action>
end;

«другое» — это главное. Вы можете обработать только этот случай, но, как и при любой обработке ошибок, лучше сначала отслеживать конкретные случаи.

Для завершения, вот примерно так будет выглядеть ваша примерная процедура. Я удалил флаг кода ошибки, поскольку он не нужен, а также изменил int s на number s:

 create or replace procedure proc_add_entry (
  in thename varchar(50),
  in thekey varchar(50),
  in theotherdata varchar(50),
  in theotherdata2 number,
  in thestartdate date,
  in theenddate date,
  in thereferencedate date
) as
  thenameid number;
  thekeyid number;
  theotherdataid number default null;
begin
  begin
    select keyid into thekeyid from map_alias ma where ma.alias = trim(thekey);

    select theotherdataid into theotherdataid from map_otherdata mc where mc.otherdata = trim(theotherdata);

    select max(nameid) into thenameid from inserttable;
    thenameid := thenameid   1;

    insert into inserttable values (thenameid, thekeyid, theotherdataid, theotherdata2, thestartdate, theenddate, thereferencedate);
  exception when others then
    insert into errors_inserttable values (thenameid, thekeyid, theotherdataid, theotherdata2, thestartdate, theenddate, thereferencedate);
  end;

  begin
    insert into map_inserttable (thenameid, datasourceid, thename) values (thenameid, 1, thename);
  exception when others then
    insert into errors_map_inserttable (thenameid, datasourceid, thename) values (thenameid, 1, thename);
  end;

end proc_add_entry;