电影点评系统users( id username password , email is_admin ),reviews(id user_id movie_id rating,comment create_time ),movies(id title director actor summary poster category_id ),comments(id review_id user_id content create_time ),categories(id,name) 五个表的存储过程,存储函数的创建代码
时间: 2023-06-20 08:09:43 浏览: 48
存储过程代码:
```sql
DELIMITER //
CREATE PROCEDURE get_movie_reviews (IN movie_id INT)
BEGIN
SELECT reviews.id, reviews.rating, reviews.comment, reviews.create_time, users.username
FROM reviews
JOIN users ON reviews.user_id = users.id
WHERE reviews.movie_id = movie_id;
END//
CREATE PROCEDURE add_movie_review (IN user_id INT, IN movie_id INT, IN rating FLOAT, IN comment TEXT)
BEGIN
INSERT INTO reviews (user_id, movie_id, rating, comment, create_time)
VALUES (user_id, movie_id, rating, comment, NOW());
END//
CREATE PROCEDURE get_movie_comments (IN movie_id INT)
BEGIN
SELECT comments.id, comments.content, comments.create_time, users.username
FROM comments
JOIN reviews ON comments.review_id = reviews.id
JOIN users ON comments.user_id = users.id
WHERE reviews.movie_id = movie_id;
END//
CREATE PROCEDURE add_movie_comment (IN user_id INT, IN review_id INT, IN content TEXT)
BEGIN
INSERT INTO comments (review_id, user_id, content, create_time)
VALUES (review_id, user_id, content, NOW());
END//
DELIMITER ;
```
存储函数代码:
```sql
DELIMITER //
CREATE FUNCTION calculate_movie_rating (movie_id INT) RETURNS FLOAT
BEGIN
DECLARE rating FLOAT;
SELECT AVG(rating) INTO rating FROM reviews WHERE movie_id = movie_id;
RETURN rating;
END//
DELIMITER ;
```
相关推荐
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)