Oracle存储过程学习记录

     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;