关于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
View Code

 

行转列

  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;
以上,感谢.

posted @ 2020-07-03 17:38  若白过隙  阅读(119)  评论(0编辑  收藏  举报