滷蛋尬貢丸: 2013

2013年11月11日星期一

oracle 判斷中英文

SELECT

CASE WHEN (LENGTH(FAC_NAME_IN) = LENGTHB(FAC_NAME_IN)) THEN FAC_NAME_IN END CUST_ENG_NAME ,

CASE WHEN (LENGTH(FAC_NAME_IN) != LENGTHB(FAC_NAME_IN)) THEN FAC_NAME_IN END CUST_CHT_NAME

FROM TABLENAME_IN DTL

oracle 讀取txt檔

Oracle的UTL_FILE用文件的I/O操作。
（1）Oracle10g之前的版本需要指定utl_file包可以操作的目。
方法： 1、alter system set utl_file_dir='e:/utl' scope=spfile;

2、在init.ora文件中，配置如下：UTL_FILE=E:/utl或者UTL_FILE_DIR=E:/utl;
（2）Oracle10g之后的版本，

1.創建目錄：
2	CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
3	2.目錄創建以後，就可以把讀寫權限授予特定用戶，具體語法如下:
4	GRANT READ[,WRITE] ON DIRECTORY directory TO username;

utl_file.fopen(

file_location IN VARCHAR2, --路径

file_name IN VARCHAR2, --文件名称

open_mode IN VARCHAR2, --打开模式 R 读 W 写 A 追加

max_linesize IN BINARY_INTEGER DEFAULT NULL)

RETURN file_type;

DECLARE
v_output utl_file.file_type;
v_contxt VARCHAR2(250);
v_pathna varchar2(10);
BEGIN
v_pathna := 'utl_file_dir';
v_output:= utl_file.fopen(v_pathna, 'test.txt', 'R');
LOOP
BEGIN
utl_file.get_line(v_output, v_contxt);
dbms_output.put_line(v_contxt);
EXCEPTION
WHEN OTHERS THEN
EXIT;
END;
END LOOP;
utl_file.fclose(v_output);
END ;

2013年11月5日星期二

ORACLE 判斷資料中, 有沒有中文字

SELECT * FROM table a WHERE LENGTH (id) != LENGTHB (id);

區別：length 是字串長度，lengthb 是位元組長度

select length('abc中國')
  from dual;
 
-- 結果: 5 碼
--------------------------
 
select lengthb('abc中國')
  from dual;
 
-- 結果: 9 碼 (UTF8 一個中文字 3 碼)
--------------------------
 
select 1
  from dual
 where length('abc中國') = lengthb('abc中國');
 
-- 結果: 0 row (表示字串中有中文字)
--------------------------
 
select 1
  from dual
 where length('abc') = lengthb('abc');
 
-- 結果: 1 row (表示字串中無中文字)

2013年10月31日星期四

ORACLE 找特定字元位置

ORACLE 找特定字元位置類似 JAVA 的 INDEXOF
INSTR('ccrbatch.aaa', '.') 回傳 9

搭配SUBSTR 可以取 . 後面的文字
select subStr('ccrbatch.ABC',INSTR('ccrbatch.ABC', '.')+1) from CTB_CT;
回傳 ABC

2013年10月17日星期四

PL/SQL 令人煩惱的單引號

"跳脫(escaping)"單引號;可用兩個連續的記號來達成

FUNCTION esc(text IN VARCHRAR2)

RETURN VARCHAR2

BEGIN

RETURN '''' || REPLACE(text,'''','''''') || '''' ;

END;

2013年10月14日星期一

JDBC读取新插入Oracle数据库Sequence值的5种方法

//公共代码：得到数据库连接
public Connection getConnection() throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:dbname", "username", "password");
return conn;
}
//方法一
//先用select seq_t1.nextval as id from dual 取到新的sequence值。
//然后将最新的值通过变量传递给插入的语句：insert into t1(id) values(?)
//最后返回开始取到的sequence值。
//这种方法的优点代码简单直观，使用的人也最多，缺点是需要两次sql交互，性能不佳。
public int insertDataReturnKeyByGetNextVal() throws Exception {
Connection conn = getConnection();
String vsql = "select seq_t1.nextval as id from dual";
PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql);
ResultSet rs=pstmt.executeQuery();
rs.next();
int id=rs.getInt(1);
rs.close();
pstmt.close();
vsql="insert into t1(id) values(?)";
pstmt =(PreparedStatement)conn.prepareStatement(vsql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
System.out.print("id:"+id);
return id;
}
//方法二
//先用insert into t1(id) values(seq_t1.nextval)插入数据。
//然后使用select seq_t1.currval as id from dual返回刚才插入的记录生成的sequence值。
//注：seq_t1.currval表示取出当前会话的最后生成的sequence值，由于是用会话隔离，只要保证两个SQL使用同一个Connection即可，对于采用连接池应用需要将两个SQL放在同一个事务内才可保证并发安全。
//另外如果会话没有生成过sequence值，使用seq_t1.currval语法会报错。
//这种方法的优点可以在插入记录后返回sequence，适合于数据插入业务逻辑不好改造的业务代码，缺点是需要两次sql交互，性能不佳，并且容易产生并发安全问题。
public int insertDataReturnKeyByGetCurrVal() throws Exception {
Connection conn = getConnection();
String vsql = "insert into t1(id) values(seq_t1.nextval)";
PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql);
pstmt.executeUpdate();
pstmt.close();
vsql="select seq_t1.currval as id from dual";
pstmt =(PreparedStatement)conn.prepareStatement(vsql);
ResultSet rs=pstmt.executeQuery();
rs.next();
int id=rs.getInt(1);
rs.close();
pstmt.close();
System.out.print("id:"+id);
return id;
}
//方法三
//采用pl/sql的returning into语法，可以用CallableStatement对象设置registerOutParameter取得输出变量的值。
//这种方法的优点是只要一次sql交互，性能较好，缺点是需要采用pl/sql语法，代码不直观，使用较少。
public int insertDataReturnKeyByPlsql() throws Exception {
Connection conn = getConnection();
String vsql = "begin insert into t1(id) values(seq_t1.nextval) returning id into :1;end;";
CallableStatement cstmt =(CallableStatement)conn.prepareCall ( vsql);
cstmt.registerOutParameter(1, Types.BIGINT);
cstmt.execute();
int id=cstmt.getInt(1);
System.out.print("id:"+id);
cstmt.close();
return id;
}
//方法四
//采用PreparedStatement的getGeneratedKeys方法
//conn.prepareStatement的第二个参数可以设置GeneratedKeys的字段名列表，变量类型是一个字符串数组
//注：对Oracle数据库这里不能像其它数据库那样用prepareStatement(vsql,Statement.RETURN_GENERATED_KEYS)方法，这种语法是用来取自增类型的数据。
//Oracle没有自增类型，全部采用的是sequence实现，如果传Statement.RETURN_GENERATED_KEYS则返回的是新插入记录的ROWID，并不是我们相要的sequence值。
//这种方法的优点是性能良好，只要一次sql交互，实际上内部也是将sql转换成oracle的returning into的语法，缺点是只有Oracle10g才支持，使用较少。
public int insertDataReturnKeyByGeneratedKeys() throws Exception {
Connection conn = getConnection();
String vsql = "insert into t1(id) values(seq_t1.nextval)";
PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql,new String[]{"ID"});
pstmt.executeUpdate();
ResultSet rs=pstmt.getGeneratedKeys();
rs.next();
int id=rs.getInt(1);
rs.close();
pstmt.close();
System.out.print("id:"+id);
return id;
}
//方法五
//和方法三类似，采用oracle特有的returning into语法，设置输出参数，但是不同的地方是采用OraclePreparedStatement对象，因为jdbc规范里标准的PreparedStatement对象是不能设置输出类型参数。
//最后用getReturnResultSet取到新插入的sequence值，
//这种方法的优点是性能最好，因为只要一次sql交互，oracle9i也支持，缺点是只能使用Oracle jdbc特有的OraclePreparedStatement对象。
public int insertDataReturnKeyByReturnInto() throws Exception {
Connection conn = getConnection();
String vsql = "insert into t1(id) values(seq_t1.nextval) returning id into :1";
OraclePreparedStatement pstmt =(OraclePreparedStatement)conn.prepareStatement(vsql);
pstmt.registerReturnParameter(1, Types.BIGINT);
pstmt.executeUpdate();
ResultSet rs=pstmt.getReturnResultSet();
rs.next();
int id=rs.getInt(1);
rs.close();
pstmt.close();
System.out.print("id:"+id);
return id;
}

方法	简介	优点	缺点
方法一	先用seq.nextval取出值，然后用转入变量的方式插入	代码简单直观，使用的人也最多	需要两次sql交互，性能不佳
方法二	先用seq.nextval直接插入记录，再用seq.currval取出新插入的值	可以在插入记录后返回sequence，适合于数据插入业务逻辑不好改造的业务代码	需要两次sql交互，性能不佳，并且容易产生并发安全问题
方法三	用pl/sql块的returning into语法，用CallableStatement对象设置输出参数取到新插入的值	只要一次sql交互，性能较好	需要采用pl/sql语法，代码不直观，使用较少
方法四	设置PreparedStatement需要返回新值的字段名，然后用getGeneratedKeys取得新插入的值	性能良好，只要一次sql交互	只有Oracle10g才支持，使用较少
方法五	returning into语法，用OraclePreparedStatement对象设置输出参数，再用getReturnResultSet取得新增入的值	性能最好，因为只要一次sql交互，oracle9i也支持	只能使用Oracle jdbc特有的OraclePreparedStatement对象

2013年10月8日星期二

update select 指定筆數

UPDATE ORDER a
SET a.STATUS='02',a.PAY_DATE='',a.CHK_DATE=''
WHERE a.rowid in
(
---先編號完再取編號>=2的資料，不可用rownum >=2，這樣會沒資料被撈出!
select RID
from (
select rownum R,RID
from
(
select rowid as RID
from ORDER
where TEXT_1='010' AND TEXT_2='22T' AND TEXT_3='205'
ORDER BY SN
)
)
where R >=2 ---限定為第2筆(含)以後的資料

2013年10月2日星期三

PL/SQL 範例

String getDBUSERByUserIdSql = "{call " + ParameterValueProvider.getInstance().getDefaultSchema() + ".SP_TFB_CUST_INFO_CRT(?,?,?,?)}";
callableStatement = conn.prepareCall(getDBUSERByUserIdSql);
callableStatement.setString(1, session_date);
callableStatement.setInt(2,1);
callableStatement.setInt(3,200);
callableStatement.setInt(4,0 );
callableStatement.executeUpdate();
return start = start + eveone;
===================================================================
create or replace
PROCEDURE SP_TFB_CUST_INFO_CRT(
SESSION_DATE_IN CIF_ORG_CUST_INFO.SESSION_DATE%TYPE ,
START_ROW_IN INTEGER,
END_ROW_IN INTEGER,
IS_FIRST_IN INTEGER
)
AS
StoO_error INTEGER;
StoO_rowcnt INTEGER;

/*========== variable ==========*/
ORG_SEQ INTEGER;
CUST_SEQ INTEGER;
newOrgSeq INTEGER;
CRT_CUST_CHT_NAME VARCHAR2(255);
CRT_CUST_ENG_NAME VARCHAR2(255);
CUSTOMER_ID VARCHAR2(255);
FB_ID VARCHAR2(255);
--SESSION_DATE_IN VARCHAR2(255):= '201303';
CUST_ID VARCHAR2(255);
START_ROW INTEGER := START_ROW_IN;
END_ROW INTEGER := END_ROW_IN;
IS_FIRST INTEGER := IS_FIRST_IN;

/*========================CURSOR=================================*/
CURSOR c2 IS
SELECT ROWNUM,TEMP.* FROM(
SELECT distinct CUSTOMER_ID,CUST_CHT_NAME,CUST_ENG_NAME,ORG_BU_CODE,SESSION_DATE,FB_ID FROM CCR.DPS_CRT_TXN_DTL x
WHERE NOT exists (
SELECT 1 from CCR.CIF_ORG_CUST_INFO
WHERE
NVL(CUST_CHT_NAME,' ') = NVL(x.CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(x.CUST_ENG_NAME,' ')
AND CUST_ID= x.CUSTOMER_ID
AND bu_code = 'FB002' AND session_date = SESSION_DATE_IN)
AND x.session_date = SESSION_DATE_IN and org_bu_code = 'FB002'
AND NVL(x.CUST_CHT_NAME||x.CUST_ENG_NAME,' ') <> ' '
and NVL(x.CUSTOMER_ID,' ') <> ' '
ORDER BY CUSTOMER_ID ) TEMP
WHERE ROWNUM BETWEEN START_ROW AND END_ROW;

CURSOR c1 IS
SELECT * FROM(
SELECT ROWNUM R,TEMP.* FROM(
SELECT distinct CUSTOMER_ID,CUST_CHT_NAME,CUST_ENG_NAME,ORG_BU_CODE,SESSION_DATE,FB_ID FROM CCR.DPS_CRT_TXN_DTL x
WHERE exists (
SELECT 1 from CCR.CIF_ORG_CUST_INFO
WHERE
NVL(CUST_CHT_NAME,' ') = NVL(x.CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(x.CUST_ENG_NAME,' ')
AND CUST_ID= x.CUSTOMER_ID
AND bu_code = 'FB002' AND session_date = SESSION_DATE_IN)
AND x.session_date = SESSION_DATE_IN and org_bu_code = 'FB002'
ORDER BY CUSTOMER_ID ) TEMP
) WHERE R >= START_ROW AND R <= END_ROW
;

r1 c1%rowtype;
r2 c2%rowtype;

/*======================== FUNCTION ==================================================*/
FUNCTION get_org_seq(CUSTOMER_ID IN VARCHAR2)
return INTEGER
is
ORG_SEQ_OUT CIF_ORG_CUST_INFO.CIF_ORG_CUST_INFO_SEQ%TYPE;
begin
BEGIN
SELECT CIF_ORG_CUST_INFO_SEQ into ORG_SEQ_OUT FROM CIF_ORG_CUST_INFO
WHERE CUST_ID = CUSTOMER_ID
AND NVL(CUST_CHT_NAME,' ') = NVL(CRT_CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(CRT_CUST_ENG_NAME,' ')
AND BU_CODE = 'FB002'
AND SESSION_DATE = SESSION_DATE_IN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;
RETURN ORG_SEQ_OUT;
end;
----------------------------------------------------------------------------------
PROCEDURE updateCRT
(
ORG_SEQ in NUMBER,
CUSTOMER_ID_IN IN VARCHAR2
)
is
BEGIN

UPDATE DPS_CRT_TXN_DTL SET CIF_ORG_CUST_INFO_SEQ = ORG_SEQ
WHERE CUSTOMER_ID = CUSTOMER_ID_IN
AND NVL(CUST_CHT_NAME,' ') = NVL(CRT_CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(CRT_CUST_ENG_NAME,' ')
AND BU_CODE = 'FB002'
AND SESSION_DATE = SESSION_DATE_IN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;
----------------------------------------------------------------------------------------------------------
PROCEDURE updateORG
(
CUST_SEQ_IN in varchar2,
CUSTOMER_ID_IN IN VARCHAR2
)
is
BEGIN

UPDATE CIF_ORG_CUST_INFO SET CIF_CUST_INFO_SEQ = CUST_SEQ_IN
WHERE CUST_ID = CUSTOMER_ID_IN
AND NVL(CUST_CHT_NAME,' ') = NVL(CRT_CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(CRT_CUST_ENG_NAME,' ')
AND BU_CODE = 'FB002'
AND SESSION_DATE = SESSION_DATE_IN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;
---------------------------------------------------------------------------------------------
FUNCTION get_cust_seq
(
FB_ID_IN in varchar2
)
RETURN INTEGER
is
CUST_SEQ_IN CIF_CUST_INFO.CIF_CUST_INFO_SEQ%TYPE;
begin
BEGIN
SELECT CIF_CUST_INFO_SEQ into CUST_SEQ_IN FROM CIF_CUST_INFO
WHERE FB_ID = FB_ID_IN
AND SESSION_DATE = SESSION_DATE_IN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;
return CUST_SEQ_IN;
end;
----------------------------------------------------------------------------------------------
PROCEDURE InsertOrg(
CRT_CUST_CHT_NAME IN varchar2,
CUST_ENG_NAME IN varchar2
)
is
begin
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
INSERT INTO ccr.CIF_ORG_CUST_INFO ( CIF_ORG_CUST_INFO_SEQ ,CIF_CUST_INFO_SEQ,CUST_ID,CUST_CHT_NAME, CUST_ENG_NAME, SESSION_DATE,BU_CODE,AGENT_ID,DATA_SOURCE )
VALUES
( newOrgSeq,CUST_SEQ,CUSTOMER_ID,CRT_CUST_CHT_NAME,CUST_ENG_NAME,SESSION_DATE_IN,'FB002','AUTO','TA01');
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
raise_application_error( SQLCODE, SQLERRM, true );
END;
end;
---------------------------------------------------------------------------------------------------
PROCEDURE get_Auto_Org_SEQ(
newOrgSeq OUT INTEGER
)
is
begin
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;

select CIF_ORG_CUST_INFO_SEQ.nextval INTO newOrgSeq from dual;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;
end;
----------------------------------------------------------------------------------------------

------------------------------- Main (BEGIN)--------------------------------------------------

BEGIN
IF IS_FIRST = 0 THEN
/**--重複**/
OPEN c1;
LOOP
FETCH c1 INTO r1;
EXIT WHEN c1%NOTFOUND;
CRT_CUST_CHT_NAME:= r1.CUST_CHT_NAME;
CRT_CUST_ENG_NAME:= r1.CUST_ENG_NAME;
CUSTOMER_ID:= r1.CUSTOMER_ID;
FB_ID:= r1.FB_ID;
--拿ORG_SEQ
ORG_SEQ := get_org_seq(CUSTOMER_ID);
--DBMS_OUTPUT.PUT_LINE(ORG_SEQ||'----');
--更新來源
updateCRT(ORG_SEQ,CUSTOMER_ID);
CUST_SEQ := get_cust_seq(FB_ID);
--更新org
updateORG(CUST_SEQ,CUSTOMER_ID);
END LOOP ;
CLOSE c1;

ELSE

/**--不重複**/
OPEN c2;
LOOP
FETCH c2 INTO r2;
EXIT WHEN c2%NOTFOUND;
CRT_CUST_CHT_NAME:= r2.CUST_CHT_NAME;
CRT_CUST_ENG_NAME:= r2.CUST_ENG_NAME;
CUSTOMER_ID:= r2.CUSTOMER_ID;
FB_ID:= r2.FB_ID;
--取INSERT SEQ
get_Auto_Org_SEQ(newOrgSeq);
--更新org
CUST_SEQ := get_cust_seq(FB_ID);
--新增
InsertOrg(CRT_CUST_CHT_NAME,CRT_CUST_ENG_NAME);
--更新來源
updateCRT(newOrgSeq,CUSTOMER_ID);
-- DBMS_OUTPUT.PUT_LINE(CRT_CUST_CHT_NAME||'----'||CRT_CUST_ENG_NAME||'----'||CUST_SEQ||'--'||FB_ID);
END LOOP ;
CLOSE c2;
END IF;

exception
when others then
RAISE_APPLICATION_ERROR( SQLCODE, SQLERRM, TRUE );
---==============================================================================---
END SP_TFB_CUST_INFO_CRT;

CURSOR c1 IS
SELECT * FROM(
SELECT ROWNUM R,TEMP.* FROM(
SELECT distinct CUSTOMER_ID,CUST_CHT_NAME,CUST_ENG_NAME,ORG_BU_CODE,SESSION_DATE,FB_ID FROM CCR.DPS_CRT_TXN_DTL x
WHERE exists (
SELECT 1 from CCR.CIF_ORG_CUST_INFO
WHERE
NVL(CUST_CHT_NAME,' ') = NVL(x.CUST_CHT_NAME,' ')
AND NVL(CUST_ENG_NAME,' ') = NVL(x.CUST_ENG_NAME,' ')
AND CUST_ID= x.CUSTOMER_ID
AND bu_code = 'FB002' AND session_date = SESSION_DATE_IN)
AND x.session_date = SESSION_DATE_IN and org_bu_code = 'FB002'
ORDER BY CUSTOMER_ID ) TEMP
) WHERE R >= START_ROW AND R <= END_ROW
;

2013年8月4日星期日

JDBC讀取新插入Oracle資料庫Sequence值的5種方法

//公共代码：得到数据库连接

public Connection getConnection() throws Exception{

Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:dbname", "username", "password");

return conn;

}

//方法一

//先用select seq_t1.nextval as id from dual 取到新的sequence值。

//然后将最新的值通过变量传递给插入的语句：insert into t1(id) values(?)

//最后返回开始取到的sequence值。

//这种方法的优点代码简单直观，使用的人也最多，缺点是需要两次sql交互，性能不佳。

public int insertDataReturnKeyByGetNextVal() throws Exception {

Connection conn = getConnection();

String vsql = "select seq_t1.nextval as id from dual";

PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql);

ResultSet rs=pstmt.executeQuery();

rs.next();

int id=rs.getInt(1);

rs.close();

pstmt.close();

vsql="insert into t1(id) values(?)";

pstmt =(PreparedStatement)conn.prepareStatement(vsql);

pstmt.setInt(1, id);

pstmt.executeUpdate();

System.out.print("id:"+id);

return id;

}

//方法二

//先用insert into t1(id) values(seq_t1.nextval)插入数据。

//然后使用select seq_t1.currval as id from dual返回刚才插入的记录生成的sequence值。

//注：seq_t1.currval表示取出当前会话的最后生成的sequence值，由于是用会话隔离，只要保证两个SQL使用同一个Connection即可，对于采用连接池应用需要将两个SQL放在同一个事务内才可保证并发安全。

//另外如果会话没有生成过sequence值，使用seq_t1.currval语法会报错。

//这种方法的优点可以在插入记录后返回sequence，适合于数据插入业务逻辑不好改造的业务代码，缺点是需要两次sql交互，性能不佳，并且容易产生并发安全问题。

public int insertDataReturnKeyByGetCurrVal() throws Exception {

Connection conn = getConnection();

String vsql = "insert into t1(id) values(seq_t1.nextval)";

PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql);

pstmt.executeUpdate();

pstmt.close();

vsql="select seq_t1.currval as id from dual";

pstmt =(PreparedStatement)conn.prepareStatement(vsql);

ResultSet rs=pstmt.executeQuery();

rs.next();

int id=rs.getInt(1);

rs.close();

pstmt.close();

System.out.print("id:"+id);

return id;

}

//方法三

//采用pl/sql的returning into语法，可以用CallableStatement对象设置registerOutParameter取得输出变量的值。

//这种方法的优点是只要一次sql交互，性能较好，缺点是需要采用pl/sql语法，代码不直观，使用较少。

public int insertDataReturnKeyByPlsql() throws Exception {

Connection conn = getConnection();

String vsql = "begin insert into t1(id) values(seq_t1.nextval) returning id into :1;end;";

CallableStatement cstmt =(CallableStatement)conn.prepareCall ( vsql);

cstmt.registerOutParameter(1, Types.BIGINT);

cstmt.execute();

int id=cstmt.getInt(1);

System.out.print("id:"+id);

cstmt.close();

return id;

}

//方法四

//采用PreparedStatement的getGeneratedKeys方法

//conn.prepareStatement的第二个参数可以设置GeneratedKeys的字段名列表，变量类型是一个字符串数组

//注：对Oracle数据库这里不能像其它数据库那样用prepareStatement(vsql,Statement.RETURN_GENERATED_KEYS)方法，这种语法是用来取自增类型的数据。

//Oracle没有自增类型，全部采用的是sequence实现，如果传Statement.RETURN_GENERATED_KEYS则返回的是新插入记录的ROWID，并不是我们相要的sequence值。

//这种方法的优点是性能良好，只要一次sql交互，实际上内部也是将sql转换成oracle的returning into的语法，缺点是只有Oracle10g才支持，使用较少。

public int insertDataReturnKeyByGeneratedKeys() throws Exception {

Connection conn = getConnection();

String vsql = "insert into t1(id) values(seq_t1.nextval)";

PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql,new String[]{"ID"});

pstmt.executeUpdate();

ResultSet rs=pstmt.getGeneratedKeys();

rs.next();

int id=rs.getInt(1);

rs.close();

pstmt.close();

System.out.print("id:"+id);

return id;

}

//方法五

//和方法三类似，采用oracle特有的returning into语法，设置输出参数，但是不同的地方是采用OraclePreparedStatement对象，因为jdbc规范里标准的PreparedStatement对象是不能设置输出类型参数。

//最后用getReturnResultSet取到新插入的sequence值，

//这种方法的优点是性能最好，因为只要一次sql交互，oracle9i也支持，缺点是只能使用Oracle jdbc特有的OraclePreparedStatement对象。

public int insertDataReturnKeyByReturnInto() throws Exception {

Connection conn = getConnection();

String vsql = "insert into t1(id) values(seq_t1.nextval) returning id into :1";

OraclePreparedStatement pstmt =(OraclePreparedStatement)conn.prepareStatement(vsql);

pstmt.registerReturnParameter(1, Types.BIGINT);

pstmt.executeUpdate();

ResultSet rs=pstmt.getReturnResultSet();

rs.next();

int id=rs.getInt(1);

rs.close();

pstmt.close();

System.out.print("id:"+id);

return id;

}

方法	简介	优点	缺点
方法一	先用seq.nextval取出值，然后用转入变量的方式插入	代码简单直观，使用的人也最多	需要两次sql交互，性能不佳
方法二	先用seq.nextval直接插入记录，再用seq.currval取出新插入的值	可以在插入记录后返回sequence，适合于数据插入业务逻辑不好改造的业务代码	需要两次sql交互，性能不佳，并且容易产生并发安全问题
方法三	用pl/sql块的returning into语法，用CallableStatement对象设置输出参数取到新插入的值	只要一次sql交互，性能较好	需要采用pl/sql语法，代码不直观，使用较少
方法四	设置PreparedStatement需要返回新值的字段名，然后用getGeneratedKeys取得新插入的值	性能良好，只要一次sql交互	只有Oracle10g才支持，使用较少
方法五	returning into语法，用OraclePreparedStatement对象设置输出参数，再用getReturnResultSet取得新增入的值	性能最好，因为只要一次sql交互，oracle9i也支持	只能使用Oracle jdbc特有的OraclePreparedStatement对象

訂閱：文章 (Atom)

滷蛋尬貢丸

2013年11月11日星期一

oracle 判斷中英文

oracle 讀取txt檔

2013年11月5日星期二

ORACLE 判斷資料中, 有沒有中文字

2013年10月31日星期四

ORACLE 找特定字元位置

2013年10月17日星期四

PL/SQL 令人煩惱的單引號

2013年10月14日星期一

JDBC读取新插入Oracle数据库Sequence值的5种方法

2013年10月8日星期二

update select 指定筆數

2013年10月2日星期三

PL/SQL 範例

2013年8月4日星期日

JDBC讀取新插入Oracle資料庫Sequence值的5種方法

網誌存檔

我的網誌清單

2013年11月11日 星期一

2013年11月5日 星期二

2013年10月31日 星期四

2013年10月17日 星期四

2013年10月14日 星期一

2013年10月8日 星期二

2013年10月2日 星期三

2013年8月4日 星期日

2013年11月11日星期一

2013年11月5日星期二

2013年10月31日星期四

2013年10月17日星期四

2013年10月14日星期一

2013年10月8日星期二

2013年10月2日星期三

2013年8月4日星期日