cursor c2(p_condition varchar2) is
select diagnosis_code from diagnosis_dict
where ||' '||p_condition||' '||DIAGNOSIS_CODE IS NOT NULL;这句语法不对,||前面没字符串
select diagnosis_code from diagnosis_dict
where ||' '||p_condition||' '||DIAGNOSIS_CODE IS NOT NULL;这句语法不对,||前面没字符串
还有到底是什么问题,也没说清楚。
可以是把条件写在WHERE后面,用其它参数传递变量
c2的参数是通过在c1中取到的值传进来的
用变量sqls:=........的方式拼接,
然后for c2 in sqls loop的方式调用cursor。
c2中的select语句是通过c1中取到的值并凑到一起的。现在想知道在c2的游标声明中where后面的整个条件都是动态是不是可以?请各位高手支招!谢谢!!
AS
p_stat_code VARCHAR2 (20);
p_stat_name VARCHAR2 (40);
p_condition VARCHAR2 (100);
p_diagnosis_code VARCHAR2 (20);
p_diagnosis_name VARCHAR2 (40);
e_exec_string VARCHAR2 (1000); CURSOR c1
IS
SELECT stat_code,
REPLACE (REPLACE (condition, '~', ''),
'diagnostic_category',
'diagnosis_dict'
)
FROM dise_query_condition; sqls VARCHAR2 (1000);
BEGIN
OPEN c1; LOOP
FETCH c1
INTO p_stat_code, p_condition; EXIT WHEN c1%NOTFOUND;
sqls :=
'select diagnosis_code from diagnosis_dict where '
|| p_condition
|| ' and DIAGNOSIS_CODE IS NOT NULL'; FOR c2 IN sqls
LOOP
p_diagnosis_code := c2.diagnosis_code; SELECT diagnosis_name
INTO p_diagnosis_name
FROM diagnosis_dict
WHERE diagnosis_code = p_diagnosis_code; SELECT class_name
INTO p_stat_name
FROM dise_class_stat_dict
WHERE stat_code = p_stat_code; INSERT INTO diag_group_comp_dict
(diagnosis_code, diagnosis_name, stad_code,
class_name
)
VALUES (p_diagnosis_code, p_diagnosis_name, p_stat_code,
p_stat_name
);
END LOOP; END LOOP; CLOSE c1; COMMIT;
END;很有可能不正确,不过基本思想是这样,你自己调试调试。
CREATE OR REPLACE PROCEDURE single_insert_pro
AS
p_stat_code VARCHAR2 (20);
p_stat_name VARCHAR2 (40);
p_condition VARCHAR2 (100);
p_diagnosis_code VARCHAR2 (20);
p_diagnosis_name VARCHAR2 (40);
e_exec_string VARCHAR2 (1000); CURSOR c1
IS
SELECT stat_code,
REPLACE (REPLACE (condition, '~', ''),
'diagnostic_category',
'diagnosis_dict'
)
FROM dise_query_condition; sqls VARCHAR2 (1000);
BEGIN
OPEN c1; LOOP
FETCH c1
INTO p_stat_code, p_condition; EXIT WHEN c1%NOTFOUND;
sqls :=
'select diagnosis_code from diagnosis_dict where '
|| p_condition
|| ' and DIAGNOSIS_CODE IS NOT NULL'; OPEN c2
FOR sqls; LOOP
FETCH c2
INTO p_diagnosis_code; EXIT WHEN c2%NOTFOUND; SELECT diagnosis_name
INTO p_diagnosis_name
FROM diagnosis_dict
WHERE diagnosis_code = p_diagnosis_code; SELECT class_name
INTO p_stat_name
FROM dise_class_stat_dict
WHERE stat_code = p_stat_code; INSERT INTO diag_group_comp_dict
(diagnosis_code, diagnosis_name, stad_code,
class_name
)
VALUES (p_diagnosis_code, p_diagnosis_name, p_stat_code,
p_stat_name
);
END LOOP;
END LOOP; CLOSE c1; COMMIT;
END;
用
TYPE refcurtyp IS REF CURSOR; c2 refcurtyp;