plsql编程学习文档Word文档格式.docx
《plsql编程学习文档Word文档格式.docx》由会员分享,可在线阅读,更多相关《plsql编程学习文档Word文档格式.docx(11页珍藏版)》请在冰豆网上搜索。
exception
whenno_data_foundthen
没有此id'
);
7.dbms_output是oracle所提供的包(类似java的开发包),该包包含一些过程,put_line就是dbms_output包的一个过程
8.setserveroutputon--打开输出选项
9.&
表示要接收从控制台输入的变量
10.过程用于执行特定的操作,当建立过程时,既可以指定输入参数(in,定义时,只需指定类型,不用指定大小),也可以指定输出参数(out),通过在过程中使用输入参数,可以将数据传递到执行部分;
通过使用输出参数,可以将执行部分的数据传递到应用环境。
在sqlplus中可以使用createprocedure命令来建立过程
11.Oracle存储过程案例:
createorreplaceproceduresp_pro3(spIdnumber,newNamevarchar2)is
updatetestsetnames=newNamewhereid=spId;
commit;
12.java程序去调用Oracle的存储过程案例:
Class.forName("
oracle.jdbc.driver.OracleDriver"
Connectionct=DriverManager.getConnection("
jdbc:
oracle:
thin:
@127.0.0.1:
1521:
orcl"
"
lhy"
//创建一个CallableStatement
CallableStatementcs=ct.prepareCall("
callsp_pro3(?
?
)"
cs.setInt(1,10);
cs.setString(2,"
ddd"
cs.execute();
java程序调用Oracle的有返回值的存储过程:
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);
编号为100的出版社是:
+publihHouse);
cs.close();
ct.close();
java程序调用Oracle有返回结果集的存储过程:
----建立一个包,在该包中,我定义类型test_cursor,是个游标。
如下
createorreplacepackagetestpackageas
TYPEtest_cursorisrefcursor;
endtestpackage;
----建立存储过程。
如下
createorreplaceproceduresp_pro6(idinnumber,p_cursorouttestpackage.test_cursor)is
openp_cursorfor
select*frombookwherebookid=id;
end
----java调用
Connectionct=DriverManager.getConnection("
//´
´
½
¨
Ò
»
¸
ö
CallableStatement
callsp_pro6(?
//¸
ø
µ
Ú
¶
þ
¡
¢
È
ý
£
¿
³
Ö
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//Ö
Ð
//µ
Ã
á
¹
û
¼
¯
ResultSetrs=(ResultSet)cs.getObject
(2);
while(rs.next()){
System.out.println("
±
à
º
Å
Î
ª
100µ
Ä
Ê
é
Í
°
æ
É
ç
+rs.getString
(2)+"
+rs.getString(3));
}
ct.close();
13.函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句。
而在函数体内必须包含return语句返回的数据。
我们可以使用createfunction来建立函数
14.函数案例:
createfunctionannual_incomec(namevarchar2)
returnnumberisannual_salazynumber(7,2);
--执行部分
selectsal*12+nvl(comm,0)intoannual_salazyfromempwhereename=name;
returnannual_salazy;
End
15.包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成
16.创建包案例:
createpackagesp_packageis
procedureupdate_test(newNamevarchar2,newidnumber);
17.创建包体案例:
createorreplacepackagebodysp_packageis
procedureupdate_test(newNamevarchar2,newidnumber)
is
begin
updatetestsetnames=newNamewhereid=newid;
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;
--执行部分
v_sql:
='
select*from(selectt1.*,rownumrnfrom(select*from'
||tableName||'
)t1whererownum<
||v_end||'
)
wherern>
||v_begin;
--把游标和sql关联
openp_cursorforv_sql;
--计算myrows和myPageCount
--组织一个sql语句
selectcount(*)from'
||tableName;
--执行sql,并把返回的值,赋给myrows;
executeimmediatev_sqlintomyrows;
--计算myPageCount
ifmod(myrows,Pagesize)=0then
myPageCount:
=myrows/Pagesize;
else
=myrows/Pagesize+1;
endif;
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;
--更新用户sal
updateempsetsal=sal+1000whereempno=spNo;
--sql%notfound这是表示没有update
--raisemyex;
触发myex
ifsql%notfoundthen
raisemyex;
whenmyexthen
没有更新任何用户'
);
29.视图是一个虚拟表,其内容由查询定义,同真实的表一样,视图包含一系列带有名称的列和行数据。
但是,视图并不在数据库中以存储的数据值集形式存在。
行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。
(视图不是真实存在磁盘上的)
30.创建或修改视图:
createorreplaceview视图名asselect语句[withreadonly]
31.
32.复合变量(composite),用于存放多个值的变量。
主要包括这几种:
pl/sql记录,pl/sql表,嵌套表,varray
33.复合类型——pl/sql记录:
类似于高级语言中的结构体,需要注意的是,当引用pl/sql记录成员时,必须要加记录变量作为前缀(记录变量.记录成员)
34.pl/sql记录案例:
--定义一个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;
selectename,sal,jobintosp_recordfromempwhereempno=7788;
dbms_output.put_line('
员工名:
||sp_record.name);
35.复合类型-pl/sql表:
相当于高级语言中的数组,但是需要注意的是在高级语言中数组的下标不能为负数,而pl/sql是可以为负数的,并且表元素的
下标没有限制
36.pl/sql表案例:
--定义了一个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;
selectenameintosp_table(-1)fromempwhereempno=7788;
dbms_output.put_line('
||sp_table(-1));
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元。
--定义游标类型sp_emp_cursor
typesp_emp_cursorisrefcursor;
--定义一个游标变量
test_cursorsp_emp_cursor;
--定义变量
v_enameemp.ename%type;
v_salemp.sal%type;
--执行
--把test_cursor和一个select结合
opentest_cursorforselectename,salfromempwheredeptno=&
no;
--循环取出
loop
fetchtest_cursorintov_ename,v_sal;
--判断是否test_cursor为空
exitwhentest_cursor%notfound;
名字:
||v_ename||'
工资:
||v_sal);
endloop;
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;
--执行
selectjobintov_jobfromempwhereempno=spNo;
ifv_job='
PRESIDENT'
then
updateempsetsal=sal+1000whereempno=spNo;
elsifv_job='
MANAGER'
updateempsetsal=sal+500whereempno=spNo;
else
updateempsetsal=sal+200whereempno=spNo;
endif;
43.循环语句–loop:
是pl/sql中最简单的循环语句,这种循环语句以loop开头,以endloop结尾,这种循环至少会被执行一次
44.循环语句–while循环:
基本循环至少要执行循环体一次,而对于while循环来说,只有条件为true时,才会执行循环体语句,while循环以while...loop开始,以endloop结束
45.循环语句–for循环:
基本for循环的基本结构如下
foriinreverse1..10loop
insertintousersvalues(i,'
shunping'
endloop;
我们可以看到控制变量i,在隐含中就在不停地增加
46.goto语句:
基本语法如下gotolable,其中lable是已经定义好的标号名
47.goto语句案例:
iint:
=1;
loop
输出i='
||i);
ifi=1{}then
gotoends_loop;
i:
=i+1;
<
<
ends_loop>
>
循环结束'
48.null语句不会执行任何操作,并且会直接将控制传递到下一条语句。
使用null语句的主要好处是可以提高pl/sql的可读性
49.
50.