存储过程（sp）的应用

存储赋权
存储逻辑处理有返加值
存储有异常处理

create or replace procedure proc_grant(OWNER VARCHAR2,OWNERAPP VARCHAR2,OWNERREAD VARCHAR2)
AS
V_OBJECT VARCHAR(128);
CURSOR BRANCH01 IS SELECT OBJECT_NAME FROM USER_OBJECTS T WHERE OBJECT_TYPE IN('TABLE','VIEW');
CURSOR BRANCH02 IS SELECT OBJECT_NAME FROM USER_OBJECTS T WHERE OBJECT_TYPE IN('FUNCTION','FROCEDURE','PACKAGE');
CURSOR BRANCH03 IS SELECT OBJECT_NAME FROM USER_OBJECTS T WHERE OBJECT_TYPE IN('SEQUENCE');
BEGIN
V_OBJCET :='';
OPEN BRANCH01;
LOOP
FETCH CUR_BRANCH01
INTO V_OBJECT;
EXIT WHEN CUR_BRANCH01%NOTFOUND;
IF LENGTH(TRIM(OWNERAPP))>0
THEN
EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,DELETE,UPDATE ON '||V_OBJECT||' TO '||OWNERAPP;
EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '||OWNERAPP||'.'||V_OBJECT||' FOR '||OWNER||'.'||V_OBJECT;

END IF;
END LOOP;
CLOSE BRANCH01
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
WHEN OTHERS THEN
INSERT INTO LOG(ECODE,EMESSAGE,EINFO) VALUES(V_ERRCODE,V_ERRMSG,V_INFORMATION);
COMMIT;
RETURN
END proc_grant;



例2：
Connection conn=ConUtil.getConnectio n();
CallableStatement proc;
int res=0;
proc=conn.prepareCall("{call proc1(?)}");
proc.registerOutParameter(1,Types.NUMBERIC);
proc.execute();
res=proc.getInt(1);
con.close();