时间戳

首页 > 解决方案 > 需要帮助从光标中进行选择，就像它是工作台一样

sql - 需要帮助从光标中进行选择，就像它是工作台一样

问题描述

我有一个正在尝试编写的存储过程，我想将游标用作工作表，以便可以使用结果来更新表。它抛出了一些错误。谁能帮我解决这些问题？

create or replace PROCEDURE SP_POPULATE_STUDENT_STOPTEMPID

AS

CURSOR c_workTable IS
SELECT  
    stdnt.STUDENT_ID, 
    stdnt.route_code, 
    stdnt.DISPATCH_TYPE, 
    stdnt.RUN_CODE, 
    stdnt.STOP_ADDRESS, 
    stdnt.STOP_TIME, 
    ST.STOP_TEMPLATE_ID 
FROM  
    STOP_TEMPLATE ST 
    JOIN TASK_TEMPLATE TT 
        ON ST.TASK_TEMPLATE_ID = TT.TASK_TEMPLATE_ID 
    JOIN RUN_TEMPLATE RT 
        ON ST.RUN_TEMPLATE_ID = RT.RUN_TEMPLATE_ID,  
    STUDENT stdnt
WHERE 
    TT.TASK_NAME = stdnt.route_code 
    AND TT.DISPATCH_TYPE = stdnt.DISPATCH_TYPE 
    AND RT.RUN_CODE = stdnt.RUN_CODE 
    AND ST.STOP_DESCRIPTION = stdnt.STOP_ADDRESS 
    AND CAST(ST.EXPECTED_ARRIVAL_TIME AS TIME(7)) = stdnt.STOP_TIME ;

BEGIN

UPDATE STUDENT stdnt  
SET stdnt.STOP_TEMPLATE_ID = c_workTable .STOP_TEMPLATE_ID 
FROM  
    c_workTable 
WHERE  
    stdnt.STUDENT_ID = c_workTable .STUDENT_ID 
    AND stdnt.route_code = c_workTable .route_code 
    AND stdnt.DISPATCH_TYPE = c_workTable .DISPATCH_TYPE 
    AND stdnt.RUN_CODE = c_workTable .RUN_CODE 
    AND stdnt.STOP_ADDRESS = c_workTable .STOP_ADDRESS     
    AND stdnt.STOP_TIME = c_workTable .STOP_TIME 


END SP_POPULATE_STUDENT_STOPTEMPID;

ETA：更改了查询中的一些 var 名称

我是基于 sql server 存储过程编写的，但在更新语句中出现了一些错误。

关于更新学生标准

错误 (30,5): PL/SQL: SQL 语句被忽略

然后在 from 子句我得到这个错误

错误（32,5）：PL/SQL：ORA-00933：SQL 命令未正确结束

最后在 END 子句上我得到

错误（42,35）：PLS-00103：在预期以下情况之一时遇到符号“文件结尾”：（开始案例声明结束异常退出 goto if loop mod null pragma raise return select update while with
<<继续关闭当前删除获取锁
插入打开回滚保存点设置sql执行提交forall合并管道清除

--new code
UPDATE(
SELECT stdnt.STOP_TEMPLATE_ID as old_val,
ST.STOP_TEMPLATE_ID as new_val
FROM STUDENT stdnt
    JOIN STOP_TEMPLATE ST 
    JOIN TASK_TEMPLATE TT 
        ON ST.TASK_TEMPLATE_ID = TT.TASK_TEMPLATE_ID 
    JOIN RUN_TEMPLATE RT 
        ON ST.RUN_TEMPLATE_ID = RT.RUN_TEMPLATE_ID
    JOIN STUDENT stdnt
        ON (TT.TASK_NAME = stdnt.route_code 
            AND TT.DISPATCH_TYPE = stdnt.DISPATCH_TYPE 
            AND RT.RUN_CODE = stdnt.RUN_CODE 
            AND ST.STOP_DESCRIPTION = stdnt.STOP_ADDRESS 
            AND CAST(ST.EXPECTED_ARRIVAL_TIME AS TIMESTAMP(7)) = 
stdnt.STOP_TIME)
) t
SET t.old_val = t.new_val;

标签： sqloraclestored-proceduresplsql

解决方案

另一种更好的方法是使用 ORACLE 中的 MERGE 语句从 2 个或更多表中更新表 -

MERGE INTO STUDENT stdnt
USING (SELECT  SRM.STUDENT_ID, 
               SRM.route_code, 
               SRM.DISPATCH_TYPE, 
               SRM.RUN_CODE, 
               SRM.STOP_ADDRESS, 
               SRM.STOP_TIME, 
               ST.STOP_TEMPLATE_ID 
       FROM STOP_TEMPLATE ST 
       JOIN TASK_TEMPLATE TT ON ST.TASK_TEMPLATE_ID = TT.TASK_TEMPLATE_ID 
       JOIN RUN_TEMPLATE RT ON ST.RUN_TEMPLATE_ID = RT.RUN_TEMPLATE_ID,  
       STUDENT_ROUTE_MAPPING SRM 
       WHERE TT.TASK_NAME = SRM.route_code 
       AND TT.DISPATCH_TYPE = SRM.DISPATCH_TYPE 
       AND RT.RUN_CODE = SRM.RUN_CODE 
       AND ST.STOP_DESCRIPTION = SRM.STOP_ADDRESS 
       AND CAST(ST.EXPECTED_ARRIVAL_TIME AS TIME(7)) = SRM.STOP_TIME) I
ON (stdnt.STUDENT_ID = I.STUDENT_ID 
    AND stdnt.route_code = I.route_code 
    AND stdnt.DISPATCH_TYPE = I.DISPATCH_TYPE 
    AND stdnt.RUN_CODE = I.RUN_CODE 
    AND stdnt.STOP_ADDRESS = I.STOP_ADDRESS     
    AND stdnt.STOP_TIME = I.STOP_TIME)
WHEN MATCHED THEN UPDATE
                  SET stdnt.STOP_TEMPLATE_ID = I.STOP_TEMPLATE_ID

推荐阅读