存储过程之使用介绍

存储过程

1.PLSQL（Procedure Lanauge/SQL）

PLSQL概念：指在sql命令语言中增加了过程处理语句（分支，循环等）,使sql语言具有过程处理能力

PLSQL分为三部分：声明部分，执行部分，异常处理部分

---

1.1 变量

1.1.1 普通变量

declare
    --直接赋值
    name varchar2(32) := '你好';
	--语句赋值
    sal number;
begin
    --语句赋值
    select 30 into sal from dual;
	--输出到DBMS
	Dbms_Output.put_line('姓名：'||name||',薪水：'||sal);
end;

1.1.2 引用数据类型

declare
    --引用数据类型
    name emp.ename%Type;
begin
    select ename into name from emp where empno =7839;
	--输出到DBMS
	Dbms_Output.put_line('姓名：'||name);
end;

1.1.3 记录型变量

declare
    --记录型变量,相当于java的一个对象
    name emp%Rowtype;
begin
    select * into name from emp where empno = 7839;
	--不能直接输出name，因为他是一个对象了相当于，可以直接输出他的属性。
	Dbms_output.put_line(name.ename);
end;
    

1.2 流程控制

1.2.1 条件分支

declare
    sal number;
begin
    select count(1) into sal from emp where empno =7839;
   if sal >= 30 then 
       Dbms_output.put_line('薪资大于等于30');
       elsif sal >= 20 then
       	Dbms_output.put_line('薪资大于等于20,小于等于30');
       else 
        Dbms_output.put_line('薪资小于20元');
        	end if;
end;

1.2.2 循环

在oracle中有三种循环方式：简单循环

declare
    num number := 1;
begin
    loop
    exit when num >= 10;
    dbms_output.put_line(num);
    num := num+1;
    end loop;
end;

for循环

declare
    begin
--查询语句有几条，默认就循环几条
for 变量名 in (select 语句) loop
    end loop;
--从5开始倒着循环
 FOR v_counter2 IN REVERSE 1..5 LOOP
	DBMS_OUTPUT.PUT_LINE(v_counter2);
END LOOP; 
--从1开始正着循环
FOR v_counter2 IN 1..5 LOOP
	DBMS_OUTPUT.PUT_LINE(v_counter2);
END LOOP; 
end;

while循环

1.3 游标

• 什么是游标

  用于临时存储一个查询返回的多条数据(相当于java中的集合)，通过遍历游标，可以逐行访问处理该结果的数据。

• 游标的使用方式

  声明 —> 打开 —> 读取 —> 关闭

• 语法

  游标声明：

  cursor 游标名[(参数列表)] is 查询语句；

  游标的打开：

  open 游标名[(参数列表)]；

  游标的取值：

  fetch 游标名 into 变量列表;

  游标的关闭：

  close 游标名；

• 游标的属性

  游标的属性	返回值类型	说明
  %rowcount	整型	获得fetch语句返回的数据行数，遍历游标，获取当前游标的下标值
  %found	布尔类型	最近的fetch语句返回一行数据则为真，否则为假
  %notfound	布尔类型	最近的fetch语句返回一行数据则为假，否则为真
  %isopen	布尔类型	游标打开时为真，否则为假

1.3.1 无参游标

• 使用游标查询emp表中所有员工的姓名和工资，并将其打印。

declare
  --声明游标
  cursor v_emp is select ename,sal from emp;
  --定义两个普通变量
  name emp.ename%Type;
  sal emp.sal%Type;
begin
  --打开游标
  open v_emp;
  --遍历游标
  loop
      	--判断前将数据放入到变量中，因为%notfound默认是假,遍历游标相当于java中的迭代器，放在判断之后，判断时还未取值，
      	--又因为%notfound默认是假，所以会比判断前多循环一次。
        fetch v_emp into name,sal;
	--当游标数据遍历完成，数据为空时为真
    exit when v_emp%notfound;
    	dbms_output.put_line('姓名：'||name||',薪水：'||sal||'，数量：'||v_emp%rowcount);
    end loop;
   close v_emp;    
end;

1.3.2 有参游标

declare
  --声明带参数游标
  cursor v_emp(v_deptno emp.deptno%Type) is select ename,sal from emp where deptno =v_deptno;
  --定义两个普通变量
  name emp.ename%Type;
  sal emp.sal%Type;
begin
  --打开游标
  open v_emp(10);
  --遍历游标
  loop
      	--判断前将数据放入到变量中，因为%notfound默认是假
        fetch v_emp into name,sal;
	--当游标数据遍历完成，数据为空时为真
    exit when v_emp%notfound;
      dbms_output.put_line('姓名：'||name||',薪水：'||sal||'，数量：'||v_emp%rowcount);
    end loop;
   close v_emp;    
end;

1.4 存储过程

• 概念

  将一个个的PLSQL的业务处理过程存储起来可以反复调用，这些被存储起来的PLSQL程序称为存储过程

• 语法

  create or replace procedure 存储过程名称(参数列表) as
      --声明变量
      
      begin
      
      end;
  

1.4.1 无参存储过程

create or replace procedure v_test as
    --声明变量
    
    begin
    dbms_output.put_line('hello world');
    end;

1.4.2 带输入参数的存储过程

--in 代表输入参数，相当于java方法入参，
create or replace procedure v_test(v_num in number) as
    --声明变量

    begin
    dbms_output.put_line('hello world'||v_num);
    end;

1.4.3 带输出参数的存储过程

-- out 代表输出参数
create or replace procedure v_test(v_num in number,v_deptno out emp.deptno%Type ) as
    --声明变量
    begin
      select deptno into v_deptno from emp where empno =v_num;
    end;

declare
 num emp.deptno%Type;
begin 
   v_test(7369,num);
   dbms_output.put_line(num);
end;