oracle - SQL Developer 不会插入数据

Question

create or replace PROCEDURE ADD_TO_BLACKLIST(
    P_EMPLOYEE_USERNAME IN VARCHAR2,
    T_CURSOR OUT SYS_REFCURSOR
    )
AS 
BEGIN
    DECLARE
        E_COUNT PLS_INTEGER := 0;
    BEGIN
        SELECT COUNT(*) INTO E_COUNT FROM EXAMPLE_TABLE
        WHERE UPPER(EMPLOYEE_USERNAME) LIKE UPPER(P_EMPLOYEE_USERNAME)||'%';

        IF E_COUNT = 0 THEN
            INSERT INTO EXAMPLE_TABLE 
            (employee_number, employee_username) 
            SELECT EMPLOYEE_NUMBER, EMAIL FROM EXAMPLE_VIEW
            WHERE UPPER(EMAIL)=CONCAT(UPPER(P_EMPLOYEE_USERNAME), '@microsoft.com');
        ELSE
            UPDATE EXAMPLE_TABLE
            SET (EMPLOYEE_NUMBER, EMPLOYEE_USERNAME) =
                (SELECT EMPLOYEE_NUMBER, EMAIL FROM EXAMPLE_VIEW
                 WHERE UPPER(EMAIL) = CONCAT(UPPER(P_EMPLOYEE_USERNAME), '@microsoft.com'));

            COMMIT;
        END IF;

        OPEN T_CURSOR For
        SELECT * FROM EXAMPLE_VIEW
        WHERE  EMAIL LIKE CONCAT(UPPER(P_EMPLOYEE_USERNAME), '%');
    END;
END ADD_TO_BLACKLIST;

这可以编译，但是当我尝试使用有效的 P_EMPLOYEE_USERNAME（我已确认它在EXAMPLE_VIEW 中）对其进行测试时，我看不到任何数据被插入。

我是 PLSQL 的新手，不知道如何计算 E_COUNT 的值

Example_Table DDL 是

CREATE TABLE "Example_Table"
(   "EMPLOYEE_NUMBER" NUMBER NOT NULL ENABLE, 
    "EMPLOYEE_USERNAME" VARCHAR2(250 BYTE) NOT NULL ENABLE, 
    "ACCOUNT_STATUS" NUMBER DEFAULT 0, 
    "ACCOUNT_STATUS_LAST_UPDATE" TIMESTAMP (6) DEFAULT SYSDATE NOT NULL ENABLE, 
     CONSTRAINT "BOE_SAFEGAURD_PK" PRIMARY KEY ("EMPLOYEE_USERNAME"))

标签： oracleplsql

Answer 1

问题在下面一行，连接后您没有转换大小写。请修改并在下面尝试，

 WHERE UPPER(EMAIL) = UPPER(CONCAT(UPPER(P_EMPLOYEE_USERNAME), '@microsoft.com'));

编辑：为了证明理论，请在下面找到详细信息。

我已经对此进行了测试，并且可以正常工作，

DDL 创建表：

CREATE TABLE Example_Table
(    EMPLOYEE_NUMBER NUMBER NOT NULL ENABLE, 
    EMPLOYEE_USERNAME VARCHAR2(250 BYTE) NOT NULL ENABLE, 
    ACCOUNT_STATUS NUMBER DEFAULT 0, 
    ACCOUNT_STATUS_LAST_UPDATE TIMESTAMP (6) DEFAULT SYSDATE NOT NULL ENABLE, 
     CONSTRAINT BOE_SAFEGAURD_PK PRIMARY KEY (EMPLOYEE_USERNAME));

CREATE TABLE Example_view
(    EMPLOYEE_NUMBER NUMBER NOT NULL ENABLE, 
    EMAIL VARCHAR2(250 BYTE) NOT NULL ENABLE, 
    ACCOUNT_STATUS NUMBER DEFAULT 0, 
    ACCOUNT_STATUS_LAST_UPDATE TIMESTAMP (6) DEFAULT SYSDATE NOT NULL ENABLE 
     );

DML 将数据填充到将用于测试的 example_view。

insert into example_view values(1,'[e-mail]',1,sysdate);

修改了UPPER在连接右侧添加插入和更新条件的过程，并将 if 放置在commit后结束。一个好的代码应该只有一个提交，并且应该在exceptionmain 块之前的执行结束时开始..end堵塞。

create or replace PROCEDURE ADD_TO_BLACKLIST(
P_EMPLOYEE_USERNAME IN VARCHAR2,
T_CURSOR OUT SYS_REFCURSOR
)
AS 
BEGIN
DECLARE E_COUNT PLS_INTEGER := 0;
BEGIN
    SELECT COUNT(*) INTO E_COUNT FROM EXAMPLE_TABLE WHERE UPPER(EMPLOYEE_USERNAME) LIKE UPPER(P_EMPLOYEE_USERNAME)||'%';
    IF E_COUNT = 0 THEN
        INSERT INTO EXAMPLE_TABLE 
            (employee_number, employee_username) 
            SELECT EMPLOYEE_NUMBER, EMAIL FROM EXAMPLE_VIEW WHERE UPPER(EMAIL)=UPPER(CONCAT(UPPER(P_EMPLOYEE_USERNAME), '@microsoft.com'));
    ELSE
        UPDATE EXAMPLE_TABLE SET (EMPLOYEE_NUMBER, EMPLOYEE_USERNAME) = (SELECT EMPLOYEE_NUMBER, EMAIL FROM EXAMPLE_VIEW WHERE UPPER(EMAIL)=UPPER(CONCAT(UPPER(P_EMPLOYEE_USERNAME), '@microsoft.com')));

    END IF;

   COMMIT;

    OPEN T_CURSOR For
        SELECT * FROM EXAMPLE_VIEW WHERE EMAIL LIKE CONCAT(UPPER(P_EMPLOYEE_USERNAME), '%');
END;
END ADD_TO_BLACKLIST;

在调用过程的匿名块中，

DECLARE
T_CURSOR  SYS_REFCURSOR;

BEGIN 
ADD_TO_BLACKLIST('test',T_CURSOR);

end;

运行查询以检查是否插入了记录，

select * from example_table;

输出如下，