oracle DML操作 绑定变量
1、数据库版本：oracle 11.2.0.4.0
2、INSERT插入语句
PROCEDURE INSERT_TEST_BDING(I_PARA1 IN NUMBER,
I_PARA2 IN VARCHAR2,
I_PARA3 IN VARCHAR2,
I_PARA4 IN VARCHAR2,
R_CODE OUT VARCHAR2, --返回结果代码
R_MSG OUT VARCHAR2 --返回结果信息
) AS
V_PARA1 NUMBER;
V_PARA2 DATE;
V_PARA3 TEST.PARA3%TYPE;
V_PARA4 TEST.PARA4%TYPE;;
V_ID NUMBER;
V_SQL VARCHAR2(32767); --存放SQL字符串
/**********************************************************************************
*[ 功能描述 ] INSERT操作多个入参绑定变量语法使用
**********************************************************************************/
BEGIN
SAVEPOINT INSERT_TEST_BDING;
BEGIN
V_PARA1 := I_PARA1;
V_PARA2 := TO_DATE(I_PARA2,'YYYY-MM-DD HH24:MI:SS');
V_PARA3 := I_PARA3;
V_PARA4 := I_PARA4;
EXCEPTION WHEN OTHERS THEN
R_CODE := 'ERR1';
R_MSG := '参数转换错误'||SQLERRM;
END;
BEGIN
SELECT NVL(MAX(ID),0)+1 INTO V_ID FROM TEST;
--插入数据
V_SQL := 'INSERT INTO TEST(ID,CREATETIME,PARA1,PARA2,PARA3,PARA4)
VALUES (:1, :1, :1, :1, :1, :1)';
EXECUTE IMMEDIATE V_SQL USING V_ID,SYSDATE,V_PARA1,V_PARA2,V_PARA3,V_PARA4;
R_CODE := 'S00';
R_MSG := '跟进内容添加成功';
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK TO INSERT_TEST_BDING;
R_CODE := 'ERR3';
R_MSG := '订单催收跟进详情，跟进内容与计划内容添加失败'||SQLERRM;
END;
END INSERT_TEST_BDING;
3、UPDATE 更新语句
V_SQL := 'UPDATE TEST
SET PARA2 = :1,
PARA3 = :2
WHERE PARA1 = V_PARA1';
EXECUTE IMMEDIATE V_SQL USING SYSDATE,V_PARA3;
3.DELETE 删除语句
V_SQL := 'DELETE FROM TEST
WHERE PARA1 = :1
AND PARA3 = :2';
EXECUTE IMMEDIATE V_SQL USING V_PARA1,V_PARA3;