SQL 如何在oracle程序中传递日期参数？

How to pass date parameter inside the oracle procedure?

提问by Bala S

Below is the sample 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 this right way to pass date directly inside the procedure? Q2 : If not, can I pass varchar inside the procedure , to convert the date in declaration part? Q3 : In begin part am using between operator, can i pass procedure parameter directly ?

Q1：这是在程序中直接传递日期的正确方法吗？Q2：如果没有，我可以在程序中通过varchar来转换声明部分的日期吗？Q3 : 在操作符之间使用的开始部分，我可以直接传递过程参数吗？

While executing this procedure, exec pro_test('10102015','30102015');What i need to mention in between sentence? between start_date and end_date is this enough or i need to mask date format?

在执行此程序时，exec pro_test('10102015','30102015');我需要在句子之间提及什么？在 start_date 和 end_date 之间就够了还是我需要屏蔽日期格式？

can someone help me to clear on this?

有人可以帮我澄清一下吗？

回答by Lalit Kumar B

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

Q1：这是在程序中直接传递日期的正确方法吗？

Yes.

是的。

Q3 : In begin part am using between operator, can i pass procedure parameter directly ?

Q3 : 在操作符之间使用的开始部分，我可以直接传递过程参数吗？

Not sure what you mean by this, but your insert statement is fine. You are passing a DATEas parameter and inserting into the table.

不确定你的意思，但你的插入语句很好。您将DATE作为参数传递并插入到表中。

In my opinion, all this could be done in a single INSERT..SELECTstatement in pure SQL.

在我看来，所有这些都可以在纯SQL中的单个INSERT..SELECT语句中完成。

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>);

---

---

UPDATEPer OP's comment:

更新根据 OP 的评论：

While executing this procedure, exec pro_test('10102015','30102015'); What i need to mention in between sentence? between start_date and end_date is this enough or i need to mask date format?

在执行此过程时， exec pro_test('10102015','30102015'); 我需要在句子之间提到什么？在 start_date 和 end_date 之间就够了还是我需要屏蔽日期格式？

'10102015'is not a DATE, it is a string literal. You must pass it as DATE, so you must either use TO_DATEwith proper format mask or ANSI Date literalas you do not have any time portion. ANSI Date literal uses a fixed format 'YYYY-MM-DD'.

'10102015'不是日期，它是字符串文字。您必须将其作为 DATE 传递，因此您必须使用具有正确格式掩码的TO_DATE或ANSI 日期文字，因为您没有任何时间部分。ANSI 日期文字使用固定格式'YYYY-MM-DD'。

For example,

例如，

Using TO_DATE:

使用TO_DATE：

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

Using ANSI Date literal:

使用ANSI 日期文字：

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

回答by HAYMbl4

Q1.You need say procedure what type of parameters inputor output(adding inor out), for your procedure it is input:

一季度。您需要说程序输入或输出什么类型的参数（添加in或out），对于您的程序，它是输入：

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

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

Everything else in your procedure fine.

您程序中的其他一切都很好。

回答by David Aldridge

If you want to be extra cautious, you should namespace your pl/sql variables when they are used in a SQL statement:

如果你想格外小心，你应该在 SQL 语句中使用 pl/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 "capturing" the variables if their name is the same as a column in a referenced table.

如果变量的名称与引用表中的列相同，这可以防止 SQL 引擎“捕获”变量。

Commonly you see people try to avoid this with:

通常你会看到人们试图通过以下方式避免这种情况：

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

Namespacing the variables allows you to keep a cleaner interface without the prefixing.

为变量命名空间可以让你保持一个更干净的界面，而无需前缀。