Oracle Function更新表，如果记录为空则INSERT [英] Oracle Function to update a table, if the record is null then INSERT

问题描述

我在这里是新来的，而且是学习oracle的新手. 我正在尝试使一个oracle函数在表上执行任务.因此，即时通讯试图实现的是，当用户提供值时，它必须在表中检入，

I am new here and I am a newbie in learning oracle. I am trying to make an oracle function to perform a task on a table. So, what im trying to achieve is, when user supplied values, it has to check in the table,

1. 如果记录基于一个值存在，则更新其余列
2. 如果记录不存在，则根据提供的值插入一行.

我有这张桌子

CREATE TABLE WELTESADMIN.MST_ERC_UPD
(
  PROJECT_NAME    VARCHAR2(25 CHAR)             NOT NULL,
  HEAD_MARK       VARCHAR2(25 CHAR)             NOT NULL,
  COLI_NUM        VARCHAR2(30 CHAR)             NOT NULL,
  ONSITE_UPD_QTY  NUMBER(38)                    NOT NULL,
)

和SQL，

CREATE OR REPLACE PROCEDURE WELTESADMIN.SP_MST_ERC_UPD
(
    p_HEAD_MARK IN MST_ERC_UPD.HEAD_MARK%TYPE,
    p_PROJECT_NAME IN MST_ERC_UPD.PROJECT_NAME%TYPE,
    p_COLI_NUM IN MST_ERC_UPD.COLI_NUM%TYPE,
    p_ONSITE_UPD_QTY IN MST_ERC_UPD.ONSITE_UPD_QTY%TYPE
)
IS
BEGIN
IF (SELECT HEAD_MARK, PROJECT_NAME, COLI_NUM FROM MST_ERC_UPD) IS NULL
    THEN 
        INSERT INTO MST_ERC_UPD (HEAD_MARK, PROJECT_NAME, COLI_NUM)
    VALUES (p_HEAD_MARK, p_PROJECT_NAME,  p_COLI_NUM, p_ONSITE_UPD_QTY)
ELSE
UPDATE MST_ERC_UPD 
    SET 
        ONSITE_UPD_QTY = p_ONSITE_UPD_QTY
    WHERE
        HEAD_MARK = p_HEAD_MARK AND
        PROJECT_NAME = p_PROJECT_NAME AND
        COLI_NUM = p_COLI_NUM;
COMMIT;
END SP_MST_ERC_UPD;

所以我想做的是基于3个变量HEAD_MARK，PROJECT_NAME和COLI_NUM.当用户提交表单时，将传递4个值并在服务器中进行处理.如果数据库中存在这3个(HEAD_MARK，PROJECT_NAME，COLI_NUM)，则使用ONSITE_UPD_QTY插入数据库.如果存在，则仅更新ONSITE_UPD_QTY.

So what im trying to do is based on 3 variables HEAD_MARK, PROJECT_NAME and COLI_NUM. When user submit a form then 4 values are passed and do the processing in the server. If those 3 (HEAD_MARK, PROJECT_NAME, COLI_NUM)are present in the database then insert into database with the ONSITE_UPD_QTY. And if present then update the ONSITE_UPD_QTY only.

我只是在学习，所以无法在SQL Developer中使此过程正常工作.我打算在PHP中使用它，并在用户提交表单时调用此过程.请帮助我

I am just learning and i cant make this procedure work in SQL Developer. I am planning to use it in PHP and call this procedure when user submit the form. Please help me

推荐答案

1. 如果记录基于一个值存在，则更新其余列
2. 如果记录不存在，则根据提供的值插入一行.

您需要的只是 MERGE 语句.您的两个要求都可以一次完成.

All you need is MERGE statement. Both your requirements could be done at one go.

语法是-

MERGE INTO table_a a
  USING table_b b
    ON (a.column= b.column)
  WHEN MATCHED THEN
    UPDATE SET a.column= b.column
  WHEN NOT MATCHED THEN
    INSERT (column1, column2)
    VALUES (b.val1, b.val2);

更新示例，显示过程参数的USING子句

Update Example to show USING clause for procedure parameters

USING(
SELECT
p_HEAD_MARK hm, 
p_PROJECT_NAME pn, 
p_COLI_NUM cn, 
p_ONSITE_UPD_QTY ouq
FROM DUAL) s

将它们用作s.hm, s.pn, s.cn, s.ouq

更新2 一个完整的测试用例

比方说，我有一个没有行的表emp1.我创建了一个将ename作为输入的过程，将使用 MERGE 将其插入到emp表中.

Let's say I have a table emp1 with no rows. I create a procedure which takes ename as INPUT, which I will use to insert into emp table using MERGE.

SQL> SELECT * FROM emp1;

no rows selected

SQL>
SQL> CREATE OR REPLACE
  2  PROCEDURE p(
  3      p_ename IN VARCHAR2)
  4  AS
  5  BEGIN
  6    MERGE INTO emp1 e USING
  7    (SELECT p_ename AS ename FROM dual
  8    ) s ON(e.ename = s.ename)
  9  WHEN MATCHED THEN
 10    UPDATE SET e.empno = 100 WHEN NOT MATCHED THEN
 11    INSERT
 12      (ename
 13      ) VALUES
 14      (s.ename
 15      );
 16  END;
 17  /

Procedure created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> BEGIN
  2    p('SCOTT');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>

让我们看看是否插入了值.

Let's see if the value got inserted.

SQL> SELECT ename FROM emp1;

ENAME
----------
SCOTT

SQL>

这篇关于Oracle Function更新表，如果记录为空则INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！