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

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;

------------------------------------------------

CREATE OR REPLACE PROCEDURE BATCH_SELECT(
p_Sqlcols Varchar2, --选择列
p_Sqlfrom Varchar2, --表名OR连接查询
p_Outrecordcount Out Int, --返回总记录数
p_counts Out Sys_Refcursor) As
v_Sql Varchar2(3000);
p_Sqlselect Varchar2(3000);

Begin

----拼接SQL查询语句
p_Sqlselect := ‘select ‘ || p_Sqlcols || ‘ from ‘ || p_Sqlfrom ;
Dbms_Output.Put_Line(p_Sqlselect);

v_Sql := ‘SELECT * FROM ( ‘ || p_Sqlselect || ‘) ‘;

--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn

Dbms_Output.Put_Line(v_Sql);

Open p_counts For v_Sql;

exception when others then p_Outrecordcount := -1 ;

END BATCH_SELECT;

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

原文：http://www.cnblogs.com/iyoume2008/p/4692815.html