【Oracle】数据库应用技术上机-week16-for update游标、存储过程procedure、触发器trigger和游标的遍历

本文链接：https://blog.csdn.net/Bella_Seven/article/details/122470580

1.修改emp表的工资，工资不足1000的，调整为1500，工资高于1000的，调整为原来工资的1.5倍，调整后，若工资〉10000，则设其为10000

SQL> set serveroutput on

更改之前：

SQL> select * from scott.emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 
14 rows selected

for update游标：

SQL> 
SQL> declare
  2    cursor c_1 is select empno,sal from scott.emp
  3    for update of sal nowait;
  4    v_sal scott.emp.sal%type;
  5  begin
  6    for cursor_1 in c_1
  7      loop
  8        if cursor_1.sal<=1000 then v_sal:=1500;
  9        else
 10          v_sal:=1.5*cursor_1.sal;
 11          if v_sal>10000 then v_sal:=10000;
 12          end if;
 13        end if;
 14        update scott.emp set sal=v_sal where current of c_1;
 15      end loop;
 16  end;
 17  /
 
PL/SQL procedure successfully completed

更改之后：

SQL> select * from scott.emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17    1500.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     2400.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1875.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      4462.50               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1875.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      4275.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      3675.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     4500.00               20
 7839 KING       PRESIDENT       1981/11/17    7500.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      2250.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1650.00               20
 7900 JAMES      CLERK      7698 1981/12/3     1500.00               30
 7902 FORD       ANALYST    7566 1981/12/3     4500.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1950.00               10
 
14 rows selected

2.修改scott.emp表员工的工资，如果员工的部门号为10，工资提高100；部门号为20，工资提高150；部门号为30，工资提高200；否则工资提高250。
修改前：

SQL> select * from scott.emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17    1500.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     2400.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1875.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      4462.50               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1875.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      4275.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      3675.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     4500.00               20
 7839 KING       PRESIDENT       1981/11/17    7500.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      2250.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1650.00               20
 7900 JAMES      CLERK      7698 1981/12/3     1500.00               30
 7902 FORD       ANALYST    7566 1981/12/3     4500.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1950.00               10
 
14 rows selected

for update游标：

SQL> 
SQL> declare
  2    cursor c_2 is select * from scott.emp for update;
  3    v_zl number;
  4    v_emp c_2%rowtype;
  5  begin
  6    for v_emp in c_2
  7      loop
  8        case v_emp.deptno
  9          when 10 then v_zl:=100;
 10          when 20 then v_zl:=150;
 11          when 30 then v_zl:=200;
 12          else v_zl:=250;
 13        end case;
 14        update scott.emp set sal=sal+v_zl where current of c_2;
 15      end loop;
 16  end;
 17  /
 
PL/SQL procedure successfully completed

修改后：

SQL> select * from scott.emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17    1650.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     2600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     2075.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      4612.50               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     2075.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      4475.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      3775.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     4650.00               20
 7839 KING       PRESIDENT       1981/11/17    7600.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      2450.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1800.00               20
 7900 JAMES      CLERK      7698 1981/12/3     1700.00               30
 7902 FORD       ANALYST    7566 1981/12/3     4650.00               20
 7934 MILLER     CLERK      7782 1982/1/23     2050.00               10
 
14 rows selected

3.创建一个存储过程，以部门号为该存储过程的in类型参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。(for 循环遍历游标)

创建

SQL> 
SQL> create or replace procedure show_emp(p_deptno  scott.emp.deptno%TYPE)
  2  is
  3    v_sal scott.emp.sal%TYPE;
  4  begin
  5    select avg(sal) into v_sal from scott.emp where deptno=p_deptno;
  6    dbms_output.put_line(p_deptno||' '||v_sal);
  7    for v_emp in(SELECT * FROM  scott.emp WHERE deptno=p_deptno AND sal>v_sal)
  8      LOOP
  9      DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
 10      END LOOP;
 11  end show_emp;
 12  /
 
Procedure created

调用

SQL> 
SQL> begin
  2    show_emp(20);
  3  end;
  4  /
 
20 3472.5
7566 JONES
7788 SCOTT
7902 FORD
 
PL/SQL procedure successfully completed

4.用存储过程进行模糊查找，如查找ename中包含L的雇员信息

创建

SQL> 
SQL> create or replace procedure tp1(varEmpName scott.emp.ename%type)
  2  is
  3    cursor c_1 is select * from scott.emp where ename like '%'||varEmpName||'%';
  4  begin
  5    for v_1 in c_1
  6      loop
  7        dbms_output.put_line(v_1.empno||'  '||v_1.ename||'  '||v_1.job||'  '||v_1.deptno);
  8      end loop;
  9  end tp1;
 10  /
 
Procedure created

调用

SQL> 
SQL> begin
  2   tp1('M');
  3  end;
  4  /
 
7369  SMITH  CLERK  20
7654  MARTIN  SALESMAN  30
7876  ADAMS  CLERK  20
7900  JAMES  CLERK  30
7934  MILLER  CLERK  10
 
PL/SQL procedure successfully completed

5.功能要求：针对Scott.emp表，记录其相应操作的信息，具体如下:
当执行插入操作时，统计操作后员工人数；
当执行更新工资操作时，统计更新后员工平均工资；
当执行删除操作时，统计删除后各个部门剩余的人数(游标)。

触发器：

create or replace trigger t4
  after insert or update or delete on scott.emp  
declare
  v_1  number;
  v_2  scott.emp.sal%type;
begin
  if inserting then
         select count(*)  into  v_1 from scott.emp;
         DBMS_OUTPUT.PUT_LINE('添加记录后总人数为'||v_1);
   elsif   updating then
          select avg(sal)  into v_2  from scott.emp;
          DBMS_OUTPUT.PUT_LINE('更新记录后平均工资为'||' '||v_2);
   else
         for v_s in (select  deptno,count(*)  num   from  scott.emp  group by deptno)
         loop
          DBMS_OUTPUT.PUT_LINE('删除记录后各个部门的部门号和人数为' ||v_s.deptno||' '||v_s.num);
     end  loop;
  end if;
end t4;

触发：

SQL> delete from scott.emp where hiredate<=to_date('1980-12-17','yyyy-mm-dd')；
 删除记录后各个部门的记录号和平均工资为20 3
    删除记录后各个部门的记录号和平均工资为80 5
    删除记录后各个部门的记录号和平均工资为10 2

6.功能要求：为保证数据库的安全性,通过触发器记录何用户，何时登陆了系统

建立表：

SQL> 
SQL> create table u_1
  2           (    username varchar2(50),
  3                activity  varchar2(20),
  4                 time date
  5            )
  6

触发器：

create or replace trigger t1
  after logon on database  
declare
  -- local variables here
begin
  insert into u_1   values(user,'LOGON',sysdate);
end t1;

触发：

SQL> select  username,activity,to_char(time,'yyyy-MM-dd HH24:mi') from u_1;
 
USERNAME                                           ACTIVITY             TO_CHAR(TIME,'YYYY-MM-DDHH24:M
-------------------------------------------------- -------------------- ------------------------------