分享最近写的几个存储过程

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;