ITPub博客

首页 > 数据库 > 数据库开发技术 > 动态sql的应用:在from后面使用变量

动态sql的应用:在from后面使用变量

原创 数据库开发技术 作者:darkhorse_dh 时间:2005-07-19 17:01:59 0 删除 编辑
介绍如何使用动态sql,比如:表名为变量等.[@more@]

CREATE OR REPLACE FUNCTION GET_TABLE_COUNT( I_TabNa IN VARCHAR2 ,I_Owner IN VARCHAR2 DEFAULT NULL )
RETURN NUMBER
IS
V_RtnVal NUMBER ;
V_CursorId INTEGER ;
V_SqlStr VARCHAR2(300) ;
BEGIN
V_CursorId := DBMS_SQL.OPEN_CURSOR ;

IF LENGTHB( RTRIM( LTRIM( NVL( I_Owner , '' ) ) ) ) = 0 THEN
V_SqlStr := 'SELECT COUNT(*) FROM ' || I_TabNa ;
ELSE
V_SqlStr := 'SELECT COUNT(*) FROM ' || I_Owner|| '.' || I_TabNa ;
END IF ;

DBMS_SQL.PARSE( V_CursorId , V_SqlStr , DBMS_SQL.V7 ) ;

DBMS_SQL.DEFINE_COLUMN( V_CursorId , 1 , 0 ) ;

IF DBMS_SQL.EXECUTE( V_CursorId ) = 0 THEN
NULL ;
END IF ;

IF DBMS_SQL.FETCH_ROWS( V_CursorId ) = 0 THEN
RETURN 0 ;
END IF ;

DBMS_SQL.COLUMN_VALUE( V_CursorId , 1 , V_RtnVal ) ;

DBMS_SQL.CLOSE_CURSOR( V_CursorId ) ;

RETURN V_RtnVal ;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR( V_CursorId ) ;
-- DBMS_OUTPUT.PUT_LINE( V_SqlStr || SQLERRM ) ;
RETURN 0 ;
END GET_TABLE_COUNT;


试验结果:
SQL> select GET_TABLE_COUNT( 'tab' ) from dual ;

GET_TABLE_COUNT('TAB')
----------------------
22

SQL> select GET_TABLE_COUNT( 'spr' , 'testman') from dual ;

GET_TABLE_COUNT('SPR','TESTMAN
------------------------------
15

SQL> select GET_TABLE_COUNT( 'U_Oausr' , 'tm') from dual ;

GET_TABLE_COUNT('U_OAUSR','TM' ------------------------------ 10

SQL>

说明:
-- DBMS_SQL.DEFINE_COLUMN( V_CursorId , 1 , 0 ) ; 里的“0”是什么意思?
DEFINE_COLUMN是用作定义数据类型的,不同的数据类型有不太的定义方式,这里面的“0”通俗点说就是“与‘0’一样的数据类型的意思”,
比如定义长度为200的varchar2型的列的时候,可以简化为这样定义:DBMS_SQL.DEFINE_COLUMN( V_CursorId , 1 , 'tmpStr' , 200 ),
更多的数据类型列的定义请查看oracle的pl/sql文档,里面很全。
-- 另外,怎么用联编变量?
联编变量是一种非常好的传递参数的方式,而且不容易出错。但是既然称之为“联编变量”那就是它只能对Oracle中认为的变量进行联编,
而刚才上面的例子中,表面是不能够作为变量的,因此不可以联编,Oracle中认为出现在逻辑表达式右边的才是变量,
例如可以这样进行联编变量:
......
V_SqlStr := 'SELECT COUNT(*) FROM TAB WHERE TName LIKE :I_Arg0 ' ;

DBMS_SQL.PARSE( V_CursorId , V_SqlStr , DBMS_SQL.V7 ) ;
V_TabName := 'MYTAB' ;
DBMS_SQL.BIND_VARIABLE( V_CursorId , ':I_Arg0' , V_TabName || '%' ) ;
......


8i以后的版本这样写也行
CREATE OR REPLACE FUNCTION GET_TABLE_COUNT(
I_TabNa IN VARCHAR2 ,
I_Owner IN VARCHAR2 DEFAULT NULL
)
RETURN NUMBER
IS
V_RtnVal NUMBER ;
V_TabName VARCHAR2(300) ;
BEGIN
IF LENGTHB( RTRIM( LTRIM( NVL( I_Owner , '' ) ) ) ) = 0 THEN
V_TabName := I_TabNa ;
ELSE
V_TabName := I_Owner|| '.' || I_TabNa ;
END IF ;

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || V_TabName INTO V_RtnVal ;
RETURN V_RtnVal ;

EXCEPTION
WHEN OTHERS THEN
RETURN 0 ;
END GET_TABLE_COUNT ;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14926/viewspace-802555/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 笔记一
请登录后发表评论 登录
全部评论

注册时间:2009-07-19

  • 博文量
    24
  • 访问量
    512210