问题简述: 一般认为声明时这样写V_OLDSTR VARCHAR2(50):='';的结果是V_OLDSTR为'',但是昨天写函数时遇到以下问题:CREATE OR REPLACE FUNCTION F_MYSORT(V_STR IN VARCHAR2) --排序主函数
RETURN VARCHAR2 IS V_OLDSTR VARCHAR2(50):='';
V_NEWSTR VARCHAR2(50):=''; --保存结果的串
V_RIGHT VARCHAR2(50):='';
V_LEFT VARCHAR2(50):='';
V_DATA NUMBER(9):=0; --当前数据
V_POS NUMBER(9):=0; --保存插入位置BEGIN
V_OLDSTR:=V_STR||',';
V_RIGHT:=V_OLDSTR;
V_LEFT:=SUBSTR(V_RIGHT,1,INSTR(V_RIGHT,','));
--V_RIGHT:=LTRIM(V_RIGHT,V_LEFT);
V_RIGHT:=SUBSTR(V_RIGHT,LENGTH(V_LEFT)+1); WHILE LENGTH(V_LEFT)>0 LOOP
V_DATA:=TO_NUMBER(SUBSTR(V_LEFT,1,LENGTH(V_LEFT)-1));
--V_DATA:=TO_NUMBER(RTRIM(V_LEFT,','));
IF V_NEWSTR='' THEN --目标串为空时
V_NEWSTR:=TO_CHAR(V_DATA)||',';
ELSE
V_POS:=F_FINDPOS(V_NEWSTR,V_DATA);
V_NEWSTR:=SUBSTR(V_NEWSTR,1,V_POS)||TO_CHAR(V_DATA)||','||SUBSTR(V_NEWSTR,V_POS+1);
END IF; V_LEFT:=SUBSTR(V_RIGHT,1,INSTR(V_RIGHT,','));
--V_RIGHT:=LTRIM(V_RIGHT,V_LEFT);
V_RIGHT:=SUBSTR(V_RIGHT,LENGTH(V_LEFT)+1);
END LOOP; DBMS_OUTPUT.PUT_LINE(SUBSTR(V_NEWSTR,1,LENGTH(V_NEWSTR)-1));
RETURN SUBSTR(V_NEWSTR,1,LENGTH(V_NEWSTR)-1);
END F_MYSORT;程序会运行出错,刚开始一直找不到原有,后来发现把上面的红色语句改成
V_NEWSTR VARCHAR2(50):='*';
和
IF V_NEWSTR='*' THEN ...
就OK了,很无语,因为我测试时把它改成IF V_NEWSTR='' OR V_NEWSTR=NULL THEN 都不行,一样报错。
高手请告诉我为什么?麻烦了。另外还有个小问题
* select ltrim('Mississippi','Mis') from dual;='ppi'
* select ltrim('63972Tech','123456789') from dual;='Tech'
LTRIM一般思想是去掉左边相同的子串,我想不通上面两句的执行思路?请高手解答。
RETURN VARCHAR2 IS V_OLDSTR VARCHAR2(50):='';
V_NEWSTR VARCHAR2(50):=''; --保存结果的串
V_RIGHT VARCHAR2(50):='';
V_LEFT VARCHAR2(50):='';
V_DATA NUMBER(9):=0; --当前数据
V_POS NUMBER(9):=0; --保存插入位置BEGIN
V_OLDSTR:=V_STR||',';
V_RIGHT:=V_OLDSTR;
V_LEFT:=SUBSTR(V_RIGHT,1,INSTR(V_RIGHT,','));
--V_RIGHT:=LTRIM(V_RIGHT,V_LEFT);
V_RIGHT:=SUBSTR(V_RIGHT,LENGTH(V_LEFT)+1); WHILE LENGTH(V_LEFT)>0 LOOP
V_DATA:=TO_NUMBER(SUBSTR(V_LEFT,1,LENGTH(V_LEFT)-1));
--V_DATA:=TO_NUMBER(RTRIM(V_LEFT,','));
IF V_NEWSTR='' THEN --目标串为空时
V_NEWSTR:=TO_CHAR(V_DATA)||',';
ELSE
V_POS:=F_FINDPOS(V_NEWSTR,V_DATA);
V_NEWSTR:=SUBSTR(V_NEWSTR,1,V_POS)||TO_CHAR(V_DATA)||','||SUBSTR(V_NEWSTR,V_POS+1);
END IF; V_LEFT:=SUBSTR(V_RIGHT,1,INSTR(V_RIGHT,','));
--V_RIGHT:=LTRIM(V_RIGHT,V_LEFT);
V_RIGHT:=SUBSTR(V_RIGHT,LENGTH(V_LEFT)+1);
END LOOP; DBMS_OUTPUT.PUT_LINE(SUBSTR(V_NEWSTR,1,LENGTH(V_NEWSTR)-1));
RETURN SUBSTR(V_NEWSTR,1,LENGTH(V_NEWSTR)-1);
END F_MYSORT;程序会运行出错,刚开始一直找不到原有,后来发现把上面的红色语句改成
V_NEWSTR VARCHAR2(50):='*';
和
IF V_NEWSTR='*' THEN ...
就OK了,很无语,因为我测试时把它改成IF V_NEWSTR='' OR V_NEWSTR=NULL THEN 都不行,一样报错。
高手请告诉我为什么?麻烦了。另外还有个小问题
* select ltrim('Mississippi','Mis') from dual;='ppi'
* select ltrim('63972Tech','123456789') from dual;='Tech'
LTRIM一般思想是去掉左边相同的子串,我想不通上面两句的执行思路?请高手解答。
解决方案 »
- 问个用java往Oracle数据库插值时,字段唯一性的问题
- [oralce 9i]: 用EXP备份数据时,出现“application failed to initialize”的问题
- 如何将存储过程加密?
- 重复数据问题解决方法
- 这么show user为空啊?
- 如何解决不能启动OracleOraHome81TNSListener服务的问题?
- 麻烦看看这句话怎么错了啊?
- oracle 乱码的问题
- 10分的问题之2:请问谁收藏了崂山道士的那篇回复220次关于如何学习数据库的文章?
- 创建dblink查询表报ora-00942 ora-02063
- oracle和.net 的简单问题,请高手帮忙。
- 菜鸟请指教 创建视图问题
V_OLDSTR VARCHAR2(50) := null;
PurposeLTRIM removes from the left end of char all of the characters contained in set. If you do not specify set, it defaults to a single blank. If char is a character literal, then you must enclose it in single quotes. Oracle Database begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.Both char and set can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char.
PL/SQL的初始化是什么原理?赋值''时为什么不可以?
* select ltrim('63972Tech','123456789') from dual;='Tech'
用 replace() * select replace('Mississippi','Mis','') from dual;
* select replace('63972Tech','123456789','') from dual;
name varchar2(20):=null;
begin
if name is null then
dbms_output.put_line('为空');
end if;
end;是可以的