另外多问一句,为什么grant create view to 'BIZDATA1';在下面的procedure中编译不过去 create or replace procedure pro_aaa (vew_name varchar2) as dysql varchar2(800); begin dysql := 'create or replace view aaa_lijm as select a.s_wage_id , SUM(CASE WHEN a.s_item_code = 1001 then a.n_item_value else 0 end) as item1001, SUM(CASE WHEN a.s_item_code = 1002 then a.n_item_value else 0 end) as item1002, SUM(CASE WHEN a.s_item_code = 1003 then a.n_item_value else 0 end) as item1003, SUM(CASE WHEN a.s_item_code = 1004 then a.n_item_value else 0 end) as item1004, SUM(CASE WHEN a.s_item_code = 1005 then a.n_item_value else 0 end) as item1005, SUM(CASE WHEN a.s_item_code = 1006 then a.n_item_value else 0 end) as item1006 from WA_DATA a group by a.s_wage_id'; grant create view to 'BIZDATA1'; execute immediate dysql; end;报告错误: Compilation errors for PROCEDURE BIZDATA1.PRO_AAAError: PLS-00103: 出现符号 "GRANT"在需要下列之一时: begindeclareendexception exitforgotoifloopmodnullpragmaraisereturnselectupdatewhile <an identifier><a double-quoted delimited-identifier> <a bind variable><<closecurrentdeletefetchlockinsertopen rollbacksavepointsetsqlexecutecommitforall <a single-quoted SQL string> Line: 13 Text: grant create view to 'BIZDATA1';Error: Hint: Parameter 'vew_name' is declared but never used in 'pro_aaa' Line: 1 Text: create or replace procedure pro_aaa (vew_name varchar2) as
呵呵,在存储过程里面ddl语句都要以动态sql的方式来执行的 execute immediate 'grant create view to ...'
还是xiaoxiao1984(笨猫儿)提醒的明确,正确的写法如下: create or replace procedure proc_test as ddl_sql varchar2(800); begin --对用户bizdata1授予创建视图的权限; execute immediate 'grant create view to bizdata1'; --创建DDL语句; ddl_sql:='create or replace view aaa_lijm as select a.s_wage_id , SUM(CASE WHEN a.s_item_code = 1001 then a.n_item_value else 0 end) as item1001, SUM(CASE WHEN a.s_item_code = 1002 then a.n_item_value else 0 end) as item1002, SUM(CASE WHEN a.s_item_code = 1003 then a.n_item_value else 0 end) as item1003, SUM(CASE WHEN a.s_item_code = 1004 then a.n_item_value else 0 end) as item1004, SUM(CASE WHEN a.s_item_code = 1005 then a.n_item_value else 0 end) as item1005, SUM(CASE WHEN a.s_item_code = 1006 then a.n_item_value else 0 end) as item1006 from WA_DATA a group by a.s_wage_id'; --执行DDL语句; execute immediate ddl_sql;
--如果意外则返回空; exception when others then null; end ; 执行成功了!感谢xiaoxiao1984(笨猫儿)!
create or replace procedure pro_aaa (vew_name varchar2) as
dysql varchar2(800);
begin
dysql := 'create or replace view aaa_lijm
as select a.s_wage_id ,
SUM(CASE WHEN a.s_item_code = 1001 then a.n_item_value else 0 end) as item1001,
SUM(CASE WHEN a.s_item_code = 1002 then a.n_item_value else 0 end) as item1002,
SUM(CASE WHEN a.s_item_code = 1003 then a.n_item_value else 0 end) as item1003,
SUM(CASE WHEN a.s_item_code = 1004 then a.n_item_value else 0 end) as item1004,
SUM(CASE WHEN a.s_item_code = 1005 then a.n_item_value else 0 end) as item1005,
SUM(CASE WHEN a.s_item_code = 1006 then a.n_item_value else 0 end) as item1006
from WA_DATA a group by a.s_wage_id';
grant create view to 'BIZDATA1';
execute immediate dysql;
end;报告错误:
Compilation errors for PROCEDURE BIZDATA1.PRO_AAAError: PLS-00103: 出现符号 "GRANT"在需要下列之一时:
begindeclareendexception
exitforgotoifloopmodnullpragmaraisereturnselectupdatewhile
<an identifier><a double-quoted delimited-identifier>
<a bind variable><<closecurrentdeletefetchlockinsertopen
rollbacksavepointsetsqlexecutecommitforall
<a single-quoted SQL string>
Line: 13
Text: grant create view to 'BIZDATA1';Error: Hint: Parameter 'vew_name' is declared but never used in 'pro_aaa'
Line: 1
Text: create or replace procedure pro_aaa (vew_name varchar2) as
execute immediate 'grant create view to ...'
create or replace procedure proc_test
as
ddl_sql varchar2(800);
begin
--对用户bizdata1授予创建视图的权限;
execute immediate 'grant create view to bizdata1'; --创建DDL语句;
ddl_sql:='create or replace view aaa_lijm
as select a.s_wage_id ,
SUM(CASE WHEN a.s_item_code = 1001 then a.n_item_value else 0 end) as item1001,
SUM(CASE WHEN a.s_item_code = 1002 then a.n_item_value else 0 end) as item1002,
SUM(CASE WHEN a.s_item_code = 1003 then a.n_item_value else 0 end) as item1003,
SUM(CASE WHEN a.s_item_code = 1004 then a.n_item_value else 0 end) as item1004,
SUM(CASE WHEN a.s_item_code = 1005 then a.n_item_value else 0 end) as item1005,
SUM(CASE WHEN a.s_item_code = 1006 then a.n_item_value else 0 end) as item1006
from WA_DATA a group by a.s_wage_id'; --执行DDL语句;
execute immediate ddl_sql;
--如果意外则返回空;
exception
when others then
null;
end ;
执行成功了!感谢xiaoxiao1984(笨猫儿)!