Oracle使用总结

---

PLSQL客户端配置

1. 配置快捷键
   https://jingyan.baidu.com/article/4665065839795cf549e5f8b2.html
   https://blog.csdn.net/long_long_ago1/article/details/82670886
2. 背景色、字体设定

---

PLSQL常用函数

-- 复制另外一张表的结构到一张新表中
create table new_table as select * from old_table where 1 = 2;

-- 日期和时间处理
sysdate + 1  -- 加一天
sysdate + 1 / 24  -- 加1小时
sysdate + 1 / (24*60)  -- 加1分钟
sysdate + 1 / (24*60*60)  -- 加1秒钟
add_months(sysdate, 12)  -- 加1年 
add_months(sysdate, 1)  -- 加1月 
to_char(sysdate + 7, 'yyyy-mm-dd HH24:MI:SS')  --加1星期 
to_char(sysdate + 1, 'yyyy-mm-dd HH24:MI:SS')  --加1天 
to_char(sysdate + 1/24, 'yyyy-mm-dd HH24:MI:SS')  --加1小时 
to_char(sysdate + 1/24/60, 'yyyy-mm-dd HH24:MI:SS')  --加1分钟 
to_char(sysdate + 1/24/60/60, 'yyyy-mm-dd HH24:MI:SS')  --加1秒
add_months(sysdate, -12)  -- 减1年 
add_months(sysdate, -1)  -- 减1月 
to_char(sysdate - 7, 'yyyy-mm-dd HH24:MI:SS')  -- 减1星期 
to_char(sysdate - 1, 'yyyy-mm-dd HH24:MI:SS')  -- 减1天 
to_char(sysdate - 1/24, 'yyyy-mm-dd HH24:MI:SS')  -- 减1小时 
to_char(sysdate - 1/24/60, 'yyyy-mm-dd HH24:MI:SS')  -- 减1分钟 
to_char(sysdate - 1/24/60/60, 'yyyy-mm-dd HH24:MI:SS')  -- 减1秒

-- 表的重命名
alter table tablename rename to new_table_name;

-- 开窗函数
lag() partition by col_name1 order by col_name2
lead() partition by col_name1 order by col_name2

---

Oracle常见模板

建表语句

-- 创建表
CREATE TABLE t_module (
	module_id INT NOT NULL ,
    module_code varchar(32) NOT NULL,
    module_name varchar(32) NOT NULL,
    isnew NT DEFAULT 0,
	PRIMARY KEY (module_id)
)
-- 为表创建序列，从1开始，每次加1
CREATE SEQUENCE SEQ_MODULE START WITH 1 INCREMENT BY 1;
 --查询下一个id
 select seq_module.nextval from dual

临时表

https://blog.csdn.net/xiaoqiangyonghu/article/details/80564778

不传参的Procedure Demo

-- 新建临时表，用于测试Oracle存储
create table t_demo as
select '1' a from dual;

-- 新建存储过程
create or replace procedure sp_demo is
begin

insert into t_demo
select 1 from dual;
commit;
end;

-- 调用存储过程的方法
begin
    sp_demo;
end;

传参的Procedure Demo

-- 新建存储过程
create or replace procedure sp_demo (
    para date
) is
begin

insert into t_demo
select para - 1 from dual;
commit;
end;

-- 调用存储过程的方法
begin
    sp_demo(date'2019-01-01');
end;

循环执行存储过程Demo

declare
    i int := 1;  -- := 表示赋值过程中的传参
begin
    while i <= 10 loop  -- loop表示循环开始，end loop表示循环结束
    insert into t_demo
    select sysdate from dual;
    i := i + 1;
    end loop;
end;

在存储过程中使用DDL语句

在oracle的存储过程中，不能直接使用DDL语句，比如create、alter、drop、truncate等。那如果我们想在存储过程中建立一张临时表就只能使用动态sql语句了：

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;

在oracle中，临时表分为会话级别(session)和事务级别(transaction)两种。会话级的临时表在整个会话期间都存在，直到会话结束；事务级别的临时表数据在transaction结束后消失，即commit/rollback或结束会话时，会清除临时表数据。

• on commit preserve rows – 会话级别临时表(退出登录会结束会话)
• on commit delete rows – 事务级别临时表(提交或回滚会结束事务)

Oracle配置定时作业
https://blog.csdn.net/rongtaoup/article/details/82461890
https://www.cnblogs.com/yx007/p/6519544.html

---

Oracle调优

1. 执行计划
   https://jingyan.baidu.com/article/ab69b270bffc2e2ca7189fee.html

2. 大表的部分数据删除过程
   Oracle部分删除表，对于数据量较多的表，要慢很多。因此，需要部分删除表时，不如新建一张备份表，然后重命名即可，更快，

---

Python连接Oralce

# demo
import cx_Oracle

dsn = cx_Oracle.makedsn('host_name', '1521', 'SID')  # Oracle的host，端口，SID
orcl = cx_Oracle.connect('username', 'password', dsn)  # 登陆Oracle的账号密码
curs = orcl.cursor()  # 游标
curs.execute(sql)

其中，host可查看Oracle安装路径下的litsener.ora文件；
SID要在Oracle command下输入select instance_name from v$instance获取；

---

Tips

1. Oracle不同于MySQL、SqlServer，它是事务性数据库，为了避免多人操作时锁表，使用Oracle时，结束ddl语句之后需要commit才能提交事务；具体来说，提交分类如下：

• 显式提交：需要点击commit命令完成的提交。这些常用的命令有：insert，delete，update；
• 隐式提交：用SQL命令间接完成的提交（不需点击，自动提交）。这些常用的命令有：create，drop，alter，grant，revoke，truncate， quit