1、数据库版本：oracle11.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; BEGINSELECT 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;
