CREATE OR REPLACE Procedure mytest(viewName Varchar2)
Is
V_i INTEGER;
v_sql VARCHAR2(200);
BEGIN
SELECT COUNT(*) INTO v_i
FROM sys.user_objects WHERE object_type= 'VIEW ' AND OBJECT_NAME= 'my_view ';
IF v_i =0 THEN
v_sql:= 'CREATE VIEW ' ¦ ¦viewName ¦ ¦ ' as (select * from my_table) ';
EXECUTE IMMEDIATE v_sql;
END IF;
END;
执行:
execute mytest( 'v_abc ');
如果出现问题:
ORA-01031: 权限不足
ORA-06512: ÔÚ"MYTEST", line 10
ORA-06512: ÔÚline 1
解决如下:
用SYS登陆,显式的给该用户授权,不论你是否已经将DBA授权给该用户。
GRANT CREATE ANY VIEW TO THE_USER;
如果是建表,或者建触发一样:
GRANT CREATE ANY TABLE TO THE_USER;
GRANT CREATE ANY TRIGGER TO THE_USER;
然后运行,就OK了。
SQL >PL/SQL procedure successfully completed.
http://freeze.iteye.com/blog/135857
------------------------------------------------------------------------------------------------------------------------------
例子 table1结构如下 id int name varchar(50) declare @id int declare @name varchar(50) declare cursor1 cursor for --定义游标cursor1 select * from table1 --使用游标的对象(跟据需要填入select文) open cursor1 --打开游标 fetch next from cursor1 into @id,@name --将游标向下移1行,获取的数据放入之前定义的变量@id,@name中 while @@fetch_status=0 --判断是否成功获取数据 begin update table1 set name=name+'1' where id=@id --进行相应处理(跟据需要填入SQL文) fetch next from cursor1 into @id,@name --将游标向下移1行 end close cursor1 --关闭游标 deallocate cursor1