sqlserver數(shù)據(jù)庫(kù)原理課程設(shè)計(jì)_工資管理系統(tǒng)設(shè)計(jì)(文件)
【正文】 ar(2), 加班天數(shù) Allowance_Salary Float, 津貼工資 primary key(Allowance_Month,Emp_ID) )。 創(chuàng)建存儲(chǔ)過(guò)程 1. 插入一條員工信息 USE [Salary] GO create PROCEDURE [dbo].[insert_Employee_Info] (Emp_ID_1 [varchar](15), Emp_Name_2 [varchar](8), Sex_3 [char](2), Birth_Data_4 [datetime], Emp_time_5 [datetime], Department_ID_6 [varchar](4), Duty_7 [varchar](10), 重慶科技學(xué)院《數(shù)據(jù)庫(kù)原理》課程設(shè)計(jì)報(bào)告 15 Politics_Status_8 [varchar](10), Telephone_9 [varchar](11)) AS Insert Into [Salary].[dbo].[Employee_Info] ([Emp_ID], [Emp_Name], [Sex], [Birth_Date], [Emp_time], [Department_ID], [Duty], [Politics_Status], [Telephone]) VALUES (Emp_ID_1, Emp_Name_2, Sex_3, Birth_Data_4, Emp_time_5, Department_ID_6, Duty_7, Politics_Status_8, Telephone_9) 2. 按部門編號(hào)查看部門信息 默認(rèn)部門編號(hào)為 39。 if Department_ID=39。 end 3. 增加獎(jiǎng)罰記錄 USE Salary GO CREATE PROCEDURE insert_Reward_pun (Record_ID bigint, Emp_ID varchar(15), Emp_Name varchar(8), Record_date datetime, Record_type varchar(2), Record_content varchar(30), Record_reason varchar(30), Allow_unit varchar(20), Remark varchar(200)) AS INSERT INTO Reward_pun (Record_ID, Emp_ID, Emp_Name, Record_date, Record_type, Record_content, Record_reason, 重慶科技學(xué)院《數(shù)據(jù)庫(kù)原理》課程設(shè)計(jì)報(bào)告 17 Allow_unit, Remark) VALUES(Record_ID, Emp_ID, Emp_Name, Record_date, Record_type, Record_content, Record_reason, Allow_unit, Remark)。 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。部門信息不存在 39。 print39。 end close Record_ID_cursor。 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。 員工基本工資更改 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。 4. 顯示所有普通員工信息 USE Salary GO create view Employee_Info_v as select *from Employee_Info where Duty = 39。 重慶科技學(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)一步的了解。 希望在以后的學(xué)習(xí)中能夠?qū)?shù)據(jù)庫(kù)系統(tǒng)的設(shè)計(jì)更加熟悉 有一個(gè)更加宏觀的看法 。在這次程序設(shè)計(jì)過(guò)程中，也出現(xiàn)了不少的問(wèn)題，但在老師和同學(xué)的耐心幫助下，問(wèn)題都得到了解 決，正是有 了你們的幫助，我才能順利地完成工資 管理系統(tǒng)的設(shè)計(jì)。 [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í)間都在學(xué)習(xí)理論的知識(shí)，所以對(duì)項(xiàng)目不是很了解。 由于 SQL SERVE 和 Oracle 之間的語(yǔ)法有很多的不同，所以導(dǎo)致在之前習(xí)慣性操作 Oracle的情況下轉(zhuǎn)入對(duì) SQL SERVE的運(yùn)用不是很熟練。 如圖： 5. 顯示工資總和 USE Salary GO create view Total_Salary_v as select SUM(Total_salary)Total_salary from Emp_Salary。 2. 顯示出勤信息 USE Salary GO create view Attendance_v as select , ,times from Attendance_info a。 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。 創(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. 如果插入
環(huán)評(píng)公示相關(guān)推薦
鄂ICP備17016276號(hào)-1