游标是数据库编程中必须要熟练掌握的技术,主要实现针对数据集合,进行循环处理,因为SQL本身只能一次性处理,所以当有稍微复杂的因为时,都在存储过程中使用游标进行实现。静态游标在执行前就能确定对应查询语句,最多只是传递一些查询参数而已,所以比较容易处理。动态游标是在执行前查询SQL是动态拼接的,不确定具体查询那些表和条件。
在MSSQL中,定义动态游标比较麻烦,一般先拼接SQL,然后动态执行存放临时表中,然后定义游标读取临时表;相比ORACLE则强大多了,直接定义游标,然后直接打开就可以实现,感慨其强大。下面通过示例进行说明。
create or replace
PROCEDURE PX_VARIFY_ZRP_REPEAT
(
V_IMPORT_ID IN VARCHAR2
) AS
V_PERSON_ID VARCHAR2(36); -- 返回主体ID
V_CNT NUMBER(5,0); -- 符合条件记录数
CURSOR CV_PERSONS IS
SELECT ID,ID_TYPE,ID_CODE FROM NAT_PERSON_INFO
WHERE IMPORT_ID = V_IMPORT_ID;
BEGIN
FOR CV_PERSON IN CV_PERSONS LOOP
PX_VARIFY_ZRP_IDS_ATOM(CV_PERSON.ID_TYPE,CV_PERSON.ID_CODE,V_PERSON_ID,V_CNT); -- 执行其他过程,实现复杂业务
IF V_CNT = 0 AND V_PERSON_ID IS NULL THEN
UPDATE NAT_PERSON_INFO SET IMPORT_CHECK_FLAG = '0' , CHECK_ERR_MSG = CHECK_ERR_MSG || ' 验证规则代码:1001;错误描述:未找到对应自然人信息.'
WHERE ID = CV_PERSON.ID;
END IF;
END LOOP;
END PX_VARIFY_ZRP_REPEAT;
2、动态游标示例
动态SQL作为游标执行的语句,定义时仅说明类型,打开时指定SQL.循环处理是采用loop,所以需要手动结束。
create or replace
PROCEDURE PX_VARIFY_ZRP_EXT_MATCH
(
V_TABLE_NAME IN VARCHAR2
, V_IMPORT_ID IN VARCHAR2
) AS
V_SQL VARCHAR2(1024);
VU_SQL VARCHAR2(1024);
TYPE CV_PERSONS IS REF CURSOR;
CV_PERSON CV_PERSONS;
V_ID NAT_PERSON_INFO.ID%TYPE; -- ID
V_ID_TYPE NAT_PERSON_INFO.ID_TYPE%TYPE; -- 证件类型
V_ID_CODE NAT_PERSON_INFO.ID_CODE%TYPE; -- 证件号码
V_PERSON_ID VARCHAR2(36); -- 返回主体ID
V_CNT NUMBER(5,0); -- 符合条件记录数
BEGIN
V_SQL := 'SELECT ID,ID_TYPE,ID_CODE ';
V_SQL := V_SQL || ' FROM ' || V_TABLE_NAME;
V_SQL := V_SQL || ' WHERE IMPORT_ID = ' || V_IMPORT_ID;
OPEN CV_PERSON FOR V_SQL;
LOOP
FETCH CV_PERSON INTO V_ID,V_ID_TYPE,V_ID_CODE;
EXIT WHEN CV_PERSON%NOTFOUND;
PX_VARIFY_ZRP_IDS_ATOM(V_ID_TYPE,V_ID_CODE,V_PERSON_ID,V_CNT);
IF V_CNT = 1 AND V_PERSON_ID IS NOT NULL THEN
VU_SQL := 'UPDATE ' || V_TABLE_NAME || ' SET PERSON_ID = ''' || V_PERSON_ID || ''' WHERE ID = ''' || V_ID || '''' ;
END IF;
IF V_CNT = 0 AND V_PERSON_ID IS NULL THEN
VU_SQL := 'UPDATE ' || V_TABLE_NAME || ' SET IMPORT_CHECK_FLAG = ''0'' , ' ;
VU_SQL := VU_SQL || ' CHECK_ERR_MSG = CHECK_ERR_MSG || ' || ''' 验证规则代码:1001;错误描述:未找到对应自然人信息.''';
VU_SQL := VU_SQL || ' WHERE ID = ''' || V_ID || '''' ;
END IF;
DBMS_OUTPUT.PUT_LINE(VU_SQL);
execute immediate VU_SQL;
END LOOP;
<pre name="code" class="sql"> CLOSE CV_PERSON;
END PX_VARIFY_ZRP_EXT_MATCH;