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 OR REPLACE PROCEDURE BEACH_DELETE_BOOK_MIAN01 (V_TABNAME IN VARCHAR2 ) AS V_SQL VARCHAR2(100); RN NUMBER(10); CURSOR CUR_BOOK IS SELECT FROM BOOK_MAIN01; V_BOOK BOOK_MAIN01%ROWTYPE; BEGIN OPEN CUR_BOOK; LOOP DBMS_OUTPUT.PUT_LINE(SYSDATE); FETCH CUR_BOOK INTO V_BOOK; EXIT WHEN CUR_BOOK%NOTFOUND; DBMS_OUTPUT.put_line(' BOOK_ID IS 'V_BOOK.BK_ID ' BOOK_NAME IS 'V_BOOK.BK_NAME); END LOOP; CLOSE CUR_BOOK; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('ERROR'); DBMS_LOCK.SLEEP(10); V_SQL=' DELETE FROM ' V_TABNAME' WHERE ROWNUM ' RN; EXECUTE IMMEDIATE V_SQL ; OPEN CUR_BOOK; LOOP DBMS_OUTPUT.PUT_LINE(SYSDATE); FETCH CUR_BOOK INTO V_BOOK; EXIT WHEN CUR_BOOK%NOTFOUND; DBMS_OUTPUT.put_line(' BOOK_ID IS 'V_BOOK.BK_ID ' BOOK_NAME IS 'V_BOOK.BK_NAME); END LOOP; CLOSE CUR_BOOK; END;
------------------------------------------------
--指定表名 指定列名 然后执行查询语句，P_ROWS 接受返回的记录数 CREATE OR REPLACE PROCEDURE BATCH_SELECT(P_SQLCOLS VARCHAR2, --选择列名P_SQLFROM VARCHAR2, --选择表名P_ROWS OUT SYS_REFCURSOR) AS --返回结果集V_SQL VARCHAR2(3000); --接受sql语句P_SQLSELECT VARCHAR2(3000); --过度sql语句BEGIN----拼接SQL查询语句P_SQLSELECT := 'SELECT ' || P_SQLCOLS || ' FROM ' || P_SQLFROM ; --这是过度的sql语句DBMS_OUTPUT.PUT_LINE(P_SQLSELECT); --输出看一下V_SQL := 'SELECT * FROM ( ' || P_SQLSELECT || ')' ; --最终的sql语句DBMS_OUTPUT.PUT_LINE(V_SQL); --输出查询语句OPEN P_ROWS FOR V_SQL ; --执行查询语句END BATCH_SELECT;
转载于:https://www.cnblogs.com/iyoume2008/p/4692815.html
