INSERT INTO COMPARE (GDLY,NBCODE,WBCODE,
NBYGM,NBFM,NBPM,NBGM,NBJM,NBWGM,
WBYGM,WBFM,WBPM,WBGM,WBJM,WBWGM)
SELECT A.GDLY,A.NBCODE,B.WBCODE,
A.NBYGM,A.NBFM,A.NBPM,A.NBGM,A.NBJM,A.NBWGM,
B.WBYGM,B.WBFM,B.WBPM,B.WBGM,B.WBJM,B.WBWGM
FROM (SELECT A.JLB_CODE,B.GDLY,B.NBCODE,
A.CBXX_YGZM NBYGM,A.CBXX_FDZM NBFM,A.CBXX_PDZM NBPM,
A.CBXX_GDZM NBGM, A.CBXX_JFZM NBJM,A.CBXX_WGZM NBWGM
FROM ELC_CBXX A,ELC_TRANSFORMERJLB B
WHERE A.JLB_ID=B.NBCODE AND A.CBXX_DATE=:use_date)A,
(SELECT A.JLB_CODE,B.NBCODE,B.WBCODE,
A.CBXX_YGZM WBYGM,A.CBXX_FDZM WBFM,A.CBXX_PDZM WBPM,
A.CBXX_GDZM WBGM, A.CBXX_JFZM WBJM,A.CBXX_WGZM WBWGM
FROM ELC_CBXX A,ELC_TRANSFORMERJLB B
WHERE A.JLB_ID=B.WBCODE AND A.CBXX_DATE=:use_date)B
WHERE A.NBCODE=B.NBCODE AND (ABS(A.NBYGM-B.WBYGM)>2 OR ABS(A.NBFM-B.WBFM)>2 OR
ABS(A.NBPM-B.WBPM)>2 OR ABS(A.NBGM-B.WBGM)>2 OR ABS(A.NBJM-B.WBJM)>2 OR
ABS(A.NBWGM-B.WBWGM)>2)' USING use_date我的变量没有问题啊 大家帮我看看吧
NBYGM,NBFM,NBPM,NBGM,NBJM,NBWGM,
WBYGM,WBFM,WBPM,WBGM,WBJM,WBWGM)
SELECT A.GDLY,A.NBCODE,B.WBCODE,
A.NBYGM,A.NBFM,A.NBPM,A.NBGM,A.NBJM,A.NBWGM,
B.WBYGM,B.WBFM,B.WBPM,B.WBGM,B.WBJM,B.WBWGM
FROM (SELECT A.JLB_CODE,B.GDLY,B.NBCODE,
A.CBXX_YGZM NBYGM,A.CBXX_FDZM NBFM,A.CBXX_PDZM NBPM,
A.CBXX_GDZM NBGM, A.CBXX_JFZM NBJM,A.CBXX_WGZM NBWGM
FROM ELC_CBXX A,ELC_TRANSFORMERJLB B
WHERE A.JLB_ID=B.NBCODE AND A.CBXX_DATE=:use_date)A,
(SELECT A.JLB_CODE,B.NBCODE,B.WBCODE,
A.CBXX_YGZM WBYGM,A.CBXX_FDZM WBFM,A.CBXX_PDZM WBPM,
A.CBXX_GDZM WBGM, A.CBXX_JFZM WBJM,A.CBXX_WGZM WBWGM
FROM ELC_CBXX A,ELC_TRANSFORMERJLB B
WHERE A.JLB_ID=B.WBCODE AND A.CBXX_DATE=:use_date)B
WHERE A.NBCODE=B.NBCODE AND (ABS(A.NBYGM-B.WBYGM)>2 OR ABS(A.NBFM-B.WBFM)>2 OR
ABS(A.NBPM-B.WBPM)>2 OR ABS(A.NBGM-B.WBGM)>2 OR ABS(A.NBJM-B.WBJM)>2 OR
ABS(A.NBWGM-B.WBWGM)>2)' USING use_date我的变量没有问题啊 大家帮我看看吧
GDLY VARCHAR2(30),
NBCODE CHAR(6),
WBCODE CHAR(6),
NBYGM NUMBER,
NBFM NUMBER,
NBPM NUMBER,
NBGM NUMBER,
NBJM NUMBER,
NBWGM NUMBER,
WBYGM NUMBER,
WBFM NUMBER,
WBPM NUMBER,
WBGM NUMBER,
WBJM NUMBER,
WBWGM NUMBER
) ON COMMIT PRESERVE ROWS';为什么每次执行完这后 数据库中 就有了 COMPARE这个表啊 。。下次执行的时候就会报错了COMPARE存在了。。
scott@YPCOST> ed
已写入 file afiedt.buf 1 create or replace procedure pro_tmp(v_col1 varchar2,v_col2 varchar2)
2 as
3 v_num number;
4 v_char varchar2(10);
5 begin
6 select count(*) into v_num from user_tables where table_name='T_TEMP';
7 if v_num<1 then
8 execute immediate 'create global temporary table T_TEMP(
9 col1 varchar2(10),col2 varchar2(20)) on commit preserve rows';
10 end if;
11 execute immediate 'insert into t_temp values('''||v_col1||''','''||v_col2||''')';
12 execute immediate 'select col1 from t_temp' into v_char;
13 dbms_output.put_line(v_char);
14* end pro_tmp;
scott@YPCOST> /过程已创建。scott@YPCOST> exec pro_tmp('a','b');
aPL/SQL 过程已成功完成。scott@YPCOST> select * from t_temp; --会话没有结束可以查询表COL1 COL2
---------- --------------------
a b
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsscott@YPCOST> select * from t_temp;未选定行
不是跟你说了临时表不要动态创建么?
oracle的临时表是外部创建好了在程序里直接用.
所以你再次执行过程的时候就会报表存在要不像minitoy说的在外部建好 在过程里用
或者在过程里创建用完之后drop掉 (当然这样肯定是没上一种方法好)
这个是对应事物级临时表
ON COMMIT PRESERVE ROWS
这个是会话级的临时表
从字面含义也容易区分出来的.