--根據人名查個人薪水和本部門平均薪水
--select語句實現
select a.deptno ?"部門編號",a.dname ? "部門名稱",a.ename ? "姓名",a.sal ? ? "薪水",b.avg_sal "部門平均薪水"from (select d.deptno, d.dname, e.ename, sal + nvl(comm, 0) salfrom emp ejoin dept d on e.deptno = d.deptnowhere ename = '&name') ajoin (select deptno, avg(sal + nvl(comm, 0)) avg_salfrom empwhere deptno in (select deptno from emp where ename = '&name')group by deptno) b on a.deptno = b.deptno;
--匿名塊實現
declarev_deptno ?emp.deptno%type;v_dname ? dept.dname%type;v_ename ? varchar(20);v_sal ? ? emp.sal%type;v_avg_sal emp.sal%type;beginv_ename := '&請輸入姓名：';select deptno into v_deptno from emp where ename = v_ename;dbms_output.put_line('部門編號：' || v_deptno);select dname into v_dname from dept where deptno = v_deptno;dbms_output.put_line('部門名稱：' || v_dname);dbms_output.put_line('員工姓名：' || v_ename);select sal + nvl(comm, 0) into v_sal from emp where ename = v_ename;dbms_output.put_line('員工薪水：' || v_sal);select avg(sal + nvl(comm, 0))into v_avg_salfrom empwhere deptno = v_deptno;dbms_output.put_line('部門平均薪水：' || v_avg_sal);end;
--游標存儲過程實現（無select語句）
create or replace procedure p1011(v_ename in emp.ename%type) iscursor c is select * from emp;cursor cd is select * from dept;v_sal emp.sal%type;v_avg_sal emp.sal%type := 0;v_dname dept.dname%type;v_deptno dept.deptno%type;v_n number := 0;beginfor v_emp in c loopif v_emp.ename = v_ename thenv_sal := v_emp.sal + nvl(v_emp.comm,0);v_deptno := v_emp.deptno;end if;end loop;for v_dept in cd loopif v_dept.deptno = v_deptno thenv_dname := v_dept.dname;end if;end loop;for v_emp in c loopif v_emp.deptno = v_deptno thenv_avg_sal := v_avg_sal + v_emp.sal + nvl(v_emp.comm,0);v_n := v_n + 1;end if;end loop;v_avg_sal := v_avg_sal / v_n;dbms_output.put_line('員工姓名：' || v_ename);dbms_output.put_line('員工薪水：' || v_sal);dbms_output.put_line(v_dname || '部門的平均薪水是：' || v_avg_sal);end;

總結

以上是生活随笔為你收集整理的Oracle plsqlI 练习 传值的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。