光标_parameter_练习_sm.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 57 行

--
declare 
  cursor c_emp is select * from sm_emp;
  cursor c_order (p_empid char) is select * from sm_saleorderlist where EMPLOYID=p_empid;
  r_emp sm_emp%rowtype;
  r_order sm_saleorderlist%rowtype;
  v_total number:=0;


begin 
  open c_emp;
  
  loop
    v_total:=0;
    fetch c_emp into r_emp;
    exit when c_emp%notfound;
    
    open c_order(r_emp.empid);
    
    loop 
      fetch c_order into r_order;
      exit when c_order%notfound;
      --对应人员已经没有经手的交易
      v_total :=v_total+r_order.totalprice;
    end loop;
    close c_order;
    dbms_output.put_line('雇员' || r_emp.name || '综销售额是：' || v_total);
  end loop;
  close c_emp;
end;


-------------------------------------------------------------------------------------
--使用光标for loop处理
declare
    
  cursor c_emp is select * from sm_emp;
  --sm_emp的光标
  cursor c_order (p_empid char) is select * from sm_saleorderlist where EMPLOYID=p_empid;
  --sm_saleorderlist的光标    

    v_total number;
    
begin
    v_total := 0;
    --外层部门光标for循环
    for r_emp in c_emp loop 
        --内层雇员光标for循环
	v_total:=0;
        for r_order in c_order(r_emp.empid) loop
            v_total := v_total + r_order.totalprice;
        end loop;

        dbms_output.put_line('雇员' || r_emp.name || '综销售额是：' || v_total);
    end loop;    

end ;