举例一个简单的增删改查存储过程demo
--如果A表中不存该号 在检查B表 CREATE OR REPLACE PROCEDURE SP_NMS_DELETE-- 删除MMS_NM_NETMESSAGE功能,所有参数均为NULL时,直接返回删除失败标识.-- 杨化龙-- 返回值R_DELETE_SECCUESS( P_ID IN MMS_NM_NETMESSAGE.ID%TYPE, R_DELETE_SECCUESS OUT NUMBER)AUTHID CURRENT_USERAS -- 声明删除SQL语句 V_DELETE_SQL VARCHAR(400); V_ID number; -- 声明条件变量 V_CONDITION VARCHAR(200) := '';BEGIN -- 声明成功标识 R_DELETE_SECCUESS := 0; SAVEPOINT DO_DELETE; select id into V_ID from (select 1 id from MMS_NM_NETMESSAGE where id=P_ID union all select 2 id from Mms_Nm_Netmessage_His where id=P_ID); dbms_output.put_line(V_ID); IF V_ID =1 THEN--SQL拼装当前表 V_DELETE_SQL := 'DELETE MMS_NM_NETMESSAGE WHERE '; END IF; IF V_ID =2 THEN--SQL拼装历史表 V_DELETE_SQL := 'DELETE MMS_NM_NETMESSAGE_HIS WHERE '; END IF; dbms_output.put_line(V_DELETE_SQL); -- 封装条件语句 IF P_ID IS NOT NULL THEN V_CONDITION := V_CONDITION || ' ID=' || P_ID; END IF; SAVEPOINT DO_DELETE; V_DELETE_SQL:= V_DELETE_SQL || V_CONDITION; --SQLERRM(SQLCODE) -- 执行删除操作 EXECUTE IMMEDIATE V_DELETE_SQL; COMMIT; EXCEPTION WHEN OTHERS THEN R_DELETE_SECCUESS := -1; ROLLBACK TO DO_DELETE; -- 只有一行删除语句时可省略 SP_NM_EXCEPTION_LOG_INSERT('SP_NMS_DELETE',SQLCODE,SP_EXCEPTION_LOG_OPERATIONTYPE.C_DELETE);END SP_NMS_DELETE;?--插入默认系统时间 如果插入的时间不存在时CREATE OR REPLACE PROCEDURE SP_Nms_ADD-- 网信当前表添加功能-- 杨化龙-- 返回值R_INSERT_SUCCESS 0:成功,-1:失败( P_TITLE IN MMS_NM_NETMESSAGE.TITLE%TYPE, P_FILE_PATH IN MMS_NM_NETMESSAGE.FILE_PATH%TYPE, P_TYPE IN MMS_NM_NETMESSAGE.TYPE%TYPE, P_CREATE_TIME IN MMS_NM_NETMESSAGE.CREATE_TIME%TYPE, P_SEND_TIME IN MMS_NM_NETMESSAGE.SEND_TIME%TYPE, P_URL IN MMS_NM_NETMESSAGE.URL%TYPE, P_STATUS IN MMS_NM_NETMESSAGE.STATUS%TYPE, P_READ_FLAG IN MMS_NM_NETMESSAGE.READ_FLAG%TYPE, P_PUSH_MESSAGE IN MMS_NM_NETMESSAGE.PUSH_MESSAGE%TYPE, P_NM_SIZE IN MMS_NM_NETMESSAGE.NM_SIZE%TYPE, P_CALLER IN MMS_NM_NETMESSAGE.CALLER%TYPE, P_CALLED IN MMS_NM_NETMESSAGE.CALLED%TYPE, P_ACCESS_TIME IN MMS_NM_NETMESSAGE.ACCESS_TIME%TYPE, P_ACCESS_COUNT IN MMS_NM_NETMESSAGE.ACCESS_COUNT%TYPE, P_RECEIVE_TYPE IN MMS_NM_NETMESSAGE.Receive_Type%TYPE, R_INSERT_SUCCESS out NUMBER)Authid Current_UserASBEGIN-- 设置成功标识 R_INSERT_SUCCESS := 0; SAVEPOINT DO_INSERT; -- 插入数据 INSERT INTO MMS_NM_NETMESSAGE(id,title,file_path,type,create_time, send_time,url,status,read_flag,push_message,nm_size,caller,called, access_time,access_count,partcol,RECEIVE_TYPE)VALUES(NETMESSAGE_ID_SEQ.NEXTVAL,P_title,p_file_path,p_type,nvl(p_create_time,sysdate),p_send_time,p_url,p_status,p_read_flag,p_push_message,p_nm_size,p_caller,p_called,p_access_time,p_access_count,to_char(nvl(p_create_time,sysdate),'mmdd'),P_RECEIVE_TYPE); COMMIT;EXCEPTION WHEN OTHERS THEN-- 其他错误 R_INSERT_SUCCESS := -1; ROLLBACK TO DO_INSERT; -- 一条INSERT 语句时可省略-- 记录异常日志 SP_NM_EXCEPTION_LOG_INSERT('SP_Nms_ADD',sqlcode, SP_EXCEPTION_LOG_OPERATIONTYPE.C_INSERT);END SP_Nms_ADD;?--根据字段条件查询 按照时间查询 以及Oracle中的单引号字符串需要用""代替
CREATE OR REPLACE PROCEDURE SP_NMS_GETNM( P_ID IN MMS_NM_NETMESSAGE.ID%TYPE, P_TITLE IN MMS_NM_NETMESSAGE.TITLE%TYPE, P_FILE_PATH IN MMS_NM_NETMESSAGE.FILE_PATH%TYPE, P_TYPE IN MMS_NM_NETMESSAGE.TYPE%TYPE, P_CREATE_TIME IN MMS_NM_NETMESSAGE.CREATE_TIME%TYPE, P_SEND_TIME IN MMS_NM_NETMESSAGE.SEND_TIME%TYPE, P_STATUS IN MMS_NM_NETMESSAGE.STATUS%TYPE, P_READ_FLAG IN MMS_NM_NETMESSAGE.READ_FLAG%TYPE, P_CALLER IN MMS_NM_NETMESSAGE.CALLER%TYPE, P_CALLED IN MMS_NM_NETMESSAGE.CALLED%TYPE, P_ACCESS_COUNT in MMS_NM_NETMESSAGE.Access_Count%TYPE, P_CURSOR OUT SP_NMS_GETNM_PACKAGE.NM_CURSOR)isV_SQL varchar2(1000);V_SQL_HIS varchar2(1000);V_SET_VALUES varchar2(200);BEGIN V_SQL:='SELECT * FROM MMS_NM_NETMESSAGE M WHERE 1=1 ' ; V_SQL_HIS:='SELECT * FROM MMS_NM_NETMESSAGE_HIS T WHERE 1=1 ' ; -- 封装SET部分内容 IF P_ID IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || 'and id=' || P_ID; END IF; IF P_TITLE IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || 'and TITLE=''' || P_TITLE||''''; END IF; IF P_FILE_PATH IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' and FILE_PATH=''' || P_FILE_PATH||''''; END IF; IF P_TYPE IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' and TYPE=' || P_TYPE; END IF; IF P_CREATE_TIME IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES ||' and create_time=to_date(''' || to_char(P_CREATE_TIME,'yyyy-mm-dd HH24:mi:ss') || ''',''yyyy-mm-dd HH24:mi:ss'')' ; END IF; IF P_SEND_TIME IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES ||' and SEND_TIME=to_date(''' || to_char(P_SEND_TIME,'yyyy-mm-dd HH24:mi:ss') || ''',''yyyy-mm-dd HH24:mi:ss'')' ; END IF; IF P_STATUS IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' and STATUS=' || P_STATUS; END IF; IF P_READ_FLAG IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' and READ_FLAG=' || P_READ_FLAG; END IF; IF P_CALLER IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' and CALLER=' || P_CALLER; END IF; IF P_CALLED IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' and CALLED=' || P_CALLED; END IF; IF P_ACCESS_COUNT IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' and ACCESS_COUNT=' || P_ACCESS_COUNT; END IF; V_SQL:= V_SQL||' '||V_SET_VALUES; V_SQL_HIS:= V_SQL_HIS||' '||V_SET_VALUES; V_SQL:= V_SQL||' union all '||V_SQL_HIS; dbms_output.put_line(V_SQL);OPEN P_CURSOR FOR V_SQL;END SP_NMS_GETNM;?---单表更新 demo
CREATE OR REPLACE PROCEDURE SP_NMS_UPDATEMESSAGE -- AUTHOR : YANG,HUALONG -- CREATED : 2011-03-28 13:14:14 -- PURPOSE : NetMessage业务更新一条网信(只定义了接口)( P_ID IN MMS_NM_NETMESSAGE.ID%TYPE, P_TITLE IN MMS_NM_NETMESSAGE.TITLE%TYPE, P_FILE_PATH IN MMS_NM_NETMESSAGE.FILE_PATH%TYPE, P_TYPE IN MMS_NM_NETMESSAGE.TYPE%TYPE, P_CREATE_TIME IN MMS_NM_NETMESSAGE.CREATE_TIME%TYPE, P_SEND_TIME IN MMS_NM_NETMESSAGE.SEND_TIME%TYPE, P_URL IN MMS_NM_NETMESSAGE.URL%TYPE, P_STATUS IN MMS_NM_NETMESSAGE.STATUS%TYPE, P_READ_FLAG IN MMS_NM_NETMESSAGE.READ_FLAG%TYPE, P_PUSH_MESSAGE IN MMS_NM_NETMESSAGE.PUSH_MESSAGE%TYPE, P_NM_SIZE IN MMS_NM_NETMESSAGE.NM_SIZE%TYPE, P_CALLER IN MMS_NM_NETMESSAGE.CALLER%TYPE, P_CALLED IN MMS_NM_NETMESSAGE.CALLED%TYPE, P_ACCESS_TIME IN MMS_NM_NETMESSAGE.ACCESS_TIME%TYPE, P_ACCESS_COUNT IN MMS_NM_NETMESSAGE.ACCESS_COUNT%TYPE, P_RECEIVE_TYPE IN MMS_NM_NETMESSAGE.RECEIVE_TYPE%TYPE, R_UPDATE_SUCCESS OUT NUMBER)AUTHID CURRENT_USER IS V_UPDATE_SQL VARCHAR(400) := 'UPDATE MMS_NM_NETMESSAGE SET ID=' || P_ID; V_SET_VALUES VARCHAR(200) := ''; V_ID MMS_NM_NETMESSAGE.ID%TYPE;BEGIN -- 设置成功标识 R_UPDATE_SUCCESS := 0; -- 设置回滚点 SAVEPOINT DO_UPDATE; -- SELECT ID INTO V_ID FROM MMS_NM_NETMESSAGE WHERE ID=P_ID; -- 封装SET部分内容 IF P_TITLE IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,TITLE=''' || P_TITLE||''''; END IF; IF P_FILE_PATH IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,FILE_PATH=''' || P_FILE_PATH||''''; END IF; IF P_TYPE IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,TYPE=' || P_TYPE; END IF; IF P_CREATE_TIME IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES ||' ,create_time=to_date('''|| to_char(P_CREATE_TIME,'yyyy-mm-dd HH24:mi:ss') || ''',''yyyy-mm-dd HH24:mi:ss'')' ; END IF; IF P_SEND_TIME IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES ||' ,SEND_TIME=to_date('''|| to_char(P_SEND_TIME,'yyyy-mm-dd HH24:mi:ss') || ''',''yyyy-mm-dd HH24:mi:ss'')' ; END IF; IF P_URL IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,URL=' || P_URL; END IF; IF P_STATUS IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,STATUS=' || P_STATUS; END IF; IF P_READ_FLAG IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,READ_FLAG=' || P_READ_FLAG; END IF; IF P_PUSH_MESSAGE IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,PUSH_MESSAGE=' || P_PUSH_MESSAGE; END IF; IF P_NM_SIZE IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,NM_SIZE=' || P_NM_SIZE; END IF; IF P_CALLER IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,CALLER=' || P_CALLER; END IF; IF P_CALLED IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,CALLED=' || P_CALLED; END IF; IF P_ACCESS_TIME IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES ||' ,ACCESS_TIME=to_date('''|| to_char(P_ACCESS_TIME,'yyyy-mm-dd HH24:mi:ss') || ''',''yyyy-mm-dd HH24:mi:ss'')' ; END IF; IF P_ACCESS_COUNT IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,ACCESS_COUNT=' || P_ACCESS_COUNT; END IF; IF P_RECEIVE_TYPE IS NOT NULL THEN V_SET_VALUES := V_SET_VALUES || ' ,RECEIVE_TYPE=' || P_RECEIVE_TYPE; END IF; V_UPDATE_SQL :=V_UPDATE_SQL || V_SET_VALUES || ' WHERE ID=' || P_ID; DBMS_OUTPUT.PUT_LINE(V_UPDATE_SQL); --设置回滚点 SAVEPOINT DO_UPDATE; -- 执行更新操作 EXECUTE IMMEDIATE V_UPDATE_SQL; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN -- 未查询到网信信息 R_UPDATE_SUCCESS := -1; ROLLBACK TO DO_UPDATE; -- 一条更新语句时可省略 -- 记录异常日志 SP_NM_EXCEPTION_LOG_INSERT('CREATE OR REPLACE PROCEDURE SP_NMS_UPDATEMESSAGE',SQLCODE, SP_EXCEPTION_LOG_OPERATIONTYPE.C_SELECT); WHEN OTHERS THEN R_UPDATE_SUCCESS := -1; ROLLBACK TO DO_UPDATE; SP_NM_EXCEPTION_LOG_INSERT('CREATE OR REPLACE PROCEDURE SP_NMS_UPDATEMESSAGE',SQLCODE,SP_EXCEPTION_LOG_OPERATIONTYPE.C_UPDATE);END;?