--建立表
create table mytestemployee
(
Num NVARCHAR2(8) not null primary key,
Name NVARCHAR2(4) not null,
Addr NVARCHAR2(8) not null,
Zip number(6) not null,
Tel number(8) not null,
Email VARCHAR2(15),
Depno number(2) not null,
Birth date not null,
Sex NVARCHAR2(1) not null
);--删除表
drop table mytestemployee;
--查询表
select * from mytestemployee;declare
myindex int:=1;
maxrecords constant int:=100000;
begin
for myindex in 1..maxrecords
loop
insert into mytestemployee values(TO_CHAR(myindex,'099999'),'新忠','湖北',
430074,87598405,'Null',2,to_date('1985-2-1','YYYY-MM-DD'),'男');
end loop;
commit;
end;
select * from mytestemployee where Num='000001';
////为何我上面的条件查询,显示不出数据;???????
create table mytestemployee
(
Num NVARCHAR2(8) not null primary key,
Name NVARCHAR2(4) not null,
Addr NVARCHAR2(8) not null,
Zip number(6) not null,
Tel number(8) not null,
Email VARCHAR2(15),
Depno number(2) not null,
Birth date not null,
Sex NVARCHAR2(1) not null
);--删除表
drop table mytestemployee;
--查询表
select * from mytestemployee;declare
myindex int:=1;
maxrecords constant int:=100000;
begin
for myindex in 1..maxrecords
loop
insert into mytestemployee values(TO_CHAR(myindex,'099999'),'新忠','湖北',
430074,87598405,'Null',2,to_date('1985-2-1','YYYY-MM-DD'),'男');
end loop;
commit;
end;
select * from mytestemployee where Num='000001';
////为何我上面的条件查询,显示不出数据;???????
select to_char(1, '099999'),length(to_char(1, '099999')),
trim(to_char(1, '099999')), length(trim(to_char(1, '099999')) )from dual;A1 A2 A3 A4
---------- ---------- ---------- ----------
000001 7 000001 6
你这样做的话[TO_CHAR(myindex,'099999')]它会在前面给你加空格。DECLARE
myindex INT :=1;
maxrecords constant INT:=10;
BEGIN
FOR myindex IN 1..maxrecords
LOOP
INSERT
INTO mytestemployee VALUES
(
TO_CHAR(myindex,'FM99000000'),
'新忠',
'湖北',
430074,87598405,
'Null',
2,
to_date('1985-2-1','YYYY-MM-DD'),
'男'
);
END LOOP;
COMMIT;
END
select * from mytestemployee where TRIM(Num)='000001';--用trim()把空格去掉
select * from mytestemployee where trim(Num)='000001';或者
update set mytestemployee num=trim(num)
insert into mytestemployee values(trim(TO_CHAR(myindex,'099999')),'新忠','湖北',
430074,87598405,'Null',2,to_date('1985-2-1','YYYY-MM-DD'),'男');