我在oracle 92的存储过程中 ,通过下面的方式,创建一个临时表,并向表中添加了数据,我在创建这个临时表中通过语句对此临时表进行检查,如果表存在直接插入数据,否则创建这个临时表,
str:='CREATE GLOBAL TEMPORARY TABLE temptest (
RQ date not null,
ZS VARCHAR2(100))
ON COMMIT DELETE ROWS';
execute immediate str;在同一个存储过程中,我还定义了一个游标,来查询这个临时表中的数据,但当临时表尚未创建或删除,下面的代码将出错,找不到这个临时表DECLARE
CURSOR L_RECORD IS SELECT * FROM temptest;--此处哪果临时表不存在出错
da L_RECORD%ROWTYPE;
BEGIN
FOR da IN L_RECORD LOOP
SELECT COUNT(*) INTO countzjs FROM A010 A INNER JOIN EX_JQZDAFB JQZ ON A.JQZDM=JQZ.JQZDM
WHERE JQZ.QKDM=p_QKDM AND tcrq<=da.rq;
END LOOP;
END;请问能否将临时表的名称 通过一个变量进行解决。后来我通过
TYPE ResultData IS REF CURSOR;
L_RECORD ResultData;
定义游标, strTemp:='SELECT * FROM temptest';
OPEN L_RECORD for strTemp;
BEGIN
LOOP
FETCH L_RECORD INTO rq;---------此处不知怎么写,没有值,
EXIT WHEN L_RECORD%NOTFOUND;
DBMS_OUTPUT.put_line(rq);
END LOOP;
close L_RECORD;
END;
如果按上面的方式可以解决问题,但不知怎么通过Fetch 循环取出 rq这个值,
str:='CREATE GLOBAL TEMPORARY TABLE temptest (
RQ date not null,
ZS VARCHAR2(100))
ON COMMIT DELETE ROWS';
execute immediate str;在同一个存储过程中,我还定义了一个游标,来查询这个临时表中的数据,但当临时表尚未创建或删除,下面的代码将出错,找不到这个临时表DECLARE
CURSOR L_RECORD IS SELECT * FROM temptest;--此处哪果临时表不存在出错
da L_RECORD%ROWTYPE;
BEGIN
FOR da IN L_RECORD LOOP
SELECT COUNT(*) INTO countzjs FROM A010 A INNER JOIN EX_JQZDAFB JQZ ON A.JQZDM=JQZ.JQZDM
WHERE JQZ.QKDM=p_QKDM AND tcrq<=da.rq;
END LOOP;
END;请问能否将临时表的名称 通过一个变量进行解决。后来我通过
TYPE ResultData IS REF CURSOR;
L_RECORD ResultData;
定义游标, strTemp:='SELECT * FROM temptest';
OPEN L_RECORD for strTemp;
BEGIN
LOOP
FETCH L_RECORD INTO rq;---------此处不知怎么写,没有值,
EXIT WHEN L_RECORD%NOTFOUND;
DBMS_OUTPUT.put_line(rq);
END LOOP;
close L_RECORD;
END;
如果按上面的方式可以解决问题,但不知怎么通过Fetch 循环取出 rq这个值,
解决方案 »
- 使用OLEDB,报“ORA-12154,TNS无法处理服务名”
- 关于创建UTL_TCP/HTTP/SMTP 的访问控制列表,提示网络权限无效
- 请教一个 ORACLE中 减法的问题,群众乱入
- ORACLE 10G 函数问题
- 如何建立满足如下条件的索引
- Oracle8i/9i 大数据量设计问题
- asp连接oracle报错rosoft OLE DB Provider for ODBC Drivers (0x80004005)问题求助!
- 我想实现不同数据库下两张相同表的数据同步该怎么做
- 如何在sqlplus中调用一个存储过程?
- 怎么得到某一行某一列的数据
- 求解释!!!!!急求!!!!!!!!!!!!
- 根据一张表更新另外一张表的相关字段,求教各位?oracle 10g DataBase
SQL> create or replace procedure p_t
2 is
3 str varchar2(4000):='CREATE GLOBAL TEMPORARY TABLE temptest (
4 RQ date not null,
5 ZS VARCHAR2(100))
6 ON COMMIT DELETE ROWS';
7 strTemp varchar2(1000) :='SELECT * FROM temptest';
8 TYPE ResultData IS REF CURSOR;
9 L_RECORD ResultData;
10 type t_r is record(RQ date,ZS VARCHAR2(100));
11 v_t t_r;
12 v_c integer:=0;
13 begin
14 execute immediate 'select count(*) from user_tables where table_name=upper(:v)'
15 into v_c using 'temptest';
16 if v_c=0 then
17 execute immediate str;
18 end if;
19 execute immediate 'insert into temptest select hiredate,empno from emp';
20 open L_RECORD for strTemp;
21 loop
22 fetch L_RECORD into v_t;
23 exit when L_RECORD%notfound;
24 dbms_output.put_line('empno:'||v_t.ZS||' date:'||to_char(v_t.RQ,'yyyy-mm-dd'));
25 end loop;
26 commit;
27 end;
28 /过程已创建。SQL> exec p_t;
empno:7369 date:1980-12-17
empno:7499 date:1981-02-20
empno:7521 date:1981-02-22
empno:7566 date:1981-04-02
empno:7654 date:1981-09-28
empno:7698 date:1981-05-01
empno:7782 date:1981-06-09
empno:7788 date:1987-04-19
empno:7839 date:1981-11-17
empno:7844 date:1981-09-08
empno:7876 date:1987-05-23
empno:7900 date:1981-12-03
empno:7902 date:1981-12-03
empno:7934 date:1982-01-23 PL/SQL 过程已成功完成。