create or replace procedure PRO_DZBM_UPDATE Is
v_num Number;
v_xh Varchar2(30);
v_dzdm Varchar2(30);
v_id Varchar2(60);
v_dzbm number;
Cursor cur_dz Is Select t.id,t.DZBM From T_DZK_JBXX t Order By t.ypcsbm;
begin
v_num:=0;
For cur_i In cur_dz Loop
v_num:=v_num+1;
v_id :=cur_i.id;
v_dzbm:=hz_dzbm.nextval;--此处编译通不过,hz_dzbm是sequence 见下
v_xh :=lpad(to_char(v_dzbm),20,'0');
v_dzdm:=v_xh;
Update T_DZK_JBXX t Set t.DZBM=v_dzdm Where t.id=v_id; End Loop;
If (v_num Mod 1000) =0 Then
Commit;
End If ;
Commit;
end PRO_DZBM_UPDATE;create sequence HZ_DZBM
minvalue 1
maxvalue 9999999999999999999
start with 6
increment by 1
nocache;编译时报错:Compilation errors for PROCEDURE PSMIS.PRO_DZBM_UPDATEError: PLS-00357: Table,View Or Sequence reference 'HZ_DZBM.NEXTVAL' not allowed in this context
Line: 15
Text: v_dzbm:=hz_dzbm.nextval;Error: PL/SQL: Statement ignored
Line: 15
Text: v_dzbm:=hz_dzbm.nextval;
v_num Number;
v_xh Varchar2(30);
v_dzdm Varchar2(30);
v_id Varchar2(60);
v_dzbm number;
Cursor cur_dz Is Select t.id,t.DZBM From T_DZK_JBXX t Order By t.ypcsbm;
begin
v_num:=0;
For cur_i In cur_dz Loop
v_num:=v_num+1;
v_id :=cur_i.id;
v_dzbm:=hz_dzbm.nextval;--此处编译通不过,hz_dzbm是sequence 见下
v_xh :=lpad(to_char(v_dzbm),20,'0');
v_dzdm:=v_xh;
Update T_DZK_JBXX t Set t.DZBM=v_dzdm Where t.id=v_id; End Loop;
If (v_num Mod 1000) =0 Then
Commit;
End If ;
Commit;
end PRO_DZBM_UPDATE;create sequence HZ_DZBM
minvalue 1
maxvalue 9999999999999999999
start with 6
increment by 1
nocache;编译时报错:Compilation errors for PROCEDURE PSMIS.PRO_DZBM_UPDATEError: PLS-00357: Table,View Or Sequence reference 'HZ_DZBM.NEXTVAL' not allowed in this context
Line: 15
Text: v_dzbm:=hz_dzbm.nextval;Error: PL/SQL: Statement ignored
Line: 15
Text: v_dzbm:=hz_dzbm.nextval;
Is
v_num Number;
v_xh Varchar2(30);
v_dzdm Varchar2(30);
v_id Varchar2(60);
v_dzbm number;--默认情况下没有显示说明的参数的类型是in(输入型)[没有记错的话],不能够对in型的参数赋值
Cursor cur_dz Is Select t.id,t.DZBM From T_DZK_JBXX t Order By t.ypcsbm;
begin
v_num:=0;
For cur_i In cur_dz Loop
v_num:=v_num+1;
v_id :=cur_i.id;
v_dzbm:=hz_dzbm.nextval;--此处编译通不过,hz_dzbm是sequence 见下
一下code略
之前的版本只能先查出序列的值再into给变量
create or replace procedure PRO_DZBM_UPDATE Is
v_num Number;
v_xh Varchar2(30);
v_dzdm Varchar2(30);
v_id Varchar2(60);
v_dzbm number;
Cursor cur_dz Is Select t.id,t.DZBM From T_DZK_JBXX t Order By t.ypcsbm;
begin
v_num:=0;
For cur_i In cur_dz Loop
v_num:=v_num+1;
v_id :=cur_i.id;
select hz_dzbm.nextval into v_dzbm from dual; v_xh :=lpad(to_char(v_dzbm),20,'0');
v_dzdm:=v_xh;
Update T_DZK_JBXX t Set t.DZBM=v_dzdm Where t.id=v_id; End Loop;
If (v_num Mod 1000) =0 Then
Commit;
End If ;
Commit;
end PRO_DZBM_UPDATE;--你的写法要到11G才支持
Compilation errors for PROCEDURE SCOTT.PROC_TTTTError: PLS-00363: expression cannot be used as an assignment target