Oracle--存储过程

 开发者博客：www.developsearch.com

 

 

savepoint
保存点，是事务中的一点，通过rollback可以返回到某个保存点。
一个事务中可以有多个保存点，一旦事务提交，该事务中的保存点会自动被删除，那么无论刚才做了多少个保存点，都统统没有了。

 

SAVEPOINT ROLLBACK_C2_POINT;
...
ROLLBACK TO ROLLBACK_C2_POINT;

 

 

输出异常信息

dbms_output.put_line("..................");

 

raise_application_error
用于在plsql使用程序中自定义不正确消息，将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或者其他前台开发语言)。
该异常只好在数据库端的子程序（流程、函数、包、触发器）中运用，而无法在匿名块和客户端的子程序中运用。
语法为 ：raise_application_error(error_number,error_msg[,[truefalse]]);
其中
error_number用于定义不正确号，该不正确号必须在-20000到-20999之间的负整数；
error_msg用于指定不正确消息，并且该消息的长度无法超过2048字节；
第三个参数假如为true，则该不正确会被放在先前不正确堆栈中，假如为false（默认值）则会替代先前所有不正确。

IF product_not_found THEN
      RAISE_APPLICATION_ERROR(-20123,'Invald product code'TRUE);
ENDIF;

例：阻止小于18岁的用户增加到数据库 employee 表中
CREATE OR REPALCE TRIGGER minimun_age_check BEFORE INSERT ON employee FOR EACH ROW
BEGIN
        IF ADD_MONTHS( :new.birth_date, 18*12) > SYSDATE
        THEN
               RAISE_APPLICATION_ERROR(-20001, 'Employees must at least eighteen years of age.');
        END IF;
END;

下面我们编写一个客户端程序，为了简单，同样用PL/SQL调用
DECLARE
       no_babies_allowed EXCEPTION;
       /*将名称与用于触发器中的错误号码关联起来*/
       PRAGMA EXCEPTION_INIT(no_babies_allowed, -20001);
BEGIN
         INSERT INTO employee .;
         EXCEPTION
         WHEN no_babies_allowed
         THEN
                /*
                || SQLERRM 将传递给内置过程 RAISE_APPLICATION_ERROR 的消息返回
                */
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

 

 

 

新折扣例子：验证两个组合是否完全相同

@SuppressWarnings("unchecked")
public String validateSetsRepeat(final String distCodes,
	final String regionCodes, final String timeCodes,
	final String businessCodes, final String cargoCodes,
	final String appendCodes, final Long setId) {
		Object object = getHibernateTemplate().execute(new HibernateCallback() {
			@SuppressWarnings("deprecation")
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				CallableStatement statement = session.connection().prepareCall("{call PKG_VALID_SET_INTERSECT.CDH_CHECK_REPEAT_RBTSET(?,?,?,?,?,?,?)}");
				statement.setString(1, distCodes);
				statement.setString(2, regionCodes);
				statement.setString(3, businessCodes);
				statement.setString(4, cargoCodes);
				statement.setString(5, timeCodes);
				statement.setString(6, appendCodes);
				statement.setLong(7, setId == null ? -1L : setId);
				statement.registerOutParameter(7, Types.BIGINT);
				statement.execute();
				return statement.getObject(7);
			}
		});
		return object == null ? null : object.toString();
}

PROCEDURE CDH_CHECK_REPEAT_RBTSET(DIRS     IN VARCHAR2,
                                    REGIONS  IN VARCHAR2,
                                    BUSINESS IN VARCHAR2,
                                    CARGOS   IN VARCHAR2,
                                    TIMES    IN VARCHAR2,
                                    APPENDS  IN VARCHAR2,
                                    SETID    IN OUT NUMBER) AS
    V_COUNT NUMBER;
  BEGIN
    FOR RBT_SET IN (SELECT T.SET_ID,
                           T.DIR_ID,
                           T.REGION_DMNSN_ID,
                           T.BUSINESS_DMNSN_ID,
                           T.CARGO_DMNSN_ID,
                           T.TIME_DMNSN_ID,
                           T.APPEND_DMNSN_ID
                      FROM CDH_NEWRBT_SET T
                     WHERE T.SET_ID <> DECODE(SETID, NULL, -1, SETID)) LOOP
    
      -- 判断流向是否完全相等 依据：如果A-B=B-A 表示A,B两项完全相等
      WITH DIR AS
       (SELECT D.ORG_DIST_CODE || '_' || D.DESC_DIST_CODE
          FROM CDH_NEWRBT_SET_DIR D
         WHERE D.DIR_ID = RBT_SET.DIR_ID),
      IN_DIR AS
       (SELECT * FROM TABLE(SPLIT_STR(DIRS)))
      SELECT SUM(M)
        INTO V_COUNT
        FROM (SELECT COUNT(0) AS M
                FROM (SELECT DIR.*
                        FROM DIR
                      MINUS
                      SELECT IN_DIR.* FROM IN_DIR)
              UNION
              SELECT COUNT(0)
                FROM (SELECT IN_DIR.*
                        FROM IN_DIR
                      MINUS
                      SELECT DIR.* FROM DIR));
    
      -- 判断流向是否完全相等 等于零表示A-B=B-A 全等，接下来判断其他维度是否全等
      IF V_COUNT = 0 THEN
      
        -- 判断区域类型 是否完全相同
        V_COUNT := COUNT_DMNSN_MINUS(RBT_SET.REGION_DMNSN_ID, REGIONS);
        IF V_COUNT = 0 THEN
        
          -- 判断业务类型 是否完全相同
          V_COUNT := COUNT_DMNSN_MINUS(RBT_SET.BUSINESS_DMNSN_ID, BUSINESS);
          IF V_COUNT = 0 THEN
          
            -- 判断快件内容 是否完全相同
            V_COUNT := COUNT_DMNSN_MINUS(RBT_SET.CARGO_DMNSN_ID, CARGOS);
            IF V_COUNT = 0 THEN
            
              -- 判断时效 是否完全相同
              V_COUNT := COUNT_DMNSN_MINUS(RBT_SET.TIME_DMNSN_ID, TIMES);
              IF V_COUNT = 0 THEN
              
                -- 判断附加费 是否完全相同
                V_COUNT := COUNT_DMNSN_MINUS(RBT_SET.APPEND_DMNSN_ID,
                                             APPENDS);
                IF V_COUNT = 0 THEN
                
                  -- 运行到此处表示检测到完全相同的组合
                  SETID := RBT_SET.SET_ID;
                
                  -- 立即中断返回
                  RETURN;
                
                END IF;
              
              END IF;
            
            END IF;
          
          END IF;
        
        END IF;
      
      END IF;
    
    END LOOP;
  
    -- 如果以上循环查找未返回结果 表示没有找到完全相同的组合 程序返回NULL
    SETID := NULL;
  
  END;

 

开发者博客：www.developsearch.com