存储过程 procedure
代码开发注意事项:
1)基础要求:准确 完整 规范
2)加强要求:思路清晰
基本框架:
create procedure 存储过程名称
is
begin
null; --具体要执行的脚本
end ;
–丰富一下
create or replace procedure 存储过程名称 --第一次创建是create procedure,后续修改就用create or replace procedure
(
输入参数名称 in 类型, --统计日期/统计月份
输出参数名称 out 类型 --结束成功是否标志(0为成功,1为错误)
)
is
变量名称 类型;–变量的定义(for循环后面的变量是不需要定义的)
cursor cur is select * from 表名; --游标的定义
begin
变量名:=常量;–变量的赋值
exception --异常部分
null;
end ;
存储过程的执行步骤:
创建存储过程->编辑存储过程(存储过程名称处右键选择"编辑"),看下是否有报错->运行存储过程(存储过程名称处右键选择"测试",再点击齿轮运行)
存储过程编辑过程中,如果该存储过程正在运行或其它窗口有正在执行的操作,存储过程都是无法编辑的。
注意事项:
1.表的字段,存储过程的输入/输出参数,存储过程的变量,都需要指定类型(varchar2或number或integer)
2.存储过程只是将里面的代码执行,不保存执行后的结果,如果要保留结果,有以下两种方法:
一种方法:将结果打印出来
dbms_output.put_line(要打印的结果对应的变量);
示例:需求是要将变量v_i的值打印出来
dbms_output.put_line(v_i);
另一种方法:将结果插入到某个表中
insert into 表名
select * from 表名;
注:如果只想把单个值的结果保留,可以使用打印的方法;
如果想把许多值的结果都保留,最好将结果插入到表中.
3.存储过程中用select查询出来的结果首先要赋给一个变量,再把变量的值打印或插入表中
4.什么情况下使用游标,即cursor?
想将表中的数据循环取出时,就可以定义和使用游标.
游标的定义语句:
cursor 游标名称 is select 字段名称 from 表名;
如:cursor cur is select id,name from tt;
游标的语句:
for 变量名 in 游标名称 loop
变量名.字段名称
end loop;
如:
for c in cur loop
c.name
end loop;
6.变量的几种使用方式如下:
1)普通变量
a.变量必须定义,格式为:变量名 类型;
示例1:
v_i integer;
b.变量的赋值是将左边的值赋给右边,格式为:变量名:=某值(注:也可以将变量值再赋给变量);
示例1:
v_i:=3; --将数据值为3赋给变量v_id
示例2:
v_i:=v_i --将变量v_i的值赋给变量v_i
示例3:
v_a:=2;
v_b:=3;
v_c:=v_a+v_b --将变量v_a的值与v_b的值相加的结果,再赋给变量v_c
c.变量一次只能存储一个值,如果给某个变量多次赋值,只存储最后一次的赋值结果;
示例1:
v_i:=3;
dbms_output.put_line(v_i); --输出结果为3
v_i:=8;
dbms_output.put_line(v_i); --输出结果为8
2)游标变量
a.游标中使用的变量是不需要定义的(如示例1中的变量c是不用定义的);
b.使用变量时,格式为:变量名.游标中查询语句的查询字段(如示例1中c.prov_id),
因为游标可以取出查询语句的多个字段,所以
使用时,为了区分使用的是哪个字段,所以特殊标识一下;
示例1:
declare
cursor cur is select prov_id,prov_name from tb_det_prov;
begin
for c in cur loop
dbms_output.put_line(c.prov_id);
end loop;
end;
b.
3)循环变量
a.循环中使用的变量是不需要定义的(如示例1中的变量i是不用定义的);
示例1:
for i in 1…100 loop
dbms_output.put_line(i);
end loop;
要在ETL里面调度,记得要 oi_return:=0;表示变量的输出:
示例:
CREATE OR REPLACE Procedure p_lj_002
(
iv_date In Varchar2,
oi_return Out Integer
)
Is
Begin
Delete From tdw_lj_002 Where statis_date=iv_date;
Insert Into tdw_lj_002
Select statis_date,prov_Name,Count(Distinct use_serv_number) usr,Count(use_serv_number) cnt
From tdw_mm_dnld_d t1,vb_det_prov t2
Where t1.prov_id=t2.prov_id
And statis_date=iv_date
Group By statis_date,prov_name
Order By statis_date,prov_name Asc;
Commit;
oi_return:=0;
End;