第23讲 PLSQL的数据库编程游标记录表.docx
- 文档编号:17558366
- 上传时间:2023-07-26
- 格式:DOCX
- 页数:22
- 大小:22.06KB
第23讲 PLSQL的数据库编程游标记录表.docx
《第23讲 PLSQL的数据库编程游标记录表.docx》由会员分享,可在线阅读,更多相关《第23讲 PLSQL的数据库编程游标记录表.docx(22页珍藏版)》请在冰点文库上搜索。
第23讲PLSQL的数据库编程游标记录表
第24讲PLSQL的数据库编程:
游标,记录,表
目的:
1.掌握PL/SQL中使用DQL,DML语句。
2.掌握游标概念,原理,使用,语法。
3.掌握使用循环处理游标。
授课内容:
1.PL/SQL中使用DQL,DML语句:
1)使用select语句:
语法:
select列名,列名,。
。
。
into变量名,变量名。
。
。
。
from表名
where条件
注:
使用select语句,必须保证select返回单行记录,才可以将select的值赋值给变量。
Select的项目,必须与into的项目的个数相同,且数据类型相同。
但是可以根据Oracle的数据类型自动转换原则进行。
变量必须在变量定义区定义好。
为了PL/SQL的通用性,变量定义最好使用表名.列名%TYPE方式定义。
为了处理SELECT返回多行记录的情况,要使用以后的游标数据类型来处理。
SELECT语句的使用例子:
declare
emp_novarchar2(10);
begin
selectsalintoemp_no--虽然emp_no定义为varchar2,单依然可以接收数字数据。
fromemp
whereempno=9100;
dbms_output.put_line(emp_no);//是调用系统存储过程
end;
以上说明的是Oracle的数据自动转换原则的作用。
动态定义数据类型
变量名表名.列名%TYPE;
使用此定义,变量的类型会自动与表的列的字段数据类型和尺寸相同。
当将来表的结构发生改变时,此变量会自动跟随。
2)使用insert,update,delete语句:
使用insert,update,delete语句比较简单。
在这些语句中,可以使用PL.SQL的参数,变量,给表的列赋值。
在where条件中使用参数,变量在表达式中。
凡是可以出现值的地方都可以出现PL/SQL的变量。
3)PL/SQL中处理事务:
可以在PL/SQL中使用commit,rollback.
PL/SQL中不能使用settransactionto[readonly|readwrite]
使用
dbms_transaction.readonly;//只读
dbms_transaction.read_write;
settransaction{read{only|write}}
启动一个事务。
Readonly保证更强的读取一致性。
一个只读的事务中,对表只能进行select,locktable命令。
Declare
My_empnoemp.empno%tyep:
=1234;
Begin
Dbms_transaction.read_write;
Insertintoemp(empno,ename,job)values(my_empno,‘LLL’,‘TEACHER’);
Commit;
End;
4)隐含游标的属性:
在Oracle运行select,insert,update,delete语句时,就自动使用了隐含游标的。
隐含游标的名称为SQL,它的属性有
sql%notfound:
判断执行的SQL语句是否捕捉到了记录。
True:
有影响的记录,false:
没有影响记录。
sql%rowcount:
返回sql影响的记录的个数。
sql%found:
判断sql是否操作了记录。
Sql%isopen:
判断游标是否打开,对显式游标特别有用,对隐含游标意义不大。
Oracle在服务器的oracle内存中自动保存当前的SQL语句的执行结果。
注:
在PL/SQL中不能使用DDL语句。
2.创建PL/SQL记录类型
1)记录:
记录是一种特殊的数据类型,它包含多个元素,每个元素可以是不同数据类型,如Oracle的number,varchar2,date,等。
2)创建记录数据的步骤:
1>使用Oracle记录的语法定义一种记录类型。
TAPExxxxisRECORD()
2>使用定义的类型定义变量:
变量名TYPE_name
3)定义记录类型(RECORD)
语法:
TYPEtype_nameISRECORD
(
element_nameTYPE,
element_nameTYPE,
…
element_nameTYPE
);
4)定义记录类型的变量:
语法:
varible_nametype_name;
(注:
类型必须先定义,然后才能使用,如果引用没有定义的数据类型,Oracle会返回错误号码。
)
5)给记录变量的元素赋值:
使用.来引用记录的元素:
记录变量.元素名:
=值
如:
employee.empno:
=1000;
employee.empname:
=’Luhaidong’;
employee.joindate:
=sysdate;
6)记录的完全赋值:
如果2个变量是同一记录类型,可以使用赋值操作,将2个记录的每个元素赋值为相同值。
语法:
记录变量:
=记录变量;
如:
declare
employee01EMPLOYEE_TYPE;
employee02EMPLOYEE_TYPE;
begin
employee01:
=employee02;
end;
(注:
如果2个记录变量,是不同的记录类型,则不能使用记录赋值操作)
7)在SQL语句中使用记录的元素。
(使用记录的DOT操作符)
可以在select,insert,update,delete中使用记录变量的元素值。
如:
selectempno,ename,job
intoemployee.emp_no,employee.emp_name,employee.emp_job
fromemp
whereempno=9001;//返回单行记录
8)记录中的记录(RecordsinsideRecords)。
CREATEORREPLACEprocedureSP002
is
typeemp_addressisrecord
(
addressvarchar2(50),
cityvarchar2(20),
statevarchar2(20),
countryvarchar2(50),
postcodevarchar2(10)
);
--定义记录数据类型
typeemp_recordisrecord
(
empnonumber(4),
empnamevarchar2(10),
empjobemp.job%type,
empaddressemp_address//嵌套一个记录类型
);
--定义一个记录数据类型的变量
emp001emp_record;
begin
--给一个记录类型的各个字段赋值
emp001.empno:
=9102;
emp001.empname:
='Luhadoong';
emp001.empjob:
='SALESMAN';
--给记录中的记录赋值
emp001.empaddress.address:
='中山区黄河路22号';
emp001.empaddress.city:
='大连';
emp001.empaddress.state:
='辽宁';
--使用记录的各个字段值,进行insertinto工作
insertintoemp(empno,ename,job)values(emp001.empno,emp001.empname,emp001.empjob);
--提交插入数据
commit;
end;
/
9)使用TABLE%ROWTYPE创建记录类型
当创建一个记录,包含表中的所有字段时,使用一般的记录定义要有许多的变量定义,非常的不方便。
Oracle提供一个简单的语法,来创建表的记录类型。
语法:
TYPE_NAMETABLE_NAME%ROWTYPE;
例子1:
createorreplaceproceduresp003
(emp_nonumber)
is
employee01emp%rowtype;
begin
select*intoemployee01
fromemp
whereempno=emp_no;//返回单行记录
insertintoSPTABLEvalues('002',employee01.ename||'isfound',emp_no);//‘||’:
字符串连接
exception
whenothersthen
insertintoSPTABLEvalues('001','theempnoisnotexist',emp_no);
end;
如2:
createorreplaceproceduresp003
(emp_nonumber)
is
employee01emp%rowtype;
typeSP_TABLEisrecord
(
sp_namevarchar2(100),
sp_messvarchar2(500),
sp_numnumber(38)
);
sptable01SP_TABLE;
begin
select*intoemployee01
fromemp
whereempno=emp_no;
select*intosptable01
fromsptable
wherespname='001';
insertintoSPTABLEvalues('002',employee01.ename||'isfound',emp_no);
exception
whenothersthen
insertintoSPTABLEvalues('001','theempnoisnotexist',emp_no);
end;
3.PL/SQLTABLE(表):
1)PL/SQLtable一般称collection.每个记录都由其位置来决定。
2)PL/SQLtable类型
1>有序号的table(index-bytable):
类似于Java的数组。
2>嵌套表(nestedtable)
3)有序号的表(index-bytable)
1>创建:
语法:
TYPEtable_type_nameISTABLEOFdatatype
indexbybinary_integer;
注:
如果要索引选项,就必须使用indexbybinary_integer,不能使用integer.Oracle不支持其他的选项。
注:
只有indexbybinary_integer,才表示建立的是带序号的表。
2>使用创建的table类型,建立table变量:
variable_nameTABLE_TYPE_NAME;
3>使用Table类型变量:
table变量名(序号):
=值;
其他变量:
=table变量(序号);
序号可以时任何整数。
例子1:
createorreplaceproceduresp004
is
typetable_typeistableofvarchar2(20)
indexbybinary_integer;
table01table_type;
table02table_type;
begin
table01(0):
='a0';
table01
(1):
='a1';
end;
例子2:
createorreplaceproceduresp004
is
typetable_typeistableofvarchar2(20)
indexbybinary_integer;
table01table_type;
table02table_type;
begin
table01(-11.6):
='-a11.76';
table01(11.76):
='a11.76';
insertintosptablevalues(table01(-11.76),table01(11.76),100);
end;
4)嵌套表(nestedtable)
创建:
TYPEtable_type_nameISTABLEOFdatatype
使用:
变量名table类型名;
嵌套表赋值:
变量:
=table_type_name(值1,值2,值3,,,,,,);
(嵌套表必须经过初始化后,才可以使用序号访问)
使用嵌套表中的元素:
变量名(序号):
=值
嵌套表例子1:
createorreplaceproceduresp005
is
typetable_typeistableofvarchar2(20);
table01table_type;
table02table_type;
begin
table01:
=table_type('a01','a02','a03');
table01
(1):
='a011';
insertintosptablevalues(table01
(1),table01
(2),100);
end;
嵌套表的属性:
1>count:
2>exists(n):
3>first:
4>last:
5>prior:
6>next:
7>extend(n):
8>trim(n):
9>delete(n):
10>delete(m,n):
嵌套表例子2:
createorreplaceproceduresp005
is
typetable_typeistableofvarchar2(20);
table01table_type;
table02table_type;
table_element_numnumber(10);
begin
table01:
=table_type('a011','a022','a033');
table01
(1):
='a011';
table_element_num:
=table01.count;--使用了嵌套表的属性
insertintosptablevalues(table01
(1),table01
(2),table_element_num);
end;
5)PL/SQLTablesofRecords(PL/SQL记录的表)
表的类型可以是记录类型,这样就可以表达Oracle的数据库中的表的数据结构。
TYPErecord_type_nameisRECORD
(
record_element_namedatatype(specification);
);
TYPEtable_type_nameisTABLEOFrecord_type_name;
table_variable_nametable_type_name;
使用%ROWTYPE创建TABLE
TYPEtable_type_nameisTABLEOFtable_name%ROWTYPE;
记录类型的表的例子1:
createorreplaceproceduresp006
is
typeemp_recordisrecord
(
emp_nonumber(4),
emp_namevarchar2(50),
emp_jobvarchar2(20)
);
table_element_numnumber(10);
begin
null;
end;
使用了ROWTYPE的例子:
createorreplaceproceduresp007
is
TYPEemp_table_typeisTABLEOFemp%ROWTYPE;
emp_table_01emp_table_type;
table_element_numnumber(10);
begin
null;
end;
4.PL/SQLExplicitCursorHandling(显式游标处理)
1>什么是游标
当用户每次执行select,insert,update,delete语句时,Oracle把执行的结果保存在Oracle的内存中,保存的内存地址就是游标(cursor)。
游标的类型:
隐式游标(implicitcursor)和显式游标(Explicitcursor)
显式游标就是有名字的游标,即有名字的SQL语句。
2>游标的定义语法
游标定义要定义在PL/SQL的变量定义区:
CURSORcursor_nameIS
(
SELECT语句
);
游标定义的例子:
createorreplaceproceduresp008
is
CURSORemp001is
(
select*fromemp
);
emp_recordemp%rowtype;//定义记录类型
begin
openemp001;
fetchemp001intoemp_record;
insertintosptablevalues(emp_record.ename,emp_record.job,emp_record.sal);
closeemp001;
end;
3>游标的使用:
显式游标的是使用流程:
-定义显式游标(declarecursor)
-打开定义的游标(opencursor)
OPENcursor_name;打开游标,意味让Oracle执行select语句,进行查询,查询结果保存在游标名字的地址中。
游标打开后,游标指针指向第一记录的前的位置(beforethefirst)
-从游标中抓取数据(fetchdatafromcursor)
Oracle中,不能直接使用游标进行数据处理,必须使用FETCH语句,将游标中的记录取出来,保存到记录变量中。
FETCHcursor_nameINTOrecord_variable
Fetch首先移动游标的指针到下个记录,并把这个记录的字段值写入到变量中。
-对抓取的数据处理(fetcheddatahandling)
对取出来的记录数据,进行处理,判断,根据条件进行处理。
-关闭游标(closecursor)
CLOSEcursor_name;
关闭游标,释放内存。
4>使用游标获取表中的记录值
语法:
FETCHcursor_nameINTO独立变量名,独立变量名,。
。
。
。
将游标一个记录结果存入到变量中,要求:
(1)游标select中的项目个数,必须等于变量的个数相同。
(2)项目的类型与变量的类型相同。
FETCHcursor_nameINTO记录变量
将游标的一个记录结果保存到记录变量中,要求:
(1)游标select的项目个数与记录变量的元素个数相同
(2)游标对应的项目类型与记录变量的对应元素类型相同。
Oracle根据位置,把记录的字段值保存到变量中。
例子:
createorreplaceproceduresp009
is
CURSORemp001is
(
selectename,job,salfromemp
);
emp_recordemp%rowtype;
begin
openemp001;
fetchemp001intoemp_record.ename,emp_record.job,emp_record.sal;
insertintosptablevalues(emp_record.ename,emp_record.job,emp_record.sal);
closeemp001;
end;
上面的例子,只能将查询的第一个记录写入到表SPTABLE中,无法取得其他的记录。
要想取得游标的所有记录,必须使用循环结果,来遍历游标的结果。
5>使用循环来获取表的记录
(1)使用LOOP循环:
OPENcursor_name;
LOOP
FETCHcursor_nameINTOvariable
EXITWHENcursor_name%notfound;
--数据处理;
ENDLOOP;
CLOSEcursor_name;
例子:
createorreplaceproceduresp009
is
CURSORemp001is
(
selectename,job,salfromemp
);
emp_recordemp%rowtype;
begin
openemp001;
loop
fetchemp001intoemp_record.ename,emp_record.job,emp_record.sal;
exitwhenemp001%notfound;//判断执行的SQL语句是否捕捉到了记录
insertintosptablevalues(emp_record.ename,emp_record.job,emp_record.sal);
endloop;
closeemp001;
end;
例子:
createorreplaceproceduresp010
is
CURSORemp001is
(
select*fromemp
);
emp_recordemp%rowtype;
begin
openemp001;
loop
fetchemp001intoemp_record;//先捕捉才能知道是不是为空记录
exitwhenemp001%notfound;//判断是不是为空记录
insertintosptablevalues(emp_record.ename,emp_record.job,emp_record.sal);
endloop;
closeemp001;
end;
(2)使用FOR循环
语法:
FOR记录变量名IN游标名LOOP
处理语句;
ENDLOOP;
这是最简单的游标处理语法。
-自动打开游标,自动提取游标的数据,自动关闭游标。
-自动定义记录类型变量,将游标当前记录提取到记录变
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第23讲 PLSQL的数据库编程游标记录表 23 PLSQL 数据库 编程 游标 记录
![提示](https://static.bingdoc.com/images/bang_tan.gif)