谁给举个简单的例子提示一下。另外,存储过程和函数要如何取舍?感觉这两个差不多。
重点是标题的问题,各位不要偏题了。
重点是标题的问题,各位不要偏题了。
解决方案 »
- 问一个字符集很技术的问题,希望高手过来帮解答一下
- 求一SQL语句,急!!!
- 這條語句如何改?
- 为什么我的审计在SYS.AUD$视图中,看不到审计记录呢?在初始化(init.ora)文件中,参数audit_trail设为true设为DB都不行请赐教?
- WINXP下10g客户端连接linux 10g服务端问题及如何plsqldev如何连接linux 10g服务端问题!
- 如何在Oracle9i中创建schema
- 在oracle数据库中,传入2013-11-11和2013-11-15,怎么样能在一列中显示,如图
- 请教Oracle8i安装失败后的解决方法
- 安装 oracle9i过程中怎么不能创建数据库,安装失败?
- ★★★100分拜会各位老大,顺便问几个问题:如何设置数据库用户名和Oracle Enterprise Manager 管理员名?我的EMCA怎么也连接不上,就是
- 在2个机器上可以装 两个 Oracle 的 RAC 集群吗?
- 新手问题
DROP TABLE t1;
CREATE TABLE t1 (a INT,b INT);
CREATE OR REPLACE TYPE myTable AS TABLE OF INTEGER;
CREATE OR REPLACE FUNCTION MYFUN(P_T MYTABLE) RETURN INTEGER IS
L_R INT;
BEGIN
FOR I IN 1..P_T.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(P_T(I));
INSERT INTO T1 VALUES (P_T(I), P_T(I));
END LOOP;
COMMIT;
SELECT COUNT(A) INTO L_R FROM T1;
RETURN(L_R);
END;
/DECLARE
L_M1 MYTABLE := MYTABLE(1, 2, 3, 4);
L_M2 MYTABLE := MYTABLE(6,7);
L_I INT;
BEGIN
L_I := MYFUN(L_M1);
dbms_output.put_line('t1表中数据的总量是:'||l_i);
L_I := MYFUN(L_M2);
dbms_output.put_line('t1表中数据的总量是:'||l_i);
END;
/---------
输出:
1
2
3
4
t1表中数据的总量是:4
6
7
t1表中数据的总量是:6
函数和过程的区别就是返回值的方法不同,并且procedure可以不返回值,函数必须返回值DROP TABLE t1;
CREATE TABLE t1 (a INT,b INT);
CREATE OR REPLACE TYPE myTable AS TABLE OF INTEGER;
CREATE OR REPLACE PROCEDURE MYpro(P_T IN MYTABLE,p_return OUT INT) IS
BEGIN
FOR I IN 1..P_T.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(P_T(I));
INSERT INTO T1 VALUES (P_T(I), P_T(I));
END LOOP;
COMMIT;
SELECT COUNT(A) INTO p_return FROM T1;
END;
/DECLARE
L_M1 MYTABLE := MYTABLE(1, 2, 3, 4);
L_M2 MYTABLE := MYTABLE(6, 7);
L_I INT;
BEGIN
MYPRO(L_M1, L_I);
DBMS_OUTPUT.PUT_LINE('t1表中数据的总量是:' || L_I);
MYPRO(L_M2, L_I);
DBMS_OUTPUT.PUT_LINE('t1表中数据的总量是:' || L_I);
END;
/---------
输出:
1
2
3
4
t1表中数据的总量是:4
6
7
t1表中数据的总量是:6
type varraytype is table of varchar2(100) index by BINARY_INTEGER;
procedure test(varray varraytype);
end mypack;create or replace package body mypack is
procedure test(varray varraytype) is
begin
--sql代码
end test;
end mypack;
比如前段时间帮JAVA组的GUYS同事写了输入参数为数组的存储过程。
具体需求:实现了一个输入数值->update记录->返回游标的存储过程,这些过程很简单。但当时就考虑到了一个问题,因为有在JAVA代码中调用这个存储过程之前先要从一个XLS文件中读取数据然后用INSERT插入ORACLE数据库表中。但发现在JAVA代码实现的方式是一条条记录地INSERT进ORACLE表中,当时就提出了这个问题,想在JAVA端传个数组对象进ORACLE存储过程中,然后在ORACLE存储过程代码中分解这个数组的数据并将其插入表中,然后再执行其他的操作。
LZ可以参考一下:
首先创建两个ORACLE数据类型,目的就是为了存储结果集数据(数组等): CREATE OR REPLACE TYPE CDW_OBJECT AS OBJECT(
FISCAL_MONTH VARCHAR2(10),
CUSTOMER_NUMBER VARCHAR2(10),
CUSTOMER_NAME VARCHAR2(50)
); CREATE OR REPLACE TYPE CDW_TABLE AS TABLE OF CDW_OBJECT;表结果及数据:SQL> DESC CDW_AR_SA_EXPOSURE_T;
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
FISCAL_MONTH VARCHAR2(10) Y
CUSTOMER_NUMBER VARCHAR2(10) Y
CUSTOMER_NAME VARCHAR2(50) Y SQL> DESC CDW_AR_SA_EXPOSURE_FACT;
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
FISCAL_MONTH VARCHAR2(10) Y
CUSTOMER_NUMBER VARCHAR2(10) Y
CUSTOMER_NAME VARCHAR2(50) Y
表结构及测试数据代码:
CREATE TABLE CDW_AR_SA_EXPOSURE_FACT (FISCAL_MONTH VARCHAR2(10),CUSTOMER_NUMBER VARCHAR2(10),CUSTOMER_NAME VARCHAR2(50));
INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200708','1001','XIEFENG');
INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200705','1002','MANTISXF');
INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200706','2001','CHENGUOZHENG');
INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200808','3001','XIAOFANG');
INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200809','1056','ZIMING');
INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200810','7701','BOSHI');
INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200811','1821','BOGE');
INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200903','2431','FEIZAI');
INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200811','3301','BOSHI_HAOREN');
INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200812','1921','BOGE_HAOREN');
INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200905','6666','FEIZAI_HAOREN');
COMMIT; 存储过程代码:CREATE OR REPLACE PROCEDURE SP_CARC_UPLOAD_FILE_TEST(OUT_CURSOR OUT SYS_REFCURSOR, V_ARRAY IN CDW_TABLE) AS
-- DECLARE THE VARIABLE AND CURSOR TYPE
V_STEP VARCHAR2(100);
VCOUNTS NUMBER;
BEGIN
V_STEP := 'INSERT RECORDS FROM ARRAY';
FOR I IN 1..V_ARRAY.COUNT LOOP
INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES
(
V_ARRAY(I).FISCAL_MONTH,
V_ARRAY(I).CUSTOMER_NUMBER,
V_ARRAY(I).CUSTOMER_NAME
);
DBMS_OUTPUT.PUT_LINE('FISCAL_MONTH: '||V_ARRAY(I).FISCAL_MONTH);
DBMS_OUTPUT.PUT_LINE('CUSTOMER_NUMBER: '||V_ARRAY(I).CUSTOMER_NUMBER);
DBMS_OUTPUT.PUT_LINE('CUSTOMER_NAME: '||V_ARRAY(I).CUSTOMER_NAME);
IF MOD(I,1000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
V_STEP := 'GET THE INVALID CUSTOMER COUNT';
SELECT COUNT(1)
INTO VCOUNTS
FROM CDW_AR_SA_EXPOSURE_T STG
WHERE NOT EXISTS (SELECT 1
FROM CDW_AR_SA_EXPOSURE_FACT FACT
WHERE STG.CUSTOMER_NUMBER = FACT.CUSTOMER_NUMBER
AND STG.FISCAL_MONTH = FACT.FISCAL_MONTH);
--FETCH CUR_MATCH_USERID INTO RT_CUR_MATCH_USERID;
IF VCOUNTS = 0 THEN
V_STEP := 'UPDATE USER_INFORMATION';
DBMS_OUTPUT.PUT_LINE('UPDATED SQL COUNT: ' || SQL%ROWCOUNT);
OPEN OUT_CURSOR FOR SELECT DUMMY AS CUSTOMER_NUMBER FROM DUAL WHERE ROWNUM < 1;
ELSE
V_STEP := 'RETURN CURSOR WHICH STORED ALL INVILD CUSTOMER_DETAILS';
OPEN OUT_CURSOR FOR
SELECT CUSTOMER_NUMBER
FROM CDW_AR_SA_EXPOSURE_T STG
WHERE NOT EXISTS (SELECT 1
FROM CDW_AR_SA_EXPOSURE_FACT FACT
WHERE STG.CUSTOMER_NUMBER = FACT.CUSTOMER_NUMBER
AND STG.FISCAL_MONTH = FACT.FISCAL_MONTH);
END IF;
V_STEP := 'DELETE DATA FROM TABLE CDW_AR_SA_EXPOSURE_T';
/*DELETE FROM CDW.CDW_AR_SA_EXPOSURE_T;
COMMIT;*/
EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
--EXECUTE IMMEDIATE 'TRUNCATE TABLE CDW_AR_SA_EXPOSURE_T REUSE STORAGE';
DBMS_OUTPUT.PUT_LINE('Error at :' || V_STEP);
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' : ' || SQLERRM);
END;
END SP_CARC_UPLOAD_FILE_TEST;
JAVA 代码:import oracle.sql.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import java.util.*;public class ArrayTest {
static public Connection conn;
static public OracleCallableStatement stmt = null;
public Connection getConnectionDB()throws SQLException, ClassNotFoundException{
Class.forName("oracle.jdbc.driver.OracleDriver"); // B. 创新新数据库连接
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Oraxf", "scott", "tiger");
DatabaseMetaData md = conn.getMetaData();
System.out.println("驱动程序名称与版本:");
System.out.println("------------------------------------------------");
System.out.print(md.getDriverName() + " " + md.getDriverVersion());
return conn;
}
public static void main(String[] my)throws SQLException, ClassNotFoundException{
try{
ArrayTest at = new ArrayTest();
at.getConnectionDB();
ArrayList myArray = new ArrayList();
String[][] values = {
{"200811","3301","BOSHI_HAOREN"},
{"200812","1921","BOGE_HAOREN"},
{"200905","6666","FEIZAI_HAOREN"}
};
myArray.add(values);
ARRAY array = getArray("CDW_OBJECT","CDW_TABLE",myArray);
stmt = (OracleCallableStatement)conn.prepareCall("begin SP_CARC_UPLOAD_FILE_TEST(?,?); end;");
stmt.registerOutParameter(1, OracleTypes.CURSOR,"OUT_CURSOR");
stmt.setArray(2, array);
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);
while(rs.next()){
String CUSTOMER_NUMBER = rs.getString("CUSTOMER_NUMBER");
System.out.println("CUSTOMER_NUMBER: "+CUSTOMER_NUMBER);
}
at.closeConnectionDB();
}
catch(Exception e){
e.printStackTrace();
}
}
public static ARRAY getArray(String OracleObj,String OracleTbl, ArrayList alist) throws Exception{
ARRAY list = null;
if (alist != null && alist.size() > 0){
StructDescriptor structdesc = new StructDescriptor(OracleObj,conn);
Object[] result = null;
Object[] o1 = new Object[]{"200811","3301","BOSHI_HAOREN"};
Object[] o2 = new Object[]{"200812","1921","BOGE_HAOREN"};
Object[] o3 = new Object[]{"200905","6666","FEIZAI_HAOREN"};
STRUCT s1 = new STRUCT(structdesc, conn, o1);
STRUCT s2 = new STRUCT(structdesc, conn, o2);
STRUCT s3 = new STRUCT(structdesc, conn, o3);
STRUCT[] structs = {s1,s2,s3};
ArrayDescriptor arraydesc = new ArrayDescriptor(OracleTbl,conn);
list = new ARRAY(arraydesc,conn,structs);
}
return list;
}
public void closeConnectionDB()throws SQLException{
conn.close();
}
}-- 输出结果:-- JAVA 控制台驱动程序名称与版本:
------------------------------------------------
Oracle JDBC driver 10.1.0.2.0CUSTOMER_NUMBER: 6666
CUSTOMER_NUMBER: 1921
CUSTOMER_NUMBER: 3301
-- 数据库SQLPLUSSQL> SELECT * FROM CDW_AR_SA_EXPOSURE_T;FISCAL_MONTH CUSTOMER_NUMBER CUSTOMER_NAME
------------ --------------- --------------------------------------------------
200811 3301 BOSHI_HAOREN
200812 1921 BOGE_HAOREN
200905 6666 FEIZAI_HAORENSQL>