plsql编程语言

–pl/sql编程语言 –pl/sql编程语言是对sql语言的扩展，是的sql语言具有过程化编程的特性 –pl/sql编程语言比一般的过程化编程语言，更加灵活高效 –pl/sql编程语言主要用来编写存储过程和存储函数等。

--声明方法,定义变量 --赋值操作可以用 := 也可以使用 into 查询语句赋值 declare i number(2):=10; --数值型变量 s varchar2(10):='小明'; --字符型变量 ena emp.ename%type; --引用型变量，直接取出emp表中ename的类型给ena emprow emp%rowtype; --记录型变量，可以理解为可以存一行记录 begin dbms_output.put_line(i); --输出语句 dbms_output.put_line(s); select ename into ena from emp where empno=7788; dbms_output.put_line(ena); select * into emprow from emp where empno=7788; dbms_output.put_line(emprow.ename||'的工作为：'||emprow.job); end;

执行之后看输出结果：

--pl/sql中的if判断 --输入小于18的数字，输出未成年 --输入大于18小于40的数字，输出中年人 --输入大于40的数字，输出老年人 declare --number给个3位表示年龄够了，最大可以是999，输入的写法 & 后根据自己喜欢写个字母 i number(3):=ⅈ begin if i<18 then dbms_output.put_line('未成年'); elsif i<40 then dbms_output.put_line('中年人'); else dbms_output.put_line('老年人'); end if; end;

执行 结果

--pl/sql中的loop循环 --用三种方式输出1到10十个数字 --while循环 declare i number(2):=1; begin while i<11 loop dbms_output.put_line(i); i:=i+1; end loop; end; --exit循环 declare i number(2):=1; begin loop exit when i>10; dbms_output.put_line(i); i:=i+1; end loop; end; --for循环 declare begin for i in 1..10 loop dbms_output.put_line(i); end loop; end;

–游标

--游标：可以存放多个对象，多行记录 --输出emp表中的所有员工的姓名 declare cursor c1 is select * from emp; emprow emp%rowtype; begin open c1; loop fetch c1 into emprow; exit when c1%notfound; dbms_output.put_line(emprow.ename); end loop; close c1; end;

--给指定部门员工涨工资 --用到带参数的游标 declare cursor c2(eno emp.deptno%type) is select empno from emp where deptno=eno; en emp.empno%type; begin open c2(10); loop fetch c2 into en; exit when c2%notfound; update emp set sal=sal+100 where empno=en; commit; end loop; close c2; end;

执行前 执行后 存储过程

--存储过程 --存储过程：存储过程就是提前编译好的一段pl/sql语言，放置在数据库端 ---可以直接被调用。这一段pl/sql一般都是固定步骤的业务。 --给指定员工涨100块钱 create or replace procedure p1(eno emp.empno%type) is begin update emp set sal=sal+100 where empno=eno; commit; end;

--测试p1 declare begin p1(7788); end;

测试执行前： 测试执行后： 存储函数

---通过存储函数计算指定员工的年薪 --存储过程和存储函数的参数都不能带长度 --存储函数的返回值类型不能带长度 create or replace function f_yearsal(eno emp.empno%type) return number is s number(10); begin select sal*12+nvl(comm,0) into s from emp where empno=eno; return s; end;

---测试f_yearsal ---存储函数在调用的时候，返回值需要接收 declare s number(10); begin s:=f_yearsal(7788); dbms_output.put_line(s); end;

--out类型参数如何使用 ---使用存储过程来算年薪 create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number) is s number(10); c emp.comm%type; begin select sal*12,nvl(comm,0) into s,c from emp where empno=eno; yearsal:=s+c; end;

--测试 declare yearsal number(10); begin p_yearsal(7788,yearsal); dbms_output.put_line(yearsal); end;

in和out类型参数的区别是什么？ 凡是涉及到into查询语句复制或者 := 复制操作的参数，都必须用out来修饰。

–存储过程和存储函数的区别 –语法区别：关键字不一样 ----存储函数比存储过程多了两个return。 –本质区别：存储函数有返回值，而存储过程没有返回值。 —如果存储过程实现有返回值的业务，我们就必须使用out类型的参数 —即便是存储过程使用了out类型的参数，其本质也不是真的有了返回值 —而是在存储过程内部给out类型的参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。

--我们可以使用存储函数有返回值的特性，来自定义函数。 --而存储过程不能用来自定义函数。 --案例需求：查询出员工姓名，员工所在部门名称。 --案例准备工作：把scott用户下的dept表复制到当前用户下 create table dept as select * from scott.dept; --使用传统方式来实现需求 select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

--使用存储函数提供一个部门编号，输出一个部门名称 create or replace function fdna(dno dept.deptno%type) return dept.dname%type is dna dept.dname%type; begin select dname into dna from dept where deptno=dno; return dna; end;

--使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称。 select e.ename,fdna(e.deptno) from emp e;

触发器

--触发器，就是制定一个规则，在我们做增删改操作得时候 --只需要满足该规则，就自动触发，无须调用 ---语句级触发器：不包含for each row的触发器 ---行级触发器：包含for each row的及时行级触发器 ----加for each row是为了使用 :old 或者 :new 对象或者一行记录 ---插入一条记录，输出一个新员工入职 create or replace trigger t1 after insert on person declare begin dbms_output.put_line('一个新员工入职'); end;

一下面的表为例：

--触发t1 insert into person values(1,'小红'); commit;

再看表数据：

--行级触发器 --不能给员工降薪 ---raise_application_error(-20001~-20999之间,'错误信息'); create or replace trigger t2 before update on emp for each row declare begin if :old.sal>:new.sal then raise_application_error(-20001,'不能给员工降薪'); end if; end; ---触发t2 update emp set sal=sal-1 where empno=7788; commit;

工资没变：

--触发器实现主键自增【行级触发器】 --分析：在用户做插入操作之前，拿到即将插入的数据 ---给给该数据中的主键列赋值。 create or replace trigger auid before insert on person for each row declare begin select s_person.nextval into :new.pid from dual; end;

--使用auid实现主键自增 insert into person (pname) values ('a'); commit;

insert into person values(1,'b'); commit;