(selectSAGE
Fromstudent
WhereSDEPT='IS')
ANDSDEPT<>'IS'
7带有EXISTS谓词的子查询
EXISTS代表存在,带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值”true”或逻辑假值“false”。
本查询涉及student和SC关系。
我们可以在student中依次取每个元祖的SNO值,用此值去检查SC关系。
若SC中存在这样的元祖,其SNO值等于此Student.SNO值,并且CNO=‘1’,则取此Student.SNAME送入结果关系。
SelectSNAMEFromstudent
WhereEXISTS
(Select*
FromSC
WhereSNO=Student.SNOANDCNO='1')
8Union(并集)运算符
语法格式:
Select*fromAunion[all]select*fromB
注:
(1)All表示将重复出现的记录全部显示。
(2)要求A与B的结构相同(字段类型相同)或者指定查询结构相同、数目相同和顺序相同的字段组合。
例13:
查询计算机科学系的学生和年龄不大于19岁的学生的集合
Select*fromstudent
WhereSDEPT='CS'
UNION
Select*fromstudent
WhereSAGE<='19'
9INTERSECT(交集)运算符
语法格式:
Select*fromAintersectselect*fromB
注:
要求A与B的结构相同(字段类型相同)或者指定查询结构相同、数目相同和顺序相同的字段组合。
例14:
查询计算机科学系的学生且年龄不大于19岁的学生的集合
Select*fromstudent
WhereSDEPT=‘CS’
INTERSECT
Select*fromstudent
WhereSAGE<=‘19’
二.PL/SQL语言
1介绍
(SQL)StructureQueryLanguage的含义是结构化查询语句.
目前的PL/SQL语言包括两部分:
一部分是数据库引擎部分,另一部分是可嵌入到许多产品(如C语言、java语言等)工具中的独立引擎。
这两部分称为数据库PL/SQL和工具PL/SQL。
两者的编程非常相似,都具有编程结构、语法和逻辑机制。
2组成
PL/SQL语言由以下几个部分组成
(1)数据定义语言(DDL-DataDefinitionLanguage)。
数据定义语言用于执行数据库的任务,对数据库以及数据库中的各种对象进行创建、删除、修改等操作。
基本的DDL命令及功能如下表:
(2)数据操纵语言用于操纵数据库中各种对象、检索和修改数据。
(3)数据控制语言用于安全管理、确定哪些用户可以查看或修改数据库中的数据。
DCL命令包括的主要语句及功能如下表:
3使用PL/SQL语言的好处
(1)有利于客户-服务器环境应用的运行。
对于客户-服务器环境来说,真正的瓶颈在网络上。
无论网络多快,只要客户端与服务器进行大量数据交换,应用运行的效率自然就会受到影响。
如果使用PL/SQL语言进行编程,将这种具有大量数据处理的应用放在服务器端来执行,就省略了数据在网上的传输时间。
(2)适合于客户环境。
PL/SQL语言分为数据库PL/SQL和工具PL/SQL。
对于客户端来说,PL/SQL语言可以嵌入到相应的工具中,客户端程序可以执行本地包含PL/SQL部分,也可以向服务器发SQL命令,或激活服务器端的PL/SQL程序运行
4.PL/SQL基础知识
4.1合法字符
包括以下三类:
(1)所有大小写英文字母(朝鲜文,日文…不可以)
(2)数字0~9
(3)符号(、)、+、-、*、/、<、>、=、!
、~、:
、;、.、‘、@、%、,、“、#、^、&、{、}、?
、[、]。
4.2变量的声明
语法
<变量名><数据类型>[(数据尺寸):
=<初始值>];
Eg:
abcvarchar2(20):
=’100’;
%用于表示属性提示符。
(1)%type属性提供了变量的数据库列的数据类型。
在声明一个包含数据库值的变量时非常有用。
例如:
在XS表中包含XH列,为了声明一个变量my_xh与XH列具有相同的数据类型,可使用点和%TYPE属性,格式如下:
My_xhXS.XH%TYPE;
使用%TYPE声明具有两个优点
1)不必知道XH列的确切数据类型
2)如果改变了XH列的数据库定义,my_xh的数据类型在运行时会自动进行修改。
(2)%Rowtype属性,可以使用%Rowtype属性声明描述表的行数据的记录,对于用户定义的记录,必须声明自己的域。
记录包含唯一的命名域,具有不同的数据类型。
Declare
TypetimeRecisrecord(hhnumber
(2),mmnumber
(2));其中timerec相当于类名
TypemeetingTypisrecord
(meeting_Datedate,
meeting_timetimeRec,
meeting_addrvarchar2(20),
meeting_purposevarchar2(50));
注意:
在定义记录时可以嵌套记录,也就是说,记录可以是另一个记录的一个组件。
行中的列和记录中相对应地具有相同的名称和数据类型的一个type。
例如,声明一个记录名为my_rec,它与XS具有相同的名称和数据类型。
declare
My_recXS%Rowtype;(XS是表名)
例
declare
my_recxs%rowtype;
begin
select*intomy_recfromxswhererownum=1;
dbms_output.put_line(my_rec.xm);
end;
语法格式
<变量名>constant<数据类型>:
=<值>
例:
numconstantinteger:
=4;
Strconstantchar:
=‘HelloWorld!
’;
4.3PL/SQL常用数据类型
Varchar类型(最大长度4000B)
可变长字符数据
语法格式:
myStrvarchar(n);
Number类型(4B)
数值类型
语法格式:
myNumnumber(precision,scale);
其中:
precision表示总的位数;scale表示小数的位数
myNumnumber(10,2);
表示整数部分最多8位,小数部分最多2位的变量。
如果实际的数据超出设定的精度则出现错误。
Date类型
Date用于存放日期时间类型数据,用7字节分别描述年、月、日、时、分、秒。
语法格式:
myDateDate;
日期默认格式为DD-MON-YY,分别对应日、月、年,例如17-JUN-2002。
注意,月份的表达要用英文单词的缩写格式。
日期格式可以设置为中文格式。
例如17-六月-2002
对象类型
在多表操作的情况下,当多个表中的列要存储相同类型的数据时,要确保这些列具有完全相同的数据类型、长度和为空性(数据类型是否允许空值)。
语法格式:
Createorreplacetypeschema.type_name
[authid{current_user|definer}]asobject
(attribute1datatype,
[attribure2datatype,]
…
[attributendatatype]
[method1]
[method2]
…
[methodn])
说明
其中,schema:
用户自定义类型所属方案。
Type_name:
用户自定义类型名称。
Authid:
指示将来执行该方法时,必须使用在创建时定义的current_user或definer的权限集合。
Current_user是调用该方法的用户。
Definer是该对象类型的所有者。
Attribute1:
对象类型的属性。
属性的声明有一些限制,包括以下内容。
(1)属性的声明必须在方法声明之前。
(2)数据类型可以是任何数据库类型,但是不能包括rowid、urowid、long、longraw、nchar、nclob、nvarchar2类型,以及PL/SQL语言的专用类型或在PL/SQL包中定义的类型。
(3)不能使用那些只能在PL/SQL语言中使用而不能在数据库中使用的数据类型。
这些类型包括:
binary_integer、boolean、pls_integer、record和refcursor。
定义
createorreplacetypetest_objasobject
(
item_idchar(6),
pricenumber(10,2)
);
调用
Setserveroutputon;
Declare
myObjtest_obj:
=test_obj(item_id=>'abcdef',price=>12.5);
Begin
dbms_output.put_line(myObj.item_id);
dbms_output.put_line(myObj.price);
End;
4.4PL/SQL程序结构
4.4.1.IF逻辑结构
IF逻辑结构有3种表达式。
(1)IF-THEN
语法格式:
IFboolean_expressionTHEN
Run_expression
ENDIF
(2)IF-THEN-ELSE
语法格式:
Ifboolean_expressionthen
run_expression
Else
run_expression
Endif
(3)IF-THEN-ELSIF-THEN-ELSE
语法格式:
Ifboolean_expression1then
run_expression1
Elsifboolean_expression2then
run_expression2
Else
run_expression3
Endif
如果IF后的条件表达式boolean_expression1成立,执行then后的语句run_expression1,否则判断elsif后面的条件表达式boolean_expression2,为真时执行run_expression2,否则执行else后的语句run_expression3。
4.4.2循环执行语句一定要确保有相应的退出条件。
1).LOOP-IF-EXIT-END循环
语法格式:
Loop
run_expression
ifboolean_expressionthen
exit;
endif;
endloop;
run_expression是在循环体中需要完成的操作。
如果boolean_expression条件表达式为true则跳出循环,否则继续循环,直到满足条件表达式跳出循环。
2).LOOP-EXIT-WHEN-END循环
语法格式:
Loop
run_expression
exitwhenboolean_expression
Endloop;
3).WHILE-LOOP-END循环
语法格式:
Whileboolean_expression
Loop
run_expression
Endloop;
此结构的循环在while部分测试退出条件boolean_expression,当条件成立时执行循环体run_expression,否则退出循环。
这种结构的循环不同于前两种循环结构是因为:
前两种循环至少执行一次。
4.FOR-IN-LOOP-END循环
语法格式:
Forcountincount_1..count_n(注意:
..)
Loop
run_expression
Endloop;
Count是循环变量,in确定循环变量的初始值count_1和终止值count_n,在循环变量的范围count_1和count_n之间是分隔符两个点号(..)。
如果循环变量的值小于终值,则运行循环体内的语句,否则跳出循环,执行以下语句。
每循环一次循环变量自动增加一个步长的值,直到循环变量的值超过终值,退出循环,执行后面的语句。
4.4.3选择和跳转语句
1).CASE语句
Case语句是在oracle9i才引入的,它可以使用简单的结构,对数值列表做出选择,更为重要的是,它还可以用于设置变量的值。
语法格式:
Caseinput_name
whenexpression1thenresult_expression1
whenexpression2thenresult_expression2
…
whenexpressionNthenresult_expression
[elseresult_expressionN]
End;
首先设定变量input_name的值,然后顺序比较expression表达式与input_name的值,若相等,则返回对应的result_expression表达式的值,并且停止case语句的处理。
例8:
创建表
createtablekc(kchchar(3)primarykey,kcmvarchar2(16),kkxqnumber
(1),xsnumber
(2),xfnumber
(1)notnull);
数据
insertintokcvalues('101','计算机基础',1,80,5);
程序
Declare
v_kchchar(3);
v_resultvarchar2(16);
Begin
selectkchintov_kchfromkcwherekkxq='1';
dbms_output.put_line(v_kch);
casev_kch
when'101'thenv_result:
='计算机基础';
when'102'thenv_result:
='程序设计语言';
when'103'thenv_result:
='离散数学';
when'104'thenv_result:
='数据结构';
else
v_result:
='nothing';
endcase;
dbms_output.put_line(v_result);
end;
2.GOTO语句
PL/SQL语言提供GOTO语句,实现将执行流程转移到标号指定的位置。
语法格式:
Gotolabel
Label是指向的语句标号,标号必须符合标识符规则。
标号的定义形式如下:
<
使用goto语句,可以控制执行顺序。
例9:
创建表
createtabletemp(xhchar(6),xbchar
(2));
程序
Declare
v_counterbinary_integer:
=1;
v_xhnumber(6);
Begin
v_xh:
=100001;
loop
insertintotempvalues(to_char(v_xh),'男');
v_counter:
=v_counter+1;
v_xh:
=v_xh+1;
Ifv_counter=11then
gototarget_sign;
endif;
endloop;
<>
dbms_output.put_line('InitOK!
');
end;
三.Oracle存储过程
1.存储过程对比
2.语法
CREATE[ORREPLACE]PROCEDURE[schema.]procedure_name
[(argument[{IN|OUT|INOUT}]datatype,
...
argument[{IN|OUT|INOUT}]datatype)]
{IS|AS}
[descriptionpart说明部分]
BEGIN
SQLSTATEMENT语句序列
[EXCEPTION例外处理]
END[procedureName过程名];
注:
datatype中可以有varchar2,不能是varchar2(10);
3.语法分析
ORREPLACE
是一个可选的关键字,建议用户使用此关键字。
如果过程已经存在,该关键字将重新创建过程,这样就不必删除和重新创建过程。
关键字IS和AS均可,
它们本身没有区别。
IS后面是一个完整的PL/SQL块,可以定义局部变量,但不能以DECLARE开始。
局部变量在过程内部存放值。
形式参数可以有三种模式:
IN、OUT、INOUT。
如果没有为形式参数指定模式,那么默认的模式是IN。
IN表示输入参数
OUT表示输出参数
Eg:
创建第一个存储过程HelloWorld
SQL>createorreplaceprocedurehelloworldasbegindbms_output.put_line('helloworld');end;
Eg:
执行
SQL>setserveroutputon;
SQL>exec[ute]helloworld;
4.编译过程
注意:
存储过程不论创建是否成功,创建过程/函数命令CREATEPROCEDURE或CREATEFUNCTION都将自动把其源代码存入数据库中,而编译代码只有在编译成功后才能存入数据库中。
只有编译代码被存入到数据库的存储过程和函数才能被调用。
也就是说,如果你创建存储过程的语句是错误的,那么存储过程的源代码也会放入数据库,只是被显示为错误。
查看错误请用USER_ERRORS数据字典或用SHOWERRORS命令,可以查询到当前系统中错误。
5.带参存储过程
5.1带参存储过程(输入参数)在过程中不赋值
创建存储过程Hello‘Tom’
SQL>createorreplaceprocedurehelloTom(pnameinvarchar2)asbegindbms_output.put_line('hello'||pname||'!
');end;
SQL>/
执行
SQL>setserveroutputon;
SQL>exechelloTom('jerry');
显示如下结果
hellojerry!
PL/SQLproceduresuccessfullycompleted
5.2带参存储过程(输出参数)在过程中赋值.
创建存储过程Write‘Tom’
SQL>createorreplaceprocedurewriteTom(pnameoutvarchar2)asbeginselectnameintopnamefromt1whererownum=1;end;
SQL>/
带输出参数存储过程的调用格式:
(1)绑定参数值
variable输出参数变量1,输出参数变量2…;
调用存储过程
EXEC[UTE]procedure_name(参数值1…参数名n,:
绑定变量1,,:
绑定变量2….);
SQL>variablepnamevarchar2(40);
SQL>execwriteTom(:
pname);
(2)在程序块中调用存储过程
SQL>declare
2pnamevarchar2(40);
3begin
4writeTom(pname);
5dbms_output.put_l