Oracle Function更新表，如果记录为空则INSERT

本文介绍了Oracle Function更新表，如果记录为空则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,

如果记录基于一个值存在，则更新其余列

如果记录不存在，则根据提供的值插入一行.

我有这张桌子

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

推荐答案

如果记录基于一个值存在，则更新其余列

如果记录不存在，则根据提供的值插入一行.

您需要的只是 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, 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>