oracle sql存储过程procedure

简单记录一下oracle sql中存储过程写法。 1.无参数的简单procedure：

create or replace procedure PRO_TEST_DELETE  is
begin

  DELETE FROM Test T WHERE T.TESTNO = 123;
  commit;

end;

2.有输入参数的存储过程

create or replace procedure PRO_INPUT_TEST(testId in varchar2) is
begin
  delete  FROM Test T where t.testno = testId;

end;

如果要写select语句需要将查询结果赋给变量，没有into时会有编译错误

create or replace procedure PRO_INPUT_TEST(testId in varchar2) is
  r_no varchar2(100);
begin

  select t.testno into r_no FROM Test T where t.testno = testId;

end;

3.有输出参数的存储过程

create or replace procedure PRO_OUTPUT_TEST(testId in varchar2,testVer out varchar2) is
begin

  select t.testver into testVer FROM Test T where t.testno = testId;

end;

4.存储过程循环输出查询结果

create or replace procedure PRO_OUTPUT_LOOP(testId in varchar2) is
begin
  for testData in
  (select * FROM Test T where t.testno like %||testId||%) loop
   dbms_output.put_line(testData.testno);
   end loop;
end;

5.存储过程输出拼接的结果

create or replace procedure PRO_OUTPUT_LOOP(testId in varchar2) is
begin
  for testData in
  (select * FROM Test T where t.testno like %||testId||%) loop
   dbms_output.put_line(ID= ||testData.testno);
   end loop;
end;

6.存储过程根据查询结果输出不同结果

create or replace procedure PRO_OUTPUT_LOOP(testId in varchar2) is
begin
  for testData in (select *
                     FROM Test T
                    where t.testno like % || testId || %) loop
    if testData.Testver = 1 then
      dbms_output.put_line(ID= || testData.testno);
    else
      dbms_output.put_line(Ver= || testData.testno);
    end if;
  end loop;
end;

7.存储过程指定输出结果赋值

create or replace procedure PRO_OUTPUT_LOOP(testId in varchar2,
                                            new_ID out varchar2,
                                            old_ID out varchar2) is
begin
  for testData in (select *
                     FROM Test T
                    where t.testno like % || testId || %
                    and rownum <= 2
                    order by t.testno) loop
    if testData.Testver = 1 then
      new_ID := (ID= || testData.testno);
    else
      old_ID := (ID= || testData.testno);
    end if;
  end loop;
end;

8.调用存储过程 1.使用begin end调用

declare
  test_ID varchar2(20);
  new_ID varchar2(20);
  old_ID varchar2(20);

begin
   test_ID:=1; -- 为输入参数赋值
  -- Call the procedure
  PRO_OUTPUT_LOOP(test_ID, new_ID,old_ID);
  dbms_output.put_line(new_ID);
  dbms_output.put_line(old_ID);
end;

2.也可以在plsql等软件中直接右键编辑测试。