Oracle 12c SQL函数调用未按要求工作

Question

我在SQL中有一个函数，它将用户插入USERS。当我使用SELECT insert_users('user', 'email', 'hash') FROM dual;调用Oracle12c应用程序express中的函数时，它会运行，但会从异常返回FAIL。在SQL Developer中运行此SQL语句时也会发生同样的情况，但当我在SQL Developer直接运行或调试函数时，它会成功执行。所以我认为问题出在SQL语句中。那么，需要改变什么才能让它发挥作用呢？

SQL函数

create or replace FUNCTION insert_users(p_user_name in varchar2, 
p_user_email in varchar2, p_user_password in varchar2)
RETURN VARCHAR2 AS
p_salt varchar2(20) := '';
BEGIN
select dbms_random.string('P', 20) str
into p_salt
from dual;
INSERT INTO USERS(USER_ID, USER_NAME, USER_EMAIL, SALT, USER_PASSWORD)
VALUES (seq_users.nextval, p_user_name, p_user_email, p_salt, p_user_password);
return 'SUCCESS';
EXCEPTION
WHEN others THEN
RETURN 'FAIL';
END;

SQL调用

SELECT insert_users('user', 'email', 'hash') FROM dual;

PL/SQL块(直接从运行函数)

DECLARE
P_USER_NAME VARCHAR2(200);
P_USER_EMAIL VARCHAR2(200);
P_USER_PASSWORD VARCHAR2(200);
v_Return VARCHAR2(200);
BEGIN
P_USER_NAME := 'user';
P_USER_EMAIL := 'email';
P_USER_PASSWORD := 'hash';
v_Return := USER.INSERT_USERS(
P_USER_NAME => P_USER_NAME,
P_USER_EMAIL => P_USER_EMAIL,
P_USER_PASSWORD => P_USER_PASSWORD
);
:v_Return := v_Return;
--rollback; 
END;

Answer 1

如果你没有压缩异常，你会看到：

ORA-14551:无法在查询内执行DML操作

您的函数正在执行DML，即插入到您的表中。当从PL/SQL上下文运行时，这是可以的，尽管通常认为最好使用过程来实现这一点。但由于函数正在执行此操作，因此不能将其作为查询的一部分进行调用。

捕获和压缩错误通常被认为是一个错误。你所做的一切都是为了隐藏有用的信息。调用者不知道函数调用失败的原因，任何调查问题的人也不知道发生了什么。

一个更好的方法是有一个程序。如果插入有效，那没关系。如果发生任何类型的错误，让异常传播，客户端或调用方会看到它并知道实际出了什么问题。不能从查询中调用过程，但可以从匿名块中调用，也可以从围绕匿名块的SQL*Plus和SQL Developerexecute包装器中调用。

create or replace PROCEDURE insert_users(p_user_name in varchar2, 
p_user_email in varchar2, p_user_password in varchar2) AS
BEGIN
INSERT INTO USERS(USER_ID, USER_NAME, USER_EMAIL, SALT, USER_PASSWORD)
VALUES (seq_users.nextval, p_user_name, p_user_email, dbms_random.string('P', 20), p_user_password);
END;
/
Procedure INSERT_USERS compiled
EXEC insert_users('user', 'email', 'hash');
PL/SQL procedure successfully completed.
select * from users;
USER_ID USER_NAME            USER_EMAIL           SALT                 USER_PASSWORD                                                   
---------- -------------------- -------------------- -------------------- ----------------------------------------------------------------
1 user                 email                Er-U1zL-v0lP%1m*Tz&t hash                                                            

您实际上并不需要p_salt变量，您可以调用dbms_random作为插入的一部分，所以我已经删除了它。

不过，在存储密码之前，您可能需要使用salt对其进行散列。。。