oracle PLS-00103：在预期以下情况之一时遇到符号“(”：
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27492212/
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
PLS-00103: Encountered the symbol "(" when expecting one of the following:
提问by Man HOOD
I am trying to retrieve a customer id pk column from a report page to a form page that gets automatically incremented whenever the user moves from the report page to the form page using oracle application express.So i am trying to create a trigger that returnns a number having the value of the final row along with a +1 increment to it. However i get this error
我正在尝试从报告页面检索客户 ID pk 列到表单页面，每当用户从报告页面移动到使用 oracle application express 的表单页面时，该表单页面就会自动递增。所以我正在尝试创建一个触发器，返回一个数字具有最后一行的值以及 +1 的增量。但是我收到这个错误
Error at line 8: PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from
6. INTO number
7. FROM (SELECT a.cust_id, max(cust_id) over() as max_pk FROM customer a)
8. WHERE cust_id = max_pk;
9. number:=(cust_id+1);
10. END;
This is my PL/SQL procedure.
这是我的 PL/SQL 过程。
CREATE OR REPLACE FUNCTION cust_id_incremental(cust_id IN number)
RETURN number;
BEGIN
SELECT cust_id
INTO number
FROM (SELECT a.cust_id, max(cust_id) over() as max_pk FROM customer a)
WHERE cust_id = max_pk;`enter code here`
number:=(cust_id+1);
END;
回答by mmmmmpie
numberis a reserve word, you need to call it something else like l_numberto be a variable name.
number是保留字，您需要将其称为其他l_number名称，例如变量名。
回答by Allan
You need to define a variable to contain the result, populate it, then return it. The corrected procedure might look like this:
您需要定义一个变量来包含结果，填充它，然后返回它。更正后的程序可能如下所示：
CREATE OR REPLACE FUNCTION cust_id_incremental (cust_id IN NUMBER)
RETURN NUMBER IS
v_cust_id NUMBER;
BEGIN
SELECT cust_id
INTO v_cust_id
FROM (SELECT a.cust_id, MAX (cust_id) OVER () AS max_pk
FROM customer a)
WHERE cust_id = max_pk;
v_cust_id := v_cust_id + 1;
RETURN v_cust_id;
END;
Taking a second look, the structure of this procedure is far more convoluted than it needs to be. Unless I'm missing something, you could accomplish the same thing with a procedure that looked like this:
再看一眼，这个过程的结构比它需要的要复杂得多。除非我遗漏了什么，否则您可以使用如下所示的程序完成相同的事情：
CREATE OR REPLACE FUNCTION cust_id_incremental
RETURN NUMBER IS
v_cust_id NUMBER;
BEGIN
SELECT MAX (cust_id) + 1
INTO v_cust_id
FROM customer a;
RETURN v_cust_id;
END;
It occurred to me as I was writing this that you may have namespace issue: your original function accepts a parameter CUST_ID, then queries a table with column CUST_ID. All of the references to CUST_IDin the query will refer to the column, not the parameter. If the parameter serves a purpose, it is being obscured.
我在写这篇文章时突然想到您可能有命名空间问题：您的原始函数接受一个 parameter CUST_ID，然后查询一个带有 column 的表CUST_ID。CUST_ID查询中的所有引用都将引用列，而不是参数。如果参数用于某个目的，则它被掩盖了。
However, you really shouldn't be doing this. If two sessions call this procedure simultaneously and insert the resulting value into a new row, you'll have a primary key violation. This is the entire reason that sequences exist. As sequences are not transactional, multiple sessions that access the same sequence will get different values.
但是，您真的不应该这样做。如果两个会话同时调用此过程并将结果值插入新行，则会发生主键冲突。这就是序列存在的全部原因。由于序列不是事务性的，访问相同序列的多个会话将获得不同的值。
