ORCALE常用语法[待添加]

//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 表名 (字段);