一下一存储过程:
create or replace procedure P_SYS_GetButtonListByOBJ(v_aspID VARCHAR2,v_userID VARCHAR2,v_objID INTEGER) is
v_optList VARCHAR(100);
v_optListTemp VARCHAR(100);
v_qxList VARCHAR(1000) ;
v_groupID VARCHAR(20);
v_idd INTEGER ; ---对应的操作编号
v_size INTEGER ; ----剩下操作的长度
v_rowcount integer;
CURSOR qxList_cursor IS
SELECT vcQxList
FROM TB_SYS_QUANXIAN
WHERE nObjID=v_objID AND vcAspID=v_aspID AND userID IN (SELECT vcGroupCode FROM TB_SYS_USER_GROUP WHERE vcUserID = v_userID AND vcAspID=v_aspID ) ;/* 获取用户的可用操作*/
begin
SELECT vcOptList INTO v_optListTemp
FROM TB_SYS_OBJECT
WHERE nObjID=v_objID; /* 获取功能的可用操作*/
execute immediate 'CREATE GLOBAL TEMPORARY TABLE Tab( nOptID number) ON COMMIT delete ROWS';
OPEN qxList_cursor ;
FETCH qxList_cursor into v_qxList;
while qxList_cursor%Found loop
v_optList:= v_optListTemp;
v_idd:= 1 ;
v_size:= LENGTH(v_optList);
WHILE v_size>0 loop
if (Substr(v_optList,1,1)='1'AND Substr(v_qxList,1,1)='1') then
execute immediate'SELECT COUNT(*) FROM Tab WHERE nOptID=idd'INTO v_rowcount;
if (v_rowcount<=0) then
execute immediate'INSERT into Tab(nOptID) VALUES(idd)';
end if;
end if;
v_idd:=v_idd+1;
v_size:=v_size-1;
v_optList:=Substr(v_optList,-1);
v_qxList:=SubStr(v_qxList,-1);
end loop;
fetch qxList_cursor into v_qxList;
end loop;
close qxList_cursor;
execute immediate'SELECT a.nOptID,a.vcOptName,a.vcTitle,a.vcInIMG,a.vcOutIMG
FROM TB_SYS_OPERATOR a, Tab b
WHERE a.nOptID=b.nOptID ORDER BY a.nOptID';
execute immediate'DROP TABLE Tab' ;
end P_SYS_GetButtonListByOBJ;
在测试时出错,好像时临时表操作的问题,我刚接触ORACLE,哪位大侠帮我指出下
create or replace procedure P_SYS_GetButtonListByOBJ(v_aspID VARCHAR2,v_userID VARCHAR2,v_objID INTEGER) is
v_optList VARCHAR(100);
v_optListTemp VARCHAR(100);
v_qxList VARCHAR(1000) ;
v_groupID VARCHAR(20);
v_idd INTEGER ; ---对应的操作编号
v_size INTEGER ; ----剩下操作的长度
v_rowcount integer;
CURSOR qxList_cursor IS
SELECT vcQxList
FROM TB_SYS_QUANXIAN
WHERE nObjID=v_objID AND vcAspID=v_aspID AND userID IN (SELECT vcGroupCode FROM TB_SYS_USER_GROUP WHERE vcUserID = v_userID AND vcAspID=v_aspID ) ;/* 获取用户的可用操作*/
begin
SELECT vcOptList INTO v_optListTemp
FROM TB_SYS_OBJECT
WHERE nObjID=v_objID; /* 获取功能的可用操作*/
execute immediate 'CREATE GLOBAL TEMPORARY TABLE Tab( nOptID number) ON COMMIT delete ROWS';
OPEN qxList_cursor ;
FETCH qxList_cursor into v_qxList;
while qxList_cursor%Found loop
v_optList:= v_optListTemp;
v_idd:= 1 ;
v_size:= LENGTH(v_optList);
WHILE v_size>0 loop
if (Substr(v_optList,1,1)='1'AND Substr(v_qxList,1,1)='1') then
execute immediate'SELECT COUNT(*) FROM Tab WHERE nOptID=idd'INTO v_rowcount;
if (v_rowcount<=0) then
execute immediate'INSERT into Tab(nOptID) VALUES(idd)';
end if;
end if;
v_idd:=v_idd+1;
v_size:=v_size-1;
v_optList:=Substr(v_optList,-1);
v_qxList:=SubStr(v_qxList,-1);
end loop;
fetch qxList_cursor into v_qxList;
end loop;
close qxList_cursor;
execute immediate'SELECT a.nOptID,a.vcOptName,a.vcTitle,a.vcInIMG,a.vcOutIMG
FROM TB_SYS_OPERATOR a, Tab b
WHERE a.nOptID=b.nOptID ORDER BY a.nOptID';
execute immediate'DROP TABLE Tab' ;
end P_SYS_GetButtonListByOBJ;
在测试时出错,好像时临时表操作的问题,我刚接触ORACLE,哪位大侠帮我指出下
解决方案 »
- 求助,pde文件导入失败,Character set marker unknown
- 如何利用程序的方法查询一个数据库中的所有表和这些表的属性?急!!
- QQ信息存储的表设计?
- solaris下的oracle内存分配
- 同样的SQL,在三个环境中跑,两个OK,另外一个会抱错(00972标示过长)
- 使用Case语句如何得到下面的结果
- 请问在access中连接表时,文件种类下拉框中找不到ODBC Databases ()怎莫办?
- 如何实现ORACLE象SQLSERVER一样实现INT的自动加一的功能??
- enterprise manager中怎样连上数据库
- ORCALE SQL查询 表中某字段的某值,最大的前10个,万分感谢!
- 函数索引碰到的麻烦,从来没碰到过,请高手帮忙解答一下。
- 求sql语句:检索得到重复数据
动态执行SQL,应使用绑定变量
或者
execute immediate'INSERT into Tab(nOptID) VALUES('||v_idd||')'
execute immediate'SELECT a.nOptID,a.vcOptName,a.vcTitle,a.vcInIMG,a.vcOutIMG
FROM TB_SYS_OPERATOR a, Tab b
WHERE a.nOptID=b.nOptID ORDER BY a.nOptID';
在存储过程中返回记录集我不知道怎么写?