PL/SQL更新命令不更新

Question

我在下面的存储过程编写

CREATE OR REPLACE PROCEDURE EmployeeProcedure 
(id IN NUMBER, name IN VARCHAR, salary IN NUMBER, 
stmt IN VARCHAR 
) AS 

BEGIN 
IF stmt='INSERT' then 
    INSERT INTO Employee(employee_id,name,salary) VALUES(id, name, salary); 
END IF; 

IF stmt='UPDATE' then 
    update employee set name=name, salary=salary 
     where employee_id=id; 
END IF; 

IF stmt='DELETE' then 
    Delete from employee where employee_id=id; 
END IF; 

END EmployeeProcedure; 

这是可在雇员表

EMPLOYEE_ID NAME SALARY TITLE 
2 Ranjan 40000 Manager 
3 Stella 5000 President 
4 Fred 500 Janitor 
10 Ranjan 3000 - 
1 John 64876 - 

当我执行该存储的数据在数据库中的记录程序使用以下statment

begin 
EmployeeProcedure(10,'John',78987,'UPDATE'); 
end; 

我正在处理的语句。但员工ID = 10的记录没有更新。任何帮助将不胜感激！

Answer 1

不要使用相同的名称，只要你想更新

这不是什么都不做现场使用的变量：

SET name = name, salary = salary 

设置你的变量，如：

str_name, int_salary 

然后你将有

SET name = str_name, salary = int_salary 

Answer 2

在调用您的过程或程序本身时，我看不到提交。

如果您的执行从您声明的匿名块运行，请务必在事后提交。

另外，作为胡安·卡洛斯·Oropeza说，具有相同名称的列中的参数，已被证明是一种不好的做法，追加字母“P”来区分列名的参数，如：

CREATE OR REPLACE PROCEDURE EmployeeProcedure 
(p_id IN NUMBER, p_name IN VARCHAR, p_salary IN NUMBER, 
p_stmt IN VARCHAR 
) AS 

BEGIN 
IF p_stmt='INSERT' then 
    INSERT INTO Employee(employee_id,name,salary) VALUES(p_id, p_name, p_salary); 
END IF; 

IF p_stmt='UPDATE' then 
    update employee set name=p_name, salary=p_salary 
     where employee_id=p_id; 
END IF; 

IF p_stmt='DELETE' then 
    Delete from employee where employee_id=p_id; 
END IF; 

END EmployeeProcedure; 

Answer 3

很高兴我帮了忙。

关于在你的过程中使用'select'语句，我认为你将不得不稍微改变一下实现一个返回值，因为在PL/SQL块中使用select语句是不可能的，除非你在明确的/隐含的游标中使用它。

我想象这样的事情可以做：

create or replace PROCEDURE EmployeeProcedure(
    p_id IN NUMBER, 
    p_name IN VARCHAR, 
    p_salary IN NUMBER, 
    p_stmt IN VARCHAR, 
    p_CURSOR in out SYS_REFCURSOR 
) AS 
BEGIN 
    IF p_stmt='INSERT' then 
     INSERT INTO Employee(employee_id,name,salary) VALUES(p_id, p_name, p_salary); 
    END IF; 

    IF p_stmt='UPDATE' then 
     update employee set name=p_name, salary=p_salary 
      where employee_id=p_id; 
    END IF; 

    IF p_stmt='DELETE' then 
     Delete from employee where employee_id=p_id; 
    END IF; 

    IF p_stmt='SELECT' then 
     /*open the explicit cursor*/ 
     open p_cursor for 
      select * 
      from employee 
      where employee_id = p_id; 
    END IF; 

END EmployeeProcedure; 

，然后你将要读光标这样：

declare 
    cur  SYS_REFCURSOR; 
    data_row employee%rowtype; 
begin 
    EmployeeProcedure(1,null,null,'SELECT', cur);--opens cursor 
    loop 
     fetch cur into data_row; --reads one row 
     exit when cur%notfound; --exits when you reach the end of rows 

     --at this point you can access every row in your table. 
     dbms_output.put_line(data_row.employee_id); 
    end loop; 
    close cur; --always remember close every cursor you open to keep your DBA happy. 
end;