select 查询数据传入存储过程，用游标循环 ，再插入临时表 join 关联

• select 查询数据传入存储过程，用游标循环 ，再插入临时表 join 关联

• OPENQUERY 跨库查询

• USE [His_Data_From_All_Pay_Ora]
  GO
  /****** Object:  StoredProcedure [dbo].[SP_CARD_MONEY_4WORKER_NEWCard2023]    Script Date: 2023-03-24 11:13:18 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:        
  -- Create date: 
  -- Description:    
  -- =============================================
  ALTER PROCEDURE [dbo].[SP_CARD_M]
      -- Add the parameters for the stored procedure here
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      
      DECLARE @CardNodnumber TABLE (CardNo VARCHAR(20))
  
      INSERT INTO @CardNodnumber (CardNo)
      SELECT a.card_no 
      FROM dbo.tbl_sell a
      join (
      SELECT  ID_NO  as 身份证
      FROM OPENQUERY(ORACLE,'select a.card_no from zy_ca_acc a,zy_wor_info b,zy_wor_info c where a.card_no=b.opcard_no and b.op_no=c.op_no and c.dept_no=87 and is_bt=0 order by employee_name') z, gj_center.dbo.交 y 
      WHERE z.op_no=y.职工编号 and y.退休标志<>'(退休卡)') b on a.ID_NO=b.身份证 and a.CARD_TYPE='0014';
  
      DECLARE @CardNo VARCHAR(20)
  
      --创建临时表存放tsql数据
      CREATE TABLE #temp_table (card_no1  VARCHAR(20), name VARCHAR(20),str1 VARCHAR(20),str2 VARCHAR(20), str3 VARCHAR(20), balance VARCHAR(20))
  
      --游标自动下一个查询
      DECLARE CardNo_cursor CURSOR FOR 
      SELECT CardNo FROM @CardNodnumber
  
  
      OPEN CardNo_cursor
  
      FETCH NEXT FROM CardNo_cursor INTO @CardNo
  
      WHILE @@FETCH_STATUS = 0
      BEGIN
          -- 调用存储过程获取查询结果 将查询结果插入临时表
          INSERT INTO #temp_table ( card_no1 , name , str1 , str2,str3, balance)
          EXEC Work_Platform_4UPTIC.dbo.sp_Query_IC_Card_Trade_Related_Data_From_ALL_PAY_ORACLE 'CARD_ACC',@CardNo
          
          FETCH NEXT FROM CardNo_cursor INTO @CardNo
      END
  
      CLOSE CardNo_cursor
      --释放游标
      DEALLOCATE CardNo_cursor
  
      -- 在临时表中筛选字段
      SELECT CARD_NO1 AS 卡 ,a.NAME AS 姓 , b.ID_NO as 身份证, BALANCE AS 余额  FROM #TEMP_TABLE a
      JOIN ard_sl b ON a.card_no1 = b.card_no
      ORDER BY a.NAME
  
      DROP TABLE #TEMP_TABLE
  END