从plsql到存储过程
基本语法结构
declare -- 声明变量 begin -- 代码逻辑 exception --异常处理 end;
变量
声明变量的语法
变量名 类型（长度）;
变量赋值的语法
变量：=变量值
例子：
-- 变量声明与赋值
declare
v_price number(10,2); -- 单价
v_usenum number; -- 水费字数
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); --金额
begin
v_price := 2.45; -- 单价赋值
v_usenum := 9213; --水费的字数
v_usenum2 := round(v_usenum/1000,2); -- 吨数
v_money := v_price*v_usenum2; -- 金额
dbms_output.put_line('金额：'||V_money);
end;
-- 输出结果：金额：22.56
**************************************************************************
-- 变量 是数据库中查出来的数据
-- select 列名 into 变量名
declare
v_price number(10,2); -- 单价
v_usenum number; -- 水费字数
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); --金额
v_num0 number; -- 上月水表数
v_num1 number; -- 本月水表数
begin
v_price := 2.45; -- 单价赋值
-- v_usenum := 9213; --水费的字数
--从数据库读取
select usenum,num0,num1 into v_usenum,v_num0,v_num1 from t_account where year = '2012' and month = '01' and owneruuid = 1;
v_usenum2 := round(v_usenum/1000,2); -- 吨数
v_money := v_price*v_usenum2; -- 金额
dbms_output.put_line('水费字数：'||v_usenum||' 金额：'||v_money||' num0:'||v_num0||' num1:'||v_num1);
end;
-- 输出结果：水费字数：2104 金额：5.15 num0:15 num1:20属性类型
-- 属性类型 （引用类型 表名.列名%type）
declare
v_price number(10,2); -- 单价
v_usenum t_account.usenum%type; -- 水费字数
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); --金额
v_num0 t_account.num0%type; -- 上月水表数
v_num1 t_account.num1%type; -- 本月水表数
begin
v_price := 2.45; -- 单价赋值
-- v_usenum := 9213; --水费的字数
select usenum,num0,num1 into v_usenum,v_num0,v_num1 from t_account where year = '2012' and month = '01' and owneruuid = 1;
v_usenum2 := round(v_usenum/1000,2); -- 吨数
v_money := v_price*v_usenum2; -- 金额
dbms_output.put_line('水费字数：'||v_usenum||' 金额：'||v_money||' num0:'||v_num0||' num1:'||v_num1);
end;
-------------------------------------------------------
--属性类型 （行记录型 表名%rowtype）
declare
v_price number(10,2); -- 单价
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); --金额
v_account t_account%rowtype; -- 台账行记录类型
-- 相当于java中的实体类 直接通过 表名.列名 调用
begin
v_price := 2.45; -- 单价赋值
-- v_usenum := 9213; --水费的字数
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||' num0:'||v_account.num0||' num1:'||v_account.num1);
end;异常（例外）
有 预定义异常和用户自定义异常
--语法结构
exception
when 异常类型 then
异常处理
-- no_data_found
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; -- 单价赋值
-- v_usenum := 9213; --水费的字数
select * into v_account from t_account
where year = '2012' and month = '01' and owneruuid = 100;
v_usenum2 := round(v_account.usenum/1000,2); -- 吨数
v_money := v_price*v_usenum2; -- 金额
dbms_output.put_line('水费字数：'||v_account.usenum||' 金额：'||v_money||' num0:'||v_account.num0||' num1:'||v_account.num1);
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
end;
--too_many_rows
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; -- 单价赋值
-- v_usenum := 9213; --水费的字数
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||' num0:'||v_account.num0||' num1:'||v_account.num1);
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
when too_many_rows then
dbms_output.put_line('返回多行数据');
end;条件判断
--基本语法
if 条件 then
业务逻辑
end if;
--相当于
if(){
}
------------------------------------
if 条件 then
业务逻辑
else
业务逻辑
end if;
--相当于
if(){
}else{
}
------------------------------------
if 条件 then
业务逻辑
elsif then
elsif then
else
end if;
-- 相当于
if(){
}else if(){
}else{
}
------------------------------------------------
declare
v_price1 number(10,2); -- 单价
v_price2 number(10,2); -- 单价
v_price3 number(10,2); -- 单价
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); --金额
v_account t_account%rowtype; -- 台账行记录类型
begin
v_price1 := 2.45; -- 单价赋值(五吨以下)
v_price2 := 3.45; -- 单价赋值（五到十吨）
v_price3 := 4.45; -- 单价赋值（十吨以上）
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; -- 金额
--阶梯水费计算
if v_usenum2<=5 then
v_money := v_price1*v_usenum2;
elsif v_usenum2>5 and v_usenum2<=10 then
v_money := v_price1*5+v_price2*(v_usenum2-5);
else
v_money := v_price1*5+v_price2*5+v_price3*(v_usenum2-10);
end if;
dbms_output.put_line('水费字数：'||v_account.usenum||' 金额：'||v_money||' num0:'||v_account.num0||' num1:'||v_account.num1);
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
when too_many_rows then
dbms_output.put_line('返回多行数据');
end;循环
无条件循环
--基本语法 loop --循环语句 end loop; ---------------------------------------------------------- --无条件循环：1到100 declare v_num number; begin v_num := 1; loop dbms_output.put_line(v_num); v_num := v_num+1; if v_num>100 then exit; -- loop （无条件循环）中需要写exit跳出循环 相当于java中的return end if; -- exit when v_num>100; -- 也可以不写if then 直接写这个 这种写法效果是一样的 end loop; end;
条件循环
--基本语法 while 条件 loop --循环语句 end loop; ----------------------------------------------------------- --有条件循环：1到100 declare v_num number; begin v_num := 1; while v_num<=100 loop dbms_output.put_line(v_num); v_num:=v_num+1; end loop; end; ----------------------------------------------------------- declare v_num number; begin v_num := 1; while v_num<=100 loop dbms_output.put_line(v_num); v_num:=v_num+1; exit when v_num>50 -- 可以手动跳出 end loop; end;
for循环
--基本语法 declare -- declare可以不写 begin for 变量 in 1..100 --变量可以不声明 但是这个变量只能在循环着中使用 loop end loop; end;
游标
游标是系统为用户开设的一个数据缓冲区，存放sql语句的执行结果。我们可以把游标理解PL/SQL中的结果集。
基本语法
--在声明区声明游标 cursor 游标名称 is sql语句 --使用游标语法 open 游标名称 --打开游标 loop fetch 游标名称 into 变量 exit when 游标名称%notfound -- notfound 是游标中的属性 当游标走到底会返回一个true 除此之外还有一个found的属性它返回的值与notfound相反 end loop; close 游标名称;--关闭游标
不带参数的游标
declare
cursor cur_pricetable is select * from t_account where usenum = '2104';--声明游标
v_pricetable t_account%rowtype;
begin
open cur_pricetable;--打开游标
loop
fetch cur_pricetable into v_pricetable;--提取游标
exit when cur_pricetable%notfound;--退出循环
dbms_output.put_line('ID:'||v_pricetable.OWNERUUID);
end loop;
close cur_pricetable;--关闭游标
end;带参数的游标
declare
cursor cur_pricetable(v_ownertype varchar2) is select * from t_account where usenum = v_ownertype;
v_pricetable t_account%rowtype;
begin
open cur_pricetable('2104');--打开游标 感觉这个open相当于java里调用方法传参数
loop
fetch cur_pricetable into v_pricetable;--提取游标
exit when cur_pricetable%notfound;--退出循环
dbms_output.put_line('ID:'||v_pricetable.OWNERUUID);
end loop;
close cur_pricetable;--关闭游标
end;for循环 游标
declare
cursor cur_pricetable(v_ownertype varchar2) is select * from t_account where usenum = v_ownertype;
-- v_pricetable t_account%rowtype; --自动声明 但是只能在循环中使用
begin
for v_pricetable in cur_pricetable('2104') --带参数的
-- for v_pricetable in cur_pricetable --如果不带参数不写就行了
loop
dbms_output.put_line('ID:'||v_pricetable.OWNERUUID);
end loop;
end;
--for循环会自动打开关闭游标 会自动提取游标 变量也会自动声明存储函数
存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用plsql进行逻辑处理。
存储函数基本语法
create [or replace] function 函数名称 (参数名称 参数类型，参数名称 参数类型,....) return 结果变量数据类型 is 变量声明部分； begin 逻辑部分； return 结果变量； [exception 异常处理部分] end;
create or replace function fn_gecaddress (v_id number) return varchar2 is v_name varchar2(30); begin select year into v_name from t_account where OWNERUUID = v_id; return v_name; end; -- 执行之后在function文件夹中可以看到 ------------------------------------------------------------------------- --自定义存储函数测试 select fn_gecaddress(1) from dual;--通过函数名调用 有参数需要传参
存储过程
存储过程是被命名的pl/ql块，存储于数据库中，是数据库对象的一种，应用程序可以调用存储过程，执行相应的逻辑。
存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：
存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。
存储函数可以在select语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。
存储函数一般都是封装一个查询结果，而存储过程一般都橙装一段事务代码。
存储过程基本语法
create [or replace] procedure 存储过程名称 （参数名 类型，参数名 类型，参数名 类型） is|as 变量声明; begin 逻辑部分 [exception 异常处理部分] end;
参数只指定类型，不指定长度
过程参数的三种模式：
IN 传入参数（默认,可以不写）
OUT 传出参数，主要用于返回程序运行结果
IN OUT 传入传出参数
不带传出参数的存储过程
CREATE OR REPLACE procedure pro_owners_add ( v_id number, v_usenum number, v_year varchar2, v_month varchar2 ) is begin insert into t_account values(v_id,v_usenum,v_year,v_month,0,0); commit; end; --调用存储过程 call pro_owners_add(11,22,'ss','kk') --或者 begin pro_owners_add(66,77,'qs','dk'); end;
带传出参数的存储过程
CREATE OR REPLACE procedure pro_owners_add ( v_id number, v_usenum number, v_year varchar2, v_month varchar2, v_kk out number ) is begin v_kk:=v_id; insert into t_account values(v_id,v_usenum,v_year,v_month,0,0); commit; end; --调用 declare v_id number; begin pro_owners_add(65,65,'qgs','dgk',v_id); dbms_output.put_line(v_id); end;
0条评论
点击登录参与评论