pl/sql 关于变量定义的问题

1.
create or replace procedure  test_prc(p_data_dt in date) IS
e_name emp.ename%type;     
begin                                               

select ename into e_name
from emp where hiredate =p_data_dt; 

DBMS_OUTPUT.PUT_LINE(e_name || '---' ||p_data_dt); 
 end;

此时输入的是日期型的参数:
SQL> call test_prc(date'1980-12-17');
SMITH---17-DEC-80

Call completed.

---------------------------------------------------------------------
2.
create or replace procedure  test_prc(p_data_dt in VARCHAR) IS
e_name emp.ename%type;
v_data_dt DATE :=to_date(p_data_dt,'YYYY-MM-DD');    
begin                                               

select ename into e_name
from emp where hiredate =v_data_dt; 

DBMS_OUTPUT.PUT_LINE(e_name || '---' ||v_data_dt); 
 end;

SQL> call test_prc('1980-12-17');
SMITH---17-DEC-80

Call completed.

-------------------------------------------------------------------
3.

create or replace procedure test_prc(p_data_dt in VARCHAR) IS
e_name emp.ename%type;
begin

select ename into e_name
from emp where hiredate =to_date(P_data_dt,'yyyy-mm-dd');

DBMS_OUTPUT.PUT_LINE(e_name || '---' ||P_data_dt);
 end;

SQL> exec test_prc('1980-12-17');
SMITH---1980-12-17

PL/SQL procedure successfully completed.

 

4.

create or replace procedure test_prc(p_data_dt in VARCHAR) IS
org dwm.debit_card_org.card_open_org%type;
begin
select dwm.debit_card_org.card_open_org into org
from dwm.debit_card_org  where data_dt =to_date(P_data_dt,'yyyy-mm-dd')
and rownum<2;
DBMS_OUTPUT.PUT_LINE(org  || '---' ||P_data_dt);
end;

exec test_prc(2013-01-01);

SQL> exec test_prc(2013-01-01);
BEGIN test_prc(2013-01-01); END;

*
ERROR at line 1:
ORA-01840: input value not long enough for date format
ORA-06512: at "DWM.TEST_PRC", line 4
ORA-06512: at line 1

exec test_prc('2013-01-01');
---------------------------------------------------------------------------------------
5.
create or replace procedure test_prc(p_data_dt  VARCHAR2) IS
org dwm.debit_card_org.card_open_org%type;
begin
select dwm.debit_card_org.card_open_org into org
from dwm.debit_card_org  where data_dt =to_date(P_data_dt,'yyyy-mm-dd')
and rownum<2;
DBMS_OUTPUT.PUT_LINE(org  || '---' ||P_data_dt);
end;

 

----------------------------------------------------------------
6.
create or replace procedure test_prc(p_data_dt in NUMBER) IS
e_name emp.ename%type;
v_data_dt DATE :=to_date(p_data_dt,'YYYY-MM-DD');
begin

select ename into e_name
from emp where hiredate =v_data_dt;

DBMS_OUTPUT.PUT_LINE(e_name || '---' ||v_data_dt);
 end;

SQL> exec test_prc(19801217);

PL/SQL procedure successfully completed.

 

SQL> set serveroutput
SP2-0265: serveroutput must be set ON or OFF
SQL> set serveroutput on
SQL> exec test_prc(19801217);
SMITH---17-DEC-80

PL/SQL procedure successfully completed.

7.
SQL> create table test_2(id int,data_dt DATE);

Table created.

SQL>  insert into test_2 values(1,to_date(20130101,'yyyymmdd'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_2;

 ID DATA_DT
---------- ---------
  1 01-JAN-13

create or replace procedure test_prc(p_data_dt in VARCHAR) IS
ID test_2.ID%type;
    begin

select ID into ID
from test_2 where data_dt =to_date(p_data_dt,'yyyymmdd');

DBMS_OUTPUT.PUT_LINE(ID || '---' ||p_data_dt);
 end;

SQL> exec test_prc(20130101);
1---20130101

PL/SQL procedure successfully completed.

SQL> exec test_prc('20130101');
1---20130101

PL/SQL procedure successfully completed.

SQL> exec test_prc(2013-01-01);
BEGIN test_prc(2013-01-01); END;

*
ERROR at line 1:
ORA-01840: input value not long enough for date format
ORA-06512: at "SCOTT.TEST_PRC", line 5
ORA-06512: at line 1

SQL> exec test_prc('2013-01-01');
BEGIN test_prc('2013-01-01'); END;

*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at "SCOTT.TEST_PRC", line 5
ORA-06512: at line 1

8.
create or replace procedure test_prc(p_data_dt in VARCHAR) IS
ID test_2.ID%type;
    begin

select ID into ID
from test_2 where data_dt =to_date(p_data_dt,'yyyy-mm-dd');

DBMS_OUTPUT.PUT_LINE(ID || '---' ||p_data_dt);
 end;

SQL> exec test_prc(2013-01-01);
BEGIN test_prc(2013-01-01); END;

*
ERROR at line 1:
ORA-01840: input value not long enough for date format
ORA-06512: at "SCOTT.TEST_PRC", line 5
ORA-06512: at line 1

SQL> exec test_prc('2013-01-01');
1---2013-01-01

PL/SQL procedure successfully completed.

9.
create or replace procedure  test_prc(p_data_dt in VARCHAR) IS
ID test_2.ID%type;
    begin                                               

/*select ID into ID
from test_2 where data_dt =to_date(p_data_dt,'yyyy-mm-dd');  */

DBMS_OUTPUT.PUT_LINE(ID || '---' ||p_data_dt); 
 end;

SQL> exec test_prc('2013-01-01');
---2013-01-01

PL/SQL procedure successfully completed.

SQL> exec test_prc(2013-01-01);
---2011

PL/SQL procedure successfully completed.

但是在PL/SQL工具里可以直接选择字符窜,就不会出现这种问题,如下图: