Oracle存储函数-将表名作为参数传递 [英] Oracle stored function - pass table name as parameter

问题描述

我正在尝试在Oracle中创建一个存储函数，该函数将对表行进行计数..我想使表名动态化，因此我将其作为参数传递，存储函数代码如下所示

I'm trying to create a stored function in Oracle that will count the table rows..i want to make the table name dynamic, so i passed it as a parameter, the stored function code looks like this

create type tes_jml_obj is object(jumlah integer);
create type tes_jml_table is table of tes_jml_obj;
create or replace function jumlahBaris(namatabel varchar)
return tes_jml_table
is
  tabel tes_jml_table := tes_jml_table();
begin
  for r in (execute immediate 'select count(*) as jumlah from' || namatabel)
  loop
    tabel.extend;
    tabel(1) := tes_jml_obj(r.jumlah);
  end loop;
  return tabel;
end;

但是当我执行它时，它返回错误.我在这里想念什么吗?这是动态获取表行的正确方法吗?

But when i execute it, it returns errors. Am i missing something here? Is that the correct way to get the table rows dynamically?

推荐答案

1. 您的立即执行将仅返回一个值，即计数，那么有什么要循环?
2. 我也不确定使用隐式游标执行即时工作.
3. 在您的SQL中，from关键字后似乎没有空格.

1. Your execute immediate will return only one value, the count, so what is there to loop over?
2. Also I'm not sure that execute immediate works with an implicit cursor.
3. In your SQL it looks like you don't have a space after the from keyword.

尝试这样的方法:

create or replace function jumlahBaris(namatabel varchar)
return tes_jml_table
is
  tabel tes_jml_table := tes_jml_table();
  the_count integer;
  the_sql varchar(100);
begin
  the_sql := 'select count(*) as jumlah from ' || namatabel;
  execute immediate the_sql INTO the_count;

  if the_count IS NOT NULL THEN
      tabel.extend;
      tabel(1) := tes_jml_obj(the_count);
  end if;
  return tabel;
end;

这篇关于Oracle存储函数-将表名作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！