举例一个简单的增删改查存储过程demo

blueyanghualong

浏览: 230903 次
性别:
来自: 杭州

最近访客更多访客>>

yumgpkpm

hui459982242

DD.

zp刘涛

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

SQL Access Oracle

--如果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 MMS_NM_NETMESSAGE.Access_Count%TYPE,
  P_CURSOR OUT SP_NMS_GETNM_PACKAGE.NM_CURSOR
)
is
V_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;

分享到：