sql用法

case 用法：

SELECT ORDER_ID, EXTERNAL_SOURCE_REF, (case when FIXED_CCY_CD = BASE_CCY_CD then NON_BASE_CCY_CD else BASE_CCY_CD end) as COUNTERCURRENCYID, ORDER_STATE_ID,FIXING_DTS, TO_CHAR(FIXING_DTS, 'DD-MM-YYYY'),TO_CHAR(FIXING_DTS, 'HH24:MI'), (case when BANK_BUYS_FIXED_FG='Y' then 'S' else 'B' end) as SIDE, COMMENT_1_TXT from atable where ...

sql function (SELECT * from table( PKG_Name.FN_SPLIT('a,b,c',',') ) where column_value='a'):

FUNCTION FN_SPLIT (P_SOURCE_STR IN VARCHAR2, P_DELIMITER IN VARCHAR2)
	    RETURN LIST_STR PIPELINED
	IS
	    TARGER_LOCATION INT := 0;
	    START_LOCATION INT := 1;
	    SOURCE_LENGTH INT := 0;
	    DELIMITER_LENGTH INT := 0;
	    RESULT_STR VARCHAR2 (4000);
	BEGIN
	    SOURCE_LENGTH := LENGTH (P_SOURCE_STR);
	    DELIMITER_LENGTH := LENGTH (P_DELIMITER);
	    WHILE TARGER_LOCATION < SOURCE_LENGTH
	    LOOP
	        TARGER_LOCATION := INSTR (P_SOURCE_STR, P_DELIMITER, START_LOCATION);
	        IF TARGER_LOCATION = 0
	        THEN
	            TARGER_LOCATION := SOURCE_LENGTH;
	            RESULT_STR := SUBSTR (P_SOURCE_STR, START_LOCATION);
	            PIPE ROW (RESULT_STR);
	            IF START_LOCATION >= SOURCE_LENGTH
	            THEN
	                EXIT;
	            END IF;
	        ELSE
	            RESULT_STR := SUBSTR (P_SOURCE_STR, START_LOCATION, TARGER_LOCATION - START_LOCATION);
	            START_LOCATION := TARGER_LOCATION + DELIMITER_LENGTH;
	            PIPE ROW (RESULT_STR);
	        END IF;
	    END LOOP;
	    RETURN;
END FN_SPLIT;

PROCEDURE DeleteCounterparty(
    	P_REGION		IN  VARCHAR2
 )
 AS
	PCONUT_STR       VARCHAR2(4000);
	PERROR_STR       VARCHAR2(4000);
	BEGIN
    PERROR_STR := '';

	SELECT listagg(column_value, ',' ) within GROUP (ORDER BY column_value) AS REGION INTO PCONUT_STR FROM TABLE(FN_SPLIT(P_REGION,',')) 
		WHERE column_value NOT IN 
		(SELECT a.region FROM
		(SELECT SOURCE_REGION || '|' || TARGET_REGION AS region FROM COUNTERPARTY_MAPPING) a); 

	IF (NVL(length(PCONUT_STR),0) > 0) THEN
	 PERROR_STR := PERROR_STR || 'Unsupported Operation: Risk Routing Counterparty Code(s) '''||PCONUT_STR||''' not existed. ';
	END IF;

	IF(NVL(length(PERROR_STR),0) > 0) THEN
	 	RAISE_APPLICATION_ERROR (-20001, PERROR_STR);
	ELSE
	 	DELETE FROM COUNTERPARTY_MAPPING where SOURCE_REGION || '|' || TARGET_REGION in (SELECT column_value FROM TABLE(FN_SPLIT(P_REGION,',')));
	END IF;	 
END DeleteCounterparty;

PROCEDURE AddBroker(
    	P_INTERNAL_GROUP 		IN  VARCHAR2,
		P_EXTERNAL_GROUP 		IN  VARCHAR2,
		P_CLIENT_BROKER 				IN  VARCHAR2,
		P_POSITION_BROKER 				IN  VARCHAR2,
		P_USER 							IN  VARCHAR2
 )
 AS
	PCONUT       NUMBER;
	PERROR_STR   VARCHAR2(4000);

	BEGIN
	PERROR_STR := '';
	SELECT COUNT(*) INTO PCONUT FROM BROKER_MAPPING WHERE INTERNAL_GROUP=P_INTERNAL_GROUP and EXTERNAL_GROUP=P_EXTERNAL_GROUP;
	IF (PCONUT>0) THEN
		RAISE_APPLICATION_ERROR (-20001, 'Unique Constraint: Internal Trader Broker Mapping '''||P_INTERNAL_GROUP||''' and External Trader Broker Mapping '''||P_EXTERNAL_GROUP|| ''' already existed.');
	ELSE
	   SELECT COUNT(*) INTO PCONUT FROM GROUP_MAPPING WHERE GROUP_ID=P_INTERNAL_GROUP;
	   IF (PCONUT=0) THEN
	   		PERROR_STR := PERROR_STR || 'Unsupported Operation: Trader Group(s) '''||P_INTERNAL_GROUP||'''';
	   END IF;

	   SELECT COUNT(*) INTO PCONUT FROM GROUP_MAPPING WHERE GROUP_ID=P_EXTERNAL_GROUP;
	   IF (PCONUT=0) THEN
	   		IF (NVL(length(PERROR_STR),0) > 0) THEN
	   			PERROR_STR := PERROR_STR || ' and ''' || P_EXTERNAL_GROUP ||''' both do not exist in Risk Trader Group Mapping.';
	   		ELSE
	   			PERROR_STR := PERROR_STR || 'Unsupported Operation: Trader Group(s) '''||P_EXTERNAL_GROUP||''' does not exist in Risk Trader Group Mapping.';
	   		END IF;
	   	ELSE
	   	   IF (NVL(length(PERROR_STR),0) > 0) THEN
	   			PERROR_STR := PERROR_STR || ' does not exist in Risk Trader Group Mapping.';
	   	   END IF;
	   END IF;

	   IF (NVL(length(PERROR_STR),0) > 0) THEN
	   			RAISE_APPLICATION_ERROR (-20001, PERROR_STR);
	   	ELSE
	   			INSERT INTO BROKER_MAPPING(INTERNAL_GROUP,EXTERNAL_GROUP,CLIENT_BROKER,POSITION_BROKER,CREATED_BY,CREATED_DT,MODIFIED_BY,MODIFIED_DT)
	        	VALUES(P_INTERNAL_GROUP,P_EXTERNAL_GROUP,P_CLIENT_BROKER,P_POSITION_BROKER,P_USER,CURRENT_DATE,P_USER,CURRENT_DATE);
	   	END IF;
	END IF;
END AddBroker;

PROCEDURE DeleteGroupInfo(
    	P_GROUP 		IN  VARCHAR2
 )
 AS
	PCONUT_STR       VARCHAR2(4000);
	PERROR_STR       VARCHAR2(4000);
	BEGIN
    PERROR_STR := '';

	SELECT listagg(column_value, ',' ) within group (order by column_value) as GROUP_ID INTO PCONUT_STR FROM TABLE(FN_SPLIT(P_GROUP,',')) 
		where column_value not in (
			SELECT distinct GROUP_ID from GROUP_MAPPING 
				WHERE GROUP_ID in (SELECT * FROM TABLE(FN_SPLIT(P_GROUP,',')))
	);

	IF (NVL(length(PCONUT_STR),0) > 0) THEN
	 PERROR_STR := PERROR_STR || 'Unsupported Operation: Risk Trader Group Code '''||PCONUT_STR||''' not existed. ';
	END IF;

	 SELECT listagg(GROUP_ID, ',' ) within group (order by GROUP_ID) as GROUP_ID INTO PCONUT_STR from GROUP_MAPPING 
		WHERE GROUP_ID in (
		    select distinct INTERNAL_GROUP from BROKER_MAPPING WHERE INTERNAL_GROUP in (SELECT * FROM TABLE(FN_SPLIT(P_GROUP,',')))
		    union
		    select distinct EXTERNAL_GROUP from BROKER_MAPPING WHERE EXTERNAL_GROUP in (SELECT * FROM TABLE(FN_SPLIT(P_GROUP,',')))
    );

	 IF (NVL(length(PCONUT_STR),0) > 0) THEN
	 	PERROR_STR := PERROR_STR || 'Constraint Error: '''||PCONUT_STR||''' has been referred by Broker Mapping';
	 END IF;

	 IF(NVL(length(PERROR_STR),0) > 0) THEN
	 	RAISE_APPLICATION_ERROR (-20001, PERROR_STR);
	 ELSE
	 	DELETE FROM GROUP_MAPPING WHERE GROUP_ID in (SELECT * FROM TABLE(FN_SPLIT(P_GROUP,',')));
	 END IF;


END DeleteGroupInfo;

public Map<String, Object> executeNotCloseConn(AbstractCallableStatement procedure,Connection conn)
            throws SQLException {
    	if(procedure == null) {
    		throw new SQLException("StoredProcedure is null.");
    	}
        CallableStatement cs = null;
        Map<String, Object> returnedResults = null;
        try {
            cs = conn.prepareCall(procedure.getCallString());
            CallableStatement csToUse = this.prepareCallableStatement(cs, procedure.getDeclaredParameters());
            csToUse.execute();
            returnedResults = extractOutputParameters(csToUse, procedure.getDeclaredParameters());
        } catch (SQLException e) {
            this.rethrow(e, procedure.getCallString(), procedure.getDeclaredParameters());
        }finally {
        	DBUtils.closeQuietly(cs);
        }
        return returnedResults;
    }

public abstract class DatabaseAccessSupport {

	private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseAccessSupport.class);

	private DataSourceFactory dataSourceFactory;

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public JdbcTemplate getJdbcTemplate(){
		if (jdbcTemplate == null) {
			jdbcTemplate = new JdbcTemplate(dataSourceFactory.getDataSource());
			LOGGER.trace("JdbcTemplate set to [{}]", jdbcTemplate);
		}

		return jdbcTemplate;
	}

	public void setDataSourceFactory(final DataSourceFactory dataSourceFactory) {
		this.dataSourceFactory = dataSourceFactory;
	}
}

	/**
	 * @param tradeIds
	 * @return Map<TRADE_ID, FIXML_TEXT>
	 */
	public Map<String, String> findAllFIXML(final Collection<String> tradeIds) {

		if (tradeIds.isEmpty()) {
			return new HashMap<String, String>();
		}

		NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(super.getJdbcTemplate());

		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("ids", tradeIds);

		try {
			return jdbcTemplate.query(QUERY_FIXML_SQL, parameters, new ResultSetExtractor<Map<String, String>>() {
				@Override
				public Map<String, String> extractData(ResultSet rs) throws SQLException, DataAccessException {
					Map<String, String> idMap = new HashMap<String, String>();
					while (rs.next()) {
						idMap.put(rs.getString(1), rs.getString(2));
					}
					return idMap;
				}
			});
		} catch (Exception e) {
			throw new OMSDBConnectionException(e);
		}
	}

exp rpadba/rpadba@${host} file=E:\FXAG_DB_Dump\rpadba.dump owner=('RPADBA') log=E:\FXAG_DB_Dump\rpadba.log 

imp rpadba/rpadba@${host} file=E:\FXAG_DB_Dump\rpadba.dump log=E:\FXAG_DB_Dump\rpadba_imp.log fromuser=RPADBA touser=RPADBA


GRANT CONNECT TO $schema;
GRANT CREATE VIEW TO $schema;
GRANT DEBUG ANY PROCEDURE TO $schema;
GRANT DEBUG CONNECT SESSION TO $schema;
GRANT RESOURCE TO $schema;
GRANT UNLIMITED TABLESPACE TO $schema;

TNS_ADMIN:
C:\app\

export GTH DB:
exp gthdba/gthdba@${host} file=c:\FXAG_DB_Dump\rpadba.dump owner=('RPADBA') log=E:\FXAG_DB_Dump\rpadba.log