sql.sql

来自「固定资产管理信息系统,虽然还有些不足,但基本功能已实现!希望大家多多改进!」· SQL 代码 · 共 330 行

drop database ASSETSMANAGEDB

create database ASSETSMANAGEDB

use ASSETSMANAGEDB
 --执行了----
drop table T_USER  
create table T_USER
(
   UID VarCHAR(4) primary key,
   UNAME VarCHAR(10) NOT NULL,
   UPWD VarCHAR(6) NOT NULL,
   UTYPE SMALLINT NOT NULL
)

insert into T_USER values('C001','thp520','520134',1)
insert into T_USER values('C002','abc','654321',2)
insert into T_USER values('S003','hzm','123456',3)

select*from T_USER

delete from T_USER

update T_USER set UPWD = 123456 where UNAME = 'hzm'


--创建一个存储过程来自动的增加数据库的数据 
--===当用户要增加用户表中的记录时，应当调用此存储过程用来增加表（T_USER）中的记录===========
CREATE PROCEDURE PR_T_USER	
(
  @UNAME VarCHAR(10),
  @UPWD VarCHAR(6),
  @UTYPE SMALLINT
)
AS 
BEGIN
   DECLARE @UID INT
   DECLARE @STR CHAR(4)
   SET @UID=(SELECT COUNT(*) FROM T_USER)
   IF (floor(@UID/10)=0)
     BEGIN 
        SET @STR='C00'+CONVERT(CHAR(3),@UID)
      --  INSERT INTO T_USER VALUES(@STR,@UNAME,@UPWD,@UTYPE)
     END  
   IF ((floor(@UID/10)>0) AND (floor(@UID/10)<10))
     BEGIN
        SET @STR='C0'+CONVERT(CHAR(3),@UID)
      --  INSERT INTO T_USER VALUES(@STR,@UNAME,@UPWD,@UTYPE)
     END 
   IF ((floor(@UID/10)>=10) AND (floor(@UID/10)<100))
     BEGIN
        SET @STR='C'+CONVERT(CHAR(3),@UID)
     --   INSERT INTO T_USER VALUES(@STR,@UNAME,@UPWD,@UTYPE)
     END
   IF (@UID>1000) 
     BEGIN
        PRINT '数据库中记录以满，请修改存储过程'
     END
   INSERT INTO T_USER VALUES(@STR,@UNAME,@UPWD,@UTYPE)
END 


--测试语句
select * from T_USER

exec PR_T_USER @UNAME='hzm',@UPWD='123456',@UTYPE=3
exec PR_T_USER @UNAME='thp520',@UPWD='520134',@UTYPE=2
exec PR_T_USER @UNAME='大林',@UPWD='520134',@UTYPE=1


DROP TABLE T_USER
DROP PROC PR_T_USER

--==========================================================================================
 
--创建资产类别表
--此表主要用来查询，动态的维护资产的类别
--例如：现在想增加一个类别，我就只需要在此表中增加一条记录，然后就可以在前台进行自动的更新

--执行了----
create table T_ASSET_TYPE
( 
   TYPE_CODE CHAR(1) PRIMARY KEY,      --资产类别代号
   TYPE_NAME CHAR(20) NOT NULL         --资产类别的名称
)

insert into T_ASSET_TYPE values('C','教学设备')
insert into T_ASSET_TYPE values('T','管理设备')
insert into T_ASSET_TYPE values('F','办公室设备')

delete from T_ASSET_TYPE where TYPE_CODE=''

--测试语句
SELECT * FROM T_ASSET_TYPE

DROP TABLE T_ASSET_TYPE

--==========================================================================================

--创建部门表
--此表主要用来查询，检索，动态的维护部门的类别
--例如：现在想增加一个类别，我就只需要在此表中增加一条记录，然后就可以在前台进行自动的更新

--执行了----
create table T_DEPARTMENT
(
  DEP_CODE CHAR(1) PRIMARY KEY,        --部门代号
  TYPE_NAME CHAR(20) NOT NULL          --部门名称
)

insert into T_DEPARTMENT values('S','软件学院')
insert into T_DEPARTMENT values('M','民政系')
insert into T_DEPARTMENT values('Y','艺术系')
insert into T_DEPARTMENT values('K','康复系')
insert into T_DEPARTMENT values('J','经贸系')
insert into T_DEPARTMENT values('B','宾仪系')

--测试语句
SELECT * FROM T_DEPARTMENT 

delete from T_DEPARTMENT where DEP_CODE = ''

DROP TABLE T_DEPARTMENT

--==========================================================================================



--执行了----
drop table T_ASSET_MAIN

CREATE TABLE T_ASSET_MAIN
(
  ASSET_CODE VARCHAR(6) PRIMARY KEY,
  ASSET_NAME VARCHAR(20), 
  USE_DEP VARCHAR(20),
  BUY_TIME DATETIME,
  ASSENT_QUANTITY   INT,
  ASSENT_PRICE  MONEY,
  BUY_MAN VARCHAR(10),
  BURDEN_MAN VARCHAR(10),
  USE_MAN VARCHAR(10),
  ADD_DATE DATETIME,
  ADD_REN VARCHAR(10),
  ASSET_TYPE VARCHAR,
  BAK VARCHAR(200)
)

insert into T_ASSET_MAIN values('CS001','电脑','软件学院','2006-4-29',2,3800,'陈小飞','徐刚强','谭海鹏','2006-4-29','谭海鹏','1','this is a good computer')
insert into T_ASSET_MAIN values('CS002','电脑','软件学院','2006-4-30',2,4800,'陈小飞','徐刚强','谭海鹏','2006-4-30','谭海鹏','1','this is a good computer')
insert into T_ASSET_MAIN values('CS003','房屋','软件学院','2006-4-30',2,14800,'陈小飞','徐刚强','谭海鹏','2006-4-30','谭海鹏','3','this is a good computer')
insert into T_ASSET_MAIN values('CM001','电脑','民政系','2006-4-29',2,3800,'陈小飞','徐刚强','谭海鹏','2006-4-29','谭海鹏','2','this is a good computer')
insert into T_ASSET_MAIN values('CJ002','电脑','经贸系','2006-4-30',2,4800,'陈小飞','徐刚强','谭海鹏','2006-4-30','谭海鹏','4','this is a good computer')
insert into T_ASSET_MAIN values('CY003','房屋','艺术系','2006-4-30',2,14800,'陈小飞','徐刚强','谭海鹏','2006-4-30','谭海鹏','3','this is a good computer')
insert into T_ASSET_MAIN values('CK003','房屋','康复系','2006-4-30',2,14800,'陈小飞','徐刚强','谭海鹏','2006-4-30','谭海鹏','2','this is a good computer')
insert into T_ASSET_MAIN values('YK001','房屋','宾仪系','2006-5-5',2,14800,'陈小飞','徐刚强','谭海鹏','2006-5-5','谭海鹏','2','this is a good computer')


Select * from T_ASSET_MAIN Where USE_DEP = '软件学院' 

delete from T_ASSET_MAIN ASSET_CODE =  'Cs002'

delete from T_ASSET_MAIN where ASSET_CODE =  'Cs002'

select*from T_ASSET_MAIN

--创建一个存储过程用来增加资产登记表（T_ASSET_MAIN）中的记录。并使自动的匹配和增加。形如：CS001,CS002
--当程序员需要在资产登记表（T_ASSET_MAIN）中的记录时，应该调用次存储过程，以完成组合、键ID的增加。
CREATE PROCEDURE PR_T_ASSET_MAIN
(
  @TYPE CHAR(1),               --用来传递一个字母
  @DEPARTMENT CHAR(1),         --用来传递第二字母
  @ASSET_NAME VarCHAR(20),        
  @USE_DEP VarCHAR(20),           
  @BUY_TIME DATETIME, 
  @ASSENT_QUANTITY   INT,   
  @ASSENT_PRICE  MONEY,      
  @BUY_MAN VarCHAR(10),           
  @BURDEN_MAN CHAR(10),        
  @USE_MAN VarCHAR(10),           
  @ADD_DATE DATETIME,          
  @ADD_REN VarCHAR(10),           
  @ASSET_TYPE VarCHAR,            
  @BAK VARCHAR(200)            
)
AS 
BEGIN
   DECLARE @UID INT
   DECLARE @STR CHAR(6)
   SET @UID=(SELECT COUNT(*) FROM T_ASSET_MAIN)
   IF (floor(@UID/10)=0)
     BEGIN 
        SET @STR=@TYPE+@DEPARTMENT+'00'+CONVERT(CHAR(3),@UID)
     END  
   IF ((floor(@UID/10)>0) AND (floor(@UID/10)<10))
     BEGIN
        SET @STR=@TYPE+@DEPARTMENT+'0'+CONVERT(CHAR(3),@UID)  
     END 
   IF ((floor(@UID/10)>=10) AND (floor(@UID/10)<100))
     BEGIN
        SET @STR=@TYPE+@DEPARTMENT+CONVERT(CHAR(3),@UID)
     END
   IF (@UID>1000) 
     BEGIN
        PRINT '数据库中记录以满，请修改存储过程'
     END
   INSERT INTO T_ASSET_MAIN
             VALUES( 
                     @STR,
                     @ASSET_NAME,  
                     @USE_DEP, 
                     @BUY_TIME,
                     @ASSENT_QUANTITY,
                     @ASSENT_PRICE,
                     @BUY_MAN,
                     @BURDEN_MAN,
                     @USE_MAN,
                     @ADD_DATE,
                     @ADD_REN,
                     @ASSET_TYPE,
                     @BAK
                    )
END 


--测试语句
SELECT * FROM T_ASSET_MAIN

insert into T_ASSET_MAIN values('CS001','电脑','软件学院','2006-4-29',2,3800,'陈小飞','徐刚强','谭海鹏','2006-4-29','谭海鹏','1','this is a good computer')
insert into T_ASSET_MAIN values('CS002','电脑','软件学院','2006-4-30',2,4800,'陈小飞','徐刚强','谭海鹏','2006-4-30','谭海鹏','1','this is a good computer')
insert into T_ASSET_MAIN values('CS003','房屋','软件学院','2006-4-30',2,14800,'陈小飞','徐刚强','谭海鹏','2006-4-30','谭海鹏','3','this is a good computer')
insert into T_ASSET_MAIN values('CM001','电脑','民政系','2006-4-29',2,3800,'陈小飞','徐刚强','谭海鹏','2006-4-29','谭海鹏','2','this is a good computer')
insert into T_ASSET_MAIN values('CJ002','电脑','经贸系','2006-4-30',2,4800,'陈小飞','徐刚强','谭海鹏','2006-4-30','谭海鹏','4','this is a good computer')
insert into T_ASSET_MAIN values('CY003','房屋','艺术系','2006-4-30',2,14800,'陈小飞','徐刚强','谭海鹏','2006-4-30','谭海鹏','3','this is a good computer')
insert into T_ASSET_MAIN values('CK003','房屋','康复系','2006-4-30',2,14800,'陈小飞','徐刚强','谭海鹏','2006-4-30','谭海鹏','2','this is a good computer')
insert into T_ASSET_MAIN values('YK001','房屋','宾仪系','2006-5-5',2,14800,'陈小飞','徐刚强','谭海鹏','2006-5-5','谭海鹏','2','this is a good computer')


EXEC  PR_T_ASSET_MAIN @TYPE='C',@DEPARTMENT='S',@ASSET_NAME='IUJ',@USE_DEP='HGJK',@BUY_TIME=2006,@BUY_MAN='JHKJ',@BURDEN_MAN='UGG',@USE_MAN='GJ',@ADD_DATE=2006,@ADD_REN='CXF',@ASSET_TYPE='1',@BAK='CXF'

DROP TABLE T_ASSET_MAIN
DROP PROC PR_T_ASSET_MAIN

DELETE FROM T_ASSET_MAIN


--==========================================================================================

--创建报废登记表
CREATE TABLE T_SCRAP
(
  ASSET_CODE VARCHAR(6) PRIMARY KEY FOREIGN KEY REFERENCES T_ASSET_MAIN(ASSET_CODE),   --资产登记表中的ID
  ASSET_NAME VARCHAR(20) NOT NULL,                                         --资产名称
  TYPE_NAME VARCHAR(20) NOT NULL,                                          --部门名称
  SCRAP_MAN VARCHAR(10) NOT NULL,                                          --报废人
  ASSET_PRICE MONEY NOT NULL,                                           --资产净值
  SCRAP_CAUSE VARCHAR(200),                                             --报废的原因说明
  SCRAP_DATE DATETIME NOT NULL,                                         --报废登记的时间
  ADD_NAME VARCHAR(10),                                                    --登记人
  BAK VARCHAR(200)                                                      --备注说明
)

drop table T_SCRAP
--创建更改资产登记表（T_ASSET_MAIN）中资产状态记录（ASSET_TYPE)的触发器（TRI_T_SCRAP）
CREATE TRIGGER TRI_T_SCRAP
ON T_SCRAP
FOR INSERT
AS 
BEGIN
   DECLARE @ASSET_CODE CHAR(6)
   SET @ASSET_CODE=(SELECT ASSET_CODE FROM INSERTED)
   UPDATE T_ASSET_MAIN SET ASSET_TYPE='3' WHERE ASSET_CODE=@ASSET_CODE
END


--利用存储过程进行增加报废登记表中的记录，并执行触发器（TRI_T_SCRAP）

--测试语句
SELECT * FROM T_SCRAP
INSERT INTO T_SCRAP VALUES ('CS001','CXF','CXF',1520.5,'CXF',2006,'SA','CXF')  
INSERT INTO T_SCRAP VALUES ('CS011','CXF','CXF',1520.5,'CXF',2006,'SA','CXF')  --有可能产生外键约束


DELETE FROM T_SCRAP

--==========================================================================================

--创建资产转移表
CREATE TABLE T_TRANSFER
( 
  ASSET_CODE VarCHAR(6) PRIMARY KEY FOREIGN KEY REFERENCES T_ASSET_MAIN(ASSET_CODE),  --转出资产在资产登记表的ID
  ASSET_NAME VARCHAR(20) NOT NULL,
  O_DEP VarCHAR(20) NOT NULL,               --转出部门的名称
  I_DEP VarCHAR(20) NOT NULL,               --转入部门的名称
  TRANSFER_DATE DATETIME NOT NULL,       --转移的日期
  ADD_NAME VarCHAR(10),                     --
  BAK VARCHAR(200)
)

--创建一个触发器
CREATE TRIGGER TRI_T_TRANSFER
ON T_TRANSFER
FOR INSERT
AS 
BEGIN
   DECLARE @ASSET_CODE CHAR(6)
   SET @ASSET_CODE=(SELECT ASSET_CODE FROM INSERTED)
   UPDATE T_ASSET_MAIN SET ASSET_TYPE='4' WHERE ASSET_CODE=@ASSET_CODE
END

--测试语句
SELECT * FROM T_TRANSFER
DROP TABLE T_TRANSFER
DELETE FROM T_TRANSFER

DROP TRIGGER TRI_T_TRANSFER

INSERT INTO T_TRANSFER VALUES ('CS001','CXF','CXF',2001,'CXF','CXF')

--在此处的插入说明：
--当在资产转移表（T_TRANSFER）中插入条记录时，会触发触发器，
--并且要在此处调用存储过程（PR_T_ASSET_MAIN）
--从业务的逻辑处理层传来相应的参数给次存储过程。
--例如：
--例子开始的SQL查询语句
INSERT INTO T_TRANSFER VALUES ('CS002','CXF','CXF',2006,'CXF','CXF')
EXEC  PR_T_ASSET_MAIN @TYPE='B',@DEPARTMENT='E',@ASSET_NAME='IUJ',@USE_DEP='HGJK',@BUY_TIME=2006,@BUY_MAN='JHKJ',@BURDEN_MAN='UGG',@USE_MAN='GJ',@ADD_DATE=2006,@ADD_REN='CXF',@ASSET_TYPE='1',@BAK='CXF'
--例子结素
--例子注释：次处为了演示的方便，将@TYEP,@DEPARTMENT,等参数写死了，其