有四个表
userlist表
id 主键编号自增
uname 用户昵称
upass 用户密码
state用户状态
flag 用户标识board表
bid 主键自增
bname 板块名字topic表
tid 主键自增
title 标题
context 主题内容
ptime 发帖时间
id 外键
bid 外键reply表
rid 主键自增
title 标题
context 内容
ptime 回帖时间
id 外键
tid 外键
创建一个存储过程实现登录过程
创建一个存储过程查看最活跃的三个用户,
创建一个存储过程查看某个板块的总帖量存储mysql
userlist表
id 主键编号自增
uname 用户昵称
upass 用户密码
state用户状态
flag 用户标识board表
bid 主键自增
bname 板块名字topic表
tid 主键自增
title 标题
context 主题内容
ptime 发帖时间
id 外键
bid 外键reply表
rid 主键自增
title 标题
context 内容
ptime 回帖时间
id 外键
tid 外键
创建一个存储过程实现登录过程
创建一个存储过程查看最活跃的三个用户,
创建一个存储过程查看某个板块的总帖量存储mysql
验证的
create procedure checkLogin
(naeme varchar(10),pwd varchar(10),flags int)
select id from userlist where uname=name and upass=pwd and flag=flags and state=0;查看最活跃的3个用户
create procedure hotuser
select uname from userlist where id in(select top 3 id from topic group by id order by count(id)desc);查看某个板块的总帖量
create procedure topicCountOfBoard
(bids int)
declare num1 int
declare num2 int
select num1=count(*)from topic where bid=bids
select num2=count(*)from reply where tid in (select tid from topic where bid=bids)
return num1+num2;
select uname from userlist where id in(select id from topic group by id order by count(id) desc limit 3);create procedure topicCountOfBoard
(bids int)
begin
declare num1 int
declare num2 int
select count(*) from into num1 topic where bid=bids
select count(*) from into num2 reply where tid in (select tid from topic where bid=bids)
end;
其实我们只学了sqlserver
mysql和oracle还没学呢
这些都是我自己摸索的
那用oracle怎么写存储过程呢
问好多人都没人回答
很郁闷
-> select uname from userlist where id in(select id from topic group by id order by count(id) d
esc limit 3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'select uname from userlist where id in(sele
ct id from topic group by id order by' at line 2
mysql> create procedure topicCountOfBoard
-> (bids int)
-> begin
-> declare num1 int
-> declare num2 int
-> select count(*) from into num1 topic where bid=bids
-> select count(*) from into num2 reply where tid in (select tid from topic where bid=bids)
-> end;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'declare num2 int
select count(*) from into num1 topic where bid=bids
select co' at line 5
delimiter $$
create procedure hotuser()
begin
select uname from userlist where id in(select id from topic group by id order by count(id) desc limit 3);
end $$
delimiter ;
mysql> create procedure hotuser()
-> begin
-> select uname from userlist where id in(select id from topic group by id order by count(id) d
esc limit 3);
-> end $$
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
CREATE PROCEDURE hotuser()
BEGIN
SELECT a.uname FROM userlist a
INNER JOIN
(SELECT id FROM topic GROUP BY id ORDER BY COUNT(id) DESC LIMIT 3) b
ON a.id=b.id;
END $$
DELIMITER ;
mysql> create procedure hotuser()
-> begin
-> select a.uname from userlist a
-> inner join
-> (select id from topic group by id order by count(id) desc limit 3)b
-> on a.id=b.id;
-> end $$
ERROR 1046 (3D000): No database selected
还有问题啊
SELECT 你的数据库名,再运行上述代码
先谢谢版主了
mysql> create procedure TopicCountOfBoard(bids int)
-> begin
-> declare num1 int
-> declare num2 int
-> select count(*) into num1 from topic where bid=bids
-> select count(*) into num2 from reply where tid in(select tid from topic where bid=bids
建议自己看看MYSQL HELP
mysql> create procedure TopicCountOfBoard
-> (bids int)
-> begin
-> declare num1 int
-> declare num2 int
-> select count(*) from into num1 topic where bid=bids
-> select count(*)from into num2 reply where tid in(select tid from topic where bid=bids)
-> end;
delimiter $$
-> (bids int)
-> begin
-> declare num1 int
-> declare num2 int
-> select count(*) from into num1 topic where bid=bids
-> select count(*) from into num2 reply where tid in(select tid from topic where bid=bids);
-> end $$
还不对
mysql> create procedure TopicCountOfBoard
-> (bids int)
-> begin
-> declare num1 int
-> declare num2 int
-> select count(*) from into num1 topic where bid=bids
-> select count(*) from into num2 reply where tid in(select tid from topic where bid=bids);
-> end $$
delimiter ;
这个我写了
mysql> delimiter $$
mysql> create procedure TopicCountOfBoard
-> (bids int)
-> begin
-> declare num1 int
-> declare num2 int
-> select count(*)from into num1 topic where bid=bids
-> select count(*)from into num2 reply where tid in(select tid from topic where bid=bids);
-> end $$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'declare num2 int
select count(*)from into num1 topic where bid=bids
select count' at line 5
CREATE PROCEDURE topicCountOfBoard
(bids INT)
BEGIN
DECLARE num1 INT;
DECLARE num2 INT;
SELECT COUNT(*) INTO num1 FROM topic WHERE bid=bids;
SELECT COUNT(*) INTO num2 FROM reply WHERE tid IN (SELECT tid FROM topic WHERE bid=bids);
END;$$
DELIMITER ;
-> select uname from userlist where id in(select id from topic group by id order by count(id) d
esc limit 3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'select uname from userlist where id in(sele
ct id from topic group by id order by' at line 2
mysql> create procedure topicCountOfBoard
-> (bids int)
-> begin
-> declare num1 int
-> declare num2 int
-> select count(*) from into num1 topic where bid=bids
-> select count(*) from into num2 reply where tid in (select tid from topic where bid=bids)
-> end;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'declare num2 int
select count(*) from into num1 topic where bid=bids
select co' at line 5