Oracle在学习存储过程中,有一些心得和体会,特此做一些总结,供大家学习参考。包括动态 存储过程的拼接,还有一些存储过程在编写过程中该注意的地方。
1.不带参数存储过程
create or replace procedure mypro1
is
begin
insert into test values(3,'Haha');
commit;
end;
2.带参数进行拼接的存储过程(这种方式即为动态执行的过程,用execute immediate来实现)
create or replace procedure myproc1(id int)
is
v_sql varchar2(40);
begin
v_sql:='insert into test select * from test0'||id;
execute immediate v_sql;
commit;
end;
3.带多个参数的存储过程拼接
create or replace procedure PT(num number,id number)
is
v_sql varchar2(400);
begin
v_sql:='insert into test select * from test0'||num||' t where t.id='||id;
dbms_output.put_line(v_sql);
execute immediate v_sql;
commit;
end;
4.传入参数的插入过程
create or replace procedure PT(id int,descr varchar2)
is
v_sql varchar2(40);
begin
v_sql:='insert into test values('''||id||''','''||descr||''')';
dbms_output.put_line(v_sql);
execute immediate v_sql;
commit;
end;
5.execute immediate(into语句,和单纯的直接执行不同)
create or replace procedure PT
num int;
v_sql varchar2(50);
begin
v_sql:='select count(*) from test';
execute immediate v_sql into num;
commit;
dbms_output.put_line(num);
end PT;
如果单独的执行则为
create or replace procedure PT
num int;
begin
select count(*) into num from test;
commit;
dbms_output.put_line(num);
end PT;
6.字符串的传递
create or replace procedure PT is
v_sql varchar2(100);
v_index_id_n varchar2(40);
num int;
begin
v_index_id_n:='(''Hello'',''HaHa'')';
v_sql:='select count(1) from test where descr='||v_index_id_n;
dbms_output.put_line(v_sql);
execute immediate v_sql into num;
commit;
dbms_output.put_line(num);
end PT;
Oracle中字符串的表示为''字符串值'',这点要注意。
7.Oracle 带参数的游标
create or replace PT
is
num int;
cursor c_maininfo(v_id int) is
select * from test where id=v_id;
begin
num:=1;
for cs in c_maininfo(num) loop
dbms_output.put_line(cs.id||' '||cs.descr);
end loop;
end PT;
8.双循环&带参数的游标
create or replace procedure PT is
v_begin int;
v_end int;
cursor v_maininfo(v_id int) is select
* from test where id=v_id;
begin
v_begin:=1;
v_end:=4;
for v_id in v_begin..v_end loop
for curs in v_maininfo(v_id) loop
dbms_output.put_line(curs.id||' '||curs.name);
end loop;
end loop;
end PT;
9.Oracle中对于变量和字符串的定义。
create or replace procedure PT(v_id int,v_name varchar2,v_id2 int)
is
v_sql long;
begin
v_sql:='Hello'||v_id||v_name||v_id2;
dbms_output.put_line(v_sql);
v_sql:='Hello'||v_id||v_name||''''||v_id2||'''';
dbms_output.put_line(v_sql);
end PT;
得到的结果为:
Hello1HuHu2
Hello1HuHu'2'
当使用||变量时,变量会自动转换为字符串,相当于java中的+,
而如果要在字符串中使用变量,则通过'''||变量||'''的方式,这个时候传入的变量
就是个实际的参数。
10.Oracle的左连接和右连接,除了可以用left outer join和right outer join
的方式外,有更加简易的方法。
select * from test01 t1,test02 t2
where t1.id=t2.id(+);-----以test01为标准进行的左连接
select * from test01 t1,test02 t2
where t1.id(+)=t2.id;-----以test02为标准进行的右连接
存储过程的开发(这时会涉及到很多细节问题,但是实际去做才会发现问题,也才能
学到东西。
集团v网成员中当日和当月产生4G流量用户数。首先建立目标表GROUP_4G,为了防止数据量
过大,按照day_number进行分区,在建表时完成
create or replace procedure PT
(in_stat_date in number)
/*
源表:jsmkt.md_app_4g_total_day_01/02/03
bsdata.md_grp_mem_vpmn_day_01/02/03
目标表:GROUP_4G
*/
is
const_table_name constant varchar2(30) := 'GROUP_4G';
v_sql long;
v_day_number varchar2(2);
v_threemonth varchar2(2);
begin
-------------------初始化----------------------
v_day_number := fun_get_dd_yyyymmdd_c(in_stat_date);
v_threemonth := fun_get_thrmonth_yyyyymmdd_c(in_stat_date);
execute immediate 'alter table '||const_table_name||' truncate partition P_MD_4G_'||v_day_number;
v_sql:='insert /*+append*/ into '||const_table_name||'
(DAY_NUMBER,
STAT_DATE,
CUSTOMER_ID,
USER_ID,
AREA_ID,
Dangri_4G,
Dangyue_4G
)
select /*+parallel(t,12)+*/
t.day_number,
t.stat_date,
t.customer_id,
t.user_id,
t.area_id,
case when t1.col_name023=1 then 1 else 0 end as Dangri_4G,
case when t1.col_name025=1 then 1 else 0 end as Dangyue_4G
from bsdata.md_grp_mem_vpmn_day_'||v_threemonth||' t,
jsmkt.md_app_4g_total_day_'||v_threemonth||' t1
where
t.day_number='||v_day_number||' and t1.day_number='||v_day_number||'
and t.user_id=t1.user_id and t.org_attr2 not in(''41'',''42'') and t.start_date<='||in_stat_date||'
and nvl(t.end_date,''20991212'')>='||in_stat_date;
execute immediate v_sql;
commit;
end PT;