有一数据库test,有字段line_num,line_code,想让line_num列的内容为“000”,“001”等,这样,如何实现呢?
我写了点,但不对
declare
a number;
declare
i number;
select count(*) into a from TEST;
begin
if a>0 then
while i<a
update TEST set LINE_NUM='"+i+"';
i=i+1;
end
COMMIT;
我写了点,但不对
declare
a number;
declare
i number;
select count(*) into a from TEST;
begin
if a>0 then
while i<a
update TEST set LINE_NUM='"+i+"';
i=i+1;
end
COMMIT;
v_c1 number := 1;
v_c2 varchar2(3);
i number := 1;
begin
v_c1 := v_c1 + 1;
v_c2 := to_char(v_c1);
while length(v_c2) < 3 loop
v_c2 := '0'||v_c2;
end loop;
dbms_output.put_line(v_c2);
end;
例如:select lpad(0,3,'0') from dual;--000
select lpad(1,3,'0') from dual;--001PS:
LZ的这句update TEST set LINE_NUM='"+i+"';是update所有记录吗?
select lpad('1',3,'0') from dual;
--2
select to_char(1,'000') from dual;
a number;
i number:=0;
begin
select count(*) into a from TEST;
begin
if a>0 then
while i<a loop
if length(i)=1 then
update TEST set LINE_NUM="00"||i;
elsif length(i)=2 then
update TEST set LINE_NUM="0"||i;
else
update TEST set LINE_NUM="0"||i;
end if;
i=i+1;
end loop;
end if;
COMMIT;
end;
--前面的改下
declare
a number;
i number:=0;
begin
select count(*) into a from TEST;
begin
if a>0 then
while i<a loop
if length(i)=1 then
update TEST set LINE_NUM="00"||i;
elsif length(i)=2 then
update TEST set LINE_NUM="0"||i;
else
update TEST set LINE_NUM=i;
end if;
i=i+1;
end loop;
end if;
COMMIT;
end;--可以用lpad函数
declare
a number;
i number:=0;
begin
select count(*) into a from TEST;
begin
if a>0 then
while i<a loop
update TEST set LINE_NUM=lpad(i,3,0);
i=i+1;
end loop;
end if;
COMMIT;
end;
--可以用lpad函数
declare
a number;
i number:=0;
begin
select count(*) into a from TEST;
if a>0 then
while i<a loop
update TEST set LINE_NUM=lpad(i,3,0);
i=i+1;
end loop;
end if;
COMMIT;
end;
--多了个begin