现在需要些一个存储过程,描述如下:
有a, b ,c 三个参数,如果a有值,则在sql语句的where部分加上条件a,如果a没有值,则在sql语句的where部分不加在
where部分。b, c也是同样的处理。 我写了一个存储过程如下:
create or replace package body INVOICE_PACKAGE is
function FINDSUBINVOICE(a varchar2,
b date,
c date,
) return SYS_REFCURSOR is
outCursor SYS_REFCURSOR;
str varchar2(256);
begin
open outCursor for
select
INVOICE_ID,
ACCOUNT_ID,
STATUS,
INVOICE_NUMBER,
INVOICE_DT,
ENTERED_DT,
DUE_DT,
INVOICED_AMOUNT,
CURRENCY_BASE,
AGREED_AMOUNT,
DISPUTE_AMOUNT,
DISPUTE_ID,
TRAFFIC_START_DT,
TRAFFIC_END_DT,
TOTAL_CALLS,
TOTAL_MINS,
UPDATE_USER,
UPDATE_DT,
GLOBAL_SITE_NAME
from invoice_header
where
IF a IS not NULL
then
str := 'ACCOUNT_ID = a';
end IF;
IF b IS NOT NULL
then
str := 'and TRAFFIC_START_DT >= b and TRAFFIC_END_DT <= c';
end IF;
0=0 AND := str;
return outCursor;
end FINDSUBINVOICE;
end INVOICE_PACKAGE;上面的写法有错,不知道该怎样改,请帮忙。
有a, b ,c 三个参数,如果a有值,则在sql语句的where部分加上条件a,如果a没有值,则在sql语句的where部分不加在
where部分。b, c也是同样的处理。 我写了一个存储过程如下:
create or replace package body INVOICE_PACKAGE is
function FINDSUBINVOICE(a varchar2,
b date,
c date,
) return SYS_REFCURSOR is
outCursor SYS_REFCURSOR;
str varchar2(256);
begin
open outCursor for
select
INVOICE_ID,
ACCOUNT_ID,
STATUS,
INVOICE_NUMBER,
INVOICE_DT,
ENTERED_DT,
DUE_DT,
INVOICED_AMOUNT,
CURRENCY_BASE,
AGREED_AMOUNT,
DISPUTE_AMOUNT,
DISPUTE_ID,
TRAFFIC_START_DT,
TRAFFIC_END_DT,
TOTAL_CALLS,
TOTAL_MINS,
UPDATE_USER,
UPDATE_DT,
GLOBAL_SITE_NAME
from invoice_header
where
IF a IS not NULL
then
str := 'ACCOUNT_ID = a';
end IF;
IF b IS NOT NULL
then
str := 'and TRAFFIC_START_DT >= b and TRAFFIC_END_DT <= c';
end IF;
0=0 AND := str;
return outCursor;
end FINDSUBINVOICE;
end INVOICE_PACKAGE;上面的写法有错,不知道该怎样改,请帮忙。
解决方案 »
- ORA-12154: TNS:无法解析指定的连接标识符
- SPOOL 最大文件限制
- 请问,在Oracle10g里,有不支持的SQL文吗?急!!
- 请问在oracle中如何批量更新一列记录?在线急等,各位大虾帮帮忙啊~~~~
- 关于多表联查
- 关于Oracle中使用的SQL语句,求救!!在线等待
- 紧急—通过ODBC方式在IIS记录日志,用SQL SERVER可以。但是用Oracle却不能导入?
- 菜鸟求救(一) Help!Help!Help!Help!
- 请教ORACLE和SQL SEVER之间数据转换的问题
- 哪两个函数可以查询第一条记录和最后一条记录,举例
- Oracle优化问题
- 微软 SQL Server北京研发中心招聘一名 SQL IT System Engineer
Compilation errors for PACKAGE BODY TEST01.INVOICE_PACKAGEError: PL/SQL: ORA-00920: 无效的关系运算符
Line: 121
Text: IF accountId_ is not nullError: PL/SQL: SQL Statement ignored
Line: 98
Text: selectError: PLS-00103: 出现符号 "IF"在需要下列之一时:
; <an identifier>
<a double-quoted delimited-identifier> delete exists prior
<a single-quoted SQL string>
Line: 124
Text: end IF;
create or replace package body INVOICE_PACKAGE is
function FINDSUBINVOICE(a varchar2,
b date,
c date,
) return SYS_REFCURSOR is
outCursor SYS_REFCURSOR;
str varchar2(256);
begin
if a <> null && b <> null && c <> null then
open outCursor for
select
INVOICE_ID,
ACCOUNT_ID,
STATUS,
INVOICE_NUMBER,
INVOICE_DT,
ENTERED_DT,
DUE_DT,
INVOICED_AMOUNT,
CURRENCY_BASE,
AGREED_AMOUNT,
DISPUTE_AMOUNT,
DISPUTE_ID,
TRAFFIC_START_DT,
TRAFFIC_END_DT,
TOTAL_CALLS,
TOTAL_MINS,
UPDATE_USER,
UPDATE_DT,
GLOBAL_SITE_NAME
from invoice_header
where
ACCOUNT_ID = a and
TRAFFIC_START_DT >= b and TRAFFIC_END_DT <= c;
end;
if a <> null && b <> null && c = null then
open outCursor for
select
INVOICE_ID,
ACCOUNT_ID,
STATUS,
INVOICE_NUMBER,
INVOICE_DT,
ENTERED_DT,
DUE_DT,
INVOICED_AMOUNT,
CURRENCY_BASE,
AGREED_AMOUNT,
DISPUTE_AMOUNT,
DISPUTE_ID,
TRAFFIC_START_DT,
TRAFFIC_END_DT,
TOTAL_CALLS,
TOTAL_MINS,
UPDATE_USER,
UPDATE_DT,
GLOBAL_SITE_NAME
from invoice_header
where
ACCOUNT_ID = a and
TRAFFIC_START_DT >= b;
end;
if a <> null && b = null && c = null then
open outCursor for
select
INVOICE_ID,
ACCOUNT_ID,
STATUS,
INVOICE_NUMBER,
INVOICE_DT,
ENTERED_DT,
DUE_DT,
INVOICED_AMOUNT,
CURRENCY_BASE,
AGREED_AMOUNT,
DISPUTE_AMOUNT,
DISPUTE_ID,
TRAFFIC_START_DT,
TRAFFIC_END_DT,
TOTAL_CALLS,
TOTAL_MINS,
UPDATE_USER,
UPDATE_DT,
GLOBAL_SITE_NAME
from invoice_header
where
ACCOUNT_ID = a ;
end;
if a = null && b <> null && c <> null then
open outCursor for
select
INVOICE_ID,
ACCOUNT_ID,
STATUS,
INVOICE_NUMBER,
INVOICE_DT,
ENTERED_DT,
DUE_DT,
INVOICED_AMOUNT,
CURRENCY_BASE,
AGREED_AMOUNT,
DISPUTE_AMOUNT,
DISPUTE_ID,
TRAFFIC_START_DT,
TRAFFIC_END_DT,
TOTAL_CALLS,
TOTAL_MINS,
UPDATE_USER,
UPDATE_DT,
GLOBAL_SITE_NAME
from invoice_header
where
TRAFFIC_START_DT >= b and TRAFFIC_END_DT <= c;
end;
if a = null && b = null && c <> null then
open outCursor for
select
INVOICE_ID,
ACCOUNT_ID,
STATUS,
INVOICE_NUMBER,
INVOICE_DT,
ENTERED_DT,
DUE_DT,
INVOICED_AMOUNT,
CURRENCY_BASE,
AGREED_AMOUNT,
DISPUTE_AMOUNT,
DISPUTE_ID,
TRAFFIC_START_DT,
TRAFFIC_END_DT,
TOTAL_CALLS,
TOTAL_MINS,
UPDATE_USER,
UPDATE_DT,
GLOBAL_SITE_NAME
from invoice_header
where
ACCOUNT_ID = a
end;
if a = null && b = null && c = null then
open outCursor for
select
INVOICE_ID,
ACCOUNT_ID,
STATUS,
INVOICE_NUMBER,
INVOICE_DT,
ENTERED_DT,
DUE_DT,
INVOICED_AMOUNT,
CURRENCY_BASE,
AGREED_AMOUNT,
DISPUTE_AMOUNT,
DISPUTE_ID,
TRAFFIC_START_DT,
TRAFFIC_END_DT,
TOTAL_CALLS,
TOTAL_MINS,
UPDATE_USER,
UPDATE_DT,
GLOBAL_SITE_NAME
from invoice_header;
end;
return outCursor;
end FINDSUBINVOICE;
end INVOICE_PACKAGE;
最简单的解决方法:select
INVOICE_ID,
ACCOUNT_ID,
STATUS,
INVOICE_NUMBER,
INVOICE_DT,
ENTERED_DT,
DUE_DT,
INVOICED_AMOUNT,
CURRENCY_BASE,
AGREED_AMOUNT,
DISPUTE_AMOUNT,
DISPUTE_ID,
TRAFFIC_START_DT,
TRAFFIC_END_DT,
TOTAL_CALLS,
TOTAL_MINS,
UPDATE_USER,
UPDATE_DT,
GLOBAL_SITE_NAME
from invoice_header
where (ACCOUNT_ID = a or a is null)
and (TRAFFIC_START_DT >= b or b is null)
and (TRAFFIC_END_DT >= c or c is null)
最佳解决方案是使用动态游标,这里就不给具体的脚本了。
还有好的写法吗?
Strsql Varchar2(4000);
Type Tcur Is Ref Cursor;
Cur_Queue Tcur;
begin
--设置游标SQL
Strsql := 'SELECT * FROM …………'; --这里的SQL可以根据需要生成 --循环执行
Open Cur_Queue For Strsql;
Loop
Fetch Cur_Queue Into ……;
Exit When Cur_Queue%Notfound;
Begin
……
……
END;
END LOOP;
Close Cur_Queue;
end;
function FINDSUBINVOICE(accountId_ varchar2,
trafficEndDate_ date,
trafficStartDate_ date,
status_ varchar2,
invoiceDate_ date,
invoiceNumber_ varchar2
) return SYS_REFCURSOR is
outCursor SYS_REFCURSOR;
strsql varchar2(1000);
begin
strsql := 'select '
||'INVOICE_ID, '
||'ACCOUNT_ID, '
||'STATUS, '
||'INVOICE_NUMBER, '
||'INVOICE_DT, '
||'ENTERED_DT, '
||'DUE_DT, '
||'INVOICED_AMOUNT, '
||'CURRENCY_BASE, '
||'AGREED_AMOUNT, '
||'DISPUTE_AMOUNT, '
||'DISPUTE_ID, '
||'TRAFFIC_START_DT, '
||'TRAFFIC_END_DT, '
||'TOTAL_CALLS, '
||'TOTAL_MINS, '
||'UPDATE_USER, '
||'UPDATE_DT, '
||'GLOBAL_SITE_NAME '
||'from invoice_header '
||'where 0=0 ';
if accountId_ is NULL then
strsql := strsql ||'';
else
strsql := strsql || 'and ACCOUNT_ID = accountId_ ';
end if;
if status_ is NULL then
strsql := strsql ||'';
else
strsql := strsql || 'and STATUS = status_ ';
end if;
if trafficStartDate_ is null then
strsql := strsql || '';
else
strsql := strsql || 'and TRAFFIC_START_DT >= trafficStartDate_ ';
end if;
if trafficEndDate_ is null then
strsql := strsql || '';
else
strsql := strsql || 'and TRAFFIC_END_DT <= trafficEndDate_ ';
end if;
if invoiceDate_ is null then
strsql := strsql || '';
else
strsql := strsql || 'and INVOICE_DT = invoiceDate_ ';
end if;
if invoiceNumber_ is null then
strsql := strsql || ';';
else
strsql := strsql || 'and INVOICE_NUMBER = invoiceNumber_; ';
end if;
open outCursor for strsql;
return outCursor;
end FINDSUBINVOICE;在执行时,显示 open outCursor for strsql; 行有错 ORA-00911 字符无效 错误, 怎么解决?
“strsql ¦¦ ';'”问题2:SQL字符串拼接有问题(好几个地方)strsql := strsql ¦¦ 'and STATUS = status_ '; 改成:strsql := strsql ¦¦ 'and STATUS = ''' || status_ || '''';
trafficEndDate_ date,
trafficStartDate_ date,
status_ varchar2,
invoiceDate_ date,
invoiceNumber_ varchar2)
return SYS_REFCURSOR is
outCursor SYS_REFCURSOR;
strsql varchar2(1000);
begin
strsql := 'select '
||chr(10)||' INVOICE_ID, '
||chr(10)||' ACCOUNT_ID, '
||chr(10)||' STATUS, '
||chr(10)||' INVOICE_NUMBER, '
||chr(10)||' INVOICE_DT, '
||chr(10)||' ENTERED_DT, '
||chr(10)||' DUE_DT, '
||chr(10)||' INVOICED_AMOUNT, '
||chr(10)||' CURRENCY_BASE, '
||chr(10)||' AGREED_AMOUNT, '
||chr(10)||' DISPUTE_AMOUNT, '
||chr(10)||' DISPUTE_ID, '
||chr(10)||' TRAFFIC_START_DT, '
||chr(10)||' TRAFFIC_END_DT, '
||chr(10)||' TOTAL_CALLS, '
||chr(10)||' TOTAL_MINS, '
||chr(10)||' UPDATE_USER, '
||chr(10)||' UPDATE_DT, '
||chr(10)||' GLOBAL_SITE_NAME '
||chr(10)||'from invoice_header'
||chr(10)||'where 0=0'; if accountId_ is not null then
strsql := strsql ||chr(10)|| ' and ACCOUNT_ID = ''' || accountId_ || '''';
end if;
if status_ is not null then
strsql := strsql ||chr(10)|| ' and STATUS = ''' || status_ || '''';
end if;
if trafficStartDate_ is not null then
strsql := strsql ||chr(10)|| ' and TRAFFIC_START_DT >= to_date(''' || to_char(trafficStartDate_, 'yyyy-mm-dd hh24:mi:ss') || ''' ,''yyyy-mm-dd hh24:mi:ss'')';
end if;
if trafficEndDate_ is not null then
strsql := strsql ||chr(10)|| ' and TRAFFIC_END_DT <= to_date(''' || to_char(trafficEndDate_, 'yyyy-mm-dd hh24:mi:ss') || ''' ,''yyyy-mm-dd hh24:mi:ss'')';
end if;
if invoiceDate_ is not null then
strsql := strsql ||chr(10)|| ' and INVOICE_DT = to_date(''' || to_char(invoiceDate_, 'yyyy-mm-dd hh24:mi:ss') || ''' ,''yyyy-mm-dd hh24:mi:ss'')';
end if;
if invoiceNumber_ is not null then
strsql := strsql ||chr(10)|| ' and INVOICE_NUMBER = ''' || invoiceNumber_ ||'''';
end if;
open outCursor for strsql;
return outCursor;
end FINDSUBINVOICE;
Date trafficStartDate;
因为没有给这两个参数初始化,所以在java中值是null,如果将参数传给上面的存储过程,那么oracle中得到的将是
什么值,还是不是null,或者会出现什么错误?