基础技术:
例子业务功能:
1.根据传入的类型A_TYPE联合查询PROCEDURE_TEST_A表、PROCEDURE_TEST_A_SUB表中的数据,并显示主要内容。
2.根据传入的类型A_TYPE联合查询PROCEDURE_TEST_A表、PROCEDURE_TEST_A_SUB表,并将结果插入PROCEDURE_TEST_B表中。
这里若B_EMAIL字段为空则取传入的默认值。
3.若PROCEDURE_TEST_B表进行了插入操作,则分组统计ASUB_NUMBER字段更新或插入PROCEDURE_TEST_C表中。
里面包含了存储过程常用的大部分操作,包含循环、条件、增改查、参数传入、变量赋值等,话不多说直接上例子:
1.创建所需要的表,并初始化数据
创建4张表,其中3个表需要初始数据,代码如下:
--数据来源表PROCEDURE_TEST_A CREATE TABLE PROCEDURE_TEST_A ( A_ID VARCHAR2(255) NOT NULL, A_USER VARCHAR2(255), A_EMAIL VARCHAR2(255), A_TYPE VARCHAR2(5), CONSTRAINT PROCEDURE_TEST_A PRIMARY KEY (A_ID) ); --数据来源表的子表PROCEDURE_TEST_A_SUB CREATE TABLE PROCEDURE_TEST_A_SUB ( ASUB_ID VARCHAR2(255) NOT NULL, ASUB_NAME VARCHAR2(255), ASUB_NUMBER NUMBER(18,2), ASUB_COMMENT VARCHAR2(2000), A_ID VARCHAR2(255), CONSTRAINT PROCEDURE_TEST_A_SUB PRIMARY KEY (ASUB_ID), CONSTRAINT PROCEDURE_TEST FOREIGN KEY (A_ID) REFERENCES PROCEDURE_TEST_A (A_ID) ); --数据整合后插入表 CREATE TABLE PROCEDURE_TEST_B ( B_ID VARCHAR2(255) NOT NULL, B_USER VARCHAR2(255), B_EMAIL VARCHAR2(255), B_NAME VARCHAR2(255), B_NUMBER NUMBER(18,2), B_COMMENT VARCHAR2(2000), CONSTRAINT PROCEDURE_TEST_B PRIMARY KEY (B_ID) ); --数据整合后更新表 CREATE TABLE PROCEDURE_TEST_C ( C_USER VARCHAR2(255) NOT NULL, C_NUMBER NUMBER(18,2), CONSTRAINT PROCEDURE_TEST_C PRIMARY KEY (C_USER) ); --PROCEDURE_TEST_A初始化 INSERT INTO PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('AID00006', 'system', '', 'T00'); INSERT INTO PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('AID00001', 'popkidorc', 'popkidorc@mail.com', 'T01'); INSERT INTO PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('AID00002', 'csdn', 'csdn@mail.com', 'T01'); INSERT INTO PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('AID00003', 'pop', '', 'T01'); INSERT INTO PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('AID00004', 'kid', 'kid@mail.com', 'T01'); INSERT INTO PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('AID00005', 'orc', 'orc@mail.com', 'T01'); --PROCEDURE_TEST_A_SUB初始化 INSERT INTO PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('ASUBID00001', 'oralce_blog', 9.90, 'oralce博客', 'AID00001'); INSERT INTO PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('ASUBID00002', 'sql_blog', 1.50, 'sql博客', 'AID00001'); INSERT INTO PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('ASUBID00003', 'swift_blog', 1.00, 'swift博客', 'AID00001'); INSERT INTO PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('ASUBID00004', 'game_blog', 6.00, 'game博客', 'AID00003'); INSERT INTO PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('ASUBID00005', 'sport_blog', 5.55, 'sport博客', 'AID00003'); INSERT INTO PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('ASUBID00006', 'kid_blog', 99.00, 'kid博客', 'AID00004'); INSERT INTO PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('ASUBID00007', 'zero_blog', 0.00, 'zero博客', 'AID00005'); INSERT INTO PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('ASUBID00008', 'large_blog', 100000.00, 'large博客', 'AID00005'); --PROCEDURE_TEST_C初始化 INSERT INTO PROCEDURE_TEST_C (C_USER, C_NUMBER) VALUES ('popkidorc', 9.90);
执行后,表结构及数据结果如图:
2.创建存储过程
代码如下,注释非常详细,直接copy就可慢慢看:
CREATE OR REPLACE PROCEDURE PROCEDURE_TEST(I_A_TYPE VARCHAR2, I_DEFAULT_MAIL VARCHAR2) IS --声明变量 start-- L_DEFAULT_MAIL VARCHAR2(255) := 'default@mail.com'; --声明变量,并赋值;若第二个输入参数不为空,则取该值作为MAIL字段的默认值 L_TEST_B_COUNT INTEGER; --B表更新后影响记录数 L_TEST_C_COUNT INTEGER; --C表更新后影响记录数 CURSOR A_CURSOR IS SELECT A.A_USER, S.ASUB_NUMBER FROM PROCEDURE_TEST_A_SUB S LEFT JOIN PROCEDURE_TEST_A A ON A.A_ID = S.A_ID WHERE A.A_TYPE = I_A_TYPE; --游标对象,用来储存结果集 --声明变量 end-- BEGIN --循环显示A表中数据 start-- FOR A_C IN A_CURSOR LOOP DBMS_OUTPUT.PUT_LINE('===LOOP PROCEDURE_TEST_A===' || A_C.A_USER || '===' || A_C.ASUB_NUMBER); END LOOP; --这里用的是FOR IN循环,WHILE循环也比较常用,可以去查一下 --循环显示A表中数据 end-- --查询A、A_SUB表,并插入B表 start-- INSERT INTO PROCEDURE_TEST_B (B_ID, B_USER, B_EMAIL, B_NAME, B_NUMBER, B_COMMENT) SELECT SYS_GUID(), A.A_USER, DECODE(A.A_EMAIL, NULL, DECODE(I_DEFAULT_MAIL, NULL, L_DEFAULT_MAIL, I_DEFAULT_MAIL), A.A_EMAIL), S.ASUB_NAME, S.ASUB_NUMBER, S.ASUB_COMMENT FROM PROCEDURE_TEST_A_SUB S LEFT JOIN PROCEDURE_TEST_A A ON A.A_ID = S.A_ID WHERE A.A_TYPE = I_A_TYPE; L_TEST_B_COUNT := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('===INSERT PROCEDURE_TEST_B ROWCOUNT===' || L_TEST_B_COUNT); --影响的记录数,SQL%ROWCOUNT --查询A、A_SUB表,并插入B表 end-- --更新C表 start-- IF L_TEST_B_COUNT > 0 THEN --先判断若B表有更改才来更新C表 MERGE INTO PROCEDURE_TEST_C C USING (SELECT A.A_USER, SUM(S.ASUB_NUMBER) AS SUM_NUMBER FROM PROCEDURE_TEST_A_SUB S LEFT JOIN PROCEDURE_TEST_A A ON A.A_ID = S.A_ID GROUP BY A.A_USER) A ON (A.A_USER = C.C_USER) WHEN MATCHED THEN UPDATE SET C.C_NUMBER = A.SUM_NUMBER WHEN NOT MATCHED THEN INSERT VALUES (A.A_USER, A.SUM_NUMBER); L_TEST_C_COUNT := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('===UPDATE OR INSERT PROCEDURE_TEST_C ROWCOUNT===' || L_TEST_C_COUNT); --影响的记录数,SQL%ROWCOUNT END IF; --更新C表 end-- --提交事务 start-- COMMIT; --这里慎用,最好不要直接在存储过程中提交,而是使用服务端代码手动提交。 --提交事务 end-- --异常处理 start-- EXCEPTION --很多异常我就不一一写出来了,常见的写两个,其他的用OTHER了 WHEN DUP_VAL_ON_INDEX THEN --违反了唯一性限制。 DBMS_OUTPUT.PUT_LINE('===DUP_VAL_ON_INDEX EXCEPTION==='); RAISE; WHEN NO_DATA_FOUND THEN --SELECT时候未找到数据 DBMS_OUTPUT.PUT_LINE('===NO_DATA_FOUND EXCEPTION==='); RAISE; --终止进程 WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('===OTHERS EXCEPTION==='); RAISE; --异常处理 end-- END PROCEDURE_TEST;
3.执行存储过程
直接执行,代码如下:
BEGIN --执行存储过程 PROCEDURE_TEST('T01', 'test@mail.com'); END;
java通过thin调用,代码如下(关键代码):
Class.forName("数据库驱动包"); Connection conn = DriverManager.getConnection("连接字符串", "用户名", "密码"); CallableStatement proc = null; proc = conn.prepareCall("{ call PROCEDURE_TEST(?,?) }"); proc.setString(1, "T01"); proc.setString(2, "test@mail.com"); proc.execute();
执行结果如下图,DBMS控制台打印的:
两个被更新的表: