关于oracle的一些记录
添加表空间：
CREATE TABLESPACE OIS
DATAFILE 'C:\app\HuiMu06\oradata\orcl\OIS.dbf'
SIZE 200M
AUTOEXTEND ON;
添加用户：
CREATE USER pacs
IDENTIFIED BY pacs
DEFAULT TABLESPACE PACS
TEMPORARY TABLESPACE temp;
添加权限：
GRANT CREATE SESSION,CREATE TABLE,CREATE VIEW,CREATE SEQUENCE,UNLIMITED TABLESPACE TO pacs;
GRANT CONNECT TO pacs;
GRANT RESOURCE TO pacs;
GRANT DBA TO pacs;
删除非空表空间：
drop tablespace tablespace_name including contents and datafiles;
查找表被应用在哪些视图中：
select * from dba_dependencies where REFERENCED_NAME='表名' and TYPE='VIEW';
创建视图或存储过程调用其他用户表提示权限不足：
GRANT SELECT ANY TABLE TO USER_A WITH ADMIN OPTION; USER_A是指创建视图或存储过程的用户，语句由表的拥有者运行
更新其他用户表权限不足：
GRANT UPDATE ON SYS_MANAGE_DOCTOR TO OIS_ZY;
查询表的字段及其对应的注释：
select a.column_name column_name,a.comments comments from user_col_comments a where a.table_name = 'tab_name';
Oracle 存储过程中like语句的参数问题：
PROCEDURE FIND_PLANKEYWORD(I_STRKEY IN VARCHAR2, --关键字
O_CUR OUT SYS_REFCURSOR) --预案信息集合
IS
BEGIN
OPEN O_CUR FOR
SELECT * FROM TB_PLAN_MNAGER T WHERE T.PLANNAME LIKE '%'||I_STRKEY||'%';
END;
对于已经存在值的字段修改其类型：
alter table tbl add CREATE_TIME_N DATE;
update tbl set CREATE_TIME_N = to_date(CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss');
alter table tbl drop column CREATE_TIME;
alter table tbl rename column CREATE_TIME_N to CREATE_TIME;
日期加减
日期+ 1 年 SYSDATE + INTERVAL '1' YEAR
日期+ 1 月 SYSDATE + INTERVAL '1' MONTH
日期+ 1 日 SYSDATE + INTERVAL '1' DAY
日期+ 1 时 SYSDATE + INTERVAL '1' HOUR
日期+ 1 分 SYSDATE + INTERVAL '1' MINUTE
日期+ 1 秒 SYSDATE + INTERVAL '1' SECOND
日期+ 1 日1 时1 分 SYSDATE + INTERVAL '1 1:1' DAY TO MINUTE
将含有中文字符的日期格式转化（如：19-8月 -17）：
TO_CHAR(TO_DATE(CREATE_TIME, 'dd-mm"月"-yy'),'yyyy-mm-dd')
自增长主键：
drop sequence dectuser_tb_seq; create sequence dectuser_tb_seq minvalue 1 maxvalue 99999999 increment by 1 start with 1; /*步长为1*/
create or replace trigger dectuser_tb_tri
before insert on dectuser /*触发条件：当向表dectuser执行插入操作时触发此触发器*/
for each row /*对每一行都检测是否触发*/
begin /*触发器开始*/
select dectuser_tb_seq.nextval into :new.userid from dual;
end;
判断是否包含字符串：
instr(address, ‘beijing’) > 0
截取字符串
substr('ABCDEFG', 0); -- 返回结果是：ABCDEFG，从0位开始截取后面所有
substr('ABCDEFG', 2); -- 返回结果是：BCDEFG，从2位开始截取后面所有
substr('ABCDEFG', 0, 3); -- 返回结果是：ABC，从0位开始往后截取3个字符长度
substr('ABCDEFG', 0, 100); -- 返回结果是：ABCDEFG，虽然100超出了元字符串长度，但是系统会按元字符串最大数量返回，不会影响返回结果
substr('ABCDEFG', -3); -- 返回结果是：EFG，如果是负数，则从尾部往前数，截取-3位置往后的所有字符串
替换字符串：
replace('ABCDEFG', 'CDE', 'cde'); -- 返回结果是：ABcdeFG
replace('ABCDEFG', 'CDE', ''); -- 返回结果是：ABFG，CDE被替换成空字符
replace('ABCDEFG', 'CDE'); -- 返回结果是：ABFG，当不存在第三个参数时，CDE直接被删掉
字符串和数字互转
to_number to_char
select TO_NUMBER(TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss')) AS 相差天数 from dual;
--Oracle中两个日期相差小时数--
select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24)
AS 相差小时数 from dual;
--Oracle中两个日期相差分钟数--
select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24*60)
AS 相差分钟数 from dual;
--Oracle中两个日期相差秒数--
select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24*60*60)
AS 相差秒数 from dual;
--oracle两个日期的相差月数--
--1）月份都是最后一天，A日期 > B日期 ,返回整数 ---
select months_between(TO_DATE('2018-6-30','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))
As 相差月份1 from dual;
--2）月份都是最后一天，B日期 > A日期 ,返回负数 ---
select months_between(TO_DATE('2018-4-30','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))
As 相差月份2 from dual;
--3）月份天数不一样，A日期 > B日期 ,返回带小数的数字---
select months_between(TO_DATE('2018-6-25','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))
As 相差月份3 from dual;
--oracle两个日期的相差年份-- select ((months_between(TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2016-5-31','yyyy-mm-dd hh24:mi:ss')))/12) As 相差年份 from dual;
相差几个星期
用to_char(sysdate,'ww')获取时间为当年的第多少个星期，同理做减法即可获得差值
function
create or replace function GET_PATIENT_FZ_DATE(PAT_ID_NUMBER in varchar2)
return varchar2 is
sItems varchar2(200);
last_visit_date date;
begin
return sItems;
EXCEPTION
WHEN OTHERS THEN
return SQLERRM ;
end GET_PATIENT_FZ_DATE;
关于树状数据的查询
select distinct * from XXXX start with NODE_GUID=‘0’ connect by prior NODE_GUID = PARENT_GUID order siblings by NODE_INDEX , CREATE_TIME
oralce 存储过程中的switch用法
case
when v_id = 1 then
dbms_output.put_line(v_id);
when v_id = 2 then
dbms_output.put_line('elsif');
else
dbms_output.put_line(v_id);
end case;
oracle update from
（1）单列
UPDATE A
SET A.NAME=(SELECT B.NAME FROM B WHERE B.ID=A.ID)
WHERE A.ID IN (SELECT ID FROM B)；
（2）多列
UPDATE order_rollup
SET(qty,price)=(SELECT SUM(qty),SUM(price) FROM order_lines WHERE customer_id='KOHL' )
WHERE cust_id='KOHL' AND order_period=TO_DATE('01-Oct-2000')
字段拼接
SELECT ID,create_date, LISTAGG(VALUE, ' / ') WITHIN GROUP (ORDER BY VALUE) As VALUE FROM V_WEB_DATA GROUP BY ID,create_date
行转列
SELECT PATIENT_ID_NUMBER,create_date,OD,OS FROM ( SELECT PATIENT_ID_NUMBER,create_date,EYE, LISTAGG(VALUE, ' / ') WITHIN GROUP (ORDER BY VALUE) As VALUE FROM V_WEB_DATA GROUP BY PATIENT_ID_NUMBER,create_date,EYE) PIVOT ( MAX(VALUE) FOR EYE IN ( 'OD' As "OD", 'OS' As "OS" ) )
给相同项同序号，其他的继续递增
select PATIENT_ID_NUMBER, create_date,OD,OS, dense_rank() over(order by PATIENT_ID_NUMBER) 序号结果 from V_WEB_EVERYTIME;
运行示例如下：
给相同项递增序列号，其他的重新计算，如需顺序，修改PARTITION BY PATIENT_ID_NUMBER ORDER BY create_date
select PATIENT_ID_NUMBER, create_date,OD,OS, row_number() over(PARTITION BY PATIENT_ID_NUMBER ORDER BY create_date) 序号结果 from V_WEB_EVERYTIME;
运行示例如下：
行转列
with temp as( select input_time,pat_id_number,a.before_breakfast,a.after_breakfast,a.before_lunch,a.after_lunch,a.before_dinner,a.after_dinner,a.before_sleep from drgs_patient_xt_data a where a.before_breakfast is not null or a.after_breakfast is not null or a.before_lunch is not null or a.after_lunch is not null or a.before_dinner is not null or a.after_dinner is not null or a.before_sleep is not null ) select to_char(input_time,'yyyy-mm-dd') as input_time,pat_id_number,type,value from temp unpivot (value for type in (before_breakfast,after_breakfast,before_lunch,after_lunch,before_dinner,after_dinner,before_sleep))t
数据库的自动备份
通过电脑的自动任务以及bat实现
1.bat
@echo off set sid=192.168.2.144/lis.eye.ac.cn set username=qgycase set password=C2a016022022 set connect=%username%/%password%@%sid% set back_path=D:\DataBak set date_string=%date:~0,4%_%date:~5,2%_%date:~8,2% set time_string=%time:~0,2%_%time:~3,2%_%time:~6,2% set file_string=%back_path%\qyw_%date_string%_%time_string% exp %connect% file=%file_string%.dmp INDEXES=y grants=y constraints=y
nolog.bat
E:\app\admin\virtual\product\12.2.0\dbhome_1\bin\sqlplus.exe /nolog
关于写存储过程时 select into 语句报错
begin
select age, sex, name into a, b, c from myemp t where t.empno = pno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('捕获到异常');
END;
关于运行定时任务报错
解决:
1.在命令行查看job_queue_processes状态
show parameter job_queue_processes;
2.设置job_queue_processes的值大于0即可
alter system set job_queue_processes=8;
以上,感谢.
