存储过程（sp）的应用 博客分类： 数据库 Oracle

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

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();