先把建表命令给出吧——
再请各位看怎样构造存储过程——
create procedure pro_usrlist
as
...可能确实不太容易?/*
create database usrmng
on
primary
(name=usrmng_data,
filename='c:\Program Files\um\data\usrmng.mdf',
size=5MB,
maxsize=20MB,
filegrowth=10%)
log on
(name=usrmng_log,
filename='c:\Program Files\um\data\usrmng.ldf',
size=2MB,
maxsize=10MB,
filegrowth=1MB)gouse usrmng*/create table group
(group_id varchar(3) constraint PK_grp primary key clustered,
group_name varchar(30) not NULL,
right varchar(4))gocreate table user
(user_id varchar(3) constraint PK_usr primary key clustered,
user_name varchar(30) not NULL)gocreate table member
(group_id varchar(3) constraint FK_mem_grp foreign key
references group(group_id),
user_id varchar(3) FK_mem_usr foreign key references user(user_id),
constraint PK_mem primary key(group_id,user_id))go
再请各位看怎样构造存储过程——
create procedure pro_usrlist
as
...可能确实不太容易?/*
create database usrmng
on
primary
(name=usrmng_data,
filename='c:\Program Files\um\data\usrmng.mdf',
size=5MB,
maxsize=20MB,
filegrowth=10%)
log on
(name=usrmng_log,
filename='c:\Program Files\um\data\usrmng.ldf',
size=2MB,
maxsize=10MB,
filegrowth=1MB)gouse usrmng*/create table group
(group_id varchar(3) constraint PK_grp primary key clustered,
group_name varchar(30) not NULL,
right varchar(4))gocreate table user
(user_id varchar(3) constraint PK_usr primary key clustered,
user_name varchar(30) not NULL)gocreate table member
(group_id varchar(3) constraint FK_mem_grp foreign key
references group(group_id),
user_id varchar(3) FK_mem_usr foreign key references user(user_id),
constraint PK_mem primary key(group_id,user_id))go
and
O
请问一定要全部用存储过程完成吗?我再试试。
select * from group;
select user_name from user,member where user.user_id=member.user_id
and member.group_id=第一个光标选出来的group_id.
第二个cursor套在第一个里
用变量把选出的user_name拼起来
然后将cursor1的Group_ID Group_Name Right和cursor2产生的变量(user_list)
插如一个临时表(事先建好)
select * from 临时表
由于手头没装sql_server,所以没有调,oracle下可以的
先建一个存储过程可以将某表的某个字符列的值串在一起返回
然后再建一个存储过程,针对不重复的group_id调用先前的存储过程
最后将结果写入临时表至haihong:
两个光标,只有一个@@fetch_status,会不会乱套?
不过我仍有些淡淡的失望。
其实问完问题的那个上午我已经把那个存储过程做出来了——
只不过深知自已太菜了。
不好意思出来现丑。
一直想学习学习大侠们的妙招。
可惜就是没有人提供源码!
我当然也是用光标做的,不过我只用了一个光标。
对于haihong大侠,我当时也有同none大侠一样的疑问:
两个光标,只有一个@@fetch_status,会不会乱套?
另外,我设问时已相当明确要存储过程原码,即使Oralce也应有原码吧?
我希望诸位大侠用完整的原码教我,多谢!
我的实际SP其实比这要复杂得多,但为了大家好理解我用NT用户组简化了——
我再打个比方——
比如很多人一起干同一件事,算工时算收益,你就必须这样列明才清晰。
而不能每个人都占一行。
在前端当然怎么做都能实现,不过那样我得多写很多,并且绝对不会有做成存储过程好。
望大家继续赐教,我好打分。
分虽不多,现在这情形,实在难以给分。
WHQ的大侠风范,高手气派,蔼然可亲,肃然可敬!
如下——————我想写一个触发器,作用是修改表的时候让PRIMARY字段自动+1,就想ACCESS一样。由于从来没有写过触发器,请指教。
另外一个问题,哪里有关于SQL SERVER编程的中文教材,929你不要回答了,你给我的主页的确有很多好书...名:),连接全错了:(。
Re:触发器问题 WHQ 2000-3-13 19:41:39 20
Create TRIGGER AutoIncreate ON INSERT
DECLARE @ID AS INTEGER;
SELECT @ID = select max(ID)+1 from Table; // ID为需要自动加1的字段名
update table set ID = @ID WHERE ID = inserted.ID
Re:触发器问题 King 2000-3-13 19:43:40 0
买单:)
Re:触发器问题 lixq 2000-3-16 22:06:56 0
我以前也碰到过这个问题,因不会用触发器。费了老大劲也没理想地解决。这可解决大问题了。真高兴!
(group_id varchar(3) constraint PK_grp primary key clustered,
group_name varchar(30) not NULL,
rights varchar(4))gocreate table users
(usr_id varchar(3) constraint PK_usr primary key clustered,
usr_name varchar(30) not NULL)gocreate table member
(group_id varchar(3) constraint FK_mem_grp
foreign key references groups(group_id),
usr_id varchar(3) constraint FK_mem_usr
foreign key references users(usr_id),
constraint PK_mem primary key(group_id,usr_id))go
declare @pt_grpid varchar(3),@pt_usrname varchar(30),@grpid varchar(3),@usrname varchar(30)
declare cur_member scroll cursor
for
select member.group_id,users.usr_name from member inner join users
on member.usr_id=users.usr_id
order by member.group_id
open cur_member
fetch first from cur_member
into @grpid,@usrname
set @pt_grpid=''
set @pt_usrname=''
create table #usrlst
(group_id varchar(11),
userlist varchar(30))
while(@@FETCH_STATUS=0)
begin
if(@pt_grpid<>@grpid)
begin
if(@pt_grpid<>'')
begin
insert #usrlst
values(@pt_grpid,@pt_usrname)
end
set @pt_grpid=@grpid
set @pt_usrname=''
end
if(@pt_usrname='')
begin
set @pt_usrname=@usrname
end
else
begin
set @pt_usrname=@pt_usrname+','+@usrname
end
fetch next from cur_member
into @grpid,@usrname
end
insert #usrlst
values(@pt_grpid,@pt_usrname)
close cur_member
deallocate cur_member
select distinct groups.group_id,groups.group_name,#usrlst.userlist,groups.rights
from groups inner join #usrlst
on groups.group_id=#usrlst.group_id
drop table #usrlst
(@grpname varchar(30))
as
declare @pt_grpid varchar(3),@pt_usrname varchar(30),@grpid varchar(3),@usrname varchar(30)
declare cur_member scroll cursor
for
select member.group_id,users.usr_name from member inner join users
on member.usr_id=users.usr_id
order by member.group_id
open cur_member
fetch first from cur_member
into @grpid,@usrname
set @pt_grpid=''
set @pt_usrname=''
create table #usrlst
(group_id varchar(11),
userlist varchar(30))
while(@@FETCH_STATUS=0)
begin
if(@pt_grpid<>@grpid)
begin
if(@pt_grpid<>'')
begin
insert #usrlst
values(@pt_grpid,@pt_usrname)
end
set @pt_grpid=@grpid
set @pt_usrname=''
end
if(@pt_usrname='')
begin
set @pt_usrname=@usrname
end
else
begin
set @pt_usrname=@pt_usrname+','+@usrname
end
fetch next from cur_member
into @grpid,@usrname
end
insert #usrlst
values(@pt_grpid,@pt_usrname)
close cur_member
deallocate cur_member
select distinct groups.group_id,groups.group_name,#usrlst.userlist,groups.rights
from groups inner join #usrlst
on groups.group_id=#usrlst.group_id
where groups.group_name=@grpname
drop table #usrlst
在15000条数据的情况下,游标要几小时
create procedure pro_name
(
@FindGoup char(30)="%"
)
as
begin
create table #temptable (Group_ID char(5),
Group_Name char(5),
User_List char(255)/*假设可以存下所有用户*/ )
select * into #tempMember from Member where 1=2
declare @Group_ID char(5),@User_ID char(5)
select *
into #tempGroup
from Group
where Group_Name like @FindGoup
select @Group_ID =""
while (1=1)
begin
select @Group_ID=min(Group_ID)
from (select Group_ID from Group where Group_ID>@Group_ID)
if @@RowCount=0
break
insert into #temptable values(Group_ID ,Group_Name )
select Group_ID ,Group_Name
from Group
where Group_ID =@Group_ID
delete #tempMember
insert into #tempMember
select *
from Member
where Group_ID =@Group_ID
select @User_ID=""
while
begin
select @User_ID=min(User_ID)
from (select User_ID
from #tempMember
where @User_ID>User_ID) a
if @@RowCount=0
break
update #tempTable set #tempTable.User_List =
#tempTable.User_List + "," +User.UserName
from #tempTable,User
where #tempTable.Group_ID=@Group_ID and
User.User_ID=@User_IDendend
select * from #tempTable
end