这个问题在这里已有答案:
使用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 answersI 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.)
更多推荐
发布评论