oracle PL/SQL程序设计、函数、存储过程、触发器练习

PL/SQL程序设计

• 求圆的面积。Pi为常量，半径可以输入

set serveroutput on
declare
pi constant number(3,2):=3.14;
r number(5,2):=&a;
area number(8,2);
begin
area:=pi*r*r;
dbms_output.put_line('圆的面积时'||area);
end;

• 计算某个数字的平方值，使用替代变量来提供这个数字的值。然后在屏幕上显示处理结果。

set serveroutput on
declare
a number:=&a;
begin
dbms_output.put_line(a||'的平方是'||a*a);
end;

• 基于当天的日期计算当日是星期几。

set serveroutput on
declare
v_day varchar2(10);
v_date date:=sysdate;
begin
v_day:=to_char(v_date,'day');
dbms_output.put_line('今天是'||v_day);
end;

存储过程

• 编写一个过程，要求，可以传入部门的编号，部门的名称，部门的位置，之后调用此过程就可以完成部门的增加操作。

create or replace procedure adddept(no dept.deptno%type,name dept.dname%type,loc dept.loc%type) 
is
cou number;
begin
select count(*) into cou from dept where deptno=no;
if cou=0 then
insert into dept values(no,name,loc);
else
dbms_output.put_line('部门已存在');
end if;
end;

1)创建三张表 dept10,dept20,dept30，表结构和 dept 一致（不拷贝数据）

create table dept10 as select * from dept where 1=2;
create table dept20 as select * from dept where 1=2;
create table dept30 as select * from dept where 1=2;

where 1=2 表示不选择记录
where 1=1 表示选择所有记录
(2). 编写一个存储过程 mypro，
i. 把 dept 表中 depto=10 的数据，存到 dept10,
ii. 把 dept 表中 depto=20 的数据，存到 dept20
iii. 把 dept 表中 depto=30 的数据，存到 dept30
iv. 执行该存储过程

create or replace procedure myproc
as
begin
insert into dept10 select * from dept where deptno=10;
insert into dept20 select * from dept where deptno=20;
insert into dept30 select * from dept where deptno=30;
end;

• 写一个存储过程 (给一个用户名，判断该用户名是否存在)

create or replace procedure findname(pname emp.ename%type)
is
cou number;
begin
select count(*) into cou from emp where ename=upper(pname);
if
cou=0 then
dbms_output.put_line('用户不存在');
else
dbms_output.put_line('用户存在');
end if;
end;
exec findname('smith');

• 编写一个过程，可以输入一个雇员号，如果雇员的补助不是0，则在原来基础上增加100元；如果雇员的补助为0,则把补助设为200元。

create or replace procedure proc_emp(v_empno varchar2) is
v_comm emp.comm%type;
begin
select nvl(comm,0) into v_comm from emp where empno=v_empno;
if v_comm<>0 then
update emp set comm=comm+100 where empno=v_empno;
else
update emp set comm=200 where empno=v_empno;
end if;
end;

触发器

• 每天12点以后，不允许修改雇员的工资和奖金

create or replace trigger notupdata before update or insert or delete on emp
declare
time varchar2(20):=to_char(sysdate,'hh24');
begin
if time>=12 then
raise_application_error(-20001, '当前时间不能修改');
end if;
end;

不是每月的十号不允许进行增删操作

create or replace trigger adduser_trigger before insert or delete on emp 
declare
riqi VARCHAR2(20):=to_char(sysdate,'dd');
begin
if riqi <>10 then
raise_application_error(-20001, '不是操作日');
end if;
end;

• 编写一个存储过程，批量插入 1000 条数据（只插入 ID 为奇数的数据）

create table test(i number(10));
create or replace procedure add1
as
i number(10);
begin
for i in 1..1000 loop
if mod(i,2) = 1 then
insert into test values(i);
end if;
end loop;
end;

• 统计员工工资变化

Create table audit_emp_change(
Name varchar2(10),
Oldsal number(6,2),
Newsal number(6,2),
Time date);
Create or replace trigger tr_sal_sal
after update of sal on emp
for each row
declare
v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate
where name=:old.ename;
end if;
end;

• 只统计销售员工资的变化

Create or replace trigger tr_sal_sal
after update of sal on emp
for each row
when (old.job=‟SALESMAN‟)
declare
v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate
where name=:old.ename;
end if;
end;

• 限定新工资不能低于其原来工资，也不能高于 20%。

create or replace trigger changesal before update of sal on emp for each row
when (new.sal<old.sal or new.sal>old.sal*1.2)
begin
raise_application_error(-20002, 'dddd');
end;

• 限定新工资不能低于其原来工资

create or replace trigger addsal before update on emp for each row
begin
if :old.sal>:new.sal then
raise_application_error(-20002, '工资降低');
end if;
end;

• 向dept表中添加数据时，部门编号是主键，要求必须有值且不重复，可以使用序列实现这个要求。
  创建一个触发器，使用该触发器自动为该主键赋值，从而不需要手动方式向dept表的主键列添加数据。

create or replace trigger adddata before insert on dept for each row
begin
if :new.deptno is null then 
select sequence2.nextval into :new.deptno from dual;
end if;
end;
insert into dept(dname,loc) values ('123','weihai');
select * from dept;

-将插入的雇员的名字变成以大写字母开头

create or replace trigger initcap before insert on emp for each row
declare
begin
:new.ename:=initcap(:new.ename);
end;

函数

• 写一个函数 输入一个员工名字，判断该名字在员工表中是否存在。存在返回 1，不存在返回 0

create or replace function empfun(name emp.ename%type) return number
is
is_exist number;
begin
select count(*) into is_exist from emp where ename=upper(name);
return is_exist;
end;

• 查询某个员工的年收入

create or replace function queryempincomm(eno in number) RETURN NUMBER
as
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=eno;
RETURN psal*12+  nvl(pcomm,0);
end;

• 记录类型作为返回类型，根据指定的部门号返回其对应的部门信息
  函数创建：

create or replace function dept_info(dno dept.deptno%type) RETURN dept%rowtype
is
rec_dept dept%rowtype;
begin
  select * into rec_dept from dept where deptno=dno;
  return rec_dept;
EXCEPTION
  when no_data_found then
    dbms_output.put_line('指定的部门不存在');
end;

函数调用：

set serveroutput on
declare
dept_rec dept%rowtype;
begin
  dept_rec:=dept_info(10);
  dbms_output.put_line('10部门信息为：'||dept_rec.deptno||' '||dept_rec.dname);
end;

• 写一个函数，传入员工编号，返回所在部门名称

create or replace function dname(no emp.empno%type) return dept.dname%type
is
dno emp.deptno%type;
deptname dept.dname%type;
begin
select deptno into dno from emp where empno=no;
select dname into deptname from dept where deptno=dno;
return deptname;
end;
begin
dbms_output.put_line(dname(7369));
end;
--相当于连接查询
select dname from dept d join emp e on d.deptno=e.deptno where empno=7369; 

• 写一个函数，传入时间，返回入职时间比这个时间早的所有员工的平均工资

create or replace function getAvgSal(hdate emp.hiredate%type) return emp.sal%type
is
avgsal emp.sal%type;
begin
select avg(sal) into avgsal from emp where hiredate<hdate;
return avgsal;
end;
begin
dbms_output.put_line(getAvgSal('18-12月-1980'));
end;