有关自治事务的问题： 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;
补充: 其中也可以用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)