1樓：匿名使用者
1,if exists (select name from sysobjects
where name = 'num_em' and type = 'p')
drop procedure num_em
gocreate procedure num_em
as-- 員工號 userid
select count(userid) userid from employeesgo
2,if exists (select name from sysobjects
where name = 'is_em' and type = 'p')
drop procedure is_em
gocreate procedure is_em@userid varchar(20),@zt bit out
asbegin
-- 員工號 userid
if exists (select userid from employees where userid=@userid) begin return false end else begin delete from salary where userid=@userid if @@error <>0 return true endselect * from employees where userid='00001'endgo 3,
if exists (select name from sysobjects
where name = 'is_yfb' and type = 'p')
drop procedure is_yfb
gocreate procedure is_yfb@userid varchar(20),@xueli varchar(20) out
asbegin declare @xuli1 varchar(20)
--部門 cdept 員工 userid
if exists (select @xuli1=xueli from employees where cdept='研發部' and userid=@userid) begin return @xuli1 end else begin return 'no' endselect * from employees where userid in ('000001','302566')endgo 4, if exists (select name from sysobjects
where name = 'abc' and type = 'p')
drop procedure abc
gocreate procedure abc@userid varchar(20),@zt bit out
asbegin
--入職日期為djoindate 員工號位userid
if exists (select userid from employees where
(datediff(year,djoindate,getdate()) - (case when datepart(month,djoindate)>datepart(month,getdate()) then 1 when datepart(month,djoindate)=datepart(month,getdate()) and datepart(day,djoindate)>datepart(day,getdate()) then 1 else 0 end ))>=4
and userid=@userid) begin
--工資 gz
update employees set gz=gz+500 where userid=@userid
return true end else begin return false end
endgo5,
if exists (select name from sysobjects
where name = 'zj' and type = 'p')
drop procedure zj
gocreate procedure zj@userid varchar(20)
asbegin
--入職日期為djoindate 員工號位userid
if exists (select userid from employees where
(datediff(year,djoindate,getdate()) - (case when datepart(month,djoindate)>datepart(month,getdate()) then 1 when datepart(month,djoindate)=datepart(month,getdate()) and datepart(day,djoindate)>datepart(day,getdate()) then 1 else 0 end ))>=6
and userid=@userid) begin
--部門 cdept
update employees set cdept='經理辦公室' where userid=@userid end
endgo
6,if exists (select name from sysobjects
where name = 'zengjia1' and type = 'p')
drop procedure zengjia1
gocreate procedure zengjia1@zt bit out
asbegin
declare @userid varchar(20)
declare userid_cursor cursor for
select userid
from employees
where (datediff(year,djoindate,getdate()) - (case when datepart(month,djoindate)>datepart(month,getdate()) then 1 when datepart(month,djoindate)=datepart(month,getdate()) and datepart(day,djoindate)>datepart(day,getdate()) then 1 else 0 end ))>=4
open userid_cursor
fetch next from userid_cursor into @userid if @@fetch_status <> 0
print '無4年以上員工 ' while @@fetch_status = 0
begin
--工資 gz 員工號=userid
update employees set gz=gz+500 where userid=@userid
if @@error <>0
begin
return false
end fetch next from userid_cursor into @userid
endclose userid_cursor
deallocate userid_cursor return true
endgo 7,if exists (select name from sysobjects
where name = 'zj' and type = 'p')
drop procedure zj
gocreate procedure zj@zt bit out
asbegin
declare @userid varchar(20)
declare userid_cursor cursor for
select userid
from employees
where (datediff(year,djoindate,getdate()) - (case when datepart(month,djoindate)>datepart(month,getdate()) then 1 when datepart(month,djoindate)=datepart(month,getdate()) and datepart(day,djoindate)>datepart(day,getdate()) then 1 else 0 end ))>=6
open userid_cursor
fetch next from userid_cursor into @userid if @@fetch_status <> 0
print '無6年以上員工 ' while @@fetch_status = 0
begin
--部門 cdept
update employees set cdept='經理辦公室' where userid=@userid
if @@error <>0
begin
return false
endfetch next from userid_cursor into @userid
endclose userid_cursor
deallocate userid_cursor
return trueendgo 以上儲存過程沒具體測試，
2樓：匿名使用者
分在哪呢？我天天都在寫儲存過程
！！！！高分求sql問題答案，懂得大師請幫幫忙
sql題目求答案
3樓：匿名使用者
/*建立moonfox_db資料庫*/
use master
if exists(select * from sysdatabases where name='moonfox_db')
drop database moonfox_db
create database moonfox_dbon(
name='moonfox_db_data',
filename='d:\visual studio 2008 & sql server 2005\sql server\moonfox_db.mdf',
size=10,
filegrowth=2mb
)log on
(name='moonfox_db_log',
filename='d:\visual studio 2008 & sql server 2005\sql server\moonfox_db.ldf',
size=5,
filegrowth=20%
)/*建立department表*/
use moonfox_db
if exists(select * from sysobjects where name='department')
drop table department
create table department
(did int identity (1,1)primary key,--部門編號，主鍵
dname nvarchar(20),--部門名稱
address nvarchar(50),--部門地址
photo decimal(12,0),--**
)/*建立employee表*/
use moonfox_db
if exists(select * from sysobjects where name='employee')
drop table employee
create table employee
(eid int identity (1,1)primary key,--職工編號，主鍵
ename varchar(10),--職工名
gender nchar(2) check(gender='男' or gender='女'),--性別，新增限制
position nvarchar(10) check(position='員工' or position='組長' or position='經理'),--職務，新增限制
address nvarchar(50),--家庭地址
did int,--部門編號，外來鍵
foreign key(did) references department(did)--外來鍵約束
)/*建立care表*/
use moonfox_db
if exists(select * from sysobjects where name='care')
drop table care
create table care
(cid int identity (1,1)primary key,--保健卡編號，主鍵
eid int,--職工號，外來鍵
foreign key(eid) references employee(eid),--外來鍵約束
checkdate datetime,--檢查身體日期
physicalcondition nvarchar(4) check(physicalcondition='一般' or physicalcondition='差' or physicalcondition='好'),--健康狀況
)/*建立care表約束*/
alter table care
addconstraint df_checkdate default(getdate()) for checkdate--預設，預設淨時間為當前計算機時間 路徑自己修改，試圖自己做，選擇語句自己寫。我該睡覺了，抱歉，你試著在sql server中執行下，我等著休息，也不知道寫的有沒有錯誤，沒時間幫你寫省下的了。不急著用的話我明天幫你寫吧。
求sql答案，求SQL答案
老師小喬 1.c 是被長期存放在計算機內的 有組織的 統一管理的相關資料的集合。a data b information c db d dbs 2.需求分析的主要方法是 a a 自頂向下 b 自底向上 c 從左到右 d 混合策略 3.在關係運算中，選取符合條件的元組是 d 運算。a 除法 b 投影 ...
SQL的練習,求答案
建立moonfox db資料庫 use master if exists select from sysdatabases where name moonfox db drop database moonfox db create database moonfox dbon name moonfox...
SQL資料庫問題！急啊，高分求一道SQL資料庫問題，急！！！（線上等）
估計是sql server的備份檔案,你在查詢分析器中執行下面的語句檢查一下就知道了 restore verifyonly from disk c 你的檔案 還原的話,到企業管理器裡面去還原 不過你的檔案這麼小,不知道是否完全備份,如果不是完全備份,則不可還原 還原資料庫 企業管理器 右鍵 資料庫 ...