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工具里可以直接选择字符窜,就不会出现这种问题,如下图:
