17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure p_complte_d_rank(filterStr varchar2 , p_period varchar2) is
type c_total is ref cursor;
v_total c_total; -- 游标记录每行值
v_unitid SGCC_FACT_COMPLETE_D.unit_id%type; -- 单位ID
v_total_value number; -- 总得分
v_rank number ; -- 排名
begin
open v_total for
select unit_id from sgcc_fact_complete_d
where sj_type= p_period and unit_id = filterStr; -- 这样写就没有值循环
-- select unit_id from sgcc_fact_complete_d
-- where sj_type= '201104' and unit_id = '501101010000009112'; 这样写就有值循环
LOOP
fetch v_total into v_unitid ;
exit when v_total%notfound;
dbms_output.put_line(v_unitid);
end LOOP ;
end p_complte_d_rank;
open v_total for
select unit_id from sgcc_fact_complete_d
where sj_type= ''||p_period||'' and instr(',' || filterStr || ',',',' || unit_id || ',') > 0
;
open v_total for
select unit_id from sgcc_fact_complete_d
where sj_type= ''||p_period||'' and substr(',' || filterStr || ',',',' || unit_id || ',') > 0
;
open v_total for
select unit_id from sgcc_fact_complete_d
where sj_type= ''||p_period||'' and unit_id = ''||filterStr ||'';
--改成这样
CREATE OR REPLACE PROCEDURE p_complte_d_rank(filterStr VARCHAR2,
p_period VARCHAR2) IS
TYPE c_total IS REF CURSOR;
v_total c_total; -- 游标记录每行值
v_unitid SGCC_FACT_COMPLETE_D.unit_id%TYPE; -- 单位ID
v_total_value NUMBER; -- 总得分
v_rank NUMBER; -- 排名
BEGIN
OPEN v_total FOR 'SELECT unit_id
FROM sgcc_fact_complete_d
WHERE sj_type = ''' || p_period || ''' AND unit_id = ''' || filterStr || '''';
LOOP
FETCH v_total
INTO v_unitid;
EXIT WHEN v_total%NOTFOUND;
dbms_output.put_line(v_unitid);
END LOOP;
END p_complte_d_rank;
create or replace procedure p_complte_d_rank(filterStr varchar2 , p_period varchar2) is
v_unitid SGCC_FACT_COMPLETE_D.unit_id%type; -- 单位ID
v_total_value number; -- 总得分
v_rank number ; -- 排名
cursor c_total is select unit_id from sgcc_fact_complete_d
where sj_type= p_period and unit_id = filterStr;
begin
for data in c_total
loop
dbms_output.put_line('111');
end loop;
end p_complte_d_rank;