我是菜鸟都不算的新手,求帮忙了啊,
找了一天也不知道什么地方错了。create or replace package FIND_USER istype cur_user is REF CURSOR;procedure p_finduser (u_name in varchar2,page_no in number,page_user_num in number,allusers out cur_user);end FIND_USER;
create or replace package body FIND_USER isprocedure p_finduser (u_name in varchar2,page_no in number,page_user_num in number,allusers out cur_user)
as
manager varchar2(20);
begin
SELECT ismanager into manager FROM t_users WHERE username=u_name;
IF manager='普通员工权限' THEN
open allusers for (SELECT db.user_id,db.username,db.password,db.sex,db.age,db.phone,db.address,db.ismanager
from (SELECT rownum as rn,t.* FROM t_users t WHERE t.username=u_name )db
where rn between page_user_num*(page_no-1)+1 and page_user_num*page_no);
ELSE
open allusers for(SELECT db.user_id,db.username,db.password,db.sex,db.age,db.phone,db.address,db.ismanager
from (SELECT rownum as rn,t.* FROM t_users t)db
where rn between page_user_num*(page_no-1)+1 and page_user_num*page_no);
END IF;
end p_finduser;
end FIND_USER ;
找了一天也不知道什么地方错了。create or replace package FIND_USER istype cur_user is REF CURSOR;procedure p_finduser (u_name in varchar2,page_no in number,page_user_num in number,allusers out cur_user);end FIND_USER;
create or replace package body FIND_USER isprocedure p_finduser (u_name in varchar2,page_no in number,page_user_num in number,allusers out cur_user)
as
manager varchar2(20);
begin
SELECT ismanager into manager FROM t_users WHERE username=u_name;
IF manager='普通员工权限' THEN
open allusers for (SELECT db.user_id,db.username,db.password,db.sex,db.age,db.phone,db.address,db.ismanager
from (SELECT rownum as rn,t.* FROM t_users t WHERE t.username=u_name )db
where rn between page_user_num*(page_no-1)+1 and page_user_num*page_no);
ELSE
open allusers for(SELECT db.user_id,db.username,db.password,db.sex,db.age,db.phone,db.address,db.ismanager
from (SELECT rownum as rn,t.* FROM t_users t)db
where rn between page_user_num*(page_no-1)+1 and page_user_num*page_no);
END IF;
end p_finduser;
end FIND_USER ;
解决方案 »
- 这句话怎么翻译
- 提示错误"ORA-01502: 索引''或这类索引的分区处于不可用状态"
- 求数据表中字段的比较,请高手帮帮忙(急,在线等。。。)
- .sql 脚本有没有类似C里面的文件包含命令?如#include
- 急!请问oracle9.01触发器为什么导出又重新导入状态变为invalid?
- 注册表中的字符集和props$表中的字符集是什么关系啊?
- >>求oracle 9i的入门教材!!
- ★★★急急急,各位大虾,怎么判断一个字符串是不是日期,有函数吗???
- 各位大虾,ORACLE中上一月的函数是什么,怎么自动用函数表示当前日期的上一月?深表感谢!
- 在oracle中有没有像在sql server一样用命令行创建数据库的命令?
- 对象库(object libraries)不是属于某个form的吗?它和对象组(object groups)有什么区别
- 标题难写(oracle执行一次事务前后查询出的数据变化)
调到现在,我只要一点调试到了
open allusers for (SELECT db.user_id,db.username,db.password,db.sex,db.age。。
这一行,PL/SQL就变成未响应了。
我崩溃了。
哪位大神帮忙给我找找啊
需要的话,我可以给表贴上
from (SELECT rownum as rn,t.* FROM t_users t WHERE t.username=u_name )db
where rn between page_user_num*(page_no-1)+1 and page_user_num*page_no
-----------------------------------------------------------------------------------
page_user_num,page_no,u_name 分别给参数赋值,直接执行这个SQL,看看运行效果
所以这个存储过程至今不能使用。
那还问什么,直接去里面找语法错误,没有提示就换个工具比如pl-sql dev,不用sql-plus
我对oracle存储过程就不懂,这次用到oracle只是打个酱油 ,平时都是用mysql。
create or replace package body FIND_USER is procedure p_finduser(u_name in varchar2,
page_no in number,
page_user_num in number,
allusers out cur_user) as
manager varchar2(20);
begin
SELECT ismanager into manager FROM t_users WHERE username = u_name;
IF manager = '普通员工权限' THEN
open allusers for
SELECT db.user_id,
db.username,
db.password,
db.sex,
db.age,
db.phone,
db.address,
db.ismanager
from (SELECT rownum as rn, t.*
FROM t_users t
WHERE t.username = u_name) db
where rn between page_user_num * (page_no - 1) + 1 and
page_user_num * page_no;
ELSE
open allusers for
SELECT db.user_id,
db.username,
db.password,
db.sex,
db.age,
db.phone,
db.address,
db.ismanager
from (SELECT rownum as rn, t.* FROM t_users t) db
where rn between page_user_num * (page_no - 1) + 1 and
page_user_num * page_no;
END IF;
end p_finduser;
end FIND_USER;
虽然我感觉我的代码和8楼的代码几乎一样,但是我的就是有错,8楼的却是正确的。
小弟在此受教了,以后代码也要书写的清晰一点,格式分明,看着也舒服。
可以结贴了,如果8楼的大哥看再看到我的回复,请告诉我一下,是不是我的代码书写的格式问题?
还是那一对括号的问题?