IF @sflowlog = '' or @sflowlog is null or @sflowlog = '0' BEGIN
/*********************************************************************/
--이전공정 로그 만들기
SET @ipreflowlog = ''
--이전 공정 로그를 만들기 위한 커서 생성
DECLARE CUR_1 CURSOR FOR
(select flow_no from sy_code..tblplflow where pl_no = @splno and flow_no < @sflowno and degree = (select degree from sy_code..tblmodelkind where modelkind_no = @smodelkindno))
OPEN CUR_1
FETCH NEXT FROM CUR_1 INTO @iimsiflow --조회한 flow_no 를 @iimsiflow에 담는다.
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @ipreflowlog = @ipreflowlog + @iimsiflow
FETCH NEXT FROM CUR_1 INTO @iimsiflow
END
CLOSE CUR_1
DEALLOCATE CUR_1
if @ipreflowlog = '' set @ipreflowlog = '0'
/*********************************************************************/
--현공정 로그 만들기 SET @iflowlog = @ipreflowlog + @sflowno
/*********************************************************************/
--이전공정코드번호찾기
Set @iprecodeno = dbo.F_GET_CHGCODE(@splno,substring(@ipreflowlog,len(@ipreflowlog),1))
--현공정코드번호찾기
SET @icodeno = dbo.F_GET_CHGCODE(@splno,@sflowno)
/*********************************************************************/
--코드번호로 부자재종류코드를 가져온다
SET @isubkindno = (SELECT isnull(subkind_no,'0') from SY_CODE..tblcode where code_no = @icodeno)
IF @isubkindno = '' or @isubkindno is null BEGIN
SET @isubkindno = '0'
END
END重点应该注意什么?DECLARE CUR_1 CURSOR FOR
/*********************************************************************/
--이전공정 로그 만들기
SET @ipreflowlog = ''
--이전 공정 로그를 만들기 위한 커서 생성
DECLARE CUR_1 CURSOR FOR
(select flow_no from sy_code..tblplflow where pl_no = @splno and flow_no < @sflowno and degree = (select degree from sy_code..tblmodelkind where modelkind_no = @smodelkindno))
OPEN CUR_1
FETCH NEXT FROM CUR_1 INTO @iimsiflow --조회한 flow_no 를 @iimsiflow에 담는다.
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @ipreflowlog = @ipreflowlog + @iimsiflow
FETCH NEXT FROM CUR_1 INTO @iimsiflow
END
CLOSE CUR_1
DEALLOCATE CUR_1
if @ipreflowlog = '' set @ipreflowlog = '0'
/*********************************************************************/
--현공정 로그 만들기 SET @iflowlog = @ipreflowlog + @sflowno
/*********************************************************************/
--이전공정코드번호찾기
Set @iprecodeno = dbo.F_GET_CHGCODE(@splno,substring(@ipreflowlog,len(@ipreflowlog),1))
--현공정코드번호찾기
SET @icodeno = dbo.F_GET_CHGCODE(@splno,@sflowno)
/*********************************************************************/
--코드번호로 부자재종류코드를 가져온다
SET @isubkindno = (SELECT isnull(subkind_no,'0') from SY_CODE..tblcode where code_no = @icodeno)
IF @isubkindno = '' or @isubkindno is null BEGIN
SET @isubkindno = '0'
END
END重点应该注意什么?DECLARE CUR_1 CURSOR FOR
定义游标 选出符合条件的OPEN CUR_1
FETCH NEXT FROM CUR_1 INTO @iimsiflow 打开游标把符合条件俄付给 @iimsiflow循环下一次
FETCH NEXT FROM CUR_1 INTO @iimsiflow 然后后面是调用函数
提示这么多尼自己去看吧
此例中上部分是游标操作,下部分是赋值操作。遵循从大到小的原则来分析。
IF @sflowlog = '' or @sflowlog is null or @sflowlog = '0'
BEGIN
SET @ipreflowlog = ''
DECLARE CUR_1 CURSOR FOR
(select flow_no from sy_code..tblplflow where pl_no = @splno and flow_no < @sflowno and degree =
(select degree from sy_code..tblmodelkind where modelkind_no = @smodelkindno))
OPEN CUR_1
FETCH NEXT FROM CUR_1 INTO @iimsiflow -- flow_no @iimsiflow WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ipreflowlog = @ipreflowlog + @iimsiflow
FETCH NEXT FROM CUR_1 INTO @iimsiflow
END CLOSE CUR_1
DEALLOCATE CUR_1 if @ipreflowlog = '' set @ipreflowlog = '0'
SET @iflowlog = @ipreflowlog + @sflowno
Set @iprecodeno = dbo.F_GET_CHGCODE( @splno, substring(@ipreflowlog,len(@ipreflowlog),1) )
SET @icodeno = dbo.F_GET_CHGCODE( @splno,@sflowno )
SET @isubkindno = (SELECT isnull(subkind_no,'0') from SY_CODE..tblcode where code_no = @icodeno)
IF @isubkindno = '' or @isubkindno is null
BEGIN
SET @isubkindno = '0'
END
END
抓住主要目的,再来看游标,while循环中完成的是@iimsiflow 的连接串,往回看@iimsiflow 来自游标定义中的flow_no 字段,静下来考察游标的定义,主要是条件从句中涉及三个字段,其中两字段直接与变量比较,另一字段与子查询比较,子查询没有与原表没有发生关系,应当好理解。
顺便看一下上一句set是给@ipreflowlog 赋初值以便连接串时不出问题(什么都没有)。set @ipreflowlog = '0' 与@isubkindno = '0' 在if句中一般都是补充处理,具体作用还得看程序的其它部分dbo.F_GET_CHGCODE 与 dbo.F_GET_CHGCODE 是两个函数,具体含义要还得从其它地方获得,马上去查函数内容
总之积累必要的知识是前提,整理大多数情况下还是必要的,要整体把握从大到小,从粗到细,当然变量太长可以用@AA,@id之类简单的来替换原来较长的符号,甚至可将长字段名也同样处理。
SET @ipreflowlog = ''
DECLARE CUR_1 CURSOR FOR
(select flow_no from sy_code..tblplflow where pl_no = @splno and flow_no < @sflowno and degree = (select degree from sy_code..tblmodelkind where modelkind_no = @smodelkindno))
OPEN CUR_1
FETCH NEXT FROM CUR_1 INTO @iimsiflow
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @ipreflowlog = @ipreflowlog + @iimsiflow
FETCH NEXT FROM CUR_1 INTO @iimsiflow
END
CLOSE CUR_1
DEALLOCATE CUR_1
if @ipreflowlog = '' set @ipreflowlog = '0' SET @iflowlog = @ipreflowlog + @sflowno
SET @icodeno = dbo.F_GET_CHGCODE(@splno,@sflowno)
SET @isubkindno = (SELECT isnull(subkind_no,'0') from SY_CODE..tblcode where code_no = @icodeno)
IF @isubkindno = '' or @isubkindno is null BEGIN
SET @isubkindno = '0'
END
END
没仔细看这段语句,但是以下这段代码效率实在太低了,
FETCH NEXT FROM CUR_1 INTO @iimsiflow
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @ipreflowlog = @ipreflowlog + @iimsiflow
FETCH NEXT FROM CUR_1 INTO @iimsiflow
END
这段可以用表变量,一次查询就可以实现累加,为什么一定要用游标啊,效率低了不知道多少倍