Oracle:如何将过程局部变量用于“立即执行"程序陈述 [英] Oracle: How to use procedure local variables for "EXECUTE IMMEDIATE" statements in procedures

问题描述

我们有一个从以下代码开始的过程:

We have a procedure starting with following code:

CREATE OR REPLACE PROCEDURE get_id
  (id_ IN OUT number, type_ IN number)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  local_id number;
BEGIN
  EXECUTE IMMEDIATE 'SELECT SYS_LOCAL_ID_SERIAL_SEQ.NEXTVAL into :local_id FROM dual';
  ...

现在，如果我执行此操作，则变量"local_id"不会用下一个序列值填充，而是填充为空(尽管序列会加1).如果我将其更改为"... into local_id ..."，则会收到ORA错误1008.这里出了什么问题?

Now if i execute this, the variable "local_id" is not filled with the next sequence value, but with null (although the sequence is raised by 1). If i change this to "... into local_id ..." i get ORA Error 1008 . What is going wrong here?

推荐答案

可以使用USING [OUT][IN]子句将过程中的局部变量绑定到查询占位符:

A local variable from the procedure can be bind to the query placeholder with USING [OUT][IN] clause:

  local_id number;
BEGIN
  EXECUTE IMMEDIATE 
       'SELECT SYS_LOCAL_ID_SERIAL_SEQ.NEXTVAL into :local_id FROM dual'
     USING OUT local_id;

但是对于此查询，您不需要execute immediate，只需执行以下操作:

But for this query you don't need execute immediate, just do:

  local_id number;
BEGIN
  SELECT SYS_LOCAL_ID_SERIAL_SEQ.NEXTVAL into local_id FROM dual;

在Oracle 11g上，您可以使用赋值运算符来做到这一点:

on Oracle 11g you can do it using the assignment operator:

  local_id number;
BEGIN
  local_id := SYS_LOCAL_ID_SERIAL_SEQ.NEXTVAL;

这篇关于Oracle:如何将过程局部变量用于“立即执行"程序陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！