常用自定义类函数
1、分割字符串函数
CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (100);---定义一个数组,数组每个元素是 VARCHAR2 (100)类型
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)--定义一个函数
RETURN str_split -----返回一个数组
PIPELINED----将数据进行按流水线方式进行处理,执行无等待,而不是那种传统的将本次数据全处理完了,再送往下一个处理环节。 PIPELINED方式,能让数据无等待,效率非常高,尤其是应用在Oracle的表函数返回
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END splitstr;
oracle数据库自带函数
1、repalce函数----------替换
语法:repalce(str_source,str1,str2),把 str_source 中 str1 字符串替换为 str2 字符串
replace(str_source,str1) 把str_source 中的 str1 字符串删除
2、regexp_replace函数----------替换
语法:regexp_replace(str_source,pattern_str,rep_str) 支持正则表达式,用法类似于 replace,但功能更强大
语法:regexp_replace(str_source,pattern_str) 把 str_source 中的 pattern_str 字符串删除
示例:select regexp_replace('123456qwert', '56q', '*'), regexp_replace('123456qwert', '[13t]', '*'),regexp_replace('123456qwert', '[we1]') from dual;
结果: 1234*wert *2*456qwer* 23456qrt
3、translate函数----------替换
语法:translate(str_source,chr1,chr2) 以字符为单位,把 str_source 中的 chr1 字符对应替换为 chr2。如果 chr1 比chr2 长,那么在 chr1 中而不在 chr2 中的字符将被剔除,因为没有对应的替换字符。需注意 chr2 不能为 null 或'',否则返回值也为空
示例:select translate('123456qwert','qwe','#$%'),translate('123456qwert','qwe','&*'),translate('123456qwert','#qwe','#') from dual;
结果:123456#$%rt 123456&*rt 123456rt
说明:结果三中删除掉字符'a'、'b'、'c',translate 有 # 的特殊用法,以 # 开头的表示所有字符。
4、wm_concat函数-------拼接不同行数据
测试数据:create table usr(id int,name varchar2(32) )
insert into usr values(1,'张三')
insert into usr values(2,'李四')
insert into usr values(3,'王五')
示例:select wm_concat(name) from usr ;
结果:张三,李四,王五
5、length()函数和lengthb(string)函数------获取字符串长度
length(string)计算string所占的字符长度:返回字符串的长度,单位是字符
lengthb(string)计算string所占的字节长度:返回字符串的长度,单位是字节
示例:
select lengthb('汉字') from dual ;-----4--查询汉字在Oracle数据库里占多少字节
select lengthb('qw') from dual ;------2
select length('汉字') from dual ;------2
select length('qw') from dual ;------2
说明:对于单字节字符,LENGTHB和LENGTH是一样的.
如可以用length(‘string’)=lengthb(‘string’)判断字符串是否含有中文。
一个汉字在Oracle数据库里占多少字节跟数据库的字符集有关。
6、instr()函数-----对某个字符串进行判断,判断其是否含有指定的字符。
在一个字符串中查找指定的字符,返回被查找到的指定的字符的位置。
语法:instr(sourceString,destString,start,appearPosition)
sourceString代表源字符串;
destString代表要从源字符串中查找的子串;
start代表查找的开始位置,这个参数可选的,默认为1;
appearPosition代表想从源字符中查找出第几次出现的destString,这个参数也是可选的, 默认为1
如果start的值为负数,则代表从右往左进行查找,但是位置数据仍然从左向右计算。
返回值为:查找到的字符串的位置。
7、substr()函数------截取子字符串
语法:substr(字符串,截取开始位置,截取长度) //返回截取的字
示例:select substr('qwertyuiop',0,1) from dual;--返回结果为 'q' *从字符串第一个字符开始截取长度为1的字符串
select substr('qwertyuiop',1,1) from dual; --返回结果为 'q' *0和1都是表示截取的开始位置为第一个字符
select substr('qwertyuiop',2,4) from dual; --返回结果为 'wert'
select substr('qwertyuiop',-3,3) from dual;--返回结果为 'iop' *负数(-i)表示截取的开始位置为字符串右端向左数第i个字符
8、decode()函数,条件判断
语法:decode(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value 等于if1时,decode函数的结果返回then1,...,如果不等于任何一个if值,则返回else。
select decode(123,1,'1',-1,'2','0') from dual;--0
select decode(sign(80-60),1,'1',-1,'2','0') from dual;--1
select decode(sign(80-90),1,'1',-1,'2','0') from dual;--2
9、sign()函数
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
select sign(22),sign(80-80),sign(-111) from dual;--1 0 -1
10、dbms_output.put_line()------输出打印函数
在oracle函数编辑中,输出需要显示的数据
declare
begin
dbms_output.put_line('Hello World');
end;
结果:Hello World
oracle语法
1、
Oracle LOOP…END LOOP循环是最基本的循环,也称为无条件的循环。在这种类型的循环中如果没有指定EXIT语句,循环将一直运行,即出现死循环。死循环是应该尽量避免的。因此,LOOP循环中必须指定EXIT语句,以便循环停止执行。
示例:declare
i number := 1;
begin
loop
dbms_output.put_line('世界上第'||i||'个人!!!');
i := i + 1;
exit when i > 10;
end loop;
end;
结果:世界上第1个人!!!
世界上第2个人!!!
世界上第3个人!!!
世界上第4个人!!!
世界上第5个人!!!
世界上第6个人!!!
世界上第7个人!!!
世界上第8个人!!!
世界上第9个人!!!
世界上第10个人!!!
上帝在创造10个人之后会自动休息。
示例:declare
i number := 1;
begin
loop
dbms_output.put_line('世界上第'||i||'个人!!!');
i := i + 1;
exit when i > 10;
end loop;
end;
结果:世界上第1个人!!!
世界上第2个人!!!
世界上第3个人!!!
世界上第4个人!!!
世界上第5个人!!!
。。。。。。
世界上第8个人!!!
世界上第9个人!!!
世界上第481个人!!!
上帝在创造481个人之后累死了...。
示例:declare
i number := 1;
begin
while(i<=10)
loop
dbms_output.put_line('世界上第'||i||'个人!!!');
i := i + 1;
end loop;
end;
结果:世界上第1个人!!!
世界上第2个人!!!
世界上第3个人!!!
世界上第4个人!!!
世界上第5个人!!!
世界上第6个人!!!
世界上第7个人!!!
世界上第8个人!!!
世界上第9个人!!!
世界上第10个人!!!
上帝在创造10个人之后会自动休息。