Oracle 插入was was函数Select给出错误

askTom 2018-10-05

357

问题描述

嗨，问汤姆团队，

下面的select查询对我来说工作正常，但是无法使用insert语句插入结果。，请给我一些建议

WITH FUNCTION T11 (P_A1 VARCHAR2) RETURN NUMBER  
IS   
BEGIN  
IF P_A1 = 'A' THEN RETURN 10;   
ELSE  
RETURN 100;   
END IF;  
END;  
SELECT T11('A1') FROM DUAL;  
复制

现在，上述查询的输出结果正在尝试插入到表中

CREATE TABLE T1 (A1 NUMBER); 复制

所以我写了下面的查询插入这个输出到表T1，但得到错误:

Insert statement tried 1

INSERT INTO T1 (A1)  
WITH FUNCTION T11 (P_A1 VARCHAR2) RETURN NUMBER  
IS   
BEGIN  
IF P_A1 = 'A' THEN RETURN 10;   
ELSE  
RETURN 100;   
END IF;  
END;  
SELECT T11('A1') FROM DUAL;  
复制

Error for above insert:

ORA-00904: "T11": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 12 Column: 8

Insert statement tried 2

WITH FUNCTION T11 (P_A1 VARCHAR2) RETURN NUMBER  
IS   
BEGIN  
IF P_A1 = 'A' THEN RETURN 10;   
ELSE  
RETURN 100;   
END IF;  
END;  
INSERT INTO T1 (A1)  
SELECT T11('A1') FROM DUAL;复制

Error for above insert option 2

ORA-00928: missing SELECT keyword
00928. 00000 - "missing SELECT keyword"
*Cause:
*Action:
Error at Line: 12 Column: 1

专家解答

您需要DML的WITH_PLSQL提示

SQL> WITH FUNCTION T11 (P_A1 VARCHAR2) RETURN NUMBER IS
  2  BEGIN
  3  IF P_A1 = 'A' THEN RETURN 10;
  4  ELSE
  5  RETURN 100;
  6  END IF;
  7  END;
  8  SELECT T11('A1') FROM DUAL;
  9  /

 T11('A1')
----------
       100

1 row selected.

SQL>
SQL> CREATE TABLE T1 (A1 NUMBER);

Table created.

SQL>
SQL> INSERT INTO T1 (A1)
  2  WITH FUNCTION T11 (P_A1 VARCHAR2) RETURN NUMBER
  3  IS
  4  BEGIN
  5  IF P_A1 = 'A' THEN RETURN 10;
  6  ELSE
  7  RETURN 100;
  8  END IF;
  9  END;
 10  SELECT T11('A1') FROM DUAL;
 11  /
WITH FUNCTION T11 (P_A1 VARCHAR2) RETURN NUMBER
*
ERROR at line 2:
ORA-32034: unsupported use of WITH clause


SQL>
SQL> INSERT /*+ WITH_PLSQL */  INTO T1 (A1)
  2  WITH FUNCTION T11 (P_A1 VARCHAR2) RETURN NUMBER
  3  IS
  4  BEGIN
  5  IF P_A1 = 'A' THEN RETURN 10;
  6  ELSE
  7  RETURN 100;
  8  END IF;
  9  END;
 10  SELECT T11('A1') FROM DUAL;
 11  /

1 row created.
复制

oracle asktom

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 插入was was函数Select给出错误

问题描述

专家解答

评论

相关阅读