oracle 插入存储过程 (PL/SQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16255852/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT stored procedure (PL/SQL)
提问by Dmitry
I'm new to PL/SQL and stored procedures in general. I'm trying to write a stored procedure that will be executed from a Java program via a CallableStatement. The procedure takes two parameters, gets the id of the last record, increments it and inserts a new record with the newly incremented id. I found some examples online which are largely doing the same thing, but I can't resolve the errors.
我是 PL/SQL 和存储过程的新手。我正在尝试编写一个存储过程，该过程将通过 CallableStatement 从 Java 程序中执行。该过程接受两个参数，获取最后一条记录的 id，将其递增并插入一个具有新增加的 id 的新记录。我在网上找到了一些在很大程度上做同样事情的例子，但我无法解决错误。
CREATE OR REPLACE PROCEDURE insertEmployeeProcedure
(lastname IN VARCHAR, firstname IN VARCHAR) AS
BEGIN
lastEmpId NUMBER := SELECT COUNT(*)
INTO lastEmpId
FROM Employees;
INSERT INTO Employees(id, lname, fname) VALUES(lastEmpId + 1, lastname, firstname);
END insertEmployeeProcedure;
/
The errors are:
错误是：
Executed successfully in 0.018 s, 0 rows affected.
Line 1, column 1
Error code 984, SQL state 42000: ORA-00984: column not allowed here
Line 8, column 5
Error code 900, SQL state 42000: ORA-00900: invalid SQL statement
Line 9, column 1
Error code 0, SQL state null: java.lang.NullPointerException
Line 9, column 1
Execution finished after 0.018 s, 3 error(s) occurred.
As far as I understand a store procedure is a mix of PL and SQL.That said, I tried incrementing lastEmpId (as lastEmpId := lastEmpId + 1) but got an "Invalid SQL statement" error. Also, Oracle docs (http://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm#CIHBCBHC) do not do a good job explaining how to define and use local variables in stored procedures.
据我了解，存储过程是 PL 和 SQL 的混合体。也就是说，我尝试增加 lastEmpId（如 lastEmpId := lastEmpId + 1），但出现“无效的 SQL 语句”错误。此外，Oracle 文档 ( http://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm#CIHBCBHC) 没有很好地解释如何在存储过程中定义和使用局部变量。
Thanks in advance.
提前致谢。
回答by Alex Gitelman
You probably want something like
你可能想要类似的东西
CREATE OR REPLACE PROCEDURE insertEmployeeProcedure
(lastname IN VARCHAR, firstname IN VARCHAR) AS
lastEmpId NUMBER;
BEGIN
SELECT COUNT(*)
INTO lastEmpId
FROM Employees;
INSERT INTO Employees(id, lname, fname) VALUES(lastEmpId + 1, lastname, firstname);
END insertEmployeeProcedure;
/
Note that the variable is declared before BEGIN section.
请注意，该变量是在 BEGIN 部分之前声明的。
Also, if you use SQL developer, after you execute package creation code run
此外，如果您使用 SQL developer，则在执行包创建代码后运行
show errors
It will show you any problem before you actually call the procedure.
它会在您实际调用该过程之前向您显示任何问题。
回答by Rodrigo Villalba Zayas
You must use sequences to increment id column. Not always number of rows matches last used id
您必须使用序列来增加 id 列。行数并不总是与上次使用的 ID 匹配
An example to create sequences:
创建序列的示例：
CREATE SEQUENCE sequence_name
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
An example of how to use this sequence follows:
如何使用此序列的示例如下：
CREATE OR REPLACE PROCEDURE insertEmployeeProcedure
(lastname IN VARCHAR, firstname IN VARCHAR) AS
BEGIN
INSERT INTO Employees(id, lname, fname) VALUES(sequence_name.nextval, lastname, firstname);
END insertEmployeeProcedure;
