CREATE OR REPLACE PROCEDURE pr_food_comment
(
i_shopId in tb_shop.shopId%TYPE,
o_total_count out NUMBER(8),
o_total_num out NUMBER(8),
o_taste_count out NUMBER(8),
o_taste_num out NUMBER(8),
o_envi_count out NUMBER(8),
o_envi_num out NUMBER(8),
o_service_count out NUMBER(8),
o_service_num out NUMBER(8),
o_price_count out NUMBER(9,2),
o_price_num out NUMBER(8)
)
IS
BEGIN
-- 总体评分的总分
SELECT SUM(commentTotal) INTO o_total_count FROM tb_food_comment WHERE shopId=i_shopId;
-- 口味评分的总分
SELECT SUM(commentTaste) INTO o_taste_count FROM tb_food_comment WHERE shopId=i_shopId;
-- 环境评分的总分
SELECT SUM(commentEnvi) INTO o_envi_count FROM tb_food_comment WHERE shopId=i_shopId;
-- 服务评分的总分
SELECT SUM(commentService) INTO o_service_count FROM tb_food_comment WHERE shopId=i_shopId;
-- 总的价格
SELECT SUM(commentPrice) INTO o_price_count FROM tb_food_comment WHERE shopId=i_shopId;
-- 总体评分的数量
SELECT COUNT(commentTotal) INTO o_total_num FROM tb_food_comment WHERE commentTotal>=0 AND commentTotal IS NOT NULL AND shopId=i_shopId;
-- 口味评分的数量
SELECT COUNT(commentTaste) INTO o_taste_num FROM tb_food_comment WHERE commentTaste>=0 AND commentTaste IS NOT NULL AND shopId=i_shopId;
-- 环境评分的数量
SELECT COUNT(commentEnvi) INTO o_envi_num FROM tb_food_comment WHERE commentEnvi>=0 AND commentEnvi IS NOT NULL AND shopId=i_shopId;
-- 服务评分的数量
SELECT COUNT(commentService) INTO o_service_num FROM tb_food_comment WHERE commentService>=0 AND commentService IS NOT NULL AND shopId=i_shopId;
-- 总的价格次数
SELECT COUNT(commentPrice) INTO o_price_num FROM tb_food_comment WHERE commentPrice>=0 AND commentPrice IS NOT NULL AND shopId=i_shopId;END;
/
这里执行的时候总是有问题,总说编译过程出错
定义out的参数的时候出错了???
麻烦路过帮忙解决下,谢谢
CREATE OR REPLACE PROCEDURE pr_food_comment
(
i_shopId in tb_shop.shopId%TYPE, o_total_count out NUMBER,
o_total_num out NUMBER,
o_taste_count out NUMBER,
o_taste_num out NUMBER,
o_envi_count out NUMBER,
o_envi_num out NUMBER,
o_service_count out NUMBER,
o_service_num out NUMBER,
o_price_count out NUMBER,
o_price_num out NUMBER
)
ISBEGIN
-- 总体评分的总分
SELECT SUM(commentTotal) INTO o_total_count FROM tb_food_comment WHERE shopId=i_shopId;
-- 口味评分的总分
SELECT SUM(commentTaste) INTO o_taste_count FROM tb_food_comment WHERE shopId=i_shopId;
-- 环境评分的总分
SELECT SUM(commentEnvi) INTO o_envi_count FROM tb_food_comment WHERE shopId=i_shopId;
-- 服务评分的总分
SELECT SUM(commentService) INTO o_service_count FROM tb_food_comment WHERE shopId=i_shopId;
-- 总的价格
SELECT SUM(commentPrice) INTO o_price_count FROM tb_food_comment WHERE shopId=i_shopId; -- 总体评分的数量
SELECT COUNT(commentTotal) INTO o_total_num FROM tb_food_comment WHERE commentTotal>=0 AND commentTotal IS NOT NULL AND shopId=i_shopId;
-- 口味评分的数量
SELECT COUNT(commentTaste) INTO o_taste_num FROM tb_food_comment WHERE commentTaste>=0 AND commentTaste IS NOT NULL AND shopId=i_shopId;
-- 环境评分的数量
SELECT COUNT(commentEnvi) INTO o_envi_num FROM tb_food_comment WHERE commentEnvi>=0 AND commentEnvi IS NOT NULL AND shopId=i_shopId;
-- 服务评分的数量
SELECT COUNT(commentService) INTO o_service_num FROM tb_food_comment WHERE commentService>=0 AND commentService IS NOT NULL AND shopId=i_shopId;
-- 总的价格次数
SELECT COUNT(commentPrice) INTO o_price_num FROM tb_food_comment WHERE commentPrice>=0 AND commentPrice IS NOT NULL AND shopId=i_shopId;END;
记住在过程函数里面的传参的类型不要申明长度,也可以像你
tb_shop.shopId%TYPE 这样申明 移植性高
o_total_count out NUMBER,
o_total_num out NUMBER,
o_taste_count out NUMBER,
o_taste_num out NUMBER,
o_envi_count out NUMBER,
o_envi_num out NUMBER,
o_service_count out NUMBER,
o_service_num out NUMBER,
o_price_count out NUMBER,
o_price_num out NUMBER
SQL> CREATE OR REPLACE PROCEDURE pr_food_comment
2 (
3 i_shopId in tb_shop.shopId%TYPE,
4 o_total_count out NUMBER(8),
5 o_total_num out NUMBER(8),
6 o_taste_count out NUMBER(8),
7 o_taste_num out NUMBER(8),
8 o_envi_count out NUMBER(8),
9 o_envi_num out NUMBER(8),
10 o_service_count out NUMBER(8),
11 o_service_num out NUMBER(8),
12 o_price_count out NUMBER(9,2),
13 o_price_num out NUMBER(8)
14 )
15 IS
16
17 BEGIN
18 -- 总体评分的总分
19 SELECT SUM(commentTotal) INTO o_total_count FROM tb_food_comment WHERE shopId=i_shopId;
20 -- 口味评分的总分
21 SELECT SUM(commentTaste) INTO o_taste_count FROM tb_food_comment WHERE shopId=i_shopId;
22 -- 环境评分的总分
23 SELECT SUM(commentEnvi) INTO o_envi_count FROM tb_food_comment WHERE shopId=i_shopId;
24 -- 服务评分的总分
25 SELECT SUM(commentService) INTO o_service_count FROM tb_food_comment WHERE shopId=i_shopId;
26 -- 总的价格
27 SELECT SUM(commentPrice) INTO o_price_count FROM tb_food_comment WHERE shopId=i_shopId;
28
29 -- 总体评分的数量
30 SELECT COUNT(commentTotal) INTO o_total_num FROM tb_food_comment WHERE commentTotal>=0 AND commentTotal IS NOT NULL AND shopId=i_shopId;
31 -- 口味评分的数量
32 SELECT COUNT(commentTaste) INTO o_taste_num FROM tb_food_comment WHERE commentTaste>=0 AND commentTaste IS NOT NULL AND shopId=i_shopId;
33 -- 环境评分的数量
34 SELECT COUNT(commentEnvi) INTO o_envi_num FROM tb_food_comment WHERE commentEnvi>=0 AND commentEnvi IS NOT NULL AND shopId=i_shopId;
35 -- 服务评分的数量
36 SELECT COUNT(commentService) INTO o_service_num FROM tb_food_comment WHERE commentService>=0 AND commentService IS NOT NULL AND shopId=i_shopId;
37 -- 总的价格次数
38 SELECT COUNT(commentPrice) INTO o_price_num FROM tb_food_comment WHERE commentPrice>=0 AND commentPrice IS NOT NULL AND shopId=i_shopId;
39
40 END;
41 /
Warning: Procedure created with compilation errors
SQL> show errors
Errors for PROCEDURE SCOTT.PR_FOOD_COMMENT:
LINE/COL ERROR
-------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
4/29 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
5/29 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
6/29 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
7/29 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
8/29 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
9/29 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
10/29 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
11/29 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
12/29 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
13/29 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
SQL>