如何处理含有动态SQL语句的函数

Question

动态SQL语句的结构，参照下我写的，我的是可以的，
CREATE OR REPLACE FUNCTION ALTER_CONS (I_NAME VARCHAR2,I_BZ INTEGER)
   RETURN INTEGER  
IS
T_NAME VARCHAR2(30);
T_CONS_NAME VARCHAR2(30);
C_SQL  NUMBER;
S_SQL  VARCHAR2(500); --ALTER TABLE T_NAME DISABLE CONSTRAINT T_CONS_NAME;
RET    NUMBER;
P_ERROR INTEGER;
CURSOR C_CONS IS SELECT A.TABLE_NAME,A.CONSTRAINT_NAME FROM USER_CONSTRAINTS A
 WHERE A.R_CONSTRAINT_NAME = (
 SELECT B.CONSTRAINT_NAME FROM USER_CONSTRAINTS B
 WHERE B.TABLE_NAME=UPPER(I_NAME) AND B.CONSTRAINT_TYPE='P');BEGIN
P_ERROR := 0;
C_SQL := DBMS_SQL.OPEN_CURSOR;
OPEN C_CONS;
LOOP
BEGIN
FETCH C_CONS INTO T_NAME,T_CONS_NAME;
EXIT WHEN C_CONS%NOTFOUND;
IF I_BZ = 0 THEN --把所有与 I_NAME 有关的 CONSTRAINT DISABLE
  S_SQL :='ALTER TABLE '||T_NAME||' DISABLE CONSTRAINT '||T_CONS_NAME;
ELSE        --恢复与 I_NAME 有关的 CONSTRAINT ENABLE
  S_SQL :='ALTER TABLE '||T_NAME||' ENABLE CONSTRAINT '||T_CONS_NAME;
END IF;
DBMS_SQL.PARSE( C_SQL, S_SQL, DBMS_SQL.v7 ) ;
ret := DBMS_SQL.EXECUTE( C_SQL ) ;
EXCEPTION
          WHEN OTHERS THEN
                 P_ERROR := P_ERROR + 1; 
END;
END LOOP;
CLOSE C_CONS;
DBMS_SQL.CLOSE_CURSOR(C_SQL);
RETURN P_ERROR;
END ALTER_CONS;
/
SHOW ERROR

Answer 1

1    create or replace function getCount(tablename in varchar2,n in varchar2)
  2           return number
  3    is
  4         resultvalue NUMBER(8,2);
  5         l_str varchar2(1000);
  6         cursor_i INTEGER;
  7         cursor_ret INTEGER;
  8    begin
  9        cursor_i:=dbms_sql.open_cursor;
 10        l_str:='select count(*) from '||tablename||' where sal >= '||n;   -- 注意这里我用了emp表的sal 
 11        DBMS_SQL.PARSE(cursor_i,l_str,DBMS_SQL.NATIVE);
 12        cursor_ret := dbms_sql.execute(cursor_i);
 13        dbms_sql.define_column(cursor_i, 1, resultvalue);
 14        if dbms_sql.fetch_rows(cursor_i) > 0 then
 15              dbms_sql.column_value(cursor_i, 1, resultvalue);
 16        end if;
 17        dbms_sql.close_cursor(cursor_i);
 18        return resultvalue;
 19*  end;
SQL> /Function created.SQL> select getcount('emp',3000) from dual;GETCOUNT('EMP',3000)
--------------------
                   3SQL>

Answer 2

To Onejune4450(中文字符)：
   你是在Oracle8.0.5上执行通过的吗？现在看起来，似乎是Oracle版本的问题，我试了一下，还是老问题：（SQL> create or replace function getCount(tablename in varchar2,n in varchar2)
  2  return number
  3  is
  4  resultvalue NUMBER(8,2);
  5  l_str varchar2(1000);
  6  cursor_i INTEGER;
  7  cursor_ret INTEGER;
  8  begin
  9   cursor_i:=dbms_sql.open_cursor;
 10   l_str:='select count(*) from '||tablename||' where data >= '||n;--这儿改了
 11   DBMS_SQL.PARSE(cursor_i,l_str,DBMS_SQL.NATIVE);
 12   cursor_ret := dbms_sql.execute(cursor_i);
 13   dbms_sql.define_column(cursor_i, 1, resultvalue);
 14   if dbms_sql.fetch_rows(cursor_i) > 0 then
 15   dbms_sql.column_value(cursor_i, 1, resultvalue);
 16   end if;
 17   dbms_sql.close_cursor(cursor_i);
 18   return resultvalue;
 19  end;
 20  /函数已创建。SQL> select getCount('zltxsh001','10') from dual;
select getCount('zltxsh001','10') from dual
       *
错误位于第1行：
ORA-06571: 函数GETCOUNT不能保证不更新数据库

Answer 3

你需要了解动态SQL的机制，这是我帮你改好的函数，请参考一下
我测试通过了，
 create or replace function getCount(tablename  varchar2,n varchar2)
  return integer
  is
  resultvalue integer;
  l_str varchar2(1000);
  cursor_i INTEGER;
  cursor_ret INTEGER;
  begin
   cursor_i:=dbms_sql.open_cursor;
   l_str:= 'SELECT COUNT(*) FROM '||tablename||' where data>='||n;
DBMS_SQL.PARSE( cursor_i,l_str, DBMS_SQL.v7 ) ;
DBMS_SQL.DEFINE_COLUMN( cursor_i, 1, resultvalue ) ;
cursor_ret := DBMS_SQL.EXECUTE( cursor_i ) ;
LOOP
IF DBMS_SQL.FETCH_ROWS( cursor_i ) = 0 THEN 
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE( cursor_i, 1, resultvalue ) ;
END LOOP;
    dbms_sql.close_cursor(cursor_i);
  return resultvalue;
  end;
/

Answer 4

我的测试环境是817，结果如下：
SQL> ed
Wrote file afiedt.buf
  1  create or replace function getCount(tablename  varchar2,n varchar2)
  2    return integer
  3    is
  4    resultvalue integer;
  5    l_str varchar2(1000);
  6    cursor_i INTEGER;
  7    cursor_ret INTEGER;
  8    begin
  9     cursor_i:=dbms_sql.open_cursor;
 10     l_str:= 'SELECT COUNT(*) FROM '||tablename||' where sbh>='||n; --这是我的表的字段，你的是DATA，其他不变
 11    DBMS_SQL.PARSE( cursor_i,l_str, DBMS_SQL.v7 ) ;
 12    DBMS_SQL.DEFINE_COLUMN( cursor_i, 1, resultvalue ) ;
 13    cursor_ret := DBMS_SQL.EXECUTE( cursor_i ) ;
 14    LOOP
 15     IF DBMS_SQL.FETCH_ROWS( cursor_i ) = 0 THEN
 16      EXIT;
 17     END IF;
 18     DBMS_SQL.COLUMN_VALUE( cursor_i, 1, resultvalue ) ;
 19    END LOOP;
 20      dbms_sql.close_cursor(cursor_i);
 21    return resultvalue;
 22*   end;
SQL> /Function created.SQL> select getcount('cw_rjzb',1) from dual;GETCOUNT('CW_RJZB',1)
---------------------
                64448

Answer 5

Oracle8.0.5及一下版本看来不行