pl/sql学习(6): 引号/程序调试/列中的字符串合并/正则表达式

有关自治事务的问题： https://www.cnblogs.com/princessd8251/p/4132649.html

我在plsql development学习中遇到的常见问题:

(一) 引号

Oracle 中的单引号与双引号有着完全不同的意义. 单引号用于界定字符串, 双引号用于表示对象名称,,,三个单引号啥情况?

insert into student (123, '''张三');
select * from  student;
--得到
123 '张三

解释: 连续三个单引号,第一个是字符串界定符,第二个是转义符, 第三个是被转义的字符.

update student set name ='''张三''' where sno=123;
select * from student ;  --得到
 123  '张三'

解释: 三个连续的单引号, 前两个用于转义并获得原义单引号, 第三个是字符串界定符的结尾符.

insert into student (123, ''张三');-- 报错, 引号没有正常结束!

总结: Oracle 遇到字符串的第一个单引号时, 解析为字符串界定符的开始符, 遇到第二个,解释为界定字符串的结束符,但是有第三个单引号存在时, 第二第三个引号解释为原义单引号,

依次类推, Oracle 会寻找下一个单引号作为字符串界定结束符.

''张三' 为啥报错? : 第一个是字符串界定开山符, 第二个解释为字符串界定结束符, 但是后面又是 张三, 整个被认为是非法字符串.

(二)实现一个procedure 的调试

假设已经有了一张表： emp

step1: 创建一个procedure 名字为proc_xy

create or replace procedure proc_xy(v_deptno in varchar2)
is begin
declare
type emp_cursor is ref cursor;
emp_cur emp_cursor;
v_empno varchar2(10);
v_ename varchar2(10);
begin
open emp_cur for select ename, empno from emp where deptno=v_deptno;--打开与select一起
loop 
  fetch emp_cur into v_empno,v_ename;
  
  dbms_output.put_line(v_empno||'_'||v_ename);
  exit when emp_cur%notfound;
  end loop;
close emp_cur;
end;
end;

 step2: 在左侧的菜单栏中找到 proc_xy

右键proc_xy 先选择添加调试信息!! 这很重要, 否则不能单步调试了.

step3: 右键 选择->测试, 进入测试界面,

然后在下方输入这个procedure 的输入参数, 

第一个为开始调试, 第三个是单步调试,下方可以输入想要查看的变量名称.我这里输入的是v_ename,

每次点击"单步调试" 就可以看到变量的变化, 当出现问题的时候, 会终止运行并自动识别出来!

 (三) 强大的wmsys.wm_concat, 实现字符串合并

已知一张表bbb

现在想要把相同id1 的name1进行合并,如何操作?

select id1, wmsys.wm_concat(name1) name_all from bbb group by id1;

得到: 

结果是clob 形式, 点击打卡可以看到具体内容

如何把clob 转为string 类型?

 select id1, dbms_lob.substr(wmsys.wm_concat(name1),1000,1) name_all from bbb group by id1; 

20180901 补充: 其中也可以用to_char()  就行了, to_char(wm_concat(name1)) 

得到:

进一步: 如果需要将字符串进行转变, 每个字符串加*  建立函数

create or replace function func1(str1 in varchar2) return varchar2
is 
str2 varchar2(100);
begin
str2:=concat(str1,'*');
return str2;
end;

直接可以调用函数func1

select id1, dbms_lob.substr(wmsys.wm_concat(func1(name1)),1000,1) name_all from bbb group by id1;

得到:

 进一步: 上述的wm_concat 是逗号分隔,  如何指定分隔符? listagg

-- 用listagg,可以指定的分割符号 
select id1, listagg(name1,'*') within group(order by name1) name_all from bbb group by id1; -- 根据name1的顺序排布

得到

注意: 如果列的数据类型不是char , 那么需要转一下?

-- 如果列不是char 类型, 那么to_char
LISTAGG( to_char(列名), ',') WITHIN GROUP(ORDER BY 列名)

 但是我实际操作没有问题.. 已知表ccc, sal 是number

   

select id1, wmsys.wm_concat(sal) sal_all from ccc group by id1;
select id1, dbms_lob.substr(wm_concat(sal),1000,1) sal_all from ccc group by id1;

没有写 to_char  得到结果也是对的

  

用listagg 也没有报错

select id1, listagg(sal,'*') within group(order by sal) sal_all from ccc group by id1;

结果为: 

当然 select id1, listagg(to_char(sal),'*') within group(order by sal) sal_all from ccc group by id1; 也没有错!

 (四) 正则表达式

Oracle 10g 之后的版本都是支持正则表达式.

这里的正则表达式与Python 一样的, 都是通用标准用法的, 具体每个保留字 的含义可以见我的另一片博文.

具体函数介绍

(1) regexp_like()

regexp_like(expression, regexp) 第一个参数为源字符串, 第二个是用于匹配的正则表达式. 函数返回值为bool 值, True / False

select * from emp where regexp_like(ename, '^J.*$');
-- J 开头,后面任意个数的结尾
-- 也可以用like , %通配符, 实现上述功能 
select * from emp where ename  like 'J%' ;

注意: 如何忽略大小写?

select * from emp where regexp_like(ename, 'or','i');-- i 表示ignore
-- 结果得到 Jordan, Oracle 这两个人

(2) regexp_instr()

regexp_instr(expression, regexp, startindex, times), 

startindex 为开始匹配的位置, times 为第几次匹配结果最为最终的结果, 这两个参数默认都是1.

select regexp_instr('12.243','\.') position from dual;-- 结果是3

(3) regexp_substr()

regexp_substr(expression, regexp)  返回匹配的字符串

(4) regexp_replace(expression, regexp,replacement)