--1 authid current_user
create or replace procedure table_basic_new(
strtable in varchar2
)
as
v_count number(10);
begin
select count(*) into v_count from user_objects
where object_name = upper('||strtable||');
if v_count > 0 then
execute immediate 'drop table '||strtable||' purge';
end if;
execute immediate 'create table '||strtable||'(
id number not null,
bname varchar2(50),
btotal number,
btimes number,
newbid number not null,
btype number,
bdate varchar(20)
)';
execute immediate'alter table '||strtable||' add constraint Pk_bid primary key(id)';
end;--2,insert and select
create or replace procedure Insert_sms_wwl(
counts in integer,--总记录数
ids in varchar2,--所有连接的Id的字符串
bdate in varchar2--日期
)
as
i integer;
strsql varchar(500);
str varchar2(100);
begin
i:=0;
table_basic_new('sms_newbasicInfo_wwl');
while i<counts
loop
str:=substr(ids,i*2+1,1);
dbms_output.put_line(substr(ids,i*2+1,1));
if str is not null then
strsql:= ' (select sbi.name,sum(srd.DURATION_TOTAL) total,sum(srd.LOGIN_TIMES) times,srd.basic_id,sbi.type,srd.duration_date
from sms_report_duration srd inner join sms_basic_info sbi on srd.basic_id=sbi.id where 1=1
and srd.basic_id ='||substr(ids,i*2+1,1)||' and '||bdate||'
group by srd.basic_id,sbi.type,sbi.name,srd.duration_date)';
dbms_output.put_line(strsql);
execute immediate 'insert into sms_newbasicInfo_wwl values('||i||','||strsql||')';
end if;
-- execute immediate 'insert into sms_newbasicInfo_wwl values('||i||','||substr(ids,i*2+1,1)||')';
i:=i+1;
end loop;
commit;
end;我写了两个,编译都通过了,怎么调用呢。
就是根据条件把一个表中的数据查出来,查到另一个表中。
create or replace procedure table_basic_new(
strtable in varchar2
)
as
v_count number(10);
begin
select count(*) into v_count from user_objects
where object_name = upper('||strtable||');
if v_count > 0 then
execute immediate 'drop table '||strtable||' purge';
end if;
execute immediate 'create table '||strtable||'(
id number not null,
bname varchar2(50),
btotal number,
btimes number,
newbid number not null,
btype number,
bdate varchar(20)
)';
execute immediate'alter table '||strtable||' add constraint Pk_bid primary key(id)';
end;--2,insert and select
create or replace procedure Insert_sms_wwl(
counts in integer,--总记录数
ids in varchar2,--所有连接的Id的字符串
bdate in varchar2--日期
)
as
i integer;
strsql varchar(500);
str varchar2(100);
begin
i:=0;
table_basic_new('sms_newbasicInfo_wwl');
while i<counts
loop
str:=substr(ids,i*2+1,1);
dbms_output.put_line(substr(ids,i*2+1,1));
if str is not null then
strsql:= ' (select sbi.name,sum(srd.DURATION_TOTAL) total,sum(srd.LOGIN_TIMES) times,srd.basic_id,sbi.type,srd.duration_date
from sms_report_duration srd inner join sms_basic_info sbi on srd.basic_id=sbi.id where 1=1
and srd.basic_id ='||substr(ids,i*2+1,1)||' and '||bdate||'
group by srd.basic_id,sbi.type,sbi.name,srd.duration_date)';
dbms_output.put_line(strsql);
execute immediate 'insert into sms_newbasicInfo_wwl values('||i||','||strsql||')';
end if;
-- execute immediate 'insert into sms_newbasicInfo_wwl values('||i||','||substr(ids,i*2+1,1)||')';
i:=i+1;
end loop;
commit;
end;我写了两个,编译都通过了,怎么调用呢。
就是根据条件把一个表中的数据查出来,查到另一个表中。
解决方案 »
- 多个job一起执行后 其中一个无法得到正确结果
- 请问大家能给这段alert注释一下吗?谢谢!
- ORACLE权限管理问题
- 不借助第三方软件,如何想把oracle的数据导入到informix中?
- [oracle9i]请问外健是什么意思,起什么作用?
- What's wrong anout this procedure?
- 在动态SQL中,直接用‘||’构造动态SQL字符串的效率高还是使用BIND_VARIABLE的方法效率高?为什么?
- oracle9i中装库命令,oracle服务器配置--求救!
- 不好解决的trigger问题
- 没有oracle服务端复制文件的权限,Linux的oracle客户端如何获取并使用sqlldr?
- 问个弱弱的SQL问题
- 请教oracle的ORA-01034和ORA-27101的修改方案
set serveroutput on
exec Insert_sms_wwl( '429,474,483','to_char(srd.DURATION_date,'YYYY-MM-DD') >= '2010-10-01' and to_char(srd.DURATION_date,'YYYY-MM-DD') <= '2010-10-31' ' );
就是第二个参数 ,调的时候有问题,看看该怎么写。以下是报错信息: *
第 1 行出现错误:
ORA-06550: 第 1 行, 第 65 列:
PLS-00103: 出现符号 "YYYY"在需要下列之一时:
) , * & = - +
< / > at in is mod remainder not rem => <an exponent (**)>
<> or != or ~= >= <= <> and or like like2 like4 likec between
|| multiset member submultiset
符号 "," 被替换为 "YYYY" 后继续。
ORA-06550: 第 1 行, 第 75 列:
PLS-00103: 出现符号 ") >= "在需要下列之一时:
. ( ) , * @
% & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || member submultiset
符
ORA-06550: 第 1 行, 第 92 列:
PLS-00103: 出现符号 " and to_char(srd.DURATION_date,"在需要下列之一时:
) , * & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec as between from
ORA-06550: 第 1 行, 第 135 列:
PLS-00103: 出现符号 ") <= "在需要下列之一时:
. ( ) , * @
% & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec be
begin
Insert_sms_wwl(3,'abcdefghijklmn',sysdate);
end;
第一个是number型的,第二个是varchar2型的,第三个是date型的
但是,我要的第三个参数,实质上是一个字符串,而这个字符串中有日期的比较,而且必须是这个
'to_char(srd.DURATION_date,'YYYY-MM-DD') >= '2010-10-01' and to_char(srd.DURATION_date,'YYYY-MM-DD') <= '2010-10-31'
那怎么调用呢
counts in integer,--总记录数
ids in varchar2,--所有连接的Id的字符串
bdate in varchar2--日期
)
缺少第三个参数,另外,第二个字符串的拼接,注意引号的嵌套:
exec Insert_sms_wwl( '429,474,483','to_char(srd.DURATION_date,'YYYY-MM-DD') >= '2010-10-01' and to_char(srd.DURATION_date,'YYYY-MM-DD') <= '2010-10-31' ' );