MySQL存储过程搜索另一个表

I am totally new to Stored Procedures, but know that this could be more efficient than trying to write PHP+MySQL code each time I need to do something like this.

我对存储过程完全不熟悉,但是知道这可能比每次我需要做这样的事情时编写PHP + MySQL代码更有效。

I have two tables. CapitalAssets, Systems I want find all CapitalAssets.ServerName that are not null

我有两张桌子。 CapitalAssets,我想要的系统找到所有非空的CapitalAssets.ServerName

I have to link the two tables together, the Systems table has IP addresses, hostname.

我必须将两个表链接在一起,Systems表有IP地址,主机名。

I want to (row-by-row) grab CapitalAssets.ServerName and search Systems.hostname, IF it is found I want to link/print

我想(逐行)抓取CapitalAssets.ServerName并搜索Systems.hostname,如果发现我要链接/打印

CapitalAssests: Systems.id, Systems.hostname, Systems.IP, CapitalAssets.id, CapitalAssets.ServerName

CapitalAssests:Systems.id,Systems.hostname,Systems.IP,CapitalAssets.id,CapitalAssets.ServerName

Here is my start to my stored procedure, It is wrong. I do not now how to pass the Systems.hostname to do the search (where the ? is)

这是我的存储过程的开始,这是错误的。我现在不知道如何传递Systems.hostname来进行搜索(其中?)

begin
      declare GSATcur cursor for
            'select id,NEName,ManagementAddress FROM GSAT WHERE NEName like ?';

      declare CapitalCurr CURSOR FOR
            'SELECT id,SystemName FROM CapitalAssets WHERE SystemName != ""';

      DECLARE start INT DEFAULT 0;
      DECLARE sysname_not_found BOOL DEFAULT FALSE;

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET sysname_not_found = TRUE;

      OPEN GSATcur;
      OPEN CapitalCur;

      loop1:
            WHILE start < 5 do
                  FETCH SystemName INTO NEName;
                  IF sysname_not_found THEN
                        LEAVE loop1;
                  END IF;

            END WHILE;

      CLOSE CapitalCur;
      CLOSE GSATcur;
END;

The two tables are in the same dB.

这两个表格的dB相同。

1 个解决方案

#1

" grab CapitalAssets.ServerName and search Systems.hostname, IF it is found I want to link/print "

“抓住CapitalAssets.ServerName并搜索Systems.hostname,如果发现我要链接/打印”

If this is the ultimate goal. Try this

如果这是最终目标。试试这个

SELECT * FROM Systems
WHERE hostname IN ( SELECT DISTINCT(ServerName) 
   FROM CapitalAssets WHERE ServerName IS NOT NULL );

UPDATE CapitalAssets 
INNER JOIN Systems
    ON Systems.hostname = CapitalAssests.ServerName
SET CapitalAssets.ipAddress = Systems.ipAddress;

UPDATE CapitalAssets 
SET ipAddress = ( SELECT ipAddress 
    FROM Systems 
    WHERE  Systems.hostname = CapitalAssests.ServerName );

#1