我们用 Oracle 的 SQL*Plus 执行 SQL 语句。首先看看 Oracle 的版本号:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
其次打开服务器端的输出信息:
set serveroutput on;
--
-- column type is char, but parameter type is varchar.
--
drop table foo;
create table foo(fno number(4), fname char(20));
insert into foo values(1, 'smith');
insert into foo values(2, 'john');
SQL> select * from foo where fname = 'smith';
FNO FNAME
---------- --------------------
1 smith
SQL> select * from foo where fname = cast('smith' as char(20));
FNO FNAME
---------- --------------------
1 smith
注意,varchar2 找不出结果:
SQL> select * from foo where fname = cast('smith' as varchar2(20));
no rows selected
现在,我们换成存储过程试试。
create or replace procedure foop(bname varchar2)
as
v_fno number(4);
v_fname char(20);
begin
dbms_output.put_line('in procedure');
select fno, fname into v_fno, v_fname from foo where fname = bname;
dbms_output.put_line('results: ' || v_fno || ',' || v_fname );
exception
when no_data_found then
dbms_output.put_line('employee ' || bname || ' not found');
when others then
dbms_output.put_line('the following is sqlerrm:');
dbms_output.put_line(sqlerrm);
dbms_output.put_line('the following is sqlcode:');
dbms_output.put_line(sqlcode);
end;
/
call foop('smith');
用 varchar2 做存储过程的参数也是找不出结果。
Expected result:
<result>
in procedure
employee smith not found
Call completed.
</result>
create or replace procedure foop2(bname char)
as
v_fno number(4);
v_fname char(20);
begin
dbms_output.put_line('in procedure');
select fno, fname into v_fno, v_fname from foo where fname = bname;
dbms_output.put_line('results: ' || v_fno || ',' || v_fname );
exception
when no_data_found then
dbms_output.put_line('employee ' || bname || ' not found');
when others then
dbms_output.put_line('the following is sqlerrm:');
dbms_output.put_line(sqlerrm);
dbms_output.put_line('the following is sqlcode:');
dbms_output.put_line(sqlcode);
end;
/
call foop2('smith');
用 char 作为存储过程的参数,还是能够找出结果的。
Expected results:
<result>
in procedure
results: 1,smith
Call completed.
</result>
下面我们测试一下列的类型是 varchar2 的情况。
--
-- column type is varchar, but parameter type is char.
--
drop table foo;
create table foo(fno number(4), fname varchar2(20));
insert into foo values(1, 'smith');
insert into foo values(2, 'john');
SQL> select * from foo where fname = 'smith';
FNO FNAME
---------- --------------------
1 smith
SQL> select * from foo where fname = cast('smith' as varchar2(20));
FNO FNAME
---------- --------------------
1 smith
注意,用 char 是找不出结果的:
SQL> select * from foo where fname = cast('smith' as char(20));
no rows selected
create or replace procedure foop(bname char)
as
v_fno number(4);
v_fname varchar2(20);
begin
dbms_output.put_line('in procedure');
select fno, fname into v_fno, v_fname from foo where fname = bname;
dbms_output.put_line('results: ' || v_fno || ',' || v_fname );
exception
when no_data_found then
dbms_output.put_line('employee ' || bname || ' not found');
when others then
dbms_output.put_line('the following is sqlerrm:');
dbms_output.put_line(sqlerrm);
dbms_output.put_line('the following is sqlcode:');
dbms_output.put_line(sqlcode);
end;
/
call foop('smith');
存储过程的参数为 char 时,居然也可以找出该记录。
Expected result:
<result>
in procedure
results: 1,smith
Call completed.
</result>
create or replace procedure foop2(bname varchar2)
as
v_fno number(4);
v_fname varchar2(20);
begin
dbms_output.put_line('in procedure');
select fno, fname into v_fno, v_fname from foo where fname = bname;
dbms_output.put_line('results: ' || v_fno || ',' || v_fname );
exception
when no_data_found then
dbms_output.put_line('employee ' || bname || ' not found');
when others then
dbms_output.put_line('the following is sqlerrm:');
dbms_output.put_line(sqlerrm);
dbms_output.put_line('the following is sqlcode:');
dbms_output.put_line(sqlcode);
end;
/
call foop2('smith');
存储过程参数为 varchar2 时当然更应该能找出该记录了。
Expected results:
<result>
in procedure
results: 1,smith
Call completed.
</result>
1. http://www.psoug.org/reference/dbms_output.html