//DBLINK常用语句
select * from jk_sj_bb@HIS_LOCALHOST
update dba_db_links set db_link='HIS_LOCALHOST' WHERE DB_LINK='HIS_LOCLHOST'
update jk_sj_bb@his_ls set "name"='LS' WHERE "name"= '董裕发'
select * from dba_db_links
drop public database link HIS_LOCLHOST
insert into jk_sj_bb(name,sex) select "name","sex" from jk_sj_bb@HIS_LS where "name"='董裕发'
create public database link HIS_GS connect to "sa" identified by "1" using '(description=
(address= (protocol=tcp)(host=127.0.0.1)(port=1521))
(connect_data=(SID=dg4his))
(HS=OK)
)';
select * from all_triggers WHERE table_name='jk_sj_bb'
select ORCALE_SEQ.nextval from dual;
select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner='C##LRD';
//序列创建
CREATE TABLE DATAX_DATA_SYNCHRONISM_INFO (
DATAX_ID NUMBER PRIMARY KEY,
TABLENAME VARCHAR2(100) ,
SYNTYPE VARCHAR2(20),
T0TALCOUNT NUMBER DEFAULT 0,
SUCCOUNT NUMBER DEFAULT 0,
ERRCOUNT NUMBER DEFAULT 0,
SYNTIME DATE DEFAULT SYSDATE
)
//触发器创建
CREATE OR REPLACE TRIGGER jk_sj_bb_trigger
before insert on jk_sj_bb for each row
begin
select ORCALE_SEQ.nextval into :new.TAB_ID from dual;
end;
//导入导出
exp system/manager@orcl file=d:\daochu.dmp full=y
imp system/manager@orcl file=d:\daochu.dmp full=y ignore=y
imp C##LRD/123@orcl file=d:\daochu.dmp fromuser=C##LRD ignore=y
drop user C##LRD cascade;
//查询列重复
SELECT * FROM EX_DIC_VALUE WHERE DIC_VALUE_ID IN(
SELECT DIC_VALUE_ID FROM EX_DIC_VALUE GROUP BY DIC_VALUE_ID HAVING COUNT(DIC_VALUE_ID)>1
)
//存储过程示例
CREATE OR REPLACE
PROCEDURE TOTAL_CRB (
p_CURSOR out MYPACKAGE.MY_CURSOR,
indexCard IN NUMBER,
strTime IN DATE,
endTime IN DATE
) IS
BEGIN
IF indexCard = 1 THEN
OPEN p_CURSOR FOR SELECT
REPORT_DEP as 科室,
count(case when INFECTIOUS_CLASS='鼠疫' then '鼠疫' end) as 鼠疫,
count(case when INFECTIOUS_CLASS='钩端螺旋体病' then '钩端螺旋体病' end) as 钩端螺旋体病,
count(case when INFECTIOUS_CLASS='HIV' then 'HIV' end) as HIV,
count(case when INFECTIOUS_CLASS='Ⅱ期' then 'Ⅱ期' end) as Ⅱ期,
count(case when INFECTIOUS_CLASS='非淋菌性尿道炎' then '非淋菌性尿道炎' end) as 非淋菌性尿道炎,
count(REPORT_DEP) as 总人数
FROM
REPORTCARD_INFECT
WHERE
CARD_DATE BETWEEN strTime
AND endTime group BY REPORT_DEP ;
END
IF ;
IF indexCard = 2 THEN
OPEN p_CURSOR FOR SELECT
INFECTIOUS_CLASS as 疾病名称,
count(case when REPORT_DEP='眼科门诊' then '眼科门诊' end) as 眼科门诊,
count(INFECTIOUS_CLASS) as 合计
FROM
REPORTCARD_INFECT
WHERE
CARD_DATE BETWEEN strTime
AND endTime group BY INFECTIOUS_CLASS ;
END
IF ;
IF indexCard = 3 THEN
OPEN p_CURSOR FOR SELECT
*
FROM
REPORTCARD_INFECT
WHERE
CARD_DATE BETWEEN strTime
AND endTime ;
END
IF ;
IF indexCard = 12 THEN
OPEN p_CURSOR FOR SELECT
"TO_CHAR"(BAK1,'yyyy-MM-dd HH24:mi:ss') AS 发生日期,
BAK4 AS 科室,
BAK5 AS 医生名称,
BAK6 AS 病人名称,
BAK12 AS 病人类型,
BAK7 AS 住院号OR门诊号,
BAK8 AS 次数,
BAK9 AS 诊断名称,
BAK11 AS 未上报理由备注
FROM
MESSAGE_UNKNOW
WHERE
BAK1 BETWEEN strTime
AND endTime ORDER BY BAK1 DESC;
END
IF ;
END TOTAL_CRB ;
//连接指定实例
用户名/密码@数据库名称
sqlplus:system/123@gwyh
//创建一个包
create or replace package mypackage as
type my_cursor is ref cursor;
end mypackage;
//SQLPLUS常用命令
1.conn /as sysdba
2.create user username identified by password
3.grant dba to username
4.grant create session to username
5.grant unlimited session to username
6.grant create table to username
7.grant drop table to username
8.grant insert table to username
9.grant update table to username
10.grant all to public
//视图创建和权限
创建使用用户
conn /as sysdba
create user username identified by password
给用户添加视图权限
GRANT CONNECT TO zhangsan;
GRANT SELECT ON C##LRD.V_TEST TO zhangsan;
访问测试
SELECT * FROM C##LRD.V_TEST
视图创建
create or replace view 视图名称 as select * from 表名。
例
create or replace view test as
select t.empno,t.ename,t.job,t.sal ,t.deptno from emp t;
//索引增加
create index 索引名 on 表名 (字段);
ORCALE常用语法[待添加]
最新推荐文章于 2023-11-29 14:55:43 发布