ORACLE(10g)一个完整的存储过程

小弟最近在把sqlserver2000数据结构迁到oracle10g上，发现用sqlserver导出工具只能导出表结构，对于存储过程，触发器等好像不能导出，而且导出的表结构也有错误，最后还是要手工改。干脆还是手工写好了，以下是存储过程中的一个，请大家看看，在执行效率，结构等方面有什么不妥之处
大概功能：查询子表(临时表和子表同构)，然后把结果插入到临时表，最后查询临时表并实现分页--oracle存储过程实用
create or replace procedure sp_query_data(
       pageIndex   in integer default 1,
       pageSize    in integer default 10,
       vcBeginTime in varchar,
       vcEndTime   in varchar,
       vcWhere     in varchar
       --此时不能定义长度(与sqlserver的区别)
)
AS 
   pageIndex1 integer;
   temp number;
   cur_dt1 varchar2(30);
   mm1 varchar2(4);
   --游标相关变量
   type mycurtest is ref cursor;
   cltxpro mycurtest;   hphms varchar2(15);
   hpyss varchar2(4);
   --jgsjs date;
   --
   sqlstr varchar2(4000);
   sql_where varchar2(2048);
   sql_where2 varchar2(2048);
   start_ym varchar2(32);
   end_ym varchar2(32);
   start_datetime date;
   end_datetime date;
   
   start_y varchar2(4);
   start_m varchar2(4);
   end_y varchar2(4);
   end_m varchar2(4);
   
   tmp_tablename varchar2(1000);
   TABLENAME varchar2(2000);
   cur_dt date;begin
   dbms_output.put_line('Beginning.....');
   dbms_output.put_line('vcBeginTime='||vcBeginTime);
   dbms_output.put_line('vcEndTime='||vcEndTime);
   dbms_output.put_line('pageIndex:'||pageIndex);
   dbms_output.put_line('pageSize:'||pageSize);
   sql_where:='';
  /**  oracle不能直接为参数赋值
  if pageIndex<1 then 
     pageIndex1 :=1;
     pageIndex := pageIndex1;
  end if;
  if pageSize<1 then
     pageSize:=20;
  end if;
  
  */
  
  if to_date(vcBeginTime,'yyyy-mm-dd hh24:mi:ss')>to_date(vcEndTime,'yyyy-mm-dd hh24:mi:ss') then 
       begin
      dbms_output.put_line('格式错误,开始时间应该小于结束时间'); 
      --vcBeginTime:='tttt';
      --sql_where:= vcBeginTime;
      --vcEndTime:= sql_where;
      --vcBeginTime:= sql_where;
      --vc_where:= ' ';   
       end;
  else
       dbms_output.put_line('开始时间应该小于结束时间，格式正确');  
  end if;  
  select to_char(to_date(vcBeginTime,'yyyy-mm-dd hh24:mi:ss'),'yyyy') into start_y from dual;
  select to_char(to_date(vcBeginTime,'yyyy-mm-dd hh24:mi:ss'),'mm') into start_m from dual;
  select to_char(to_date(vcEndTime,'yyyy-mm-dd hh24:mi:ss'),'yyyy') into end_y from dual;
  select to_char(to_date(vcEndTime,'yyyy-mm-dd hh24:mi:ss'),'mm') into end_m from dual;
  
  start_ym:= start_y||'-'||start_m||'-01 00:00:00';
  end_ym:= end_y||'-'||end_m||'-01 00:00:00';
  
  start_datetime:= to_date(start_ym,'yyyy-mm-dd hh24:mi:ss');
  end_datetime:= to_date(end_ym,'yyyy-mm-dd hh24:mi:ss');
  
  tmp_tablename:='CLTX_'||start_y||start_m||'_'||end_y||end_m;
  dbms_output.put_line('创建临时表:'||tmp_tablename);
  
  execute immediate 'create  table '||tmp_tablename||'(
            ID number null,SBBH varchar(10) null,CLBH varchar(7) null,FXBH varchar(8) null,
            HPHM varchar(15) null,HPZL varchar(16) null,HPYS varchar(4) null,JGSJ date null,
            CLSD decimal(18,0),CWKC float null,CLLX varchar(4),TJTP varchar(100) null,
            QMTP varchar(100) null,HPTP varchar(100) null,JLLX varchar(4) null,CLBJ varchar(1) null,
            HDGG varchar(1) null,QBGG varchar(1) null,CFGG varchar(1) null,CLDD varchar(100) null,FDID varchar(8) null,
            WZDW varchar(100) null,CFBM varchar(100) null,CFSJ varchar(19) null,WZXW varchar(30) null,
            WZDD varchar(100) null,FKBM varchar(50) null,MEMO varchar(100) null,ICCP varchar(20) null,
            SCIP varchar(20) null,CLXS integer null,CDBH varchar(20) null,KKBH varchar(32) null,
            FJDM varchar(32) null,TPWZ varchar(32) null) ';
  
  --dbms_output.put_line(tmp_tablename);
 
  sql_where := ' JGSJ between to_date('''||vcBeginTime||''',''yyyy-mm-dd hh24:mi:ss'||''') and to_date('''||vcEndTime||''',''yyyy-mm-dd hh24:mi:ss'||''')';
  sql_where2 :=' and JGSJ between to_date('''||vcBeginTime||''',''yyyy-mm-dd hh24:mi:ss'||''') and to_date('''||vcEndTime||''',''yyyy-mm-dd hh24:mi:ss'||''')';
  --dbms_output.put_line('vcWhere='||vcWhere);
  
  if vcWhere is not null then
    sql_where:=' where '||vcWhere||' and '||sql_where;
    dbms_output.put_line('附加查询条件:'||sql_where);
  else
    sql_where:=' where 1=1 '||vcWhere||' and '||sql_where;
    dbms_output.put_line('附加查询条件为空');
  end if;
  
  sqlstr:='';
  cur_dt:=end_datetime;
  
  while cur_dt>=start_datetime loop
     --dbms_output.put_line('进入循环开始匹配表......');
 
     TABLENAME :='CLTX_'||to_char(to_date(cur_dt),'yyyy')||to_char(to_date(cur_dt),'mm');
       --dbms_output.put_line(TABLENAME);
       
       --编译无法通过
       select count(*) into temp from USER_TABLES where table_name=TABLENAME;
       --dbms_output.put_line(temp);
       
       if temp>0 then
       dbms_output.put_line('表'||TABLENAME||'存在,执行插入......');
            sqlstr :='insert into '||tmp_tablename||' select * from '||TABLENAME||' '||sql_where;
            --sqlstr :='insert into '||tmp_tablename||' select * from '||TABLENAME;
            dbms_output.put_line(sqlstr);
            execute immediate sqlstr;
            commit;
       else
          dbms_output.put_line('表'||TABLENAME||'不存在');
       end if;
       
       select add_months(cur_dt,-1) into cur_dt from dual;
  end loop;
  --dbms_output.put_line('pageSize*(pageIndex-1)+1:'||(pageSize*(pageIndex-1)+1));
  sqlstr:='select hphm,hpys from (select rownum as rn,tt.* from '||tmp_tablename ||' tt) b where b.rn>='||(pageSize*(pageIndex-1)+1)||' and b.rn<='||(pageSize*pageIndex)||' order by JGSJ desc';
  dbms_output.put_line('查询临时表:'||sqlstr);
  
  --游标相关操作
  if cltxpro%isopen =false then
      open cltxpro for sqlstr;
      fetch cltxpro into hphms,hpyss;
  end if;
    
    while cltxpro%found loop
      dbms_output.put_line(hphms||','||hpyss);
      fetch cltxpro into hphms,hpyss;
      exit when cltxpro%notfound;
    end loop;
    close cltxpro;
   --游标相关操作
   
  /**异常处理
  exception
     when others then
       dbms_output.put_line('异常');
       --rollback;
      commit;
     raise;
   */
     
  sqlstr:='drop table '||tmp_tablename;
  dbms_output.put_line('删除临时表:'||sqlstr);
  execute immediate sqlstr;
  dbms_output.put_line('The end......');
end sp_query_data;--调用存储过程
begin
  sp_query_data(1,10,'2008-08-01 09:56:24','2008-12-30 09:56:24','1=1');
end;

解决方案 »

  1.   

    sqlServer 可以导出表结构   表内容     存储过程 和 函数 创建信息    触发器 都 可以的不过 你的sql编程挺强的       研究下
      

  2.   

    -- 你这创建的不是临时表，创建临时表的语法类似如下：CREATE GLOBAL TEMPORARY TABLE mem1_u_state(
    rid NUMBER(18,0),
    mobile VARCHAR2(20),
    state NUMBER(18,0),
    sign VARCHAR2(120),
    md5 VARCHAR2(32),
    customize_status varchar2(120)
    )
    ON COMMIT PRESERVE ROWS;CREATE GLOBAL TEMPORARY TABLE mem2_u_state(
    rid NUMBER(18,0), 
    mobile VARCHAR2(20), 
    state NUMBER(18,0), 
    sign VARCHAR2(120), 
    md5 VARCHAR2(32),
    customize_status varchar2(120)
    ) 
    ON COMMIT DELETE ROWS;-- Oracle有两种类型的临时表!(你可以多去查查这方面的资料!