pl/sql编程语言

–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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

执行之后看输出结果：

--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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

执行

结果

--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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

–游标

--游标：可以存放多个对象，多行记录

--输出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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

--给指定部门员工涨工资

--用到带参数的游标

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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

执行前

执行后

存储过程

--存储过程

--存储过程：存储过程就是提前编译好的一段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;

1

2

3

4

5

6

7

8

9

10

11

--测试p1

declare

begin

p1(7788);

end;

1

2

3

4

5

6

测试执行前：

测试执行后：

存储函数

---通过存储函数计算指定员工的年薪

--存储过程和存储函数的参数都不能带长度

--存储函数的返回值类型不能带长度

create or replace function f_yearsal(eno emp.empno%type) return numbe

is

s number(10);

begin

select sal*12+nvl(comm,0) into s from emp where empno=eno;

return s;

end;

1

2

3

4

5

6

7

8

9

10

11

---测试f_yearsal

---存储函数在调用的时候，返回值需要接收

declare

s number(10);

begin

s:=f_yearsal(7788);

dbms_output.put_line(s);

end;

1

2

3

4

5

6

7

8

--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;

1

2

3

4

5

6

7

8

9

10

--测试

declare

yearsal number(10);

begin

p_yearsal(7788,yearsal);

dbms_output.put_line(yearsal);

end;

1

2

3

4

5

6

7

in和out类型参数的区别是什么？

凡是涉及到into查询语句复制或者 := 复制操作的参数，都必须用out来修饰。

–存储过程和存储函数的区别

–语法区别：关键字不一样

----存储函数比存储过程多了两个return。

–本质区别：存储函数有返回值，而存储过程没有返回值。

—如果存储过程实现有返回值的业务，我们就必须使用out类型的参数

—即便是存储过程使用了out类型的参数，其本质也不是真的有了返回值

—而是在存储过程内部给out类型的参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。

--我们可以使用存储函数有返回值的特性，来自定义函数。

--而存储过程不能用来自定义函数。

--案例需求：查询出员工姓名，员工所在部门名称。

--案例准备工作：把scott用户下的dept表复制到当前用户下

create table dept as select * from scott.dept;

1

2

3

4

5

--使用传统方式来实现需求

select e.ename,d.dname

from emp e,dept d

where e.deptno=d.deptno;

1

2

3

4

5

--使用存储函数提供一个部门编号，输出一个部门名称

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;

1

2

3

4

5

6

7

8

--使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称。

select e.ename,fdna(e.deptno)

from emp e;

1

2

3

触发器

--触发器，就是制定一个规则，在我们做增删改操作得时候

--只需要满足该规则，就自动触发，无须调用

---语句级触发器：不包含for each row的触发器

---行级触发器：包含for each row的及时行级触发器

----加for each row是为了使用 :old 或者 :new 对象或者一行记录

---插入一条记录，输出一个新员工入职

create or replace trigger t1

afte

insert

on person

declare

begin

dbms_output.put_line('一个新员工入职');

end;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

一下面的表为例：

--触发t1

insert into person values(1,'小红');

commit;

1

2

3

再看表数据：

--行级触发器

--不能给员工降薪

---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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

---触发t2

update emp set sal=sal-1 where empno=7788;

commit;

1

2

3

工资没变：

--触发器实现主键自增【行级触发器】

--分析：在用户做插入操作之前，拿到即将插入的数据

---给给该数据中的主键列赋值。

create or replace trigger auid

before

insert

on person

for each row

declare

begin

select s_person.nextval into :new.pid from dual;

end;

1

2

3

4

5

6

7

8

9

10

11

12

13

--使用auid实现主键自增

insert into person (pname) values ('a');

commit;

1

2

3

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

commit;

1

2

---------------------

作者：HHRunning

来源：CSDN

原文：https://blog.csdn.net/qq_34721292/article/details/90579498

版权声明：本文为博主原创文章，转载请附上博文链接！