【oracle】存储过程执行增删改&IN/OUT参数

1、准备一个测试表

2、添加数据

1、写一个添加数据的sp（包含IN/OUT参数）

create or replace procedure SP_test_wen(
fun      in VARCHAR2,
p_empno   in VARCHAR2,
p_name   in VARCHAR2,
p_ip      in VARCHAR2,
p_opid    in VARCHAR2,
p_Res  out VARCHAR2
)
as 
    v_Count number;
begin
    --添加数据代码块
     if fun = 'add' then
        --先判断工号是否存在
        select count(empno) into v_Count from test_wen where empno = p_empno;
        if v_Count > 0 then
            p_Res := 'NO';
        else
            insert into test_wen
            select p_empno,p_name,p_ip,p_opid,to_char(SYSDATE,'yyyymmddHH24miss') from dual;
            p_Res := 'OK';
            commit;--一定要commit
        end if;
     end if;
end;

备注：

IN参数说明：fun---不同逻辑的代码块；p_empno到p_opid是---要在表里塞的数据

OUT参数说明：p_Res---执行结果成功或失败状态；

注意：执行操作后一定要记得commit提交

2、执行添加数据的sp

set serveroutput on;
declare 
--out 参数
  res varchar2(20);
begin
  SP_test_wen('add','16812068','哆哆','10.11.221','16812059',res);
  --
  dbms_output.put_line('res='||res);
end;

备注：

set serveroutput on;--output服务器打开，就可以用dbms_output.put_line输出out参数
set serveroutput off;--output服务器关闭，out参数不能输出

3、执行结果

成功结果：

失败结果：

 4、查看表数据

3、修改数据

1、写一个修改数据的sp（包含IN/OUT参数）

create or replace procedure SP_test_wen(
fun      in VARCHAR2,
p_empno   in VARCHAR2,
p_name   in VARCHAR2,
p_ip      in VARCHAR2,
p_opid    in VARCHAR2,
p_Res  out VARCHAR2
)
as 
    v_Count number;
begin
      --修改数据代码块
      --修改数据要给表数据设置一个主键，
     if fun = 'update' then
        --工号参数不能为空
        if p_empno is not null then
          --先判断主键是否存在
            select count(empno) into v_Count from test_wen where empno = p_empno;
            if v_Count > 0 then
                --存在就修改
                update test_wen set name = p_name , ip = p_ip ,opid = p_opid ,trandatetime = to_char(SYSDATE,'yyyymmddHH24miss')
                where empno = p_empno;
                commit;
                p_Res := 'OK';
            else
                --不存在就直接返回错误信息即可
                p_Res := 'ON';

            end if;
        else
            --参数不能为空
             p_Res := 'ON';
        end if;
     end if;
end;

备注：

IN参数说明：fun---不同逻辑的代码块；p_empno到p_opid是---要修改的数据

OUT参数说明：p_Res---执行结果成功或失败状态；

注意：执行操作后一定要记得commit提交

2、执行修改数据的sp

set serveroutput on;
declare 
--out 参数
  res varchar2(20);
begin
  SP_test_wen('update','16812061','张小三','10.11.221','16812059',res);
  --
  dbms_output.put_line('res='||res);
end;

3、执行结果

成功结果

4、查看表数据

4、删除数据

1、写一个删除数据的sp

create or replace procedure SP_test_wen(
fun      in VARCHAR2,
p_empno   in VARCHAR2,
p_name   in VARCHAR2,
p_ip      in VARCHAR2,
p_opid    in VARCHAR2,
p_Res  out VARCHAR2
)
as 
    v_Count number;
begin
      --删除数据代码块
     if fun = 'delete' then
        --工号参数不能为空
        if p_empno is not null then
          --先判断主键是否存在
            select count(empno) into v_Count from test_wen where empno = p_empno;
            if v_Count > 0 then
                --存在就删除
                delete test_wen
                where empno = p_empno and;
                commit;
                p_Res := 'OK';
            else
                --不存在就直接返回错误信息即可
                p_Res := 'ON';
            end if;
        else
            --参数不能为空
             p_Res := 'ON';
        end if;
     end if;
end;

备注：

IN参数说明：fun---不同逻辑的代码块；p_empno到p_opid是---要删除的数据（ip&opid可以传空字符串）

OUT参数说明：p_Res---执行结果成功或失败状态；

注意：执行操作后一定要记得commit提交

2、执行删除数据的sp

set serveroutput on;
declare 
--out 参数
  res varchar2(20);
begin
  SP_test_wen('delete','16812059','','','',res);
  --
  dbms_output.put_line('res='||res);
end;

3、执行结果

成功结果

4、查看表数据

工号16812059的数据已被删除