Oracle存储过程插入数据全为NULL，原因是什么？

我是Oracle新手，尝试通过存储过程向表中插入数据，但插入的内容全为NULL值。以下是我的存储过程代码：

create or replace NONEDITIONABLE PROCEDURE   OUTPATIENT_DLTE_PRE_ADMIN  (send_app IN VARCHAR, 
                                           send_facility IN VARCHAR ,
                                           receiving_app IN VARCHAR ,
                                           receiving_facility IN VARCHAR ,
                                           sendcreateddtm IN VARCHAR )
                                           
AS
    l_send_app VARCHAR(227);
    l_send_facility VARCHAR(227);
    l_receiving_app VARCHAR(227);
    l_receiving_facility VARCHAR(227);
    l_sendcreateddtm VARCHAR(40);

BEGIN
    INSERT INTO OPS_DLTE_PRE_ADMIN (SEND_APP, SEND_FACILITY,  
                                    RECEIVING_APP, RECEIVING_FACILITY,
                                    send_CREATE_DTTM)
    VALUES (l_send_app, l_send_facility,
            l_receiving_app, l_receiving_facility,
            l_sendcreateddtm);

我通过该存储过程插入数据，却在表中得到NULL值，请问这是什么原因？

原因分析 #

你定义的局部变量l_send_app、l_send_facility等从未被赋值，Oracle中未初始化的变量默认值为NULL，因此插入到表中的自然全是NULL值。

修正方案 #

方案1：直接使用传入参数插入（更简洁） #

无需额外定义局部变量，直接把传入的参数值插入表中：

create or replace NONEDITIONABLE PROCEDURE OUTPATIENT_DLTE_PRE_ADMIN  
(
    send_app IN VARCHAR, 
    send_facility IN VARCHAR ,
    receiving_app IN VARCHAR ,
    receiving_facility IN VARCHAR ,
    sendcreateddtm IN VARCHAR 
)
AS
BEGIN
    INSERT INTO OPS_DLTE_PRE_ADMIN 
    (SEND_APP, SEND_FACILITY, RECEIVING_APP, RECEIVING_FACILITY, send_CREATE_DTTM)
    VALUES 
    (send_app, send_facility, receiving_app, receiving_facility, sendcreateddtm);
END;
/

方案2：先给局部变量赋值再插入 #

如果确实需要使用局部变量，在定义时直接赋值或在BEGIN块内赋值：

create or replace NONEDITIONABLE PROCEDURE OUTPATIENT_DLTE_PRE_ADMIN  
(
    send_app IN VARCHAR, 
    send_facility IN VARCHAR ,
    receiving_app IN VARCHAR ,
    receiving_facility IN VARCHAR ,
    sendcreateddtm IN VARCHAR 
)
AS
    l_send_app VARCHAR(227) := send_app;
    l_send_facility VARCHAR(227) := send_facility;
    l_receiving_app VARCHAR(227) := receiving_app;
    l_receiving_facility VARCHAR(227) := receiving_facility;
    l_sendcreateddtm VARCHAR(40) := sendcreateddtm;
BEGIN
    INSERT INTO OPS_DLTE_PRE_ADMIN 
    (SEND_APP, SEND_FACILITY, RECEIVING_APP, RECEIVING_FACILITY, send_CREATE_DTTM)
    VALUES 
    (l_send_app, l_send_facility, l_receiving_app, l_receiving_facility, l_sendcreateddtm);
END;
/

额外建议：如果表中send_CREATE_DTTM字段是日期类型，建议把参数类型改为DATE而非VARCHAR，避免字符串转日期时出现格式不匹配的问题。

内容的提问来源于stack exchange，提问作者mahesh babu