需要帮助创建pl/sql查询

Greetings to all! I want to create a pl/sql query by declaring variables for the following eg:

的问候!我想通过声明以下变量来创建一个pl/sql查询，例如:

:stay_id = (SELECT Stay_Id from MVStay where StayNumber = 'xxxx' AND StayState = 2);
-- get passage linked to the stay and is 'discharged'
:passage_id = (SELECT Passage_Id from MVStayWorkflow where Stay_Id = :stay_id and WorkflowAction = 31);

-- get current date
:now = to_char(sysdate, 'YYYYMMDD HH:MI:SS.FF')
-- get a new sequence number
:stay_workflow_id = (get it from the concerned table)

--insert ‘Admin discharged’ workflow step
if( passage_id is not NULL)
begin
  Insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id, User_Id, RespUnit_Id, Resource_Id,
  WorkflowAction, CurrentState, PreviousState, WorkflowTime, UserStamp, TimeStamp)
  values (:stay_workflow_id, :stay_id, :passage_id, 1, 0, 0, 11, 7, 7, :now, 1, :now)
end

Regards Mohammed

认为*

Hi Alex..

嗨,亚历克斯. .

I used your code but encountered an error: Error starting at line 3 in command: declare l_stay_id MVStay.Stay_Id%TYPE; l_passage_id MVStayWorkflow.Passage_Id%TYPE; l_stay_workflow_id MVStayWorkflow.Stay_Workflow_Id%TYPE; l_now MVSTAY.ENDDATETIME%TYPE; begin

我使用了您的代码，但是遇到了一个错误:从命令行3开始的错误:声明l_stay_id MVStay.Stay_Id%类型;l_passage_id MVStayWorkflow.Passage_Id %类型;l_stay_workflow_id MVStayWorkflow.Stay_Workflow_Id %类型;l_now MVSTAY.ENDDATETIME %类型;开始

/* get closed stay having stay_number = '030074559' */ select Stay_Id into l_stay_id from MVStay where StayNumber = '030074559' and StayState = 2;

/*关闭stay_number = '030074559' */从MVStay将Stay_Id选择为l_stay_id, StayNumber = '030074559'， StayState = 2;

/* get passage linked to the stay and is 'discharged' */
select Passage_Id into l_passage_id
from MVStayWorkflow
where Stay_Id = l_stay_id
and WorkflowAction = 31;

/* get current date types in MVStayWorkflow? */
l_now := to_char(sysdate, 'YYYYMMDD HH:MI:SS.FF');

/* get a new sequence number */
l_stay_workflow_id := 500000

/* insert ‘Admin discharged’ workflow step */
if passage_id is not NULL then
    insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
        User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
        PreviousState, WorkflowTime, UserStamp, TimeStamp)
    values (l_stay_workflow_id, l_stay_id, l_passage_id,
        1, 0, 0, 11, 7, 7, l_now, 1, l_now);
end if;

end; Error report: ORA-06550: line 27, column 5: PLS-00103: Encountered the symbol "IF" when expecting one of the following:

结束;错误报告:ORA-06550:第27行，第5行:pl -00103:在预期以下情况时，遇到了“IF”的符号:

& = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol ";" was substituted for "IF" to continue.
1. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
2. 00000 -“行%s，列%s:只\n%s”*原因:通常是PL/SQL编译错误。*行动:
& = - +;< / >在in处的余数为mod not rem <> or !=或~= >= <= <>，或类似于|| multiset member submultiset The symbol ";00000 -“行%s，列%s:只\n%s”*原因:通常是PL/SQL编译错误。*行动:

2 个解决方案

#1

Question seems to be that you want to learn PL/SQL, rather than help with a query. Various issues with datatypes in here but the basic outline would be something like:

问题似乎是您想学习PL/SQL，而不是帮助查询。这里的数据类型有各种各样的问题，但是基本的大纲是这样的:

declare
    l_stay_id MVStay.Stay_Id%TYPE;
    l_passage_id MVStayWorkflow.Passage_Id%TYPE;
    l_stay_workflow_id MVStayWorkflow.Stay_Workflow_Id%TYPE;
    l_now varchar2(20); /* but why isn't this a date? */
begin
    select Stay_Id into l_stay_id
    from MVStay
    where StayNumber = 'xxxx' /* number or string? */
    and StayState = 2;

    /* get passage linked to the stay and is 'discharged' */
    select Passage_Id into l_passage_id
    from MVStayWorkflow
    where Stay_Id = l_stay_id
    and WorkflowAction = 31;

    /* get current date - really, why hold it as a string? what are the field
       types in MVStayWorkflow? */
    l_now := to_char(sysdate, 'YYYYMMDD HH:MI:SS.FF');

    /* get a new sequence number */
    l_stay_workflow_id := (get it from the concerned table)
    /* or, select ... into; or use a proper sequence for the insert? */

    /* insert ‘Admin discharged’ workflow step */
    if passage_id is not NULL then
        insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (l_stay_workflow_id, l_stay_id, l_passage_id,
            1, 0, 0, 11, 7, 7, l_now, 1, l_now);
    end if;
end;

You need to understand what each part is doing though, and read up on the differences between SQL and PL/SQL...

您需要了解每个部分正在做什么，并阅读SQL和PL/SQL之间的差异……

#2

In Oracle, you populate variables using the SELECT ... INTO ... syntax:

在Oracle中，使用SELECT填充变量…成……语法:

DECLARE v_workflow_id NUMBER;

BEGIN

  SELECT ct.workflow_id
    INTO v_workflow_id 
    FROM CONCERNED_TABLE ct;

  INSERT INTO MVSTAYWORKFLOW
    (StayWorkflow_Id, Stay_Id, Passage_Id, User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState, PreviousState, WorkflowTime, UserStamp, TimeStamp)
    SELECT v_workflow_id, s.stay_id, smf.passage_id, 1, 0, 0, 11, 7, 7, TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS.FF'), 1, TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS.FF')
     FROM MVSTAY s
LEFT JOIN MVSTAYWORKFLOW smf ON smf.stay_id = s.stay_id
                          AND smf.workflowaction = 31
  WHERE smf.passage_id IS NOT NULL
    AND s.stayNumber = 'xxxx' 
    AND s.staystate = 2;

END;

Also, Oracle interprets text prefixed with a colon (":") to be a BIND variable, meaning it expects to be populated with an incoming parameter value.

此外，Oracle将前缀为冒号(“:”)的文本解释为绑定变量，这意味着它希望使用传入的参数值填充。

If you want the stay_id separate from what I provided, use:

如果您希望stay_id与我提供的id分开，请使用:

DECLARE your_variable_name MYSTAY.stay_id%TYPE;

SELECT s.stay_id 
  INTO your_variable_name
  FROM MVSTAY s 
 WHERE s.stayNumber = 'xxxx' 
   AND s.staystate = 2;

#1