sql - 如何与其他过程的调用进行外部连接

Question

我有一个存储过程:

procedure qr_get_dep_boss(...,...)

接受两个参数(year，main_code)并返回一个记录 boss_num 和他的名字。

我想创建另一个过程并在之前的过程调用上进行循环，用另一个查询的结果进行外连接

SELECT 
  year,
  main_code,
  name,
  CASE 
    WHEN father_code in(-1)  THEN NULL
    ELSE father_code 
  END AS father_code,main_code || '_' || year AS main_id, 
  (SELECT COUNT(*) 
   FROM sm_r_build 
   WHERE father_code = sc.main_code 
     AND year = (SELECT MAX(year)
                 FROM st_quit_info)) childcount,
   main_code||'_'|| father_code AS serial 
FROM sm_r_build sc 
WHERE year=(SELECT MAX(year)FROM st_quit_info)

所以我想要结果:

year,main_code,name,father_code,main_id,childcount,serial,boss_num,boss_name

---

我试试这个:

create procedure new_get_alldepwithboss()
returning int as year , int as main_code ,nvarchar(100) as name,int as father_code,nvarchar(255) as main_id,int as childcount,int as ll_boss_num,nvarchar(100) as ll_boss_name 

define ll_year int;
define ll_main_code int;
define ll_name nvarchar(100);
define ll_father_code int;
define ll_main_id nvarchar(255) ;
define ll_childcount int;
define ll_boss_num int;
define ll_boss_name nvarchar(100);

foreach 
SELECT year,main_code,name,CASE WHEN father_code in(-1)  THEN NULL ELSE father_code END AS father_code,main_code || '_' || year AS main_id, (SELECT COUNT(*) 
FROM sm_r_build 
WHERE father_code=sc.main_code AND year= (SELECT MAX(year)
FROM st_quit_info)) childcount ,a.emp_num,a.emp_name
INTO ll_year ,ll_main_code,ll_name  ,ll_father_code ,ll_main_id ,ll_childcount ,ll_boss_num,ll_boss_name
FROM sm_r_build  sc , TABLE(FUNCTION qr_get_dep_boss(ll_main_code, ll_year))AS a(emp_num,emp_name)  
WHERE year=(SELECT MAX(year)FROM st_quit_info)


return ll_year , ll_main_code, ll_name,ll_father_code,ll_main_id, ll_childcount ,  ll_boss_num,ll_boss_name with resume;

end foreach

end procedure 

但徒劳无功!

Answer 1

如果是 SQL 服务器，我建议使用函数而不是存储过程。函数头应该是这样的:

CREATE FUNCTION qr_get_dep_boss( @year DATE, @main_code INT)
RETURN @t TABLE ( boss_num INT, boss_name VARCHAR(50))
AS
... ( here boss_num and boss_name  should be set )

在您的主查询中，您可以通过以下方式使用此功能:

SELECT year, main_code, ... , t.bos_num , t.boss_name 
FROM ...
CROSS APPLY
qr_get_dep_boss(year, main_code) t

但我不确定在 Informix 中是否可行，如果不行，您总是可以定义两个返回单个值的函数。 GL!