Need help selecting from cursor as if it were a worktable

0
votes

I have a stored procedure I am trying write and I wanted to use a cursor as a worktable so I could use the results to update a table. It is throwing a few errors. Would anyone be able to help me resolve these?

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: changed some var names in query

I have written this based on a sql server stored procedure but I am getting a few errors in the update statement.

On UPDATE STUDENT stdnt

Error(30,5): PL/SQL: SQL Statement ignored

Then on the from clause I get this error

Error(32,5): PL/SQL: ORA-00933: SQL command not properly ended

Finally on the END clause I get

Error(42,35): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with
<< continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall merge pipe purge

--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;
2
sqloraclestored-proceduresplsql
Is there a reason why you need to do it this way? It would be quicker, easier and more maintainable to do the update in a single SQL statement (probably a MERGE statement). – Boneist
There is no real reason other than my inexperience with sql in general... – Kevin Lord of Spaghetti Code
a quick look suggests the end is missing at the end of file – EvilTeach
It was included in my qorkspace, I just suck at copying. I adjusted the original post. – Kevin Lord of Spaghetti Code

2 Answers

1
votes

Another and better way is to use the MERGE statement in ORACLE to update the table from 2 or more tables - 

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
0
votes

You can achieve it by using one UPDATE statement that utilizes JOIN: 

UPDATE(
    SELECT  
        stdnt.STOP_TEMPLATE_ID as old_val 
      , ST.STOP_TEMPLATE_ID as new_val
    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
        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 TIME(7)) = stdnt.STOP_TIME)
) t
SET t.old_val = t.new_val;

Link to another question and answers that might help: Update statement with inner join on Oracle.

Alternative but similar solution would be getting a value for update from a subquery:

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

Link to an answer on a related problem with similar solution: ORA-00933: SQL command not properly ended error on update statement