oracle基础

常用自定义类函数

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个人之后会自动休息。