在from后面使用变量
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 ;
SQL*PLUS环境输入'&字符'的方法
我们知道在SQL*PLUS默认环境里会把'&字符'当成变量来处理.
有些时候我们也需要在SQL>的符号下输入'&字符', 只需要改变SQL*PLUS下一个环境变量define即可.
SQL> set define off;
是把默认的&绑定变量的功能取消, 可以把'&字符'当成普通字符处理
SQL> set define on;
打开&绑定变量的功能, &后面的字符串当变量使用.
SQL> show define;
查看当前SQL*PLUS的define状态
举例说明:
---------------------------------------------------------------
SQL> CREATE TABLE TEST3 (
ID NUMBER (2) PRIMARY KEY,
NAME VARCHAR2 (20));
SQL> show define;
define "&" (hex 26)
SQL> insert into test3 values(1,'sgs&a&n');
Enter value for a: abc
Enter value for n: 456
old 1: insert into test3 values(1,'sgs&a&n')
new 1: insert into test3 values(1,'sgsabc456')
1 row created.
SQL> commit;
Commit complete.
SQL> set define off;
SQL> insert into test3 values(2,'sgs&a&n');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test3;
ID NAME
-- --------------------
1 sgsabc456
2 sgs&a&n