cchest.sql

胸外科信息管理系统.rar(powerbuilder)

--胸外科
create table chest_pat_visit
	(
	SERIAL_NO				number(6),
	patient_id				varchar2(10),
	visit_id				number(2),
	inp_no					varchar2(6),
	DOCTOR_IN_CHARGE			varchar2(8),
	NAME					varchar2(8),
	NAME_PHONETIC				varchar2(16),
	SEX					varchar2(4),
	AGE					number(3),
	HOME_ADDR				varchar2(40),
	PHONE					varchar2(20),
	EMAIL					varchar2(20),
	SERVICE_AGENCY				varchar2(40),
	ZIP_CODE				varchar2(6),
	NEXT_OF_KIN				varchar2(8),
	DIAGNOSIS_DESC_IN1			varchar2(40),
	DIAGNOSIS_DESC_IN2			varchar2(40),
	DIAGNOSIS_DESC_IN3			varchar2(40),
	DIAGNOSIS_DESC_OUT1			varchar2(40),
	DIAGNOSIS_DESC_OUT2			varchar2(40),
	DIAGNOSIS_DESC_OUT3			varchar2(40),
	DIAGNOSIS_DESC_OUT4			varchar2(40),
	DIAGNOSIS_PATHOLOGY			varchar2(100),
	T					varchar2(2),
	N					varchar2(2),
	M					varchar2(2),
	ADMISSION_DATE_TIME			Date,
	OPERATING_DATE				Date,
	DISCHARGE_DATE_TIME			date,
	SYMPTOM					varchar2(200),
	X_RAY					varchar2(100),
	CT					varchar2(100),
	MIR					varchar2(100),
	B					varchar2(100),
	NKJ					varchar2(100),
	ZY					varchar2(100),
	ECT					varchar2(100),
	ECG					varchar2(100),
	FVC					number(3,2),
	MVV					number(3,2),
	FEV					number(3,2),
	FEV_FVC					number(3,2),
	PaO2					number(3,2),
	PaCO2					number(3,2),
	Rx					varchar2(200),
	ANAESTHESIA_METHOD			varchar2(80),
	SURGEON					varchar2(8),
	FIRST_ASSISTANT				varchar2(8),
	SECOND_ASSISTANT			varchar2(8),
	THIRD_ASSISTANT				varchar2(8),
	DIAGNOSIS_DEATH				varchar2(30),
	DEATH					varchar2(20),
	OPER_COM1				varchar2(40),
	OPER_COM2				varchar2(40),
	NONOPER_COM1				varchar2(40),
	NONOPER_COM2				varchar2(40),
	EXPERINCE				varchar2(200),
	VISITATION				varchar2(200),
	constraint pk_chest_pat_visit
		primary key
			(
			patient_id,
			visit_id
			)
		using index
			pctfree		20
			storage
			(
			initial		2M
			next			128K
			minextents		1
			maxextents		unlimited
			pctincrease		0
			)
			tablespace tsp_chest
	)
	pctfree				20
	pctused				40
	storage
	(
	initial				40M
	next					3M
	minextents				1
	maxextents				unlimited
	pctincrease				0
	);
/*
create index ind_1_chest_pat_visit
	on pat_visit
		(
		admission_date_time
		)
	pctfree				20
	storage
	(
	initial				1M
	next					128K
	minextents				1
	maxextents				unlimited
	pctincrease				0
	)
	tablespace tsp_medrec;
create index ind_2_chest_pat_visit
	on pat_visit
		(
		discharge_date_time
		)
	pctfree				20
	storage
	(
	initial				1M
	next					128K
	minextents				1
	maxextents				unlimited
	pctincrease				0
	)
	tablespace tsp_medrec;
create index ind_3_chest_pat_visit
	on pat_visit
		(
		dept_admission_to
		)
	pctfree				20
	storage
	(
	initial				1M
	next					128K
	minextents				1
	maxextents				unlimited
	pctincrease				0
	)
	tablespace tsp_chest;
*/
grant select,insert,update,delete
	on	chest_pat_visit
	to	role_chest;

--人员基本信息 PERSONNEL_INFO	
CREATE TABLE PERSONNEL_INFO 
	(
	NAME		varchar2(10)	NOT NULL, 
	SID 		number(8)	NOT NULL, 
	TYPE		varchar2(8)	NOT NULL, 
	SEX		varchar2(4), 
	BIRTH_DATE	date,	
	ENROLLMENT_DATE	date,	
	TITLE_CODE	varchar2(10),
	TITLE_DATE	date,
	BUSINESS_CODE	varchar2(8),
	BUSINESS_DATE	date,	
	RETAIN_DATE	date,	
	BARGAIN		Number(3),
	BASE_PAY	Number(8,3),
	FIRST_LEVEL	varchar2(10),
	FIRST_DEGREE	varchar2(8),
	FIRST_DATE	date,
	FIRST_SCHOOL	varchar2(20),
	FIRST_FORMAT	varchar2(8),
	SECONDLY_LEVEL	varchar2(10),
	SECONDLY_DEGREE	varchar2(8),
	SECONDLY_DATE	Date,	
	SECONDLY_SCHOOL	varchar2(20),
	SECONDLY_FORMAT	varchar2(8),
	LINGUAL_LEVEL	varchar2(4),
	LANGUAGE	varchar2(10),
	MILITARY_RANK	varchar2(8),
	MILITARY_RANK_DATE	Date,	
	RKSJ		Date,	
	LEAVE_DATE	Date,	
	SPELL_CODE	varchar2(5),
	ZZMM		varchar2(8),
	NATIVE_PLACE	varchar2(20),
	ENGAGERMENT	varchar2(4),
	HOME_ADDRESS	varchar2(50),
	POSTALCODE	varchar2(6),
	PHONE_NUMBER	varchar2(16),
	constraints P_PERSONNEL_INFO
		PRIMARY KEY (SID)
		using index 
			PCTFREE 10 
			INITRANS 2 
			MAXTRANS 255 
			STORAGE
			(
			INITIAL 1M 
			NEXT 512K 
			MINEXTENTS 1 
			MAXEXTENTS unlimited
			PCTINCREASE 0 
			)
			TABLESPACE "TSP_CHEST"
	)
	PCTFREE 10 
	PCTUSED 40 
	INITRANS 1 
	MAXTRANS 255 
	STORAGE
	(
	INITIAL 1M 
	NEXT 512K 
	MINEXTENTS 1 
	MAXEXTENTS unlimited
	PCTINCREASE 0
	);

grant select,insert,update,delete
	on	PERSONNEL_INFO 
	to	role_CHEST;
	
--进修人员基本信息 APPRENTICE_INFO
CREATE TABLE APPRENTICE_INFO
	(
	NAME		varchar2(10)	NOT NULL, 
	SPELL_CODE	varchar2(5),
	SID 		number(8)	NOT NULL, 
	TYPE		varchar2(8)	NOT NULL, 
	SEX		varchar2(4),
	AGE		varchar2(3),
	BUSINESS_CODE	varchar2(8),
	ZZMM		varchar2(8),
	ENGAGERMENT	varchar2(4),
	SERVICE_AGENCY	varchar2(40),
	ZIP_CODE	varchar2(6),
	UNIT_PHONE_CODE	varchar2(16),
	SPECIALTY_WANT	varchar2(30),
	SPECIALTY_NOW	varchar2(30),
	REGISTER_DATE	DATE,
	TIME_LIMIT	varchar2(2),
	DWELLING_PLACE	varchar2(50),
	PHONE_CODE	varchar2(16),
	MOBILE_PHONE_CODE	varchar2(16),
	TEACHER_ONE	varchar2(10),
	TEACHER_TWO	varchar2(10),
	FIRST_LEVEL	varchar2(10),
	FIRST_DEGREE	varchar2(8),
	FIRST_DATE	DATE,	
	FIRST_SCHOOL	varchar2(20),
	FIRST_FORMAT	varchar2(8),
	SECONDLY_LEVEL	varchar2(10),
	SECONDLY_DEGREE	varchar2(8),
	SECONDLY_DATE	DATE,	
	SECONDLY_SCHOOL	varchar2(20),
	SECONDLY_FORMAT	varchar2(8),
	NATIVE_PLACE	VARCHAR2(20),
	LEAVE_DATE	DATE,
	constraints P_APPRENTICE_INFO
		PRIMARY KEY (SID)
		using index 
			PCTFREE 10 
			INITRANS 2 
			MAXTRANS 255 
			STORAGE
			(
			INITIAL 1M 
			NEXT 512K 
			MINEXTENTS 1 
			MAXEXTENTS unlimited
			PCTINCREASE 0 
			)
			TABLESPACE "TSP_CHEST"
	)
	PCTFREE 10 
	PCTUSED 40 
	INITRANS 1 
	MAXTRANS 255 
	STORAGE
	(
	INITIAL 1M 
	NEXT 512K 
	MINEXTENTS 1 
	MAXEXTENTS unlimited
	PCTINCREASE 0
	);

grant select,insert,update,delete
	on	APPRENTICE_INFO 
	to	role_CHEST;

--人员字典视图
create or replace view staff_dict
as
select name,SPELL_CODE
FROM PERSONNEL_INFO
WHERE LEAVE_DATE IS NULL
union
select '不祥','BX'
from dual
Union
select name,spell_code
from APPRENTICE_INFO;

--图片信息记录 chest_picture_info
CREATE TABLE chest_picture_info
(
	patient_id		varchar2(10)	not null,
	visit_id		number(2)	not null,
	pic_name		VARCHAR2(4)	NOT NULL,
	item_num		number(3),
	item_stat		varchar2(2),
	constraints PK_chest_picture_info
		primary key (patient_id,visit_id,pic_name,item_num)
		using index
		PCTFREE 10 
		INITRANS 2 
		MAXTRANS 255 
	STORAGE (
		INITIAL 10K 
		NEXT 10K 
		MINEXTENTS 1 
		MAXEXTENTS unlimited
		PCTINCREASE 0 
		FREELISTS 1
		)
	TABLESPACE "TSP_CHEST"
		)
		PCTFREE 10
		PCTUSED 80 
		INITRANS 1 
		MAXTRANS 255
	STORAGE ( 
		INITIAL 10K 
		NEXT 10K
		MINEXTENTS 1
		MAXEXTENTS unlimited
		PCTINCREASE 0
		);
grant select,insert,update,delete
	on	chest_picture_info
	to	role_CHEST;