--上机1
1)
declare
v_taxes number :=3500;--起征点
v_money number ;--应交税
begin
if v_sal-v_taxes<1500 then
v_money :=(8000-v_taxes)*0.03-0;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=1500 and v_sal-v_taxes<4500 then
v_money :=(8000-v_taxes)*0.1-105;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=4500 and v_sal-v_taxes<9000 then
v_money :=(8000-v_taxes)*0.2-555;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=9000 and v_sal-v_taxes<35000 then
v_money :=(8000-v_taxes)*0.25-1005;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=35000 and v_sal-v_taxes<55000 then
v_money :=(8000-v_taxes)*0.3-2755;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=55000 and v_sal-v_taxes<80000 then
v_money :=(8000-v_taxes)*0.35-5505;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=80000 then
v_money :=(8000-v_taxes)*0.45-13505;
dbms_output.put_line('应交税:'|| v_money);
else
null;
end if;
end;
2)
declare
v_year number :=6; --工作时间
v_money number;--奖金
begin
if v_year>=6 then
v_money :=2000;
dbms_output.put_line('奖金:'|| v_money);
elsif v_year<6 then
v_money :=1500;
dbms_output.put_line('奖金:'|| v_money);
else
null;
end if;
end;
3)
declare
v_rec employee%rowtype;
begin
select * into v_rec from employee where ename='张三' ;
if v_rec.sal>=700 and v_rec.sal<3200 then
dbms_output.put_line('部门'|| v_rec.deptno || '薪水:'|| v_rec.sal || '级别:1');
elsif v_rec.sal>=3201 and v_rec.sal<4400 then
dbms_output.put_line('部门'|| v_rec.deptno || '薪水:'|| v_rec.sal || '级别:2');
elsif v_rec.sal>=4401 and v_rec.sal<5000 then
dbms_output.put_line('部门'|| v_rec.deptno || '薪水:'|| v_rec.sal || '级别:3');
elsif v_rec.sal>=5001 and v_rec.sal<7000 then
dbms_output.put_line('部门'|| v_rec.deptno || '薪水:'|| v_rec.sal || '级别:4');
elsif v_rec.sal>=7001 and v_rec.sal<9999 then
dbms_output.put_line('部门'|| v_rec.deptno || '薪水:'|| v_rec.sal || '级别:5');
else
null;
end if;
end;
4)
declare
v_sal number :=1000;
begin
loop
v_sal :=v_sal+100;
exit when v_sal>10000;
dbms_output.put_line('工资:'|| v_sal);
end loop;
end;
--上机2
declare
v_ename varchar2(4);
begin
select ename into v_ename from employee where empno=2001;
dbms_output.put_line('名字:'||v_ename);
exception
when no_data_found then
dbms_output.put_line('不存在,查询结果为空!');
when value_error then
dbms_output.put_line('找到姓名,但长度超过变量长度!');
when others then
dbms_output.put_line('出现其他异常');
end;
--上机3
declare
-- v_sal employee.sal%type;--薪水
--v_eno employee.empno%type;--编号
v_rec employee%rowtype;
e_empno_is_null exception;--定义异常类型
begin
select * into v_rec from employee where empno='2001';
if v_rec.empno=10 then
if v_rec.sal<10000 then
update employee set sal=10000 where empno='2001';
else
dbms_output.put_line('工资不低于10000元');
end if;
end if;
exception
when no_data_found then
dbms_output.put_line('不存在,查询结果为空!');
when e_empno_is_null then
dbms_output.put_line('没有找到该编号!');
when others then
dbms_output.put_line('出现其他异常');
end;
--上机4
1)
declare
v_taxes constant number :=3500;--起征点
v_money number ;--税金
v_sum number :=0 ;--总额
v_sal employee.sal%type;
cursor emp_cursor is
select sal into v_sal from employee;
begin
open emp_cursor;
loop
fetch emp_cursor into v_sal;
exit when emp_cursor%notfound;
if v_sal-v_taxes<1500 then
v_money :=(v_sal-v_taxes)*0.03-0;
elsif v_taxes>=1500 and v_taxes<4500 then
v_money :=(v_sal-v_taxes)*0.1-105;
elsif v_taxes>=4500 and v_taxes<9000 then
v_money :=(v_sal-v_taxes)*0.2-555;
elsif v_taxes>=9000 and v_taxes<35000 then
v_money :=(v_sal-v_taxes)*0.25-1005;
elsif v_taxes>=35000 and v_taxes<55000 then
v_money :=(v_sal-v_taxes)*0.3-2755;
elsif v_taxes>=55000 and v_taxes<80000 then
v_money :=(v_sal-v_taxes)*0.35-5505;
elsif v_taxes>=v_sal then
v_money :=(8000-v_taxes)*0.45-13505;
else
null;
end if;
v_sum :=v_sum+v_money;
end loop;
close emp_cursor;
dbms_output.put_line('总额:'|| v_sum);
end;
2)
declare
v_money number(7,2) ;--奖金
cursor emp_cursor is
select hiredate from employee for update ;
begin
-- open emp_cursor;
for emp_record in emp_cursor
loop
if (sysdate-emp_record.hiredate)>=365*6 then
v_money:=2000;
else
v_money:=1500;
end if;
update employee set comm=v_money where current of emp_cursor;
end loop;
-- close emp_cursor;
end;
select * from employee
3)
declare
leve number(10);
cursor emp_cursor is
select e.ename ,d.dname,e.sal from employee e join dept d on e.deptno=d.deptno where e.ename='张三' ;
begin
for emp_record in emp_cursor loop
case
when emp_record.sal>=700 and emp_record.sal<=3200 then
leve:=1;
when emp_record.sal>=3201 and emp_record.sal<=4400 then
leve:=2;
when emp_record.sal>=4401 and emp_record.sal<=5000 then
leve:=3;
when emp_record.sal>=5001 and emp_record.sal<=7000 then
leve:=4;
when emp_record.sal>=7001 and emp_record.sal<=9999 then
leve:=5;
end case;
dbms_output.put_line('部门名称:'|| emp_record.dname || '薪水' || emp_record.sal || '级别:'|| leve);
end loop;
end;
select * from employee;
--上机5
1)
--创建存储过程
create or replace procedure QueryEmp
(v_empno in employee.empno%type,--输入参数,员工编号
on_Flag out number,--执行状态
os_Mgs out varchar2 --提示信息
)
is
begin
delete from employee where empno=v_empno;
on_Flag:=1;
os_Mgs:='删除成功';
exception
when no_data_found then
on_Flag:=-1;
os_Mgs:='员工不存在';
when others then
on_Flag:=-2;
os_Mgs:='其他错误,与管理员联系';
end;
--进行调用
declare
v_empno employee.empno%type;
on_Flag number(10);--执行状态
os_Mgs varchar2(100); --提示信息
begin
v_empno:=1006;
QueryEmp(v_empno,on_Flag,os_Mgs);
dbms_output.put_line(on_Flag || '' || os_Mgs);
end;
2)
--创建存储过程
create or replace procedure get_sals
(
cur_salary out sys_refcursor
)
as
begin
open cur_salary for
select empno,sal from employee;
end;
--调用存储过程
declare
v_eno varchar2(20);
v_sal number(8,2);
v_salary sys_refcursor;
begin
get_sals(v_salary );
loop
fetch v_salary into v_eno,v_sal;
if v_salary%found then
dbms_output.put_line( 'found');
end if;
if v_salary%notfound then
dbms_output.put_line( 'notfound');
end if;
exit when v_salary%notfound;
dbms_output.put_line('员工编号:' || v_eno || ',薪水:' || v_sal);
end loop;
close v_salary;
end;
--简答2
1)
declare
v_ename varchar2(4);
begin
select ename into v_ename
from employee
where empno=1005;
dbms_output.put_line('名字:'||v_ename);
exception
when no_data_found then
dbms_output.put_line('不存在,查询结果为空!');
when value_error then
dbms_output.put_line('找到姓名,但长度超过变量长度!');
when others then
dbms_output.put_line('出现其他异常');
end;
2)
declare
v_empno employee.empno%type;
begin
select empno into v_empno
from employee
where deptno=10;
dbms_output.put_line('编号:'||v_empno);
exception
when too_many_rows then
dbms_output.put_line('返回多行');
when no_data_found then
dbms_output.put_line('不存在,查询结果为空!');
when others then
dbms_output.put_line('出现其他异常');
end;
3)
select * from orders
declare
v_order_status orders.order_status%type;
begin
select order_status into v_order_status
from orders
where order_id=4;
if v_order_status=0 then
delete from orders where order_id=5;
else
dbms_output.put_line('该订单已确认,无法删除!');
end if;
exception
when no_data_found then
dbms_output.put_line('不存在,查询结果为空!');
when others then
dbms_output.put_line('出现其他异常');
end;
4)
declare
order_total orders.order_total%type;
cursor ord_cursor is
select order_total ,order_id
from orders;
begin
for ord_record in ord_cursor loop
if ord_record.order_total<50000 then
dbms_output.put_line('订单编号为'||ord_record.order_id||':需提高订单额');
else
dbms_output.put_line('订单编号为'||ord_record.order_id||':已达到订单额');
end if;
end loop;
end;
select * from orders
5)
--1.所得税
--创建存储过程
create or replace procedure reTaxes
(
v_eno in employee.empno%type,
cur_salary out sys_refcursor
)
is
begin
open cur_salary for
select empno,sal from employee where empno=v_eno;
end;
select * from employee
--调用存储过程
declare
v_eno employee.empno%type;--部门编号
v_sal employee.sal%type;--工资
v_salary sys_refcursor;
v_taxes number :=3500;--起征点
v_money number ;--应交税
begin
v_eno :=1005;
reRank(v_eno,v_salary,on_Flag,on_Mgs);
if v_salary%found then
dbms_output.put_line( 'found');
end if;
if v_salary%notfound then
dbms_output.put_line( 'notfound');
end if;
fetch v_salary into v_eno,v_sal;
if v_sal-v_taxes<1500 then
v_money :=(v_sal-v_taxes)*0.03-0;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=1500 and v_sal-v_taxes<4500 then
v_money :=(v_sal-v_taxes)*0.1-105;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=4500 and v_sal-v_taxes<9000 then
v_money :=(v_sal-v_taxes)*0.2-555;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=9000 and v_sal-v_taxes<35000 then
v_money :=(v_sal-v_taxes)*0.25-1005;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=35000 and v_sal-v_taxes<55000 then
v_money :=(v_sal-v_taxes)*0.3-2755;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=55000 and v_sal-v_taxes<80000 then
v_money :=(v_sal-v_taxes)*0.35-5505;
dbms_output.put_line('应交税:'|| v_money);
elsif v_sal-v_taxes>=80000 then
v_money :=(v_sal-v_taxes)*0.45-13505;
dbms_output.put_line('应交税:'|| v_money);
else
null;
end if;
end;
--2
drop procedure UpdEmps
create or replace procedure UpdEmps
(v_empno in employee.empno%type,--输入参数,员工编号
on_Flag out number,--执行状态
on_Mgs out varchar2 --提示信息
)
is
begin
update employee set comm = 1200 where empno=v_empno;
on_Flag:=1;
on_Mgs:='修改成功';
exception
when no_data_found then
on_Flag:=-1;
on_Mgs:='员工不存在';
when others then
on_Flag:=-2;
on_Mgs:='其他错误,与管理员联系';
end;
--进行调用
declare
v_empno employee.empno%type;
on_Flag number(10);--执行状态
on_Mgs varchar2(100); --提示信息
begin
v_empno:=11;
UpdEmps(v_empno,on_Flag,on_Mgs);
dbms_output.put_line(on_Flag || '' || on_Mgs);
end;
--3
--创建存储过程
drop procedure reRank
create or replace procedure reRank
(
v_eno in employee.empno%type,
cur_salary out sys_refcursor
)
is
begin
open cur_salary for
select empno,sal from employee where empno=v_eno;
end;
--调用存储过程
declare
v_eno varchar2(100);
v_sal number(8,2);
v_salary sys_refcursor;
begin
v_eno :=1002;
reRank(v_eno,v_salary);
fetch v_salary into v_eno,v_sal;
if v_salary%found then
dbms_output.put_line( 'found');
end if;
if v_salary%notfound then
dbms_output.put_line( 'notfound');
end if;
if v_sal>=700 and v_sal<3200 then
dbms_output.put_line('编号:'|| v_eno || '级别:1' );
elsif v_sal>=3201 and v_sal<4400 then
dbms_output.put_line('编号:'|| v_eno || '级别:2' );
elsif v_sal>=4401 and v_sal<5000 then
dbms_output.put_line('编号:'|| v_eno || '级别:3' );
elsif v_sal>=5001 and v_sal<7000 then
dbms_output.put_line('编号:'|| v_eno || '级别:4' );
elsif v_sal>=7001 and v_sal<9999 then
dbms_output.put_line('编号:'|| v_eno || '级别:5' );
else
null;
end if;
end;
--上机11)declare v_taxes number :=3500;--起征点 v_money number ;--应交税begin if v_sal-v_taxes<1500 then v_money :=(8000-v_taxes)*0.03-0; dbms_output.put_line('应交税:'|| v_money); ...