湖南网站推广哪家专业,决定网站打开的速度,浙江网站建设而,太原网站优化步骤当用EXECUTE IMMEDIATE执行SQL语句中的参数个数也是动态的#xff1f;用什么方法实现#xff1f;描述详细一点就是#xff1a;在要执行的SQL语句中所用到(: parameter)这种参数的个数#xff0c;因具体条件不同#xff0c;而不同#xff0c;而在执行语句EXECUTE IMMEDIAT…当用EXECUTE IMMEDIATE执行SQL语句中的参数个数也是动态的用什么方法实现描述详细一点就是在要执行的SQL语句中所用到(: parameter)这种参数的个数因具体条件不同而不同而在执行语句EXECUTE IMMEDIATE中USING后边如何动态与前边的个数相匹配例子如下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/*************************************************************功能: 出租面积计算组件 **参数楼盘ID,楼宇ID,物业类型,客户,日期从,日期到*返回值: 出租面积 **创建日期: 2002-11-19*修改日期: 2002-11-19*作者: xy **************************************************************/v_SQL_Select VARCHAR(1000);v_SQL_From VARCHAR(1000);v_SQL_Where VARCHAR(1000);v_SQL VARCHAR(3000);v_Condition INTEGER; --跟踪SQL语句e_DateNUll EXCEPTION; --错误变量TYPE t_arr IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; --定义数组v_Con_Arr t_arr; --保存查询参数v_Month INTEGER; --DATEFROM与DATATO间隔的月的个数BEGIN--若DATEFROM与DATATO为NULL则引发错误IF (pDateFrom IS NULL) OR (pDateTo IS NULL) THENRAISE e_DateNUll;END IF;-- 取得DATEFROM与DATATO间隔的月的个数SELECT TRUNC(MONTHS_BETWEEN(pDateTo,pDateFrom),0)-1INTO v_MonthFROM DUAL;--加日期条件v_Condition : 0;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;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;--加楼盘ID条件IF pFloorId IS NOT NULL THENv_Condition : v_Condition 1;v_Sql_Where : v_Sql_Where || AND b.mainfloor : pFloorId;v_Con_Arr(v_Condition) : pFloorId;END IF;--加楼宇ID条件IF pBldgId IS NOT NULL THENv_Condition : v_Condition 1;v_Sql_From : v_Sql_From ||,fm_bldginf c;v_Sql_Where : v_Sql_Where || AND b.mainfloor c.floorid AND c.BldgId : pBldgId;v_Con_Arr(v_Condition) : pBldgId;END IF;--加物业类型条件IF pRoomStyle IS NOT NULL THENv_Condition : v_Condition 1;v_Sql_Where : v_Sql_Where|| AND b.roomstyle : pRoomStyle;v_Con_Arr(v_Condition) : pRoomStyle;END IF;--加客户ID条件IF pAccountId IS NOT NULL THENv_Condition : v_Condition 1;v_Sql_Where : v_Sql_Where || AND b.accountid : pAccountId;v_Con_Arr(v_Condition) : pAccountId;END IF;--全部语句v_SQL : v_Sql_Select||v_Sql_Select||v_Sql_Where;--得到出租面积IF v_Condition 0 THENEXECUTE IMMEDIATE v_SQL INTO pArea USING pDateFrom,pDateTo;ELSIF v_Condition 1 THENEXECUTE IMMEDIATE v_SQL INTO pArea USING pDateFrom,pDateTo,v_Con_Arr(1);ELSIF v_Condition 2 THENEXECUTE IMMEDIATE v_SQL INTO pArea USING pDateFrom,pDateTo,v_Con_Arr(1),v_Con_Arr(2);ELSIF v_Condition 3 THENEXECUTE IMMEDIATE v_SQL INTO pArea USING pDateFrom,pDateTo,v_Con_Arr(1),v_Con_Arr(2),v_Con_Arr(3);ELSIF v_Condition 4 THENEXECUTE IMMEDIATE v_SQL INTO pArea USING pDateFrom,pDateTo,v_Con_Arr(1),v_Con_Arr(2),v_Con_Arr(3),v_Con_Arr(4);END IF;--failureEXCEPTIONWHEN e_Datenull THENROLLBACK;pArea : NULL;WHEN OTHERS THENROLLBACK;pArea : NULL;END p_RentAreaCalcu;****************特别说明原来我是照上边那样做的最后发现不行因为在SELECT语句中有几个DECODE函数会用到参数而用到参数的个数根据数据的实际情况会有所不同故我下边的EXECUTE IMMEDIATE语句中USING子句后的参数没办法写怎样才能根据实际的条件自动匹配SELECT语句中用到的参数个数并且参数名称也匹配
