Oracle技术_5分钟会用存储过程_存储过程简单实例

--数据来源表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);