SELECT * FROM mqcitem
DROP TABLE TEST_CUR
CREATE TABLE TEST_CUR
(
QCCD VARCHAR2(5) NOT NULL, ---创建表时候这里是单引号 不是;号码,如用;号码会报出“右カッコがありません。”错误
GENNO VARCHAR2(20) NOT NULL,
LOTNO VARCHAR2(20) NOT NULL,
ITEMCD VARCHAR2(100) NOT NULL,
SEX VARCHAR2(2) NOT NULL
);
--SELECT * FROM TEST_CUR
DECLARE
sQccd VARCHAR2(5);
sGenno VARCHAR2(20);
sLotno VARCHAR2(20);
sItemcd VARCHAR2(100);
CURSOR ItemInfo_Cur IS
SELECT DISTINCT qccd, genno, lotno, itemcd
FROM MQCITEM WHERE QCCD IS NOT NULL;
BEGIN
IF ItemInfo_Cur%ISOPEN THEN
FETCH ItemInfo_Cur INTO sQccd, sGenno, sLotno, sItemcd;
ELSE
OPEN ItemInfo_Cur;
END IF;
LOOP
EXIT WHEN NOT ItemInfo_Cur%FOUND;---若游标到结尾则结束
IF SUBSTR(sLotno, 5, 3) = '101' AND sGenno = '00' THEN --(用sGenno == '00' 不对的)
INSERT INTO TEST_CUR(qccd, genno, lotno, itemcd, sex)
VALUES (sQccd, sGenno, sLotno, sItemcd, '1');
ELSE
INSERT INTO TEST_CUR(qccd, genno, lotno, itemcd,sex)
VALUES (sQccd, sGenno, sLotno, sItemcd, '0');
END IF;
FETCH ItemInfo_Cur INTO sQccd, sGenno, sLotno, sItemcd;
END LOOP;
CLOSE ItemInfo_Cur;
END;
--DELETE FROM TEST_CUR;
--SELECT * FROM TEST_CUR;
--SELECT COUNT(*) FROM TEST_CUR;
--2---------------------------
--コンポーネントSLOTNOを宣言してください。
DECLARE
TYPE teacher_record IS RECORD
(
sQccd VARCHAR2(5),
sGenno VARCHAR2(20),
sLotno VARCHAR2(20),
sItemcd VARCHAR2(100)
);
CURSOR ItemInfo_Cur IS
SELECT DISTINCT qccd, genno, lotno, itemcd
FROM MQCITEM WHERE QCCD IS NOT NULL;
BEGIN
FOR teacher_record IN ItemInfo_Cur
LOOP
IF teacher_record.sLotno = '1QL-101' THEN
UPDATE TEST_CUR SET SEX ='2';
END IF;
END LOOP;
END;
---3参数化游标----------------
--識別子MY_LOTを宣言してください。
ACCEPT my_lot prompt 'Please input the id ' ;
DECLARE
sQccd VARCHAR2(5);
sGenno VARCHAR2(20);
sLotno VARCHAR2(20);
sItemcd VARCHAR2(100);
CURSOR ItemInfo_Cur(CURSOR_Lot VARCHAR2) IS
SELECT DISTINCT qccd, genno, lotno, itemcd
FROM MQCITEM WHERE lotno = CURSOR_Lot;
BEGIN
OPEN ItemInfo_Cur(my_lot);
LOOP
FETCH ItemInfo_Cur INTO sQccd, sgenno, slotno, sitemcd;
EXIT WHEN ItemInfo_Cur%NOTFOUND;
---......
END LOOP;
CLOSE ItemInfo_Cur;
END;