oracle 给每个用户添加表 (游标)
第一次在oracle下用游标,动态sql。
DECLARE v_row rmsys.rmsys_unitaccount%ROWTYPE;
u_USERCODE VARCHAR2(80):='';
strsql varchar2(2000);
strIs varchar2(2000);
strsqldel VARCHAR2(2000);
spbnum NUMBER;
isN NUMBER;
isStr VARCHAR2(2000);
CURSOR curUnit IS SELECT * FROM rmsys_unitaccount;
BEGIN
OPEN curUnit;
FETCH curUnit INTO v_row;
while curUnit%found LOOP
--判断帐套是否存在
strIs:='select COUNT(*) from all_users WHERE username =UPPER( '''||v_row.saccount||''')';
EXECUTE IMMEDIATE strIs INTO spbnum;
IF spbnum > 0 THEN
isStr:='SELECT COUNT(*) FROM dba_tables WHERE owner=UPPER('''||v_row.saccount||''') AND TABLE_NAME=''SYST_UM''';
EXECUTE IMMEDIATE isStr INTO isN;
IF isN>0 THEN --帐套下已存在该表
--删除
strsqldel:='drop table '|| v_row.saccount||'.SYST_UM' ;
EXECUTE IMMEDIATE strsqldel;
dbms_output.put_line('删除:表'||v_row.saccount||'已删除!');
isN:=0;
END IF;
--创建表
strsql:='create table '|| v_row.saccount||'.SYST_UM ( SUNITCODE VARCHAR2(15) not NULL , SUNITNAME VARCHAR2(512) , SMENUCODE VARCHAR2(40) not NULL , SUSERCODE VARCHAR2(40) not NULL , DDATE DATE default sysdate not null, primary key (SMENUCODE, SUSERCODE, SUNITCODE))';
EXECUTE IMMEDIATE strsql;
dbms_output.put_line('创建:已在'||v_row.saccount||'下创建表!');
END IF;
FETCH curUnit INTO v_row;
end loop;
CLOSE curUnit;
END;
posted on 2009-07-02 22:31 YWN's blog 阅读(172) 评论(0) 收藏 举报
浙公网安备 33010602011771号