学习oracle笔记PLSQL.docx
- 文档编号:18506064
- 上传时间:2023-08-18
- 格式:DOCX
- 页数:26
- 大小:24.55KB
学习oracle笔记PLSQL.docx
《学习oracle笔记PLSQL.docx》由会员分享,可在线阅读,更多相关《学习oracle笔记PLSQL.docx(26页珍藏版)》请在冰点文库上搜索。
学习oracle笔记PLSQL
学习oracle笔记
一、PLSQL概念2
1.过程化语言:
在oracle中编写存储过程、函数、触发器等。
2
2.特点2
3.学习的必要性:
2
二、开发工具介绍2
1.Sqlplus2
2.PL/SQLdeveloper3
三、PL/SQL基础3
1.介绍:
3
2.PL/SQL可以做什么3
3.编写规范3
4.块(block)3
5.过程5
6.函数5
7.包6
8.变量类型6
四、Pl/sql进阶(控制语句、存储过程、例外)10
1.控制结构(条件语句、循环语句、顺序控制结构…)10
2.存储过程(编写分页过程)13
3.例外处理16
五、Oracle视图20
1.概念:
20
2.视图的必要性20
3.视图和表的区别21
4.创建视图21
5.视图可以当作表一样进行joinon联合查询。
21
一、PLSQL概念
1.过程化语言:
在oracle中编写存储过程、函数、触发器等。
扩展oracle数据库的编程性能;
减轻前端程序的复杂性;
增强数据传输的安全性等等
2.特点
1)过程、函数、触发器是由PLSQL编写的
2)过程、函数、触发器是写在oracle中
3)PLSQL是非常强大的数据库过程语言
4)过程、函数可以在java程序中调用
3.学习的必要性:
1)提高应用程序的运行性能(减少翻译语句步骤)
2)模块化的设计思想(分页存储过程、复杂查询过程)
3)减少网络传输量(传输语句减少,直接调用存储过程语句即可)
4)提高安全性(传输语句直接调用存储过程名,看不到表名等,具体语句封装在oracle里,多了一层安全性)
5)移植性不好(语句写在oracle里,换数据库则得重写)
二、开发工具介绍
1.Sqlplus
举例:
编写一个存储过程,该过程可以向某表中添加记录
1)创建一张表
Createtablemytable(namevarchar2(20),passwdvarchar2(6));
2)创建存储过程:
往mytable表里添加数据
Create(orreplace)proceduremypro1is
Begin
--执行部分
Insertintomytablevalues(‘韩顺平’,’pw1234’);
End;
回车,输入/,再回车,如果有错误,可以SQL>showerror查看错误
3)如何调用该过程
●Exec过程名(参数值1,参数值2)
●Call过程名(参数值1,参数值2)
2.PL/SQLdeveloper
举例:
编写一个存储过程,该过程可以删除某张表的记录
1)Create(orreplace)proceduremypro2is
Begin
Deletefrommytable;
End;
2)Execmypro2;
三、PL/SQL基础
1.介绍:
需掌握SQL语句、PL/SQL语句语法规则
可以使用变量、逻辑控制语句
可以轻松完成复杂的查询
可以编写分页存储过程模块、订单处理存储过程模块等
2.PL/SQL可以做什么
1)简单分类:
块(最小单元,衍生出):
●存储过程
●函数
●包
●触发器
3.编写规范
1)注释:
a)单行注释:
--
b)多行注释:
/*.....*/
2)标识符号的命名规范
a)当定义变量时,建议用v_作为前缀,v_sal
b)当定义常量时,建议用c_作为前缀,c_rate
c)当定义游标时,建议用_cursor作为后缀,emp_cursor
d)当定义例外时,建议用e_作为前缀,e_error
4.块(block):
PLSQL执行的最小单元,嵌在存储过程、函数等等之中。
1)块是PL/SQL的基本程序单元,编写PL/SQL程序就是编写块,PL/SQL块中可以嵌套其他的PL/SQL块,可以实现复杂的功能
2)块的结构:
a)由三个部分构成:
定义部分、执行部分、例外处理部分
b)Declear--定义部分(该部分可选)
/*定义常量、变量、游标、例外、复杂数据类型*/
c)Begin--执行部分(该部分必须)
/*执行PL/SQL语句和SQL语句*/
d)Exception--例外处理部分(该部分可选)
/*处理各种运行的错误*/
3)实例1(最简单的块:
只有执行部分)
(要先执行Setserveroutputon/off–打开/关闭输出选项,才能输出)
Begin
Dbms_output.put_line(‘hello’);--输出hello
End;
/,再回车,即可输出hello
--dbms_output是oracle提供的包,put_line是该包中的过程
4)实例2(有定义个执行部分的块)
Declare
V_enamevarchar2(5);--定义字符串变量
Begin
SelectenameintoV_enamefromempwhereempno=&no;--执行部分
Dbms_output.put_line(‘雇员名:
’||v_ename);--输出部分
End;
/回车
--&表示要接收从控制台输入的变量
实例:
declare
v_enamevarchar2(5);
v_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromempwhereempno=&no;
dbms_output.put_line(’雇员名:
’||v_ename||’,’||'工资:
'||v_sal);
end;
/回车
5)实例3(有定义、执行和例外部分的块)
declare
v_enamevarchar2(5);
v_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromempwhereempno=&no;
dbms_output.put_line(’雇员名:
’||v_ename||’,’||'工资:
'||v_sal);
exception
whenno_data_foundthen
dbms_output.put_line(’编号有误,请重新输入’);
end;
/回车
5.存储过程
实例4(要求输入用户名和新的工资,即可修改该用户的工资)
Createproceduresp_pro4(spnamevarchar2,newsalnumber)is
Begin
Updateempsetsal=newsalwhwereename=spname;
End;
/回车
然后调用该过程即可实现修改工资的功能。
Java程序调用过程的方法
6.函数
实例5:
输入雇员的姓名,返回该雇员的年薪
createfunctionsp_fun1(spnamevarchar2)returnnumberisyearsalnumber(7,2);
begin
selectsal*12+nvl(comm,0)*12intoyearsalfromempwhereename=spname;
returnyearsal;
end;
/回车
调用函数:
var年薪number;
callsp_fun1(’SCOTT’)into:
年薪;
7.包
--创建一个包(包名sp_pack1)
--声明该包有一个过程update_sal
--声明该包有一个函数annual_income
createpackagesp_pack1is
procedureupdate_sal(namevarchar2,newsalnumber);
functionannual_income(namevarchar2)returnnumber;
end;
--给包(sp_pack1)实现包体
createpackagebodysp_pack1is
procedureupdate_sal(namevarchar2,newsalnumber)
is
begin
updateempsetsal=newsalwhereename=name;
end;
functionannual_income(namevarchar2)
returnnumberis
annual_salarynumber;
begin
selectsal*12+nvl(comm,0)intoannual_salaryfromempwhereename=name;
returnannual_salary;
end;
end;
--调用包中的过程或函数:
execsp_pack1.update_sal('SCOTT',4000);
8.变量类型
●标量类型(scalar)
●复合类型(composite)
●参照类型(reference)
●Lob(largeobject)
1)标量,用于存放单个数据的变量,是最简单的变量
定义标量类型案例
a)定义一个可变长字符串
V_namevarchar2(10);
b)定义一个小数范围-9999.99~9999.99
V_salnumber(6,2);
c)定义一个小数并给一个初始值为5.4:
=是pl/sql的赋值号
V_sal2number(6,2):
=5.4;
d)定义一个日期类型的数据
V_hiredatedate;
e)定义一个布尔变量,不能为空,初始值为false
V_validBooleannotnulldefaultfalse;
使用标量案例
输入员工号,显示雇员姓名、工资、个人所得税(税率为0.03)
declare
c_tax_ratenumber(3,2):
=0.03;
v_enamevarchar2(5);
v_salnumber(7,2);
v_tax_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromempwhereempno=&no;
v_tax_sal:
=v_sal*c_tax_rate;
dbms_output.put_line('姓名:
'||v_ename||'工资:
'||v_sal||'所得税:
'||v_tax_sal);
end;
/回车
为防止定义字符串的长度不够用,可以用%type来定义:
declare
c_tax_ratenumber(3,2):
=0.03;
v_enameemp.ename%type;
v_salnumber(7,2);
v_tax_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromempwhereempno=&no;
v_tax_sal:
=v_sal*c_tax_rate;
dbms_output.put_line('姓名:
'||v_ename||'工资:
'||v_sal||'所得税:
'||v_tax_sal);
end;
/回车
2)复合变量,用于存放多个值的变量,主要包括:
●Pl/sql记录
●Pl/sql表
●嵌套表
●varray
案例:
a)Pl/sql记录,类似高级语言中的结构体(类),引用记录成员时必须有前缀:
(记录变量.记录成员)
--pl/sql记录实例
declare
--定义一个pl/sql记录类型emp_record_type,类型包含三个数据name,salary,title
typeemp_record_typeisrecord(nameemp.ename%type,salaryemp.sal%type,titleemp.job%type);
sp_recordemp_record_type;
begin
selectename,sal,jobintosp_recordfromempwhereempno=&no;
dbms_output.put_line('员工名:
'||sp_record.name||'工资:
'||sp_record.salary);
end;
/回车
b)Pl/sql表,相当于高级语言中的数组,pl/sql表的下标可以为负数,并且没有限制。
--pl/sql表实例
declare
--定义了一个pl/sql表的类型sp_table_type,该类型是用于emp.ename%type
--indexbybinary_integer表示下标是整数
typesp_table_typeistableofemp.ename%typeindexbybinary_integer;
--定义了一个sp_table变量,这个变量的类型是sp_table_type
sp_tablesp_table_type;
begin
selectenameintosp_table
(1)fromempwhereempno=&no;
dbms_output.put_line('员工名:
'||sp_table
(1));
end;
/回车
c)嵌套表
d)Varray
3)参照变量(常用游标变量(refcursor)和对象类型变量(refobj_type))
是指用于存放数值指针的变量,使用参照变量可以使得应用程序共享相同对象,从而降低占用空间,pl/sql常用游标变量(refcursor)和对象类型变量(refobj_type)这两种参照变量。
a)ref_cursor游标变量:
当定义游标时不需要指定相应的select语句,但当使用游标时(open时)需要指定select语句,这样一个游标就与一个select语句结合了。
实例:
1.请使用plsql编写一个块,可以输入部门号,并显示该部门所有员工姓名和工资;2.在1的基础上,如果某个员工工资低于200,就增加100元。
declare
--定义游标类型sp_emp_cursor
typesp_emp_cursorisrefcursor;
--定义一个游标变量
test_cursorsp_emp_cursor;
--定义变量
v_enameemp.ename%type;
v_salemp.sal%type;
begin
--把test_cursor和一个select结合
opentest_cursorforselectename,salfromempwheredeptno=&no;
--循环取出
loop
fetchtest_cursorintov_ename,v_sal;
--判断test_cursor是否为空
exitwhentest_cursor%notfound;
dbms_output.put_line('名字:
'||v_ename||','||'工资:
'||v_sal);
endloop;
--关闭游标
closetest_cursor;
end;
四、Pl/sql进阶(控制语句、存储过程、例外)
1.控制结构(条件语句、循环语句、顺序控制结构…)
a)条件语句:
共三种条件分支语句if-then,if-then-else,if-then-elsif-else
●If-then实例:
编写一个过程,输入一个雇员名,如果该雇员工资低于2000,就给该雇员工资增加10%。
createproceduresp_pro6(spnamevarchar2)is
v_salemp.sal%type;
begin
selectsalintov_salfromempwhereename=spname;
ifv_sal<2000then
updateempsetsal=sal*1.1whereename=spname;
endif;
end;
/回车
Execsp_pro6();执行调用该过程即可实现该需求。
●If-then-else二重条件分支实例
编写一个过程,可以输入一个雇员名,如果该雇员的补助不是0,那么就在原来的基础上增加100;如果补助为0,那么就把补助设为200。
createproceduresp_pro7(sp_namevarchar2)is
v_commm%type;
begin
selectcommintov_commfromempwhereename=sp_name;
ifv_comm<>0then
updateempsetcomm=comm+100whereename=sp_name;
else
updateempsetcomm=comm+200whereename=sp_name;
endif;
end;
●If-then-elsif-else多重分支实例
编写一个过程,要求,输入一个雇员编号,如果该雇员的职位是president就给他的工资增加1000;如果该雇员的职位是manager就给他的工资增加500;如果是其他职位就增加200。
createproceduresp_pro8(sp_nonumber)is
v_jobemp.job%type;
begin
selectjobintov_jobfromempwhereempno=sp_no;
ifv_job='PRESIDENT'then
updateempsetsal=sal+1000whereempno=sp_no;
elsifv_job='MANAGER'then
updateempsetsal=sal+500whereempno=sp_no;
else
updateempsetsal=sal+200whereempno=sp_no;
endif;
end;
b)循环语句-loop
●Loop循环:
最简单的循环,以loop开头,endloop结尾,至少循环一次,先执行后判断。
Loop循环实例
编写一个过程,可输入用户名,并循环添加10个用户到test_user表中,用户编号从1开始增加。
(test_user表有id、name两个字段)
createproceduresp_pro9(spnamevarchar2)is
v_numnumber:
=1;
begin
loop
insertintotest_uservalues(v_num,spname);
exitwhenv_num=10;
v_num:
=v_num+1;
endloop;
end;
●While循环:
先判断后执行,若判断条件为true,则执行。
以while…loop开始,以endloop结束。
实例:
编写一个过程,输入用户名,即可循环添加10个用户到test_user表中,编号从11开始逐一递增。
●For循环(不建议用)
begin
foriinreverse1..10loop
insertintotest_uservalues(i,'²âÊÔÃû×Ö3');
endloop;
end;
c)顺序控制结构:
goto和null
●Goto语句(建议不要使用):
用于跳转到特定标号去执行语句,由于使用goto语句会增加程序的复杂性,并使得应用程序可读性变差,所以在做一般应用开发时不建议使用。
语法:
gotolablc,其中lablc是已经定义好的标号名
declare
iint:
=1;
begin
loop
dbms_output.put_line('Êä³ö='||i);
ifi=10then
gotoend_loop;
endif;
i:
=i+1;
endloop;
<
dbms_output.put_line('Ñ»·½áÊø');
end;
/回车
●Null语句:
null语句不会执行任何操作,而是会直接将控制语句传递到下一条语句,其好处是可提高pl/sql的可读性。
declare
v_enameemp.ename%type;
v_salemp.sal%type;
begin
selectename,salintov_ename,v_sal
fromempwhereempno=&no;
ifv_sal<3000then
updateempsetcomm=sal*0.1whereename=v_ename;
else
null;
endif;
end;
2.存储过程(编写分页过程)
a)掌握变量、掌握控制语句后学习分页
b)无返回值存储过程。
实例1:
有一张book表,内有书号、书名、出版社三个字段,要求编写一个过程,可以向book表添加书,要求通过Java程序调用该过程。
【
--创建book表
createtablebook
(bookidnumber,booknamevarchar2(20),
pubhousevarchar2(20));
】
【
--创建过程(in表示一个输入的参数,out表示一个输出的参数)
createproceduresp_pro7
(spbookidinnumber,
spbooknameinvarchar2,
sppubhouseinvarchar2)is
begin
insertintobookvalues(spbookid,spbookname,sppubhouse);
end;
--调用过程
execsp_pro7
(001,
'java教程',
'新华出版社');
--编写Java调用过程。
。
。
】
c)有返回值的存储过程(单个返回值或多个返回值)
输入一个雇员的编号,返回该雇员的姓名、岗位、工资
createproceduresp_pro8
(spnoinnumber,
spnameoutvarchar2,
spjoboutvarchar2,
spsaloutnumber)
is
begin
selectename,job,salintospname,spjob,spsalfromempwhereempno=spno;
end;
d)有返回结果集的存储过程
编写一个过程,输入部门号,返回该部门所有雇员的信息
因oracle存储过程无返回值,其返回值是用out参数来替代,但是集合不能用一般的参数,必须要用package
--创建一个包,在该包中定义了一个叫test_cursor的游标类型
createpackagetestpack
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 学习 oracle 笔记 PLSQL