绑定变量小例子

PROCEDURE SetAlarmInfoLog(strStcdt in char,
strStnm in varchar2,
strYMDHM in char,
V_Cause in varchar2,
AlarmLevel in char,
V_AlarmType in varchar2)
IS
str varchar2(150);
V_CURSOR_Id integer;
V_RowsUpdate integer;
v_Count int;
BEGIN

select count(0) into v_Count
from AlarmLog
where stcdt=strStcdt
and YMDHM=to_date(strYMDHM,'yyyy-mm-dd hh24:mi:ss')
and upper(trim(AlarmType))=upper(trim(V_AlarmType));

if v_Count=0 then
str:='insert into AlarmLog values (:aSTCDT,:aSTNM,:aYMDHM,:aCause,:aAlarmLevel,:aAlarmType)';
V_CURSOR_Id:=DBMS_SQL.Open_Cursor;

--分析语句
DBMS_SQL.Parse(V_CURSOR_Id,str,DBMS_SQL.NATIVE);
--联编变量
DBMS_SQL.Bind_Variable(V_CURSOR_Id,':aSTCDT',strStcdt);
DBMS_SQL.Bind_Variable(V_CURSOR_Id,':aSTNM',strStnm);
DBMS_SQL.Bind_Variable(V_CURSOR_Id,':aYMDHM',to_date(strYMDHM,'yyyy-mm-dd hh24:mi:ss'));
DBMS_SQL.Bind_Variable(V_CURSOR_Id,':aCause',V_Cause);
DBMS_SQL.Bind_Variable(V_CURSOR_Id,':aAlarmLevel',AlarmLevel);
DBMS_SQL.Bind_Variable(V_CURSOR_Id,':aAlarmType',V_AlarmType);
--执行语句
--dbms_output.put_line(V_CURSOR_Id);
V_RowsUpdate:=DBMS_SQL.Execute(V_CURSOR_Id);
commit;
end if;
END SetAlarmInfoLog;[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/79291/viewspace-915687/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/79291/viewspace-915687/