oracle----块编程

1.块的构成：---这里不是存储过程哈。。。。
定义部分，执行部分，例外处理部分

declear --可选  相当于java   int a=1;定义部分啦

begin     --这里其实可以看成静态块，动态块，方法块 看成{ } 就可以了

execption --可选  在块里面当然可以try catch

end;     


2.实例2----打印hello wrold

set serveroutput on --打开输出选项
begin
  dbms_output.put_line('hello world'); //dbms_output是一个包，put_line是一个过程
end;


3.包含定义部分和执行部分
declare
  v_ename varchar2(5);   //相当于String ename;
  v_sal   number(7,2);
begin
  select ename ,sal into v_ename,v_sal from emp where empno=&no;
  dbms_output.put_line('雇员名:'||v_ename||'  工资'||v_sal);  
end;

&表示要接收从控制台输入的变量

注意：这里变量的目的是在多个执行部分可以用到，他可以跨越多条语句

------- 加上例外
注意：如果在控制台输入的参数，比如说empno=77在表里面查不到，那么就会将空值放到
变量里面去，那么会抛异常，注意，oracle中的exception块相当于java中的catch块，他不需要写try块
declare
  v_ename varchar2(5);   //相当于String ename;
  v_sal   number(7,2);
begin
  select ename ,sal into v_ename,v_sal from emp where empno=&no;
  dbms_output.put_line('雇员名:'||v_ename||'  工资'||v_sal);  
exception
when no_data_found then
dbms_output.put_line('朋友，你的编号输入有误');  
end;


4。块编程简单分类： 过程(存储过程),函数，触发器，包。也就是说这四种东西里面都有块


5。命名规范：
1。当定义变量时，建议用v_作为前缀v_sal
2.当定义常量，建议用c_做前缀c_rate
3.当定义游标时，建议用_cursot作为后缀，emp_cursor
4.当定义例外时，建议用e_作为前缀 e_error

6。如何调用
在数据库中可以这样调用： 
     exec sp_pro1(参数1，参数2)
或者
     call sp_pro1(参数1，参数2)
但是在java中调用存储过程只能用call.....

-------------------------存储过程----------------------------------------------------------
1.创建存储过程--存储过程中有块结构
create or replace procedure sp_pro1 is
 begin
  insert into mytest ('zwz','m1234');
 end;
 /   --斜杠的意思是执行

 但是出错了，我们通过show error可以查看错误信息
 
 SQL Statement ignored ,缺少select 关键字
 
 意思就是说我们这条sql少了些关键字，仔细观察，原来少了 values这个关键字
 
 改：create or replace procedure sp_pro1 is
 begin
  insert into mytest values('zwz','m1234');
 end;
 / 

2。// 注意，不应该指定类型大小 newSal number相当于 int newSal ,newSal number(3,2)相当于 Float f = new Float(); //分配空间了哈
create procedure sp_pro3(spName varchar2,newSal number) is
--下面就是按照块的规则写就是了
begin
  update emp set sal=newSal where ename=spName;
end;

--------------------输入与输出参数-------------------------------------------------
3。单个结果返回
//如果使用了out,那么就可以把它当做变量来使用，into到变量里面，在java中就可以拿到了
create or replace procedure sp_pro8
(spno in number,spName out varchar2) is
begin
 select ename into spName from emp where empno=spno; 
end;
在java中这样来拿值
CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");
cs.setInt(1,7788);//给第一个参数赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
String name = cs.getString(2); //得到第二个参数的返回值


4。多结果集 ---使用游标，其实就是ResultSet

----返回一个List,而不是单个数据
由于oracle存储过程没有返回值，他的所有返回值都是通过out参数来替代的，
列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用package了，所以要分成两部分

create or replace package test1 as 
type test_cursor is ref cursor;
end test1;  //创建了一个包test1,在包里面声明了一个游标类型 test_cursor

create or replace package sp_pro9
(spNo in number,p_cursor out test1.test_cursor) is
begin
   open p_cursor for select * from emp where deptno = spNo;
end;

--如何在java中调用游标
CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");
cs.setInt(1,10);
cs.registeroutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
cs.execute();

ResultSet rs = (ResultSet)cs.getObject(2); 


----------------------if/else--------------------------------------------------------
理论：

条件分支
if---then  对应  if(){}  其实then就相当于java中的{}
if---then--else 对应
 if(){
 }else{
 }
if---then--elsif---else  
对应 if(){
} else if(){
} else{}

1。if---then 例子

编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000,就给改雇员工资增加10%
create or replace procedure pro_sp4(spName varchar2) is

v_sal emp.sal%type;  --%type是变量定义的一种方法,v_StudentID students.id%type意思是：变量v_StudentID与表students中id字段的数据类型一致
begin
select sal into v_sal from emp where ename=spName;
if v_sal <2000 then
 update emp set sal=sal+sal*1.1 where ename=spName;
end if;
end;

2。if---then--else例子
编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100;如果补助为0
就把补助设为200
//create or replace procedure pro_sp4(spName in varchar2) is 这里比下面多了一个in,这个in是可以省略的，意思是可以输入
create or replace procedure pro_sp4(spName varchar2) is

v_comm emp.comm%type; 
begin
select comm into v_comm from emp where ename=spName;
if v_comm <> 0 then
 update emp set comm=comm+100 where ename=spName;
else
  update emp set comm=comm+200 where ename=spName;
end if;
end;

3。if---then--elsif---else例子

编写一个过程，可以输入一个雇员编号，如果该雇员的职位是president,就给他的工资增加1000,如果该雇员的职位是manager就给他的工资加500，其他雇员加200

create or replace procedure sp_pro6(spNo number) is
v_job emp.job%type;
begin

 select job into v_job from emp where empno=spNo;
 if v_job = 'president' then
   update emp set sal = sal+1000 where empno = spNo;
 elsif v_job='manager' then
   update emp set sal = sal+500 where empno = spNo;
 else
   update emp set sal = sal + 200 where empno = spNo;
 end if;
 
end;


-------------------case when--then end case-----------------------------------------------------------------
SQL> declare
  2   v_sal emp.sal%type;
  3   v_ename emp.ename%type;
  4  begin
  5    select ename,sal into v_ename,v_sal from emp
  6    where empno=&emp_no;
  7   case
  8      when v_sal<1000 then
  9       update emp
 10        set comm=100
 11       where ename=v_ename;
 12      when v_sal<2000 then
 13       update emp
 14        set comm=80
 15        where ename=v_ename;
 16      when v_sal<6000 then
 17        update emp
 18         set comm=50
 19        where ename=v_ename;
 20      end case;
 21  end;
 22  /
原值    6:   where empno=&emp_no;
新值    6:   where empno=7369;



------------------------循环----------------------------------------------------

理论：
循环：
for    ---for 

do{}       ---相当于loop---exit when
while();

while(){}  ---相当于 while ---loop

1。loop---exit when例子

编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加
create or replace procedure sp_pro7(spName varchar2) is
--定义
v_num number := 1;
--执行
begin
  loop
    insert into users values(v_num,spName);
    --判断循环是否退出
    exit when v_num=10;
    --自增
    v_num:=v_num+1;
  end loop;

end;

2。while ---loop例子

create or replace procedure sp_pro7(spName varchar2) is
--定义
v_num number := 1;
--执行
begin
  while v_num <=20 loop
     insert into users values(v_num,spName);
     v_num = v_num+1;
  end loop;

end;

3。for例子

--for的好处是不用定义变量，而且也是隐含的i++ ,相当于java中的增强for
begin 
  for i in reverse 1..10 loop
    insert into users values(i,'zwz');
  end loop;  
end;

SQL> declare result int;
  2  begin
  3    for i in 1..5 loop
  4      for j in 1..5 loop
  5        result:=i*j;
  6       exit when result=100;
  7       exit when result=50;
  8      end loop;
  9     dbms_output.put_line(result);
 10    end loop;
 11  end;
 12  /

--顺序控制语句---goto(不建议使用，破坏了封装，相当于汇编语言的特性),null
SQL> declare i int:=1;
  2  begin
  3    loop
  4      insert into temp values(i);
  5      if i=20 then  goto end_loop;  --这里goto 到end_loop标签下面
  6      end if;
  7      i:=i+1;
  8     end loop;
  9    <<end_loop>> ---直接跳到这个标签这里了
 10   dbms_output.put_line('循环结束');
 11  end;
 12  /



null语句只是为了提高程序的可读性
SQL> run
  1  declare
  2  v_sal emp.sal%type;
  3  v_ename emp.ename%type;
  4  begin
  5  select ename,sal into v_ename,v_sal from emp
  6   where empno=&emp_no;
  7   if v_sal<3000 then
  8    update emp
  9     set comm=sal*0.1
 10    where ename=v_ename;
 11  else
 12    null; --否则什么也不做
 13  end if;
 14* end;
if v_sal < 3000 then
.....;
else 
 null;   --其实这里的else null;完全可以不用
 end if;  

------------------------execption-------------------------------------------------------

SQL> set serveroutput on
SQL> declare
  2    emp_name emp.ename%type;
  3  begin
  4    select ename into emp_name from emp;
  5  EXCEPTION
  6    when too_many_rows then
  7     dbms_output.put_line('出错了，不允许返回多行!');
  8  end;
  9  /
when too_many_rows then  ---返回多行
when no_data_found then   ---数据不存在
 when value_error then   ----值不合法，比如我要一个int你给我一个varchar,我要5位，你给我7位 dbms_output.put_line('要存储在姓名中的值过长');


自定义异常
SQL>  declare
  2    sal_error  exception; ---申明异常
  3    emp_comm   emp.comm%type;
  4  begin
  5   select NVL(comm,0) into emp_comm from emp
  6    where empno=&emp_no;
  7   if emp_comm>1000 then
  8     raise sal_error;  ----抛出
  9   end if;
 10  EXCEPTION
 11    when sal_error then  ---接住
 12      dbms_output.put_line('奖金超出范围');
 13* end;

引发应用程序错误：
SQL> declare
  2     emp_no  emp.empno%type;
  3     emp_comm emp.comm%type;
  4     comm_exception  exception;
  5  begin
  6    emp_no:=7369;
  7    select nvl(comm,0) into emp_comm from emp
  8    where empno=emp_no;
  9   if emp_comm=0 then
 10     raise comm_exception;
 11   else
 12     dbms_output.put_line('奖金为:' || emp_comm );
 13   end if;
 14  EXCEPTION
 15    when comm_exception then
 16       raise_application_error(-20001,'未指定奖金的值'); --直接抛给java
 17  end;
 18  /
在oracle端的效果是
*
ERROR 位于第 1 行: 
ORA-20001: 未指定奖金的值
ORA-06512: 在line 16