方便的话把你的过程贴出来看看 还有仿照下面的方法执行的建表语句会快一点: create table empa nologging as select * from emp;
declare type Bus_Fee_Stat is record ( unit_name varchar2(20), begin_time date, end_tiem date, booktype number(10), send_amount number(5), send_man varchar(10), fact_count number(8), discount_rate number(3), sum_fee number(10), fact_fee number(9), ); Fee_State Bus_Fee_Stat; begin select distinct A.SHORTNAME as 委托单位,A.sendman as 送样人,A.discount as 折扣率, B.certbooktype as 证书类型, B.amount as 送检数量,B.CERTIFICATEAMOUNT as 实际检验数量 ,C.feesum as 应收费,C.factfeesum as 实际收费,C.Recdate as 开始日期,C.Recdate as 结束日期 into Fee_State from client A,regformlist B, regform C where B.regformky=C.OBJECTID and C.recdate>to_date('2010-3-1','YYYY-MM-DD') order by A.shortname;
不好意思 我看错了。你实际写的是select into 格式的语句,这样是可以的。但是有个问题,一个record只能接受一行的数据,你可以参考下面的,或许能对你有帮助:declare type ret is record( e number, en varchar2(10)); type tab_ret is table of ret index by binary_integer; t tab_ret; i number := 0; begin for cur in (select empno, ename from EMP) loop i := i + 1; t(i).e := cur.empno; t(i).en := cur.ename; end loop; for j in 1 .. t.last loop dbms_output.put_line(t(j).e); dbms_output.put_line(t(j).en); end loop; end;
谢谢了! 如果用循环的话,数据量大了以后,速度上面应该会有所影响。这个还可以优化吧? 另外想请问下: create or replace procedure 业务量和费用统计 /*( unit_name client.name%type, begin_date regform.recdate%type, end_date regform.recdate%type, book_type regformlist.certbooktype%type , send_amount regformlist.amount%type, discount_rate client.discount%type ) */ is begin select distinct client.shortname,client.sendman,client.discount,regformlist.certbooktype, regformlist.amount, regformlist.certificateamount,regform.regdate,regform.feesum, regform.factfeesum from client, regformlist,regform where regform.objectid=regformlist.regformky; end;为啥子这个存储过程会报错呢?单独执行中间的sql语句是没有问题的. 如果我想加参数应该砸写呢?这几个参数都是可以根据用户的输入条件来查询! 犯难中。。错误提示:PLS-00428:在select 语句中缺少INTO子句
这就是我上面说的不能单独使用select语句,形如select * from tt是不能在begin end;之间使用的,你可以这样用select .. into .. from .. 。 这应该就是pl/sql和sql一个的区别。如果你要dbms_output.put_line()结果,除了循环也没有什么好方法 了。
create or replace procedure 业务量和费用统计 ( U_name varchar2,B_date date,E_date date,S_amount number,B_type number,Cus_discount number ) declareType Tabtype is record ( unit_name client.name%type, begin_date regform.recdate%type, end_date regform.recdate%type, book_type regformlist.certbooktype%type, --send_amount regformlist.amount%type, discount_rate client.discount%type ) Type Tabtype Is Table Of Tabtype Index By Binary_Integer; --指定索引列为主键约束和唯一约束列或自增Integer。可用列为Record类型或%RowType类型。 gettype Tabtype;v_sql varchar2(2000); v_num number; v_num:=1; v_sql:='select A.sendman 送样人,C.amount 送检数量,C.Certificateamount 实际检验数量, B.Feesum 应收费,B.Factfeesum 实际收费 from client A,regform B,regformlist C'; is begin if U_name is null then ---判段参数委托单位是否为空 v_sql:=v_sql||'select client.shortname 委托单位 from client'; --是,返回所有单位名 else v_sql:=v_sql||'select client.shortname 委托单位 from client where unitname:=U_name';--否,返回指定参数的单位名 end if; if B_date is null and E_date is null then--判断送检的时间段为空 v_sql:=v_sql||'select regdate from regform where regdate>to_date('','YYYY-MM-DD')'; --???默认送检时间为当年的1月1日到今日 else v_sql:=v_sql||'select regdate from regform where regdate between begin_date and end_date'; end if; if B_type is null then--判断证书类型参数是否为空 v_sql:=v_sql||'select certbooktype from regformlist';--为空则统计所以的证书类型 else v_sql:=v_sql||'select certbooktype from regformlist where certbooktype:=book_type';--不为空则按指定的类型进行统计 end if; if Cus_discount is null then--判断证书类型 v_sql:=v_sql||'select discount from client'; --为空则返回所有折扣率 else v_sql:=v_sql||'select discount from client where discount:=discount_rate';--不为空则查询出指定的折扣率 end if; --执行SQL语句并将数据插入到临时表中 for varR in(||v_sql||) loop gettype(v_num):=varR; v_num:=v_num+1; end loop;/* select distinct client.shortname 委托单位,client.sendman 送样人,client.discount 折扣率,regformlist.certbooktype 证书类型, regformlist.amount 送检数量, regformlist.certificateamount 实际检验数量,regform.regdate 送检日期,regform.feesum 应收费, regform.factfeesum 实际收费 from client, regformlist,regform where regform.objectid=regformlist.regformky order by client.shortname;*/ end; 你好,现在这个比较完整了,但是编译报错。我自己找了半天也没有发现哪错了。可以帮我看看吗? 谢谢了! PROCEDURE SA.业务量和费用统计 编译错误错误:PLS-00103: 出现符号 "DECLARE"在需要下列之一时: ; is with authid as cluster order using external deterministic parallel_enable pipelined result_cache 行:5 文本:declare错误:PLS-00103: 出现符号 "TYPE"在需要下列之一时: ; 行:15 文本:Type Tabtype Is Table Of Tabtype Index By Binary_Integer; --指定索引列为主键约束和唯一约束列或自增Integer。可用列为Record类型或%RowType类型。PROCEDURE SA.业务量和费用统计 编译错误错误:PLS-00103: 出现符号 "DECLARE"在需要下列之一时: ; is with authid as cluster order using external deterministic parallel_enable pipelined result_cache 行:5 文本:declare错误:PLS-00103: 出现符号 "TYPE"在需要下列之一时: ; 行:15 文本:Type Tabtype Is Table Of Tabtype Index By Binary_Integer; --指定索引列为主键约束和唯一约束列或自增Integer。可用列为Record类型或%RowType类型。
指点个方法也可以吧? 这样说, 用record你确定可以吧?
create table empa nologging as select * from emp;
(
unit_name varchar2(20),
begin_time date,
end_tiem date,
booktype number(10),
send_amount number(5),
send_man varchar(10),
fact_count number(8),
discount_rate number(3),
sum_fee number(10),
fact_fee number(9),
);
Fee_State Bus_Fee_Stat;
begin
select distinct A.SHORTNAME as 委托单位,A.sendman as 送样人,A.discount as 折扣率,
B.certbooktype as 证书类型, B.amount as 送检数量,B.CERTIFICATEAMOUNT as 实际检验数量
,C.feesum as 应收费,C.factfeesum as 实际收费,C.Recdate as 开始日期,C.Recdate as 结束日期 into Fee_State
from client A,regformlist B, regform C
where B.regformky=C.OBJECTID and C.recdate>to_date('2010-3-1','YYYY-MM-DD') order by A.shortname;
dbms_output.put_line(Fee_State.unit_name);
dbms_output.put_line(Fee_State.send_man);
dbms_output.put_line(Fee_State.discount_rate);
dbms_output.put_line(Fee_State.booktype);
dbms_output.put_line(Fee_State.send_amount);
dbms_output.put_line(Fee_State.fact_count);
dbms_output.put_line(Fee_State.sum_fee);
dbms_output.put_line(Fee_State.fact_fee);
dbms_output.put_line(Fee_State.begin_time);
dbms_output.put_line(Fee_State.end_tiem);
end;
就这个东东! 还没有嵌套到存储过程中
2需求不清楚
3单说你创建表慢的解决方法:可以create table empa nologging as select * from emp;--加一个nologging 参数。
type ret is record(
e number,
en varchar2(10));
type tab_ret is table of ret index by binary_integer;
t tab_ret;
i number := 0;
begin for cur in (select empno, ename from EMP) loop
i := i + 1;
t(i).e := cur.empno;
t(i).en := cur.ename;
end loop;
for j in 1 .. t.last loop
dbms_output.put_line(t(j).e);
dbms_output.put_line(t(j).en);
end loop;
end;
谢谢了! 如果用循环的话,数据量大了以后,速度上面应该会有所影响。这个还可以优化吧?
另外想请问下:
create or replace procedure 业务量和费用统计
/*(
unit_name client.name%type,
begin_date regform.recdate%type,
end_date regform.recdate%type,
book_type regformlist.certbooktype%type ,
send_amount regformlist.amount%type,
discount_rate client.discount%type
) */
is
begin
select distinct client.shortname,client.sendman,client.discount,regformlist.certbooktype,
regformlist.amount, regformlist.certificateamount,regform.regdate,regform.feesum,
regform.factfeesum from client, regformlist,regform
where regform.objectid=regformlist.regformky;
end;为啥子这个存储过程会报错呢?单独执行中间的sql语句是没有问题的.
如果我想加参数应该砸写呢?这几个参数都是可以根据用户的输入条件来查询! 犯难中。。错误提示:PLS-00428:在select 语句中缺少INTO子句
(
U_name varchar2,B_date date,E_date date,S_amount number,B_type number,Cus_discount number
)
declareType Tabtype is record
(
unit_name client.name%type,
begin_date regform.recdate%type,
end_date regform.recdate%type,
book_type regformlist.certbooktype%type,
--send_amount regformlist.amount%type,
discount_rate client.discount%type
)
Type Tabtype Is Table Of Tabtype Index By Binary_Integer; --指定索引列为主键约束和唯一约束列或自增Integer。可用列为Record类型或%RowType类型。
gettype Tabtype;v_sql varchar2(2000);
v_num number;
v_num:=1;
v_sql:='select A.sendman 送样人,C.amount 送检数量,C.Certificateamount 实际检验数量, B.Feesum 应收费,B.Factfeesum 实际收费 from client A,regform B,regformlist C';
is
begin if U_name is null then ---判段参数委托单位是否为空
v_sql:=v_sql||'select client.shortname 委托单位 from client'; --是,返回所有单位名
else
v_sql:=v_sql||'select client.shortname 委托单位 from client where unitname:=U_name';--否,返回指定参数的单位名
end if; if B_date is null and E_date is null then--判断送检的时间段为空
v_sql:=v_sql||'select regdate from regform where regdate>to_date('','YYYY-MM-DD')'; --???默认送检时间为当年的1月1日到今日
else
v_sql:=v_sql||'select regdate from regform where regdate between begin_date and end_date';
end if; if B_type is null then--判断证书类型参数是否为空
v_sql:=v_sql||'select certbooktype from regformlist';--为空则统计所以的证书类型
else
v_sql:=v_sql||'select certbooktype from regformlist where certbooktype:=book_type';--不为空则按指定的类型进行统计
end if; if Cus_discount is null then--判断证书类型
v_sql:=v_sql||'select discount from client'; --为空则返回所有折扣率
else
v_sql:=v_sql||'select discount from client where discount:=discount_rate';--不为空则查询出指定的折扣率
end if;
--执行SQL语句并将数据插入到临时表中
for varR in(||v_sql||) loop
gettype(v_num):=varR;
v_num:=v_num+1;
end loop;/* select distinct client.shortname 委托单位,client.sendman 送样人,client.discount 折扣率,regformlist.certbooktype 证书类型,
regformlist.amount 送检数量, regformlist.certificateamount 实际检验数量,regform.regdate 送检日期,regform.feesum 应收费,
regform.factfeesum 实际收费 from client, regformlist,regform
where regform.objectid=regformlist.regformky order by client.shortname;*/
end;
你好,现在这个比较完整了,但是编译报错。我自己找了半天也没有发现哪错了。可以帮我看看吗?
谢谢了!
PROCEDURE SA.业务量和费用统计 编译错误错误:PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
; is with authid as
cluster order using external deterministic parallel_enable
pipelined result_cache
行:5
文本:declare错误:PLS-00103: 出现符号 "TYPE"在需要下列之一时:
;
行:15
文本:Type Tabtype Is Table Of Tabtype Index By Binary_Integer; --指定索引列为主键约束和唯一约束列或自增Integer。可用列为Record类型或%RowType类型。PROCEDURE SA.业务量和费用统计 编译错误错误:PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
; is with authid as
cluster order using external deterministic parallel_enable
pipelined result_cache
行:5
文本:declare错误:PLS-00103: 出现符号 "TYPE"在需要下列之一时:
;
行:15
文本:Type Tabtype Is Table Of Tabtype Index By Binary_Integer; --指定索引列为主键约束和唯一约束列或自增Integer。可用列为Record类型或%RowType类型。
declare是匿名块用的。