各位大虾,我创建了一个存储过程代码如下
create or REPLACE PROCEDURE p_insertcertstorage(
attrib2 in varchar2(100),
attrib4 in varchar2(100),
startno in varchar2(30),
nolens in number,
nocounts in number
)
as
DECLARE
counts number;
sqls varchar2(3000);
nos varchar2(30);
begin
counts:=0;
loop
if counts<=nocounts then
nos:=lpad(to_number(startno)+counts,nolens,'0') ;
sqls:=insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,nos,'0',attrib2,attrib4);
execute sqls;
counts:=counts+1;
end if
end loop
end创建时候不报错,但是创建了之后上面有个红色叉叉,用hibernate出错
03-13 15:13:35.353 ERROR [JDBCExceptionReporter.java:78] ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 HYXADMIN.P_INSERTCERTSTORAGE 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignoredorg.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
我在oracle 里面测试,提示程序包或函数处于无效状态
create or REPLACE PROCEDURE p_insertcertstorage(
attrib2 in varchar2(100),
attrib4 in varchar2(100),
startno in varchar2(30),
nolens in number,
nocounts in number
)
as
DECLARE
counts number;
sqls varchar2(3000);
nos varchar2(30);
begin
counts:=0;
loop
if counts<=nocounts then
nos:=lpad(to_number(startno)+counts,nolens,'0') ;
sqls:=insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,nos,'0',attrib2,attrib4);
execute sqls;
counts:=counts+1;
end if
end loop
end创建时候不报错,但是创建了之后上面有个红色叉叉,用hibernate出错
03-13 15:13:35.353 ERROR [JDBCExceptionReporter.java:78] ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 HYXADMIN.P_INSERTCERTSTORAGE 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignoredorg.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
我在oracle 里面测试,提示程序包或函数处于无效状态
解决方案 »
- 100分在线等:SQL Developer转移SQL Server Express问题求助
- 使用WCF访问oracle是不是每次都要建立OracleConnection
- number(11)
- 谁用过ORACLE 中的PROC*C/C++预编译器,进来一下。。。。
- 在oracle中如何实现跨月多表查询?
- Oracle 10g 增加日志成员为什么是 INVALID ?
- 问一个初级问题,oracle9i可以装在windows xp professional版上吗,谢谢!
- 如何在不同用户之间导入dmp文件!
- 修改表里一个字段的类型,怎样写SQL语句?
- 安装oracle12c,无法登陆,报:ora-12560 tns 协议适配器错误,求。。。
- 请教个SQL语句
- 如何写这样的代码
会有窗口把错误信息给你输出的呀。
存储过程改了下
CREATE OR REPLACE PROCEDURE p_insertcertstorage(
attrib2 in varchar2,
attrib4 in varchar2,
startno in varchar2,
nolens in number,
nocounts in number
)
as
counts number;
sqls varchar2(3000);
nos varchar2(30);
begin
counts:=0;
loop
if counts<=nocounts then
nos:=lpad(to_number(startno)+counts,nolens,'0') ;
sqls:='insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,nos,''0'',attrib2,attrib4)';
execute immediate (sqls);
counts:=counts+1;
end if
end loopend
在end loop那里 出现符号“end”在需要下列之一时: ;
-- 一般这样写循环块撒:例如循环50次:
FOR i in 1..50 LOOP
...END LOOP;
DECLARE
counts number;
sqls varchar2(3000);
nos varchar2(30);
换成
is
counts number;
sqls varchar2(3000);
nos varchar2(30);
看看
CREATE OR REPLACE PROCEDURE p_insertcertstorage(
attrib2 in varchar2,
attrib4 in varchar2,
startno in varchar2,
nolens in number,
nocounts in number
)
as
counts number;
sqls varchar2(3000);
nos varchar2(30);
begin
counts:=0;
WHILE counts<=nocounts LOOP
nos:=lpad(to_number(startno)+counts,nolens,'0') ;
sqls:='insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,nos,''0'',attrib2,attrib4)';
execute immediate (sqls);
counts:=counts+1;
END LOOP;END;
dbms_sql.execute(sqls) ;
可是提示找不到
就这一个错,其他都是语法问题
CREATE OR REPLACE PROCEDURE p_insertcertstorage(
cattrib2 in varchar2,
cattrib4 in varchar2,
cstartno in varchar2,
cnolens in number,
cnocounts in number
)
as
counts number;
sqls varchar2(3000);
nos varchar2(30);
begin
counts:=0; while counts<=cnocounts
loop
nos:=lpad(to_number(cstartno)+counts,cnolens,'0') ;
sqls:='insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,''0'',cattrib2,cattrib4)';
execute immediate sqls;
counts:=counts+1;
end loop;
end p_insertcertstorage;按照上面的换成这句
execute immediate sqls;提示此列在这里不允许
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,cnos,'0',cattrib2,cattrib4)参数并没有放进去
-- 改成while 循环撒:
CREATE OR REPLACE PROCEDURE p_insertcertstorage(
attrib2 in varchar2,
attrib4 in varchar2,
startno in varchar2,
nolens in number,
nocounts in number
)
as
counts number;
sqls varchar2(3000);
nos varchar2(30);
begin
counts:=0;
WHILE counts<=nocounts LOOP
nos:=lpad(to_number(startno)+counts,nolens,'0') ;
sqls:='insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,:nos,''0'',:attrib2,:attrib4)';
execute immediate sqls using nos,attrib2,attrib4; counts:=counts+1;
END LOOP;END;
CREATE OR REPLACE PROCEDURE p_insertcertstorage(
attrib2 in varchar2,
attrib4 in varchar2,
startno in varchar2,
nolens in number,
nocounts in number
)
as
counts number;
sqls varchar2(3000);
nos varchar2(30);
BEGIN
counts:=0;
WHILE counts<=nocounts LOOP
nos:=lpad(to_number(startno)+counts,nolens,'0') ;
insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4)
values(certstorageseq.nextval,nos,'0',attrib2,attrib4);
counts:=counts+1;
END LOOP;
COMMIT;
END;
/-- 你的PL/SQL功底太差啦,像没入过门似的!
cattrib2 in varchar2,
cattrib4 in varchar2,
cstartno in varchar2,
cnolens in number,
cnocounts in number
)
as
counts number;
sqls varchar2(3000);
nos varchar2(30);
begin
counts:=0;
while counts<cnocounts
loop
nos:=lpad(to_number(cstartno)+counts,cnolens,'0') ;
sqls:='insert into certstorage(cpnseq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval,'||nos||',''0'','||cattrib2||','||cattrib4||')';
execute immediate sqls;
counts:=counts+1;
end loop;
end p_insertcertstorage;
代码已经成功,谢谢各位关注,
我传这几个参数,是为了生成记录数
例如起始值000000000000001
长度100000000
就在数据库中生成从
000000000000001
000000100000000
的记录,
我刚才在我本机跑过,生成十万条记录都要10来秒,这样太慢,请问有什么方式可以提升速度
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[p_Midpoint](@postClass varchar(20),@jichaN decimal(16,8))
as
begin
declare @row int
declare @MidPointID varchar(20)
declare @maxscore decimal(16,8),@minscore decimal(16,8),@count int,@jicha decimal(16,8)
select @maxscore=max(score),@minscore=min(score),@count=count(*) from Hr_MidPoint where postType=@postClass;
set @row=0;
set @jicha=(@maxscore-@minscore)/count(*)
if @jichaN>0
set @jicha=@jichaN
declare c_mid cursor for select MidPointID from Hr_MidPoint where postType=@postClass order by score;
Fetch Next From c_mid Into @MidPointID;
while @@Fetch_status = 0
begin
set @row=@row+1;
if @row<2
update Hr_MidPoint set levelCount=@count,levelPoor=@jicha,level=1,minScore=@minscore, maxScore=@minscore+@jicha,midScore=@minscore+@minscore*0.5 where MidPointID=@MidPointID;
else
update Hr_MidPoint set levelCount=@count,levelPoor=@jicha,level=@row,minScore=@minscore+(@jicha+1)*(@row-1), maxScore=@minscore+(@jicha+1)*(@row-1)+@jicha,midScore=@minscore+(@jicha+1)*(@row-1)+@jicha*0.5 where MidPointID=@MidPointID;
Fetch Next From c_mid Into @MidPointID;
end
Close Cur_Mid;
Deallocate Cur_Mid;
End
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[Up_WageLevel1]
@orgid varchar(20)
As
Select Distinct PayGrade1 As T0 Into #Temp From Hr_WageLevel where Default01=1 and organId=@orgid
Declare @Cols Int, @iCount Int, @sql01 NVarchar(4000), @sql02 NVarchar(4000)
Set @sql01 = ''
Set @iCount = 0
Select @Cols = Max(Pay1) From Hr_WageLevel where Default01=1 and organId=@orgidWhile @Cols > 0
Begin
Set @iCount = @iCount + 1
Set @sql01 = @sql01 + 'T'+ Cast(@iCount As Varchar(2))+' Decimal(8,2),'
Set @Cols = @Cols - 1
End if (Len(@sql01) > 1)
Begin
Set @sql01 = 'Alter Table #Temp Add '+ SubString(@sql01, 0, Len(@sql01) )
Exec(@sql01)
End Declare @Pay1 Int , @PayGrade1 Int ,@Wage Decimal(8,2)Declare Cur_Row Cursor For
Select T0 From #Temp
Open Cur_Row;
Fetch Next From Cur_Row Into @PayGrade1;
While @@Fetch_status = 0
Begin
Declare Cur_Col Cursor For
Select Pay1,Wage From Hr_WageLevel Where PayGrade1 = @PayGrade1 and Default01=1 and organId=@orgid
Open Cur_Col;
Fetch Next From Cur_Col Into @Pay1, @Wage
While @@Fetch_status = 0
Begin
Set @sql02 = 'UpDate #Temp Set T' + Cast(@Pay1 As Varchar(10)) + '='
+ Cast(@Wage As Varchar(10)) + ' Where T0 = ' + Cast(@PayGrade1 As Varchar(10))
Exec(@sql02)
Fetch Next From Cur_Col Into @Pay1, @Wage
End
Close Cur_Col;
Deallocate Cur_Col; Fetch Next From Cur_Row Into @PayGrade1 ;
End
Close Cur_Row;
Deallocate Cur_Row;Select * From #Temp
,atrrib4 in varchar2
,startno in varchar2
,nolens in number
,nocounts in number
)
is
v_counts number;
v_sqls varchar2(3000);
v_nos varchar2(30);
v_atrrib2 varchar2(100);
v_atrrib4 varchar2(100);
v_startno varchar2(30);
v_nolens number;
v_nocounts number;
begin
v_counts := 0;
v_atrrib2 := atrrib2;
v_atrrib4 := atrrib4;
v_startno := startno;
v_nolens := nolens;
v_nocounts := nocounts;
while(v_counts <=v_nocounts)
loop
v_nos := lpad(to_number(v_startno)+v_counts,v_nolens,'0');
v_sqls := 'insert into certstorage(cpnsq,presserialno,status,attrib2,attrib4) values(certstorageseq.nextval'
||','||v_nos
||','||chr(39)||0||chr(39)
||','||v_atrrib2
||','||v_atrrib4
||')';
execute immediate v_sqls;
v_counts := v_counts + 1;
end loop;
end p_insertStorage;这样写,我在plsql编译通过