Oracle 游标的使用 及函数

1：在SCOTT模式下显示工资排名前5的员工的编号、姓名和工资，并将所有执行正确的PL/SQL语句记录下来。

SQL> declare
  2  i number:=1;
  3  begin
  4  for r in( select *from emp order by sal desc)
  5  loop
  6  dbms_output.put_line(r.empno||' '||r.ename||' '||r.sal);
  7  i:=i+1;
  8  exit when i>5;
  9  end loop;
 10  end;
 11  /

2：创建一个函数，他以部门号为参数，返回部门名称和位置。

SQL> create or replace function fun(dpno number) return dept%rowtype is v_dept dept%rowtype;
  2  begin
  3  select * into v_dept from dept where deptno=dpno;
  4  return v_dept;
  5  end;
  6  /


SQL> declare
  2  v_dept dept%rowtype;
  3  begin
  4  v_dept:=fun(30);
  5  dbms_output.put_line(v_dept.dname||' '||v_dept.loc);
  6  end;
  7  /

查询用户名

select username from user_users;

select username from dba_users；

通过输入的部门号输出员工信息

SQL> declare cursor emp_cur (dno in number)
  2  is select empno,ename,sal from emp where deptno=dno;
  3  type employee is record(
  4  no emp.empno%type,
  5  name emp.ename%type,
  6  salary emp.sal%type);
  7  emp_row employee;
  8  begin
  9  open emp_cur(30);
 10  fetch emp_cur into emp_row;
 11  while emp_cur%found loop
 12  dbms_output.put_line(emp_row.no);
 13  dbms_output.put_line(emp_row.name);
 14  dbms_output.put_line(emp_row.salary);
 15  fetch emp_cur into emp_row;
 16  end loop;
 17  close emp_cur;
 18  end;
 19  /

改进，部门号从键盘输入

SQL> declare cursor emp_cur (dno in number)
  2  is select empno,ename,sal from emp where deptno=dno;
  3  type employee is record(
  4  no emp.empno%type,
  5  name emp.ename%type,
  6  salary emp.sal%type);
  7  emp_row employee;
  8  no number:=&no;
  9  begin
 10  open emp_cur(no);
 11  fetch emp_cur into emp_row;
 12  while emp_cur%found loop
 13  dbms_output.put_line(emp_row.no);
 14  dbms_output.put_line(emp_row.name);
 15  dbms_output.put_line(emp_row.salary);
 16  fetch emp_cur into emp_row;
 17  end loop;
 18  close emp_cur;
 19  end;
 20  /

创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。并且调用该存储过程,要求根据输入部门编号，查询平均工资及输出比平均工资高的员工号、员工名。

SQL> create or replace procedure ss (pno emp.deptno%type)is
  2  v_sal emp.sal%type;
  3  begin
  4  select avg(sal) into v_sal from emp where deptno=pno;
  5  dbms_output.put_line(v_sal);
  6  for v_emp in(select *from emp where deptno=pno and sal>v_sal) loop
  7  dbms_output.put_line(v_emp.empno||v_emp.ename);
  8  end loop;
  9  end ss;
 10  /




SQL> declare
  2  pno emp.deptno%type;
  3  begin
  4  pno:=&pno;
  5  ss(pno);
  6  end;
  7  /
输入 pno 的值:  10
原值    4: pno:=&pno;
新值    4: pno:=10;

PL/SQL 过程已成功完成。

创建一个函数，以部门号为参数，返回部门名、部门人数及部门平均工资。并且调用该函数，输出所有有员工的部门的名称、部门人数和平均工资。

CREATE OR REPLACE FUNCTION ret_deptinfo(p_deptnodept.deptno%TYPE,
 
p_num OUT NUMBER, p_avg OUT NUMBER)
 
RETURN dept.dname%TYPE
 
AS
 
v_dnamedept.dname%TYPE;
 
BEGIN
 
  SELECT dname INTO v_dname FROM dept WHERE deptno=p_deptno;
 
  SELECT count(*),avg(sal) INTO p_num,p_avg FROM emp WHERE deptno=p_deptno;
 
  RETURN v_dname;
 
END ret_deptinfo;
 
函数调用语句：
 
DECLARE
 
v_avgsalemp.sal%TYPE;
 
v_num    NUMBER;
 
v_dnamedept.dname%TYPE;
 
BEGIN
 
  FOR v_dept IN (SELECT DISTINCT deptno FROM emp) LOOP
 
v_dname:=ret_deptinfo(v_dept.deptno,v_num,v_avgsal);
 
    DBMS_OUTPUT.PUT_LINE(v_dname||'  '||v_maxsal||'  '||  v_avgsal||' '||v_num);
 
  END LOOP;
 
END;

oracle语句，创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。

SQL> create or replace function s0(v_no emp.empno%type)
  2  return number is
  3  aver number;
  4  begin
  5  select avg(sal)into aver from emp where deptno=(select deptno from emp where empno=v_no);
  6  return aver;
  7  end;
  8  /

SQL> declare
  2  m number;
  3  v_no emp.empno%type:=&v_no;
  4  begin
  5  m:=s0(v_no);
  6  dbms_Output.put_line(m);
  7  end;
  8  /


SQL> set serveroutput on;
SQL> /
输入 v_no 的值:  7369
原值    3: v_no emp.empno%type:=&v_no;
新值    3: v_no emp.empno%type:=7369;
2610

PL/SQL 过程已成功完成。

输出员工工资<1200的员工姓名和工资（游标FOR）

SQL> declare begin
  2  for r in(select *from emp where sal<1200) loop
  3  dbms_output.put_line(r.ename);
  4  dbms_output.put_line(r.sal);
  5  end loop;
  6  end;
  7  /
SMITH
960
JAMES
1140
crq
174