--如果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_USER
AS  
  -- 声明删除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_User

AS

BEGIN
--  设置成功标识
  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