mysql

oracle官方文档

《oracle编程艺术》，有深度

生产环境推荐使用percona

恒生学习

参数文件默认路径

• /etc/my.cnf等

参数分组

[mysqld]

[mysql_safe]：mysqld的守护进程

[client]

[mysqldump]：导数据

隔离级别

重要参数

• character_set_server，默认latin1
• collation_server，默认latin1_swdish_ci，根据字符集自动改变
• 8.0，字符集和排序规则需要添加,init_connect = “SET NAMES ‘utf8mb4’ COxxx”
• innodb log file

用户管理

忘记密码了：

--- 5.7之后的，需要写mysql-init
mysqld --init-file=/home/me/mysql-init &

备份

--- 全库
mysqldump -uroot -pxxx -A -F --single-transaction --triggers --events --routines > full_backup.sql

--- 指定数据库
mysqldump -uroot -pxxx -B db1,db2 -A -F --single-transaction --triggers --events --routines > db_backup.sql

-- 导出指定表
mysqldump -uroot -pxx -F -q --single-transaction db1 test > test.sql

--- 客户端导入数据
mysql > source db_backup.sql

--- 操作系统导入
mysql -uroot -pxxx < xxx.sql

表碎片

产生原因：

• 删除一行数据，该行数据不会立即释放，而是编程空白空间
• 插入数据时，尝试使用空白空间，数据小于空白空间，则形成碎片

解决方案

1. optimize table tableName
2. alter table tableName engine = innodb；

清理碎片会暂时锁表，数据量大时，时间长

统计信息收集

手动收集：analyze table tableName

自动收集：

1. 第一次打开表
2. 新的记录插入
3. 表修改行超过1/6或20亿条

开启参数 innodb_stats_on_metadata

1. 执行show table status
2. 访问information_schema下的元数据表

binlog日志管理

-- 存在哪些binlog
show master/binary logs;
-- 清除
purge binary logs before 'yyyy-mm-dd hh24:mi:ss'
-- 自动清理
--- 设置expire_logs_days

冗余的索引

select table_schema, table_name, redundant_index_name, redundant_index_columns
from sys.schema_redundant_indexes;

常见信息查询

--- 线程列表
show processlist;
--- 复制状态
show slave status;
--- 最大连接数
show global status like 'max_used_connections'

表设计优化

自增ID或unsigned整型作为主键

禁止用外键，让应用实现

建议字段not null

不存放text、blob等大字段

金钱相关字段使用decimal

禁止存储过程，触发器、函数、视图、事件等高级功能

sql优化

慢查询日志设置

• slow_query_log：慢查询是否开启

• long_query_time：超过多长时间记录

• slow_query_log_file：慢查询文件路径和名称

• log_queries_not_using_indexes：是否有没使用索引的SQL

分析慢查询日志，mysqldumpslow

-- 执行次数最多的5条
mysqldumpslow -s -c -t 5 /mylog/my3306/slow.log
-- 返回记录最多的
mysqldumpslow -s -r -t 5 /mylog/my3306/slow.log
-- 执行时间最长的
mysqldumpslow -s t -t 5 /mylog/my3306/slow.log

语句优化

explain

• id：执行顺序
• table，输出结果集的表
• type，表的访问路径，连接类型
• filterd，过滤百分比
• rows扫描出的行数
• possible_key，可能使用的索引
• key，实际使用的索引
• ref，进行关联的字段

• 禁止使用 select *
• 所有表有主键
• 禁止使用find_in_set、instr函数
• 生产状态下禁止大事务，要求单事务数据控制在10万条以内

---
select xxx limit 50000,5000;

--- 优化
select *
from (select no from xxx limit 250000,5000) b, employees a
where a.xxx = b.xxx

--- union 优化
union all

--- union 去重并排序，union all 直接返回合并的结果，不去重也不排序
--- union all比 union 性能好

乱码

存入和取出编码不一致

两个字符集之间无法无损装换

解决：

• 确保客户端编码格式
• mysql编码格式

字段类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

decimal

• 非精准浮点：float,double
• 精准浮点：decimal

占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字

小数点占用一个字节

可用于存储比 bigint 更大的整型数据

字符串类型

CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

日期

DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

TIMESTAMP、DATETIME

使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间

TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

使用字符串

• 缺点 1：无法用日期函数进行计算和比较
• 缺点 2：用字符串存储日期要占用更多的空间

SQL

展示警告

show warnings;

基本CRUD

select * 
from table1
[where Clause]
[limit N][ OFFSET M];

update table1 
set name = ''
[where ];

insert into table1(name, age) 
            values('ming', 12);

delete from table1 

连接

(left join) left outer join 即使右边为空，也保留左边的

inner join 只保留on xxx一样的交集

连接更新

https://blog.csdn.net/u012604745/article/details/80642015

UPDATE student s , class c 
SET s.class_name='test00',c.stu_name='test00' 
WHERE s.class_id = c.id

UPDATE student s 
  JOIN class c ON s.class_id = c.id 
SET 
  s.class_name='test11',
  c.stu_name='test11'

UPDATE student s 
  LEFT JOIN class c ON s.class_id = c.id 
SET 
  s.class_name='test22',
  c.stu_name='test22'

多数据库

oracle中不允许联表更新，需要使用游标，https://www.jianshu.com/p/61717c19c213或者update set xxx = (select xxx )

mysql中可以用left join where

postgresql可以用from where

<update id="" parameterType="java.lang.String" databaseId="oracle">
    BEGIN
        FOR C_CURSOR IN (
          SELECT A.ROWID RID, B.VC_FUND_ACCO
          FROM ${tableName} A, B
          WHERE B.VC_TENANT_ID = #{tenantId,jdbcType=VARCHAR}
          AND B.VC_DS_FUND_ACCO = A.VC_FUND_ACCO
          AND A.VC_TENANT_ID = #{tenantId,jdbcType=VARCHAR}
        )
        LOOP
          UPDATE ${tableName} T
          SET T.VC_FUND_ACCO = C_CURSOR.VC_FUND_ACCO
          WHERE T.ROWID = C_CURSOR.RID;
        END LOOP;
    END;
</update>
UPDATE ${tableName} A
SET A.VC_FUND_ACCO = (SELECT B.VC_FUND_ACCO
                      FROM B
                      WHERE B.VC_TENANT_ID = #{tenantId,jdbcType=VARCHAR}
                      AND B.VC_DS_FUND_ACCO = A.VC_FUND_ACCO
                      AND A.VC_TENANT_ID = #{tenantId,jdbcType=VARCHAR}

<update id="" parameterType="java.lang.String" databaseId="mysql">
    UPDATE ${tableName} T,  A
    SET T.VC_FUND_ACCO = A.VC_FUND_ACCO
    WHERE T.VC_TENANT_ID = A.VC_TENANT_ID
    AND T.VC_FUND_ACCO = A.VC_DS_FUND_ACCO
    AND T.VC_TENANT_ID = #{tenantId,jdbcType=VARCHAR}
</update>


<update id="" parameterType="java.lang.String" databaseId="postgresql">
    UPDATE ${tableName} T
    SET T.VC_FUND_ACCO = A.VC_FUND_ACCO
    FROM A
    WHERE T.VC_TENANT_ID = A.VC_TENANT_ID
    AND T.VC_FUND_ACCO = A.VC_DS_FUND_ACCO
    AND T.VC_TENANT_ID = #{tenantId,jdbcType=VARCHAR}
</update>

判断且插入

insert into TSYSPARAMETER ()
select 'UPS'
from dual where not exists (
    select * 
    from TSYSPARAMETER 
    where C_CLASS='UPS' 
    and C_ITEM='BANK_SALE_PAY_FILE_PATH'
);

结构设置

CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;
DROP DATABASE 库名;
DROP TABLE 表名;
DELETE FROM 表名;

# 建表实例
　create table teacher
　　(
      id int(3) auto_increment not null primary key,
      name char(10) not null,
      address varchar(50) default '深圳',
      year date
  );

# 导入数据库
use tableName;
source D:\library.sql

mysql -uroot -p123456 < runoob.sql

# 导出数据库
mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt database_name > outfile_name.sql

用户管理

https://blog.csdn.net/u014453898/article/details/55064312

1.查看用户

use mysql;
select * from user;

2.创建用户

CREATE USER  'user_name'@'host'  IDENTIFIED BY  'password';
/*创建并授权*/
GRANT ALL PRIVILEGES ON *.* TO 'mingyue'@'%'IDENTIFIED BY 'mypassword' WITH GRANT OPTION; 
flush privileges;

host：本机登陆‘localhost’ ，远程登陆‘%’

# 只允许本机登陆
CREATE USER  'aaa'@'localhost' IDENTIFED BY '123456';          
# 远程登录
CREATE USER  'bbb'@'%' IDENTIFED BY '123456';

# 创建新用户
CREATE USER  'aaa'@'%'; 
# 查看用户列表
select * from user;

# 如果添加用户无效
delete from user where user='';
flush privileges;

use mysql
update user set host ='%'where user ='root' and host ='localhost';
flush privileges;


# 修改密码
SET PASSWORD FOR root@'%'=PASSWORD('123qwe'); #root

可以看出 有 aaa用户了。但是可以看到 aaa 的权限很多都是 N，表示没有权限，因为还要为用户授予权限。

3.授权用户

GRANT privileges ON  databasename.tablename  TO  'user_name'@'host'
# 创建授权
GRANT ALL PRIVILEGES ON *.* TO 'mingyue'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; 

GRANT  select, insert  ON  database.table  TO  'aaa'@ '%'；  

privileges：表示要授予什么权力，

• select ， insert ，delete，update等
• 如果要授予全部权力，则填 ALL

databasename.tablename

• 表示用户的权限能用在哪个库的哪个表中
• 能操作所有的数据库所有的表，则填 *

4.删除用户

DROP  USER 'user_name'@'localhost' ;
# 表示删除用户mingyue
DROP USER 'mingyue'@'%'; 

5.设置与更改用户密码

SET  PASSWORD  FOR  'username'@'host' = PASSWORD('newpassword');
# 如果是设置当前用户的密码：
SET  PASSWORD = PASSWORD('newpassword')；
SET  PASSWORD = PASSWORD(‘123456’)；

6.撤销用户权限：

REVOKE privileges ON database.tablename FROM 'username'@'host';
REVOKE SELECT ON  *.*  FROM  'zje'@ '%'

撤销权利范围需要注意，要和原权利一致

数据库无法远程

# 添加用户权限后
delete from user where user=''; # 否则用户可能无法登录
flush privileges;

vim /etc/mysql/my.cnf
# 添加
skip-name-resolve
skip-external-locking # 如果无效，也能添加

# 注释掉，否则无法远程连接
skip-networking
# 指定IP能够访问数据库
bind-address=192.168.1.100
bind-address=0.0.0.0 # 或者直接注释

错误信息

show warnings;

函数

日期

最近日期

date(now()) DATE_ADD()

-- 今天的
select count(*)
from goods
where release_time >= date( now() )
  and release_time<DATE_ADD(date(now()), INTERVAL 1 DAY);

SELECT * FROM borrow bb WHERE DATE_FORMAT(bb.create_time, '%Y%m%d') = DATE_FORMAT(CURRENT_DATE(), '%Y%m%d' );

SELECT * FROM borrow bb WHERE TO_DAYS(bb.create_time) = TO_DAYS(now());
-- 昨天
SELECT * FROM borrow bb WHERE TO_DAYS( NOW( ) ) - TO_DAYS( bb.create_time) =1;
-- 近7天
SELECT * FROM borrow bb WHERE TO_DAYS( NOW( ) ) - TO_DAYS( bb.create_time) <= 7;
SELECT bb.borrow_no,bb.create_time FROM borrow bb where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(bb.create_time);

-- 走索引的
and create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)

-- 本月
SELECT * FROM borrow bb WHERE DATE_FORMAT( bb.create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );

-- 上个月
SELECT * FROM borrow bb WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( bb.create_time, '%Y%m' ) ) =1

时间戳

-- 返回当前时间的时间戳
select unix_timestamp();
-- from_unixtime（tt,‘yyyy-MM-dd’） 可以加上时间格式
select from_unixtime(1543943400);

时间

-- 2018-09-11 17:53:06
select NOW();
-- 2018-09-11
select CURRENT_DATE()

分组

group_concat(score)

SELECT id, GROUP_CONCAT( DISTINCT  score SEPARATOR '; ' ORDER BY score asc )
FROM testgroup GROUP BY id

id score

1 99;100;123

排序

--- 查询到的数据排序，加序号
select row_number() over( 
            order by socre desc 
        ) `rank` 
        , *
from student

--- 分数一样的，相同排名，但是跳跃排序
select rank() over(
        order by score desc
    )  `rank`
    , *
from studnet

---分 数一样的，相同排名，顺序排序
select dense_rank() over(
        order by score desc
    ) `rank`
    , *
from student

字符串函数

函数名	解释
lower(column|str)	全转小写
upper(column|str)	大写
concat(column|str，column|str)	连接
substr(str, pos [,len])	从pos取len长度（默认到尾），pos是位置
instr(str, substr)	从str找substr第一次出现的位置，大于0为包含（其返回是位置 不是下标）
trim(str)，ltrim(str)， rtrim(str)	去除空格
lpad(str, n, pad)	用字符串pad对str最左边进行填充， 直到长度为n个字符长度
replace(str, a, b)	将字符串str中的a更换为b
strcmp（s1, s2）	比较字符串s1, s2。0一样，小于为-1

并发

锁

for update

悲观锁（适合写多），在用户A获取获取 id=1 的商品信息时对该行记录加锁，期间其他用户阻塞等待访问该记录

乐观锁（适合读多），每次获取商品时，不对该商品加锁。在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等，如果相等则进行更新，反之程序重新获取库存量，再次进行比较，直到两个库存量的数值相等才进行数据更新

innoDb默认是行级别锁，能使用到索引，是行级锁，否则是表级别

for update仅仅使用innodb必须开启事务，在begin、commit之间生效

获取到互斥锁后，只有事务提交或失败后才会释放

避免回滚情况如读未提交：A事务获取了T1的锁，进行修改未提交，B事务也获取了T1的值，也进行修改，如果A事务完成提。交，就没问题，但是如果A事务回滚，B获取的就是脏数据。

其他

序列号

创建

-- 20221227 yixh14641 T202212203994-1 大额限制 新增序号oralce
DECLARE
ICOUNT INTEGER := 0;
BEGIN
    SELECT COUNT(1) INTO ICOUNT FROM USER_SEQUENCES T
    WHERE T.SEQUENCE_NAME = UPPER('QLRLIMIT');
    IF ICOUNT = 0 THEN
        EXECUTE IMMEDIATE 'create sequence QLRLIMIT
                minvalue 1
                maxvalue 999999999
                start with 1
                increment by 1
                nocache';
    END IF;
END;
/

-- 20221227 yixh14641 T202212203994-1 大额限制 新增序列mysql
delimiter //
drop procedure if exists my_test_proc;
create procedure my_test_proc()
begin
    declare v_rowcount numeric(5);
    select count(*) into v_rowcount from dual
    where exists (select 1 from tc_tsequence where NAME = 'QLRLIMIT');
    if v_rowcount = 0 then
        insert into tc_tsequence (VC_TENANT_ID, VC_SERIAL_TYPE, NAME, CURRENT_VALUE, INCREMENTS)
        values ('10000', 'DB_REDIS_RANGE', 'QLRLIMIT', 1, 1);
    end if;
end;
call my_test_proc();
//

-- 20221227 yixh14641 T202212203994-1 大额限制 新增序号
if (SELECT COUNT(1) FROM information_schema.sequences WHERE upper(sequence_name) = 'QLRLIMIT') = 0
then
    CREATE SEQUENCE QLRLIMIT INCREMENT 1 MINVALUE 1 MAXVALUE 9999999999 START 1 CACHE 1;
end if;

DECLARE
  V_ROWCOUNT INTEGER;
BEGIN
  SELECT COUNT(1)
    INTO V_ROWCOUNT
    FROM USER_SEQUENCES
   WHERE SEQUENCE_NAME = 'QUPLOADFILEID';
  IF V_ROWCOUNT = 1 THEN
    EXECUTE IMMEDIATE 'DROP SEQUENCE QUPLOADFILEID';
  END IF;
END;
/
DECLARE
  FILE_ID NUMBER ;
BEGIN
  SELECT NVL(MAX(L_ID),0)+1 INTO FILE_ID FROM TC_TUPLOADFILE;
  EXECUTE IMMEDIATE 'CREATE SEQUENCE QUPLOADFILEID MINVALUE 1 NOMAXVALUE START WITH '||FILE_ID||' INCREMENT BY 1 CACHE 20';
END;
/

序列号修改

方式一：修改

-- 修改递增量为n，达到期望的值
alter sequence seq_t_test increment by n;
-- 取一次，就能获取到x+n的了
select seq_t_test.nextval from dual;
-- 修改为1增量
alter sequence seq_t_test increment by 1;

方式二：删除再增加

drop sequence seq_t_test;
create sequence seq_t_test
increment by 1
start with 1;

序列号如何生成？如果生成了不使用，会怎样？

• oracle自带、mysql通过自建表实现
• 获取了就会递增

对比

oracle中，序列号能够自带

mysql中，需要通过表来控制

-- 更新并获取序列号
CREATE DEFINER=`root`@`%` FUNCTION `NEXT_VAL`(seq_name VARCHAR(50)) RETURNS decimal(32,0)
BEGIN
    UPDATE TC_TSEQUENCE
    SET current_value = current_value + increments
    WHERE name = seq_name;
    RETURN CURRENT_VAL(seq_name);
END
-- 获取当前序列号
CREATE DEFINER=`root`@`%` FUNCTION `CURRENT_VAL`(seq_name VARCHAR(50)) RETURNS decimal(32,0)
BEGIN
    DECLARE value Integer;
        SET value = 0;
   SELECT current_value INTO value
    FROM TC_TSEQUENCE
    WHERE name = seq_name;
    RETURN value;
END
-- 序列号索引表，current_value，increments
-- 唯一键 `NAME`, `VC_SERIAL_TYPE`, `VC_TENANT_ID`

程序中通过SequenceFactoryBean工厂控制获取mysql还是oracle的序列器

序列器接口的方法有添加、删除、获取下一个值

<select id="selectSerialNo" resultType="java.lang.Integer" databaseId="mysql">
    SELECT CONVERT(NEXT_VAL('QPRODUCTSTATENO'), SIGNED)
</select>
<select id="selectSerialNo" resultType="java.lang.Integer" databaseId="oracle">
    SELECT TO_NUMBER(QPRODUCTSTATENO.nextval) from dual
</select>
<select id="selectSerialNo" resultType="java.lang.Integer" databaseId="postgresql">
    SELECT (nextval('QPRODUCTSTATENO'))
</select>

序列号脚本

序列号脚本

oracle

-- 2023/02/15 T202302144211 异步复核，产品状态预设置使用序列号 sumy45276
DECLARE
  V_ROWCOUNT INTEGER;
BEGIN
  SELECT COUNT(1)
    INTO V_ROWCOUNT
    FROM USER_SEQUENCES
   WHERE SEQUENCE_NAME = 'QPRODUCTSTATENO';
  IF V_ROWCOUNT = 1 THEN
    EXECUTE IMMEDIATE 'DROP SEQUENCE QPRODUCTSTATENO';
  END IF;
END;
/
DECLARE
  PRODUCT_STATE_NO NUMBER ;
BEGIN
  SELECT NVL(MAX(L_PRODUCT_STATE_NO),0)+1 INTO PRODUCT_STATE_NO FROM TC_TPRODUCTSTATESCHEDULE;
  EXECUTE IMMEDIATE 'CREATE SEQUENCE QPRODUCTSTATENO MINVALUE 1 NOMAXVALUE START WITH '||PRODUCT_STATE_NO||' INCREMENT BY 1 CACHE 20';
END;
/

mysql

-- 2023/02/15 T202302144211 异步复核，产品状态预设置使用序列号 sumy45276
delimiter //
drop procedure if exists my_test_proc;
create procedure my_test_proc()
begin
    declare v_rowcount numeric(5);
    declare v_maxno int;
    select count(*) into v_rowcount from dual
    where exists (select 1 from tc_tsequence where NAME = 'QPRODUCTSTATENO');
        SELECT ifnull(MAX(L_PRODUCT_STATE_NO),0)+1 INTO v_maxno FROM TC_TPRODUCTSTATESCHEDULE;
    if v_rowcount = 0 then
        insert into tc_tsequence (VC_TENANT_ID, VC_SERIAL_TYPE, NAME, CURRENT_VALUE, INCREMENTS)
        values ('10000', 'DB_REDIS_RANGE', 'QPRODUCTSTATENO', v_maxno, 1);
    end if;
end;
call my_test_proc();
//

postgresql

-- 2023/02/15 T202302144211 异步复核，产品状态预设置使用序列号 sumy45276
do
$$
declare v_rowcount int default 0;
declare v_maxno int default 0;
declare sqltxt text;
begin
    SELECT COUNT(1) INTO v_rowcount FROM information_schema.sequences WHERE upper(sequence_name) = 'QPRODUCTSTATENO';
    SELECT coalesce(MAX(L_PRODUCT_STATE_NO),0)+1 into v_maxno  FROM TC_TPRODUCTSTATESCHEDULE;
    if v_rowcount = 0 then
        sqltxt := concat(concat('CREATE SEQUENCE QPRODUCTSTATENO 
        INCREMENT 1
        MINVALUE  1
        MAXVALUE 9999999999
        START ', v_maxno), ' CACHE 20;'); 
        execute sqltxt;
    end if;
end;
$$;

nocache

https://www.cnblogs.com/friday69/p/9999332.html

序列是一数据库对象，利用它可生成唯一的整数。一个序列的值是由特别的Oracle程序自动生成。
如果不设定cycle循环的话，每一个序列号是唯一的。 当一个序列号生成时，序列是递增

当使用到序列的事务发生回滚。会造成序列号不连续

cache的作用：当大量语句发生请求，申请序列时，为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成 cache x个先存入内存，
在发生大量申请序列语句时，可直接到运行最快的内存中去得到序列。但cache个数也不能设置太大，因为在数据库重启时，会清空内存信息，预存在内存中的序列会丢失，
当数据库再次启动后，序列从上次内存中最大的序列号+1 开始存入cache x个。
比如你的sequence中cache 100，那当你sequence取到90时突然断电，那么在你重启数据库后，sequence的值将从101开始。

NOCACHE

1. 创建nocache sequence在高并发访问时，容易导致row cache lock等待事件，主要原因是每次获取nextval时都需要修改rowcache中的字典信息
2. 由于每次修改字典信息都需要commit,可能导致log file sync等待，nocache sequence在RAC环境下，会对基于sequence生成的列创建的索引造成实例间大量索引块争用

基于以上问题,避免创建nocache sequence。

忘记密码

https://developer.aliyun.com/article/792438

忽略表大小写

/etc/my.cnf

[mysqld]

lower_case_table_names=1

重启

Row size too large (＞ 8126)

[mysqld]
innodb_strict_mode = 0
max_allowed_packet = 1G
innodb_log_file_size = 512M
innodb_log_buffer_size = 512M

健康状态

连接数

# 更为全面的进程信息
SHOW   full    processlist;
# 　查看当前失败连接数
SHOW GLOBAL  STATUS   LIKE   'aborted_connects'
# 由于客户没有正确关闭连接而死掉的连接数
SHOW GLOBAL  STATUS   LIKE   'aborted_clients'
# 最大连接数
show variables like '%max_connections%';
# 线程数需要在一个合理的范围之内
show global status like 'Thread%';
# 如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。

修改

show variables like '%max_connections%';
set global max_connections=1000
# /etc/my.cnf
[mysqld]
max_connections = 1000
thread_cache_size = 64 

# 忽略表大小写
lower_case_table_names=1

连接控制

show variables like '%timeout%';

connect_timeout    10
delayed_insert_timeout    300
have_statement_timeout    YES
innodb_flush_log_at_timeout    1
innodb_lock_wait_timeout    50
innodb_rollback_on_timeout    OFF
interactive_timeout    28800
lock_wait_timeout    31536000
net_read_timeout    30
net_write_timeout    60
rpl_stop_slave_timeout    31536000
slave_net_timeout    60
wait_timeout    28800

• connect_timeout：默认为10S，在获取连接阶段（authenticate）起作用
• wait_timeout：默认是8小时，即28800秒，在连接空闲阶段（sleep）起作用
• interactive_timeout：默认是8小时，即28800秒，在连接空闲阶段（sleep）起作用
• net_read_timeout：默认是30S
• net_write_timeout：默认是60S

死锁

SHOW   ENGINE   INNODB    STATUS

存储过程

结构

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

-- 结束符号
DELIMITER $$
或
DELIMITER //

-- 声明
CREATE PROCEDURE demo_in_parameter(IN p_in int)  

-- 定义变量
DECLARE l_int int unsigned default 4000000; 

-- 赋值
SET @p_in=1  

参数

输入

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;

# 全局变量
mysql> set @p_in=1; 

mysql> call in_param(@p_in);

输出

out不接受参数，传进来的值会被存储过程改变

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out; # 首先是null，因为是out，不接受值
    ->     set p_out=2;
    ->     select p_out; # 改为了2，能够输出
    ->   end
    -> //
mysql> delimiter ;

mysql> set @p_out=1;

mysql> call out_param(@p_out);

mysql> select @p_out; # 存储过程内改为了2

inout

能够接受、也能改变

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

变量

定义

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

-- datatype 为 MySQL 的数据类型，如: int, float, date,varchar(length)
DECLARE l_int int unsigned default 4000000;  
DECLARE l_numeric number(8,2) DEFAULT 9.95;  
DECLARE l_date date DEFAULT '1999-12-31';  
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

赋值

SET 变量名 = 表达式值 [,variable_name = expression ...]

用户变量

mysql > SELECT 'Hello World' into @x;  
mysql > SELECT @x;  
| Hello World |  

# 使用用户变量
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
mysql > SET @greeting='Hello';  
mysql > CALL GreetWorld( );  
+----------------------------+  
| CONCAT(@greeting,' World') |  
+----------------------------+  
|  Hello World               |  
+----------------------------+

控制语句

if then， else

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
         -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
         -> update t set s1=s1+1;  
     -> else 
         -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

case

case
    when var=0 then
        insert into t values(30);
    when var>0 then
    when var<0 then
    else
end case

循环

while

while 条件 do
    --循环体
endwhile

repeat···· end repeat

像do while

repeat
    --循环体
until 循环条件  v>=5 
end repeat;

loop ·····endloop

一直循环，通过if leave LOOP_LABLE结束

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
         -> insert into t values(v);  
         -> set v=v+1;  
         -> if v >=5 then 
            -> leave LOOP_LABLE;  
         -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

ITERATE

像goto

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc10 ()  
     -> begin 
         -> declare v int;  
         -> set v=0;  
         -> LOOP_LABLE:loop  
             -> if v=3 then   
                 -> set v=v+1;  
                 -> ITERATE LOOP_LABLE;  
             -> end if;  
             -> insert into t values(v);  
             -> set v=v+1;  
             -> if v>=5 then 
                 -> leave LOOP_LABLE;  
             -> end if;  
        -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

样例

-- 改为使用 // 否则可能报错
delimiter $$　　-- 将语句的结束符号从分号;临时改为两个$$(可以是自定义)，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释
CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
BEGIN
 　　DELETE FROM MATCHES
    WHERE playerno = p_playerno;
END$$

delimiter;　　-- 将语句的结束符号恢复为分号

-- 使用
call sp_name[(传参)];

游标

from：http://c.biancheng.net/view/7823.html

声明

DECLARE cursor_name CURSOR FOR select_statement;
mysql> DELIMITER //
mysql> CREATE PROCEDURE processnames()
    -> BEGIN
    -> DECLARE nameCursor CURSOR
    -> FOR
        -> SELECT name FROM tb_student;
    -> END//

使用

-- 打开才能提取数据
OPEN cursor_name;
-- 使用
FETCH cursor_name INTO var_name [,var_name]...;
-- 关闭
CLOSE cursor_name;

样例1

mysql> DELIMITER //
mysql> CREATE PROCEDURE test_cursor (in param INT(10),out result VARCHAR(90))
    -> BEGIN
        -> DECLARE name VARCHAR(20);
        -> DECLARE pass VARCHAR(20);
        -> DECLARE done INT;
        -> DECLARE cur_test CURSOR FOR SELECT user_name,user_pass FROM users;
        -> DECLARE continue handler FOR SQLSTATE '02000' SET done = 1; -- 定义cursor未找到(NOT FOUND或SQLSTATE '02000')的条件来标识游标结束
        -> IF param THEN INTO result FROM users WHERE id = param;
        -> ELSE
            -> OPEN cur_test;
            -> repeat
                -> FETCH cur_test into name,pass;
                -> SELECT concat_ws(',',result,name,pass) INTO result;
            -> until done
            -> END repeat;
            -> CLOSE cur_test;
        -> END IF;
    -> END //

mysql> call test_cursor(3,@test)//

样例2

mysql> CREATE PROCEDURE pro_users()
    -> BEGIN
        -> DECLARE result VARCHAR(100);
        -> DECLARE no INT;
        -> DECLARE cur_1 CURSOR FOR SELECT user_name FROM users;
        -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET no=1; -- 当找不到数据时，设置 no = 1
        -> SET no=0;
        -> OPEN cur_1;
        -> WHILE no=0 do
            -> FETCH cur_1 into result;
            -> UPDATE users SET user_name='MySQL'
            -> WHERE user_name=result;
        -> END WHILE;
        -> CLOSE cur_1;
    -> END //

预处理

某需求某一条 SQL 语句可能会被反复调用执行，或者每次执行的时候只有个别的值不同

一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止 SQL 注入。

MySQL 官方将 prepare、execute、deallocate 统称为 PREPARE STATEMENT

# 定义预处理语句
PREPARE stmt_name FROM preparable_stmt;
# 执行预处理语句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
# 删除(释放)定义
{DEALLOCATE | DROP} PREPARE stmt_name;

字符串定义

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';

mysql> SET @a = 3;

mysql> SET @b = 4;                                                   

mysql> EXECUTE stmt1 USING @a, @b;

mysql> DEALLOCATE PREPARE stmt1;                                     

变量定义

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';

mysql> PREPARE stmt2 FROM @s;

mysql> SET @c = 6;

mysql> SET @d = 8;

mysql> EXECUTE stmt2 USING @c, @d;

mysql> DEALLOCATE PREPARE stmt2;

limit

对于 LIMIT 子句中的值，必须是常量，不得使用变量，用 PREPARE 语句

# mysql> SET @skip = 100; SET @numrows = 3;
# mysql> SELECT * FROM t1 LIMIT @skip, @numrows;
# ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@skip, @numrows' at line 1


mysql> PREPARE stmt3 FROM "SELECT * FROM t1 LIMIT ?, ?";

mysql> EXECUTE stmt3 USING @skip, @numrows;

mysql> DEALLOCATE PREPARE stmt3;

注意：

定义一个已存在的 stmt_name ，原有的将被立即释放，类似于变量的重新赋值

PREPARE stmt_name 的作用域是session级

information_schema

information_schema数据库表说明:

SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。

COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。

STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。

SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。

COLLATIONS表：提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。

KEY_COLUMN_USAGE表：描述了具有约束的键列。

ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表。

oracle

高性能高可靠，面向对象关系型数据库管理系统

• 关系型Oracle、MySQL、SQL Server、PostgreSQL
• 非关系型：Mongodb、Redis、Hbase
• 国产：TiDB、达梦、OceanBase、LightDB

体系结构

用户进程 -> 服务器进程 ->

硬盘文件：【数据文件、控制文件、重做日志文件】、参数文件、口令文件、归档日志文件

逻辑结构和物理结构

物理结构（不要动）

• 数据文件：实际数据

• 重做日志：对数据库的修改记录，数据失败后恢复，至少要两个

• 控制文件：维护和校验数据库一致性信息

参数文件：oracle实例特性

口令验证：验证启动和关闭oracle实例的用户

归档日志文件：重做日志的备份，用以恢复数据

追踪文件：后台进程和服务器

逻辑结构

操作系统一个块4kb，oracle一个块8kb

基础概念

数据库对象

user/ profile /role /directory

• user: 数据库用户
• profile：每个用户有且仅有一个，数据库资源限制

schema：一组数据库逻辑数据结构的集合，一个用户创建后自动创建一个同名的schema

schema objects

• table

  • 数据的集合，二维表的形式存在，每一列代表一个属性，每一行为表的一个实例

• index

  • 索引可以提高表中数据检索的性能，通常由表的一个或多个列组成
  • 注意返回的数据量，如果大量的数据，遍历表可能还快

• constraint

  • 约束，对数据库表中的值（列）指定规则，确保其完整性，not null / unique /pk / foreign key / check

• view

  • 视图本质是一段查询，逻辑上代表一张或多张表组织起来的数据，可对外展示数据的裁剪和权限的管控

• synonym

  • 同义词，table view取别名

• sequence

  • 生成为数据库提供自动生成的唯一值

• database link

  • 允许授权的用户通过

connect

sqlplus /as sysdba
连接上空闲实例
启动 startup 
startup 
nomount：验证命令文件，读取参数文件，记录警告日志文件，分配内存和启动进程
mount：打开控制文件，验证正确性，获取数据文件和重做日志文件的状态
open: 打开数据文件和重做日志文件

shutdown
immediate：没有结束的事务自动rollback

# ORACLE启动 
cd /u01/app/oracle
su - oracle
# 进入Sqlplus控制台
sqlplus /nolog
# sql
start 
# 以系统管理员身份登录
connect /as sysdba
exit
# 进入监听器控制台
lsnrctl
start
exit
su - root

sqlplus

# server_name
jdbc:oracle:thin:@//<host>:<port>/<service_name>
# sid
jdbc:oracle:thin:@<host>:<port>:<SID>

sqlplus 用户名/密码@数据库主机Ip/service

安装：https://blog.csdn.net/wherejaly/article/details/50857255

http://www.oracle.com/technetwork/topics/winx64soft-089540.html

instantclient-basic-windows.x64-12.1.0.2.0.zip
instantclient-sqlplus-windows.x64-12.1.0.2.0.zip

解压后文件名应该为instantclient_12_1，在运行工具之前我们需要在windows中配置以下环境变量，先右键计算机->属性->高级系统设置->环境变量,在系统变量中找到Path并在后面加上刚才解压后instantclient_12_1的目录与sdk子目录

C:\Program Files\instantclient_12_1\;C:\Program Files\instantclient_12_1\sdk;


再新增两个变量到系统环境中

TNS_ADMIN=C:\Program Files\instantclient_11_2
NLS_LANG=AMERICAN_AMERICA.UTF8

配置文件：$oracle_home/network/admin/listener.ora，监听端口1521

监听器的作用：

• 监听客户端请求
• 分配oracle server process

-

rac

oracle real application clusters

RAC，实时应用集群

• 通过心跳网络内联为逻辑上的单数据库对外提供服务

SQL

DQL / *DML / DDL */ DCL

DML：数据操作语言

• select
• insert
• update
• delete
• merge
• explain plan
• lock table

DDL：数据定义语言

• create / alter / drop database
• truncate
• grant revoke
• audit noaudit
• comment

TCL 事务控制语言

• commit
• rollback rollback to savepoint
• savepoint
• set transaction
• set constraint

other 会话控制+系统控制

CRUD

select column
from table
[where ]
[group by ]
[having ]
[order by column]

update table
set column = value, []
[where ]

insert into table[(column[, column])]
values(value[, value]);

delete from table
[where condition]

--- 真正释放删除的数据空间
truncate table table_name;

分页

通过rownum将行号传递给上一层，让上一层处理分页

-- oracle
select * 
 from (
     select rownum rn, t.* 
     from t
     where rownum <= #{end}
 )
 where rn >= (#{start} +1)

-- mysql 
select *
from t
limit #{start}, #{pageSize}

-- oracle 设计
select * 
from (
    select t1.COLUMN_NAME,
                    t1.DATA_TYPE,
                    t1.DATA_LENGTH, 
                    rownum rn
    from(
        select t.COLUMN_NAME, t.DATA_TYPE, t.DATA_LENGTH, t.DATA_SCALE
        from (
            select * 
            from user_tab_cols 
            where DATA_TYPE != 'VARCHAR2'
        ) t
        group by t.COLUMN_NAME, t.DATA_TYPE, t.DATA_LENGTH, t.DATA_SCALE
    ) t1 
) t2
where t2.rn < 10;

优化

优化器： SQL分析和执行的优化工具，负责制定SQL的执行计划，是SQL执行的核心

种类：

• RBO（role base optimizer）基于规则
• CBO（Cost base optimizer）基于成本，或者统计信息

执行计划：

• 根据统计信息进行执行

--- select * from table(dbms_xplan.display_curor('')) 获取

统计信息：

hint：

• 强制走索引或者全表扫描

索引：

表的连接方式：

• 嵌套循环
• hash join （大表关联），基于CBO
• merge join，适合不等价连接

常用流程

su – oracle #不是必需，适合于没有DBA密码时使用，可以不用密码来进入sqlplus界面。
sqlplus /nolog 或sqlplus system/manager 或./sqlplus ;
connect / as sysdba ;（as sysoper）或
connect internal/oracle AS SYSDBA ;(scott/tiger)
conn sys/change_on_install as sysdba;
# 启动数据库实例
startup; 

# 所有数据库: select * from v$database;
select name from v$database;
desc v$databases; 查看数据库结构字段

# 查看哪些用户拥有SYSDBA、SYSOPER权限:
SQL>select * from V_$PWFILE_USERS;

# 查看当前数据库连接用户
show user;

# 进入test数据库
database test;

# 数据库实例
select * from v$instance;

# 当前库的所有数据表：
select TABLE_NAME from all_tables;
select * from all_tables;
elect table_name from all_tables where table_name like ‘u’;

# 查看表结构：
desc all_tables;

# CQI.T_BBS_XUSER的所有字段结构：
desc CQI.T_BBS_XUSER;

# CQI.T_BBS_XUSER表中的记录：
select * from CQI.T_BBS_XUSER;

# 增加数据库用户：(test11/test)
create user test11 identified by test default tablespace users Temporary TABLESPACE Temp;

# 用户授权:
grant connect,resource,dba to test11;
grant sysdba to test11;
commit;

# 更改数据库用户的密码：(将sys与system的密码改为test.)
alter user sys indentified by test;
alter user system indentified by test;

查看主键

select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = upper('tablename')

表结构

select * from user_tab_columns where table_name='大写表名'

表索引

select * 
  from all_indexes 
 where table_name = '表名'
-- user_indexes 
select * 
  from user_indexes 
 where table_name = '表名'
-- 索引对应哪些列
   select * 
     from user_ind_columns 
    where table_name='TC_TCUSTINFO'
 order by INDEX_OWNER    ,INDEX_NAME, 
           COLUMN_POSITION

集合操作

条件：两个结果集的字段个数和字段类型一一对应

minus

第一个表的数据减去第二个表的数据，不包括重复行，进行默认排序

union all

单纯合并两个集合，不去重、不排序，包括重复行

union

合并两集合，进行去重排序

intersect

两集合交集，找出重复的，不包括重复行，进行默认规则排序

日期

select TO_CHAR(SYSDATE, 'YYMMDD')
from dual;

数据类型

https://www.cnblogs.com/abeam/p/12153432.html

数值类型

number(p,s)

即可以用于存储整数，也可以存储小数

• p表示有效位，从左边第一个非0的数字开始数，到结尾的长度，取值范围：1 ~ 38；

• s表示小数位，取值范围：-84 ~ 127。

类型	描述	存储描述
DECIMAL(P,S)	数字类型	P为有效位，S为小数
INTEGER	整数类型	小的整数
FLOAT	浮点数类型	NUMBER(38), 双精度
REAL	实数类型	NUMBER(63), 精度更高

属于 number 类型的“变种”，而且使用 number 类型都能替代这些数据类型，所以这里不过多去研究它们

字符类型

四种字符类型比较：

类型	可变长度	一个空间占用字节数	最大字节数(bytes)	适用场景
CHAR	否	1	2000、 char(2000)	定长：英文、数字
NCHAR	否	2	2000, nchar(1000)	定长：中文
VARCHAR2	是	1	4000, varchar2(40000)	变长：英文、数字、中文
NCARCHAR2	是	2	4000, ncarchar2(2000)	变长：中文

1. 对于中文，在这四种类型中存储，都是占2个或2个以上字节；

   1. 在 ORACLE 数据库汉字占用几个字节，是根据 ORACLE 中字符集编码决定，一般情况，数据库的 NLS_CHARACTERSET 为 AL32UTF8 或 UTF8，一个汉字占三到四个字节；如果 NLS_CHARACTERSET 为 ZHS16GBK，则一个字符占两个字节。

2. 对于英文或数字，在 CHAR 和 VARCHAR2 中占一个字节，在 NCHAR 和 NVARCHAR2 中占两个字节。

CHAR

用于存储固定长度的字符串，一个空间占用1个字节，最多存储2000个字节，即：char(2000)，这里的2000表示字节数。通常也就是1000个汉字

NCHAR

根据字符集而定的固定长度字符串。用于存储固定长度的字符串，一个空间占用2个字节，最多存储2000个字节，即：nchar(1000)，注意这里的1000表示字符数

VARCHAR2

VARCHAR2 与 CHAR 类似，不同的是 VARCHAR2 用于存储可变长度的字符串。最大长度是 CHAR 的两倍，也就是4000个字节，即：varchar2(4000) ，通常也就是2000个汉字，或者4000个字母、数字等

NVARCHAR2

根据字符集而定的可变长度字符串。NVARCHAR2 与 NCHAR 类似，不同的是 NVARCHAR2 用于存储可变长度的字符串。最大长度是 NCHAR 的两倍，也就是4000个字节，即：nvarchar2(2000) ，通常也就是2000个汉字、字母、数字等。

与 NCHAR 一样，一个英文或数字都占用了两个字节，但是长度是不固定的

-- name = '中文' 'test'
select name, dump(name) from t_nvarchar2;

日期类型

date

date 用于存储日期和时间类型，date 类型的默认格式为：DD-MM-YYYY

timestamp

时间戳类型，与 DATE 相比较，TIMESTAMP 类型具有小数位毫秒数，比 DATE 的精度更高。

大数据与二进制类型

long

用于存储可变长度的超长字符串，最大长度为2G，通常用于存储备注字段

提示：LONG 是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。

clob

字符型大型对象，最大长度为4G

-- 使用
-- Create table
create table TEMP
(
  name      VARCHAR2(200),
  age       NUMBER,
  temp_clob CLOB
);


-- 直接拼接的长度只能是varchar2
SELECT t.name, t.temp_clob FROM temp t; --普通查询 INSERT INTO temp t VALUES ('Grand.Jon', 22, '加入博客园的第一天');


--使用PL/SQL语法，采取绑定变量的方式解决，而不是直接拼接SQL
DECLARE 
       V_LANG CLOB := '待插入的海量字符串';
       V_UPDATE CLOB := '更新的海量字符串';
BEGIN

  INSERT INTO temp t VALUES ('Grand.Jon', 22, V_LANG);        --增加

  UPDATE temp t SET t.temp_clob = V_UPDATE WHERE rownum = 1;  --修改

  SELECT t.NAME, dbms_lob.substr(t.temp_clob) FROM TEMP t;    --查询　　将CLOB转成字符类型

  DELETE temp t WHERE rownum = 1;                             --按列删除　　

  COMMIT;

END;
/

dbms_lob

dbms_lob.createtemporary(V_SQL,true);     --创建一个临时clob，用来存储拼接的sql
dbms_lob.write(v_SQL,'写入信息');          --写入操作
dbms_lob.append(v_SQL,',');               --拼接clob
dbms_lob.substr(v_SQL);                   --截取clob，不传参数就是全部读取
dbms_lob.freetemporary(v_SQL);            --释放clob

mybatis

直接string接收就行

<result column="temp_clob" property="content" jdbcType="Clob"  typeHandler="org.apache.ibatis.type.ClobTypeHandler"/> 

NCLOB

根据字符集而定的字符数据，最大长度为4G

BLOB

BLOG 是一种二进制大型对象（Binary Large Object），最大长度为4G，适用于存储非文本的字节流数据

常用类型

CREATE TABLE Temp01(ID number, Data_Type varchar2(16), Value varchar2(256), Type varchar2(256));

declare

  v_integer    integer       := 22;

  v_decimal    decimal(5,2)  := 22.6;

  v_float      float         := 22.7;

  v_real       real          := 22.8;

  v_number     number(5,2)   := 22.9;

  v_char       char(1)       := 'z';

  v_nchar      nchar(1)      := '中';

  v_varchar2   varchar2(2)   := 'z';

  v_nvarchar2  nvarchar2(2)  := '中';

  v_varchar    varchar(1)    := 'z';

  v_date       date          := to_date('2020-12-31', 'yyyy-mm-dd');

  v_timestamp  timestamp     := to_timestamp('2020-12-31 23:59:59.999', 'yyyy-mm-dd hh24:mi:ss.ff3');

  v_long       long          := 'aaa';

begin

  INSERT INTO Temp01 SELECT * FROM (

    SELECT 1, 'integer', to_char(v_integer), dump(v_integer) FROM dual UNION

    SELECT 2, 'decimal(5,2)', to_char(v_decimal), dump(v_decimal) FROM dual UNION

    SELECT 3, 'float', to_char(v_float), dump(v_float) FROM dual UNION

    SELECT 4, 'real', to_char(v_real), dump(v_real) FROM dual UNION

    SELECT 5, 'number(5,2)', to_char(v_number), dump(v_number) FROM dual UNION

    SELECT 6, 'char(1)', to_char(v_char), dump(v_char) FROM dual UNION

    SELECT 7, 'nchar(1)', to_char(v_nchar), dump(v_nchar) FROM dual UNION

    SELECT 8, 'varchar2(2)', to_char(v_varchar2), dump(v_varchar2) FROM dual UNION

    SELECT 9, 'nvarchar2(2)', to_char(v_nvarchar2), dump(v_nvarchar2) FROM dual UNION

    SELECT 10, 'varchar(1)', to_char(v_varchar), dump(v_varchar) FROM dual UNION

    SELECT 11, 'date', to_char(v_date), dump(v_date) FROM dual UNION

    SELECT 12, 'timestamp', to_char(v_timestamp), dump(v_timestamp) FROM dual UNION

    SELECT 13, 'long', to_char(v_long), dump(v_long) FROM dual

  ) t;

end;

事务

包含多个SQL语句操作的具有原子性的逻辑单元

set transaction开始

结束：

• commit / rollback
• 隐式commit(ddl)
• IDE或者工具的正常退出
• 连接异常中断

ACID，原子性、一致性、隔离性、持久性

锁

独占锁、共享锁

行锁（独占）和表锁（共享）

死锁

预防死锁

• 避免长事务，减少锁的等待时间
• 合理处理并发

避免在plsql使用for update进行数据库修改，忘记处理事务就要死锁了

查死锁

SELECT l.session_id sid,  
       s.serial#,  
       l.locked_mode,  
       l.oracle_username,  
       l.os_user_name, 
       s.machine,  
       s.terminal,  
       o.object_name,  
       s.logon_time,  
       p.SPID  
  FROM v$locked_object l, all_objects o, v$session s,v$process p  
 WHERE l.object_id = o.object_id  
   AND l.session_id = s.sid  
   AND s.paddr = p.addr  
 ORDER BY sid, s.serial#; 

删死锁

-- 方法 alter system kill session 'sid,serial#'  
alter system kill session '26,7013';

表结构

创建表

1、创建5.0的表空间
create tablespace yy50_data
logging 　　　　　　　　　　　　　　　　
datafile '/u01/app/oracle/oradata/orcl/YY50.dbf'
size 50m
autoextend on                       
next 50m maxsize 20480m 　　　
extent management local;

2、创建用户 
create user yy50 identified by oracle
default tablespace yy50_data;

3、为yy50用户赋权限
grant connect, resource,dba to yy50;

如果没有表空间权限

select * from dba_data_files
select tablespace_name from dba_tablespaces  ;

create user C##TCS identified by oracle
default tablespace LXFINOTS202203;

grant connect, resource,dba to C##TCS;

视图

-- 创建
CREATE VIEW ot_tdictionary_simple_view AS
SELECT
    L_KEY_NO,
    VC_KEY_VALUE
FROM
    ot_tdictionary;
-- 删除
DELETE view ot_tdictionary_simple_view;

-- 查看结构
select text from user_views where view_name='viewName'

索引

declare
v_rowcount integer ;
begin
select count(1) into v_rowcount from USER_INDEXES where upper(INDEX_NAME) = upper('IREQUESTDETAIL_HKCAPITALMODE')
                                                    and upper(TABLE_NAME) = upper('TC_TREQUESTDETAIL');
if v_rowcount = 0 then
        execute IMMEDIATE 'create index IREQUESTDETAIL_HKCAPITALMODE on TC_TREQUESTDETAIL (C_HK_CAPITAL_MODE)';
end if
end;
/

create [unique | bitmap] index [schema.] 索引名
on [schema.] 表名 (列名1, .., 列名N);
1. 创建 '一般索引'（'B-Tree' 索引）
   create index scott.idx_si_sname on scott.student_info(sname);
   create index scott.idx_si_sname_age on scott.student_info(sname, sex);
2. 创建 '位图索引'
   create bitmap index scott.bidx_si_sex on scott.student_info(sex);

1. 查询索引
   select t.*
     from dba_indexes t
    where t.owner = 'SCOTT'
      and t.table_name = 'STUDENT_INFO';
2. 修改索引
   -- 修改索引名称 idx_si_sname -> idx_si_sname_new
   alter index scott.idx_si_sname rename to idx_si_sname_new;
   -- 修改索引为无效
   alter index scott.idx_si_sname_new unusable;
   -- 重建索引
   alter index scott.idx_si_sname_new rebuild online;
3. 删除索引
   drop index scott.idx_si_age;

函数

https://www.cnblogs.com/zougang/p/9209706.html

create or replace function GetNextWorkDay_tmp(v_currentdate IN varchar2)
  return varchar is Result varchar2(10);
begin
  select to_char(min(d_date), 'yyyymmdd')
    into Result
    from tc_topenday_tmp t
   where t.d_date > to_date(v_currentdate, 'yyyy-mm-dd')
     and t.l_work_flag = 1;
  return(Result);
end GetNextWorkDay_tmp;

select GetNextWorkDay_tmp('20221221') from dual;

语句少了分号，看了半天

存储过程

一组完成特定功能的PL/SQL语句集

优点：

• 降低网络通讯量
• 提高执行效率，编译过了
• 可修复性高
• 简化流程

缺点：

• 数据库移植不方便
• 不支持面向对象
• 维护性差，不支持集群

基础结构

存储过程包括：过程声明、执行过程部分、存储过程异常

无参数

CREATE 
    OR REPLACE PROCEDURE mingTest
    AS
        icount number;
    begin
        select count(1) into icount from user_tab_cols;
        if icount = 1 then
            dbms_output.put_line(icount);
        else 
            dbms_output.put_line('---');
        end if;
    exception
        when too_many_rows then 
              DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
          when others then 
            DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');  
end mingTest;

BEGIN
    mingTest;
END;

有参

create or replace procedure runbyparmeters    
    (isal in emp.sal%type,   
     sname out varchar,  
     sjob in out varchar)  
 as  
    icount number;  
 begin 
      select count(*) into icount 
      from emp 
      where sal > isal and job = sjob;  
      if icount = 1 then 
        -- ....  
      else 
       -- ....  
     end if;  
exception  
     when too_many_rows then 
         DBMS_OUTPUT.PUT_LINE('返回值多于1行');  
     when others then 
         DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');  
end;

其中参数IN表示输入参数，是参数的默认模式。

OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。

OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程

IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。

样例

实例

-- 将column_name属性相同的分组，并将table_name拼接，逐条插入ming_temp_table1表中
CREATE 
    OR REPLACE PROCEDURE mingTest AS 
        str_sql varchar2(4000);
        same_count number;
        TYPE ref_cursor_type IS REF CURSOR;
        one_group_cur ref_cursor_type;
        table_names varchar2(4000);
        table_name varchar2(4000);
    BEGIN
    DECLARE
--     column_name属性不同的组
        CURSOR cur_group IS 
            SELECT
                t3.COLUMN_NAME,
                t3.DATA_TYPE,
                t3.DATA_LENGTH 
            FROM
                user_tab_cols t3 
            WHERE
                EXISTS (
                SELECT
                    t2.COLUMN_NAME 
                FROM
                    (
                    SELECT
                        t1.COLUMN_NAME
                    FROM
                        (
                        SELECT
                            t.COLUMN_NAME,
                            t.DATA_TYPE,
                            t.DATA_LENGTH 
                        FROM
                            user_tab_cols t 
                        GROUP BY
                            t.COLUMN_NAME,
                            t.DATA_TYPE,
                            t.DATA_LENGTH 
                        ) t1 
                    GROUP BY
                        t1.COLUMN_NAME 
                            HAVING count(1) >= 2
                    ) t2 
                WHERE t2.COLUMN_NAME = t3.COLUMN_NAME 
                ) 
            group BY
                t3.COLUMN_NAME, t3.DATA_TYPE, t3.DATA_LENGTH;

    BEGIN
            DBMS_OUTPUT.ENABLE(buffer_size => null);
--             str_sql := 'drop table ming_temp_table1';
--             execute immediate str_sql;
--             创建临时表
--                 str_sql := 'create global temporary table ming_temp_table1 (
--                                              COLUMN_NAME varchar2(200),
--                                              DATA_TYPE varchar2(200),
--                                              DATA_LENGTH varchar2(100),
--                                              TABLE_NAMES varchar2(4000)
--                                         ) on commit preserve rows';
--             execute immediate str_sql;

--         遍历存在column_name属性不同的组
            FOR group_row IN cur_group
                loop
                table_names := '';
--             查询同组内的table_name
                str_sql := 'select TABLE_NAME from user_tab_cols where COLUMN_NAME = ''' || group_row.COLUMN_NAME || ''' and DATA_TYPE = ''' || group_row.DATA_TYPE || ''' and DATA_LENGTH = ''' || group_row.DATA_LENGTH || ''''; 
--             游标拼接同组的table_name
                open one_group_cur for str_sql;
                loop
                    fetch one_group_cur into table_name;
                    exit when one_group_cur%notfound;                    
                    table_names := ltrim(table_names || ' , ' || table_name, ' , ');
                end loop;
                str_sql := 'insert into ming_temp_table1(COLUMN_NAME, DATA_TYPE, DATA_LENGTH, TABLE_NAMES) values(''' || group_row.COLUMN_NAME || ''',''' || group_row.DATA_TYPE || ''', ''' || group_row.DATA_LENGTH || ''', ''' ||  table_names || ''')';
                execute immediate str_sql;
            END loop;
-- 删除临时表
--   str_sql := 'drop table ming_temp_table1';
--   execute immediate str_sql;
    commit;
    END;
END mingTest;

-- 运行存储过程
BEGIN
    mingTest;
END;

-- 获取column_name相同属性的表信息
select * from ming_temp_table1 order by column_name, DATA_TYPE, DATA_LENGTH;

select * from user_tab_cols where table_name = 'MING_TEMP_TABLE' ;

delete from ming_temp_table1
drop table ming_temp_table1

select t.COLUMN_NAME,
                            t.DATA_TYPE,
                            t.DATA_LENGTH, t.rownum from(
    select * from (
        select * from user_tab_cols 
        where DATA_TYPE != 'VARCHAR2'
    ) t
    group by t.COLUMN_NAME, t.DATA_TYPE, t.DATA_LENGTH, t.DATA_SCALE
) t where rownum < 10;

desc TC_TCHARGEACCOREQUEST;

变量

标量类型

CLOB长文本

常用：NUMBER、CHAR 、VARCHAR2 、VARCHAR、NCHAR 、NVARCHAR2 、LONG 、DATE 、TIMESTAMP

%TYPE 利用已经存在的数据类型来定义新数据的数据类型

例如，当定义多个变量或常量时，只要前面使用过的数据类型，后边的变量就可以利用%TYPE 引用

一组完成数据库功能的SQL语句集。

sex LY_DS.LY_NB%TYPE ，变量sex，它的类型与表LY_DS中的字段LY_NB 类型一致。

复合类型

记录类型

countNum number(10); --别忘了写上具体的长度,并且以分号结束
maxId number(10); --别忘了写上具体的长度,并且以分号结束

select count(*), max(id) into countNum, maxId from ly_ds where LY_NB=sex;

(1).type声明，类似对象

DECLARE
    TYPE new_type IS RECORD   --以TYPE type_name IS RECORD开始
    (
       v_ly_nl LY_DS.LY_NL%TYPE,
       v_ly_mc varchar(100) --最后不要加,   
    );  --最后以 ;结束
    v_obj new_type; --将新对象定义为刚才声明的类型
BEGIN
     --into赋值给v_obj，会按照定义的type顺序赋值
    select ly_nl, ly_mc into v_obj from ly_ds where id = '2'; 
    dbms_output.put_line('第一个变量：'||v_obj.v_ly_nl);
    dbms_output.put_line('第二个变量：'||v_obj.v_ly_mc);
END;

(2).%rowtype，直接一整行当做对象

set serveroutput on;
DECLARE
    v_obj ly_ds%rowtype; --ROWTYPE不区分大小写
BEGIN
     --into赋值给v_obj，会按照定义的type顺序赋值
    select * into v_obj from ly_ds where id='2'; 
    -- v_obj.ly_nl
    -- v_obj.ly_mc
END;

索引表类型

set serveroutput on;
DECLARE
  /**
   *声明一个存储ly_ds整行数据的索引表，下标为数字，
   */
  type index_row_type is table of ly_ds%rowtype
      index by binary_integer;
  v_row index_row_type; 
  /**
  *将某行数据赋值给行变量的下标1上
  */
  select * into v_row(1) from ly_ds where id = '2'; 
  dbms_output.put_line('v_row(1)中ly_mc的值：'||v_row(1).ly_mc);
  dbms_output.put_line('v_row(1)中ly_nl的值：'||v_row(1).ly_nl);

  /**
   *声明一个存储字符串数据的索引表，下标也为数字，
   */
  type index_val_type is table of varchar2(10)
      index by pls_integer;
  v_val index_val_type; 
  v_val(1) :='正数'; 

  /**
   *声明一个存储字符串数据的索引表，下标为字符串，
   *即 varchar(100)、varchar(10),必须给固定大小
   */
  type index_str_val_type is table of varchar2(100)
      index by varchar(10);
  v_str_val index_str_val_type; 
  v_str_val('test1') :='java太可怕了'; 

BEGIN
 -- 以上的数据赋值
END;

varry定长数组

下边从1开始，要初始化，使用前要确定容量

DECLARE
  /**
   *声明一个最多容纳100个数的varry数组，注意，它的下标是从1开始的。
   *即 binary_integer
   */
  type array_type is varray(100) of varchar(100);
  /**
   *分别定义一个直接赋值的和两个未赋值的数组。
   *注意：一定要初始化，但可以不赋值。对于没有赋值的这种数组，在用之前
   *也一定要先确定容量。
   */
  v_val_array array_type := array_type('one','two');
  v_val_array2 array_type := array_type();
  v_val_array3 array_type := array_type();
BEGIN
    -- v_val_array(1)
    -- v_val_array(2)

   /**
    *第二个varry没有初始化长度，所以通过extend方法，为该数组加一个空位
    */
    v_val_array2.extend;
    v_val_array2(1) :='aaa'; 
    v_val_array2.extend;
    v_val_array2(2) :='bbb'; 
    v_val_array2.extend;
    v_val_array2(3) :='ccc'; 

    v_val_array3.extend(v_val_array2.count());
    v_val_array3(3) :='fff'; 
END;

CRUD样例

create or replace procedure test_add_procedure
    (id varchar,createtime varchar,name varchar,
    age varchar,sex varchar)
    AS
    str_sql varchar2(4000);
BEGIN
    insert into ly_ds values(id,createtime,name,age,sex);
    update ly_ds set ly_mc = mc where id = dsId ;
    select count(*),max(id) into countNum,maxId from ly_ds where LY_NB=sex;

    str_sql := 'insert into ming_temp_table1() xxxx';
    execute immediate str_sql;

    commit;  --别忘了加提交
end;

-- 单独执行
call test_add_procedure(4,'2018-08-09','李四','20','女');
call mingTest();

-- 程序块内执行
BEGIN
    mingTest();
END;

游标

oracle存储过程—-游标(cursor）的学习_茁壮成长的凌大大的博客-CSDN博客_存储过程cursor用法

(2条消息) Oracle存储过程中游标的简单使用_峡谷修身养性的博客-CSDN博客_oracle存储过程游标

是SQL的内存工作区，临时存储从数据库中提取的数据块

隐式游标

增删改查 and

create or replace procedure test_cryptic_procedure
as
begin
    for cryptic_rec in (
      select * from ly_ds
    )
    loop
        dbms_output.put_line('名称：'||cryptic_rec.ly_mc||' 年龄：'||cryptic_rec.ly_nl);
    end loop;
end;

显式游标

显式游标分为静态游标和动态游标，一般包含：声明、打开、提取、关闭，游标使用完后，必须显式关闭。

隐式游标默认打开，执行完后自动关闭

动态

-- ref游标，动态关联结果集的临时对象，即在运行的时候动态决定执行查询
TYPE ref_cursor_type IS REF CURSOR;
one_group_cur ref_cursor_type;

DECLARE
    --     column_name属性不同的组
    CURSOR cur_group IS select xxxx;

-- 游标遍历
            FOR group_row IN cur_group
                loop
                table_names := '';
--             查询同组内的table_name
                str_sql := 'select TABLE_NAME from user_tab_cols where COLUMN_NAME = ''' || group_row.COLUMN_NAME || ''' and DATA_TYPE = ''' || group_row.DATA_TYPE || ''' and DATA_LENGTH = ''' || group_row.DATA_LENGTH || ''''; 
--             游标拼接同组的table_name
                open one_group_cur for str_sql;
                loop
                    fetch one_group_cur into table_name;
                    exit when one_group_cur%notfound;                    
                    table_names := ltrim(table_names || ' , ' || table_name, ' , ');
                end loop;
                str_sql := 'insert into ming_temp_table1(COLUMN_NAME, DATA_TYPE, DATA_LENGTH, TABLE_NAMES) values(''' || group_row.COLUMN_NAME || ''',''' || group_row.DATA_TYPE || ''', ''' || group_row.DATA_LENGTH || ''', ''' ||  table_names || ''')';
                execute immediate str_sql;
            END loop;

游标例子

CREATE 
    OR REPLACE PROCEDURE test_select3_procedure ( sex VARCHAR ) AS --游标的定义
    Cursor test_cursor IS SELECT
        id,
        ly_mc,
        LY_NB
    FROM
        ly_ds 
    WHERE
        LY_NB = sex;
    cur test_cursor%rowtype; --游标的类型，类似于list的泛型
BEGIN
    FOR cur IN test_cursor
    loop
        exit WHEN test_cursor % notfound;
        dbms_output.put_line ( '数据是:' || cur.id || '_' || cur.ly_mc );
    END loop;
END;

遍历游标

create or replace procedure myprocedure is
      CURSOR CUR_TEST IS --声明显式游标
             SELECT ECODE,ENAME
              FROM EMP;
      CUR CUR_TEST%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录

    BEGIN 
      --For 循环
      FOR CUR IN CUR_TEST LOOP
          --循环体
        DBMS_OUTPUT.PUT_LINE('员工编号：'||CUR.ECODE ||'员工姓名：'|| CUR.ENAME);
      END LOOP;

      --Fetch 循环
      OPEN CUR_TEST;--必须要明确的打开和关闭游标
      LOOP 
        FETCH CUR_TEST INTO CUR;
        EXIT WHEN CUR_TEST%NOTFOUND;
        --循环体
        DBMS_OUTPUT.PUT_LINE('员工编号：'||CUR.ECODE ||'员工姓名：'|| CUR.ENAME);
      END LOOP;
      CLOSE CUR_TEST;

      --While 循环
      OPEN CUR_TEST;--必须要明确的打开和关闭游标
        FETCH CUR_TEST INTO CUR;
        WHILE CUR_TEST%FOUND LOOP  
          --循环体
          DBMS_OUTPUT.PUT_LINE('员工编号：'||CUR.ECODE ||'员工姓名：'|| CUR.ENAME);

          FETCH CUR_TEST INTO CUR;
        END LOOP;
      CLOSE  CUR_TEST;



    END myprocedure; 

流程控制

赋值i := 0

判断相等i = 0

判断不等i <> 0

if 、elsif 、else 、end if

create or replace procedure test_select5_procedure
    (innum  number,outnum out number)
AS
BEGIN
   if (innum > 5) then --（判断条件是否大于5 ,满足条件则以then标识进入当前分支）
       if(innum = 10) then -- （= 为相等）
          outnum := 20;   -- （:= 表示赋值）
       else
         outnum := innum + 1 ;
       end if;            --- （每个分支的结束都以 end if;来结束）
      dbms_output.put_line('第一个分支：'||outnum);
    elsif (innum > 0) then  --- （elsif 的写法注意，亲测必须这么写才行，写成else if不行的）
      outnum := innum - 1 ;
      dbms_output.put_line('第二个分支：'||outnum);
    else
      outnum := -1 ;
      dbms_output.put_line('第三个分支：'||outnum);
    end if;  --- （每个分支的结束都以 end if;来结束）
END;

case

简单case

CREATE 
    OR REPLACE PROCEDURE test_case_procedure 
        ( idnum VARCHAR ) 
    AS 
        v_ly_ds ly_ds % rowtype;
BEGIN
    SELECT * 
    INTO v_ly_ds 
    FROM
        ly_ds 
    WHERE
        id = idnum;
    CASE v_ly_ds.ly_nb 
        WHEN '女' THEN
            dbms_output.put_line ( '女人' );
        WHEN '男' THEN
            dbms_output.put_line ( '男人' );
        ELSE dbms_output.put_line ( '人妖' );
    END CASE;
END;

搜索case，case后边没有参数

select * 
into v_ly_ds 
from ly_ds 
where id=idnum;
case 
    when v_ly_ds.ly_nb='女' then
        dbms_output.put_line('女人');
    when  v_ly_ds.ly_nb='男' then
        dbms_output.put_line('男人');
    else
        dbms_output.put_line('人妖');
end case;

异常

预定义异常

-- 查看oracle预定义的异常
SELECT * FROM DBA_SOURCE WHERE NAME='STANDARD' AND TEXT LIKE '%EXCEPTION_INIT%'

自定义异常

    exception
        when too_many_rows then 
              DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
          when others then 
            DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');  

临时表

create or replace procedure pro as
  str_sql varchar2(100);
begin
  -- 创建临时表
  str_sql := 'create global temporary table temp_table (
       col1 varchar2(10),
       col2 number
    ) on commit preserve rows';
  execute immediate str_sql;

  -- 使用临时表
  str_sql := 'insert into temp_table(col1, col2) values(''a'', 1)';
  execute immediate str_sql;

  -- 删除临时表
  str_sql := 'drop table temp_table';
  execute immediate str_sql;

end;

查看SQL

-- 查看所有存储过程
select distinct name From user_source where type = 'PROCEDURE' 

-- 查看内容
SELECT text
    FROM user_source
   WHERE NAME = 'PRC_APP_GETTALENTALLARTICLES'
ORDER BY line
;
-- 复制去除空行，https://uutool.cn/nl-trim/

其他

初始化

CREATE TABLESPACE FS50_MINGYUE
LOGGING
DATAFILE '/home/oracle/FS50.DBF'
SIZE 1024M
AUTOEXTEND ON
NEXT 50M
EXTENT MANAGEMENT LOCAL;

CREATE USER c##_mingyue_tcs IDENTIFIED BY mingyue DEFAULT TABLESPACE FS50_MINGYUE TEMPORARY TABLESPACE TEMP;

GRANT
    CREATE SESSION,
    UNLIMITED TABLESPACE,
    CREATE TABLE,
    CREATE CLUSTER,
    CREATE SYNONYM,
    CREATE VIEW,
    CREATE SEQUENCE,
    CREATE DATABASE LINK,
    CREATE PROCEDURE,
    CREATE TRIGGER,
    CREATE TYPE,
    CREATE OPERATOR,
    CREATE INDEXTYPE
    TO c##_mingyue_tcs;

备份dmp

# 导出
expdp C##CS/ling@ORCLCDB dumpfile=221027.dmp
# 导入
impdp user_name/pwd@orcl dumpfile=xx.DMP log=xx.log

expdp userid=aaa/bbb directory=DMUP_DIR dumpfile=ccc.dmp schemas=aaa

expdp userid=aaa/bbb directory=DMUP_DIR dumpfile=ccc.dmp schemas=aaa

expdp C##TCS/libing@10.20.26.250:1521/ORCLCDB dumpfile=test.dmp ;

/opt/oracle/admin/ORCLCDB/dpdump

报错

报错：路径为空，是因为没有路径权限

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39145: directory object parameter must be specified and non-null

http://blog.itpub.net/519536/viewspace-700840/

# dba进入
su - oracle
mkdir -p /home/oracle/database

sqlplus /nolog
connect /as sysdba

# sql中
create directory oracle_database_bak_dir as '/home/oracle/database';
grant read,write on directory oracle_database_bak_dir to public;
exit
# bash运行
expdp C##UPS/libing@ORCLCDB dumpfile=oracle_database_bak_dir:ups221027.dmp logfile=oracle_database_bak_dir:ups_database_log.log

备份

create table user_info_bak 
as select * from user_info;

insert into user_info 
select * from bosdata.user_info_bak;

常用工具

导出导入：exp/imp

• 简单，针对少量稳定数据
• 时间长，效率低

数据泵导出导入：expdp/impdp

• 简单，效率高，可以控制进程

手动备份和恢复

RMAN备份和恢复（自动，只备份有数据的数据块）

ash报告信息收集

exp 用户名/密码@连接串 file=test.dmp tables=test rows=y
exp C##tcs/libing@10.20.26.250/ORCLCDB file=city_test.dmp tables=L_TCITY rows=y

unl/sqlldr

1. 建表
2. ctl文件
3. 运行

建表

CREATE TABLE L_TPROVINCE
(
  VC_PROVINCE_CODE VARCHAR2(10) NOT NULL,
  VC_PROVINCE_NAME VARCHAR2(32) NOT NULL);
ALTER TABLE L_TPROVINCE
  ADD CONSTRAINT PK_L_TPROVINCE PRIMARY KEY (VC_PROVINCE_CODE);

ctl文件

load data infile 'D:\联行号\province.unl'
insert into table L_TPROVINCE
fields terminated  by '|'
  (VC_PROVINCE_CODE,
  VC_PROVINCE_NAME)

运行

sqlldr 用户/密码@ip/xx control=sql.ctl direct=y log=log.log errors=5000 rows=500 bindsize=1048576

sqlldr C##tcs/libing@10.20.26.250/ORCLCDB control=D:\联行号\province.ctl direct=y log=D:\联行号\province.log

日志检查

检查alter$sid.log

show parameter background_dump_dest

针对性检查

• 是否宕机过

• 是否出现ORA错误，

• 操作系统是否出错

  • vi /var/log/message

• 是否过分使用交换分区

信息收集

• 故障时间
• 操作系统
• alterlog
• awr ash报告
• 事发top/cpu/内存/io信息

数据库的创建和删除，要使用工具，不能直接 rm

脚本的斜杆

如果是存储过程，那么遇到分号，就不能马上执行了。这个时候，就需要通过斜杠(/)来执行

备份

create table ${tableName}_bak as select * from ${tableName}

表空间

-- https://blog.csdn.net/by0920/article/details/90142920
-- 用户
select * from dba_users;
-- 会话
select username,sid,serial# from v$session;
alter system kill session '$sid,$serial';
-- 表空间
SELECT * FROM User_Tablespaces;
drop tablespace FS50 including contents AND datafiles;
select * from dba_data_files;
-- 用户 LCS_09  TCS_09
drop user LCS_09 cascade;
drop user TCS_09 cascade;

字符编码

-- sql
select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; 
SHUTDOWN IMMEDIATE;

STARTUP MOUNT;


ALTER SYSTEM ENABLE RESTRICTED SESSION;


ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;


ALTER SYSTEM SET AQ_TM_PROCESSES=0;


ALTER DATABASE OPEN;


ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; -- 跳过超子集检测


-- ？ ALTER DATABASE national CHARACTER SET INTERNAL ZHS16GBK

SHUTDOWN IMMEDIATE;
STARTUP

连接数

https://blog.csdn.net/tianqishu11/article/details/78615850

- 以sysdba身份登陆PL/SQL 
sqlplus / as sysdba;
-- 查看当前连接进程数
SQL>select count(*) from v$process;
-- 查看连接数上限
SQL>select value from v$parameter where name = 'processes';
-- 查询processes数量
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 3
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
-- 查询sessions数量
SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 170
shared_server_sessions integer
SQL>

经过以上数据库参数查询，发现sessions数量不够，只有150，故增大sessions数量。
只要会话连接数超过上面的process数150或者sessions数170，再来一个的会话进程，就会产生12516错误。因此可以修改一下该值。sessions和processes关系必须按照如下关系设置。
sessions=1.1processes+5;
//这个是sessions值和processes值的关系，最好按照这样做，因此如果要将processes数设置为500，则sessions数必须为1.1500+5=555

修改连接数的限制

 -- 以sysdba身份登陆PL/SQL 
 -- 查询目前连接数
    show parameter processes;
 -- 修改processes
    alter system set processes=500 scope=spfile;
 -- 修改session
    alter system set sessions=555 scope=spfile;
 -- 重启Oracle服务或重启Oracle服务器
 -- 然后reboot数据库，使其生效

postGreSQL

官网：https://www.postgresql.org/about/

语法：https://www.runoob.com/postgresql/postgresql-syntax.html

from：https://blog.csdn.net/mr_door/article/details/102527225

下载：https://www.runoob.com/postgresql/windows-install-postgresql.html

介绍

优势：

1. 稳定性强
2. 免费开源，BSD协议，mysql开源，但是实际上是能被oracle公司控制的，如果和oracle市场冲突，必然会牺牲mysql
3. 分布式环境好，集群架构，读写分离、负载均衡、数据水平拆分
4. 源代码易读，容易二次开发
5. 多进程，充分利用cpu，高并发逼近极限时，性能维持对数曲线，不再下降
6. pg的text无限长，可以直接访问（一般关系型限定8k），sql语法内置正则、索引、全文检索、xml xpath

差异

http://hsfs-kb.hundsun.com/pages/viewpage.action?pageId=36274213

原生pg没适配oracle、mysql

1. 类型强校验，numeric转integer也不行

   1. entity str ---> db int or other，最佳实践：
      增加jdbc连接参数stringtype=unspecified，该参数可让pg数据库会动尝试做合适的类型转换，程序中正常全都使用String类型即可。
      specify the type to use when binding PreparedStatement parameters set via setString(). If stringtype is set to varchar(the default), such parameters will be sent to the server as varchar parameters. If stringtype is set to unspecified, parameters will be sent to the server as untyped values, and the server will attempt to infer an appropriate type. This is useful if you have an existing application that uses setString() to set parameters that are actually some other type, such as integers, and you are unable to change the application to use an appropriate method such as setInt().
      
      # 会将string类型交给pg进行类型转换
      
      例：jdbc连接串
      db.config.datasource.trans2.jdbcurl=jdbc:postgresql://20.198.116.333:5432/ifm60_trans2?stringtype=unspecified&useUnicode=true&characterEncoding=UTF-8&useCursorFetch=true&useSSL=false

   2. 但添加stringtype=unspecified后部分函数会出问题，

      1. 如concat需在变量后指定类型.
         如原语句是 concat(#{xxxx}, ‘1’) 需改为 concat(#{xxxx}<if test="_databaseId=='postgresql'">::text</if>,'1')
      2. 如原语句是 case when #{xxxx} is null then ‘’ else #{xxxx} end as xxx 的写法也直接用mybatis判断变量是否为空，而不需要通过case when语句

2. 不支持blob和clob，要用text/bytea

3. 时间sysdate()、sysdate等无法使用

   1. now()，转数字CAST(to_char(now(), ‘YYYYMMDDHH24MISS’) AS BIGINT);
   2. 精确时间to_char(CURRENT_TIMESTAMP,’YYYYMMDDHH24MISS’)
   3. 日期to_char(CURRENT_TIMESTAMP,’YYYYMMDDHH24MISS’)

4. 原生pg不支持rownum

   1. limit 1 offset 0（不支持start,size写法，建议mysql也用limt offset)
   2. ROW_NUMBER() over()

5. 原生PG不支持rowid

   1. 一般用ctid
   2. 但oracle的rowid是固定不变的，而PG的ctid在重新收集统计信息后可能会变，一般在使用上两者可以等同

6. 原生pg的update语句在set子句上不能用别名

   1. 如t.xxx

7. 连表更新

   1. 只能update一个表，关联表要放在from子句里
   2. form查出要更新的数据，主表where关联

8. 游标

   1. 写法不同
   2. 并且游标只能在存储过程里用

9. 原生pg没有NVL、INSTR，不支持decode（有一个decode函数，但用法不同）

   1. 可以创建函数

   2. CREATE OR REPLACE FUNCTION nvl(sourceVal text, defaultVal text) RETURNS text AS $body$
      declare
      BEGIN
      RETURN case when sourceVal is null or sourceVal='' then defaultVal else sourceVal end;
      END;
      $body$ LANGUAGE plpgsql;

10. 原生PG不支持dual

11. 原生PG不支持CALL调用，直接select

12. for update不能用在left join右边为空的情况

13. 表名和约束名constraint不能一样，否则建表失败

(select nextval(#{sequence}))，貌似会缓存，因为sql已经被确定为某个值

<if test="_databaseId == 'oracle' or _databaseId == 'mysql' ">
</if>
<if test="_databaseId == 'postgresql'">
</if>

<if test="_databaseId == 'postgresql'">::text</if>

<select id="" resultMap="" databaseId="postgresql">
</select>
<select id="" resultMap="" databaseId="oracle">
</select>

类型

https://www.postgresql.org/docs/current/datatype.html

类型转换

select 233::text;
select text '233'; 
select text'233';
select cast(233 as numeric); -- as integer
-- numeric , 与integer不兼容
to_number('')

字符类型

序号	名字 & 描述
1	character varying(n), varchar(n)变长，有长度限制
2	character(n), char(n)定长,不足补空白
3	text变长，无长度限制

默认为普通字符串，不存在转义字符，但是mysql存入时会自动进行转义，将\\转为\，postgresql在9之后如果要当做转义字符，需要添加#'\\data'

如果要导入mysql的进入postgresql，需要将\\替换为\

数值类型

名字	存储长度	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

货币类型

money 类型存储带有固定小数精度的货币金额。

numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。

名字	存储容量	描述	范围
money	8 字节	货币金额	-92233720368547758.08 到 +92233720368547758.07

JSON 类型

实例	实例结果
array_to_json('{{1,5},{99,100}}'::int[])	[[1,5],[99,100]]
row_to_json(row(1,’foo’))	{“f1”:1,”f2”:”foo”}

数组类型

PostgreSQL 允许将字段定义成变长的多维数组。

数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。

声明数组

创建表的时候，我们可以声明数组，方式如下：

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。

我们也可以使用 “ARRAY” 关键字，如下所示：

CREATE TABLE sal_emp (
   name text,
   pay_by_quarter integer ARRAY[4],
   schedule text[][]
);

插入值

插入值使用花括号 {}，元素在 {} 使用逗号隔开：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

访问数组

现在我们可以在这个表上运行一些查询。

首先，我们演示如何访问数组的一个元素。 这个查询检索在第二季度薪水变化的雇员名：

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

数组的下标数字是写在方括弧内的。

修改数组

我们可以对数组的值进行修改：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

或者使用 ARRAY 构造器语法：

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

数组中检索

要搜索一个数组中的数值，你必须检查该数组的每一个值。

比如：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

另外，你可以用下面的语句找出数组中所有元素值都等于 10000 的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

或者，可以使用 generate_subscripts 函数。例如：

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

复合类型

复合类型表示一行或者一条记录的结构，相当于一个字段包含多个字段

它实际上只是一个字段名和它们的数据类型的列表。

像简单数据类型那样使用复合类型。比如，一个表的某个字段可以声明为一个复合类型。

声明复合类型

下面是两个定义复合类型的简单例子：

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

语法类似于 CREATE TABLE，只是这里只可以声明字段名字和类型。

定义了类型，我们就可以用它创建表：

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

复合类型值输入

要以文本常量书写复合类型值，在圆括弧里包围字段值并且用逗号分隔他们。 你可以在任何字段值周围放上双引号，如果值本身包含逗号或者圆括弧， 你必须用双引号括起。

复合类型常量的一般格式如下：

'( val1 , val2 , ... )'

一个例子是:

'("fuzzy dice",42,1.99)'

访问复合类型

要访问复合类型字段的一个域，我们写出一个点以及域的名字， 非常类似从一个表名字里选出一个字段。实际上，因为实在太像从表名字中选取字段， 所以我们经常需要用圆括弧来避免分析器混淆。比如，你可能需要从on_hand 例子表中选取一些子域，像下面这样：

SELECT item.name FROM on_hand WHERE item.price > 9.99;

这样将不能工作，因为根据 SQL 语法，item是从一个表名字选取的， 而不是一个字段名字。你必须像下面这样写：

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

或者如果你也需要使用表名字(比如，在一个多表查询里)，那么这么写：

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

现在圆括弧对象正确地解析为一个指向item字段的引用，然后就可以从中选取子域。

范围类型

着某一元素类型在一定范围内的值。

PostgreSQL 内置的范围类型有：

• int4range — integer的范围
• int8range —bigint的范围
• numrange —numeric的范围
• tsrange —timestamp without time zone的范围
• tstzrange —timestamp with time zone的范围
• daterange —date的范围

自定义

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界
SELECT upper(int8range(15, 25));

-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 范围是否为空
SELECT isempty(numrange(1, 5));

数据库操作

程序jdbc:postgresql://xx.xx.xx.xx:15432/tcs?currentSchema=gaussdb

创建

CREATE DATABASE dbname;

选择

# 登录
psql -h localhost -p 5432 -U postgres runoobdb
psql -h localhost -p 5432 -U postgres postgres
psql -h 10.188.140.49 -p 5432 -U postgres postgres
psql -h 10.188.140.49 -p 5432 -U postgres -d postgres
# 查看数据库
\l
# 进入数据库
\c + 数据库名

连接无权限

https://blog.csdn.net/u013719339/article/details/78414188

# pg_hba.conf文件在Postgre安装文件目录下的data文件夹
# 末尾添加，重启
host    all             all              0.0.0.0/0              md5

删除

DROP DATABASE [ IF EXISTS ] name;
-- https://www.runoob.com/postgresql/postgresql-drop-database.html
dropdb [connection-option...] [option...] dbname;

表结构

查看

-- 查看表信息
-- 所有，可以接通配符
\d
\d tablename

select * from pg_tables;

SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER  BY  tablename;

创建

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   -- constraint pk_test_a_id primary key( id)
   PRIMARY KEY( 一个或多个列 )
);

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

-- 查看是否成功
\d

删除

DROP TABLE table_name;
drop table department, company;

\d

修改

-- add
ALTER TABLE table_name ADD column_name datatype;
-- delete
ALTER TABLE table_name DROP COLUMN column_name;
-- update
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

-- constraint not null
ALTER TABLE table_name ALTER column_name datatype NOT NULL;
-- constraint unique
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
-- constraint PRIMARY
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
-- constraint common delete
ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;
-- mysql delete index，unique/primary key
ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;
ALTER TABLE table_name
DROP PRIMARY KEY;

do
$$
begin
     if (SELECT count(1) FROM pg_indexes WHERE upper(tablename) = 'LC_TRESULTNOTICECFG' AND upper(indexname) = 'IRESULTNOTICECFG') > 0 then 
            DROP INDEX iresultnoticecfg;
    end if;
    CREATE UNIQUE INDEX iresultnoticecfg ON LC_TRESULTNOTICECFG USING btree (vc_tenant_id, l_serial_no);
end;
$$;
commit;

视图

虚拟表

1. 更自然直观的查找结构数据
2. 限制数据访问，只能看到有限的数据
3. 汇总各个表的数据

pg视图自读，无法执行delete/insert/update，但是可以创建触发器，尝试修改时触发。

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

DROP VIEW view_name;

触发器

触发场景：操作前、操作后，更新时

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- 触发器逻辑....
];

顺便插入日志

 CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

 CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

-- 查看
SELECT * FROM pg_trigger;
SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

-- 删除
drop trigger ${trigger_name} on ${table_of_trigger_dependent};

模式

PostgreSQL 模式（SCHEMA）可以看着是表的集合

使用模式的优势：

• 允许多个用户使用一个数据库并且不会互相干扰。
• 将数据库对象组织成逻辑组以便更容易管理。
• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

元数据

官网：https://www.postgresql.org/docs/15/catalogs.html

视图

INFORMATION_SCHEMA中的视图由SQL标准定义

SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'tsys_user'

系统表

pg_class

记录了数据库中的表，索引，序列，视图（“关系”）。

relname 表，索引，视图等的名字。

relnamespace 包含这个关系的名字空间（模式）的 OID，对应pg_namespace.oid

relkind r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表

pg_namespace

记录了数据库的名字空间（模式）

其中比较重要的字段有：

nspname 名字空间的名字

nspowner 名字空间的所有者

pg_attribute

记录了数据库关于表的字段的信息。
其中比较重要的字段有：

attrelid 此列/字段所属的表，对应于pg_class.oid

attname 字段名字

atttypid 这个字段的数据类型,对应于pg_type.oid

attlen 对于定长类型，typlen是该类型内部表现形式的字节数目。 对于变长类型，typlen 是负数。 -1 表示一种”变长”类型（有长度字属性的数据）， -2 表示这是一个 NULL 结尾的 C 字串。是本字段类型 pg_type.typlen 的拷贝。

attnum 字段数目。普通字段是从 1 开始计数的。系统字段， 比如 oid， 有（任意）正数。

atttypmod atttypmod 元组在创建表的时候 提供的类型相关的数据（比如，一个 varchar 字段的最大长度）。 它传递给类型相关的输入和长度转换函数当做第三个参数。 其值对那些不需要 atttypmod 的类型而言通常为 -1。

attnotnull 这代表一个非空约束。我们可以改变这个字段以打开或者关闭这个约束。

attisdropped 这个字段已经被删除了，不再有效。

pg_type

1. typname 数据类型名字
2. typlen 对于定长类型，typlen是该类型内部表现形式的字节数目。 对于变长类型，typlen 是负数。 -1 表示一种”变长”类型（有长度字属性的数据）， -2 表示这是一个 NULL 结尾的 C 字串。

pg_description

记录了数据库中对象（表、字段等）的注释。

其中比较重要的字段有：

objoid 这条描述所描述的对象的 OID。如果这条注释是一个表或表中字段的注释，那么，该值对应于pg_class.oid

objsubid 对于一个表字段的注释，它是字段号，对应于pg_attribute.attnum。对于其它对象类型，它是零。

description 作为对该对象的描述的任意文本

常用SQL

select attr.attname, class.relname, type.typname, attr.atttypid, attr.*
from pg_attribute attr
    left join  pg_class class on class.oid =  attr.attrelid
    left join pg_type type on type.oid = attr.atttypid
where class.relname = 'test_table';

存储过程

function样例

无需创建即可执行function

do
$$
declare
    test int;
begin
    select count(*) into test from tc_tsysparameter;
end;
$$;

do
$$
DECLARE
  d_news_id numeric;
  d_tag_id numeric;
  cur_list CURSOR FOR
      select flu.id,fcm.id
        FROM
           a fcm,
            b frr,
            c flur,
            d flu
        WHERE
            fcm.create_id = frr.relation_id
        AND flur.ID = frr.role_id
        AND flu.id = flur.login_user_id
        AND fcm.create_id IS NOT NULL
        AND flur.role_type = 1
        AND flur.status = 1
        AND frr. TYPE = 1;
BEGIN
    OPEN cur_list;
    LOOP
        FETCH cur_list INTO d_news_id, d_tag_id;
        EXIT WHEN NOT FOUND;
        --插入数据
        UPDATE a SET login_user_id = d_news_id where id = d_tag_id;
    END LOOP;
    CLOSE cur_list;
END;
$$;

样例

多条记录

create or replace function f_get_member_info(id integer)
--setof是关键字，暂时不清楚其作用；record是返回的数据类型，即“记录类型”数据；
returns setof record as 
--两个美元符必须存在，中间可以填入符合命名规则的字符（如$body$，$abc$），但必须与下方的两个美元符相统一
$$
declare
    rec record;--定义记录类型的变量，用于存储查询的结果
begin
    --开始for循环，执行SELECT语句。注意，第一个loop后没有分号！
    for rec in EXECUTE 'SELECT id,real_name FROM a_account_all' loop
        return next rec;--将查询结果依次追加到rec变量中
    end loop;--for循环结束
    return;
end
$$

language 'plpgsql';

--调用存储过程f_get_member_info示例
-- a_account_all 为存储过程中被查询的表，id和real_name是表中的字段，也是在存储过程中被查询的字段
SELECT * FROM f_get_member_info(1568) as a_account_all(id integer,real_name character varying(50));

事务

begin;
-- or
BEGIN TRANSACTION;

备份

pg_dump -h127.0.0.1 -U postgres ots_09 > /mingyue_pg_.sql

其他

连接数：https://www.cnblogs.com/suwhatsu/p/15047665.html#:~:text=Postgres%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BF%AE%E6%94%B9%E6%9C%80%E5%A4%A7%E8%BF%9E%E6%8E%A5%E6%95%B0%20%E5%9C%A8%E5%AE%9E%E9%99%85%E9%A1%B9%E7%9B%AE%E4%B8%AD%20postgres%20%E6%95%B0%E6%8D%AE%E5%BA%93%20%E4%BC%9A%E5%87%BA%E7%8E%B0%E8%B6%85%E8%BF%9E%E6%8E%A5%E6%95%B0%EF%BC%8C%E8%B6%85%E5%87%BA%20postgres%20%E9%BB%98%E8%AE%A4100%E4%B8%AA%E8%BF%9E%E6%8E%A5%E6%95%B0%20org.postgresql.util.PSQLException%3A,many%20clients%20already.%20%E8%BF%90%E8%A1%8C%E6%AD%A4%20SQL%20%E4%BB%A5%E6%9F%A5%E7%9C%8B%E5%85%81%E8%AE%B8%E7%9A%84%20postgresql%20%E6%9C%80%E5%A4%A7%E8%BF%9E%E6%8E%A5%E6%95%B0%EF%BC%9A

postgresql.conf max_connections

运行此 SQL 以查看允许的 postgresql 最大连接数：

show max_connections;

默认值为 100。良好硬件上的 PostgreSQL 一次可以支持几百个连接。如果你想拥有数千个，你应该考虑使用连接池软件来减少连接开销。

看看究竟是谁/什么/何时/何地打开了您的连接：

SELECT * FROM pg_stat_activity;

当前使用的连接数为：

SELECT COUNT(*) from pg_stat_activity;