我想写一个能把任意字符串截去掉最后2位并输出的过程,要用动态SQL才能实现吗?可我用动态SQL也没能搞定啊~~~~ :(
=========================================================================
Create OR REPLACE PROCEDURE TEST(MYSTR IN VARCHAR2) AS
strsql VARCHAR2(200);
len INTEGER;
strvalue VARCHAR2(60);
v_exc INTEGER;
v_rows INTEGER;
v_s_cursor INTEGER;
BEGIN
v_s_cursor := DBMS_SQL.OPEN_CURSOR;
strsql := 'SELECT LENGTH(''' || MYSTR || ''')-2 LEN FROM DUAL';
DBMS_SQL.PARSE(v_s_cursor,strsql,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(v_s_cursor,1,len);
v_exc := DBMS_SQL.EXECUTE(v_s_cursor);
v_rows := DBMS_SQL.FETCH_ROWS(v_s_cursor);
DBMS_SQL.COLUMN_VALUE(v_s_cursor,1,len);
DBMS_SQL.CLOSE_CURSOR(v_s_cursor);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(len)); v_s_cursor := DBMS_SQL.OPEN_CURSOR;
strsql := 'SELECT SUBSTR(''' || MYSTR || ''',1,' || len || ') STR_FLD FROM DUAL';
DBMS_SQL.PARSE(v_s_cursor,strsql,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(v_s_cursor,1,strvalue);
-- 以上这句编译时出错!!!
-- 说什么PLS-003007:有太多的'DEFINE_COLUMN'说明与此次调用相匹配
-- 问题处在哪里啊????????????????????????
v_exc := DBMS_SQL.EXECUTE(v_s_cursor);
v_rows := DBMS_SQL.FETCH_ROWS(v_s_cursor);
DBMS_SQL.COLUMN_VALUE(v_s_cursor,1,strvalue);
DBMS_SQL.CLOSE_CURSOR(v_s_cursor);
DBMS_OUTPUT.PUT_LINE(strvalue);
v_s_cursor := DBMS_SQL.OPEN_CURSOR;
END;
/
=========================================================================
Create OR REPLACE PROCEDURE TEST(MYSTR IN VARCHAR2) AS
strsql VARCHAR2(200);
len INTEGER;
strvalue VARCHAR2(60);
v_exc INTEGER;
v_rows INTEGER;
v_s_cursor INTEGER;
BEGIN
v_s_cursor := DBMS_SQL.OPEN_CURSOR;
strsql := 'SELECT LENGTH(''' || MYSTR || ''')-2 LEN FROM DUAL';
DBMS_SQL.PARSE(v_s_cursor,strsql,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(v_s_cursor,1,len);
v_exc := DBMS_SQL.EXECUTE(v_s_cursor);
v_rows := DBMS_SQL.FETCH_ROWS(v_s_cursor);
DBMS_SQL.COLUMN_VALUE(v_s_cursor,1,len);
DBMS_SQL.CLOSE_CURSOR(v_s_cursor);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(len)); v_s_cursor := DBMS_SQL.OPEN_CURSOR;
strsql := 'SELECT SUBSTR(''' || MYSTR || ''',1,' || len || ') STR_FLD FROM DUAL';
DBMS_SQL.PARSE(v_s_cursor,strsql,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(v_s_cursor,1,strvalue);
-- 以上这句编译时出错!!!
-- 说什么PLS-003007:有太多的'DEFINE_COLUMN'说明与此次调用相匹配
-- 问题处在哪里啊????????????????????????
v_exc := DBMS_SQL.EXECUTE(v_s_cursor);
v_rows := DBMS_SQL.FETCH_ROWS(v_s_cursor);
DBMS_SQL.COLUMN_VALUE(v_s_cursor,1,strvalue);
DBMS_SQL.CLOSE_CURSOR(v_s_cursor);
DBMS_OUTPUT.PUT_LINE(strvalue);
v_s_cursor := DBMS_SQL.OPEN_CURSOR;
END;
/
-----
12345实际:902
08:33:43 SQL>
DBMS_SQL.DEFINE_COLUMN(v_s_cursor,1,strvalue);
注释掉,好象是多余的
substr()和length()