数据库存储过程示例

存储过程

• 使用存储过程来解决涨工资
  • 涨工资，总裁涨1000，经理涨800，其他人涨400。

伪代码：

ResultSet rs = “select empno,job from emp”;

While(rs.next()){

Int eno = rs.getInt(“empno”);

String job = rs.getString(“job”);

If(“PRESIDENT”.equals(job))

{

       update emp set sal = sal+1000;

}else if(“Manager”.equals(job))

{

       update emp set sal = sal+800;

}else

{

       update emp set sal = sal+400;

}

}

分析：那么上面这样的一个过程，使用存储过程(PL/SQL)就可以轻松解决。可以写很少的java代码。

①.查询emp表：7839的姓名和薪水并打印.

declare

pename emp.ename%type;

psal emp.sal%type;

begin

--得到7839的姓名和薪水

select ename,sal into pename,psal from emp where empno = 7839;

dbms_output.put_line(pename||'的薪水是'||psal);

end;

运行结果：KING的薪水是5000

②.使用记录型变量

记录型变量：取这个表里面的一行作为我们的这个类型，那么一行有多个列，那么就有多个类型，相当于一个数组。

多个数据类型作为的我们的数据类型。

set serveroutput on

declare

  emp_rec emp%rowtype;

  begin

  select * into  emp_rec from emp where empno=7839;

  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);

  end;

 /

③.从键盘接收一个数字(使用PL/SQL判断输入输出数字)

set serveroutput on

accept num prompt '请输入一个数字';

declare

  pnum number :=#

  begin

  if pnum = 0 then dbms_output.put_line('你输入的数字是0');

  elsif pnum = 1 then dbms_output.put_line('你输入的数字是1');

  else dbms_output.put_line('其他数字');

  end if;

  end;

  /

运行结果：你输入的数字是1

④.输出1~10的数字

set serveroutput on

 declare

   pnum number :=1;

   begin

 loop

  exit when pnum>10;

 dbms_output.put_line(pnum);

  pnum := pnum+1;

  end loop;

end;

• 光标的使用

光标的作用：用于存储一个查询返回的多行数据。

①.查询所有人的薪水：

set serveroutput on

declare

cursor cemp is select ename,sal from emp;

pename emp.ename%type;

psal emp.sal%type;

begin

  open cemp;

  loop

  fetch cemp into pename,psal; 

  exit when cemp%notfound;

  dbms_output.put_line(pename||'的薪水是'||psal); 

  end loop;

end;

运行结果：

SMITH的薪水是800

ALLEN的薪水是1600

WARD的薪水是1250

JONES的薪水是2975

MARTIN的薪水是1250

BLAKE的薪水是2850

CLARK的薪水是2450

SCOTT的薪水是3000

KING的薪水是5000

TURNER的薪水是1500

ADAMS的薪水是1100

JAMES的薪水是950

FORD的薪水是3000

MILLER的薪水是1300

②.使用光标：涨工资，总裁涨1000，经理涨800，其他人涨400

set serveroutput on

declare

cursor cemp is select empno,job[h1]  from emp;

pno emp.empno%type;

pjob emp.job%type;

begin

  open cemp;

  loop

    fetch cemp into pno,pjob;

    exit when cemp%notfound; 

     -- 判断职位

  if pjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pno;

  elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=pno;

  else update emp set sal = sal +400 where empno=pno;

  end if;

  end loop;

  close cemp;

end;

③.定义带参数的光标(查询并打印某个部门中员工的姓名)

set serveroutput on

declare

 cursor cemp(dno number) is select ename from emp where empno = dno;

 pname emp.ename%type;

begin

open cemp(7369);//传递参数7369

loop

  fetch cemp into pname;

  exit when cemp%notfound; 

  dbms_output.put_line(pname);

end loop;

close cemp;

end;

运行结果：

SMITH

④.查询并打印30号部门员工姓名。

  declare

  cursor cemp(dno number) is select ename from emp where deptno = dno;

  dename emp.ename%type;

  begin

    open cemp(30);

    loop

      fetch cemp into dename;

      exit when cemp%notfound;

      dbms_output.put_line(dename);

    end loop;

    close cemp;

    end;

 

⑤.为部门号为10的员工涨工资

declare

  cursor cemp(dno number) is select deptno from emp where deptno = dno;

  dempno emp.deptno%type;

  begin

    open cemp(10);

    fetch cemp into dempno;

    update emp set sal= sal*1.1 where deptno=dempno;

    close cemp;

  end;

• 例外的使用

• 被0除例外

declare

  pnum number;

  begin

    pnum := 1/0;

    exception

      when zero_divide then dbms_output.put_line('1:0不能做被除数');

      dbms_output.put_line('2:0不能做被除数');

      when value_error then dbms_output.put_line('算数或者替换错误');

      when others then dbms_output.put_line('其他例外');

  end;

运行结果：

1:0不能做被除数

2:0不能做被除数

• 查询并打印50号部门的员工姓名(测试异常：原因：数据表里面没有50号部门)

declare

  cursor cemp is select ename from emp where deptno=50[h2] ;

  pename emp.ename%type; 

  no_emp_found[h3]  exception;

  begin

    open cemp;

    fetch cemp into pename;

    if cemp%notfound then    

      raise no_emp_found[h4] ;

    end if;

    close cemp;   

    exception

      when no_emp_found[h5]  then dbms_output.put_line('没有找到员工');

      when others then dbms_output.put_line('其他例外');

end;

运行结果：

没有找到员工

• 存储过程存储函数

 

---

 [h1]必须是数据库的字段列名。

 [h2]由于数据库没有部门号50这个部门，那么就会抛出异常，我们这里手动自定义一个异常，然后抛出，然后再下面捕获。

 [h3]定义异常

 [h4]抛出异常

 [h5]捕获异常