将 MySQL 存储过程移植到 Oracle

发布于 2024-12-12 07:39:27 字数 2802 浏览 0 评论 0原文

我正在尝试将存储过程从 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 的人来说非常简单，但我正在阅读文档，并且发现有关在何处以及如何声明变量的信息存在冲突，继续处理程序，并将值分配给变量。（是用 := 或 = 来赋值吗？我是在 begin 语句之后使用“declare”一词来声明变量，还是按照下面所示的方式进行操作？）

如果有人可以告诉我：

a) 在哪里声明本地变量变量

b) 如何为其赋值（即 1 为 int）

c) 如何从 DB 为变量赋值（set var = select number from table_number tn where tn.number = 1）

d) 如何正确声明继续处理程序

我真的很感激。

原文

I'm trying to port a stored procedure from MySQL to Oracle, and I'm having a lot of trouble. I've gone through Oracle documentation, and I'm having trouble doing very basic things like declaring variables properly. I was hoping someone could show me how to properly declare and set variables.

My stored procedure is used to add values to two different tables and ensure that it's being mapped properly and the foreign keys aren't being violated.

Here is my MySQL Code:

 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

In Oracle, my last statement are being ignored (ORA-00922: Missing or invalid option). It should be a local variable, so I'm not sure why i'm getting that particular error.

I'm struggling to declare the continue handler as well. I'm getting the error:

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

Here is my oracle code so far:

 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;

I'm sure this is stupidly simple for someone that uses oracle, but I'm reading the documentation and I'm seeing conflicting information on where and how to declare variables, continue handlers, and assign values to variables. (is it := or = to assign values? Do i use the word declare after the begin statement to declare variables, or do I do it the way I show below?)

If someone could show me:

a) where to declare a local variable

b) how to assign a value to it (i.e. 1 to an int)

c) how to assign a value from the DB to a variable (set var = select number from table_number tn where tn.number = 1)

d) how to declare a continue handler properly

I would really appreciate it.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

缪败 2024-12-19 07:39:27

你的基本结构已经很好了。

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

为了解决您的具体问题：

a) 在哪里声明局部变量

我已在上面标记了此部分。

b) 如何给它赋值（即 1 给 int）

您可以使用 := 进行赋值。

例如。 thenameid := 1;

您想要的数据类型通常与 sql 数据类型相匹配（例如上述的 NUMBER），尽管存在 PL/SQL 特定的数据类型，例如如 PLS_INTEGER。请参阅PL/SQL 数据类型文档了解更多详细信息。

c) 如何将数据库中的值分配给变量（set var = select number from table_number tn where tn.number = 1）

您可以将 into 关键字与本地定义的变量一起使用来存储价值例如

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

d) 如何正确声明继续处理程序

如果我正确理解了您的代码，您希望每次 sql 语句出现问题时执行 set error = true 然后您希望继续执行的存储过程。

异常处理就是您所追求的。您可以将任何您认为可能有错误的 SQL PL/SQL 语句包装在这样的异常块中，并根据需要提供尽可能多的异常情况（例如 NO_DATA_FOUND）：

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

“其他”是包罗万象的。您可以只处理这种情况，但与任何错误处理一样，最好首先捕获特定情况。

为了完整起见，您的示例过程大致如下。我删除了错误代码标志，因为不需要它，并将 int 更改为 number：

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;

You've go the basic structure fine.

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

To address your specific questions:

a) where to declare a local variable

I've marked this section up above.

b) how to assign a value to it (i.e. 1 to an int)

You would use := for assignment.

eg. thenameid := 1;

The data type you want will typically match the sql data types (eg. NUMBER for the above) though there are PL/SQL-specific data types such as PLS_INTEGER. See the PL/SQL data types documentation for more details.

c) how to assign a value from the DB to a variable (set var = select number from table_number tn where tn.number = 1)

You would use the into keyword with a locally defined variable to store the value in. eg.

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

d) how to declare a continue handler properly

If I'm reading understanding your code correctly, you want set error = true to be executed every time there is a problem with an sql statement and then you want the stored procedure to carry on.

Exception handling is what you are after. You would wrap any or SQL PL/SQL statements that you think may have errors in an exception block like this, with as many exception cases as needed (eg. NO_DATA_FOUND):

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

"other" is the catchall. You can have just this case handled but as with any error handling it is better practise to catch specific cases first.

For completion, here's roughly what your example procedure would look like. I've removed the error code flag as it is not needed and also changed the ints to numbers:

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;

回复收藏 0 原文

~没有更多了~