在PL / SQL中使用日期[复制](Using Dates in PL/SQL [duplicate])

在PL / SQL中使用日期[复制](Using Dates in PL/SQL [duplicate])

这个问题在这里已有答案：

使用Oracle SQL 3中的 日期 答案

我是使用PL / SQL的新手，我已经创建了一个程序，但我无法弄清楚正确的语法，以获取当前月份和年份的数据，以及另一个光标来获取前一年的数据：

create or replace procedure data(acc integer, month integer, year integer) as Cursor c1 is select usage from bill where account =acc_num and to_char(BILL_DATE, 'MM-YYYY') = 'month-year'; Cursor c3 is select usage from bill where account =acc_num and to_char(BILL_DATE, 'MM-YYYY') = 'month-year' - 1;

**我明白这只是代码的一部分，但我相信我的逻辑几乎完全可以找到我想要的数据。 使用PLSQL

This question already has an answer here:

Working with dates in Oracle SQL 3 answers

I am very new to using PL/SQL and I have created a procedure but I cannot figure out the correct syntax in order to get the current month and year's data and another cursor to get the data from exactly 1 year prior:

create or replace procedure data(acc integer, month integer, year integer) as Cursor c1 is select usage from bill where account =acc_num and to_char(BILL_DATE, 'MM-YYYY') = 'month-year'; Cursor c3 is select usage from bill where account =acc_num and to_char(BILL_DATE, 'MM-YYYY') = 'month-year' - 1;

** I do understand this is only part of the code, but I believe my logic is almost complete for finding the data I want. Using PLSQL

最满意答案

我想你正在寻找这样的东西：

select usage from bill where account = in_account and extract(year from bill_date) = in_year and extract(month from bill_date) = in_month;

如果要比较年份和月份（以整数形式传递），只需从日期中提取这些属性即可。

如果您正在学习PL / SQL，请学习命名参数和参数，以便将它们与列区分开来：

create or replace procedure data ( in_account integer, in_month integer, in_year integer ) as begin . . .

（而“data”对于存储过程来说是一个非常奇怪的名称。我希望名字中有一个动词。）

I think you are looking for something like this:

select usage from bill where account = in_account and extract(year from bill_date) = in_year and extract(month from bill_date) = in_month;

If you want to compare the year and month (which are passed in as integers), just extract those attributes from the date.

If you are learning PL/SQL, learn to name your parameters and arguments so you can distinguish them from columns:

create or replace procedure data ( in_account integer, in_month integer, in_year integer ) as begin . . .

(And "data" is a very curious name for a stored procedure. I would expect a verb in the name.)