--将表wip.xwp_seq_daily_total_quantity中数据复制给临时表temp_emp
--然后删除临时表temp_emp 中日期为20101118的数据,
--再删除表wip.xwp_seq_daily_total_quantity
--再将临时表temp_emp复制给wip.xwp_seq_daily_total_quantity
这些语句怎么写在存储过程中呢
create table temp_emp as (select * from wip.xwp_seq_daily_total_quantity) ;delete temp_emp where to_char(DATETIME,'YYYYMMDD') ='20101118' ;drop table wip.xwp_seq_daily_total_quantity;create table wip.xwp_seq_daily_total_quantity as (select * from temp_emp);
--然后删除临时表temp_emp 中日期为20101118的数据,
--再删除表wip.xwp_seq_daily_total_quantity
--再将临时表temp_emp复制给wip.xwp_seq_daily_total_quantity
这些语句怎么写在存储过程中呢
create table temp_emp as (select * from wip.xwp_seq_daily_total_quantity) ;delete temp_emp where to_char(DATETIME,'YYYYMMDD') ='20101118' ;drop table wip.xwp_seq_daily_total_quantity;create table wip.xwp_seq_daily_total_quantity as (select * from temp_emp);
解决方案 »
- Access数据库中Update问题
- Oracle 10g 触发器更新问题
- 为什么不用连接池就会报ORA-12519错误?
- 急求:oracle存取速度问题 (50分)
- 多表查询的sql语句写法问题,跪求大家帮助!!
- 为何在方案下看不到新建的用户
- oracle安全的问题
- 第一次运行SQLPLUS时,提示ORACLE initialization or shutdown in progress 是什么意思,应该怎么办??在线等
- 请问我定义的变量如何写在sql语句的where里
- tnsnames.ora,sqlnet.ora,init.ora ,listener.ora分别是用来做什么的,如何配置?
- 请问关于oem的问题
- 大侠们。。。如何给oracle的一个字段进行加密和解密。。?
create or replace procedure p
as
begin
execute immediate 'create table temp_emp as (select * from wip.xwp_seq_daily_total_quantity)'
....
end;
begin
create table temp_emp as(
select * from wip.xwp_seq_daily_total_quantity);
delete temp_emp where to_char(DATETIME, 'YYYYMMDD') = '20101118';
commit;
drop table wip.xwp_seq_daily_total_quantity;
create table wip.xwp_seq_daily_total_quantity as(
select * from temp_emp);
exception
when others then
dbms_output.put_line('操作失败') ; end;
EXECUTE IMMEDIATE 'create table temp_emp as (select * from wip.xwp_seq_daily_total_quantity) ';delete temp_emp where to_char(DATETIME,'YYYYMMDD') ='20101118' ;EXECUTE IMMEDIATE 'drop table wip.xwp_seq_daily_total_quantity';EXECUTE IMMEDIATE 'create table wip.xwp_seq_daily_total_quantity as (select * from temp_emp)';
-------没注意呵呵见谅。。
create or replace procedure exeDDL() as
begin
dbms_utility.exec_ddl_statement('create table temp_emp as(select * from wip.xwp_seq_daily_total_quantity)');
delete temp_emp where to_char(DATETIME, 'YYYYMMDD') = '20101118';
commit;
dbms_utility.exec_ddl_statement('drop table wip.xwp_seq_daily_total_quantity');
dbms_utility.exec_ddl_statement('create table wip.xwp_seq_daily_total_quantity as(select * from temp_emp');
exception
when others then
dbms_output.put_line('操作失败') ; end;
if temp_emp exits then
drop table temp_empend if
if temp_emp exits then
drop table temp_empend if
if temp_emp exits then
drop table temp_empend if
execute immediate 'drop table temp_emp';
execute immediate 'create table temp_emp as(select .....)'
if cnt=0 then
dbms_utility.exec_ddl_statement('drop table temp_emp');
end if;
if cnt=1 then
dbms_utility.exec_ddl_statement('drop table temp_emp');
end if;
num number;
begin
select count(*) into num from user_tables where table_name =upper('temp_emp');
if num>0 then
EXECUTE IMMEDIATE 'drop table temp_emp';
else
EXECUTE IMMEDIATE 'create table temp_emp as (select * from wip.xwp_seq_daily_total_quantity) ';delete temp_emp where to_char(DATETIME,'YYYYMMDD') ='20101118' ;EXECUTE IMMEDIATE 'drop table wip.xwp_seq_daily_total_quantity';EXECUTE IMMEDIATE 'create table wip.xwp_seq_daily_total_quantity as (select * from temp_emp)';
end if;
end;
使用存储过程时,发生了这个错误,
dim objCmd
set objCmd =server.createobject("adodb.command")
objCmd.ActiveConnection= cn
objCmd.CommandText="update_total_quantity"
objCmd.CommandType=adCmdStoredProc
objCmd.execute 存储过程如下
create or replace procedure update_total_quantity() as
begin
dbms_utility.exec_ddl_statement('create Global Temporary table temp_emp as(select * from wip.xwp_seq_daily_total_quantity)');
delete temp_emp where to_char(DATETIME, 'YYYYMMDD') = '20101116';
commit;
dbms_utility.exec_ddl_statement('drop table wip.xwp_seq_daily_total_quantity');
dbms_utility.exec_ddl_statement('create table wip.xwp_seq_daily_total_quantity as(select * from temp_emp');
exception
when others then
dbms_output.put_line('操作失敗') ;
end;select count(*) into cnt from user_tables where table_name = 'temp_emp';
if cnt=1 then
dbms_utility.exec_ddl_statement('drop table temp_emp');
end if;哪位高手帮帮忙
EXEC update_total_quantity
报错:
ORA-00900:sql叙述句无效
--先前写的时候说了 全动态
--执行的时候还得显示授权
--grant create any table to 目标用户create or replace procedure update_total_quantity as
num number;
begin
select count(*) into num from user_tables where table_name =upper('temp_emp');
if num>0 then
EXECUTE IMMEDIATE 'drop table temp_emp';
else
EXECUTE IMMEDIATE 'create table temp_emp as (select * from wip.xwp_seq_daily_total_quantity) ';
end if;
EXECUTE IMMEDIATE 'delete temp_emp where to_char(DATETIME,''YYYYMMDD'') =''20101118''' ;EXECUTE IMMEDIATE 'drop table wip.xwp_seq_daily_total_quantity';EXECUTE IMMEDIATE 'create table wip.xwp_seq_daily_total_quantity as (select * from temp_emp)'; end;
不直接在A表里面操作呢......这个很有问题....