oracle 创建或替换角色？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1197236/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create or replace role?
提问by Dave Jarvis
How do you create or replace a role (that might or might not exist) in Oracle? For example, the following does not work:
您如何在 Oracle 中创建或替换角色（可能存在也可能不存在）？例如，以下不起作用：
CREATE OR REPLACE ROLE role_name;
GRANT SELECT ON SCM1_VIEW_OBJECT_VW TO role_name;
Any way to do this without PL/SQL?
没有 PL/SQL 有什么方法可以做到这一点？
回答by Dave Jarvis
Solution
解决方案
A combination of the given answers and a pragma control accomplishes this task for Oracle 10g.
给定答案和编译指示控件的组合为 Oracle 10g 完成了此任务。
CREATE OR REPLACE PROCEDURE create_role( role_name IN VARCHAR2 ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE '||role_name;
EXCEPTION
WHEN OTHERS THEN
-- ORA-01921: If The role name exists, ignore the error.
IF SQLCODE <> -01921 THEN
RAISE;
END IF;
END create_role;
Test
测试
This sequence works:
此序列有效：
DROP ROLE role_name;
CREATE ROLE role_name;
CALL create_role( 'role_name' );
CALL create_role( 'role_name' );
The final create role statement fails, as expected:
正如预期的那样，最终的 create role 语句失败：
DROP ROLE role_name;
CALL create_role( 'role_name' );
CREATE ROLE role_name;
回答by Jeffrey Kemp
Best practice is to attempt the creation of the role, then handle the appropriate exception gracefully if it occurs; this means you don't need to run potentially expensive data dictionary queries:
最佳实践是尝试创建角色，然后在发生时优雅地处理相应的异常；这意味着您不需要运行可能昂贵的数据字典查询：
begin
execute immediate 'create role role_name';
exception
when others then
--"ORA-01921: role name 'x' conflicts with another user or role name"
if sqlcode = -01921 then
null;
else
raise;
end if;
end;
And yes, you need PL/SQL to do this - it's the best tool for this job, anyway.
是的，您需要 PL/SQL 来执行此操作 - 无论如何，它是完成这项工作的最佳工具。
回答by jva
DECLARE
v_dummy NUMBER;
BEGIN
SELECT 1
INTO v_dummy
FROM dba_roles
WHERE role = 'MY_ROLE_NAME';
EXCEPTION
WHEN no_data_found THEN
EXECUTE IMMEDIATE 'CREATE ROLE my_role_name';
END;
/
回答by David
There is no syntax for "create or replace" for roles. Not sure of your version of Oracle but this hasn't changed much that I can recall. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6012.htm
角色的“创建或替换”没有语法。不确定您的 Oracle 版本，但我记得这并没有太大变化。http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6012.htm
You can grant select to the role multiple times and it will accept the grant every time provided the role exists.
您可以多次向角色授予选择权，只要角色存在，它就会接受每次授予。
You could do an anonymous block and ignore the execption if the role already exists or something else where you see if the role exists by querying DBA_ROLES.
如果角色已经存在，您可以执行匿名块并忽略执行，或者通过查询 DBA_ROLES 来查看角色是否存在。
