一 : oracle first 和last的用法

oracle first 和last的用法

select first(p_age) from person　提示fisrt标识符无效 不知道错在哪里,请人指教下,并详解

oracle first 和last的用法的参考答案

ORACLE 没有名字为FIRST和LAST函数

如果要实现这样的功能,可以用分析函数,这个效率高点

二 : 数据库MySQL与Oracle的一些去O注意项

一、oracle递归查询语句start with ...connect by prior

①　给你一张表，表里面有主键id,以及该项的父节点parent_id,查询出该表中所有的父子关系节点树？

Oracle: start with ...connect by prior

例如：对分类下的所有组图(包括子分类下的组图)列表进行分页

select g.* from t_group g, t_counter c where g.counter_id = c.id and g.category_id in ( select id from t_category start with id = ? connect by prior id = parent_id and status = 1) and status = 1 order by c.pv desc

mysql:没有oracle那种自带的查询参数，在java端写递归函数，或者递归的存储过程

递归查找某一分类下的所有子分类,数据表结构如图

CREATE TABLE t_category ( category_id int(20) unsigned NOT NULL COMMENT '栏目id，主键', parent_id int(20) unsigned COMMENT '分类父类id', original_parent_id int(20) unsigned COMMENT '原始父类id，放到垃圾箱保留原有从属关系', name varchar(255) COMMENT '名称', code varchar(100) COMMENT '编码', ... status int(2) COMMENT '状态；0： 草稿, 1： 通过（发布）, -1： 删除', ... )

java解决方案:

public void getAllChildren(long categoryId, int status, List<Long> categoryIdList) {  List<Long> childrenIds = getCategoryChildrenIds(categoryId, status);  for (long cateId : childrenIds) {  categoryIdList.add(cateId);  int count = geliDao.count("select count(1) from t_category where parent_id = ? and status = ?", cateId, status);  if (count > 0) {  getAllChildren(cateId, status, categoryIdList);  }  }  }

在要使用该递归函数的地方，该这样使用

public Pager<Group> findGroupByCateAndName(long categoryId, String name, int pageNo, int pageSize) {  SqlBuilder sqlBuilder = new SqlBuilder();  sqlBuilder.appendSql("select group_id from t_group where category_id in ");  List<Long> routeIds = new ArrayList<Long>();  routeIds.add(categoryId);  getAllChildren(categoryId, Category.STATUS_NORMAL, routeIds);  sqlBuilder.appendValues(routeIds.toArray());  if(StringUtils.isNotBlank(name)) {  sqlBuilder.appendSql(" and name like ");  sqlBuilder.appendValue("%" + name + "%");  }  sqlBuilder.appendSql(" order by group_id desc");  LOG.debug("findGroupByCateAndName : {}; {}", sqlBuilder.getSql(), sqlBuilder.getValues());  return new Pager<Group>(sqlBuilder.getSqlExt(), sqlBuilder.getValuesExt(), pageNo, pageSize);  }

二、oracle函数decode处理

在Oracle/PLSQL中,  decode 具有和 IF-THEN-ELSE 一样的功能。［www.61k.com］

decode 函数语法如下：

decode( expression , search , result [, search , result]... [, default] )

expression 要比较的表达式.

search 要与expression 比较的字段。.

result 如果expression 与search 一样的话，返回该结果。.

default 此参数可选，如果没有与expression 匹配上的search . 就返回此结果，如果此参数没有设置，当没有与expression匹配上的search时，返回null。

search 和 result可成对出现多次，代表各种要匹配的情况。

sign(number) 函数返回一个数字的正负标志.

问题1:  现在一个阅读者想问，怎么使用decode函数来比较两个日期呢？（例如：date1 和 date2）, 如果date1 > date2, decode 函数返回date2. 否则decode函数返回 date1.

可用decode函数绑定SIGN 函数 像下面这样:

上面比较日期的语句可修改如下:

DECODE(SIGN(date1-date2), 1, date2, date1)

eg：如果有app参数就查询该app对应的指令数目,否则查询所有指令数目

public long searchCommandCount(String app)  {  if(app == null) app="";  String sql = "select count(0) "+  "from t_command a,t_application b where a.applicationid=b.id and b.application = decode(?,'',b.application,?) ";  return simpleJdbcTemplate.queryForLong(sql, app, app);  }

mysql处理后

public long searchCommandCount(String app)  {  if(app == null) app="";  StringBuilder sb = new StringBuilder();  sb.append("select count(0) from t_command a,t_application b where a.applicationid=b.id");  if(!"".equals(app)){  sb.append(" and b.application = "+ app);  }  /*String sql = "select count(0) "+  "from t_command a,t_application b where a.applicationid=b.id and b.application = decode(?,'',b.application,?) ";*/  return simpleJdbcTemplate.queryForLong(sb.toString());  }

三、oracle自增长序列处理

关于SEQUENCE 的基本知识请参考ORACLE SEQUENCE用法

原处理方案：

CREATE SEQUENCE SEQ_TONY_APK_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1976634 CACHE 20 NOORDER NOCYCLE ;

//获取id public long createTonyId() {  return simpleJdbcTemplate.queryForLong("select seq_tony_apk_id.nextval from dual"); }

mysql解决方案：

普通的可能会想到用mysql的自增长auto_increament,不过这个在数据库做分库分表的时候，有可能出问题，具体原因请参照数据库分库分表(sharding)系列(二) 全局主键生成策略

使用全局主键表

CREATE TABLE gl_keygen( table_name varchar(255) NOT NULL COMMENT '表名，主键', last_used_id int(20) unsigned NOT NULL COMMENT '最后使用的id' ) ENGINE= InnoDB DEFAULT CHARSET=gbk;

我们使用一个Java类来控制某一字段自增长

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Map; import javax.sql.DataSource; public class IdTableGenerator {  DataSource idGenDataSource;  public void setIdDataSource(DataSource idGenDataSource) {  this.idGenDataSource = idGenDataSource;  }  int size = 10;  Map<String, IdHolder> holderMap = new java.util.concurrent.ConcurrentHashMap<String, IdHolder>();  public long generate(String tableName, String columnName) {  IdHolder holder = holderMap.get(tableName);  if (holder == null) {  holder = new IdHolder();  holderMap.put(tableName, holder);  }  synchronized (holder) {  if (holder.needAlloc()) {  long lastUsedId = alloc(tableName, columnName, size);  holder.currentId = lastUsedId + 1;  holder.limit = lastUsedId + size;  } else {  holder.currentId ++;  }  return holder.currentId;  }  }  static class IdHolder {  long currentId;  long limit;  boolean needAlloc() {return currentId >= limit; }  }  public long alloc(String tableName, String columnName, int size) {  long result = 0;  Connection con = null;  boolean oldAutoCommit = false;  try {  con = idGenDataSource.getConnection();  oldAutoCommit = con.getAutoCommit();  con.setAutoCommit(false);  int updateCount = updateLastUsedId(con, tableName, columnName, size);  if (updateCount == 0) {  initIdTable(con, tableName, columnName);  }  result = getLastUsedId(con, tableName, columnName);  con.commit();  } catch (Exception e) {  try {  con.rollback();  } catch (Exception ex) {  ex.printStackTrace();  }  throw new RuntimeException(e);  } finally {  if (con != null) {  try {  con.setAutoCommit(oldAutoCommit);  con.close();  } catch (Exception ex) {  ex.printStackTrace();  }  }  }  return result;  }  static long getLastUsedId(Connection con, String tableName, String columnName) throws SQLException {  PreparedStatement ps = con.prepareStatement("select LAST_USED_ID from GL_KEYGEN where table_name = ?");  ps.setString(1, tableName);  ResultSet rs = ps.executeQuery();  rs.next();  long result = rs.getLong(1);  rs.close();  ps.close();  return result;  }  static int updateLastUsedId(Connection con, String tableName, String columnName, int size) throws SQLException {  PreparedStatement ps = con.prepareStatement("update GL_KEYGEN set last_used_id = last_used_id + ?" +  " where table_name = ?");  ps.setInt(1, size);  ps.setString(2, tableName);  int result = ps.executeUpdate();  ps.close();  return result;  }  static void initIdTable(Connection con, String tableName, String columnName) throws SQLException {  PreparedStatement ps = con.prepareStatement("select max(" + columnName + ") from " + tableName);  ResultSet rs = ps.executeQuery();  rs.next();  long maxId = rs.getLong(1);  rs.close();  ps.close();  ps = con.prepareStatement("insert into GL_KEYGEN (table_name, last_used_id) values (?, ?)");  ps.setString(1, tableName);  ps.setLong(2, maxId);  ps.executeUpdate();  ps.close();  } }

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/test"/> <bean id="idGenerator" class="cn.tony.repository.IdTableGenerator" p:idDataSource-ref="dataSource"/>

数据源配置

<database>  <jndi-name>jdbc/test</jndi-name>  <driver type="com.mysql.jdbc.Driver">  <url>jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&amp;characterEncoding=GBK&amp;zeroDateTimeBehavior=convertToNull  </url>  <user>root</user>  <password>root</password>  </driver>  <prepared-statement-cache-size>8</prepared-statement-cache-size>  <max-connections>20</max-connections>  <max-idle-time>30s</max-idle-time>  </database>

主键自增生成器使用案例：

public abstract class AbstractRepository <T> {  @Autowired  IdTableGenerator idGenerator;  protected long getNextId(String tableName, String columnName){  return idGenerator.generate(tableName, columnName);  }  //默认使用id做主键  protected long getNextId(String tableName){  return idGenerator.generate(tableName, "id");  } } public class TonyRepository extends AbstractRepository<Tony> {  public long createTonyId(){  return getNextId(Tony.TABLE_NAME);  } }

四、其他函数

to_date---> str_to_date
(yyyy-MM-dd HH24:mi:ss) --- (%Y-%m-%d %H:%i:%s)
sysdate --->sysdate()
to_char --->mysql中没有对应的函数，MySQL必要时自动变换数字为字符串

mysql数据类型和长度

mysql中key 、primary key 、unique key 与index区别

Oracle的to_date函数

五、SQL中特殊符号处理

单引号处理

用字符串拼接的话，单引号必须经过判断并替换，在数据库中，用2个单引号代表1个实际的单引号。所以，如果是拼接方式，需要用String.Replace("’", "”")来替换一下，将1个单引号替换为2个就没有问题了。在模糊查询中,为了避免单引号，我们使用参数的方式，下面的语句是不对的：

SELECT * FROM yourTable WHERE name LIKE ‘%?%’;在这个句子中，’%?%’被整体当作一个字符串来处理，你无论如何查询不到结果。修改一下，SELECT * FROM yourTable WHERE name LIKE ?;然后添加参数的时候这么添加：

new Parameter("?", "%" + categoryName + "%"); 

通配符_ % 处理

如果用户输入的查询条件中含有通配符，必须将这些字符作为数据而不是通配符来对待

s = s.Replace("%", "[%]"); 
s = s.Replace("_", "[_]"); 

左方括号([)问题

如果用户输入的查询参数本身就包括方括号时，会出现什么结果呢？ 
根据用户的期望，如果输入一个方括号，查询结果中应该只包括那些字段值中含有方括号的记录。 
但是实验结果表明，如果是没有配成对的单个左方括号，查询时这个左方括号会被忽略。 
也就是说，下面这个语句： 
WHERE T2.name like (%+ [ + %) 
等价于下面这个语句： 
WHERE T2.name like (%+ + %) 
这将导致查询结果中包含表中的全部记录，就像没有任何过滤条件一样。 
为此，如果用户输入的查询条件中含有左方括号的话，还必须对左方括号进行转义： 
s = s.Replace("[", "[[]"); 
注：右方括号没有这个问题。 

结论

为了防止SQL注入，同时避免用户输入特殊字符时查询结果不准确的问题，应该做两件事： 
（1）使用参数化查询。 
（2）在使用用户输入的字符串数据设置查询参数值之前，首先调用下面的共通处理函数： 
private static string ConvertSql(string sql) {
  sql = sql.Replace("[", "[[]"); // 这句话一定要在下面两个语句之前，否则作为转义符的方括号会被当作数据被再次处理 
  sql = sql.Replace("_", "[_]").Replace("%", "[%]"); 
  return sql; 
}

三 : oracle Wallet的使用

oracle Wallet的使用（即内部加密技术TDE（Transparent Data Encryption ））

1. TDE是Oracle10gR2中推出的一个新功能，使用时要保证Oracle版本是在10gR2或者以上

--查看oracle版本：

select * from v$version;

2、创建一个新目录，并指定为Wallet目录

D:\oracle\product\10.2.0\admin\ora10\ora_wallet

3. 设置wallet目录，在参数文件sqlnet.ora中，按照下面的格式加入信息：

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)

(METHOD_DATA=(DIRECTORY=D:\oracle\product\10.2.0\admin\ora10\ora_wallet)))

4. 创建master key文件，指定wallet密码，使用SYS用户登入系统，建立加密文件

SQL> alter system set encryption key identified by "wallet";

System altered

-- 密码"wallet"不加引号时，后面使用时也不需要用引号

此时在设置的目录下，多出一个Personal Information Exchange类型的文件，相当于我们生成的master key文件。D:\oracle\product\10.2.0\admin\ora10\ora_wallet\ewallet.p12

5、启动、关闭Wallet

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet";

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet"

ORA-28354: wallet 已经打开

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE; --关闭

System altered

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet"; --打开

System altered

到此，已经成功配置了Wallet，创建了master key。

下面看如何使用TDE进行数据加密：

加密数据列：

对数据列加密是TDE一个常用的功能。常需要对数据库中某个表的某个敏感数据进行加密处理，方式信息的外泄。

首先，在定义数据表中的数据列（或者修改数据列）的时候，使用ENCRYPT进行标注。表示这个字段是使用加密保护的重要字典。

--建临时表：

create table t_tmp_emplorey

as

select * from emplorey t

--加密数据列，使用了ENCRYPT进行标志，表明需要对这个字段进行加密处理，采用默认的加密配置。

alter table scott.t_tmp_emplorey modify (sal encrypt);

注意：在默认不指定的情况下，Oracle在加密之前，对明文都要进行salt处理。所谓salt处理是一种强化加密数据的方法。通过在加密前明文中掺入一个随机字符串，来强化加密层级，防止进行字典攻击和其他类型的破解操作。如果不需要进行salt处理，就是在ENCRYPT后面加No Salt。

--例：alter table scott.t_tmp_emplorey modify (sal encrypt no salt);

--指定加密算法

alter table scott.t_tmp_emplorey modify (sal encrypt using '3DES168');

--如果要对一个已经加密处理的数据列，解除加密，使用alter table…和DECRYPT关键字就可以实现。

SQL> alter table scott.t_tmp_emplorey modify (sal DECRYPT) ;

Table altered

--解除加密后，关闭Wallet后，查询数据不受影响。

6. 对查询、索引的影响：

查询结果似乎和一般的没有差别，但是如果关闭了解密Wallet会如何？

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

System altered

--再次查询时报错

select * from scott.t_tmp_emplorey t;

ORA-28365: Wallet 未打开

--能够查找到数目

select count(1) from scott.t_tmp_emplorey t;

--对索引的影响，如果列加密使用了salt，在对该列进行索引的时候，会报错。：

SQL> create index ind_t_tmp_emplorey on t_tmp_emplorey(sal);

create index ind_t_tmp_emplorey on t_tmp_emplorey(sal);

ORA-28338: 无法使用salt 值加密索引列

SQL> create index ind_t_tmp_emplorey on t_tmp_emplorey(EMPNO);

Index created

--没有加密的列可以建立索引

本文出自 “srsunbing” 博客

本文标题：oracle数据库的使用-oracle first 和last的用法
本文地址： http://www.61k.com/1056029.html