【正文】 [4] 陳根才等 . 數(shù)據(jù)庫(kù)課程設(shè)計(jì) ．浙江． 浙江大學(xué)出版社 ． 2020 。 重慶科技學(xué)院《數(shù)據(jù)庫(kù)原理》課程設(shè)計(jì)報(bào)告 29 參考文獻(xiàn) [1] 雷亮等《數(shù)據(jù)庫(kù)原理課程設(shè)計(jì)》指導(dǎo)書 [2] 王珊、薩師煊．《數(shù)據(jù)庫(kù)系統(tǒng)概述》 (第四版 ) ．北京：高等教育出版社． 2020。在這次程序設(shè)計(jì)過(guò)程中，也出現(xiàn)了不少的問(wèn)題，但在老師和同學(xué)的耐心幫助下，問(wèn)題都得到了解 決，正是有 了你們的幫助，我才能順利地完成工資 管理系統(tǒng)的設(shè)計(jì)。由于以前大部分時(shí)間都在學(xué)習(xí)理論的知識(shí)，所以對(duì)項(xiàng)目不是很了解。 希望在以后的學(xué)習(xí)中能夠?qū)?shù)據(jù)庫(kù)系統(tǒng)的設(shè)計(jì)更加熟悉 有一個(gè)更加宏觀的看法 。 由于 SQL SERVE 和 Oracle 之間的語(yǔ)法有很多的不同，所以導(dǎo)致在之前習(xí)慣性操作 Oracle的情況下轉(zhuǎn)入對(duì) SQL SERVE的運(yùn)用不是很熟練。 重慶科技學(xué)院《數(shù)據(jù)庫(kù)原理》課程設(shè)計(jì)報(bào)告 27 總 結(jié) 在這一 周的課程設(shè)計(jì)中我學(xué)到了許多書本之外的東西，動(dòng)手能力得到了提高，對(duì)數(shù)據(jù)庫(kù)系統(tǒng)的設(shè)計(jì)流程有了進(jìn)一步的了解。 如圖： 5. 顯示工資總和 USE Salary GO create view Total_Salary_v as select SUM(Total_salary)Total_salary from Emp_Salary。 4. 顯示所有普通員工信息 USE Salary GO create view Employee_Info_v as select *from Employee_Info where Duty = 39。 2. 顯示出勤信息 USE Salary GO create view Attendance_v as select , ,times from Attendance_info a。 員工基本工資更改 create trigger update_Employee_Info_tr on Employee_Info for insert,update as declare a varchar(15), b varchar(10), c float select a=Emp_ID from deleted select b=Duty from inserted select c=Base_Salary from Base_Salary where Duty = b update Emp_Salary set Base_Salary = c where = a 員工工資信息更改 create trigger update_Allowance_Salary_tr on Allowance for update,insert as declare a varchar(15), b varchar(10),c float 重慶科技學(xué)院《數(shù)據(jù)庫(kù)原理》課程設(shè)計(jì)報(bào)告 24 select b=Allowance_Month from inserted select a=Emp_ID from updated select c=Allowance_Salary from inserted update Emp_Salary set Allowance_Salary = c where = a and = b。 rollback transaction end 4. 刪除一個(gè)部門 刪除該部門下的所有員工信息 重慶科技學(xué)院《數(shù)據(jù)庫(kù)原理》課程設(shè)計(jì)報(bào)告 23 create trigger delete_Department_Info_tr on Department_Info for delete as delete from Employee_Info where Department_ID in (select Department_ID from deleted ) print39。 rollback transaction end 3. 如果更新津貼 月份信息錯(cuò)誤 打印 錯(cuò)誤 create trigger update_Allowance_tr on Allowance for update,insert as if (select Allowance_Month from inserted)12 or (select Allowance_Month from inserted)1 begin print 39。 創(chuàng)建觸發(fā)器 1. 刪除一條員工信息 一并刪除相關(guān)員工信息 create trigger delete_Employee_Info_tr on Employee_Info FOR DELETE as 重慶科技學(xué)院《數(shù)據(jù)庫(kù)原理》課程設(shè)計(jì)報(bào)告 22 delete from Pro_title WHERE Emp_ID in (select Emp_ID from deleted) delete from Reward_pun WHERE Emp_ID in (select Emp_ID from deleted) delete from Allowance WHERE Emp_ID in (select Emp_ID from deleted) delete from Emp_Salary WHERE Emp_ID in (select Emp_ID from deleted) delete from Attendance_info WHERE Emp_ID in (select Emp_ID from deleted) 2. 如果插入員工信息中部門不存在 則打印錯(cuò)誤 create trigger insert_Employee_Info_tr on Employee_Info for insert as if (select Department_ID from inserted) not in (select Department_ID from Department_Info ) begin print 39。 end close Record_ID_cursor。 end else begin print39。 print39。 deallocate Department_ID_cursor。部門信息不存在 39。刪除成功 39。 END 6. 按部門編號(hào)刪除部門信息 USE Salary GO create PROCEDURE delete_Department_Info (Department_ID VARCHAR(4)) AS DECLARE Department_ID_1 varchar(4), Department_ID_cursor CURSOR SELECT Department_ID from Department_Info OPEN Department_ID_cursor fetch next from Department_ID_cursor into Department_ID_1 if(Department_ID_1 = Department_ID) begin DELETE FROM Department_Info WHERE Department_ID = Department_ID。 5. 插入工資 基本工資根據(jù)員工職務(wù)插入 津貼根據(jù)員工 ID 和月份查找 再插入 Use Salary GO CREATE PROCEDURE insert_Emp_Salary (Emp_ID Varchar(15), House_Allowance Float, Trade_union_fee Float, Utilities_fee Float, House_fund Float, Endownment_insutance Float, Rewards_punishment Float, Total_salary Float, Ine_tax Float, Duty Varchar(10), Month_ID Varchar(10)) AS DECLARE Base_Salary float, Allowance_Salary float, Base_Salary_c CURSOR 重慶科技學(xué)院《數(shù)據(jù)庫(kù)原理》課程設(shè)計(jì)報(bào)告 19 SELECT , from Base_Salary b,Allowance a where = Month_ID and = Emp_ID and = Duty BEGIN OPEN BASE_SALARY_c fetch next from Base_Salary_c into Base_Salary,Allowance_Salary INSERT INTO Emp_Salary (Emp_ID, Base_Salary, House_Allowance, Allowance_Salary, Trade_union_fee, Utilities_fee, House_fund, Endownment_insutance, Rewards_punishment, Total_salary, Ine_tax, Duty, Month_ID) VALUES(Emp_ID, Base_Salary, House_Allowance, Allowance_Salary, Trade_union_fee, Utilities_fee, House_fund, Endownment_insutance, Rewards_punishment, 重慶科技學(xué)院《數(shù)據(jù)庫(kù)原理》課程設(shè)計(jì)報(bào)告 20 Total_salary, Ine_tax, Duty, Month_ID) CLOSE Base_Salary_c。 end 3. 增加獎(jiǎng)罰