How to pass date parameter inside oracle procedure?

Question

The following is an example procedure code,

create or replace procedure pro_test(start_date date, end_date date)
is 
begin

insert into test1 select col1, col2, col3 from main where range_date between start_date and end_date;

exception

< what are the exception I need to capture >

end;
/

Q1: Is the correct way to pass the date directly inside the procedure? Q2: If not, can I pass varchar inside the procedure to convert the date to part of the declaration? Q3: At the beginning of work, am is used between the operator, can I pass the procedure parameter directly?

When performing this procedure exec pro_test('10102015','30102015');What do I need to specify between the sentences? is it enough between start_date and end_date or do i need to mask the date format?

can someone help me clarify this?

Answer 1

Q1: Is the correct way to pass the date directly inside the procedure?

Yes.

Q3: , ?

, , insert . DATE .

, INSERT..SELECT SQL.

insert into test1 
select col1, col2, col3 
from main 
where range_date 
between TO_DATE(<date_literal>,<format mask>)
and TO_DATE(<date_literal>,<format mask>);

---

OP:

exec pro_test ('10102015', '30102015'); ? start_date end_date ?

'10102015' DATE, . DATE, TO_DATE , ANSI Date literal, - . ANSI Date literal 'YYYY-MM-DD'.

,

TO_DATE:

EXEC pro_test(TO_DATE('10102015','DDMMYYYY'),TO_DATE('30102015','DDMMYYYY'));

ANSI Date literal:

EXEC pro_test(DATE '2015-10-10', DATE '2015-10-30');

Answer 2

Q1. , ( out), :

create or replace procedure pro_test(start_date in date, end_date in date)

.

Answer 3

, pl/sql, SQL:

create or replace procedure pro_test(start_date date, end_date date)
is 
begin

  insert into test1
  select col1, col2, col3
  from main where range_date
  between pro_test.start_date and pro_test.end_date;
...

This prevents the SQL engine from capturing variables if their name matches the column in the referenced table.

Usually you try to avoid this:

create procedure my_procedure (p_customer_id number)
...
where customer_id = p_customer_id

Putting variables allows you to maintain a cleaner interface without a prefix.