PL/SQL

2021-12-20 大约 7 分钟

# PL/SQL

--基础概念:数据库,实例同用户和表空间为一对多,用户和表空间的关系为多对一
--中文乱码解决,
select userenv('language') from dual;--查询服务器编码
select * from V$NLS_PARAMETERS  --查询plsql developer编码

--创建表空间
create tablespace myspace
datafile 'D:\Oracle\mydata\myfiles.dbf'
size 100m
autoextend on
next 10m;


--删除表空间
drop tablespace myspcae;


--创建用户
create user cai
identified by cai --cai的密码
default tablespace myspace;--指定用户出生的表空间

--给用户授权
--数据库中常用角色
connect --连接角色
resource --开发者角色
dba  --超级管理员角色
grant dba to cai;


--常用数据类型
varchar2 --可变字符串,可以自动截取
number(4) --四位数
NUMBER(4,2) --11.11
data --日期
clob --Character Large Object文本数据类型,可存4g
blob -- Binary Large Object二进制数据类型,4g


--创建表
create table person(
       pid number(30),
       pname varchar2(30)
);


--修改表结构
--添加表结构
alter table person add (geder number(1));
--修改列类型
alter table person modify gender char(1); --char为定长
--修改列名称
alter table person rename column gender to sex;
--删除列名称
alter table person drop column sex;


--数据增删改
--添加一条记录
insert into person(pid,pname)values(1,'小明');
commit;--增删改需要手动提交
--修改一条记录
update person set pname = '小马' where pid = 1;
commit;

--删除
--删除表中全部记录
delete from person;
--删除表结构
drop table person;
--先删除表,再创建表
--在数据量大或表中带有索引的情况下,该操作会先删索引,效率高
truncate table person;

--序列不属于表,但可以和表做逻辑绑定
--dual:虚表,只为补全语法
create sequence s_person;
select s_person.nextval from dual;
select s_person.currval from dual;
--修改一条记录
insert into person(pid,pname)values(s_person.currval,'小明');


--scott用户,密码tiger
--解锁scott用户
alter user scott account unlock;
--解锁密码【也可以重置密码】
alter user scott identified by tiger;
--切换到scott


--查询数据

--单行函数;作用于一行,返回一个值
--字符函数
select upper('yes') from dual;
select lower('Yes') from dual;
--数组函数
select round(56.16, -2) from dual; --四舍五入
select trunc(56.16, -2) from dual; --直接截取
--日期函数
select sysdate-e.hiredate from emp e;
select months_between(sysdate,e.hiredate) from emp e;
--转换函数
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual;--日期转字符串
select to_date('2020-11-16 01:22:09','yyyy-mm-dd hh:mi:ss')from dual;--日期转字符串
--通用函数
select e.sal*12+nvl(e.comm,0)from emp e; --如果不为null,则为第二个参数值


--条件表达式(通用)
select e.ename,--等值判断
       case e.ename
            when 'SMITH' then 'aa'
            when 'ALLEN' then 'bb'
            else 'cc'
        end
from emp e;

--范围判断
select e.sal,--等值判断
       case
            when e.sal>3000 then 'aa'
            when e.sal>1500 then 'bb'
            else 'cc'
        end
from emp e; 

--orcale专用条件表达式
select e.ename,--等值判断
       decode(e.ename,
            'SMITH' , 'aa',
            'ALLEN' , 'bb',
            'cc') 中文名
from emp e;


--多行函数【聚合函数】,作用于多行,返回 一个值、
select sum(1) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;


--分组查询:group by后面的原始列或者聚合函数才能出现在select后面
--查询每个部门工资高于800的平均工资再查询平均工资高于2000的部门
select e.deptno,avg(e.sal)a --,e.ename
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;--条件不能使用别名having where条件优先级大于select
--where过滤分组前数据,having过滤分组后的数据


--多表查询
--笛卡尔积
select * 
from emp e,dept d
--等值连接
select * 
from emp e,dept d
where e.deptno=d.deptno; 
--内连接
select *
from emp e inner join dept d
on e.deptno=d.deptno;
--左右外连接
select *
from emp e right dept d
on e.deptno=d.deptno;
--oracle专用
select * 
from emp e,dept d
where e.deptno=d.deptno(+);--左连接


--自连接
select e1.ename, e2.ename--查询出员工姓名,员工领导姓名
from emp e1,emp e2
where e1.mgr= e2.empno


--子查询
--返回一个值,集合
select * from emp where sal in
(select *sal from emp where ename = 'SCOTT')
--返回一张表
--三表连查询
select t.deptno,t.msal,e.ename,d.dname
from (select deptno ,min(sal) msal
      from emp
      group by deptno)t,emp e,dept d
where t.deptno=e.deptno
and t.msal = e.sal
and e.deptno = d.deptno;


--分页
--rownum:select之后会在结果上加行号order优先级低于select
--涉及排序可以嵌套查询
select rownum,t.* from(
select rownum,e.* from emp e order by e.sal desc) t;

--rownum行号不能写大于一个正数
select * from(
       select rownum rn,e.* from(
              select * from emp order by sal desc
       ) e where rownum<11
)where rn >5;


--视图:提供一个查询的窗口,所有数据来自原表
--创建表
create table emp as select * from scott.emp;
--创建只读视图【必须有dba权限】
create view v_emp as select ename,job from emp with read only;
select * from v_emp;
--视图的作用:1.屏蔽敏感字段。2.保证总部和分部数据的统一


--索引:在表的列上构建二叉树,提高查询效率,但会影响增删改的效率
--单列索引,必须是原始值才能触发,模糊查询,单行函数
create index idx_ename on emp(ename);
--复核索引,第一列为优先检索列,优先检索列中必须包含原始值
create index idx_enamejob on emp(ename,job);

select * from emp where enmae='SCOTT' and job='xxx';--触发复合索引
select * from emp where enmae='SCOTT' or job='xxx';--不触发索引
select * from emp where enmae='SCOTT' --触发单列索引

--qlsql编程
--变量声明
declare 
     i number(2) :=10;
     s varchar2(10) :='小妹';
     ena emp.ename%type;--引用型变量
     enprow emp%rowtype; --记录型变量
begin 
     dbms_output.put_line(i);
     select ename into ena from emp where empno = 7788;--对ena初始化
     select * into emprow from emp where empno = 7788;
     dbms_output.put_line('你好'||'小妹');
end;

--if分支
declare
     i number(3) :=&ii;
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;

--loop循环
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;


--游标:存放多行记录
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;



--存储过程:提前编译好的sql语句集
create or replace procedure p1(eno emp.empno%type)
is
begin
  update emp set sal = sal + 100 where empno = eno;
  commit;
end; 


--存储函数:
create or replace function f_yersal(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;

--out类型参数,涉及到赋值操作的参数必须用out,没有涉及的必须用in
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;
--存储过程和存储函数的区别:函数可以有一个返回值;而过程没有返回值。
--但过程和函数都可以通过 out 指定一个或多个输出参数。在过程和函数中实现返回多个值。


--触发器:制定一个规则,在增删改时如果符合规则则触发
--语句级触发器:在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行
create or replace trigger t1
after 
insert 
on emp
declare

begin
  dbms_output.put_line('入职');
end; 

--行级触发器:触发语句作用的每一条记录都被触发。在行级触发器中使用 old 和 new 伪记录变量, 识别值的状态
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; 


--触发器实现主键自增
create or replace trigger auid
before 
insert 
on person
for each row
declare

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

insert into person (pname) values('a');
commit;
insert into person  values(1,'b');--有也不用这个id
commit;
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
上次编辑于: 2021年12月20日 15:58