oracle存储过程的知识总结

1.基本结构 

CREATE OR REPLACE PROCEDURE 存储过程名字 

( 

    参数1 IN NUMBER, 

    参数2 IN NUMBER 

) IS 

变量1 INTEGER :=0; 

变量2 DATE; 

BEGIN 

END 存储过程名字 

2.SELECT INTO STATEMENT 

  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 

  记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 

  例子： 

  BEGIN 

  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; 

  EXCEPTION 

  WHEN NO_DATA_FOUND THEN 

      xxxx; 

  END; 

  ... 

3.IF 判断 

  IF V_TEST=1 THEN 

    BEGIN 

       do something 

    END; 

  END IF; 

4.while 循环 

  WHILE V_TEST=1 LOOP 

  BEGIN 

XXXX 

  END; 

  END LOOP; 

5.变量赋值 

  V_TEST := 123; 

6.用for in 使用cursor 

  ... 

  IS 

  CURSOR cur IS SELECT * FROM xxx; 

  BEGIN 

FOR cur_result in cur LOOP 

  BEGIN 

   V_SUM :=cur_result.列名1+cur_result.列名2 

  END; 

END LOOP; 

  END; 

7.带参数的cursor 

  CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; 

  OPEN C_USER(变量值); 

  LOOP 

FETCH C_USER INTO V_NAME; 

EXIT FETCH C_USER%NOTFOUND; 

    do something 

  END LOOP; 

  CLOSE C_USER; 

8.用pl/sql developer debug 

  连接数据库后建立一个Test WINDOW 

  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试 

9. 使用serveroutput和dbms_output函数
首先把输出工具打开：set serveroutput on size 100000
在sql下输入如下：
SQL> declare
  2  l number;
  3  begin
  4  for l in reverse 1..5
  5  loop
  6  dbms_output.put_line(l);
  7  end loop;
  8  end;
     /

Oracle存储过程可以有无参数存储过程和带参数存储过程。 
一、无参程序过程语法

1 create or replace procedure NoParPro
2 as  ;
3 begin
4 ;
5 exception     //存储过程异常
6     ;
7 end;
create or replace procedure stu_proc as

Pname varchar2(25);//知道参数类型

Begin

Select sname into pname from student where sno=1;

Dbms_output.put_line(pname);

End;

或者

create or replace procedure stu_proc as

Pname student.sname%type;//不知道参数类型

Begin

Select sname into pname from student where sno=1;

Dbms_output.put_line(pname);

End;

        二、带参存储过程实例

 1 create or replace procedure queryempname(sfindno emp.empno%type) as
 2        sName emp.ename%type;
 3        sjob emp.job%type;
 4 begin
 5        ....
 7 exception
          ....
8end;
仅有输入参数的过程

create or replace procedure stu_proc1(pno in student.sno%type) as

pname varchar2(25);

begin

select sname into pname from student where sno=pno;

dbms_output.put_line(pname);

end;

仅有输出参数的存储过程

Create or replace procedure stu_proc2(pname out student.sname%type) as

begin

select sname into pname from student where sno=1;

dbms_output.put_line(pname);

end;

有输入\输出参数的存储过程

Create or replace procedure stu_proc3

(pno in student.sno%type,pname out student.sname%type) as

Begin

Select sname into pname from student where sno=pno;

Dbms_output.put_line(pname);

End;

    三、 带参数存储过程含赋值方式 

 1 create or replace procedure runbyparmeters  (isal in emp.sal%type, 
                            sname out varchar,sjob in out varchar)
 2  as icount number;
 3  begin
 4       select count(*) into icount from emp where sal>isal and job=sjob;
 5       if icount=1 then
 6         ....
 9       else
10         ....
12       end if;
13  exception
14       when too_many_rows then
15       DBMS_OUTPUT.PUT_LINE('返回值多于1行');
16       when others then
17       DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
18  end;
19 

存储过程事物处理实例

 Create or replace procedure stu_proc8

Is

Begin

Insert into student values(102,’sky’);

Savepoint savepoint1;

Insert into student values(102,’good’);

Dbms_output.put_line(‘error’);

Update student set sno=103 where sname=’good’;

Commit;

Exception

  When dup_val_on_index then

Rollback to savepoint savepoint1;

RAISE_APPLICATION_ERROR(-20010,’ERROR:违反唯一索引约束！’);

End;

四、在Oracle中对存储过程的调用
  过程调用方式一

 1 declare
 2        realsal emp.sal%type;
 3        realname varchar(40);
 4        realjob varchar(40);
 5  begin   //存储过程调用开始
 6        realsal:=1100;
 7        realname:='';
 8        realjob:='CLERK';
 9        runbyparmeters(realsal,realname,realjob);     －－必须按顺序
10        DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
11  END;  //过程调用结束
12 

  过程调用方式二

 1 declare
 2       realsal emp.sal%type;
 3       realname varchar(40);
 4       realjob varchar(40);
 5 begin    //过程调用开始
 6       realsal:=1100;
 7       realname:='';
 8       realjob:='CLERK';
 9       runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);  －－指定值对应变量顺序可变
10       DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
11 END;  //过程调用结束
12 

1. create or replace procedure GetRecords(

name_out out varchar2,

age_in in varchar2) 

as     

begin

select NAME into name_out from test where AGE = age_in;     

end;     

   

2.create or replace procedure insertRecord(

UserID in varchar2, 

UserName in varchar2,

UserAge in varchar2) 

is    

begin    

 insert into test values (UserID, UserName, UserAge);    

end;

1、用来插入大量测试数据的存储过程
CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST
(
ST_NUM        IN     NUMBER,
ED_NUM        IN     NUMBER
)
IS
BEGIN
declare
       i   number;
begin
FOR i IN ST_NUM..ED_NUM LOOP
INSERT INTO tb values(i,i,'3','3','3',100,'0');
END LOOP;
end;
END;

运行：
sql>execute INSERTAMOUNTTEST(1,45000)   -- 一次插入45000条测试数据

2、从存储过程中返回值
create or replace procedure spaddflowdate
(
varAppTypeId               in varchar2, 
varFlowId                  in varchar2,
DateLength                 in number,
ReturnValue                out number    --返回值
)
is
begin
insert into td values(varAppTypeId,varFlowId,DateLength)
returning 1 into ReturnValue;   --返回值
commit;
exception
when others then
rollback;
end;

存储过程的执行
sql>variable testvalue  number;
sql>execute spaddflowdate('v','v',2,:testvalue);
sql>print
就可以看到执行结果

 

3、用包实现存储过程返回游标：
create  or  replace  package  test_p  
as  
 
type  outList  is  ref  cursor;  
 
PROCEDURE  getinfor(taxpayerList  out  outList);  
 
end  test_p;  
/  

create  or  replace  package  body  test_p  as  PROCEDURE  getinfor(taxpayerList out  outList)  is  begin  
      OPEN  taxpayerList    FOR  select  *  from
                        td where tag='0';  
 
end  getinfor;  
 
end  test_p;  
/  
运行： 
set  serverout  on;    --将输出工具打开
variable  x  refcursor;  
execute test_p.getinfor(:x);

exec  test_p.getinfor(:x);
print  x;  
drop package test_p;

完整的实例

create table employee(

id varchar2(11),

name varchar2(11)

)

insert into employee(id,name) values('12','lisi')

create or replace procedure get_name(v_id out varchar2, v_name in varchar2) as  

begin  

  select id into v_id from employee where name=v_name;

  if v_id='11' then 

   for i in 1..9 loop 

  dbms_output.put_line('success'); 

 end loop;

  else

    dbms_output.put_line('error');

    end if;

exception   

  when no_data_found then  

    raise_application_error(-20001, '你输入的ID不存在！');   

end get_name;