oracle 存储过程

存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
一个最简单的存储过程必须包含以下关键字：create,procedure,存储过程名称,IA/AS,PL/SQLB标准执行语句

create  --存储过程头部区域开始
or replace --可选表示如果数据库中已存在一条或者相同得名称的存储过程就把它替换掉
procedure 
proc_emp_create  --存储过程名称 procedure_name
(
empno number,enmae varchar2,job varchar2,mgr varchar2,hiredate date,sal number,common number,deptno number
)  --parameter_declaration 声明参数(不需要写长度),存储过程头部区域结束
as 
--声明区域，不需要声明变量可以不写
begin --PL/SQL标准执行语句
  insert into emp values (empno,enmae, job, mgr, hiredate, sal, common, deptno);
end;

既然存储过程已经写好并编译完成了，接下来就是使用我们创建的存储过程了。使用存储过程有两种方法：

 第一种方法是直接在SQL窗口中，执行SQL语句

begin
  proc_emp_create(7778,'Alexander','analyst',6789,sysdate,4321,'',20);
end;

 第二种方法是在命令窗口中使用execute命令

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as scott
 
SQL> execute   proc_emp_create(7778,'Alexander','analyst',6789,sysdate,4321,'',20);
 
PL/SQL procedure successfully completed
 
SQL> 

单条数据查询：

create or replace procedure proc_emp_read
as 
v_no number;--声明变量
begin 
  select count(empno) into v_no from emp;--在执行代码块里面查询一定要使用into赋值
  dbms_output.put_line(v_no);
end;

 

参数传入模式: PL/SQL存储过程中有三种传参模式in,out,in out，默认情况下（不写）为in模式

--in,out,in out 模式测试
create or replace procedure proc_param_demo(p1 in number,p2 out number,p3 in out number) as
begin
  dbms_output.put_line('测试in 模式存储过程中p1的值为：' ||p1)； --标准输出
  dbms_output.put_line('测试out 模式存储过程中p2的值为：' ||p2');
  dbms_output.put_line('测试in out 模式存储过程中p3的值为：' ||p3');
  --p1 :=100; --报错
  p2 := 100; -- := PL/SQL标准赋值
  p3 :=100;
end;

调试完错误之后，调用上面的存储过程，这次调用我们不直接把值传递给存储过程的参数。所以需要使用declare关键字来声明变量，然后就可以在执行区域才可以使用这些变量

declare --声明区域
p1 number;
p2 number;
p3 number;
begin
  p1 := 10;
  p2 := 10;
  p3 := 10;
  proc_param_demo(p1,p2,p3);
  dbms_output.put_line('测试in    模式存储过程后p1的值为：'||p1);
  dbms_output.put_line('测试out   模式存储过程后p2的值为：'||p2);
  dbms_output.put_line('测试in out模式存储过程后p3的值为：'||p3);
end;

输出结果如下图所示:

通过测试可以得到以下结果：

• in模式可以理解为引用传递，它的值被完整的传入到存储过程中，但在执行过程中不能被重新赋值，我们传递的值执行之后不会发生改变。
• out模式为值传递，它的值在传入到存储过程的时候会默认赋值为null，可以在执行的时候为其赋值，执行之后传递的值也会随之改变
• in out模式为值传递，它的值被完整的传入到存储过程中，可以在执行的时候为其赋值，执行之后传递的值也会随之改变

总结：可以把in模式看做是向存储过程传递的不想被改变的参数，可以把out模式看做返回值，当执行之后out模式的值就会随着业务逻辑发生改变以供我们使用，而in out模式则很灵活，我们即可以把它当参数传递，也可以当做返回值来使用。

 IF 判断语句

  IF 条件 THEN 2 执行体; 3 END IF; 

IF 条件 THEN
  执行体;
ELSE
  执行体; 
END IF;

IF 条件 THEN
  执行体;
ELSEIF 条件 THEN
  执行体; 
ELSE
  执行体; 
END IF;

CASE选择语句

CASE 变量
  WHEN 匹配变量  THEN   执行体; 
  WHEN 匹配变量  THEN   执行体; 
  WHEN 匹配变量  THEN   执行体; 
  ELSE 执行体; 
END CASE;

LOOP循环语句

LOOP 
  执行体;
  IF 条件 THEN
    执行体;
    EXIT;--此处为跳出循环
  END IF; 
END LOOP

LOOP 
  执行体;
  EXIT WHEN 条件;--跳出循环 对比的有CONTINUE和CONTINUE WHEN语法
END LOOP

 FOR i IN 1..3 LOOP -- FOR EACH 语法，遍历1~3并输出
  --执行体;
   DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
 END LOOP; 

 Cursor游标 

可以把Cursor理解为一个有名称的结果集，当需要的时候就可以拿过来用，也可以吧Cursor看成一个实体类，存储了我们定义的数据。

create or replace procedure proc_cursor_demo as
cursor emp_cursor is        --定义cursor
select * from emp;          --将数据空间指向emp_cursor
begin
  for emp_data in emp_cursor loop            --遍历cursor数据输出
    dbms_output.put_line(emp_data.ename ||'  的工作为: '||emp_data.job);
  end loop;
end;

练习题：把emp表中有奖金的员工提取到另外一个表，并在员工的姓名后添加后缀_V

create or replace procedure proc_copy_demo
(tn in varchar)
as 
v_count number; --检查是否已经有这个名词的表了
tablename varchar2(20) --表名称
v_emp_copy emp%rowtype --表结构

v_sql varchar(200) --sql

cursor emp_data_cursor is --将有emp表中有奖金的人员放到cursor中
select * from emp where comm >0;

begin 
  tablename := tn; 定义表名称
  select count(*) into v_count from user_objects where object_name = upper(tablename); --查询用户表，校验是否已经存在名称为tablename的表
  if v_count > 0 then --表存在
  excute immediate 'drop table' || tablename || 'cascade constraints'; --执行删除表 excute imediate 执行sql语句
  end if;
  
  execute immediate 'create table' || tablename || 'as select * from emp where 1=2'; --新建表结构和emp相同的表，添加条件后只创建表，不添加数据
  for emp_data in emp_data_cursor loop
      v_emp_copy.empno := emp_data.empno;
      v_emp_copy.ename := emp_data.ename || '_A';
      v_emp_copy.job := emp_data.job;
      v_emp_copy.mgr := emp_data.mgr;
      v_emp_copy.hiredate := emp_data.hiredate;
      v_emp_copy.sal := emp_data.sal;
      v_emp_copy.comm := emp_data.comm;
      v_emp_copy.deptno := emp_data.deptno;
      
      v_sql : = 'insert into' || tbalename ||'values 
      ('||v_emp_copy.empno    ||','''
        ||v_emp_copy.ename    ||''','''
        ||v_emp_copy.job      ||''','
        ||v_emp_copy.mgr      ||','''
        ||v_emp_copy.hiredate ||''','
        ||v_emp_copy.sal      ||','
        ||v_emp_copy.comm     ||','
        ||v_emp_copy.deptno   ||')';
      dbms_output.put_line(v_sql);
      execute immediate v_sql;
  end loop;
  commit;
  exception            -- 捕获异常常用写法
    when others then   
      dbms_output.put_line('捕获的异常代码（SQLCODE） 为 ' ||SQLCODE );        -- 输出异常信息 
      dbms_output.put_line('捕获的异常信息（SQLERRM） 为 ' ||SQLERRM);
end;
    

-------------------------test-------------------------------
begin
 proc_copy_demo('emp_copy');  
end;
-------------------------test-------------------------------      
      

 

参考：http://www.cnblogs.com/Singleorb/p/6231364.html