CREATE OR REPLACE FUNCTION f_get_satff_id(org_type integer,org_id varchar2)
RETURN nvarchar2
as
s_staff nvarchar2(32767);
nCnt INTEGER; --循环次数
TYPE CURSORTYPE IS REF CURSOR;
cCURSOR CURSORTYPE; --游标
staffid VARCHAR2(20);
begin
if org_type=2 then
select count(r.staff_id) into nCnt from SSO_STAFF_ROLE r,hrm_staff s,hrm_group_info g where r.staff_id=s.staff_id and s.group_id=g.group_id and sso_role_id=70 and g.department_id in ('|| org_id ||');
if nCnt > 0 then
open cCURSOR for
select r.staff_id from SSO_STAFF_ROLE r,hrm_staff s,hrm_group_info g where r.staff_id=s.staff_id and s.group_id=g.group_id and sso_role_id=70 and g.department_id in ('|| org_id ||');
loop
FETCH cCURSOR INTO staffid;
EXIT WHEN cCURSOR%NOTFOUND;
s_staff := s_staff ||staffid ||',';
end loop;
else
s_staff:='部门';
end if;
--s_staff:='部门';
elsif org_type=1 then
s_staff:='班组';
elsif org_type=0 then
s_staff:='员工';
end if;
RETURN s_staff;
END;调用函数时报错“无效数字”,我把'|| org_id ||'替换成1,2就没问题,应该是传入的参数问题,org_id参数传入的是1,2等字符串,请问为什么调用函数会出错
RETURN nvarchar2
as
s_staff nvarchar2(32767);
nCnt INTEGER; --循环次数
TYPE CURSORTYPE IS REF CURSOR;
cCURSOR CURSORTYPE; --游标
staffid VARCHAR2(20);
begin
if org_type=2 then
select count(r.staff_id) into nCnt from SSO_STAFF_ROLE r,hrm_staff s,hrm_group_info g where r.staff_id=s.staff_id and s.group_id=g.group_id and sso_role_id=70 and g.department_id in ('|| org_id ||');
if nCnt > 0 then
open cCURSOR for
select r.staff_id from SSO_STAFF_ROLE r,hrm_staff s,hrm_group_info g where r.staff_id=s.staff_id and s.group_id=g.group_id and sso_role_id=70 and g.department_id in ('|| org_id ||');
loop
FETCH cCURSOR INTO staffid;
EXIT WHEN cCURSOR%NOTFOUND;
s_staff := s_staff ||staffid ||',';
end loop;
else
s_staff:='部门';
end if;
--s_staff:='部门';
elsif org_type=1 then
s_staff:='班组';
elsif org_type=0 then
s_staff:='员工';
end if;
RETURN s_staff;
END;调用函数时报错“无效数字”,我把'|| org_id ||'替换成1,2就没问题,应该是传入的参数问题,org_id参数传入的是1,2等字符串,请问为什么调用函数会出错
解决方案 »
- oracle监听器无法启动,提示“错误1067:进程意外终止”是什么意思?怎么解决啊
- 这个视图该怎么建,急!!!
- 怎么查找表名“参数表L10-256” 里面的记录?!?!?!?!?!?
- ~~~~为什么出错?~~~~~
- 看看这几个面试问题你能回答上几个?
- 安装JInitiator 1.1.8.13的问题
- TNSListener为什么同时监听1521,8080,9090等多个端口???????????
- 安装好ORACLE后为什么不能建立数据库呢
- ORA-01688:unable to extend table LOG partition P_2005021200 by 256 in tablespace MYLOGS
- 再问oracle initialization or shotdown in progress??
- constraints
- merge into 错误
v_sql:='select ...'||...;
execute immediate v_sql into nCnt;
...
select count(r.staff_id) into nCnt from SSO_STAFF_ROLE r,hrm_staff s,hrm_group_info g where r.staff_id=s.staff_id and s.group_id=g.group_id and sso_role_id=70 and g.department_id in (org_id );
就可以了后面那个也是
g.department_id in ('|| org_id ||'); 换成
g.department_id =org_id 不行吗
execute immediate v_sql into nCnt;
上面的这样改了没报错了,但是这句怎么改
open cCURSOR for
select r.staff_id from SSO_STAFF_ROLE r,hrm_staff s,hrm_group_info g where r.staff_id=s.staff_id and s.group_id=g.group_id and sso_role_id=70 and g.department_id in ('|| org_id ||');
loop
open cCURSOR for v_sql
loop
RETURN nvarchar2
as
s_staff nvarchar2(32767);
nCnt INTEGER; --循环次数
TYPE CURSORTYPE IS REF CURSOR;
cCURSOR CURSORTYPE; --游标
staffid VARCHAR2(20);
v_sql VARCHAR2(2000);
begin
if org_type=2 then
v_sql := 'select count(r.staff_id)
from SSO_STAFF_ROLE r,hrm_staff s,hrm_group_info g
where r.staff_id=s.staff_id and s.group_id=g.group_id
and sso_role_id=70 and g.department_id in ('|| org_id ||')';
execute immediate v_sql into nCnt;
if nCnt > 0 then
v_sql := 'select r.staff_id
from SSO_STAFF_ROLE r,hrm_staff s,hrm_group_info g
where r.staff_id=s.staff_id and s.group_id=g.group_id
and sso_role_id=70 and g.department_id in ('|| org_id ||')';
open cCURSOR for v_sql;
loop
FETCH cCURSOR INTO staffid;
EXIT WHEN cCURSOR%NOTFOUND;
s_staff := s_staff ||staffid ||',';
end loop;
else
s_staff:='部门';
end if;
--s_staff:='部门';
elsif org_type=1 then
s_staff:='班组';
elsif org_type=0 then
s_staff:='员工';
end if; RETURN s_staff;
END;
select r.staff_id from SSO_STAFF_ROLE r,hrm_staff s,hrm_group_info g where r.staff_id=s.staff_id and s.group_id=g.group_id and sso_role_id=70 and g.department_id =to_number(org_id) ;
loop 干嘛那么麻烦,这样不行吗