sql变量:PL/SQL在from后面使用变量 以及输入' &字来源: 发布时间:星期四, 2008年12月18日 浏览:2次 评论:0
="t18">
在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 ) ; 里 ![]() DEFINE_COLUMN是用作定义数据类型 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() -- 另外 ![]() 联编变量是 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ...... 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> ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 举例介绍说明: --------------------------------------------------------------- SQL> CREATE TABLE TEST3 ( ID NUMBER (2) PRIMARY KEY, NAME VARCHAR2 (20)); SQL> show ![]() ![]() SQL> insert ![]() Enter value for a: abc Enter value for n: 456 old 1: insert ![]() ![]() ![]() ![]() ![]() ![]() 1 row created. SQL> commit; Commit complete. SQL> select * from test3; ID NAME -- -------------------- 1 sgsabc456 2 sgs&a&n 0
相关文章
读者评论
发表评论 |