电影点评系统users,reviews,movies,comments,categories 五个表的存储过程,存储函数的创建代码
时间: 2023-06-25 19:02:28 浏览: 49
以下是电影点评系统五个表的存储过程和存储函数的创建代码:
1. users表的存储过程和存储函数
存储过程:
```
CREATE PROCEDURE getUserById (IN userId INT)
BEGIN
SELECT * FROM users WHERE id = userId;
END
```
存储函数:
```
CREATE FUNCTION getUserByName (IN userName VARCHAR(50)) RETURNS INT
BEGIN
DECLARE userId INT;
SELECT id INTO userId FROM users WHERE name = userName;
RETURN userId;
END
```
2. reviews表的存储过程和存储函数
存储过程:
```
CREATE PROCEDURE getReviewById (IN reviewId INT)
BEGIN
SELECT * FROM reviews WHERE id = reviewId;
END
```
存储函数:
```
CREATE FUNCTION getReviewByMovieId (IN movieId INT) RETURNS INT
BEGIN
DECLARE reviewId INT;
SELECT id INTO reviewId FROM reviews WHERE movie_id = movieId;
RETURN reviewId;
END
```
3. movies表的存储过程和存储函数
存储过程:
```
CREATE PROCEDURE getMovieById (IN movieId INT)
BEGIN
SELECT * FROM movies WHERE id = movieId;
END
```
存储函数:
```
CREATE FUNCTION getMovieByCategory (IN categoryName VARCHAR(50)) RETURNS INT
BEGIN
DECLARE movieId INT;
SELECT movie_id INTO movieId FROM categories WHERE name = categoryName;
RETURN movieId;
END
```
4. comments表的存储过程和存储函数
存储过程:
```
CREATE PROCEDURE getCommentById (IN commentId INT)
BEGIN
SELECT * FROM comments WHERE id = commentId;
END
```
存储函数:
```
CREATE FUNCTION getCommentByReviewId (IN reviewId INT) RETURNS INT
BEGIN
DECLARE commentId INT;
SELECT id INTO commentId FROM comments WHERE review_id = reviewId;
RETURN commentId;
END
```
5. categories表的存储过程和存储函数
存储过程:
```
CREATE PROCEDURE getCategoryById (IN categoryId INT)
BEGIN
SELECT * FROM categories WHERE id = categoryId;
END
```
存储函数:
```
CREATE FUNCTION getCategoryByMovieId (IN movieId INT) RETURNS VARCHAR(50)
BEGIN
DECLARE categoryName VARCHAR(50);
SELECT name INTO categoryName FROM categories WHERE movie_id = movieId;
RETURN categoryName;
END
```