Skip to content
Advertisement

How to create a stored procedure that will update a table

I am trying to create a stored procedure that will update the project status from ‘D’ to ‘C’. As you can see below I do not know what I am doing.

Please do not suggest using a trigger as that does not pertain to the question.

create or replace procedure selectallprojects is
begin
    update projects
        set project_status = 'C'
            where project_status = 'D';
    
    select *
    from projects
    where project_status = 'D'
end;

begin
    selectallproject
end;

These are the errors I get when I run this code block:

Errors: PROCEDURE SELECTALLPROJECTS
Line/Col: 7/5 PL/SQL: SQL Statement ignored
Line/Col: 9/33 PL/SQL: ORA-00933: SQL command not properly ended
Line/Col: 14/1 PLS-00103: Encountered the symbol “END” when expecting one of the following:

:= . ( @ % ;

What am I doing wrong and how can I fix this?

You can find the script file I am using here.

Advertisement

Answer

The UPDATE is fine, the SELECT is wrong. The following code should work:

create or replace procedure selectallprojects is
begin
    update projects
        set project_status = 'C'
            where project_status = 'D';
end;
/

begin
  selectallprojects;
end;
/

select *
  from projects
 where project_status = 'D';
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement