--PL/SQL语言(procedure language 过程化语言)
--1.声明类型
declare
k number;
m number default 20;
--Character String buffer too small问题
--pname varchar2(4);
--所以更换声明
pname emp.ename%type;
--查询一行用表名声明
prow emp%rowtype;
begin
k:=30;
dbms_output.put_line(k);
dbms_output.put_line('原样输出 m='||m);
select ename into pname from emp where empno=7788;
dbms_output.put_line(pname);
--查询结果是一行值
select * into prow from emp where empno=7654;
dbms_output.put_line
(prow.empno||'-'||prow.ename||'-'||prow.job);
end;
--2.1 结构化判断语句
declare
k number;
begin
k:=&这里可以让你输入;
if k>0 then
dbms_output.put_line(' k是正数'||k);
elsif k<0 then
dbms_output.put_line(' k是负数'||k);
else
dbms_output.put_line(' k是零'||k);
end if;
end;
--2.2.1 结构化循环语句 loop输出1-10
declare
k number default 1;
begin
loop
dbms_output.put_line(k);
exit when k=10;
k:=k+1;
end loop;
end;
--2.2.2 结构化循环语句 while输出1-10
declare
k number default 1;
begin
while k<=10 loop
dbms_output.put_line(k);
k:=k+1;
end loop;
end;
--2.2.3 结构化循环语句 for输出1-10
--1..10 是集合 可称为游标
declare
k number default 1;
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
end;
--2.2.4 结构化循环语句 for输出1-10
-- 使用游标打印20号部门的员工姓名和工作 方法一
declare
cursor cur is select ename,job from emp where deptno=20;
begin
for k in cur loop
dbms_output.put_line(k.ename||'-'||k.job);
end loop;
end;
-- 使用游标打印20号部门的员工姓名和工作 方法二
declare
pname emp.ename%type;
pjob emp.job%type;
cursor cur is select ename,job from emp where deptno=20;
begin
open cur;
loop
fetch cur into pname,pjob;
exit when cur%notfound;
dbms_output.put_line(pname||'-'||pjob);
end loop;
close cur;
end;
-- 使用游标对20号部门的员工涨工资
declare
k number;
cursor cur is select empno from emp where deptno=20;
begin
for k in cur loop
update emp set sal=sal+100 where empno=k.empno;
end loop;
end;
select * from emp;
-- 例外(基本异常)
declare
pname emp.ename%type;
m number;
begin
m:='abc';
select ename into pname from emp where deptno=20;
dbms_output.put_line(pname);
select ename into pname from emp where deptno=40;
dbms_output.put_line(pname);
exception
when no_data_found then
dbms_output.put_line('没有记录');
when too_many_rows then
dbms_output.put_line('太多记录');
when value_error then
dbms_output.put_line('类型转换异常');
when others then
dbms_output.put_line('其他异常');
end;
-- 例外(自定义异常)
declare
not_found exception;
pname emp.ename%type;
cursor cur is select ename from emp where deptno=40;
begin
open cur;
fetch cur into pname;
if cur%notfound then
raise not_found;
end if;
close cur;
exception
when not_found then
dbms_output.put_line('游标中没发现记录');
when no_data_found then
dbms_output.put_line('没有记录');
when too_many_rows then
dbms_output.put_line('太多记录');
when value_error then
dbms_output.put_line('类型转换异常');
when others then
dbms_output.put_line('其他异常');
end;
-- 3 存储过程(感觉包含了PL/SQL所有)
-- 封装了一组SQL语句,提前编译号,放在服务器端,等待调用
--3.1 根据员工编号得到员工的年薪
create or replace procedure getYearSal(eno in number,
yearsal out number)
as --声明变量
begin --过程化语句
select sal*12+nvl(comm,0) into yearsal from emp
where empno=eno;
end;
--访问单值输出的存储过程
declare
yearsal number;
begin
getYearSal(7499,yearsal);
dbms_output.put_line('年薪'||yearsal);
end;
--3.2 给某员工涨工资(打印涨前和涨后工资)
create or replace procedure updateSal(eno in number,
plussal in number)
is --声明变量
oldsal number;
newsal number;
begin --过程化语句
--涨前
select sal into oldsal from emp where empno=eno;
dbms_output.put_line('涨前的工资:'||oldsal);
--涨工资
update emp set sal=sal+plussal where empno=eno;
commit;
--涨后
select sal into newsal from emp where empno=eno;
dbms_output.put_line('涨后的工资:'||newsal);
end;
--访问存储过程
--方法一
declare
begin
updateSal(7499,888.88);
end;
--方法二 访问只有输入的存储过程也可以使用call
call updateSal(7499,888.88);
--3.3 得到某部门所有员工的信息
create or replace procedure getEmps(dno in number,
emps out sys_refcursor)
is --声明变量
begin --过程化语句
open emps for select * from emp where deptno=dno;
end;
--访问存储过程
-- 访问输出参数为游标的存储过程
declare
emps sys_refcursor;
prow emp%rowtype;
begin
getEmps(20,emps);
--打开游标在存储过程中了
loop
fetch emps into prow;
exit when emps%notfound;
dbms_output.put_line(prow.empno||'-'||prow.job||'-'||prow.sal||'-'||prow.ename);
end loop;
close emps;
end;
--对比普通游标的使用,不能使用for循环
declare
cursor emps is select * from emp where deptno=20;
k number;
begin
for k in emps loop
dbms_output.put_line(k.empno||'-'||k.job||'-'||k.sal||'-'||k.ename);
end loop;
end;
-- 4 存储函数(感觉包含了PL/SQL所有)
-- 必须有return 可以直接用在select查询中 一般不用out输出参数
--4.1 根据员工编号得到员工的年薪
create or replace function x(eno in number) return number
as
psal emp.sal%type;
begin
select sal into psal from emp where empno=eno;
return psal;
end;
--访问单值输出的存储函数
declare
yearsal number;
begin
yearsal := x(7499);
dbms_output.put_line('年薪'||yearsal);
end;
--存储函数用于select语句中
select x(7788) from dual;
-- 5 触发器 -- 星期三不能插入数据
create or replace trigger notInsertPerson
before
insert
on emp
for each row
declare
day number;
begin
select to_char(sysdate,'d') into day from dual;
if trim(day)=('') then
raise_application_error(-20003,'不能在周四办理入职');
end if;
end;
select to_char(sysdate,'d') from dual;
insert into emp (empno,ename) VALUES('','joke');
/*综合练习:每一位雇员都要根据其收入上缴所得税，假设所得税的上缴原则为：
--2000以下上缴3%、 --2000 ~ 5000上缴8%、5000以上上缴10%，现在要求建立一张新的数据表，
--可以记录出雇员的编号、姓名、工资、佣金、上缴所得税数据，
--并且在每次修改雇员表中sal和comm字段后可以自动更新记录。*/
create or replace function tax(eno in number) return number
as
psal emp.sal%type;
tax number;
begin
select sal+nvl(comm,0) into psal from emp where empno=eno;
if psal<2000 then
tax:=psal*0.03;
elsif psal>=2000 and psal<5000 then
tax:=psal*0.08;
elsif psal>=5000 then
tax:=psal*0.1;
else
tax:=0;
end if;
return tax;
end;
declare
ptax number;
begin
ptax :=tax(7788);
dbms_output.put_line(ptax);
end;
--分配创建视图的权限给scott
grant create view to scott;
--创建一张新的视图表(包含税)
create view newemp as select empno,ename,sal,comm,(select tax(empno) from dual) tax from emp;
select * from newemp;
--设置触发器
create or replace trigger autocommit
after
update
on emp
for each row
declare
pragma autonomous_transaction;
begin
--有待补充
commit;
end;
update emp set sal=sal+33.33 where empno=7788;
select * from emp;
