CREATE OR REPLACE PROCEDURE PROC_USER_DEP (
in_user_id in VARCHAR2,
in_user_pwd in VARCHAR2,
out_node_id out VARCHAR2,
out_tree_pk out VARCHAR2,
out_id out VARCHAR2)
AS
BEGIN
select t1.node_id,
t1.TREE_PK
t2.id into out_id
from HBGH_PUB_TREE t1 ,
(
select id from Hbgh_User_Info where User_Uid='0311admin' and user_pwd='admin'
) t2
where t1.node_id =
(
select substr(PURVIEWID,1,instr(PURVIEWID,';')-1) powerid from Hbgh_User_Info
where User_Uid='0311admin' and user_pwd='admin'
) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END PROC_USER_DEP;
in_user_id in VARCHAR2,
in_user_pwd in VARCHAR2,
out_node_id out VARCHAR2,
out_tree_pk out VARCHAR2,
out_id out VARCHAR2)
AS
BEGIN
select t1.node_id,
t1.TREE_PK
t2.id into out_id
from HBGH_PUB_TREE t1 ,
(
select id from Hbgh_User_Info where User_Uid='0311admin' and user_pwd='admin'
) t2
where t1.node_id =
(
select substr(PURVIEWID,1,instr(PURVIEWID,';')-1) powerid from Hbgh_User_Info
where User_Uid='0311admin' and user_pwd='admin'
) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END PROC_USER_DEP;
你增加into ……就好了
in_user_id in VARCHAR2,
in_user_pwd in VARCHAR2,
out_node_id out VARCHAR2,
out_tree_pk out VARCHAR2,
out_id out VARCHAR2)
AS
BEGIN
select t1.node_id, t1.TREE_PK , t2.id into out_node_id ,out_tree_pk ,out_id
from HBGH_PUB_TREE t1 ,
(
select id from Hbgh_User_Info where User_Uid='0311admin' and user_pwd='admin'
) t2
where t1.node_id =
(
select substr(PURVIEWID,1,instr(PURVIEWID,';')-1) powerid from Hbgh_User_Info
where User_Uid='0311admin' and user_pwd='admin'
) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END PROC_USER_DEP;注意: 要确保查询的结果集只有一条记录
in_user_id in VARCHAR2,
in_user_pwd in VARCHAR2,
out_node_id out VARCHAR2,
out_tree_pk out VARCHAR2,
out_id out VARCHAR2)
AS
BEGIN
select
//说明
//in_user_id in VARCHAR2 输入参数
//in_user_pwd in VARCHAR2 输入参数
//out_node_id 输出参数
//out_tree_pk 输出参数
//out_id 输出参数
//从DB中获取t1.node_id,t1.TREE_PK,t2.id然后分别附值,
///////////////////////////////////////////////
//问题:一条数据可以,第二条数据开始就报错///////
////////////////////////////////////////////////
t1.node_id into out_node_id,
t1.TREE_PK into out_tree_pk,
t2.id into out_id
from HBGH_PUB_TREE t1 ,
(
select id from Hbgh_User_Info where User_Uid='0311admin' and user_pwd='admin'
) t2
where t1.node_id =
(
select substr(PURVIEWID,1,instr(PURVIEWID,';')-1) powerid from Hbgh_User_Info
where User_Uid='0311admin' and user_pwd='admin'
) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END PROC_USER_DEP;
/
你想赋两条肯定会报错.
建议用SELECT COUNT(*) INTO...做条数判断,再进行赋值处理.
如果是多条记录就只能赋给数组了
本处肯定肯定只有单条记录,但是我想要得到几个字段的值,麻烦哪为高手给写出来,比如现在我只有一条记录,但是我需要得到这条记录所包含的字段一,字段二....等等,我上面写的Procedure也是想达到这个目的,请哪为高手直接给我写出来.
谢谢!
in_user_id in VARCHAR2,
in_user_pwd in VARCHAR2,
out_node_id out VARCHAR2,
out_tree_pk out VARCHAR2,
out_id out VARCHAR2)
AS
BEGIN
select t1.node_id,
t1.TREE_PK
t2.id into out_id, out_tree_pk, out_id
from HBGH_PUB_TREE t1 ,
(
select id from Hbgh_User_Info where User_Uid='0311admin' and user_pwd='admin'
) t2
where t1.node_id =
(
select substr(PURVIEWID,1,instr(PURVIEWID,';')-1) powerid from Hbgh_User_Info
where User_Uid='0311admin' and user_pwd='admin'
) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END PROC_USER_DEP;