关系数据库与Sql

1. 数据库连接

1.1 Oracle

1.1.1 命令行连接

sqlplus /nolog

conn username/password@IP:[1521]/数据库服务名 [as sysdba]

使用tnsname

vi $ORACLE_HOME/NETWORK/ADMIN/tnsnames.ora

ORCL = “orcl服务名”

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 主机IP地址)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ABC)

)

sqlplus /nolog

conn username@orcl #使用配置文件内的名字连接,配置文件需在默认位置

1.1.2 cx_oracle

def conOra():

connection = cx_Oracle.connect('user','passwd', '1.1.1.1/实例名' , encoding='UTF-8', nencoding='UTF-8')

return connection.cursor()

1.2 mysql

1.2.1 命令行连接

连接数据库mysql -u maria -p

使用数据库 use hangzhou;

1.2.2 pymysql

2. 数据库管理

2.1 用户管理oracle

2.1.1 创建用户

CREATE USER master

IDENTIFIED BY master

DEFAULT TABLESPACE master_space //可选

TEMPORARY TABLESPACE master_temps

PROFILE master_profile //可选,缺省为default

PASSWORD EXPIRE //可选,每次登录后都必须修改口令

ACCOUNT LOCK | UNLOCK; //可选,账号是否锁定

2.1.2 修改用户

解锁用户：ALTER USER scott ACCOUNT UNLOCK;

修改密码：ALTER USER scott IDENTIFIED BY tiger;

PASSWORD scott --sqlplus

GRANT CONNECT TO scott IDENTIFIED BY tiger;

修改默认表空间:ALTER USER scott DEFAULT TABLESPACE space;

修改临时表空间:ALTER USER scott TEMPORARY TABLESPACE space;

修改表空间配额:ALTER USER scott QUOTA size ON SYSTEM;

2.1.3 查看用户

SELECT username FROM dba_users; --all_users

select * from user_users; --当前登录用户

2.1.4 删除用户

删除用户:DROP USER scott [CASCADE];

2.2 用户管理mysql

创建用户 CREATE USER u1 IDENTIFIED BY ’pass’;

修改用户名 RENAME USER u1 TO u2;

修改密码 SET PASSWORD FOR u1 = Password(‘pass’);

修改自己密码 SET PASSWORD = PASSWORD(‘pass’);

删除用户 DROP USER u1；

查看当前登录用户 SELECT USER();

查看用户表 use mysql;

select user from user;

2.3 权限管理

2.3.1 权限查询

#根据权限内容搜索权限名称

select * from session_privs WHERE PRIVILEGE LIKE '%LINK%'

#当前用户拥有的全部权限：

select * from session_privs;

select * from user_sys_privs --

#指定用户的权限

SELECT * FROM dba_sys_privs WHERE GRANTEE = UPPER('sys')

2.3.2 权限授予

grant select on t1 to u1 --授予用户u1查询t1权限

grant sysdba to sys container=all --sysdba权限

grant create public database link to sys --创建dblink权限

grant drop public database link to sys --删除dblink权限

2.3.3 mysql

查看自己的授权 show grants；

查看其他用户权限 show grants for u1;

授权 grant 权限1,权限2,select on 数据库.表 to u1;

更新授权 flush privileges;

在数据库test的所有表授予u1用户查询权限

grant select on test.* to u1;

收回权限 grant revoke 权限1 on 数据库.表 to u1;

创建用户同时授权

grant reload,lock tables,replication client on *.* to 'uback’@’localhost' identified by 'uback';

权限清单

usage 无权限

all 除进行授权本身外的所有权限

2.4 外部库dblink

2.4.1 查询

select * from "DBA_OBJECTS" where object_type='DATABASE LINK';

SELECT * FROM dba_db_links --创建语句

2.4.2 创建

CREATE PUBLIC DATABASE LINK 名字 CONNECT TO 外部用户名IDENTIFIED BY 密码 USING '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 外部ip)(PORT =端口 ))

)

(CONNECT_DATA =

(SERVICE_NAME = 外部实例名)

)

)';

2.4.3 修改

ALTER PUBLIC DATABASE LINK 名字

CONNECT TO 用户名 IDENTIFIED BY 密码;

2.4.4 删除

DROP PUBLIC DATABASE LINK to_midway

2.5 容器（cdb, pdb）

2.5.1 查询

查看当前容器

select sys_context('USERENV','CON_NAME') from dual

查看pdb

select name,open_mode from v$pdb

切换

alter session set container=CDB$ROOT --到cdb

alter session set container=PDBNAME --到某个pdb

2.6 事务

创建保存点a savepoint a;

回滚到保存点 rollback to a;

回滚到上次保存点 rollback;

提交修改 commit;

2.7 定时任务

2.7.1 创建

declare

job number; --不可省略

BEGIN

DBMS_JOB.SUBMIT(

JOB => job, /*自动生成JOB_ID*/

WHAT => 'testJob;', /*需要执行的存储过程名称或SQL语句*/

NEXT_DATE => sysdate, /*初次执行时间-sysdate为立即执行*/

INTERVAL => 'TRUNC(SYSDATE + 1) + （7*60）/(24*60)' /* 下次执行时间,此处早上7点执行*/

);

commit; --这里的commit不能代替语句里的commit

end;

2.7.2 查询

select * from all_jobs where what like ‘%存储过程名字%’;

2.7.3 启动

declare

begin

DBMS_JOB.RUN(24); /*24 job的id*/

commit;

end

2.7.4 停止

declare

begin

dbms_job.broken(24,true,sysdate);

--参数:1. job id, 2. true/false，3.什么时候停止(sysdate立刻停止)

commit;

end;

2.7.5 删除

declare

begin

dbms_job.remove(24); /*删除自动执行的job,参数是 job的id*/

commit;

end;

2.7.6 修改

修改执行间隔

declare

begin

dbms_job.interval(24,interval => 'TRUNC(SYSDATE + 1) + （18*60）/(24*60)'); /*第一个参数为job的ID,第二个参数interval: 计算下一次任务执行的时间表达式*/

commit;

end;

修改下一次执行的时间

declare

begin

dbms_job.next_date(24,to_date('2017-01-09 12:08:00','yyyy-mm-dd hh24:mi:ss')); /*第一个参数:job的ID；第二个参数:要修改后的计算下一次执行的时间表达式*/

commit;

end;

更换要执行的存储过程

declare

begin

dbms_job.what(24,'testJob2();'); /* 第一个参数:job的ID；第二个参数:要更改的新操作名称（操作名必须存在）*/

commit;

end;

2.7.7 时间参数说明

INTERVAL 部分参数值示例：

每天午夜12点: 'TRUNC(SYSDATE + 1)'

每天早上8点30分: 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'

每星期二中午12点: 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'

每个月第一天的午夜12点: 'TRUNC(LAST_DAY(SYSDATE ) + 1)'

每个季度最后一天的晚上11点: 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'

每星期六和日早上6点10分: 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'

每月25号00：00执行: 'TRUNC(LAST_DAY(SYSDATE ) + 25)'

－－－－－－－－－－－－－－－－－－－－－－－－－－

1:每分钟执行

Interval => TRUNC(sysdate,'mi') + 1/ (24*60)

或

Interval => sysdate+1/1440

2:每天定时执行

例如：每天的凌晨1点执行

Interval => TRUNC(sysdate) + 1 +1/ (24)

3:每周定时执行

例如：每周一凌晨1点执行

Interval => TRUNC(next_day(sysdate,'星期一'))+1/24

4:每月定时执行

例如：每月1日凌晨1点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

5:每季度定时执行

例如每季度的第一天凌晨1点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

6:每半年定时执行

例如：每年7月1日和1月1日凌晨1点

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

7:每年定时执行

例如：每年1月1日凌晨1点执行

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24

2.7.8 job参数说明

dba_jobs 表中字段含义：

JOB 任务的唯一标识码

LOG_USER 提交任务的用户

PRIV_USER 赋予任务权限的用户

SCHEMA_USER 对用户作语法分析的用户模式

LAST_DATE 最后一次成功执行任务的时间

LAST_SEC 最后一次成功执行任务的时间的时分秒

THIS_DATE 正在执行的任务的开始时间，若没有则为空

THIS_SEC 正在执行的任务的开始时间的时分秒，若没有则为空

NEXT_DATE 下一次执行定时任务的时间

NEXT_SEC 下一次执行定时任务的时间的时分秒

TOTAL_TIME 执行当前任务所需要的时间，单位：秒

BROKEN 标志参数，Y表示任务中断，以后不会再运行

INTERTAL 计算下一次执行定时任务的时间表达式

FAILURES 当前定时任务执行失败的总次数

WHAT 执行任务的PL/SQL代码块

NLS_ENV 任务执行的NLS会话设置

MISC_ENV 定时任务运行的其他一些参数设置

INSTANCE 标识当前任务运行是否受限，0 没有受限

3. 表操作

3.1 建表,视图,表空间

3.1.1 表空间

create tablespace abc

datafile ‘/u01/app/oracle/oradata/TestDB11/catalog.dbf’ 自定义数据文件位置及名字

size 100M #大小

extent management local autoallocate 扩展自动管理

segment space management auto;

3.1.2 建表

CREATE TABLE t1 (

c1 varchar2(50) PRIMARY KEY --主键

c2 date REFERENCES t2(c2) --外键关联到t2的c2列

c3 date UNIQUE --唯一约束,该列的值不可以重复

c4 date NOT NULL --非空

c5 number DEFAULT 5) --设置默认值

TABLESPACE abc; 指定表空间

添加注释

comment on table t1 '自受理工单'; --表注释

comment on column t1.id '主键'; --字段注释

comment on column t1.c1 '工单号';

创建序列

create sequence SEQ1 --

start with 1 --从1开始,不能小于最小值

[increment by 5 ] --每次增加5,缺省1

[minvalue 1 ] --设置最小值,递增序列缺省1

[maxvalue 99999 ] --设置最大值,缺省不设最大值

nocycle --一直累加,不循环,cycle

cache 10 --缓存,一次产生10个序号,缺省20,nocache

noorder;

--序列与表没有绑定关系,其他表也可以引用

--查看序列

select * from user_sequences;

--查看当前序列是多少

select SEQ1.currval from dual

--查看序列的下一个值

select SEQ1.nextval from dual

--更新/插入时使用序列

insert into t1(c1,c2)values(SEQ1.Nextval,'HAH');

复制创建表

SELECT * INTO t2 FROM t1;

CREATE TABLE test TABLESPACE abc AS SELECT * FROM test2;

仅复制表结构

SELECT * INTO t2 FROM t1 where rownum<1;

CREATE TABLE t2 LIKE t1;

CREATE TABLE tes AS SELECT * FROM test2 where rownum<1;

3.1.3 视图

就是一组查询语句,查询视图时执行查询语句

CREATE [OR REPLACE]

[FORCE] --相关表存在才创建视图

VIEW view_name [(alias[, alias]...)] --列别名

AS select...

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY] --只读,不能对表做修改

--在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用ORDER BY 子句

3.2 新增

增加列 ALTER TABLE test ADD hobby nvarchar2(20);

添加索引 CREATE INDEX index1 ON t1(c1)

添加唯一索引 CREATE UNIQUE INDEX index1 ON t1(c1)

添加主键 ALTER TABLE t1 ADD PRIMARY KEY(字段名);

添加主键 ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY(ename);

添加外键 ALTER TABLE t ADD CONSTRAINT f3 FOREIGN KEY(c2) REFERENCES t3(id);

//外键条件,一个表的非主键连接另一个表的主键

添加唯一约束 ALTER TABLE test ADD UNIQUE(id);

check约束 ALTER TABLE test ADD CONSTRAINT emp_chk CHECK(empno>7000);

添加虚拟列 ALTER TABLE t1 ADD ( c2 AS (sum(c3)) VIRTUAL);

3.3 删除

#删除表空间

drop tablespace abc

including contents 同时删除表空间中的数据

cascade constraints; 同时删除完整性限制

#删除表

DROP TABLE test1

CASCADE CONSTRAINTS PURGE; #同时删除引用及数据文件

删除列 ALTER TABLE test DROP COLUMN hobby;

删除唯一约束 ALTER TABLE test DROP UNIQUE(id); #列名

删除约束 ALTER TABLE test DROP CONSTRAINT emp_chk ; #约束的名字

删除主键 ALTER TABLE t1 DROP PRIMARY KEY ;

删除主键 ALTER TABLE test DROP CONSTRAINT id_pk; #主键名字

删除外键 ALTER TABLE new DROP CONSTRAINT new_test3; #外键名字

3.4 修改

改表名 ALTER TABLE t6 RENAME TO t3;

改列名 ALTER TABLE test RENAME COLUMN id TO id1;

改列数据类型/长度 ALTER TABLE t MODIFY id integer;

设置列非空 ALTER TABLE test MODIFY ename NOT NULL;

设置列可以为空 ALTER TABLE test MODIFY ename NULL;

列设置默认值 ALTER TABLE test MODIFY(id DEFAULT 22);

3.5 查询

获取表的全部字段

select wm_concat(column_name) from user_tab_columns where table_name=upper('tableName')

查询表字段类型

select * from all_tab_cols where table_name=upper('表名')

查询建表语句(可以看字段类型)

select dbms_metadata.get_ddl('TABLE',upper('表名')) from dual;

查询索引

select * from all_ind_columns where table_name=upper('表名')

4. 数据类型

5. 插入行

5.1 基础插入

INSERT INTO test VALUES('smith',7369,5);

INSERT INTO t1(c1,c2,c3) VALUES(’abc’,’123’,’hahaha’);

INSERT ALL --还可以用于一条记录插入多个表

INTO t1(c1,c2,c3) VALUES(’’,’1’,’h’)

INTO t1(c1,c2,c3) VALUES(’’,’123’,’hah’)

SELECT 1 FROM dual;

--插入日期

INSERT INTO t1 VALUES(TIMESTAMP'2021-10-28 11:19:59');

5.2 从另一个表查询数据插入

插入全部数据

insert into tab1 select * from tab2 #一个表全部数据插入另一个表

插入指定字段

INSERT INTO test(id,ename) SELECT id,ename FROM emp;

5.2.3 插入不存在的数据(表2有重复数据会出错)

merge into

merge into yytbt t1

using yytb_tmp t2

on (t1.编号=t2.编号)

when not matched then

insert (c1,c2,c3)

values (t2.a,t2.b,t2.c)

INSERT INTO t1 SELECT *

FROM t2 WHERE (

t2.id NOT IN

(SELECT t1.id FROM t1)

)

exists

insert into t2(id,name)

select id,name from t1 where not exists

(select null from t2 where t2.id=t1.id)

6. 删除行

删除某条记录 DELETE FROM t1 WHERE id=5;

删除全部行,可撤销 DELETE FROM test;

删除全部行,不可撤销 TRUNCATE TABLE t1;

删除指定列重复的数据

delete from t1 where rowid not in (select max(rowId) from t1 group by user_Id)

7. 更新列

7.1 基础更新

更新单字段

UPDATE t1 SET c2=’123’ WHERE c1=young;

更新多字段

update t1 set (字段1, c2) = (值1, v2) WHERE c1=young;

忽略错误，更新能正常更新的行

UPDATE IGNORE t1 set

使用子查询更新

UPDATE test SET id =(SELECT id FROM t2 WHERE eno=7369) WHERE name=’smith’;

更新日期

UPDATE t1 SET c1 =TIMESTAMP'2021-10-28 11:19:59' WHERE

清空列内容

UPDATE test SET id=null;

7.2 用一个表更新另一个表

7.2.1 merge into

merge into yytbt t1

using yytb_tmp t2

on (t1.编号=t2.编号)

when matched then

update set t1.a=t2.a

不能更新on连接的字段,表2有重复数据会出错,(有时update出错,在语句最后加一个where 1 = 1字句)

7.2.2 exists

update t1 set c2=(select c2 from t2 where t1.c1=t2.c1)

where exists (select 1 from t2 where t1.c1=t2.c1)

#使用c1字段进行表连接,用表2的c2更新表1的c2,表二有重复项会报错,性能不如merge into

8. 数据库及表信息查询

8.1 数据库查询

查看数据库mysql show databases;

查看当前使用的数据库mysql select database();

show tables；

查看数据库状态m status;

查看表结构sqlplus desc t1;

#查看数据库信息

select * from v$database

#查看数据库名

select name from v$database

#查看数据库版本

select * from v$version;

#查看当前系统连接数

select count(*) from v$process

#查询最大连接数设置

select value from v$parameter where name = 'processes'

#获取会话信息:

select sid,serial#,username,program,osuser,machine,event, logon_time from v$session;

终止会话: alter system kill session ‘sid,serial#’;

#获取用户执行过的命令:

select sid,sql_text,user_name from v$open_cursor;

#查询定时任务

select * from all_jobs where what like ‘%存储过程名字%’;

#查询dblink

select * from "DBA_OBJECTS" where object_type='DATABASE LINK';

SELECT * FROM dba_objects where object_type='DATABASE LINK'

8.2 表查询

字段类型

select * from all_tab_cols where table_name=upper('表名')

建表语句

select dbms_metadata.get_ddl('TABLE',upper('表名')) from dual;

只看字段

12c以下

select wm_concat(column_name) from all_tab_cols where table_name=upper('tableName')

12c及以上版本

SELECT LISTAGG(column_name,',') WITHIN group(ORDER BY table_name) FROM all_tab_cols WHERE virtual_column='NO' AND table_name=upper('表名')

查询索引

select * from all_ind_columns where table_name=upper('表名')

select * from all_indexes where table_name = upper('表名') ; --更详细

查询更新时间/状态等

select * from ALL_OBJECTS where object_name =upper('名字')

查询表操作日志,sql语句性能等

select sql_text,first_load_time,last_load_time,last_active_time,module from v$sql where sql_text like '%表名%';

视图可换成v$sqlarea

查询视图定义：

SELECT view_name,text from user_views;

--查看序列

select * from user_sequences;

--查看当前序列是多少

select SEQ1.currval from dual

--查看序列的下一个值

select SEQ1.nextval from dual

触发器

select * from all_triggers where trigger_name=upper(‘名字’);

9. 查询select

sql语句执行顺序

照片来自《sql编程思想》

9.1 去重

基础去重

单字段 select distinct c1 from t1;

多字段 select distinct(c1,c2) from t1;

去重并显示其他字段

SELECT * FROM t1 WHERE rowid IN (SELECT max(rowid) FROM t1 WHERE c2 IS NOT NULL GROUP BY c3 )

select c1, c2 FROM

(select c1, c2, row_number() over(partition by c1 order by c2) rn from t1)

WHERE rn = 1

9.2 模糊查询

like

where c1 like ‘_alo%’; not like

_ 单个字符

% 零或多个字符,无法匹配null

[abc] 其中一个字符

[^abc] 不在其中的字符

[!abc] 同上

regexp_like 正则表达式

where regexp_like(列名c1,'\d+$'); 匹配出0到多个数字结尾的项

9.3 限制返回行数

Oracle查询前6行 select c1 from t where rownum<6;

符号只能< ,<= ,!=

Mysql查询前6行 select c1 from t limit 6; 计数从0开始

Mysql查询从第7行开始的6行

select c1 from t1 limit 6,6;

Oracle查询第6行以后的数据

SELECT c1 ,c2 from ( SELECT ROWNUM N, c1 ,c2 FROM t1 ) WHERE N>6

注:内层rownum 一定要用别名, 不然会冲突出错

9.4 日期查询

字符串转时间to_date(c1, 'YYYY-MM-DD HH24:MI:SS' )

时间转字符串 to_char(时间列, 'YYYY-MM-DD' )

时间列查询 20210930以后的数据

where to_char(时间列, 'YYYY-MM-DD') > 20210930

9.5 表连接与子查询

9.5.1 表连接

内连接的两种形式

SELECT a.a1,b.b1 FROM a,b WHERE a.a2=b.b2;

SELECT a.a1,b.b1 FROM a JOIN b ON a.a2=b.b2 where ;

full join 全外连接, 同时使用左连接和右连接

left join,左连接

right join 右连接

cross join 笛卡尔积, 不需on字段

union

将两个select语句的查询结果合并到一起，表1的数据在上,对应列的数据类型要相同,union得到的结果会去重，结尾可用order by

union all 同上,不去重

intersect 取交集

minus 取差集

9.5.2 嵌套子查询

先执行括号内的子查询,再执行父查询

IN / NOT IN

WHERE LastName IN ('Adams','Carter');

WHERE LastName IN (select c2 from t2);

WHERE (c1,c2) IN (SELECT c3,c4 from t2)

IN 先查询外表（左侧），再查询内表（右侧）,用于外表大于内表

EXISTS 先查询内表（右侧），再查外表,用于内表大于外表

IN比or快,

NOT EXISTS 比NOT IN 快

9.5.3 相关子查询(子查询中where子句有连接)

先执行父查询,父查询的结果逐行传递给子查询执行.即父查询的每一行都执行一遍子查询,效率较低

select c1,c2,c3 from t1 where c3>

(select avg(c4) from t2 where t2.c1=t1.c1)

EXISTS:

select * from t1 where exists(select null from t2 where t2.c1=t1.c1)

等同于in的写法

select * from t1 where c1 in(select c1 from t2)

9.6 分组查询与聚合函数

顺序where, group, having, order by

select c1,.. from t1 group by c1 having ..

--显示分组的id,用于过滤重复项,不重复的情况id都是0,

select c1,group_id() from t1 group by c1 having group_id()=0

--根据分组把多列展示在一行

select wm_concat(c1) from t1 group by c2

聚合函数

min, max, avg

9.7 操作符

9.7.1 BETWEEN区间:

WHERE Name BETWEEN 'Adams' AND 'Carter'

NOT BETWEEN (数值,文本,日期等)

空值 where id is null; is not null

9.7.2 any all

any( ) --符合任意一个 >any()

all( ) --符合全部 <all()

9.8 查询结果进行编号或组内编号

9.8.1 rank() over

--1.以c2列进行排名,名次相同并列，下一条数据空出名次

SELECT c1,c2,RANK() OVER(ORDER BY c2 desc) 排名 FROM t1;

--2.把空值放在最后,默认空值在最前

SELECT c1,c2,RANK() OVER(ORDER BY c1 desc nulls last) 排名 FROM t1;

--3.分组排名,按照c1列分组,对每一组进行排名

SELECT c1,c2,RANK() OVER(PARTITION BY c1 ORDER BY c2 desc) 排名 FROM t1;

9.8.2 dense_rank() over

与以上的区别是,名次相同并列，但下一条数据不空出名次

9.8.3 row_number() over

与以上的区别是,名次相同不并列,算作不同名次

9.9 查询结果多行转为一行

wm_concat(11g)

把查询出的某列显示在一行 select wm_concat(c1) from t1

根据分组把某列展示为多行 select wm_concat(c1) from t1 group by c2

listagg within group(12c)

SELECT LISTAGG(c1,',') WITHIN GROUP(ORDER BY c1) FROM t1

SELECT LISTAGG(c1,',') WITHIN GROUP(ORDER BY c1) FROM t1 GROUP BY c2

9.10 排序

order by c1 --根据c1从小到大排列

order by c1 desc --根据c1从大到小排列

order by c1 desc nulls last --根据c1从大到小排列, 空值放最后

order by c1 nulls first --根据c1从小到大排列, 空值放最前

10. 系统函数

10.1 字符串函数及正则表达式

10.1.1 删除字符

translate(c1 ,'a楼层','a')

--删除c1字段中的楼和层, a可以是任意c1中不存在的字符

regexp_replace(c1,‘正则查找’) --正则删除所有查找到的字符

regexp_replace(c1, '[^0-9]', '') --所有非数字, 替换为空

trim(c1) --去掉前后的空格

ltrim(c1) --去掉左边的空格

rtrim(c1) --去掉右边的空格

trim(‘#’from c1) --去掉c1两边的指定字符

ltrim(c1,‘#’) --去掉左边的指定字符,非首尾

rtrim(c1,‘#’) --去掉右边的指定字符,非首尾

--这三个函数,删除时非首尾,在中间也会被去掉,删除指定方向查询到的第一个指定字符,如果是连续多个,都会被删掉,

10.1.2 查找

正则表达式regexp_substr(c1 ,模式)

select regexp_substr(c1 , '^\d+') from t1;

--匹配出c1列开头的数字

获取指定字符的位置 instr

instr(c1,’a’) --返回a第一次在c1出现的位置instr(c1,’a’,3,4)

--从第3个字符开始,返回a第4次出现时的位置,

位置索引从1开始,不是0

regexp_instr --根据正则查找

查询字符的字符数/长度

length(c1)

查找字符出现的次数

regexp_count(c1, ‘ab’) --查询ab在c1出现的次数, 返回整型

regexp_count(c1, ‘ab’, 5, ‘i’) --从第5个位置开始查, 忽略大小写,

‘c’ 区分大小写

‘n’ 允许点号(.)作为通配符去匹配换行符。缺省不匹配换行符

‘m’ 将源串视为多行。即Oracle 将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或结束。如果省略该参数，则Oracle将源串看作一行。

‘x’ 忽略空格字符。默认情况下，空格字符与自身相匹配

10.1.3 替换

replace(c1, ‘a’,’b’) --c1中的a换成b

regexp_replace(c1, ‘正则查找’,’替换为新内容’) --正则替换

translate(c1,'零一二三四五六七八九','0123456789') --对应顺序替换

10.1.4 修改

截取/切片

substr(c1,3) --获取c1从第3个字符到末尾的字符c1[3:]

substr(c1,-2) --获取c1从最后2个字符, c1[-2:]

substr(c1,1,3) --获取c1前3个字符,索引从1计数,且包括右侧3

substr(c1,2,5) --获取c1第2到5处的字符,

填充

lpad(c1,10) --c1左边填充空字符,使总长度为10

rpad(c1,10,’0’) --右边填充数个字符0,使总长度为0

10.1.5 拆分列

select c1,

substr(c1,0, instr(c1, '-')-1) as c2

substr(c1, instr(c1, '-')+1) as c3

from t1;

--(以-符号拆分c1列, -之前c2, 之后c3)

10.1.6 判断处理 nvl,nvl2,coalesce

nvl(c1, 0) --c1存在,返回c1, 为null则返回0

nvl2(c1, 1,0) --c1存在,返回 1, 为null则返回0

coalesce(c1,c2,c3...) --c1存在,返回c1,否则c2,否则c3..

decode(c1,'金',1,'银',2,’铜’, 3, 0)

--如果字符串等于金返回1,银返回2,其他返回0,要完全相等

CASE

WHEN a>0 THEN --条件写在when后面

NULL; --

WHEN b=1 THEN --

NULL; --

ELSE --可选

NULL; --

END CASE; --case可省略

NULLIF(c1,c2) --判断是否相等,相等返回null,不等返回c1, 可用于判断 if a == 2:

ISNULL(c1); --判断c1是否为null,null返回1,否则返回0

10.1.7

10.2 数字函数

10.2.1 取整

ceil(c1) --向上取整

floor(c1) --向下取整

trunc(c1) --直接取整(舍弃小数部分)

round(c1) --对小数点后一位四舍五入取整

10.2.2 小数

trunc(c1,1) --保留一位小数

round(c1,1) --保留一位小数(对小数点后第二位四舍五入)

to_char(0.00001,’fm999990.99999’) --解决小数前面的0不显示

10.2.3 数学计算函数

abs(c1) --绝对值

mod(c1,y) --c1除以y取余

sign(c1) --判断正负,正数返回1,负数返回-1,0返回0

power(c1,y) --c1的y次方

10.2.4 判断

nanvl(a,0) 判断是否nan类型, a如果不是, 返回a; 否则返回0

10.2.5 随机数

num float;

SELECT trunc(dbms_random.value(0,10)) INTO num FROM dual;

生成整数

--生成0到10之间的整数, 不用trunc是浮点数

SELECT trunc(dbms_random.value(0,11)) FROM dual;

生成浮点数

--生成0到10之间的浮点数

SELECT dbms_random.value(0,11) FROM dual;

生成0-1之间小数

--生成0到1之间的浮点数

SELECT dbms_random.value() FROM dual

DBMS_RANDOM.VALUE()是随机产生( 0,1 )之间的数。

要产生两位的随机数，可以DBMS_RANDOM.VALUE()*100，这样产生( 0,100 )

的随机数，当产生( 0,10)之间的数时，只要加上10就可以保证产生的数都是两位了。

--随机日期

to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J')

2454084是时间戳

--获得指定日期的时间戳

select to_char(c1_date,'J') from dual

--随机字符串

dbms_random.string(opt, length)

--长度为20的随机数字串

substr(cast(dbms_random.value as varchar2(38)),3,20)

plsql中,需要定义为float类型

DECLARE

num float ;

BEGIN

SELECT trunc(dbms_random.value(0,25)) INTO num FROM dual;

DBMS_OUTPUT.PUT_LINE(num);

END;

10.3 日期函数

10.3.1 当前日期查询

sysdate --系统时间,年月日

current_date --会话时间,年月日

systimestamp --系统完整时间,精度比sysdate更高

localtimestamp --当前时区完整时间

current_timestamp --当前会话完整时间

dbtimezone --时区

10.3.2 日期提取

extract(year from c1_date) --从日期提取年

extract(month from c1_date) --从日期提取月

extract(day from c1_date) --从日期提取日

to_char( c1_date, 'hh24') --从日期提取小时

to_char( c1_date, 'mi') --从日期提取分

to_char( c1_date, 'ss') --从日期提取秒

trunc(c1_date) --

trunc(c1_date, ‘year’) --返回当年的第一天01-1月-2021

,’month’ --当月1号 01-7月

,’day’ --25号返回20, 17号返回7, 8号返回8

round(c1_date) --

round(c1_date,’year’) --日期4舍五入,7月1日前上半年

,’month’) --日期4舍五入,16日前上半月

,’day’) --日期4舍五入,12点前上半天

10.3.3 日期计算

months_between(c1, c2) --两个日期相差几个月

last_day(c1) --查询c1当月最后一天

next_day(c1, ‘星期一’) --查询指定日期后第一个周一的日期

add_months(x, 12) --当前时间加一年

add_months(x, 3) --当前时间加1个月 /-1 减1个月

c1_date + 1 --日期加一天

c1_date + 1/24 --日期加一小时

c1_date +1/24/60 --日期加一分钟,1/24/60*2(两分钟)

c1_date +1/24/60/60 --日期加一秒

10.4 类型转换函数

to_number(c1) --字符串转数字

to_char(c1) --日期/数字转字符串

to_char(c1, ’yyyy-mm-dd’) --日期/数字转指定格式字符串

to_date(c1) --字符串转日期

to_date(c1, ’yyyy-mm-dd hh24:mi:ss’) --字符串转指定格式的日期

cast(c1 as number) --c1转为指定数据类型

ascii(c1) --转为ascii码

chr(c1) --ascii转字符

upper(c1) --大写

lower(c1) --小写

initcap(c1) --每个单词首字母大写

cast(date as timestamp) --date类型与timestamp互转

to_timestamp(c1, ’yyyy-mm-dd hh24:mi:ss.ff’) --字符串转指定格式的

10.5 其他函数

第二部分:SQL编程

11. 结构/子程序

11.1 管理

查看过程/函数等的源码

select test from user_source where name=upper(‘过程名’)

#查询触发器

select * from all_triggers where trigger_name=upper(‘名字’);

删除

DROP PROCEDURE p1;

DROP FUNCTION f1;

11.2 匿名块(以DECLARE或BEGIN开始，每次提交都被编译)

[DECLARE] --可选, 定义变量

v_name VARCHAR2(5);

BEGIN

SELECT c1 INTO v_name FROM t1 WHERE id=1;

[EXCEPTION] -- 可选, 处理异常

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('未查到到任何数据');

WHEN OTHERS THEN

END;

11.3 存储过程

11.3.0.1 定义

CREATE [OR REPLACE] PROCEDURE test_name

(v_id in t1.id%type, v_name out varchar2)

--参数,可选. 格式:参数名 in/out/in out 数据类型

--无论什么类型,调用都需要传参,out和in out传参必须是变量

AS --即使不定义变量, is/as也不能省略

no_result exception; --定义变量, 类型为异常

abc varchar2(100); --

BEGIN

SELECT name INTO v_name FROM t1 WHERE id = v_id;

[EXCEPTION]

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('未查到到任何数据');

END [test_name]; --end过程名

11.3.1 调用

call test_name; --命令行调用

exec test_name(256); --命令行调用带参数的过程

test_name(256); --代码块内调用,不用call/exce

代码块内调用过程

DECLARE

v_name type;

BEGIN

test_name(256, v_name); --调用后,test_name里into语句的值传--递给v_name

dbms_output.put_line( v_name);

END;

11.4 函数

11.4.1 定义

CREATE [or replace] FUNCTION fun1

(v_id in t1.id%type, v_name out varchar2)

--参数,和过程一样,可选. 尽量只用in

RETURN varchar2 --返回值类型

prama UDF; --优化函数性能

v_name varchar2;

BEGIN

SELECT name INTO v_name FROM t1 WHERE id = v_id;

RETURN v_name;

[EXCEPTION]

WHEN NO_DATA_FOUND THEN

RETURN ('未查到到任何数据');

END [fun1];

方式二:

with FUNCTION fun1

--不加prama UDF;

11.4.2 调用

select fun1 from dual; --sql语句调用无参数函数

select fun1(5) from t1;

fun1(5); --代码块内调用

a := fun1(5);

11.5 包

11.6 触发器

11.6.1 管理

删除表时,对应触发器自动被删除

CREATE [or replace] TRIGGER trigger1 ... END; --创建

ALTER TRIGGER trigger1 DISABLED; --禁用

ALTER TRIGGER trigger1 ENABLE; --解禁

DROP TRIGGER trigger1 ; --删除

select * from all_triggers where trigger_name=upper(‘名字’);

11.6.2 创建DML触发器

#对有序列的表,实现列自增

CREATE OR REPLACE TRIGGER tri1

BEFORE/AFTER --在之前或之后触发

INSERT or UPDATE or DELETE --单个或多个,用or连接

[OF c1] --针对特定列

ON t1 --表名

FOR EACH ROW --对每一行都执行一次触发器,缺省只执行一次

[DECLARE]

BEGIN

:NEW.id := SEQ1.NEXTVAL; --:11g及以上版本

SELECT SEQ1.NEXTVAL INTO :NEW.id FROM dual; -旧版

:NEW.user := user; --获取当前用户并赋值

:NEW.date := sysdate; --获取当前时间并赋值

END [触发器名字];

--:new :old当前正在处理的行,可用点号引用某列

--:new用于insert,update

--:old 用于delete,update

11.6.3 instead of触发器

对复杂视图应用增删改操作

11.6.4 系统事件触发器

CREATE OR REPLACE TRIGGER tri1

BEFORE/AFTER --在之前或之后触发

startup | ddl --数据库启动停止,ddl等事件

ON DATABASE | SCHEA --数据库或方案

12. 变量与数据类型

12.1 基础类型变量定义

12.1.1 变量定义

定义在is 或 declare后面

v_var varchar2(20); --定义字符串变量

v_num number(5,3) := 12.125; --定义并赋值.共5位,小数后3位

v_name t1.c1%TYPE; --定义一个变量,类型和t1表的c1列类型相同

v_index PLS_INTEGER :=0; -- plsql专用,性能更好的整型

e1 excetpion; --自定义一个异常型变量

raise e1; --引用异常

12.1.2 变量赋值

查询赋值

select c1 into v_name from t1;

等号赋值

v_name := v_name || ‘_ha’

12.2 集合类型(数组)

12.2.1 关联数组(索引表)

用于初始为空的数组, 只能用于plsql,

定义

TYPE array_name1 IS TABLE OF number [not null] --创建数组类型

INDEX BY pls_integer; --指定下标的数据类型,pls_integer整型

--下标可以是字符串,varchar2(30), 类似于字典了,示例见下方

array1 array_name1; --定义数组型变量,初始为清空

使用

array(1); --通过下标引用元素

array(1) := ‘aloha’; --通过下标赋值

array(‘orange’) := ‘15元’; --通过字符串下标赋值

12.2.2 嵌套表

用于初始不为空的数组, 使用场景不限于plsql,可以用于数据表,索引只能是整型,因此不用定义索引类型,如果初始化为空值，则后续需要使用extend不断来扩展其大小.其他用法同关联数组

12.2.2.1 plsql中使用

DECLARE

TYPE array_name1 IS TABLE OF t1.c1%TYPE; --不用index by

--TYPE array_name1 IS TABLE OF varchar2(58);

array1 array_name1 := array_name1(‘abc, ‘bcd’);

--因为初始时数组为空NULL,定义时进行初始化

BEGIN

FOR i IN 1..array1.count LOOP

--FOR i IN array1.FIRST..array1.LAST LOOP

dbms_output.put_line(array1(i));

END LOOP;

END;

12.2.2.2 表里定义及使用

create type nest_tab_type is table of varchar2(30);

create table test_nest_tab(

id int,

vals nest_tab_type --使用

) nested table vals store as nest_tab;--vals字段用嵌套表存储，表明nest_tab

--上面语句执行完之后，在生成TEST_NEST_TAB的同时会生出一个关联表NEST_TAB用来存储关联表的数据

--插入数据

insert into test_nest_tab values(1,nest_tab_type('one','two','three','four'));

--查询数据

declare

v_id int;

v_tab nest_tab_type;

begin

select * into v_id,v_tab from test_nest_tab where id=1;

dbms_output.put_line(v_id);

for i in 1..v_tab.count loop

dbms_output.put_line(v_tab(i));

end loop;

end;

12.2.3 数组方法

count 返回集合中元素的个数

delete 删除集合中所有的元素及 extend

delete(x) 删除元素下标为 x 的元素（对 varry 非法）

delete(x, y) 删除元素下标从 x 到 y 的元素（对 varry 非法）

trim 从集合末端开始删除一个元素（对 index by 非法）

trim(x) 从集合末端开始删除 x 个元素（对 index by 非法）

exists(x) 如果集合元素 x 已经初始化(extend) ，则返回 true，否则返回 false

extend 在集合末尾添加一个元素（对 index by 非法）

extend(x) 在集合末尾添加 x个元素（对 index by 非法）

extend(x, n) 在集合末尾添加元素 x 个下标为n 的副本（对 index by 非法）

first 返回集合中第一个元素的下标号，对 varry 集合始终返回 1（除非未初始化则为空）

last 返回集合中最后一个元素的下标号，对 varry 集合值始终等于 count （除非未初始化则为空）

limit 返回 varry 集合的最大的元素个数，对 index by 无效

next(x) 返回在第 x 个元素之后紧挨着它的元素下标(x+1)，若 x 是最后一个元素，则返回 null

prior(x) 返回在第x个元素之前紧挨着它的元素下标(x-1)，如果 x 是第一个元素，则返回 null

12.2.4 变长数组varray

需要定义数组长度, 需要扩展, 扩展时不能超过最大长度,可用于数据表

使用在bulk collect into语句中时,不需要初始化和扩展,但还是要写最大值

定义

TYPE type_name IS VARRAY(长度) OF 数据类型[NOT NULL];

v_varr type_name :=type_name(); --初始化

表里定义

create type varr_type is varray(10) of varchar2(30);--先创建类型

create table test_varray(

id int,

name varchar2(30),

params varr_type --param是使用可变数组类型

);

--插入数据

insert into test_varray values(1,'bird',varr_type('a','b','c'));

12.2.5 多级集合

12.3 记录类型

12.3.1 基于表的记录类型

v_row t1%ROWTYPE; --用来存储t1的整条记录

select * into v_row from t1 where..;

v_row.c1; --用变量名.列名的方式引用某一列

12.3.2 基于游标的记录类型

CURSOR cursor1 IS SELECT..; --定义一个游标

cursor2 cursor1%ROWTYPE; --基于游标定义游标型变量

12.3.3 自定义记录类型

TYPE type_name1 IS RECORD --创建记录

(v1 date,

v2 number not null,

v3 char(1) defatult ‘0’);

type1 type_name1; --定义一个记录型变量

12.4 绑定变量

12.4.1 sql中

var x number;

exec :x :=7876;

select * from emp where empno = :x;

12.4.2 plsql中

execute immediate 使用绑定变量的语句 using 具体值;

declare

vc_name varchar2(10);

begin

execute immediate 'select ename from emp where empno= :1' into vc_name using 7876;

end;

PL/SQL中DML语句的绑定变量典型用法

declare

v_sql1 varchar2(50);

v_sql2 varchar2(50);

v_num1 number;

v_num2 number;

begin

v_sql1 := 'insert into emp(empno,ename,sal) values(:1,:1,:1)';

execute immediate v_sql1 using 8001,'jack',1000;

v_num1 :=sql%rowcount; -- sql%rowcount中的sql是oracle的内部游标,rowcount的意思是之前的dml sql语句影响的多少行数据

execute immediate v_sql1 using 8002,'mike',2000;

v_num2 :=sql%rowcount;

dbms_output.put_line(v_num1+v_num2);

end;

动态sql中也可以使用绑定变量，returning 这个关键字可以和带绑定变量的SQL联用，其作用是将受该SQL影响的行记录字段取出来。

三、pl/sql中批量绑定的典型用法

批量绑定：一次性处理一批数据

pl/sql引擎是指在Oracle在数据库中处理pl/sql代码中除了SQL语句外所有剩余部分（如变量，循环，数组，赋值）的子系统

批量绑定可以有效减少SQL引擎和pl/sql引擎的交互次数

理论上来说，pl/sql中只要执行SQL语句，就会有这两个引擎的交互，实际上这两个引擎交互产生的性能影响主要体现在以下两个方面

1、当显式游标或者参考游标需要循环执行fetch操作时，这时循环由pl/sql执行，fetch中的SQL由SQL引擎执行，这样每fetch一条记录，就需要两个引擎交互一次

2、当显式游标或者参考游标的循环内部需要执行SQL语句，跟上面一样，也是每执行一次，需要交互一次

所以当fetch一批记录，或者一次执行一批SQL，就会大大提高pl/sql的效率

批量fetch对应的语法

fetch cursorname bulk colletc into 【自定义的数组】 <limit cn_batch_size>

pl/sql中批量执行一批SQL的语法

forall i in 1..[自定义数组长度】

execute immediate [带绑定变量的sql] using [绑定变量输入值】

declare

cur_emp sys_refcursor;

v_sql varchar2(4000);

type namelist is table of varchar2(10);

enames namelist;

cn_batch_size constant pls_integer :=1000;

begin

v_sql:= 'select ename from emp where empno> :1';

open cur_emp for v_sql using 7900;

loop

fetch cur_emp bulk collect into enames limit cn_batch_size;

for i in 1..enames.count loop

dbms_output.put_line(enames(i));

end loop;

exit when enames.count<cn_batch_size;

end loop;

close cur_emp;

end;

四、绑定变量分级（bind graduation)

根据文本型绑定变量的定义长度而将这些绑定变量分成四级

1、32字节（bytes)以内第一级分配32字节

2、33--128字节第二级分配120字节

3、129--2000字节第三级分配2000字节

4、2000字节以上第四级，按实际绑定变量大小，小于等于2000则分配2000字节，大于2000则分配4000字节

注意 oracle只对文本型绑定变量分级，数值型的统一分配22字节，另外一个重要知识点，在pl/sql中，要是文本绑定变量的定义长度发生了变化，因为child cursor 里存储了该绑定变量的长度和类型，所以这个sql就会新做硬解析。因此，为了避免不必要的硬解析，在pl/sql中，定义文本绑定变量是，最好统一定义长度，比如varchar2(4000);

五、目标sql中的绑定变量不宜过多

六、如何得到已执行sql中绑定变量的值

V$SQL_BIND_CAPTURE，如果已经被aged out 除shared pool,则可以看另外两张dba_hist_sqlstat,dba_hist_sqlbind

满足下列条件之一，含有绑定变量的目标sql中的绑定变量值会被捕获，然后可以从v$sql_bind_capture中查询到

1、该sql做的是硬解析

2、该SQL做的是软解析或软软解析，这是每隔15分钟，捕获一次值

注意对绑定变量值的捕获只在where 语句中的绑定变量，其他比如insert中的值不会被捕获

12.4.3 编程语言

python(cx_oracle库)

方式一:

cursor.execute("insert into t1 (c1, c2) values (:1, :2)", [280, "Facility"])

方式二

cursor.execute("""insert into t1 (c1, c2) values (:dept_id, :dept_name)""", dept_id=280,dept_name="Facility")

方式三:

data = dict(dept_id=280, dept_name="Facility")

cursor.execute("""insert into t1 (c1, c2) values (:dept_id, :dept_name)""", data)

https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html?highlight=bind

13. 输入输出

输入

输出

DBMS_OUTPUT.PUT_LINE('hello');

14. 条件判断

14.1 if

14.1.1 if

IF a =b OR (a>c AND a<d) THEN

NULL; --占位语句,类似python中的pass

END IF;

14.1.2 if-else

IF a= 2 THEN --判断用一个等号

NULL; --

ELSE --else后不用then

NULL; --

END IF;

14.1.3 if-elsif

IF a<=10 THEN

ELSIF a<100 THEN

ELSIF a<10000 THEN

ELSE

END IF;

14.2 case

CASE a

WHEN 0 THEN

NULL; --

WHEN 1 THEN

NULL; --

ELSE --可选

NULL; --

END CASE; --

14.2.1 case搜索语句

CASE --区别这里不放参数

WHEN a>0 THEN --条件写在when后面

NULL; --

WHEN b=1 THEN --

NULL; --

ELSE --可选

NULL; --

END CASE; --

14.2.2 case表达式(赋值)

b :=CASE a --结果赋值给变量b

WHEN 0 THEN

NULL --语句不加分号

ELSE --可选

NULL --语句不加分号

END; --结尾不加case,分号只加在结尾

11.4.3

15. 循环控制

15.1 for循环

结构

<<label1>> --标签,可选,用于标记开始和结束,增加可读性

FOR i IN 1..5

LOOP

NULL; --从1到5,包括5

END LOOP label1; --标签,可选

选项

FOR i IN REVERSE 1..5 --倒序,从5到1计数

15.2 跳转

EXIT WHEN i =3; --满足条件退出本级循环

CONTINUE WHEN i=3; --满足条件跳出本次循环

continue; --跳出本次循环

exit; --跳出本级循环

return ; --跳出整个函数或

<<label1>>

goto label1; --跳转到指定标签处,跳转位置需要有一条可执行语句

15.3 while循环

WHILE cursor1%FOUND LOOP --一.游标中有数据时执行

NULL; --

END LOOP; --二.结束循环

16. 游标

游标一次只读取一行,可以用bulk collect批量读取

16.1 游标结构

AS/DECLARE --根据是否匿名块决定

v_name varchar2(20);

v_num number;

CURSOR cursor1 IS SELECT c1,c2 FROM t1; --1.定义一个游标

BEGIN

OPEN cursor1; --2.打开游标

FETCH cursor1 INTO v_name,v_num; --3.从游标中取出数据

WHILE cursor1%FOUND LOOP --一.游标中有数据时执行

--执行相应语句

FETCH cursor1 INTO v_name,v_num; --3.从游标中取出数据

END LOOP;

CLOSE cursor1; --4.关闭游标

END;

16.2 游标属性

CURSOR cursor1 IS SELECT... --定义游标

IF cursor1%ISOPEN --游标打开返回TRUE

cursor1%ROWCOUNT --返回当前读取到了游标中的第几条记录

cursor1%FOUND --游标中有内容返回TRUE

cursor1%NOTFOUND --游标为空返回TRUE

16.3 游标遍历

不用打开关闭和判断退出条件,不用定义其他变量

方式一:

CURSOR cursor1 IS SELECT * FROM t1;

BEGIN

FOR i IN cursor1 LOOP

insert into t2 values(i.c1,i.c2); --直接引用

END LOOP;

commit;

END;

示例：

DECLARE

CURSOR cur1 IS SELECT GROUP_NAME FROM t1;

id_n NUMBER := 70010001;

BEGIN

FOR i IN cur1 loop

UPDATE t1 SET id = ID_N WHERE GROUP_NAME =i.group_name;

id_n := id_n+1;

END LOOP ;

COMMIT;

END;

--某个值改为随机数

DECLARE

CURSOR cur1 IS select id from t1 ;

date1 NUMBER := 20211101;

date2 NUMBER ;

num float;

BEGIN

FOR i IN cur1 LOOP

SELECT trunc(dbms_random.value(0,10)) INTO num FROM dual;

date2 := date1 + num;

--DBMS_OUTPUT.PUT_LINE(date2);

UPDATE t1 SET 数据日期 = date2 WHERE id =i.id;

END LOOP ;

COMMIT;

END;

方式二:

不定义游标,select 语句直接写在for循环里

FOR i IN (select ..) LOOP

16.4 参数游标

AS/declare

CURSOR cursor1(v_id number) --不能指定精度number(8)

IS SELECT * FROM t1 WHERE id = v_id;

BEGIN

OPEN cursor1(256); --打开时传参

16.5 游标变量

16.6 游标表达式

17. 动态sql

declare

v_col_name varchar2(30) := ’name’; --字段名 name 用变量来表示

v_user_name varchar2(30); --用户名称

v_user_age integer; --用户年龄

v_sql_str varchar2(500); --动态 SQL 语句

begin

v_sql_str := ’select ’||v_col_name||’,age from users --字段名后面不能紧随 into 到变量了

where age between :start_age and :end_age and rownum=1’; --两个绑定变量

--用 execute immediate 动态执行 SQL 语句

--注意其后的 into 字段值到变量的写法，还有 using 来代入参数

execute immediate v_sql_str into v_user_name,v_user_age using 18,25;

dbms_output.put_line(’第一个符合条件的用户：’||v_user_name||’，年龄：’||v_user_age);

end;

除此之外，在 Oracle 8i 及以上版本中，还能用 DBMS_UTILITY.EXEC_DDL_STATEMENT(ddl_sql_str) 执行 DDL 语句。

18. 批量sql

18.1 批量读取bulk collect

18.1.1 select bulk collect

declare

--定义索引表集合存储emp表中empno和ename

type i_empno is table of emp.empno%type index by pls_integer;

type i_ename is table of emp.ename%type index by pls_integer;

--定义索引变量

eno i_empno;

eme i_ename;

begin

--bull collect语句获取empno和ename

select empno,ename bulk collect into eno,eme from emp where deptno=30;

for i in eno.first..eno.last loop

dbms_output.put_line(eno(i)||eme(i));

end loop;

end;

18.1.2 fetch 游标

FETCH 游标名 BULK COLLECT INTO 集合名 LIMIT 1000;

--从游标里一次读取1000条记录到集合,单列游标,多列需要用基于游标的记录类型

18.2 批量执行forall

一次生成多条语句, 批量发送到oracle, forall中只能有一条语句, 变量都用数组类型, 不用loop

18.2.1 定义

FORALL index_name IN

{ lower_bound .. upper_bound

| INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ]

| VALUES OF index_collection

}

[ SAVE EXCEPTIONS ] dml_statement;

index_name：一个无需声明的标识符，作为集合下标使用。

lower_bound .. upper_bound：数字表达式，来指定一组连续有效的索引数字下限和上限。该表达式只需解析一次。

INDICES OF collection_name：用于指向稀疏数组的实际下标。跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值。

VALUES OF index_collection_name：把该集合中的值当作下标，且该集合值的类型只能是 PLS_INTEGER/BINARY_INTEGER。

SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。

dml_statement：静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML语句。

使用FORALL时，应该遵循如下规则：

FORALL语句的执行体，必须是一个单独的DML语句，比如INSERT，UPDATE或DELETE。
不要显式定义index_row，它被PL/SQL引擎隐式定义为PLS_INTEGER类型，并且它的作用域也仅仅是FORALL。
这个DML语句必须与一个集合的元素相关，并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。
lower_bound和upper_bound之间是按照步进 1 来递增的。
在sql_statement中，不能单独地引用集合中的元素，只能批量地使用集合。
在sql_statement中使用的集合，下标不能使用表达式。

18.2.2 示例

--1.批量插入

DECLARE

-- 定义索引表类型

TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;

tb_table tb_table_type;

BEGIN

FOR i IN 1..100 LOOP

tb_table(i).id:=i;

tb_table(i).name:='NAME'||i;

END LOOP;

FORALL i IN 1..tb_table.count

INSERT INTO tmp_tab VALUES tb_table(i);

commit;

END;

--2.批量修改

DECLARE

TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;

tb_table tb_table_type;

BEGIN

FOR i IN 1..100 LOOP

tb_table(i).id:=i;

tb_table(i).name:='MY_NAME_'||i;

END LOOP;

FORALL i IN 1..tb_table.count

UPDATE tmp_tab t SET row = tb_table(i) WHERE t.id =tb_table(i).id;

commit;

END;

--3.批量删除

DECLARE

TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;

tb_table tb_table_type;

BEGIN

FOR i IN 1..10 LOOP

tb_table(i).id:=i;

tb_table(i).name:='MY_NAME_'||i;

END LOOP;

FORALL i IN 1..tb_table.count

DELETE FROM tmp_tab WHERE id =tb_table(i).id;

commit;

END;

18.2.3

19. 异常

19.1

20.

posted @ 2021-10-15 09:54 化身天使阅读(136) 评论(0) 收藏举报

刷新页面返回顶部

化身天使的博客

化身天使

关系数据库与Sql

关系数据库与Sql

1. 数据库连接

1.1 Oracle

1.1.1 命令行连接

1.1.2 cx_oracle

1.2 mysql

1.2.1 命令行连接

1.2.2 pymysql

2. 数据库管理

2.1 用户管理oracle

2.1.1 创建用户

2.1.2 修改用户

2.1.3 查看用户

2.1.4 删除用户

2.2 用户管理mysql

2.3 权限管理

2.3.1 权限查询

2.3.2 权限授予

2.3.3 mysql

2.4 外部库dblink

2.4.1 查询

2.4.2 创建

2.4.3 修改

2.4.4 删除

2.5 容器（cdb, pdb）

2.5.1 查询

2.6 事务

2.7 定时任务

2.7.1 创建

2.7.2 查询

2.7.3 启动

2.7.4 停止

2.7.5 删除

2.7.6 修改

2.7.7 时间参数说明

2.7.8 job参数说明

3. 表操作

3.1 建表,视图,表空间

3.1.1 表空间

3.1.2 建表

3.1.3 视图

3.2 新增

3.3 删除

3.4 修改

3.5 查询

4. 数据类型

5. 插入行

5.1 基础插入

5.2 从另一个表查询数据插入

5.2.3 插入不存在的数据(表2有重复数据会出错)

6. 删除行

7. 更新列

7.1 基础更新

7.2 用一个表更新另一个表

7.2.1 merge into

7.2.2 exists

8. 数据库及表信息查询

8.1 数据库查询

8.2 表查询

9. 查询select

9.1 去重

9.2 模糊查询

9.3 限制返回行数

9.4 日期查询

9.5 表连接与子查询

9.5.1 表连接

9.5.2 嵌套子查询

9.5.3 相关子查询(子查询中where子句有连接)

9.6 分组查询与聚合函数

9.7 操作符

9.7.1 BETWEEN区间:

9.7.2 any all

9.8 查询结果进行编号或组内编号

9.8.1 rank() over

9.8.2 dense_rank() over

9.8.3 row_number() over

9.9 查询结果多行转为一行