我们的题目是这样的:输入模块编号和用户编号,查询此用户是否有权限查看
我的想法是这样的:先查出模块编号和用户编号的关系图,在存储过程中有两个输入参数MENU_ID,USER_ID,一个输出参数I,如果查得到数据,I=1,查不到,I=0。
下面是我写的用来测试查找数据的查询语句:
SELECT COUNT(*) FROM
(
SELECT E.ID,F.ID FROM TBL_USER E LEFT JOIN
(
SELECT * FROM
(
select * from TBL_USER_ROLE D LEFT JOIN
(
SELECT * FROM(select * from TBL_ROLE_MENU A LEFT JOIN TBL_MENU B
ON A.MENU_ID=B.ID))C
ON D.ROLE_ID=C.ROLE_ID))F
ON E.ID=F.USER_ID WHERE E.ID=1 AND F.ID=1)
结果:COUNT(*)=1,说明查找到一条数据,是有权限查看的
但是我的存储过程不知道哪里写错的,代码如下:
create or replace procedure proc1
(
MENU_ID in char(5),
USER_ID in char(5),
I OUT CHAR(1)
)
as
beginI:=(SELECT COUNT(*) FROM
(
SELECT E.ID,F.ID FROM TBL_USER E LEFT JOIN
(
SELECT * FROM
(
select * from TBL_USER_ROLE D LEFT JOIN
(
SELECT * FROM(select * from TBL_ROLE_MENU A LEFT JOIN TBL_MENU B
ON A.MENU_ID=B.ID))C
ON D.ROLE_ID=C.ROLE_ID))F
ON E.ID=F.USER_ID WHERE E.ID=USER_ID AND F.ID=MENU_ID))
END proc1;proc1是失效的,也就是有个红色的xx,我刚学oracle,求大家指导一下,在线等答案!
我的想法是这样的:先查出模块编号和用户编号的关系图,在存储过程中有两个输入参数MENU_ID,USER_ID,一个输出参数I,如果查得到数据,I=1,查不到,I=0。
下面是我写的用来测试查找数据的查询语句:
SELECT COUNT(*) FROM
(
SELECT E.ID,F.ID FROM TBL_USER E LEFT JOIN
(
SELECT * FROM
(
select * from TBL_USER_ROLE D LEFT JOIN
(
SELECT * FROM(select * from TBL_ROLE_MENU A LEFT JOIN TBL_MENU B
ON A.MENU_ID=B.ID))C
ON D.ROLE_ID=C.ROLE_ID))F
ON E.ID=F.USER_ID WHERE E.ID=1 AND F.ID=1)
结果:COUNT(*)=1,说明查找到一条数据,是有权限查看的
但是我的存储过程不知道哪里写错的,代码如下:
create or replace procedure proc1
(
MENU_ID in char(5),
USER_ID in char(5),
I OUT CHAR(1)
)
as
beginI:=(SELECT COUNT(*) FROM
(
SELECT E.ID,F.ID FROM TBL_USER E LEFT JOIN
(
SELECT * FROM
(
select * from TBL_USER_ROLE D LEFT JOIN
(
SELECT * FROM(select * from TBL_ROLE_MENU A LEFT JOIN TBL_MENU B
ON A.MENU_ID=B.ID))C
ON D.ROLE_ID=C.ROLE_ID))F
ON E.ID=F.USER_ID WHERE E.ID=USER_ID AND F.ID=MENU_ID))
END proc1;proc1是失效的,也就是有个红色的xx,我刚学oracle,求大家指导一下,在线等答案!
create or replace procedure proc1
(
MENU_ID in char(5),
USER_ID in char(5),
I OUT CHAR(1)
)
as
begin
SELECT COUNT(*) into I FROM
(
SELECT E.ID,F.ID FROM TBL_USER E LEFT JOIN
(
SELECT * FROM
(
select * from TBL_USER_ROLE D LEFT JOIN
(
SELECT * FROM(select * from TBL_ROLE_MENU A LEFT JOIN TBL_MENU B
ON A.MENU_ID=B.ID))C
ON D.ROLE_ID=C.ROLE_ID))F
ON E.ID=F.USER_ID WHERE E.ID=USER_ID AND F.ID=MENU_ID)
END proc1;
--簡單的例子
set serveroutput on;
declare
x int;
begin
--x:=(select count(1) from dual);--錯誤的寫法,oracle語法不是這樣地
select count(1) into x from dual;
dbms_output.put_line(x);
end;
/
MENU_ID in char(5),
USER_ID in char(5),
I OUT CHAR(1)
--改為
MENU_ID in char
USER_ID in char
I OUT CHAR
USER_ID in char,
I OUT CHAR--暈其實你用show errors;就可以看出哪裡錯了
我用了一下,提示说SQL 命令未正确结束,能 帮我看看错在哪吗