CREATE OR REPLACE function "SUPER"."SP_GETTABLE"(tablename in varchar,userid IN NUMBER)
return userstype.ref_cursor
as
sqlstr VARCHAR2 (500);
str varchar2(200);
strresult varchar2(500);
users_cursor userstype.ref_cursor;
tablenames_cursor userstype.ref_cursor;
r rolespermissionitems.items%type;
BEGIN
--得到角色能够查看的项目
sqlstr :='select items from rolespermissionitems where tablename=:1 and roleid=:2';
OPEN users_cursor FOR sqlstr USING tablename,userid;
loop
fetch users_cursor into r;
exit when users_cursor%notfound;
str:=str||','||r;
END LOOP;
close users_cursor;
str:=trim(',' from str);
--结束角色能够查看的项目
dbms_output.put_line(str);
strresult:='select * from '||tablename;
--dbms_output.put_line(strresult);
OPEN tablenames_cursor FOR strresult;
return tablenames_cursor;
END SP_GETTABLE;