oracle存储过程之immediate

本文介绍了如何通过在Oracle存储过程中使用Authid Current_User来利用角色权限执行操作,解决了存储过程中因缺少权限而无法执行的问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >



CREATE or REPLACE PROCEDURE UPDATEUSERDATA
Authid Current_User
is
   delsql   varchar2(400);
      BEGIN
         FOR T IN ( SELECT * FROM ORACLEUSER) LOOP
          BEGIN
           delsql := 'delete '|| T.C_USERNAME ||'.sys_group_command where c_group = ''Manager'' and c_command like ''sal.salbiz.market.%''';
            execute immediate  delsql;
           commit;
           END;
         END LOOP;

      END UPDATEUSERDATA;



首先是读取ORACLEUSER表字段C_USERNAME,利用dba的权限去动态更新用户某表或删除、新增数据

Authid Current_User 使用调用者权限,即假如去掉这句、调用存储过程会导致权限不足提示

引用详解:

虽然当前用户执行语句是有权限的,但是放到存储过程中就必须要显式的赋个权限给当前用户。

【IT168 技术文档】我们知道,用户拥有的role权限在存储过程是不可用的。如:  
 
  SQL> select from dba_role_privs where grantee='SUK';
 
  GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
  ------------ ------------ ------------ ------------
  SUK DBA NO YES
  SUK CONNECT NO YES
  SUK RESOURCE NO YES
 
  --用户SUK拥有DBA这个role
 
  --再创建一个测试存储过程:
  create or replace procedure p_create_table  
  is
  begin
  Execute Immediate 'create table create_table(id int)';
  end p_create_table;
 
  --然后测试
  SQL> exec p_create_table;
 
  begin p_create_table; end;
 
  ORA-01031: 权限不足
  ORA-06512: 在"SUK.P_CREATE_TABLE", line 3
  ORA-06512: 在line 1
 
  --可以看到,即使拥有DBA role,也不能创建表。role在存储过程中不可用。
  --遇到这种情况,我们一般需要显式进行系统权限,如grant create table to suk;
  --但这种方法太麻烦,有时候可能需要进行非常多的授权才能执行存储过程
  --实际上,oracle给我们提供了在存储过程中使用role权限的方法:
  --修改存储过程,加入Authid Current_User时存储过程可以使用role权限。
  create or replace procedure p_create_table  
  Authid Current_User is
  begin
  Execute Immediate 'create table create_table(id int)';
  end p_create_table;
 
  --再尝试执行:
  SQL> exec p_create_table;
 
  PL/SQL procedure successfully completed
 
  --已经可以执行了。


thank:http://bbs.youkuaiyun.com/topics/360053754


<think>嗯,用户想了解Oracle存储过程中EXECUTE IMMEDIATE的使用方法和示例。我需要先回忆一下这个命令的基本概念。EXECUTE IMMEDIATE主要用于执行动态SQL,也就是在运行时构建的SQL语句。这在需要灵活拼接SQL的情况下非常有用,比如表名或条件不固定的时候。 首先,我应该解释一下EXECUTE IMMEDIATE的作用,然后分点说明它的常见使用场景。用户可能想知道如何执行DDL、DML语句,如何处理带参数的动态SQL,以及如何获取查询结果。此外,可能还需要提到绑定变量和批量处理的用法,比如BULK COLLECT。 接下来,根据提供的引用内容,引用[3]提到EXECUTE IMMEDIATE用于动态SQL,类似于Java的形参,性能稍弱。引用[2]和[5]给出了示例,比如执行SELECT语句并将结果存入变量,或者使用BULK COLLECT。引用[4]展示了如何执行DDL语句,比如ALTER TABLE。这些例子需要整合到回答中,确保每个示例都正确并附上引用标识。 用户可能遇到的问题包括SQL注入,所以需要强调绑定变量的重要性,如引用[2]中的USING子句。同时,性能方面要注意动态SQL的执行效率,如引用[3]提到的性能稍弱,可能需要优化。 另外,用户可能想知道如何处理异常,比如动态执行时的错误,引用[4]提到了异常处理,但示例中没有错误处理部分,可能需要补充说明使用EXCEPTION块来捕获异常。 最后,生成相关问题时要围绕EXECUTE IMMEDIATE的应用场景、优化、绑定变量、批量处理等方面提问,帮助用户深入理解。</think>### Oracle存储过程中EXECUTE IMMEDIATE的使用方法及示例 #### 1. **基本功能** `EXECUTE IMMEDIATE` 用于在存储过程中执行动态构建的SQL语句,适用于以下场景: - SQL语句结构在运行时才能确定(如动态表名、条件) - 需要执行DDL语句(如创建表、修改表结构) - 动态调用存储过程或函数 #### 2. **语法格式** ```sql EXECUTE IMMEDIATE '动态SQL语句' [INTO {变量1, 变量2... | 记录类型变量}] [USING [IN | OUT | IN OUT] 绑定参数1, 绑定参数2...]; ``` #### 3. **常见使用场景** **场景1:执行DDL语句(需在自治事务中)** ```sql BEGIN EXECUTE IMMEDIATE 'CREATE TABLE temp_table (id NUMBER, name VARCHAR2(50))'; END; ``` > **注意**:DDL语句会隐式提交事务,需谨慎使用[^4]。 **场景2:带参数的DML操作** ```sql DECLARE v_sql VARCHAR2(200); v_deptno NUMBER := 10; BEGIN v_sql := 'UPDATE emp SET sal = sal * 1.1 WHERE deptno = :1'; EXECUTE IMMEDIATE v_sql USING v_deptno; END; ``` 通过`USING`子句绑定参数可防止SQL注入[^2]。 **场景3:返回单行查询结果** ```sql DECLARE v_ename VARCHAR2(100); v_empno NUMBER := 7788; BEGIN EXECUTE IMMEDIATE 'SELECT ename FROM emp WHERE empno = :id' INTO v_ename USING v_empno; END; ``` 必须确保查询仅返回一行数据[^2]。 **场景4:批量查询结果(BULK COLLECT)** ```sql DECLARE TYPE t_emp IS TABLE OF emp%ROWTYPE; v_emps t_emp; BEGIN EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE deptno = :1' BULK COLLECT INTO v_emps USING 20; END; ``` 适合处理多行数据,提高效率[^5]。 #### 4. **特殊用法** **动态调用存储过程**: ```sql BEGIN EXECUTE IMMEDIATE 'BEGIN my_proc(:1); END;' USING 100; END; ``` **动态设置表名**: ```sql DECLARE v_table VARCHAR2(30) := 'employees'; v_count NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table INTO v_count; END; ``` #### 5. **注意事项** 1. 绑定变量必须按顺序对应占位符 2. 字符串拼接需注意SQL注入风险 3. 使用`RETURNING INTO`子句处理DML返回结果: ```sql EXECUTE IMMEDIATE 'DELETE FROM emp WHERE deptno = :1 RETURNING sal INTO :2' USING 10 RETURNING INTO v_total_sal; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值