因为在SELECT语句中有几个DECODE函数,会用到参数,而用到参数的个数,根据数据的实际情况会有所不同,故我下边的EXECUTE IMMEDIATE语句中USING子句后的参数没办法写?既然参数不能确定,哪为什么不全部写全,条件成立就放变量值,条件不成立就相当于1=1,不会影响条件结果,以下修改后:CREATE OR REPLACE PROCEDURE p_RentAreaCalcu (pFloorId IN VARCHAR2, pBldgId in VARCHAR2,pRoomStyle IN VARCHAR2, pAccountId IN VARCHAR2,pDateFrom IN DATE,pDateTo IN DATE, pArea OUT NUMBER) AS v_SQL_Select VARCHAR(1000); v_SQL_From VARCHAR(1000); v_SQL_Where VARCHAR(1000); v_SQL VARCHAR(3000);
e_DateNUll EXCEPTION; --错误变量 v_Month INTEGER; --DATEFROM与DATATO间隔的月的个数 BEGIN --若DATEFROM与DATATO为NULL则引发错误 IF (pDateFrom IS NULL) OR (pDateTo IS NULL) THEN RAISE e_DateNUll; END IF; -- 取得DATEFROM与DATATO间隔的月的个数 SELECT TRUNC(MONTHS_BETWEEN(pDateTo,pDateFrom),0)-1 INTO v_Month FROM DUAL; v_SQL_Select :=' SELECT '|| ' SUM(TO_NUMBER(DECODE(SIGN(MONTHS_BETWEEN(:pDatefrom,a.begindate)),-1,'||'(TO_CHAR(LAST_DAY(a.begindate),''dd'')- TO_CHAR(a.begindate,''dd''))/TO_CHAR(LAST_DAY(a.begindate),''dd''),'||'(TO_CHAR(LAST_DAY(:pDatefrom),''dd'')- TO_CHAR(:pDatefrom,''dd''))/TO_CHAR(LAST_DAY(:pDatefrom),''dd'')))*b.leasearea +'|| 'b.leasearea*v_Month +'|| 'TO_NUMBER(DECODE(SIGN(MONTHS_BETWEEN(:pDateto,a.finishdate)),-1,'|| 'TO_CHAR(:pDateto,''dd'')/TO_CHAR(LAST_DAY(:pDateto,''dd''),'|| 'DECODE(a.finishdate,'|| 'NULL,'|| 'TO_CHAR(:pDateto,''dd'')/TO_CHAR(LAST_DAY(:pDateto,''dd''),'|| 'TO_CHAR(a.finishdate,''dd'')/TO_CHAR(LAST_DAY(a.finishdate,''dd''))))*b.leasearea)'; v_SQL_From := ' FROM Lbs_conroom a,lbs_contract b,nvl2('||pBldgId||',fm_bldginf c,lbs_contract c)'; v_SQL_Where := ' WHERE UPPER(b.constatus) = ''PUB017002'' AND b.excflag = ''1'' AND a.begindate <= '||pDateto|| ' AND (a.finishdate >= '||pDatefrom||' or a.finishdate IS NULL and nvl2('||pFloorId||',b.mainfloor,1) =nvl('||pFloorId||',1)'|| ' AND nvl2('||pBldgId||',b.mainfloor,1) =nvl2('||pBldgId||',c.floorid,1) and nvl2('||pBldgId||',c.BldgId,1) = nvl('||pBldgId||,1)'|| ' AND nvl2('||pRoomStyle||',b.roomstyle,1) =nvl('||pRoomStyle||',1)'|| ' AND nvl2('||pAccountId||',b.accountid,1) =nvl('||pAccountId||',1)';
v_SQL := v_Sql_Select||v_Sql_Select||v_Sql_Where;
EXECUTE IMMEDIATE v_SQL into pArea;
--failure EXCEPTION WHEN e_Datenull THEN ROLLBACK; pArea :=NULL; WHEN OTHERS THEN ROLLBACK; pArea :=NULL; END p_RentAreaCalcu; /
若是在from条件报错,可试试以下 v_SQL_From := ' FROM Lbs_conroom a,lbs_contract b,nvl2('||pBldgId||',fm_bldginf c,1)';请楼主以后语气客气,最近本人上网有限制,不能及时作答,请见凉!
sorry 修改如下: v_SQL_From := ' FROM Lbs_conroom a,lbs_contract b,nvl2('||pBldgId||',fm_bldginf c,dual)';
DECLARE sql_stmt VARCHAR2(200); my_empno NUMBER(4) := 7902; my_ename VARCHAR2(10); my_job VARCHAR2(9); my_sal NUMBER(7,2) := 3250.00; BEGIN sql_stmt := 'UPDATE emp SET sal = :1 WHERE empno = :2 RETURNING ename, job INTO :3, :4'; /* Bind returned values via USING clause. */ EXECUTE IMMEDIATE sql_stmt USING my_sal, my_empno, OUT my_ename, OUT my_job; /* Bind returned values via RETURNING INTO clause. */ EXECUTE IMMEDIATE sql_stmt USING my_sal, my_empno RETURNING INTO my_ename, my_job; ... END;
ORACLE数据库开发坛里真是没高人!!以后不来了。
execute immediate sql 语法 后面不用参数 把所有的参数 直接作为变量 连接到sql 语法里面就可以了
例如sql:='select '|| a || ' from dual "
你把传进去的变量直接拼成sql语句怎么样? 那样记录v_Condition也就没有必要了,v_Con_Arr也应该用不到了。
2.没看出你的select子句里面的内容是随不同情况变化的,我看从select到where的加楼盘ID条件之前的一段都是固定的。只是using后面的参数严重不足,datefrom和dateto有10来个,但你只各给了一个,这是不是个问题?
仅供参考.
(pFloorId IN VARCHAR2, pBldgId in VARCHAR2,pRoomStyle IN VARCHAR2,
pAccountId IN VARCHAR2,pDateFrom IN DATE,pDateTo IN DATE,
pArea OUT NUMBER)
AS v_SQL_Select VARCHAR(1000);
v_SQL_From VARCHAR(1000);
v_SQL_Where VARCHAR(1000);
v_SQL VARCHAR(3000);
e_DateNUll EXCEPTION; --错误变量
v_Month INTEGER; --DATEFROM与DATATO间隔的月的个数
BEGIN
--若DATEFROM与DATATO为NULL则引发错误
IF (pDateFrom IS NULL) OR (pDateTo IS NULL) THEN
RAISE e_DateNUll;
END IF;
-- 取得DATEFROM与DATATO间隔的月的个数
SELECT TRUNC(MONTHS_BETWEEN(pDateTo,pDateFrom),0)-1
INTO v_Month
FROM DUAL;
v_SQL_Select :=' SELECT '||
' SUM(TO_NUMBER(DECODE(SIGN(MONTHS_BETWEEN(:pDatefrom,a.begindate)),-1,'||'(TO_CHAR(LAST_DAY(a.begindate),''dd'')- TO_CHAR(a.begindate,''dd''))/TO_CHAR(LAST_DAY(a.begindate),''dd''),'||'(TO_CHAR(LAST_DAY(:pDatefrom),''dd'')- TO_CHAR(:pDatefrom,''dd''))/TO_CHAR(LAST_DAY(:pDatefrom),''dd'')))*b.leasearea +'|| 'b.leasearea*v_Month +'||
'TO_NUMBER(DECODE(SIGN(MONTHS_BETWEEN(:pDateto,a.finishdate)),-1,'||
'TO_CHAR(:pDateto,''dd'')/TO_CHAR(LAST_DAY(:pDateto,''dd''),'||
'DECODE(a.finishdate,'||
'NULL,'||
'TO_CHAR(:pDateto,''dd'')/TO_CHAR(LAST_DAY(:pDateto,''dd''),'||
'TO_CHAR(a.finishdate,''dd'')/TO_CHAR(LAST_DAY(a.finishdate,''dd''))))*b.leasearea)';
v_SQL_From := ' FROM Lbs_conroom a,lbs_contract b,nvl2('||pBldgId||',fm_bldginf c,lbs_contract c)';
v_SQL_Where := ' WHERE UPPER(b.constatus) = ''PUB017002'' AND b.excflag = ''1'' AND a.begindate <= '||pDateto||
' AND (a.finishdate >= '||pDatefrom||' or a.finishdate IS NULL and nvl2('||pFloorId||',b.mainfloor,1) =nvl('||pFloorId||',1)'||
' AND nvl2('||pBldgId||',b.mainfloor,1) =nvl2('||pBldgId||',c.floorid,1) and nvl2('||pBldgId||',c.BldgId,1) = nvl('||pBldgId||,1)'||
' AND nvl2('||pRoomStyle||',b.roomstyle,1) =nvl('||pRoomStyle||',1)'||
' AND nvl2('||pAccountId||',b.accountid,1) =nvl('||pAccountId||',1)';
v_SQL := v_Sql_Select||v_Sql_Select||v_Sql_Where;
EXECUTE IMMEDIATE v_SQL into pArea;
--failure
EXCEPTION
WHEN e_Datenull THEN
ROLLBACK;
pArea :=NULL;
WHEN OTHERS THEN
ROLLBACK;
pArea :=NULL;
END p_RentAreaCalcu;
/
v_SQL_From := ' FROM Lbs_conroom a,lbs_contract b,nvl2('||pBldgId||',fm_bldginf c,1)';请楼主以后语气客气,最近本人上网有限制,不能及时作答,请见凉!
v_SQL_From := ' FROM Lbs_conroom a,lbs_contract b,nvl2('||pBldgId||',fm_bldginf c,dual)';
sql_stmt VARCHAR2(200);
my_empno NUMBER(4) := 7902;
my_ename VARCHAR2(10);
my_job VARCHAR2(9);
my_sal NUMBER(7,2) := 3250.00;
BEGIN
sql_stmt := 'UPDATE emp SET sal = :1 WHERE empno = :2
RETURNING ename, job INTO :3, :4'; /* Bind returned values via USING clause. */
EXECUTE IMMEDIATE sql_stmt
USING my_sal, my_empno, OUT my_ename, OUT my_job; /* Bind returned values via RETURNING INTO clause. */
EXECUTE IMMEDIATE sql_stmt
USING my_sal, my_empno RETURNING INTO my_ename, my_job;
...
END;
首先向大家道个歉,发了问题那几天,天天上来看,总是没人答,所以也就失望了,故接下为就没有上了。今天不忙上来一看,有几个回答了,不错。不管对不对。下边一一作答。TO:wwl007(疑难杂症)
你说直接拼SQL。当有20个参数,每个参数又来自于不同表的一个字段。而我又不能确定这20个参数是否都不为空。故每个参数有两个状态:NULL,NOT NULL。如果要是拼全了,20的排列,会有多少种情况,要写多久?所以你的方法行不通。WHERE子句好象好写,但是把FROM子句和WHERE子句都正确的拼出来,就非常的庞大。
TO:mosaic(影子)
你好象是也在问能否使动态语句中的参数动态变化?TO:beckhambobo(beckham)
非常感谢,你的写法一个很好的思路,当关联条件的表比较少的时候,可以这样写,但是当参数多,关联表比较多,参数排列情况多时,你这种写法,可能还不如我的写法简单明了。
所在根本上还是一个一个拼,“动态”性不够。不过你已经做的非常好了。向你学习。
TO:其他人:
谢谢关注。最后说明:为什么要用这样的口气,只是为了激激大家,早日帮我解决问题,要不然高人也懒的出手,别无他意。呵呵。