---用意：首先插入数据，然后执行 commit 。接着检查数据是否存在，如果不存在 报错 NO_DATA_FOUND,重复执行该操作 。如果存在 抽取最大的 rownu 赋值 V_PID，然后输出 V_PID
CREATE OR REPLACE PROCEDURE BEATH_INSERT_BOOK_MAIN(
BK_ISBN IN VARCHAR2,
BK_NAME IN NVARCHAR2,
BK_AUTHOR IN NVARCHAR2,
BK_RCMD IN NUMBER,
BK_TYPE IN NUMBER,
BK_TYPELIST IN VARCHAR2,
BK_KEYWORD IN NVARCHAR2,
BK_REMARK IN NVARCHAR2,
BK_USERID IN NUMBER,
BK_PUBLISHTIME DATE ,
BK_PRESS IN NVARCHAR2,
BK_TREE IN CLOB,
BK_STATUS IN NUMBER,
BK_IMG IN NVARCHAR2,
BK_PRICE IN NUMBER ,
BK_STAR IN NUMBER,
BR_COUNTS IN NUMBER,
RESULTBKID OUT NUMBER) is
V_PID number;
V_CurrentBkid number;
V_COUNT NUMBER;
begin
SELECT MAX(bk_id) INTO V_PID FROM BOOK_MAIN;
DBMS_OUTPUT.PUT_LINE(V_PID);
--获取该表中最大的bk_id
--开始插入数据
V_CURRENTBKID := BOOK_MAIN_SEQ.NEXTVAL;
INSERT INTO BOOK_MAIN VALUES(V_CURRENTBKID,
BK_ISBN,
BK_NAME,
BK_AUTHOR,
BK_RCMD,
BK_TYPE,
BK_TYPELIST,
BK_KEYWORD,
BK_REMARK,
BK_USERID,
SYSDATE,
BK_PUBLISHTIME,
BK_PRESS,
BK_TREE,
BK_STATUS,
BK_IMG,
BK_PRICE,
BK_STAR,
BR_COUNTS);
V_COUNT :=SQL%ROWCOUNT;
DBMS_OUTPUT.put_line('V_COUNT IS '||V_COUNT);
DBMS_OUTPUT.PUT_LINE( '插入数据条数: '||V_COUNT);
SELECT MAX(bk_id) INTO V_PID FROM BOOK_MAIN;
IF V_PID=NULL THEN
DBMS_OUTPUT.put_line('ERROR!');
END IF;
DBMS_OUTPUT.put_line('最大的 bk_id 是'|| V_PID); --输出当前 最大的 bk_id
--判断是否插入成功
RESULTBKID := V_CurrentBkid;--返回主键id
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line( SQLCODE||SQLERRM);
V_COUNT :=SQL%ROWCOUNT;
DBMS_OUTPUT.put_line('V_COUNT IS '||V_COUNT);
DBMS_OUTPUT.PUT_LINE( '插入数据条数: '||V_COUNT);
DBMS_OUTPUT.put_line('最大的 bk_id 是'|| V_PID);
COMMIT;
END BEATH_INSERT_BOOK_MAIN;
基础表
CREATE TABLE "DEV"."BOOK_MAIN"
( "BK_ID" NUMBER(12,0) NOT NULL ENABLE,
"BK_ISBN" VARCHAR2(50),
"BK_NAME" VARCHAR2(100),
"BK_AUTHOR" NVARCHAR2(200),
"BK_RCMD" NUMBER(4,0),
"BK_TYPE" NUMBER(12,0),
"BK_TYPELIST" VARCHAR2(80),
"BK_KEYWORD" NVARCHAR2(80),
"BK_REMARK" NVARCHAR2(1000),
"BK_USERID" NUMBER(12,0),
"BK_TIME" DATE,
"BK_PUBLISHTIME" DATE,
"BK_PRESS" NVARCHAR2(50),
"BK_TREE" CLOB,
"BK_STATUS" NUMBER(4,0),
"BK_IMG" NVARCHAR2(500),
"BK_PRICE" NUMBER(7,2),
"BK_STAR" NUMBER(4,2),
"BR_COUNTS" NUMBER(10,0),
PRIMARY KEY ("BK_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MANAGER" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
LOB ("BK_TREE") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))