图书管理系统简单 过程

---用意：首先插入数据，然后执行 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))

转载于:https://www.cnblogs.com/iyoume2008/p/4686620.html

相关资源：图书管理系统源代码(自己写的）