这个 PL/SQL 脚本有什么问题吗？

Question

VARIABLE StayWorkflow_Id_max number;

BEGIN
  SELECT max(StayWorkflow_Id)+1 into :StayWorkflow_Id_max  from MVStayWorkflow;
END;
/

insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (:StayWorkflow_Id_max, 98485, 129844, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (:StayWorkflow_Id_max+1, 66311, 114593, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (:StayWorkflow_Id_max+2, 83742, 110157, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (:StayWorkflow_Id_max+3, 74421, 98685, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

update SYSQLSequence set nextValue = :StayWorkflow_Id_max+4 where name = '_MVStayWorkflow_auto_';

commit;

给我这个错误：

Error report:
SQL Error: Missing IN or OUT parameter at index:: 1

Answer 1

您的代码并不是真正的 PL/SQL，而主要是 SQL*plus 脚本（特别是 VARIABLE 既不是 PL/SQL 也不是 SQL）。

为什么不把它变成纯 PL/SQL：

DECLARE
  StayWorkflow_Id_max number;

BEGIN
  SELECT max(StayWorkflow_Id)+1 into StayWorkflow_Id_max  from MVStayWorkflow;

  insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (StayWorkflow_Id_max, 98485, 129844, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

  insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (StayWorkflow_Id_max+1, 66311, 114593, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

  insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (StayWorkflow_Id_max+2, 83742, 110157, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

  insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (StayWorkflow_Id_max+3, 74421, 98685, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

  update SYSQLSequence set nextValue = StayWorkflow_Id_max+4 where name = '_MVStayWorkflow_auto_';

  commit;
END;
/

Answer 2

正如 wallyk 所指出的，生成 ID 的方式很可疑。通常，它是这样的：

create sequence StayWorkflow_Id nocache;  /* you only do that once */

insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
        User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
        PreviousState, WorkflowTime, UserStamp, TimeStamp)
    values (StayWorkflow_Id.nextval, 98485, 129844, 1, '', '', 11, 7, 7,
            to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));


insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
        User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
        PreviousState, WorkflowTime, UserStamp, TimeStamp)
    values (StayWorkflow_Id.nextval, 66311, 114593, 1, '', '', 11, 7, 7,
            to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));


 ...

每次只需使用 StayWorkflow_Id.nextval 获取一个新的唯一 id，而不用担心竞争条件

Answer 3

我没有看到任何与错误消息相关的明显错误。

但是，如果 SYSQLSequence 是一个序列，那么直接更新它是不常见的（至少），并且在某些 Oracle 版本中可能不允许。我相信你必须做这样的事情：

alter sequence SYSQLSequence
increment by 4;

更新：好吧，这也有缺陷，所以我把它改掉了。