oracle 存储过程的小问题

Question

以下是截取oracle存储过程的一个片段 
tablename :='CLTX_'||cur_dt1||mm1;
       dbms_output.put_line(tablename);
       
       execute immediate 'select count(*) into '|| temp3||' from USER_TABLES where table_name='||tablename;
       dbms_output.put_line('temp3:'||temp3)
       if tem3>0 then
          dbms_output.put_line('表已存在');
       else
          dbms_output.put_line('表bu存在');
       end if;
注（tablename为varchar变量，temp3为number或者int变量）上面这段程序在触发器里面可以编译通过，为什么在存储过程中无法编译通过，很是不解？另外一个问题insert into CLTX_200805_200809 select * from CLTX_200809;为什么不能把
CLTX_200809表中数据添加进CLTX_200805_200809 ？注：两个同构

Answer 1

改成下面的语句执行看看
 EXECUTE IMMEDIATE 'select count(*)  from USER_TABLES where table_name= :1'
            INTO temp3
            USING tablename;

Answer 2

另一个问题
--表结构复制(全部复制)
--sqlserver select *　into A from B
create table A as(select * from B)
--只复制结果
--sqlserver select * into A from B where 2!=1    
create table A as(select * from B where 5=3)

Answer 3

EXECUTE IMMEDIATE中的select into要注意。
DECLARE
  TABLENAME VARCHAR2(2000) := 'temp';
  TEMP3     INTEGER;BEGIN
  TABLENAME := UPPER(TRIM(TABLENAME));
  DBMS_OUTPUT.PUT_LINE(TABLENAME);  EXECUTE IMMEDIATE 'select count(*) from USER_TABLES where table_name= ''' ||
                    TABLENAME || ''''
    INTO TEMP3;
  DBMS_OUTPUT.PUT_LINE('temp3:' || TEMP3);
  IF TEMP3 > 0 THEN
    DBMS_OUTPUT.PUT_LINE('表已存在');
  ELSE
    DBMS_OUTPUT.PUT_LINE('表不存在');
  END IF;
END;

Answer 4

输出结果：TEMP
temp3:0
表不存在

Answer 5

 execute immediate 'select count(*) into '|| temp3||' from USER_TABLES where table_name='||tablename;
 
修改为：
EXECUTE IMMEDIATE 'select count(*) from USER_TABLES where table_name= ''' ||
                    TABLENAME || ''''
    INTO TEMP3;

Answer 6

另外一个问题insert into CLTX_200805_200809 select * from CLTX_200809;为什么不能把
CLTX_200809表中数据添加进CLTX_200805_200809 ？注：两个同构-------
检查最后有没有commit，提交数据

Answer 7

       --execute immediate 'select count(*) from USER_TABLES where table_name='''||TABLENAME||'''' into temp3;
       --dbms_output.put_line('temp3:'||temp3)
       --if tem3>0 then
       --   dbms_output.put_line('表已存在');
       --else
       --   dbms_output.put_line('表bu存在');
       --end if;
这是我注释的语句，加上execute...这句编译就无法通过，郁闷了

Answer 8

--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
       )
AS 
   pageIndex1 integer;
   tem integer;
   tem2 varchar2(2000);
   tem3 integer;
   
   v_year varchar2(30);
   v_month varchar2(30);
   v_day varchar2(30);
   v_hours varchar2(30);
   v_minut varchar2(30);
   v_second varchar2(30);
   cur_dt1 varchar2(30);
   mm1 varchar2(4);
   
   sqlstr varchar2(4000);
   sql_where 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(64);
    
   TABLENAME varchar2(2000);
   i_count integer;
   record_total_count number;
   need_count int;
   fly_count int;
   cur_dt date;
   
   mm int;
begin
   dbms_output.put_line('Beginning.....');
   dbms_output.put_line('vcBeginTime='||vcBeginTime);
   dbms_output.put_line('vcEndTime='||vcEndTime);
   sql_where:='';
  /**  oracle不能直接为参数赋值
  if pageIndex<1 then 
     pageIndex1 :=1;
     pageIndex := pageIndex1;
  end if;
  if pageSize<1 then
     pageSize:=20;
  end if;
  
  */
 
  --start_ym :='2010-05-11 15:45:13';
  --dbms_output.put_line(start_ym);
  -- to_date('2008-11-22 10:12:12','yyyy-mm-dd hh24:mi:ss')
  --vcBeginTime1:='2010-05-13 13:12:12';
  --vcEndTime1:='2010-05-11 12:12:12';
  
  --dbms_output.put_line(to_char(to_date(sysdate),'yyyy-MM-dd hh:mi:ss'));
  select to_char(to_date(sysdate),'YYYY') into v_year from dual;
  select to_char(to_date(sysdate),'MM') into v_month from dual;
  select to_char(to_date(sysdate),'dd') into v_day from dual;
  
  select to_char(to_date(sysdate),'hh') into v_hours from dual;
  select to_char(to_date(sysdate),'mi') into v_minut from dual;
  select to_char(to_date(sysdate),'ss') into v_second from dual;
  
  
  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('This is test date'); 
      --vcBeginTime:='tttt';
      --sql_where:= vcBeginTime;
      --vcEndTime:= sql_where;
      --vcBeginTime:= sql_where;
      --vc_where:= ' ';   
       end;
  else
       dbms_output.put_line('开始时间应该小于结束时间，格式正确');  
  end if;
  dbms_output.put_line('pageIndex:'||pageIndex);
  dbms_output.put_line('pageSize:'||pageSize);
  
  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');
  
  dbms_output.put_line('开始时间年月设定：'||start_ym);
  dbms_output.put_line('结束时间年月设定：'||end_ym);
  
  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 '||vcBeginTime||' and '||vcEndTime;
  --dbms_output.put_line('vcWhere='||vcWhere);
  if vcWhere is not null then
    sql_where:=sql_where||' and '||vcWhere||'';
    dbms_output.put_line(sql_where);
  else
    dbms_output.put_line('可选择查询条件为空');
  end if;
  
  sqlstr:='';
  cur_dt:=end_datetime;
  
  while cur_dt>=start_datetime loop
     dbms_output.put_line('进入循环开始匹配表......');
     --select to_char(to_date('2009-10-12 16:24:19','yyyy-mm-dd hh24:mi:ss'),'yyyy') into cur_dt1 from dual;
     select to_char(cur_dt,'yyyy') into cur_dt1 from dual;
     select to_char(cur_dt,'mm') into mm1 from dual;
     TABLENAME :='CLTX_'||cur_dt1||mm1;
       dbms_output.put_line(tablename);
       
       --execute immediate 'select count(*) from USER_TABLES where table_name='''||TABLENAME||'''' into temp3;
       --dbms_output.put_line('temp3:'||temp3)
       --if tem3>0 then
       --   dbms_output.put_line('表已存在');
       --else
       --   dbms_output.put_line('表bu存在');
       --end if;
       
       sqlstr :='insert into '||tmp_tablename||' select * from '||TABLENAME;
       dbms_output.put_line(sqlstr);
       execute immediate sqlstr;
       commit;
       select add_months(cur_dt,-1) into cur_dt from dual;
  end loop;
  sqlstr:='select * from (select rownum as rn,tt.* from) '||tmp_tablename ||' tt b where b.rn>='||pageSize*(pageIndex-1)+1||' b.rn<='||pageSize*pageIndex+1||' order by JGSJ desc';
  dbms_output.put_line('sqlstr==='||sqlstr);
  --execute immediate sqlstr;
  --sqlstr:='drop table '||tmp_tablename;
  --execute immediate sqlstr;
  dbms_output.put_line('The end......');
  --return;
end sp_query_data;//测试调用
begin
  sp_query_data(1,10,'2008-08-01 09:56:24','2008-09-12 09:56:24','1=1');
end;

Answer 9

循环外的分页
sqlstr:='select * from (select rownum as rn,tt.* from) '||tmp_tablename ||' tt b where b.rn>='||pageSize*(pageIndex-1)+1||' b.rn<='||pageSize*pageIndex+1||' order by JGSJ desc';
  dbms_output.put_line('sqlstr==='||sqlstr);
可以先注释掉