存储过程美高梅网址
分类:关于美高梅

不过在网上找了一些,发现都有一个特点——就是不能传出总记录数,干脆自己研究吧。终于,算是搞出来了,效率可能不是很好,但是我也觉得不错了。贴代码吧直接:也算是对自己学习mysql的一个记录。   

mysql error1241:Operand should contain 1 column
下边是我写的代码:

复制代码 代码如下:

DELIMITER $$
CREATE DEFINER=root@% PROCEDURE findHosts(IN cluster_id VARCHAR, IN resource_id VARCHAR, IN linuxuser_id VARCHAR, IN service_type VARCHAR
SQL SECURITY INVOKER
BEGIN
SET @cluster_id = cluster_id;
SET @resource_id = resource_id;
SET @linuxuser_id = linuxuser_id;
SET @service_type = service_type;
SET @all_ids = CONCAT('(SELECT hc.host_id id FROM host_component hc INNER JOIN service_', @service_type, ' s ON
hc.', @service_type, '_id = s.id) ');
SET @head = 'SELECT DISTINCT ms.id, ms.host_name, ms.ip_address, ms.region,
ms.machine_room, ms.rack, ms.cpu, ms.memory, ms.disk_usage,
ms.status, ms.mem_used, ms.disk_used, ms.cpu_used
FROM manager_host ms INNER JOIN host_component hc
ON ms.id = hc.host_id AND hc.cluster_id = ? ';
SET @service_condition = CONCAT('AND hc.host_id NOT IN ', @all_ids);
SET @tail = 'ORDER BY ms.host_name';
SET @resource_condition = 'AND hc.resource_id = ? ';
SET @linuxuser_condition = 'AND hc.linux_user_id = ? ';
SET @host_sql = CONCAT(@head, @service_condition);

CREATE PROCEDURE p_pageList
  (
  m_pageNo int ,
  m_perPageCnt int ,
  m_column varchar(1000) ,
  m_table varchar(1000) ,
  m_condition varchar(1000),
  m_orderBy varchar(200) ,
  out m_totalPageCnt int
  )
  BEGIN
  SET @pageCnt = 1; -- 总记录数
  SET @limitStart = (m_pageNo - 1)*m_perPageCnt;
  SET @limitEnd = m_perPageCnt;
  SET @sqlCnt = CONCAT('select count(1) into @pageCnt from ',m_table); -- 这条语句很关键,用来得到总数值
  SET @sql = CONCAT('select ',m_column,' from ',m_table);
  IF m_condition IS NOT NULL AND m_condition <> '' THEN
  SET @sql = CONCAT(@sql,' where ',m_condition);
  SET @sqlCnt = CONCAT(@sqlCnt,' where ',m_condition);
  END IF;
  IF m_orderBy IS NOT NULL AND m_orderBy <> '' THEN
  SET @sql = CONCAT(@sql,' order by ',m_orderBy);
  END IF;
  SET @sql = CONCAT(@sql, ' limit ', @limitStart, ',', @limitEnd);
  PREPARE s_cnt from @sqlCnt;
  EXECUTE s_cnt;
  DEALLOCATE PREPARE s_cnt;
  SET m_totalPageCnt = @pageCnt;
  PREPARE record from @sql;
  EXECUTE record;
  DEALLOCATE PREPARE record;
  END

IF (@resource_id IS NOT NULL, @resource_id <> '')
THEN SET @host_sql = CONCAT(@host_sql, @resource_condition);
END IF;

...

IF (@linuxuser_id IS NOT NULL, @linuxuser_id <> '')
THEN SET @host_sql = CONCAT(@host_sql, @linuxuser_condition);
END IF;

SET @host_sql = CONCAT(@host_sql, @tail);

PREPARE result FROM @host_sql;
EXECUTE result USING @cluster_id, @resource_id, @linuxuser_id;
DEALLOCATE PREPARE result;
END$$

DELIMITER ;

执行调用语句后:
call CALL findHosts('DH90GH9_FHF4GHD3DNP4CG', '', '', 'storm');

报错:
Error Code: 1241
Operand should contain 1 column

当我把存储过程里边的SQL语句拿到外面直接运行的时候,是不会报错的,但是在存储过程里会报错,求各位大神指点这是什么原因

本文由美高梅网址发布于关于美高梅,转载请注明出处:存储过程美高梅网址

上一篇:MYSQL使用简述,控制台操作 下一篇:没有了
猜你喜欢
热门排行
精彩图文