orcl学习笔记.docx

1、orcl学习笔记一.Oracle连接查询1等值连接：如例1中为等值连接（连接谓词为 = ） 查询A表ID字段与B表CODE字段相等的记录Select A.*, B.* from A, B where A.ID = B.CODE2自然连接（广义笛卡尔积）例3： 查询A表与B表的广义笛卡尔积Select A.*, B.* from A, B注：在等值连接中把目标列中重复的属性列去掉为自然连接若A与B表中不包含重复的属性列，则等值连接=自然连接3外连接（不使用join）例5： 查询所有学生成绩，包含未参加考试的学生Select A.*, B.* from A, B where A.ID = B.CO

2、DE(+)注：当(+)在连接条件左侧时，表示右外连接；当(+)在连接条件右侧时，表示左外连接4内连接 内连接包含等值连接、不等值连接和自然连接 （1）等值连接 即在连接条件中使用等于运算符 比较被连接的列,其查询结果中将列出被连接表中的所有列,包括重复列.格式 SELECT 列名,列名FROM 表1 别名1 JOIN 表2 别名2 ON 连接条件 （2）不等值连接 即在连接条件中使用除等号外的运算符. （3）自然连接 即在连接条件中使用等于运算符比较被连接的列,其使用选择列表指出查询结果所包含的列,并删除连接表中的重复列。格式 SELECT 列名,列名FROM 表1 别名1 NATURAL J

3、OIN 表2 别名2 注：自然连接需要外键表的外键列与主表的主键列名称相同外连接是对内连接结果的扩展，即除返回所有匹配行外，还将返回不匹配的部分或全部行.外连接类型 左外连接 LEFT OUTER JOIN ON 说明：显示左表不匹配行 右外连接 RIGHT OUTER JOINON 说明：显示右表不匹配行 全外连接 FULL OUTER JOINON 说明：显示全部记录（忽略匹配条件）5交叉连接 交叉连接在不带WHERE子句时，返回的是被连接的两个表所有记录的笛卡尔乘积，其使用CROSS JOIN关键字连接两个表 格式：SELECT 列名,列名FROM 表1 CROSS JOIN 表2注：c

4、ross join 可以将多于两个表的记录建立笛卡尔积例7： 设有A, B, C, D四个表，获取四个表的迪卡尔积Select A.*, B.*, C.*, D.* from A cross join B cross join C cross join DWhere 6带有ANY或ALL谓词的子查询 子查询返回值时可以用比较运算符，而使用ANY和ALL谓词时必须同时使用比较运算符 查询其他系中比信息系统某一学生年龄小的学生姓名和年龄Select SNAME, SAGEFrom studentWhere SAGE ANY (select SAGE From student Where SDEPT

5、 = IS)AND SDEPT IS7带有EXISTS谓词的子查询EXISTS代表存在，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值”true”或逻辑假值“false” 。 本查询涉及student和SC关系。我们可以在student中依次取每个元祖的SNO值，用此值去检查SC关系。若SC中存在这样的元祖，其SNO值等于此 Student.SNO值，并且CNO=1，则取此 Student.SNAME送入结果关系。Select SNAME From studentWhere EXISTS (Select * From SC Where SNO=Student.SNO AND CNO

6、 = 1) 8 Union（并集）运算符语法格式：Select * from A union all select * from B注：(1)All表示将重复出现的记录全部显示。(2)要求A与B的结构相同（字段类型相同）或者指定查询结构相同、数目相同和顺序相同的字段组合。例13： 查询计算机科学系的学生和年龄不大于19岁的学生的集合Select * from studentWhere SDEPT = CSUNIONSelect * from studentWhere SAGE = 19 9 INTERSECT（交集）运算符语法格式：Select * from A intersect selec

7、t * from B注：要求A与B的结构相同（字段类型相同）或者指定查询结构相同、数目相同和顺序相同的字段组合。例14： 查询计算机科学系的学生且年龄不大于19岁的学生的集合Select * from studentWhere SDEPT = CS INTERSECT Select * from studentWhere SAGE = 19 二.PL/SQL语言1介绍(SQL）Structure Query Language 的含义是结构化查询语句.目前的PL/SQL语言包括两部分：一部分是数据库引擎部分，另一部分是可嵌入到许多产品（如C语言、java语言等）工具中的独立引擎。这两部分称为数据

8、库PL/SQL和工具PL/SQL。两者的编程非常相似，都具有编程结构、语法和逻辑机制。2组成PL/SQL语言由以下几个部分组成 （1）数据定义语言（DDL-Data Definition Language）。数据定义语言用于执行数据库的任务，对数据库以及数据库中的各种对象进行创建、删除、修改等操作。基本的DDL命令及功能如下表：（2）数据操纵语言用于操纵数据库中各种对象、检索和修改数据。（3）数据控制语言用于安全管理、确定哪些用户可以查看或修改数据库中的数据。DCL命令包括的主要语句及功能如下表：3使用PL/SQL语言的好处（1）有利于客户-服务器环境应用的运行。对于客户-服务器环境来说，真正

9、的瓶颈在网络上。无论网络多快，只要客户端与服务器进行大量数据交换，应用运行的效率自然就会受到影响。如果使用PL/SQL语言进行编程，将这种具有大量数据处理的应用放在服务器端来执行，就省略了数据在网上的传输时间。（2）适合于客户环境。PL/SQL语言分为数据库PL/SQL和工具PL/SQL。对于客户端来说，PL/SQL语言可以嵌入到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务器发SQL命令，或激活服务器端的PL/SQL程序运行 4.PL/SQL基础知识4.1合法字符包括以下三类： （1）所有大小写英文字母(朝鲜文,日文不可以)（2）数字09（3）符号（、）、+、-、*、

10、/、=、!、:、;、.、 、%、,、“ 、#、&、?、 。4.2变量的声明 语法(数据尺寸):=;Eg: abc varchar2(20):=100;%用于表示属性提示符。（1）%type属性提供了变量的数据库列的数据类型。在声明一个包含数据库值的变量时非常有用。例如：在XS表中包含XH列，为了声明一个变量my_xh与XH列具有相同的数据类型，可使用点和%TYPE属性，格式如下：My_xh XS.XH%TYPE;使用%TYPE声明具有两个优点 1）不必知道XH列的确切数据类型2）如果改变了XH列的数据库定义，my_xh的数据类型在运行时会自动进行修改。（2）%Rowtype属性，可以使用%Ro

11、wtype属性声明描述表的行数据的记录，对于用户定义的记录，必须声明自己的域。记录包含唯一的命名域，具有不同的数据类型。 DeclareType timeRec is record(hh number(2),mm number(2); 其中timerec相当于类名Type meetingTyp is record(meeting_Date date,meeting_time timeRec,meeting_addr varchar2(20),meeting_purpose varchar2(50);注意：在定义记录时可以嵌套记录，也就是说，记录可以是另一个记录的一个组件。行中的列和记录中相对应

12、地具有相同的名称和数据类型的一个type。例如，声明一个记录名为my_rec，它与XS具有相同的名称和数据类型。 declareMy_rec XS%Rowtype;(XS是表名)例 declaremy_rec xs%rowtype;beginselect * into my_rec from xs where rownum = 1;dbms_output.put_line(my_rec.xm);end;语法格式 constant:=例： num constant integer := 4;Str constant char:=Hello World!; 4.3 PL/SQL常用数据类型Varc

13、har类型(最大长度4000B)可变长字符数据 语法格式： myStr varchar(n);Number类型(4B)数值类型 语法格式： myNum number(precision,scale);其中：precision表示总的位数；scale表示小数的位数 myNum number(10,2);表示整数部分最多8位，小数部分最多2位的变量。如果实际的数据超出设定的精度则出现错误。 Date类型 Date用于存放日期时间类型数据，用7字节分别描述年、月、日、时、分、秒。 语法格式： myDate Date;日期默认格式为DD-MON-YY，分别对应日、月、年，例如17-JUN-2002。注

14、意，月份的表达要用英文单词的缩写格式。日期格式可以设置为中文格式。例如17-六月-2002对象类型 在多表操作的情况下，当多个表中的列要存储相同类型的数据时，要确保这些列具有完全相同的数据类型、长度和为空性（数据类型是否允许空值）。 语法格式： Create or replace type schema.type_name authidcurrent_user|definer as object(attribute1 datatype,attribure2 datatype,attributen datatypemethod1method2methodn) 说明 其中，schema：用户自定义

15、类型所属方案。 Type_name：用户自定义类型名称。 Authid：指示将来执行该方法时，必须使用在创建时定义的current_user或definer的权限集合。Current_user是调用该方法的用户。Definer是该对象类型的所有者。 Attribute1：对象类型的属性。属性的声明有一些限制，包括以下内容。 （1）属性的声明必须在方法声明之前。 （2）数据类型可以是任何数据库类型，但是不能包括rowid、urowid、long、long raw、nchar、nclob、nvarchar2类型，以及PL/SQL语言的专用类型或在PL/SQL包中定义的类型。 （3）不能使用那些只能

16、在PL/SQL语言中使用而不能在数据库中使用的数据类型。这些类型包括：binary_integer、boolean、pls_integer、record和refcursor。 定义 create or replace type test_obj as object( item_id char(6), price number(10,2);调用Set serveroutput on;Declare myObj test_obj := test_obj(item_id = abcdef, price = 12.5);Begin dbms_output.put_line(myObj.item_id)

17、; dbms_output.put_line(myObj.price);End;4.4PL/SQL程序结构4.4.1.IF逻辑结构 IF逻辑结构有3种表达式。 （1）IF-THEN语法格式： IF boolean_expression THEN Run_expression END IF（2）IF-THEN-ELSE语法格式： If boolean_expression then run_expression Else run_expression End if（3）IF-THEN-ELSIF-THEN-ELSE语法格式： If boolean_expression1 then run_exp

18、ression1Elsif boolean_expression2 then run_expression2Else run_expression3End if如果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 i

19、f boolean_expression then exit; end if;end loop;run_expression是在循环体中需要完成的操作。如果boolean_expression条件表达式为true则跳出循环，否则继续循环，直到满足条件表达式跳出循环。2).LOOP-EXIT-WHEN-END循环语法格式： Loop run_expression exit when boolean_expression End loop;3).WHILE-LOOP-END循环语法格式： While boolean_expression Loop run_expression End loop;此

20、结构的循环在while部分测试退出条件boolean_expression，当条件成立时执行循环体run_expression，否则退出循环。这种结构的循环不同于前两种循环结构是因为：前两种循环至少执行一次。4.FOR-IN-LOOP-END循环语法格式： For count in count_1 . count_n （注意：.）Loop run_expression End loop;Count是循环变量，in确定循环变量的初始值count_1和终止值count_n,在循环变量的范围count_1和count_n之间是分隔符两个点号（.）。如果循环变量的值小于终值，则运行循环体内的语句，否则

21、跳出循环，执行以下语句。每循环一次循环变量自动增加一个步长的值，直到循环变量的值超过终值，退出循环，执行后面的语句。4.4.3选择和跳转语句 1). CASE语句Case语句是在oracle9i才引入的，它可以使用简单的结构，对数值列表做出选择，更为重要的是，它还可以用于设置变量的值。 语法格式： Case input_name when expression1 then result_expression1 when expression2 then result_expression2 when expressionN then result_expression else result_

22、expressionNEnd;首先设定变量input_name的值，然后顺序比较expression表达式与input_name的值，若相等，则返回对应的result_expression表达式的值，并且停止case语句的处理。 例8:创建表create table kc (kch char(3) primary key, kcm varchar2(16), kkxq number(1), xs number(2), xf number(1) not null);数据insert into kc values (101,计算机基础,1,80,5);程序Declare v_kch char(3)

23、; v_result varchar2(16);Begin select kch into v_kch from kc where kkxq=1; dbms_output.put_line(v_kch); case v_kch when 101 then v_result:=计算机基础; when 102 then v_result:=程序设计语言; when 103 then v_result:=离散数学; when 104 then v_result:=数据结构; else v_result:=nothing; end case; dbms_output.put_line(v_result

24、);end;2. GOTO语句PL/SQL语言提供GOTO语句，实现将执行流程转移到标号指定的位置。语法格式： Goto labelLabel是指向的语句标号，标号必须符合标识符规则。标号的定义形式如下： 语句使用goto语句，可以控制执行顺序。 例9:创建表create table temp (xh char(6), xb char(2);程序Declare v_counter binary_integer:=1; v_xh number(6);Begin v_xh:=100001; loop insert into temp values (to_char(v_xh), 男); v_cou

25、nter:=v_counter+1; v_xh:=v_xh+1; If v_counter=11 then goto target_sign; end if; end loop; dbms_output.put_line(Init OK! );end;三.Oracle存储过程 1.存储过程对比2.语法CREATE OR REPLACE PROCEDURE schema. procedure_name ( argument IN | OUT | IN OUT datatype ,.argument IN | OUT | IN OUT datatype ) IS | ASdescription p

26、art 说明部分BEGIN SQL STATEMENT 语句序列 EXCEPTION 例外处理END procedure Name 过程名;注: datatype中可以有varchar2,不能是varchar2(10);3.语法分析 OR REPLACE 是一个可选的关键字，建议用户使用此关键字。如果过程已经存在，该关键字将重新创建过程，这样就不必删除和重新创建过程。关键字IS 和 AS均可， 它们本身没有区别。IS后面是一个完整的PL/SQL块，可以定义局部变量，但不能以DECLARE开始。局部变量在过程内部存放值。形式参数可以有三种模式：IN、OUT、IN OUT。如果没有为形式参数指定模

27、式，那么默认的模式是IN。IN表示输入参数OUT表示输出参数 Eg: 创建第一个存储过程 Hello WorldSQL create or replace procedure helloworld as begin dbms_output.put_line(hello world); end; Eg: 执行SQL set serveroutput on;SQL execute helloworld;4.编译过程注意： 存储过程不论创建是否成功，创建过程/函数命令CREATE PROCEDURE或CREATE FUNCTION都将自动把其源代码存入数据库中，而编译代码只有在编译成功后才能存入数据

28、库中。只有编译代码被存入到数据库的存储过程和函数才能被调用。 也就是说，如果你创建存储过程的语句是错误的，那么存储过程的源代码也会放入数据库，只是被显示为错误。 查看错误请用USER_ERRORS数据字典或用SHOW ERRORS命令，可以查询到当前系统中错误。5.带参存储过程5.1带参存储过程（输入参数）在过程中不赋值创建存储过程 Hello Tom SQL create or replace procedure helloTom (pname in varchar2) as begin dbms_output.put_line(hello | pname | !); end;SQL / 执

29、行SQL set serveroutput on;SQL exec helloTom( jerry );显示如下结果hello jerry !PL/SQL procedure successfully completed5.2带参存储过程（输出参数）在过程中赋值. 创建存储过程 Write Tom SQL create or replace procedure writeTom (pname out varchar2) as begin select name into pname from t1 where rownum = 1; end;SQL / 带输出参数存储过程的调用格式：(1) 绑定参数值variable 输出参数变量1,输出参数变量2 ; 调用存储过程EXECUTE procedure_name(参数值1参数名n,:绑定变量1, ,:绑定变量2.);SQL variable pname varchar2(40);SQL exec writeTom(:pname);(2)在程序块中调用存储过程SQL declare 2 pname varchar2(40); 3 begin 4 writeTom(pname); 5 dbms_output.put_l