java 创建临时表 oracle_在ORACLE存储过程中创建临时表

在ORACLE存储过程中创建临时表

create or replace procedure select_look

as

str varchar2(100);

begin

str:='select * from emp';

execute immediate str;

end;

在ORACLE存储过程中创建临时表

2007年11月15日 星期四 14:27

create procedure pro

as

str varchar2(100);

begin

str:='CREATE GLOBAL TEMPORARY TABLE TABLENAME (

COL1 VARCHAR2(10),

COL2 NUMBER

) ON COMMIT PRESERVE ROWS' ;

execute immediate str; --使用动态SQL语句来执行

end;

/

存储过程里不能直接使用DDL语句，所以只能使用动态SQL语句来执行

--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表(删除全部行)

--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。

CREATE OR REPLACE PROCEDURE temptest

(p_searchDate IN DATE)

IS

v_count INT;

str varchar2(300);

BEGIN

v_count := 0;

str:='drop table SETT_DAILYTEST';

execute immediate str;

/* str:='CREATE GLOBAL TEMPORARY TABLE SETT_DAILYTEST (

NACCOUNTID NUMBER not null,

NSUBACCOUNTID NUMBER not null)

ON COMMIT PRESERVE ROWS';

execute immediate str; ----使用动态SQL语句来执行

str:='insert into SETT_DAILYTEST (select naccountid,nsubaccountid from sett_dailyaccountbalance)';

execute immediate str;

END temptest;

上面建立一个临时表的存储过程

下面是执行一些操作

CREATE OR REPLACE PROCEDURE PR_DAILYCHECK

(

p_Date IN DATE,

p_Office IN INTEGER,

p_Currency IN INTEGER,

P_Check IN INTEGER,

p_countNum OUT INTEGER)

IS

v_count INT;

BEGIN

v_count := 0;

IF p_Date IS NULL THEN

dbms_output.put_line('´«ÈëµÄ²ÎÊý´íÎó');

ELSE

IF P_Check = 1 THEN

insert into SETT_DAILYTEST (select naccountid,nsubaccountid from sett_dailyaccountbalance

where dtdate = p_Date);

select

count(sd.naccountid) into v_count

from sett_subaccount ss,sett_account sa,sett_dailytest sd

where sd.naccountid = sa.id and sd.nsubaccountid = ss.id and sa.id = ss.naccountid

AND sa.nofficeid = p_Office AND sa.ncurrencyid = p_Currency

and rownum < 2;

COMMIT;

p_countNum := v_count;

dbms_output.put_line(p_countNum);

END IF;

IF P_Check = 2 THEN

insert into SETT_DAILYTEST (select naccountid,nsubaccountid from sett_dailyaccountbalance

where dtdate = p_Date);

select

count(sd.naccountid) into v_count

from sett_cfsubaccount ss,sett_account sa,sett_dailytest sd

where sd.naccountid = sa.id and sd.nsubaccountid = ss.id and sa.id = ss.naccountid

AND sa.nofficeid = p_Office AND sa.ncurrencyid = p_Currency

and rownum < 2;

COMMIT;

p_countNum := v_count;

dbms_output.put_line(p_countNum);

END IF;

END IF;

END PR_DAILYCHECK;

-------------------------------------------------------------------------------------------------------------------------------

oracel 日期函数

to_date(substr(ildgl,2,6),'rrDDD')

insert into scott.f42111 values('mike',600,to_date('2009-04-17 10:17:00','yyyy-mm-dd hh24:mi:ss'))