oracle存储过程1

与过程相比，存储过程相当于数据库里面的一个对象。如果编译出错，可以用show errors查看。

最简单的存储过程 eg：

create or replace procedure myproc1
as
  i number;
  begin
     i:=100;
     dbms_output.put_line('i='||i);
  end;
/

存储过程的执行： exec myproc1

当然，要想输出结果，别忘了 set serveroutput on   设置控制台可以打印。

 

过程的参数类型：

• IN：值传递，默认的

• IN OUT：带值进，带值出

• OUT：不带值进，带值出

 

下面一一举例说明。 注意下面的例子所采用的表都是oracle安装默认的4个表。

 

1.要求，可以传入部门的编号，部门的名称，部门的位置，之后调用此过程就可以完成部门的增加操作。

create or replace procedure myproc2(
dno dept.deptno%type,name dept.dname%type,dl dept.loc%type)
as
  cou number;
  begin
     select count(deptno) into cou from dept where deptno=dno;

     -- 如果部门编号已经存在，在不能够在插入
     if cou=0 then
       insert into dept(deptno,dname,loc) values (dno,name,dl);
       dbms_output.put_line('insert scuess');
     else
       dbms_output.put_line('isert fail');
     end if;
  end;
/

 

2.IN OUT：带值进，带值出

create or replace procedure myproc3(
dno in out dept.deptno%type,name dept.dname%type,dl dept.loc%type)
as
  cou number;
  begin
     select count(deptno) into cou from dept where deptno=dno;
     if cou=0 then
       insert into dept(deptno,dname,loc) values (dno,name,dl);
       dbms_output.put_line('insert scuess');

       -- 这里的数字是随便设置的，主要是用于下面打印出来
       dno:= 2;
     else
       dbms_output.put_line('isert fail');
       dno:= -2;
     end if;
  end;
/

--编写过程执行

declare deptno dept.deptno%type;
begin
   deptno:=12;
   myproc3(deptno,13,14);
   dbms_output.put_line(deptno);
end;
/

 

 

3.OUT：不带值进，带值出

create or replace procedure myproc4(dno out dept.deptno%type)
as
  i number;
  begin
    i:=dno;
    dno:=7;
    dbms_output.put_line(i);
  end;
/

 

--执行上面的存储过程

declare
deptno dept.deptno%type;
begin
deptno:=27;
myproc4(deptno);
dbms_output.put_line(deptno);
end;
/

 

 

4.利用游标，往dept10 、dept20 、dept30 中插入数据

--知识准备

create table dept10 as select * from dept where 1=2;    --新建一张与dept一样表结构的表
insert into dept10 select * from dept where deptno=10; --把dept中deptno=10的数据插入dept10 中

 

create or replace procedure myproc5
as
  cursor mycur1 is select * from dept;
  dno  dept%rowtype;
  begin
    for dno in mycur1 loop
      if dno.deptno=10 then
        insert into dept10 values(dno.deptno,dno.dname,dno.loc);
      elsif dno.deptno=20 then
        insert into dept20 values(dno.deptno,dno.dname,dno.loc);
      elsif dno.deptno=30 then
        insert into dept30 values(dno.deptno,dno.dname,dno.loc);
      end if;
    end loop;
  end;
/

 

 

5.编写一个存储过程，批量插入1000条数据（只插入ID为奇数的数据）

create table test1(i number(10));
create or replace procedure myproc6
as
  i number(10);
begin
  for i in 1..1000 loop
    if mod(i,2)=1 then
      insert into test1 values(i);
    end if;
  end loop;
end;
/

 

经过细读上面这几个关于存储过程的例子，我相信基本可以写存储过程了。