C#调用Oracle PL/SQL匿名块

1. SCOTT工资3000，如果表中有记录将工资上涨到10000，如果没有，将插入新的记录到表中！

 2.附上C#代码，SQL语法嵌入到C#代码里面

            OracleConnection connect=new OracleConnection("Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =superdog)));User Id =scott; Password=today0925");
            connect.Open();
            OracleCommand command=connect.CreateCommand();
            string ename = "SCOTT";
            double sal = 10000;
            string sqlStr = $@"DECLARE
                               v_ename VARCHAR2(35):='{ename}';-- 员工名
                               v_sal NUMBER(5):={sal};-- 工资
                               v_count NUMBER(5);-- 记录数
                               BEGIN
                               SELECT count(*) INTO v_count FROM emp WHERE ename=v_ename;
                               IF v_count>0 THEN
                               UPDATE emp 
                               SET sal=v_sal
                               WHERE ename=v_ename;
                               ELSE
                               INSERT INTO emp
                               (empno,ename,job,mgr,hiredate,sal,deptno)
                               VALUES (7788,v_ename,'ANALYST',7566,TO_DATE('1987-04-19','yyyy-MM-dd'),v_sal,20);
                               END IF;
                               END;";
            command.CommandText = sqlStr;
            command.CommandType = System.Data.CommandType.Text;
            command.ExecuteNonQuery();

 3. 程序执行过后，SCOTT工资上涨到10000，老板要哭了，人工成本增加

 

4. 这样做的目的一是减少对数据库的查询，二是方便问题的排查，SQL简单明了