一文总结总结存储过程

PL/SQL（Procedure Language/SQL）

Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。

--变量赋值

  • 变量名 类型（长度）；
  • 变量名:=变量值
declare
  v1 number(10,2);
 v2 number;
 v3 number(10,2);
 v4 number(10,2);
begin
   v1:=2.45;--单价赋值
  v2:=9213;--水费字数
  v3:=round( v2/1000,2);--吨数
  v4:=v1*v3;--金额
  DBMS_output.put_line('用水量'||v1);
  DBMS_OUTPUT.put_line('金额：'||v4);
end;
```sql
####   --select  列名 into 变量名
- select 列名 into 变量名 from 表名 where 条件
``` sql
declare
 v1 number(10,2);
 v2 number;
 v3 number(10,2);
 v4 number(10,2);
 vp number;
 vc number;
begin
   v1:=2.45;--单价赋值
  select  t.usenum,t.num0,t.num1 into v2,vp,vc 
  from t_account t 
  where year='2012' and month='01' and t.owneruuid=1;
  v3:=round( v2/1000,2);--吨数
  v4:=v1*v3;--金额
  DBMS_output.put_line('用水量'||v2);
  DBMS_OUTPUT.put_line('金额：'||v4);
  DBMS_OUTPUT.put_line('上月表数：'||vp);
  DBMS_OUTPUT.put_line('本月表数：'||vc);
 end;

--属性类型 （引用型 表名.列名%type 表名%rowtype）

 declare
  v_price number(10,2);--单价
  v_usenum2 t_account.usenum%type;--吨数
  v_money number(10,2);--金额
  v_account t_account%rowtype;--台账行记录类型 
begin
  v_price:=2.45;--单价赋值
  --从数据库中提取一行数据赋值给v_account
  select * into v_account from t_account 
  where year='2012' and month='01' and owneruuid=1;
  v_usenum2:=round( v_account.usenum/1000,2);--吨数
  v_money:=v_price*v_usenum2;--金额
  DBMS_OUTPUT.put_line('字数：'||v_account.usenum||'金额：'||v_money);
end;

--异常处理exception

declare
  v_price number(10,2);--单价
  v_usenum2 number(10,2);--吨数
  v_money number(10,2);--金额
  v_account t_account%rowtype;--台账行记录类型 
begin
  v_price:=2.45;--单价赋值
  --从数据库中提取
  select * into v_account from t_account 
  where year='2012' and month='01' ;
  v_usenum2:=round( v_account.usenum/1000,2);--吨数
  v_money:=v_price*v_usenum2;--金额
  DBMS_OUTPUT.put_line('字数：'||v_account.usenum||'金额：'||v_money);
exception
  when no_data_found  then
    DBMS_OUTPUT.put_line('没有找到账务数据');
  when too_many_rows then
    DBMS_OUTPUT.put_line('返回多行账务数据');
end;

-- 条件判断(主要中间分支是elsif不是else if)

-- 阶梯水价
declare
 p1 number(10,2);
 p2 number(10,2);
 p3 number(10,2);
 v_acc T_ACCOUNT%ROWTYPE;
 v_u2 number(10,2);
 v_m  number(10,2);
begin
  p1:=2.45;
  p2:=3.45;
  p3:=4.45;

  select * into v_acc from t_Account where year='2012' and month='01' and owneruuid=1;
  
  v_u2:=round(v_acc.usenum/1000,2);
  if v_u2<=5 then
    v_m:=v_u2*p1;
  elsif v_u2>5 and v_u2<=10  then
    v_m:=p1*5+(v_u2-5)*p2;
  else
    v_m:=p1*5+p2*5+(v_u2-10)*p3;
  end if;
  DBMS_OUTPUT.put_line('吨数：'||v_u2||'金额：'||v_m||'上月字数： '||v_acc.num0||'本月字数'||v_acc.num1);
end;

-- 循环

--loop
declare
  p1 number:=1;
begin
  loop
    dbms_output.put_line(p1);
    p1:=p1+1;
    exit when p1>100;
  end loop;
end;
--while
declare
  p1 number:=1;
begin
  while p1<=100
  loop
    dbms_output.put_line(p1);
    p1:=p1+1;
  end loop;
end;
---for
begin
  for p1 in 1..100
  loop
    dbms_output.put_line(p1);
  end loop;
end;

--游标 数据缓冲区,存放 SQL 语句的执行结果

定义：cursor 游标名称 is SQL 语句;
使用：
open 游标名称
loop
fetch 游标名称 into 变量
exit when 游标名称%notfound
end loop;
close 游标名称

--一般提取
declare
  v_p t_Pricetable%rowtype;
  --定义游标
  cursor cur_p(v_o_typeid number) is 
  select * from t_Pricetable t where t.ownertypeid=v_o_typeid;
begin
  --打开游标
  open cur_p(2);
  --循环提取游标到变量
  loop
    fetch cur_p into v_p;
    exit when cur_p%notfound;
  dbms_output.put_line('价格:'||v_p.price ||'吨位：'||v_p.minnum||'-'||v_p.maxnum );
  end loop;
  close cur_p;
end;

--fro循环提取 无需打开游标 关闭游标 循环游标 提取游标
declare
  --定义游标
  cursor cur_p(v_o_typeid number) is 
  select * from t_Pricetable t where t.ownertypeid=v_o_typeid;
begin
  --循环提取游标到变量
  for v_p in cur_p(2)
  loop
    dbms_output.put_line('价格:'||v_p.price ||'吨位：'||v_p.minnum||'-'||v_p.maxnum );
  end loop;
end;

存储函数

语法结构：

CREATE [ OR REPLACE ] FUNCTION 函数名称
（参数名称 参数类型, 参数名称 参数类型, ...）
RETURN 结果变量数据类型
IS
 变量声明部分;
BEGIN
 逻辑部分;
 RETURN 结果变量;
[EXCEPTION 
 异常处理部分]
END;

-- 根据地址 ID 查询地址名称
create or replace function fn_getadd(v_id number)
return varchar2
is
 v_name varchar2(30);
begin
  select name into v_name from t_address where id=v_id;
  return v_name;
end;

--查询业主 ID，业主名称，业主地址，业主地址使用刚才我们创建的函数
select id 编号,name 业主名称,fn_getadd(addressid) 地址 
from t_owners;

存储过程

被命名的 PL/SQL 块，存储于数据库中,可以调用存储过程，执行相应的逻辑

区别：

  • 存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过
    传出参数返回多个值。
  • 存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是
    被应用程序所调用。
  • 存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务
    代码.
    语法 
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 类型, 参数名 类型, 参数名 类型）
IS|AS
 变量声明部分;
BEGIN
 逻辑部分
[EXCEPTION 
 异常处理部分]
END;

--增加业主信息的存储过程
create or replace procedure p_owners_add(
 v_name varchar2,
 v_addressid number,
 v_housenumber varchar2, 
 v_watermeter varchar2,
 v_type number
) is 
begin
  insert into T_OWNERS 
  values( seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_type );
  commit;
end;
--调用
call p_owners_add('赵伟',1,'999-3','132-7',1);
select * from T_OWNERS;
  • 带传出参数的存储过程
--添加业主信息，传出参数为新增业主的 ID
create or replace procedure p_owners_add(
 v_name varchar2,
 v_addressid number,
 v_housenumber varchar2, 
 v_watermeter varchar2,
 v_type number,
 v_id out number
) is 
begin
  select seq_owners.nextval into v_id from dual;
  insert into T_OWNERS 
  values( seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_type );
  commit;
end;

--调用
declare
  v_id number;
begin
  p_owners_add('张伟',1,'9-3','132-8',1,v_id);
  dbms_output.put_line('增加成功,ID:'||v_id);
end;

存储过程综合案例:

--增加业主信息时，同时在账务表（account）增加一条记录，
--年份与月份为当前日期的年月，初始值（num0）为 0,其它字段信息（区域）与 t_owners表一致
create or replace procedure p_owners_account_add(
 v_name varchar2,
 v_addressid number,
 v_housenumber varchar2,
 v_watermeter varchar2,
 v_type number,
 v_uid out number
) is
 v_year char(4);
 v_mount char(2);
 v_area number;
begin
  select seq_owners.nextval into v_uid from dual;
  v_year:=to_char(sysdate,'yyyy');
  v_mount:=to_char(sysdate,'mm');
  select areaid into v_area from t_address where id=v_addressid;
  --增加业主
  insert into T_OWNERS
  values( seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_type );
  --增加账务
  insert into t_account(id,owneruuid,ownertype,areaid,year,month,num0)
  values
  (seq_account.nextval,v_uid,v_type,v_area,v_year,v_mount,0);
  commit;
  exception
    when NO_DATA_FOUND then
      v_uid:=-1;
      rollback;
end;
--调用
declare
  v_id number;
begin
  p_owners_account_add('张伟',1,'9-3','132-8',1,v_id);
  dbms_output.put_line('增加成功,ID:'||v_id);
end;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容