Oracle之语法类应用汇总

已于 2023-08-22 08:48:36 修改
阅读量336 收藏 1
点赞数 1
文章标签： oracle 数据库
于 2023-08-18 10:21:41 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44836843/article/details/132355138
版权

一、regexp在Oracle中的应用

1. 概述

分类：与下列相似，但功能更加强大（‘支持正则表达式’）
(1) regexp_substr : 同 substr 功能相似（‘截取’ 字符串）
(2) regexp_replace: 同 replace 功能相似（ ‘替换’ 字符串）
(3) regexp_like : 同 like 功能相似（模糊 ‘匹配’）
(4) regexp_instr : 同 instr 功能相似（返回字符所在 ‘下标’）
– 上述 四个 函数，正则用法相似，知晓一个，其它皆可参考

2 正则匹配规则

分组元字符描述
表达式的替换和分组
|
替换，通常和分组操作符 () 一起使用
()
分组，并且 的关系;标记一个子表达式的开始和结束位置
[char]
字符列表，或者 的关系;标记一个中括号表达式
特殊的
\
转义字符。“\n”：换行，"\\n"：文本 ‘\n’
.
匹配除转义字符外的任何单字符
定位元字符
^
使表达式定位至一行的开头
$
使表达式定位至一行的末尾
量词或重复操作符
*
匹配 0 次或多次（任意次数）
?
匹配 0 次或 1 次（最多 1 次）
+
匹配 1 次或多次（至少 1 次）
{m}
正好 匹配 m 次。eg:'^1[0-9]{10}'-手机号码
{m, }
至少 匹配 m 次
{m, n}
匹配 m 到 n 次。eg:'^0[0-9]{2,3}-[0-9]{6,7}'-固定电话(0755-888999)
分组元字符描述
预定义的 posix 字符类
[:alpah:]
任何字母
[:lower:]
任何小写字母
[:upper:]
任何大写字母
[:digit:]
任何数字，相当于 [0-9]
[:xdigit:]
任何 16 进制的数字，相当于 [0-9a-fA-F]
[:alnum:]
任何字符或数字字符
[:space:]
空白字符，如：回车符，换行符、竖直制表符和换页符
[:punct:]
任何标点符号

3 正则函数

oracle学习文档

3.1 REGEXP_SUBSTR(String, pattern, [position],[occurrence],[modifier],[subexpression])

3.1.1 参数解释

String: 需要进行正则处理的字符串。
pattern:正则表达式。
position:起始位置（从字符串的第几个开始正则，默认为1，注：数据库中的字符串起始位置为1）。
occurrence:获取第几组通过正则表达式分割出来的组。
modifier:模式（‘i’不区分大小写，‘c’区分大小写。其中默认没‘c’）。
subexpression: 含有子表达式 0-9, 默认 0：不含子表达式，1：第一个子表达式，以此类推

3.1.2 实际用例
eg1:参数subexpression 测试使用
select  regexp_substr('abc,CBA121ABC,cba', ',[^,]+,') rs1                 -- ,CBA121ABC,
       ,regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 0) rs2 -- 12345678  不含子表达式
       ,regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) rs3 -- 123       第一个子表达式
       ,regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 2) rs4 -- 45678     第二个子表达式
       ,regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3) rs5 -- 56        依次类推
       ,regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4) rs6 -- 78
       ,regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 5) rs7 --     
  from dual;
eg2:按照不同的正则使用
with temp_email as (
  select 1 user_no, 'a1@qq.com' email from dual union all
  select 2 user_no, 'bb2@sina.com' email from dual union all
  select 3 user_no, 'ccc3@aliyun.com' email from dual
)
select t.user_no 用户编号
       ,regexp_substr(t.email,'[[:alnum:]]+') 用户名
       ,regexp_substr(t.email, '\@[[:alnum:]]+\.[[:alnum:]]+') 邮箱后缀
       -- 两者等同
       ,regexp_substr(t.email,'[a-zA-Z0-9]+') 用户名2
       ,regexp_substr(t.email, '\@[a-zA-Z0-9]+\.[a-zA-Z0-9]+') 邮箱后缀2
  from temp_email t;

–执行结果

   	用户编号	用户名	邮箱后缀	用户名2	邮箱后缀2
1	1	        a1	    @qq.com	    a1	    @qq.com
2	2	        bb2	    @sina.com	bb2	    @sina.com
3	3	        ccc3	@aliyun.com	ccc3	@aliyun.com
eg3:配合CONNECT BY使用----小数据量
SELECT REGEXP_SUBSTR('四,五,六,七', '[^,]+', 1, LEVEL, 'i') AS STR
  FROM DUAL
CONNECT BY LEVEL <= LENGTH('四,五,六,七') - LENGTH(REPLACE('四,五,六,七', ',', '')) + 1
--connect by level <= REGEXP_COUNT('四,五,六,七','[^,]+')
eg4:配合CONNECT BY使用----大数据量优化版

如果数据量小的话,这样来操作是没问题的,一旦数据量上来了,并且再加上多表联查,查询的速度就会非常慢(非常非常慢!),所以就有了下面的内容,优化.

WITH NEWS_TYPE AS
 (SELECT '1' AS Id, 'A,B,C,D' AS Attach_Id
    FROM DUAL
  UNION ALL
  SELECT '2', 'E,F,G,D'
    FROM DUAL
  UNION ALL
  SELECT '3', 'H,R,K,T'
    FROM DUAL)
SELECT ID, REGEXP_SUBSTR(ATTACH_ID, '[^,]+', 1, l) AS FILE_ONE
  FROM NEWS_TYPE,
       (SELECT LEVEL l
          FROM DUAL
        CONNECT BY LEVEL  <= 100 --此处的100，可以通过取 REGEXP_COUNT(NEWS_TYPE.ATTACH_ID,'[^,]+')的最大值来代替
        ) b
 WHERE l <= REGEXP_COUNT(NEWS_TYPE.ATTACH_ID, '[^,]+')
   AND regexp_like(id, '^[0-9]+');

–执行结果

   	STR
1	四
2	五
3	六
4	七

3.2 regexp_replace(Srcstr, pattern, [replacestr],[position],[occurrence],[modifier])

3.2.1 参数解释

Srcstr:需要进行正则处理的字符串。
pattern:正则表达式。
replacestr:需要替换成什么字段
position:起始位置（从字符串的第几个开始正则，默认为1，注：数据库中的字符串起始位置为1）。
occurrence:bigint类型常量，必须大于等于0，
大于0：表示将第几次匹配替换成replace_string，
等于0：表示替换掉所有的匹配子串。
其它类型或小于0抛异常。
modifier:模式（‘i’不区分大小写，‘c’区分大小写。其中默认没‘c’）。

3.2.2 实际用例
eg1:按照规则替换字符
with temp_strings as (   
  select 'abc123' str from dual union all
  select '123abc' str from dual union all
  select 'adf13994893964adf13453983743 ' str from dual union all
  select '2020年09月17日' str from dual union all
  select 'a1b2c3' str from dual
)   
select t.str 源字符串,
       regexp_replace(t.str, '[0-9]', '', 1)              as  无数字字符串,
	   regexp_replace(str, '.*(1[[:digit:]]{10}).*','\1') as  截取手机号,
	   REGEXP_REPLACE(T2.PAY_END_DT, '[^0-9]')            as  截取所有的数字
  from temp_strings t;

执行结果

	源字符串	无数字字符串
1	abc123	abc
2	123abc	abc
3	a1b2c3	abc
eg2: regexp_replace和listagg组合使用，实现对分组聚合后的结果进行去重
eg2.1 :数字汇总去重
 WITH NEWS_TYPE_VARCHAR AS
 (SELECT 't1' AS ID, 'A' AS Attach_Id
    FROM DUAL
  UNION ALL
  SELECT 't1', 'A'
    FROM DUAL
  UNION ALL
  SELECT 't1', 'C'
    FROM DUAL
  UNION ALL
  SELECT 't2', 'A'
    FROM DUAL
  UNION ALL
  SELECT 't2', 'B'
    FROM DUAL),
NEWS_TYPE_NUMBER AS
 (SELECT 't1' AS ID, '11' AS Attach_Id
    FROM DUAL
  UNION ALL
  SELECT 't1', '12'
    FROM DUAL
  UNION ALL
  SELECT 't1', '12'
    FROM DUAL
  UNION ALL
  SELECT 't2', '1'
    FROM DUAL
  UNION ALL
    SELECT 't2', '1'
    FROM DUAL)
select 'VARCHAR' AS QULB,
       T.ID,
       listagg(T.Attach_Id, ',') within group(order by 1),
       regexp_replace((listagg(T.Attach_Id, ',') within group(order by 1)),
                      '(,[[:digit:]]+)(\1)+',
                      '\1') GROUP_CODE
  from NEWS_TYPE_VARCHAR t
 GROUP BY T.ID
UNION ALL
select 'NUMBER',
       T.ID,
       listagg(T.Attach_Id, ',') within group(order by 1),
       regexp_replace((listagg(T.Attach_Id, ',') within group(order by 1)),
                      '(,[[:digit:]]+)(\1)+',
                      '\1') GROUP_CODE
  from NEWS_TYPE_NUMBER t
 GROUP BY T.ID;

–执行结果：只对数字生效，字符串无效



1	VARCHAR	t1	A,A,C	A,A,C
2	VARCHAR	t2	A,B	A,B
3	NUMBER	t1	11,12,12	11,12
4	NUMBER	t2	1,1	1,1
eg2.2:对任何以逗号隔开的任何字符串（包括数字）去重

//注意：这只能对相邻的字符串去重，因此要对字符串去重，必须先通过order by排序后去重

WITH NEWS_TYPE_VARCHAR AS
 (SELECT 't1' AS ID, 'A' AS Attach_Id
    FROM DUAL
  UNION ALL
  SELECT 't1', 'A'
    FROM DUAL
  UNION ALL
  SELECT 't1', 'C'
    FROM DUAL
  UNION ALL
  SELECT 't2', 'A'
    FROM DUAL
  UNION ALL
  SELECT 't2', 'B'
    FROM DUAL),
NEWS_TYPE_NUMBER AS
 (SELECT 't1' AS ID, '11' AS Attach_Id
    FROM DUAL
  UNION ALL
  SELECT 't1', '12'
    FROM DUAL
  UNION ALL
  SELECT 't1', '12'
    FROM DUAL
  UNION ALL
  SELECT 't2', '1'
    FROM DUAL
  UNION ALL
    SELECT 't2', '1'
    FROM DUAL)
select 'VARCHAR' AS QULB,
       T.ID,
       listagg(T.Attach_Id, ',') within group(order by 1),
       regexp_replace((listagg(T.Attach_Id, ',') within group(order by 1)),
                      '([^,]+)(,\1)*(,|$)',
                      '\1\3') GROUP_CODE
  from NEWS_TYPE_VARCHAR t
 GROUP BY T.ID
UNION ALL
select 'NUMBER',
       T.ID,
       listagg(T.Attach_Id, ',') within group(order by 1),
       regexp_replace((listagg(T.Attach_Id, ',') within group(order by 1)),
                      '([^,]+)(,\1)*(,|$)',
                      '\1\3') GROUP_CODE
  from NEWS_TYPE_NUMBER t
 GROUP BY T.ID;

–执行结果

1	VARCHAR	t1	A,A,C	A,C
2	VARCHAR	t2	A,B	A,B
3	NUMBER	t1	11,12,12	11,12
4	NUMBER	t2	1,1	1
eg2.3:以+号分割

WITH NEWS_TYPE_VARCHAR AS
 (SELECT 't1' AS ID, 'A' AS Attach_Id
    FROM DUAL
  UNION ALL
  SELECT 't1', 'A'
    FROM DUAL
  UNION ALL
  SELECT 't1', 'C'
    FROM DUAL
  UNION ALL
  SELECT 't2', 'A'
    FROM DUAL
  UNION ALL
  SELECT 't2', 'B'
    FROM DUAL)
select 'VARCHAR' AS QULB,
       T.ID,
       listagg(T.Attach_Id, '+') within group(order by 1),
       regexp_replace((listagg(T.Attach_Id, '+') within group(order by 1)),
                      '([^\+]+)(\+\1)*(\+|$)'
					  ,'\1\3') GROUP_CODE
	   --,to_char(rtrim(xmlagg(xmlparse(content upper(Attach_Id) || '+' wellformed) order by 1).getclobval() ,'+')) item_name  未能实现去重
  from NEWS_TYPE_VARCHAR t
 GROUP BY T.ID;

–执行结果：

1	VARCHAR	t1	A+A+C	A+C
2	VARCHAR	t2	A+B	    A+B
eg2.4 特殊情况，未实操演示

//如果遇到字符串过长，则可以使用如下语句

regexp_replace(rtrim(xmlagg(xmlparse(content upper(product_id) || ',' wellformed) order by product_id).getclobval() ,','),'([^,]+)(,\1)*(,|$)', '\1\3') product_id,

//如果是mybatis中使用如下语句，否则会报错

regexp_replace((listagg(product_id, ',') within group(order by product_id)),  '([^,]+)(,\1)*(,|$)','\1\3') product_id
eg3:取正则的不同部分处理
SELECT regexp_replace('+86 13811112222',
                      '(\+[0-9]{2})( )([0-9]{3})([0-9]{4})([0-9]{4})',
                      '(\1)\3-\4-\5'),  				   --格式化手机号 ’+‘在正则表达式中有定义，需要转义。\1表示引用的第一个组 (+86)138-1111-2222
	   regexp_replace('123.456.7890',
                      '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                      '(\1)\2-\3'),  			      	   --(123)456-7890
       regexp_replace('123.456.7890',
                      '([0-9]{3})\.([0-9]{3})\.([0-9]{4})',
                      '(\1)\2-\3'),						   --(123)456-7890
	   regexp_replace('abcdefg123456ABC', '(.)', '\1 ',1), --a b c d e f g 1 2 3 4 5 6 A B C
	   regexp_replace('abcdefg123456ABC', '(.)', '\1 ',2)  --ab c d e f g 1 2 3 4 5 6 A B C 
       regexp_replace('abcd','(.*)(.)$','\1',1)            --abc
	   regexp_replace('abcd','(.*)(.)$','\1-\2',1)         --abc-d
	   
  from dual;
eg4: 其他案例：
SELECT regexp_replace('abcd','(.)','\2',1)         --结果为''，因为pattern中只定义了一个组，引用的第二个组不存在。
      ,regexp_replace('abcd','(.*)(.)$','\2',1)    --结果为'd'
      ,regexp_replace('abcd','(.*)(.)$','\1',1)    --结果为'abc'
      ,regexp_replace('abcd','(.*)(.)$','\1-\2',1) --结果为'abc-d'
      ,regexp_replace('abcd','a','\1',1)           --，结果为” \1bcd”，因为在pattern中没有组的定义，所以\1直接输出为字符。
from dual;

3.3 regexp_like(Srcstr, pattern, [replacestr],[position],[occurrence],[modifier])

3.3.1 参数解释

Srcstr:需要进行正则处理的字符串。
pattern:正则表达式。
replacestr:需要替换成什么字段
position:起始位置（从字符串的第几个开始正则，默认为1，注：数据库中的字符串起始位置为1）。
occurrence:bigint类型常量，必须大于等于0，
大于0：表示将第几次匹配替换成replace_string，
等于0：表示替换掉所有的匹配子串。
其它类型或小于0抛异常。
modifier:模式（‘i’不区分大小写，‘c’区分大小写。其中默认没‘c’）。

3.3.2 实际用例
with regexp_like_temp_table as (
select 'adf13994893964ad f13453983743' str from dual
union all
select '1222260' str from dual
union all
select '122a260' str from dual
union all
select '1badfadfa' str from dual
union all
select '1Badfadfa' str from dual
union all
select 'adfsdfsfadf adadf' str from dual
union all
select 'adfsdfsfC,adfadadf' str from dual

)
 select *
   from regexp_like_temp_table
  where regexp_like(str, '.*(1[[:digit:]]{10}).*')    --查询包含以1开头的11位数据
     or (regexp_like(str, '1....60')                   
         or str like '1____60' )                      --"____"表示4个"_"  以 1 开头 60 结尾的记录并且长度是 7 位
     or (regexp_like(str, '^1[0-9]{4}60$') 
	 	 or regexp_like(str, '^1[[:digit:]]{4}60$'))  --以 1 开头 60 结尾的记录并且长度是 7 位且全部是数字的记录
     or regexp_like(str, '^[^[:digit:]]+$')           --[] 内的 ^ 表示 '非' 查询 str 中不包含任何数字的记录
	 or not regexp_like(str, '^[[:digit:]]+$')        --查询 str 中不是纯数字的记录
	 or (regexp_like(str, '^(12)|^(1b)')  
         or regexp_like(str, '^1[2b]'))	              --查询以 12 或 1b 开头的记录，区分大小写
	 or regexp_like(str, '^1[2b]', 'i')               --以 12 或 1b 开头的记录，不区分大小写
	 OR regexp_like(str, '^([a-z]+|[0-9]+)$')         --查询所有均为 小写字母 或 数字的记录
	 OR regexp_like(str, '[[:space:]]')               --查询包含 空白 的记录
	 OR regexp_like(str, '[[:punct:]]')               --查询包含 标点符号 的记录
	 	 OR REGEXP_LIKE(lkm_mobile,'[1][1234567890]{10}') --手机号必须是11位数字，开头是1
     OR REGEXP_LIKE(lkm_tel,'[0][1234567890]2,3[0][1234567890]2,3-[1234567890]{7,8}') --电话号码必须是(010)-1234567,区号3，4位，座机号7，8位
	 OR REGEXP_LIKE(test,'[0][0123456789]2,3[0][0123456789]2,3-[0123456789]{7,8}') --验证必须是电话号码：例：(010)-6666666
 ;

3.4 regexp_instr(String, pattern, [position],[occurrence],[retrurnparam],[modifier],[subexpression])

3.4.1 参数解释

srcstr : 源字符串 – source string
pattern : 正则表达式
position : 起始位置，默认 1
occurrence : 第几次出现，默认 1（匹配成功的次数，依次递增）
returnparam : 返回参数对应的下标（0：第一个位置的下标，非0：最后一个位置的下标）
modifier : 模式（‘i’: 不区分大小写，‘c’: 区分大小写。 默认 ‘c’）
subexpression: 含有子表达式 0-9, 默认 0：不含子表达式，1：第一个子表达式，以此类推

3.4.2 实际用例
select regexp_instr('1,23,456,7890', '[0-9]+', 1, 3)    				--匹配到的第一个字符下标,   --查询结果： 6
      ,regexp_instr('1,23,456,7890', '[0-9]+', 1, 3, 1) 				--最后一个字符后一位的下标, --查询结果： 9
      ,regexp_instr('1234567890', '(123)(4)(56)(78)', 1, 1, 0, 'i', 4)  --匹配到的第四个子表达式    --查询结果： 7
  from dual;

二、DBMS_RANDOM随机函数应用

1.DBMS_RANDOM.STRING (opt IN CHAR,len IN NUMBER) RETURN VARCHAR2;

1.1 参数解释

参数len指定生成的字符串的长度。
参数opt指定生成的字符串的样式，允许的取值及其表示的含义

1.2 参数用例含义

取值含义
‘u’或’U’
返回一个由大写字母组成的字符串
‘l’或’L’
返回一个由小写字母组成的字符串
‘a’或’A’
返回一个由大写字母和小写字母组成的字符串
‘x’或’X’
返回一个由大写字母和数字组成的字符串
‘p’或’P’
返回一个由任意的可打印字符组成的字符串

1.3 执行示例

select 
 DBMS_RANDOM.STRING ('u',6) --JETRGA
,DBMS_RANDOM.STRING ('l',6) --akntyk
,DBMS_RANDOM.STRING ('a',6) --iWWCkT
,DBMS_RANDOM.STRING ('x',6) --0D4N24
,DBMS_RANDOM.STRING ('p',6) --TUB=rF
,DBMS_RANDOM.STRING ('U',6) --LNDYKS
,DBMS_RANDOM.STRING ('L',6) --mlajxi
,DBMS_RANDOM.STRING ('A',6) --MqgIaR
,DBMS_RANDOM.STRING ('X',6) --H53KN4
,DBMS_RANDOM.STRING ('P',6) --M3[.ig
from dual;

三、窗口分析函数的应用：FIRST_VALUE\LAST_VALUE\lag\lead

1 概述

1.1 说明

(1) 均属于 ‘分析函数’: 每行一条记录（不聚合）
(2) + over(partition by 列1 order by 列2) – 一同使用

1.2 含义

first_value() – 第一个
last_value() – 最后一个 特别注意：是 ‘第一个’ 到 ‘当前记录’ 排行中的 ‘最后一个’ rows between unbounded preceding and current row
lag() – 上一个
lead() – 下一个

2 应用实例

2.1 first_value()和last_value()的使用

with student_info as (
  select 1 sno, 'a' sname, 18 age, date '2020-01-01' create_date from dual union all
  select 2 sno, 'b' sname, 18 age, date '2020-02-01' create_date from dual union all
  select 3 sno, 'c' sname, 18 age, date '2020-03-01' create_date from dual
)
select si.sno,
       si.sname,
       si.age,
       si.create_date,
       first_value(si.sname) over(partition by si.age order by si.create_date) 第一条记录,
       last_value(si.sname) over(partition by si.age order by si.create_date) 至当前最后一条记录,
       first_value(si.sname) over(partition by si.age order by si.create_date desc) 所有记录中最后一条
  from student_info si;

–执行结果


sno		sname	age		create_date		第一条记录	至当前最后一条记录		所有记录中最后一条
1		a		18		2020/1/1		a			a					c
2		b		18		2020/2/1		a			b					c
3		c		18		2020/3/1		a			c					c

2.2 lag() + lead()

with student_info as (
  select 1 sno, 'a' sname, 18 age, date '2020-01-01' create_date from dual union all
  select 2 sno, 'b' sname, 18 age, date '2020-02-01' create_date from dual union all
  select 3 sno, 'c' sname, 18 age, date '2020-03-01' create_date from dual
)
select si.sno,
       si.sname,
       si.age,
       si.create_date,
       lag(si.sname) over(partition by si.age order by si.create_date) 上一条记录,
       lead(si.sname) over(partition by si.age order by si.create_date) 下一条记录
  from student_info si;

–执行结果



sno   sname age   create_date   上一条记录 下一条记录
1       a   18    2020/1/1                   b
2       b   18    2020/2/1             a     c
3       c   18    2020/3/1             b

3 扩展

3.1 保持排序，且避免返回多行 keep()


/*
   要点：在子查询中 既能达到排序的目的，又不用担心 '返回多行'
   示例：查询各部门第一个入司的人
*/
select d.*,
       (select max(e.ename) keep(dense_rank first order by e.hiredate) 
               -- 报错：子查询返回多行
               -- max(e.ename) over(partition by e.deptno order by e.hiredate)
          from scott.emp e
         where e.deptno = d.deptno) 第一个入司的人
  from scott.dept d;

四、Execute immediate语句和using关键字实现动态SQL

1 概述

1.1 Execute immediate 动态SQL语句 into 出参1，出参2… using 形参1，形参2…

insert、update 是提取 操作后 的数据，delete 是提取 操作前 的数据。

1.2 Execute immediate 动态SQL语句 using 形参1，形参2… returning into 出参1，出参2…

returning 子句只能用于insert、update、delete。
returning into 在 动态sql内部和外面都要写，内部变量需要加上标识符“:”，外面不用，且外面的 returning 后面不加字段直接 into。
returning 不支持 record

2 应用实例

准备测试表

create table test1 (id number(2));
create table test2 (id number(2));
--向测试表中添加数据
begin
  for i in 1..50 loop
    insert into test1 values(round(dbms_random.value()*10));
  end loop;
  commit;
end;

begin
  for i in 1..100 loop
    insert into test2 values(round(dbms_random.value()*10));
  end loop;
  commit；
end;

2.1 into的使用

--目标：根据test1表的ID，查询在test2表出现的次数并输出。
declare
  flag number;
begin
  --循环获取test1表的数据
  --可以在SQL后面用||拼接形参，也可以使用 :参数名 作为占位符
  for test1 in (select distinct id from test1 order by id) loop
    execute immediate 'select count(1) from test2 where id = :1'
      into flag
        --注意占位符和using后的参数绑定是按照前后顺序的
      using test1.id;
    dbms_output.put_line('ID为'||test1.id||'的数量为：'||flag);
  end loop;
end;

–执行结果

ID为0的数量为：5
ID为1的数量为：10
ID为2的数量为：11
ID为3的数量为：15
ID为4的数量为：12
ID为5的数量为：7
ID为6的数量为：8
ID为7的数量为：15
ID为8的数量为：9
ID为9的数量为：10
ID为10的数量为：5

2.2 returning的使用

--目标：根据传入的ID删除test2表的数并输出删除条数。
declare
  type v_table_id is table of test1.id%type;
  v_id v_table_id;
  flag number := 1;
begin
  execute immediate 'delete test1 where id =' || flag ||
                    'returning id into :v_id'
    returning bulk collect into v_id; --v_id 是一个list集合
    rollback;
    dbms_output.put_line('总删除数：'||v_id.count);
    for i in 1..v_id.count loop
      dbms_output.put_line('删除值'||i||'：'||v_id(i));
      end loop;
end;

–执行结果

总删除数：6
删除值1：1
删除值2：1
删除值3：1
删除值4：1
删除值5：1
删除值6：1

五、约束的应用：FIRST_VALUE\LAST_VALUE\lag\lead

1 概述 Oracle数据库中的约束有五种约束，分别是：

1.1 实体完整性：主键约束、唯一性约束（可以为空）

Constraint Primary Key|Unique (<column_name>)

1.2 域完整性：check约束

Constraint Check (列条件表达式)

1.3 引用完整性：Foreign KEY约束

Constraint Foreign key (<column_name>) references <table_name>(<column_name>)

1.4 默认约束（默认值）

column_name column_type default ‘默认值’ --创建表或修改表字段

2 约束增删改查

2.1 删除约束

alter table 表名 drop constraint 约束名称;

2.2 禁用/启用约束

alter table 表名 disable/enable constraint 约束名称;

2.3 修改约束名称

alter table 表名 rename constraint 旧约束名称 to 新约束名称;

2.4 添加约束

alter table test add constraint test_pk primary key(id); --添加主键约束（非空，不重复）（给test表的id这一列添加主键约束）
alter table test add constraint test_unique_name unique(name); --添加唯一性约束（不重复，可以为空）（给test表name这一列添加唯一性约束）
alter table test add constraint test_cus_no_fk_1  foreign key(cus_no) references cus_info (cus_no)--添加外键约束（保证外键的值取关联表里关联字段的值）（给表test的列cus_no设为外键，关联表cus_info的列cus_no
alter table test add constraint test_name_check check(name in ('a','b','c') ) /* check(REGEXP_LIKE(lkm_mobile,'[1][1234567890]{10}')) */; --完整性约束（代表一个字段或者多个字段的取值是有限定范围的）
create table t1 (tname varchar2(20) default 'yang');--默认约束-创建表
alter table test_table add (TEST_D VARCHAR2(100) NOT NULL default 'yang' );--默认约束-修改列属性
Oracle语法大全
God_00的博客
05-09 2118
基础知识 ORACLE支持五种类型的完整性约束 NOT NULL (非空)–防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值. CHECK (检查)–检查在约束中指定的条件是否得到了满足. UNIQUE (唯一)–保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的. PRIMARY KEY (主键)–用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束. POREIGN KEY (外部键)–通过使用公共列在表之间建立一
Oracle的REGEXP_SUBSTR函数简单用法
itmyhome的专栏
12-27 4万+
REGEXP_SUBSTR延伸SUBSTR函数的功能，让你搜索一个正则表达式模式字符串。 这也类似于REGEXP_INSTR，而是返回子字符串的位置，它返回的子字符串本身。 语法 Oracle数据库中的REGEXP_SUBSTR函数的语法是： REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_
Oracle分析函数基本概念和语法总结及Regexp_***用法
11-02
Oracle分析函数基本概念和语法总结及Regexp_***(regexp_substr,regexp_instr, regexp_like, regexp_replace, regexp_count)用法
oracle 中regexp的用法
陈苏漾的IT生涯
02-25 1万+
ORACLE中的支持正则表达式的函数主要有下面四个： 1，REGEXP_LIKE ：与LIKE的功能相似 2，REGEXP_INSTR ：与INSTR的功能相似 3，REGEXP_SUBSTR ：与SUBSTR的功能相似 4，REGEXP_REPLACE ：与REPLACE的功能相似 它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同， 但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。 POSIX 正则表达式由标准的元字符（me
Oracle 正则表达式详解（regexp_substr、regexp_instr、regexp_replace、regexp_like）
最新发布
wangcheng8699的专栏
11-20 1131
Oracle 正则表达式
Oracle 正则表达式详解（regexp_substr、regexp_instr、regexp_replace、regexp_like、regexp_count）
鱼丸丶粗面
07-22 2万+
文章目录1 概述2 匹配规则3 正则函数3.1 regexp_substr()3.2 regexp_instr()3.3 regexp_replace()3.4 regexp_like() 1 概述 1. 作用：处理字符时，很强大 2. 分类：与下列相似，当功能更加强大（'支持正则表达式'） (1) regexp_like : 同 like 功能相似（模糊 '匹配'） (2) regexp_instr : 同 instr 功能相似（返回字符所在 '下标'） (3) regexp_s
oracle中的函数regexp_substr()使用心得及正则表达式
热门推荐
a720561252803的博客
05-23 12万+
一、各个参数的解释说明： function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) string:需要进行正则处理的字符串 pattern：进行匹配的正则表达式 position：起始位置，从字符串的第几个字符开始正则表达式匹配（默认为1） 注意：字符串最初的位置是1而不是0 occurrence：获取第几个分割...
oracle正则表达式regexp_like的用法 详细说明 正则表达式 匹配字符
qq_45239887的博客
01-12 1608
oracle正则表达式regexp_like的用法 详细说明 正则表达式 匹配字符
SQLServer与Oracle语法差异汇总.docx
11-24
在IT行业中，数据库管理系统是核心组件之一，Oracle和SQL Server是两种广泛应用的关系型数据库系统。它们在语法上有诸多差异，这些差异主要体现在存储过程、自定义函数、游标、变量、赋值、语句结束符以及大小写敏感...
Oracle实现分页查询的SQL语法汇总
09-10
本文将详细解析Oracle实现分页查询的几种SQL语法，以便于开发者更高效地处理大数据量的检索需求。 1. **无ORDER BY排序的写法** (效率最高) 这种方法适用于数据无需特定排序的情况。通过在外层查询中设置`ROWNUM`...
oracle进阶之PLSQL编程.rar_oracle_oracle 编程_应用于java程序
09-20
总的来说，"Oracle进阶之PLSQL编程"涵盖了一系列高级数据库编程技术，包括PL/SQL的语法、函数、过程、包、触发器的使用，以及如何将这些知识应用于Java程序中，通过JDBC进行数据库操作。这不仅有助于提升数据库管理...
ORACLE函数——支持正则表达式的函数
漓涂
05-04 1171
ORACLE中的支持正则表达式的函数主要有下面五个：1、REGEXP_LIKE ：与LIKE的功能相似2、REGEXP_INSTR ：与INSTR的功能相似3、REGEXP_SUBSTR ：与SUBSTR的功能相似4、REGEXP_REPLACE ：与REPLACE的功能相似5、regexp_count它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用...
Oracle中REGEXP_SUBSTR及其它支持正则表达式的内置函数小结
06-21 943
转自：https://www.cnblogs.com/softidea/p/5208128.html
Oracle regexp_like
danevc的博客
02-10 6569
ORACLE中的支持正则表达式的函数主要有下面四个：1，REGEXP_LIKE ：与LIKE的功能相似2，REGEXP_INSTR ：与INSTR的功能相似3，REGEXP_SUBSTR ：与SUBSTR的功能相似4，REGEXP_REPLACE ：与REPLACE的功能相似它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，但是它们使用POSI
Oracle中的正则替换【REGEXP_REPLACE】和正则like【REGEXP_like】
谢彬のCSDN专栏
12-05 5万+
下面图片中有三种“加瓦”，java,Java,JAVA select regexp_replace(city,'java','苍井空',1,0,'i') from lidw li regexp_replace(1,2,3,4,5,6) 语法说明：1：字段   2：替换的字段  3：替换成什么  4：起始位置（默认从1开始）  5：替换的次数
Oracle分割字符串中的所有逗号 REGEXP_SUBSTR
KnuthZ的博客
09-06 1万+
分割字符串中所有的逗号，然后成多行 参数说明， 参数1: 待分割字符串 参数2：正则表达式 参数3：起始位置，从第几个字符开始正则表达式匹配（默认为1） 参数4：标识第几个匹配组，默认为1 参数5：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。） SELECT REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL
Oracle regexp_like函数简单用法
wimpykids的博客
07-09 5376
oracle处理字符串之正则表达式查询只包含字母或数字的数据，假设表名为t1,列名为d1,则sql如下 select d1 from t1 where regexp_like(d1,’^[0-9a-zA-Z]+$’) +：表示匹配前面的字表达式1次或多次(*号表示至少匹配0次) ^：匹配输入字符串开始的位置，对应like中的like ’xxx%’，即没有前模糊查询(^在[]中括号表示否的意思) $：匹配输入字符串结束的位置，对应like中的like ’%xxx’，即没有后模糊查询 前后加上^.
Oracle regexp_replace函数简单用法
wimpykids的博客
06-17 4225
regexp_replace(1,2,3,4,5,6) 语法说明：1：字段 2：替换的字段 3：替换成什么 4：起始位置（默认从1开始） 5：替换的次数（0是无限次） 6：不区分大小写 【补充：】用regexp_replace来判断oracle中的某列为数字的： SELECT(REGEXP_REPLACE('LSS12345', '[^0-9]')) FROM DUAL;---取出值里面的全部数字 SELECT LENGTH('LSS12345'),LENGTH(REGEXP_RE...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值