- 浏览: 300776 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
wst0350:
点赞,有空深入讲解下原理
Servlet、Filter 和 Listener 调用顺序、生命周期的实验分析 -
tuspark:
Eclipse中高亮设置内容还有很多细节选项可以设置的,可以看 ...
Eclipse 设置匹配代码高亮 -
xichao1929:
这个时候,怎么启动发布的项目呢?????
JBoss设置为Windows服务 -
xiaozi7:
非常感谢,楼主的英语水平不一般那
WebSphere MQ Version 7 发布订阅相关配置 -
qtlkw:
slave没玩过
Hudson: java.lang.OutOfMemoryError: Java heap space error
case 用法:
sql function (SELECT * from table( PKG_Name.FN_SPLIT('a,b,c',',') ) where column_value='a'):
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
发表评论
-
oracle 分页 sql
2011-07-12 11:34 1253select * from (select * from ... -
(转)数据库中乐观锁与悲观锁的概念
2011-03-06 19:23 1063锁( locking ) 业务逻辑的实现过程中,往往需要保证 ... -
Oracle Err "listener does not currently know of SID given in connect descriptor"
2010-10-20 09:54 1849关于启动数据库后提示ora-12505的解决方法: " ... -
oracle安装之后,EM和isqlplus的端口号的查看。
2010-08-23 15:32 1181D:\oracle\product\10.2.0\db_1\i ... -
oracle 迁移script
2010-07-24 18:45 978--============================= ... -
改变用户在某表空间的配额不足
2010-07-24 18:38 1516(1)查看用户的表空间的限额: select * from u ... -
ORA-01653: unable to extend table GAL_425_DATA.GAL_MESSAGES by 128 in tablespace
2010-07-19 17:35 3050(转载)http://akdora.wordpress.com ... -
(转)max_dump_file_size设置不当导致空间占满
2010-03-31 21:14 2203今天来发现一个机器空间用满了,700G的硬盘还没有什么数据就已 ... -
转载: oracle导入导出命令详解
2010-02-24 10:01 1676一. 导出工具 exp 1. 它 ... -
(转载)恢复oracle中用PL SQL误删除drop掉的表
2010-01-29 23:04 1908查看回收站中表 select object_name,ori ... -
在CentOS5.2上安装Oracle10g
2010-01-29 17:04 1729(1)可以运行如下命令检 ...
相关推荐
sql用法大全,包含sql多种基本用法 包含sql基础用法,提升用法。。。。
主要介绍SQL用法大全
mysql,oracel,sqlserver,db2 数据库sql用法
数据库操作的sql用法,很齐全,可以直接使用。
sql基本用法sql基本用法sql基本用法sql基本用法sql基本用法sql基本用法
图文并茂的SQL用法,非常详细,是初学者好资料
本SQL文件是在多年的工作经验中总结后编写的;因为业务复杂的需要;需要我们考虑性能、效率、精简的实现我们的SQL代码;该SQL技巧文档希望对你有很大的帮助。
SQL使用方法及常用语句 可以教你如何使用SQL
非常全面的sql用法 含:基础 、提升、技巧三部分,希望对大家有帮助
使用方法用于使用sql运用的一些问题,很好的解决一些sql的问题
dede在SQL里调用的方法。可以调用超链接。自己研究出来的。绝版。
很全的sql用法,希望对大家有所帮助
oracle手册,详解SQL用法 语法详解,偏重SQL
动态PL/SQL用法例子 begin execute immediate 'create table test_qiu(id number)'; end;
exists: 括号内子查询sql语句返回结果不为空(即:sql返回的结果为真),子查询的结果不为空这条件成立,执行...提醒:文章中提供了exists和not exists的常用示例,已经经过本人测试,文档中附有测试数据sql和用法sql。
很全的sql用法集,分享给大家 。值嘚收藏!
SQL Server使用方法SQL Server使用方法SQL Server使用方法
Oracle数据库-PL、SQL使用方法整理[参考].pdf
SQL常用命令使用方法 数据记录筛选 更新数据记录 删除数据记录 添加数据记录
数据库sql用法 1、说明:创建数据库 CREATEDATABASEdatabase-name 2、说明:删除数据库 dropdatabase dbname 3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', ...