oracle日常 常用函数与脚本

--在hibinate 中如何传递参数给 存储过程

---新建一个存储要存储集合id 的临时表

-- Create table
create table MBRC_OLTPORTID
(
  ID NUMBER(18) not null
)
tablespace NG_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


----存储过程接收list参数
----定义一个存储Long类型的数据库对象
CREATE TYPE AOBJECT AS OBJECT(
       a NUMBER(18)
       )
------定义一个List数据库对象
CREATE  TYPE ALIST  AS VARRAY(100) OF AOBJECT;  




---------存储过程（读取list 存放在新建的临时表里）

create or replace procedure mbrc_uptowncalculaterate(p1 in ALIST ,p2 out varchar2) as
 
begin
   TRUNCATE TABLE MBRC_OLTPORTID;
   for i in 1..p1.count loop
       insert into MBRC_OLTPORTID values(p1(i).a);      
   end loop;
   p2:='null';
end;


----JAVA CODE

@Override
    public Boolean calculateUptownRate(final ArrayList<Long> oltPorIds) {
        final String sql = "{CALL mbrc_uptowncalculaterate(?,?)}";
        return this.getHibernateTemplate().execute(
                new HibernateCallback<Boolean>() {
                    @SuppressWarnings("deprecation")
                    public Boolean doInHibernate(Session session)
                            throws HibernateException, SQLException {
                        Connection conn = null;
                        CallableStatement statement = null;
                        try {//获取数据库连接
                            conn = SessionFactoryUtils
                            .getDataSource(getSessionFactory()).getConnection();
                            ARRAY aArray = getArray(conn, "AOBJECT", "ALIST", oltPorIds);
                            statement = session.connection().prepareCall(sql);
                            statement.setArray(1, aArray);
                            statement.registerOutParameter(2, Types.VARCHAR);
                            statement.executeUpdate();                           
                        } catch (Exception e) {
                            e.printStackTrace();                            
                        } finally {
                            statement.close();
                            statement = null;
                        }
                        return statement.getString(2).equals("null");
                    }
                });        
    }
    /**

    private static ARRAY getArray(Connection con, String OracleObj,    
            String Oraclelist, ArrayList<Long> objlist) throws Exception {
        ARRAY list = null;
        if (objlist != null && objlist.size() > 0) {
            StructDescriptor structdesc = new StructDescriptor(OracleObj, con);
            STRUCT[] structs = new STRUCT[objlist.size()];
            Object[] result = new Object[0];
            for (int i = 0; i < objlist.size(); i++) {
                result = new Object[1];// 数组大小应和你定义的数据库对象(AOBJECT)的属性的个数
                result[0] = new Long(objlist.get(i).longValue()); // 将list中元素的数据传入result数组
                structs[i] = new STRUCT(structdesc, con, result);
            }
            ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
                    con);
            list = new ARRAY(desc, con, structs);
        } // if
        return list;
    } 

---merge into  9i新特性 更新表，以及刷存量数据是效率最高
MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
           col2 = col_val2 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values);

NLS_INITCAP(x[,y])
【功能】返回字符串并将字符串的第一个字母变为大写，其它字母小写;
【参数】x字符型表达式
【参数】Nls_param可选，
查询数据级的NLS设置:select * from nls_database_parameters;

例如：
指定排序的方式(nls_sort=) 。
nls_sort=SCHINESE_RADICAL_M（部首、笔画）
nls_sort=SCHINESE_STROKE_M（笔画、部首SCHINESE_PINYIN_M（拼音））

【返回】字符型


【示例】
 select nls_initcap('ab cde') "test",
nls_initcap('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;
返回：Ab Cde, A C B D E


 select nls_initcap('ab cde') "test",
nls_initcap('a c b d e','NLS_LANGUAGE=AMERICAN') "test1" from dual;