plsql编程学习文档.docx
《plsql编程学习文档.docx》由会员分享，可在线阅读，更多相关《plsql编程学习文档.docx（11页珍藏版）》请在冰豆网上搜索。
plsql编程学习文档
1.pl/sql（procedurallanguage/sql）是oracle在标准的sql语言上的扩展。
pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大
2.过程，函数，触发器是用pl/sql编写的。
过程，函数，触发器可以在java程序中调用，pl/sql是非常强大的数据库过程语言
3.如何查看错误信息：
showerrors
如何调用该过程：
（1）exec过程名（参数值1，参数值2，……）
（2）call过程名（参数值1，参数值2，……）
4.编写规范:
（1）单行注释--,多行注释/*...*/来划分
（2）标志符号的命名规范:
当定义变量时，建议用v_作为前缀v_sal;当定义常量时，建议用c_作为前缀c_rate;当定义游标时，建议用_cursor作为后缀emp_cursor;当定义例外时，建议用e_作为前缀e_error
5.块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块
6.pl/sql块由三个部分构成：
定义部分，执行部分，例外处理部分
declare
/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分——要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分——处理运行的各种错误*/
end;
定义部分是从declare开始的，该部分是可选的；
执行部分是从begin开始的，该部分是必须的；
例外处理部分是从exception开始的，该部分是可选的
例子：
declare
v_namevarchar2（20）;
begin
selectnamesintov_namefromtestwhereid=&id;
dbms_output.put_line（'姓名：
'||v_name）;
exception
whenno_data_foundthen
dbms_output.put_line（'没有此id'）;
end;
7.dbms_output是oracle所提供的包（类似java的开发包），该包包含一些过程，put_line就是dbms_output包的一个过程
8.setserveroutputon--打开输出选项
9.&表示要接收从控制台输入的变量
10.过程用于执行特定的操作，当建立过程时，既可以指定输入参数（in，定义时，只需指定类型，不用指定大小），也可以指定输出参数（out），通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。
在sqlplus中可以使用createprocedure命令来建立过程
11.Oracle存储过程案例：
createorreplaceproceduresp_pro3（spIdnumber,newNamevarchar2）is
begin
updatetestsetnames=newNamewhereid=spId;
commit;
end;
12.java程序去调用Oracle的存储过程案例：
Class.forName（"oracle.jdbc.driver.OracleDriver"）;
Connectionct=DriverManager.getConnection（"jdbc:
oracle:
thin:
@127.0.0.1:
1521:
orcl","lhy","lhy"）;
//创建一个CallableStatement
CallableStatementcs=ct.prepareCall（"callsp_pro3（?
?
）"）;
cs.setInt（1,10）;
cs.setString（2,"ddd"）;
cs.execute（）;
java程序调用Oracle的有返回值的存储过程：
Class.forName（"oracle.jdbc.driver.OracleDriver"）;
Connectionct=DriverManager.getConnection（"jdbc:
oracle:
thin:
@127.0.0.1:
1521:
orcl","lhy","lhy"）;
//创建一个CallableStatement
CallableStatementcs=ct.prepareCall（"callsp_pro5（?
?
?
）"）;
cs.setInt（1,100）;
//给第二、三个？
赋值
cs.registerOutParameter（2,oracle.jdbc.OracleTypes.VARCHAR）;
cs.registerOutParameter（3,oracle.jdbc.OracleTypes.VARCHAR）;
//执行
cs.execute（）;
//取返回值，要注意？
顺序
Stringname=cs.getString
（2）;
StringpublihHouse=cs.getString（3）;
System.out.println（"编号为100的书名是：
"+name）;
System.out.println（"编号为100的出版社是：
"+publihHouse）;
cs.close（）;
ct.close（）;
java程序调用Oracle有返回结果集的存储过程：
----建立一个包，在该包中，我定义类型test_cursor,是个游标。
如下
createorreplacepackagetestpackageas
TYPEtest_cursorisrefcursor;
endtestpackage;
----建立存储过程。
如下
createorreplaceproceduresp_pro6（idinnumber,p_cursorouttestpackage.test_cursor）is
begin
openp_cursorfor
select*frombookwherebookid=id;
end
----java调用
Class.forName（"oracle.jdbc.driver.OracleDriver"）;
Connectionct=DriverManager.getConnection（"jdbc:
oracle:
thin:
@127.0.0.1:
1521:
orcl","lhy","lhy"）;
//´´½¨Ò»¸öCallableStatement
CallableStatementcs=ct.prepareCall（"callsp_pro6（?
?
）"）;
cs.setInt（1,100）;
//¸øµÚ¶þ¡¢Èý¸ö£¿¸³Öµ
cs.registerOutParameter（2,oracle.jdbc.OracleTypes.CURSOR）;
//Ö´ÐÐ
cs.execute（）;
//µÃµ½½á¹û¼¯
ResultSetrs=（ResultSet）cs.getObject
（2）;
while（rs.next（））{
System.out.println（"±àºÅΪ100µÄÊéÃûºÍ³ö°æÉçΪ"+rs.getString
（2）+"ºÍ"+rs.getString（3））;
}
cs.close（）;
ct.close（）;
13.函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。
而在函数体内必须包含return语句返回的数据。
我们可以使用createfunction来建立函数
14.函数案例：
createfunctionannual_incomec（namevarchar2）
returnnumberisannual_salazynumber（7,2）;
begin
--执行部分
selectsal*12+nvl（comm,0）intoannual_salazyfromempwhereename=name;
returnannual_salazy;
End
15.包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成
16.创建包案例：
createpackagesp_packageis
procedureupdate_test（newNamevarchar2,newidnumber）;
end;
17.创建包体案例：
createorreplacepackagebodysp_packageis
procedureupdate_test（newNamevarchar2,newidnumber）
is
begin
updatetestsetnames=newNamewhereid=newid;
end;
end;
18.当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。
19.调用包的过程或是函数案例：
callsp_package.update_test（'SCOTT',10）;
20.定义并使用变量,复合类型
●标量类型（scalar）
●复合类型（composite）
●参照类型（reference）
●lob（largeobject）
21.pl/sql中定义变量和常量的语法如：
identifier[constant]datatype[notnull][:
=|defaultexpr]
identifier:
名称
constant：
指定常量。
需要指定它的初始值，且其值是不能改变的
datatype：
数据类型
notnull：
指定变量值不能为null
:
=给变量或是常量指定初始值
default用于指定初始值
expr：
指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等
22.在定义好变量后，就可以使用这些变量。
这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前面加冒号（:
=）
23.标量（scalar）——使用%type类型。
使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
使用格式：
标识符名表名.列名%type
24.由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了
25.Oracle分页：
createorreplaceprocedurefenye
（tableNameinvarchar2,
Pagesizeinnumber,--一页显示记录数
pageNowinnumber,
myrowsoutnumber,--总记录数
myPageCountoutnumber,--总页数
p_cursorouttestpackage.test_cursor--返回的记录集
）is
--定义部分
--定义sql语句字符串
v_sqlvarchar2（1000）;
--定义两个整数
v_beginnumber:
=（pageNow-1）*Pagesize+1;
v_endnumber:
=pageNow*Pagesize;
begin
--执行部分
v_sql:
='select*from（selectt1.*,rownumrnfrom（select*from'||tableName||'）t1whererownum<='||v_end||'）
wherern>='||v_begin;
--把游标和sql关联
openp_cursorforv_sql;
--计算myrows和myPageCount
--组织一个sql语句
v_sql:
='selectcount（*）from'||tableName;
--执行sql,并把返回的值，赋给myrows；
executeimmediatev_sqlintomyrows;
--计算myPageCount
ifmod（myrows,Pagesize）=0then
myPageCount:
=myrows/Pagesize;
else
myPageCount:
=myrows/Pagesize+1;
endif;
end;
26.预定义例外是由pl/sql所提供的系统例外。
当pl/sql应用程序违反了oracle规定的限制时，则会隐含的触发一个内部例外。
pl/sql为开发人员提供了二十多个预定义例外
case_not_found:
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found的例外
cursor_already_open:
当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open
dup_val_on_index:
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外dup_val_on_index例外
invalid_cursor:
当试图在不合法的游标上执行操作时，会触发该例外
invalid_number:
当输入的数据有误时，会触发该例外
too_many_rows:
当执行selectinto语句时，如果返回超过了一行，则会触发该例外
27.预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle错误会隐含的触发相应的例外；而自定义例外与oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外
28.自定义例外案例：
createorreplaceprocedureex_test（spNonumber）
is
--定义一个例外
myexexception;
begin
--更新用户sal
updateempsetsal=sal+1000whereempno=spNo;
--sql%notfound这是表示没有update
--raisemyex;触发myex
ifsql%notfoundthen
raisemyex;
endif;
exception
whenmyexthen
dbms_output.put_line（'没有更新任何用户'）；
end;
29.视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。
但是，视图并不在数据库中以存储的数据值集形式存在。
行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
（视图不是真实存在磁盘上的）
30.创建或修改视图：
createorreplaceview视图名asselect语句[withreadonly]
31.
32.复合变量（composite），用于存放多个值的变量。
主要包括这几种：
pl/sql记录，pl/sql表，嵌套表，varray
33.复合类型——pl/sql记录：
类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀（记录变量.记录成员）
34.pl/sql记录案例：
declare
--定义一个pl/sql记录类型emp_record_type,类型包含3个数据name,salary,title。
说白了，就是一个类型可以存放3个数据，主要是为了好管理
typeemp_record_typeisrecord（
nameemp.ename%type,
salaryemp.sal%type,
titleemp.job%type）;
--定义了一个sp_record变量，这个变量的类型是emp_record_type
sp_recordemp_record_type;
begin
selectename,sal,jobintosp_recordfromempwhereempno=7788;
dbms_output.put_line（'员工名:
'||sp_record.name）;
end;
35.复合类型-pl/sql表：
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的
下标没有限制
36.pl/sql表案例：
declare
--定义了一个pl/sql表类型sp_table_type，该类型是用于存emp.ename%type
--indexbybinary_integer表示下标是整数
typesp_table_typeistableofemp.ename%type
indexbybinary_integer;
--定义了一个sp_table变量，这个变量的类型是sp_table_type
sp_tablesp_table_type;
begin
selectenameintosp_table（-1）fromempwhereempno=7788;
dbms_output.put_line（'员工名:
'||sp_table（-1））;
end;
37.selectenameintosp_table（-1）fromemp,（sp_table_type是pl/sql表类型）实际返回的行数超出请求的行数？
解决方法是：
使用参照变量
38.参照变量是指用于存放数值指针的变量。
通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。
在编写pl/sql程序时，可以使用游标变量（refcursor）和对象类型变量（refobj_type）两种参照变量类型
39.使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了
40.游标与Select语句案例如下：
请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。
在基础上，如果某个员工的工资低于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;
end;
41.条件分支语句：
pl/sql中提供了三种条件分支语句if—then，if–then–else，if–then–elsif–then
42.条件分支语句案例：
编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资增加500，其它职位的雇员工资增加200
createorreplaceproceduresp_pro6（spNonumber）is
--定义
v_jobemp.job%type;
begin
--执行
selectjobintov_jobfromempwhereempno=spNo;
ifv_job='PRESIDENT'then
updateempsetsal=sal+1000whereempno=spNo;
elsifv_job='MANAGER'then
updateempsetsal=sal+500whereempno=spNo;
else
updateempsetsal=sal+200whereempno=spNo;
endif;
end;
43.循环语句–loop：
是pl/sql中最简单的循环语句，这种循环语句以loop开头，以endloop结尾，这种循环至少会被执行一次
44.循环语句–while循环：
基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while...loop开始，以endloop结束
45.循环语句–for循环：
基本for循环的基本结构如下
begin
foriinreverse1..10loop
insertintousersvalues（i,'shunping'）;
endloop;
end;
我们可以看到控制变量i，在隐含中就在不停地增加
46.goto语句：
基本语法如下gotolable，其中lable是已经定义好的标号名
47.goto语句案例：
declare
iint:
=1;
begin
loop
dbms_output.put_line（'输出i='||i）;
ifi=1{}then
gotoends_loop;
endif;
i:
=i+1;
endloop;
<>
dbms_output.put_line（'循环结束'）;
end;
48.null语句不会执行任何操作，并且会直接将控制传递到下一条语句。
使用null语句的主要好处是可以提高pl/sql的可读性
49.
50.
升级会员 