实验3PLSQL编程

v_increment number; begin

for v_emp in c_emp loop case v_emp.deptno

when 10 then v_increment:=100; when 20 then v_increment:=160; when 30 then v_increment:=200; else v_increment:=300; end case;

update scott.emp set sal = sal+v_increment where current of c_emp; end loop; end; 4.

5. begin

update scott.dept

set loc = 'BEIJING' where deptno=50; if sql%notfound then

insert into scott.dept(deptno,loc) values(50,'BEIJING'); dbms_output.put_line('插入成功!'); ELSE

dbms_output.put_line('更新成共'); end if; end; 6.

create or replace procedure emp_count as v_total number; begin

select count(*) into v_total from scott.emp; dbms_output.put_line('雇员总数：'||v_total); end; /

SQL> execute emp_count; 雇员总数：15

PL/SQL 过程已成功完成。

SQL> begin

2 emp_count; 3 end; 4 /

雇员总数：15

PL/SQL 过程已成功完成。 7.

1 CREATE OR REPLACE PROCEDURE EMP_LIST 2 AS

3 CURSOR emp_cursor IS

4 SELECT empno,ename FROM scott.emp; 5 BEGIN

6 FOR Emp_record IN emp_cursor LOOP

7 DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename); 8 END LOOP; 9 EMP_COUNT; 10* END; SQL> /

过程已创建。 8.

SQL> CREATE OR REPLACE PROCEDURE select_emp 2 (v_emp_no IN emp.empno%type) 3 IS

4 v_emp_name emp.ename%type; 5 v_dept_name dept.dname%type; 6 BEGIN

7 SELECT EMP.ENAME,DEPT.DNAME 8 INTO v_emp_name, v_dept_name 9 FROM EMP,DEPT

10 WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMPNO = v_emp_no; 11 DBMS_OUTPUT.PUT_LINE(v_emp_name||' '||v_dept_name); 12 END select_emp; 13 /

过程已创建。

SQL> EXECUTE select_emp(7844); TURNER SALES

PL/SQL 过程已成功完成。

SQL> CREATE OR REPLACE TRIGGER update_dept_to_emp 2 AFTER UPDATE ON DEPT FOR EACH ROW 3 BEGIN

4 IF UPDATING THEN

5 UPDATE EMP SET DEPTNO = :new.DEPTNO 6 WHERE DEPTNO=:old.DEPTNO; 7 END IF;

8 END update_dept_to_emp; 9 /

触发器已创建 9.

CREATE OR REPLACE TRIGGER tr_reg_dep AFTER update OF deptno ON dept

FOR EACH ROW BEGIN

DBMS_OUTPUT.PUT_LINE('旧的deptno值是'||:old.deptno ||'、新的deptno值是'||:new.deptno); UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno; END; 10.

select object_name,status from user_objects where object_type='FUNCTION'; select object_name,status from user_objects where object_type='PROCEDURE'; SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER';

drop procedure select_emp; DROP FUNCTION GET_AVG_PAY; DROP TRIGGER TR_REG_DEP;

五、拓展题

--创建一个包mypackage，声明该包有一个过程update_sal和一个函数get_YearSal create or replace package myPackage is

procedure update_sal(name varchar2,newsal number); function get_YearSal(name varchar2) return number; end;

create or replace package body myPackage is procedure update_sal(name varchar2,newsal number) is begin

update emp set sal=newSal where ename=name; end;

function get_YearSal(name varchar2) return number is v_sal number(7,2); begin

select sal*12+nvl(comm,0) into v_sal from emp where ename=name; return v_sal; end; end;

疑难小结：

在创建过程时，还是对基本知识掌握不牢固，在使用循环时有时会迷惑循环过程，追根揭底是对循环过程的不熟悉，还要勤加锻炼，对游标的声明及使用还是不熟悉，还有就是练习过程中会敲错代码，今后的学习过程中一点要结合课本多加练习吃透基本知识