up ~~~~~~~~~~~~~~~~~~ create or replace procedure my_pro is begin create or replace view my_view as select * from my_table; end my_pro;呵呵,谁帮我改一改。谢了。
begin execute immediate 'create or replace procedure my_pro as create or replace view my_view as select * from my_table'; end;
呵呵。 搞定了。附带给大家一个例子: 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.
~~~~~~~~~~~~~~~~~~
create or replace procedure my_pro
is
begin
create or replace view my_view as select * from my_table;
end my_pro;呵呵,谁帮我改一改。谢了。
execute immediate 'create or replace procedure my_pro
as
create or replace view my_view as select * from my_table';
end;
搞定了。附带给大家一个例子:
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.
gxgx
动态参数当然要用动态执行execute了;