#1.时间转换为字符，再比较大小
and to_char(a.scan_date,'yyyymmdd') >= '20211215' 

#2.字符转换为时间，比较时间大小。见下边的“字符转时间”笔记

#更新project_turn_delay表转固时间（最新审批时间）、转固单号（见下边的ProjectTurnBO类）
update project_turn_delay set TURN_DATE = to_date(?,'yyyy-MM-dd hh24:mi:ss'),TURN_CODE = ? where delay_id = ?

wf_taskhis    任务实例历史表，查询最新的审批时间

fun_id    功能ID（功能主键，一般为表名）
data_id    数据ID，（一般为表主键）
check_date    处理时间，即我们需要的审批时间

#查询 最新的审批时间 作为 审批通过日期
select myres.* from ( select check_date from wf_taskhis where fun_id = 'project_turn_delay' and data_id = ? order by check_date desc ) myres where rownum = 1

(select fun_id,data_id,max(check_date) check_date from wf_taskhis group by fun_id,data_id) wf_taskhis

select *
from asset_handle_det,(select fun_id,data_id,max(check_date) check_date from wf_taskhis group by fun_id,data_id) wf_taskhis,asset_handle
where (
                  asset_handle_det.handle_id = asset_handle.handle_id
              and asset_handle.auditing = '3'
              and wf_taskhis.data_id = asset_handle_det.handle_id
              and wf_taskhis.fun_id= 'asset_handle'
       );

update asset_center set (center_name,memo,center_name_en) = (
    select center_name,memo,center_name_en from (
--         根据时间排序，编号，取第一条（排序后编号为1的即最新的一条。后边还有where条件rn=1）
        select log_cost_center.*, row_number()over(partition by center_code order by add_date desc) rn 
        from log_cost_center
        )
    where rn=1 and center_code=asset_center.center_code
)
where exists(select 1 from log_cost_center where center_code=asset_center.center_code)

-- 查看数据
select rowid,mytable2.* from mytable2
-- 获取rowid最大（最新）的一条
select max(rowid),table_id,name,add_date from mytable2 group by table_id,name,add_date

select * from mytable as of timestamp to_timestamp('2022/1/6 11:21:00','yyyy-MM-dd hh24:mi:ss')

select * from mytable where my_id not in (
    select my_id from mytable as of timestamp to_timestamp('2022/1/6 11:21:00','yyyy-MM-dd hh24:mi:ss')
)

#sysdate，系统当前时间。add_months(sysdate,1)，时间增加1月，例如：2022-2-17 =》2022-3-17
update project_contract set qual_expire = add_months(sysdate,1) where contract_id = 'jxstar7252651'

-- 案例一：
#查询 最新的审批时间 作为 审批通过日期
select myres.* from ( select check_date from wf_taskhis where fun_id = 'project_turn_delay' and data_id = ? order by check_date desc ) myres where rownum = 1

-- 案例二：
select * from (
    select factory_name,factory_id,dept_id,dept_name,local_name,local_id,team_name,team_id,rownum rn 
    from wpa_card
) where rn between 10 and 20

#Oracle数据库

#测试
#round函数，四舍五入
#to_number将日期转换为数字（两日期相减，求相隔天数）
#outday 超期天数
select round(TO_NUMBER(sysdate - PLAN_TURNDATE)) as outday,PROJECT_CONTRACT.*
from PROJECT_CONTRACT
where AUDITING in ('1','3')
and CONTRACT_STATUS != '3'
and PLAN_TURNDATE is not null
and PLAN_TURNDATE < sysdate

#创建视图
create or replace force view v_cont_turn_overdue
as
select round(TO_NUMBER(sysdate - PLAN_TURNDATE)) as outday,PROJECT_CONTRACT.*
from PROJECT_CONTRACT
where AUDITING in ('1','3')
  and CONTRACT_STATUS != '3'
  and PLAN_TURNDATE is not null
  and PLAN_TURNDATE < sysdate

#测试，视图
select * from v_cont_turn_overdue

#插入
#insert into t1 (col1,col2) (select col1,col2 from t2 where ...)
#insert into t1 select * from t2 where ...
insert into equ_scan_det(device_code,device_name,dept_name,dev_memo)(select device_code,device_name,dept_name,dev_memo from asset_card where asset_code = '001000000843')

#更新（可加where条件）
update t1 set (nickname,playNum)=(select nickname,playNum from t2 where t1.id=t2.id) where ... ;

-- 注意，更新的是t1整张表，需要按具体情况添加where条件
update t1 set (nickname,playNum)=
(select nickname,playNum from t2 where t1.id=t2.id);


-- 案例：
-- 只更新 wpa_num > 1 的数据
update wpa_card a set wpa_num = (
    select wpa_num from (
        select wpa_id,wpa_num from wpa_card as of timestamp to_timestamp('20220610 12:00:00','yyyyMMdd hh24:mi:ss') where wpa_num > 1
    ) b where a.wpa_id = b.wpa_id
) where wpa_id in (
    select wpa_id from wpa_card as of timestamp to_timestamp('20220610 12:00:00','yyyyMMdd hh24:mi:ss') where wpa_num > 1
)

-- 查询 已审批通过的在建工程转固报销单
-- 获取 转固单号 和 所包含的在建工程清单中的资产号
select listagg(t_code,'、')within group(order by t_code) t_code,a_code from (
    select b.a_code,a.t_code from p_turn a,p_t_split b
    where a.data_t='2' and a.audit = 3
      and a.t_id = b.t_id(+)
) e group by a_code



-- （上边查询出来的数据）更新到 在建工程台账
merge into a_build c
using (
    select listagg(t_code,'、')within group(order by t_code) t_code,a_code from (
        select b.a_code,a.t_code from p_turn a,p_t_split b
        where a.data_t='2' and a.audit = 3
          and a.t_id = b.t_id(+)
    ) e group by a_code
) d
on (c.a_code = d.a_code)
when matched then update set c.t_code = d.t_code
where c.is_turn = 1;



-- 检查更新结果
select t_code from a_build where audit = 1 order by t_code;

#建议使用（Oracle 9以上版本）：
MERGE INTO sp_store_ext A USING (select sp_store.*,{NEWKEYID} aa from sp_store where data_type='1' and house_name=? and sp_code=? and bj_type='1') B ON (A.store_id=B.store_id)
WHEN MATCHED THEN UPDATE SET A.in_date=?,A.store_month=?,A.device_name=?,A.device_code=?,A.abc_type=?,A.use_devnum=?,A.use_num=?,A.rep_pc=?,A.pc_num=?,A.month_num=?,A.pur_cycle=?,A.cb_memo=?,A.type=?,A.c_reason=?,A.status=?,A.xh_time=?,A.out_type=?,A.memo=? 
WHEN NOT MATCHED THEN INSERT(A.ext_id,A.store_id,A.in_date,A.store_month,A.device_name,A.device_code,A.abc_type,A.use_devnum,A.use_num,A.rep_pc,A.pc_num,A.month_num,A.pur_cycle,A.cb_memo,A.type,A.c_reason,A.status,A.xh_time,A.out_type,A.memo) VALUES(B.store_id,B.store_id,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

select length(user_code) 账号长度,user_code 账号,user_name 用户名 from sys_user

-- 查询结果：
10 wangteng10 王腾
10 cuixiaoyu5 崔晓雨
10 lijinhui10 李金辉

-- 根据账号长度，从小到大（默认）排序
select length(user_code) 账号长度,user_code 账号,user_name 用户名 from sys_user order by length(user_code)
-- 根据账号长度，从大到小排序
select length(user_code) 账号长度,user_code 账号,user_name 用户名 from sys_user order by length(user_code) desc

#去除左边空格
update tableName set colName=ltrim(colName);

#去除右边空格
update tableName set colName=rtrim(colName);

#去除两边空格
update tableName set colName=trim(colName);

#例子：
update sp_store_ext set ext_id=trim(ext_id);

#函数也可用于select查询语句中

select house_name 仓库,SUBSTR(house_name, 1, INSTR (house_name, '备件周转库', 1, 1)-1)||'库存' 仓库,sp_code 物资编码,sum(store_num)||'个' 库存数量 from sp_store where data_type = '1'  group by house_name,sp_code

#大小写转换
#转换为小写 LOWER()
#转换为大写 UPPER()

#案例：
update spec_lowspare set import_degree = LOWER(import_degree) where lowspare_id = '1630'

select '姓名：' || c.stuname || ', 课程：' || b.coursename || ', 成绩：' || a.score || '分数' as sxcj
  from score a, course b, stuinfo c
 where a.courseid = b.courseid
   and a.stuid = c.stuid

#查询出来的数据，将会带上特定前缀（前边使用连接运算符拼接的姓名、课程等）

#查询仓库名
select house_name 仓库名 from sp_house where rownum <=3
仓库名
工具配件
二厂周转仓库
一厂周转仓库

#使用listagg函数，使多行显示在一行
select listagg(house_name,'；')within group(order by house_code) 仓库名 from sp_house where rownum <=3
仓库名
工具配件；一厂周转仓库；二厂周转仓库

select a.user_name 姓名,a.user_code 账号,a.dept_name 部门,a.add_date 开通日期,listagg(a.role_name,'；')within group(order by a.role_name) 角色
from 
(select sys_user.user_name user_name,sys_user.user_code user_code,sys_dept.dept_name dept_name,sys_user.add_date add_date,sys_role.role_name role_name from sys_user,sys_dept,sys_user_role,sys_role where sys_user.user_id = sys_user_role.user_id(+) and sys_user_role.role_id = sys_role.role_id(+)and sys_user.dept_id = sys_dept.dept_id(+) and sys_user.is_novalid='0' and sys_role.role_name is not null) a 
group by a.user_code,a.user_name,a.dept_name,a.add_date

select a.user_name 姓名,a.user_code 账号,a.dept_name 部门,a.add_date 开通日期,xmlagg(xmlparse(content a.role_name||'；' wellformed) order by a.role_name).getclobval() 角色
from 
(select sys_user.user_name user_name,sys_user.user_code user_code,sys_dept.dept_name dept_name,sys_user.add_date add_date,sys_role.role_name role_name from sys_user,sys_dept,sys_user_role,sys_role where sys_user.user_id = sys_user_role.user_id(+) and sys_user_role.role_id = sys_role.role_id(+)and sys_user.dept_id = sys_dept.dept_id(+) and sys_user.is_novalid='0' and sys_role.role_name is not null) a 
group by a.user_code,a.user_name,a.dept_name,a.add_date

-- 示例：查询c_where字段中，包含 '101303%' 的数据。
-- 数据中包含%，%为通配符，需要使用转义关键字处理。
-- 数据中包含单引号'，单引号'为特殊字符需要转义，写成两个单引号
select * from wf_u 
where c_where like '%''101303\%''%' escape '\'

select * from tablex where REGEXP_LIKE(字段名, '(匹配串1|匹配串2|...)') ;//全模糊匹配

select * from tablex where REGEXP_LIKE(字段名, '^(匹配串1|匹配串2|...)') ";//右模糊匹配（以xx开头）

select * from tablex where REGEXP_LIKE(字段名, '(匹配串1|匹配串2|...)$') ";//左模糊匹配

#案例：（以10、11等开头）
select distinct acc_name from asset_card where REGEXP_LIKE(type_code, '^(10|11|12|13|16|17|18)') and acc_name is not null

-- OT事业部 所有账号
select * from s_user where REGEXP_LIKE(d_id, 
(
-- 查找所有 部门全称中包含“OT事业部”的部门，截取部门根ID（前4位，顶层部门的id），分组去除重复后拼接成 REGEXP_LIKE函数的参数，用于查找对应部门下的账号
    select '^('||listagg(d_id,'|')within group(order by d_id)||')' from (
    select d_id from (
    select SUBSTR(d_id，0，4) d_id from s_dept where dept_all like '%OT事业部%' and is_disable = 0
    ) a group by d_id)
)
) and is_disable = 0
-- 过滤 没有添加指定的数据权限的账号
and not exists( select add_uid from s_user_funx where u_id = s_user.u_id and add_uid = 's_om_mb' group by add_uid having count (add_uid) = 3)

SELECT DBMS_METADATA.GET_DDL('TABLE','表名 大写') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('VIEW','视图名 大写') FROM DUAL;

-- 表数量
select count (*) from user_tables
-- 视图数量
select count (*) from user_views

-- lower()函数，将大写转换为小写
-- 包含字段“dept_id”的表的数量
select LOWER(table_name) 表名,LOWER(column_name) 列名 from DBA_TAB_COLUMNS where COLUMN_NAME= 'DEPT_ID';

-- 查找表名中包含“SP_CAT”的表（大写）
select * from user_tables where table_name like '%SP_CAT%'

select segment_name, sum(bytes) / 1024 / 1024 from user_extents where segment_type='TABLE' and segment_name='SP_CATALOG' group by segment_name
-- 或者：
select owner,segment_name,sum(bytes) / 1024 / 1024 M from dba_extents u where u.segment_type='TABLE' and segment_name = 'SP_CATALOG' group by owner,segment_name

select a.last_analyzed 最近一次更新时间,a.table_name 表名,'0'||b.mysize||'M' 数据量,a.tablespace_name 表空间,b.owner 所有者 from user_tables a,(
    select owner,segment_name,sum(bytes) / 1024 / 1024 mysize 
    from dba_extents u where u.segment_type='TABLE' group by owner,segment_name
) b 
where a.table_name = b.segment_name(+)
and a.table_name like '%SP_CATALOG%'
order by a.last_analyzed desc

1.简单Case函数写法(注意sex的位置)

select *,(CASE sex WHEN '1' THEN '男' WHEN '0' THEN '女' ELSE '保密' END) as sex_text
from user

2.Case搜索函数写法(注意sex的位置【推荐】)

select *,(CASE WHEN sex='1' THEN '男' WHEN sex='0' THEN '女' ELSE '保密' END) as sex_text
from user

#null判断
CASE WHEN 字段 is null THEN 0 ELSE 字段 END

-- 通过 table_id,name,age 判断是否相同
-- having count (table_id) > 1，取有1条以上的分组数据即有重复的
select count(*) 数量,name,table_id,age from mytable2 group by table_id,name,age having count (table_id) > 1


-- having count(*)
-- 统计某一列的数据

select acc_name 资产分类,sum(case when device_id is null then 0 else 1 end) 资产数量,sum(ori_value) 资产金额 from asset_card where acc_name is not null  group by acc_name

#as of timestamp - 应用Flashback Query查询过去的数据(基于时间的查询)

select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss')

例如：
#查看数据
select * from mytable
#误删
delete from mytable
#查看快照（可加where条件在后边）
select * from mytable as of timestamp to_timestamp('2021-12-29 15:27:00','yyyy-mm-dd hh24:mi:ss')

insert into 表名 (select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss'));

例如：
insert into mytable (select * from mytable as of timestamp to_timestamp('2021-12-29 15:27:00','yyyy-mm-dd hh24:mi:ss'))

select * from mytable where my_id not in (
    select my_id from mytable as of timestamp to_timestamp('2022/1/6 11:21:00','yyyy-MM-dd hh24:mi:ss')
)

#将row movement设置为enable
alter table 表名 enable row movement
#将表中数据闪回到某一时间点
flashback table 表名 to timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss')

#例子：
#误删
delete from mytable
#enable
alter table mytable enable row movement
#恢复
flashback table mytable to timestamp to_timestamp('2021-12-29 15:29:00','yyyy-mm-dd hh24:mi:ss')

#查看所有未被drop的表
select table_name,dropped from user_tables
#查看已被drop的表
select object_name,original_name,type,droptime from user_recyclebin

#将表恢复到drop前
flashback table 原表名 to before drop

#将表恢复到新表（重新创建一个表）
flashback table "回收站中的表名(如：Bin$DSbdfd4rdfdfdfegdfsf==$0)" to before drop rename to 新表名

#object_name 被锁的表名
select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name, ao.object_name,lo.locked_mode  from v$locked_object lo,dba_objects ao,v$session sess where ao.object_id=lo.object_id and lo.session_id=sess.sid;

#后边参数分别为SID和SERIAL#
alter system kill session '135,45402';

alter system kill session 'SID,SERIAL#';

select l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       s.user#,
       l.os_user_name,
       s.machine,
       s.terminal,
       a.sql_text,
       a.action
  from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid
   and s.prev_sql_addr = a.address
order by sid, s.serial#;

select count(*) from v$locked_object;

select * from sys_user
where sys_user.stafftype = '01'
and not exists (select 1 from sys_user_role,sys_role where sys_user.USER_ID = sys_user_role.USER_ID(+) and sys_user_role.ROLE_ID = sys_role.ROLE_ID and sys_role.role_name = '项目管理员')

insert into sys_user_data (user_id,dtype_id,dtype_data,display,has_sub,add_date,add_userid)
select 'jxstar-351-1550','jxstar1','1034','MB事业部','1',sysdate,'deptid_now'
from dual
where not exists
    (select * from sys_user_data where user_id = 'jxstar-351-1550' and dtype_id = 'jxstar1' and dtype_data = '1034');

#案例：
#查看 factory_name字段 中间包含数字的记录

#regexp_like
select * from wpa_card 
where REGEXP_LIKE(factory_name, '^.+[0-9]+.+$')

#regexp_substr
select * from wpa_card where
REGEXP_SUBSTR(factory_name, '^.+[0-9]+.+$') IS NOT NULL


#将 factory_name字段 中间包含数字2451002的记录 的factory_name字段值修改。将'2451002'更改为'订单物流部'。

#regexp_replace
update wpa_card set factory_name = regexp_replace(factory_name,'2451002','订单物流部') where wpa_attr = '10'
and rep_dept_name = '欧曼制造订单物流包装规划科'
and REGEXP_SUBSTR(factory_name, '^.+2451002.+$') IS NOT NULL

#查询数据，使用regexp_replace对查询出来的数据进行处理
select regexp_replace(factory_name,'2451002','订单物流部') as factory_name,wpa_code from wpa_card where wpa_code = 'SC016H-0001'

-- 案例1
CREATE OR REPLACE PROCEDURE demo AS/IS
	变量2 DATE;
	变量3 NUMBER;
BEGIN
	--要处理的业务逻辑
	EXCEPTION    --存储过程异常
END 

-- 案例2
CREATE OR REPLACE PROCEDURE 存储过程名称(
       s_no in varchar,
       s_name out varchar,
       s_age number) AS
total NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
  dbms_output.put_line('符合该年龄的学生有'||total||'人');
  EXCEPTION
    WHEN too_many_rows THEN 
    DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
END

-- 案例3
CREATE OR REPLACE PROCEDURE updateContractDept(
    p_code in varchar,
    p_dept_name in varchar,
    p_dept_id in varchar) AS
    dept_name_old varchar(50);
    dept_id_old varchar(50);
    dept_name_now varchar(50);
    dept_id_now varchar(50);
BEGIN
    SELECT dept_name,dept_id INTO dept_name_old,dept_id_old FROM project_contract WHERE contract_code = p_code;
    update project_contract set dept_name = p_dept_name,dept_id = p_dept_id where contract_code = p_code
    and exists ( select 1 from sys_dept where dept_id = p_dept_id );
    SELECT dept_name,dept_id INTO dept_name_now,dept_id_now FROM project_contract WHERE contract_code = p_code;
    dbms_output.put_line('合同【'||p_code||'】的实施部门已由【'||dept_name_old||dept_id_old||'】修改为【'||dept_name_now||dept_id_now||'】！');
EXCEPTION
    WHEN others THEN
    dbms_output.put_line('修改失败！');
END;

-- 更新部门（名称、id）
create or replace procedure updateDept(
    -- 接收的参数
    p_table_name in varchar,
    p_key in varchar,
    p_value in varchar,
    p_dept_name in varchar,
    p_dept_id in varchar)
AS
-- 变量
    dept_name_old varchar(50);
    dept_id_old varchar(50);
    dept_name_now varchar(50);
    dept_id_now varchar(50);
    sql_getdept varchar2(2000);
    sql_update varchar2(2000);
begin
-- 拼接sql
    sql_getdept := 'SELECT dept_name,dept_id FROM '||p_table_name||' WHERE '||p_key||' = '''||p_value||'''';
    sql_update := 'update '||p_table_name||' set dept_name = '''||p_dept_name||''',dept_id = '''||p_dept_id||''' where '||p_key||' = '''||p_value||''' and exists ( select 1 from sys_dept where dept_id = '''||p_dept_id||''' )';
-- 执行前边拼接好的sql，并将查询结果保存在变量中
    execute immediate sql_getdept into dept_name_old,dept_id_old;
    execute immediate sql_update;
    execute immediate sql_getdept into dept_name_now,dept_id_now;
-- 输出语句
    dbms_output.put_line('部门已由【'||dept_name_old||dept_id_old||'】修改为【'||dept_name_now||dept_id_now||'】！');
-- 异常处理
EXCEPTION
-- 使用others，只要出现异常就会打印下边的语句
    WHEN others THEN
        dbms_output.put_line('修改失败！');
end;

#执行存储过程
call 存储过程名()

#删除存储过程
drop procedure 存储过程名;