plsql习题

Pl/sql 语句习题： P410（1）： declare v_empno emp.empno%type; cursor c_emp is select * from emp where empno=v_empno; v_emp emp%rowtype; begin v_empno:=&x; open c_emp; loop fetch c_emp into v_emp; exit when c_emp%notfound; dbms_output.put_line(v_emp.ename||' '||v_emp.empno||' '||v_emp.sal||' '||v_emp.deptno); end loop; close c_emp; end; p410(2): declare v_ename emp.ename%type; cursor c_emp is select * from emp where ename=v_ename for update; v_emp emp%rowtype; begin v_ename:=&x; open c_emp; if c_emp%notfound then insert into emp(empno,ename,sal,deptno) values(2007,'SMITH',1500,10); else loop fetch c_emp into v_emp; exit when c_emp%notfound; dbms_output.put_line(v_emp.empno||' '||v_emp.sal||' '||v_emp.deptno); end loop; end if; close c_emp; end; p410(3): 先创建： create or replace procedure c_emp(v_empno emp.empno%type) as v_sal emp.sal%type; begin select sal into v_empno from emp where empno=7844; dbms_output.put_line(v_sal); end c_emp; 再调用： declare v_sal emp.sal%type; v_empno emp.empno%type; begin v_empno:=&x; c_emp(v_empno); dbms_output.put_line(v_sal); end; p410(4): 先创建： create or replace procedure example(v_empno emp.empno%type) as v_deptno emp.deptno%type; v_sal emp.sal%type; v_increment number(4); begin select deptno into v_deptno from emp where empno=v_empno; CASE v_deptno when 10 then v_increment:=150; when 20 then v_increment:=200; when 30 then v_increment:=250; else v_increment:=300; end CASE; update emp set sal=sal+v_increment where deptno=v_deptno; select sal into v_sal from emp where empno=v_empno; dbms_output.put_line(v_sal); end example; 在调用：

declare v_sal emp.sal%type; v_empno emp.empno%type; begin v_empno:=&x; example(v_empno); dbms_output.put_line(v_sal); end; p410（5）： 创建： create or replace procedure c_emp(v_empno emp.empno%type) as v_hiredate number(4); begin select (sysdate-hiredate)/365 into v_hiredate from emp where empno=v_empno; dbms_output.put_line(v_hiredate); end; 调用： declare v_workyears number(4); v_empno emp.empno%type; begin v_empno:=&x; c_emp(v_empno); dbms_output.put_line(v_workyears); p410（7）：先创建一个函数： create or replace function c_emp1(v_empno emp.empno%type) return emp.sal%type as v_sal emp.sal%type; begin select sal into v_sal from emp where empno=v_empno; return v_sal; end c_emp1; 在调用函数： declare v_empno emp.empno%type; v_sal emp.sal%type; begin v_empno:=&x; v_sal:=c_emp1(v_empno); dbms_output.put_line(v_sal); end; p410(9): 先创建函数： create or replace function a(v_empno emp.empno%type) return emp.sal%type as v_deptno emp.deptno%type; v_avgsal emp.sal%type; begin select deptno into v_deptno from emp where empno=v_empno; select avg(sal) into v_avgsal from emp where deptno=v_deptno; return v_avgsal; end; 在调用： declare v_empno emp.empno%type; v_avgsal emp.sal%type; begin v_empno:=&x; v_avgsal:=a(v_empno); dbms_output.put_line(v_avgsal); end; （10）: 先创建一个包规范： create or replace package pkg_emp as function func(v_deptno emp.deptno%type) return emp.sal%type; procedure proc(v_deptno emp.deptno%type); end pkg_emp; 在创建包体： create or replace package body pkg_emp as function func(v_deptno emp.deptno%type) return emp.sal%type as v_sal emp.sal%type; begin select min(sal) into v_sal from emp where deptno=v_deptno; return v_sal; end func; procedure proc(v_deptno emp.deptno%type) as v_sal emp.sal%type; begin select max(sal) into v_sal from emp where deptno=v_deptno; end proc; end pkg_emp;

最后调用包： declare v_deptno emp.deptno%type; v_minsal emp.sal%type; begin v_deptno:=&x; v_minsal:=pkg_emp.func(v_deptno); pkg_emp.proc(v_deptno); dbms_output.put_line(v_minsal); end;

创建数据库用户： （1）：创建一个口令认证的数据库用户user3，口令为user3默认表空间为USERS ,配额为10MB,初始帐号为锁定状态。 create user user3 identified by user3 default tablespace users quota 10M on users account lock。 创建概要文件：create profile res_profile limit sessions_per_user 4 connect_time 60 idle_time 20 private_sga 100k cpu_per_call 100； 触发器： P410(13)：create or replace trigger trg_emp1 after insert or update or delete on emp declare v_count number; v_sal emp.sal%type; begin if inserting or updating or deleting then select deptno,count(*),avg(sal) into v_count,v_sal from emp group by deptno; dbms_output.put_line(v_count||' '||v_sal); end if; end trg_emp1;