orcl学习笔记.doc

一.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.CODE(+)注：当(+)在连接条件左侧时，表示右外连接；当(+)在连接条件右侧时，表示左外连接4内连接 内连接包含等值连接、不等值连接和自然连接 （1）等值连接 即在连接条件中使用等于运算符 比较被连接的列,其查询结果中将列出被连接表中的所有列,包括重复列.格式 SELECT 列名,列名FROM 表1 别名1 JOIN 表2 别名2 ON 连接条件 （2）不等值连接 即在连接条件中使用除等号外的运算符. （3）自然连接 即在连接条件中使用等于运算符比较被连接的列,其使用选择列表指出查询结果所包含的列,并删除连接表中的重复列。格式 SELECT 列名,列名FROM 表1 别名1 NATURAL JOIN 表2 别名2 注：自然连接需要外键表的外键列与主表的主键列名称相同外连接是对内连接结果的扩展，即除返回所有匹配行外，还将返回不匹配的部分或全部行.外连接类型 左外连接 LEFT OUTER JOIN ON 说明：显示左表不匹配行 右外连接 RIGHT OUTER JOINON 说明：显示右表不匹配行 全外连接 FULL OUTER JOINON 说明：显示全部记录（忽略匹配条件）5交叉连接 交叉连接在不带WHERE子句时，返回的是被连接的两个表所有记录的笛卡尔乘积，其使用CROSS JOIN关键字连接两个表 格式：SELECT 列名,列名FROM 表1 CROSS JOIN 表2注：cross 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 = 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 = 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 select * 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语言等）工具中的独立引擎。这两部分称为数据库PL/SQL和工具PL/SQL。两者的编程非常相似，都具有编程结构、语法和逻辑机制。2组成PL/SQL语言由以下几个部分组成 （1）数据定义语言（DDL-Data Definition Language）。数据定义语言用于执行数据库的任务，对数据库以及数据库中的各种对象进行创建、删除、修改等操作。基本的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）数字09（3）符号（、）、+、-、*、/、=、!、:、;、.、 、%、,、“ 、#、&、?、 。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属性，可以使用%Rowtype属性声明描述表的行数据的记录，对于用户定义的记录，必须声明自己的域。记录包含唯一的命名域，具有不同的数据类型。 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);注意：在定义记录时可以嵌套记录，也就是说，记录可以是另一个记录的一个组件。行中的列和记录中相对应地具有相同的名称和数据类型的一个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常用数据类型Varchar类型(最大长度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。注意，月份的表达要用英文单词的缩写格式。日期格式可以设置为中文格式。例如17-六月-2002对象类型 在多表操作的情况下，当多个表中的列要存储相同类型的数据时，要确保这些列具有完全相同的数据类型、长度和为空性（数据类型是否允许空值）。 语法格式： Create or replace type schema.type_name authidcurrent_user|definer as object(attribute1 datatype,attribure2 datatype,attributen datatypemethod1method2methodn) 说明 其中，schema：用户自定义类型所属方案。 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）不能使用那些只能在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); 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_expression1Elsif 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 if 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;此结构的循环在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之间是分隔符两个点号（.）。如果循环变量的值小于终值，则运行循环体内的语句，否则跳出循环，执行以下语句。每循环一次循环变量自动增加一个步长的值，直到循环变量的值超过终值，退出循环，执行后面的语句。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_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); 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);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_counter:=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 part 说明部分BEGIN SQL STATEMENT 语句序列 EXCEPTION 例外处理END procedure Name 过程名;注: datatype中可以有varchar2,不能是varchar2(10);3.语法分析 OR REPLACE 是一个可选的关键字，建议用户使用此关键字。如果过程已经存在，该关键字将重新创建过程，这样就不必删除和重新创建过程。关键字IS 和 AS均可， 它们本身没有区别。IS后面是一个完整的PL/SQL块，可以定义局部变量，但不能以DECLARE开始。局部变量在过程内部存放值。形式参数可以有三种模式：IN、OUT、IN OUT。如果没有为形式参数指定模式，那么默认的模式是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都将自动把其源代码存入数据库中，而编译代码只有在编译成功后才能存入数据库中。只有编译代码被存入到数据库的存储过程和函数才能被调用。 也就是说，如果你创建存储过程的语句是错误的，那么存储过程的源代码也会放入数据库，只是被显示为错误。 查看错误请用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 / 执行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_line(pname); 6 end; 7 /5.3带参存储过程（in out 参数） 创建存储过程 InoutTom向t1表插入一条记录SQL create or replace procedure inoutTom (pname in out varchar2) as begin select name into pname from t1 where id = pname; end; SQL /执行SQL declare 2 pname varchar2(30) := 1; 3 begin 4 dbms_output.put_line(pname); 5 inoutTom(pname); 6 dbms_output.put_line(pname); 7 end; 输出结果为:111注：赋值运算符为:=，用于给参数赋默认值6参数规则 如果形式参数是IN模式的参数，实际参数可以是一个具体的值或一个有值的变量； 如果形式参数是OUT模式的参数，实际参数必须是一个变量，当调用过程后，此变量就被赋值了。可以输出此变量的值来测试过程执行的结果。 如果形式参数是IN OUT模式的参数，则实际参数必须是一个预先已经赋值的变量。执行完过程后，该变量被重新赋值，可以输出此变量的值来测试过程执行结果。四.Oracle触发器 1.概念触发器是在事件发生时隐式地自动运行的PL/SQL程序块，不能接收参数，不能被调用。2.语法CREATE OR REPLACE TRIGGER trigger_name BEFORE | AFTER | INSTEAD OF triggering_event WHEN trigger_condition -限制条件FOR EACH ROW -行级触发trigger_body; -语句体3.DML触发器DML触发器是针对某个表进行DML操作时触发的。DML = Data manipulation language（数据操纵语言） 格式 CREATE OR REPLACE TRIGGER trigger_name BEFORE | AFTER INSERT | DELETE | UPDATE OF column,column ON table_name | view_name REFERENCING OLD AS old_name | NEW AS new_name FOR EACH ROW WHEN trigger_condition trigger_body;BEFORE: 指的是在将数据插入(修改,删除)到表之前,先运行触发器脚本.After : 指的是先运行触发器脚本,在将数据插入(修改,删除)到表中.3.1语句级触发器:语句级触发器在每个数据修改语句执行后只调用一次，而不管这一操作将影响到多少行。3.2多条件触发器CREATE OR REPLACE TRIGGER BEFORE insert or update or delete ON BEGIN IF INSERTING THEN END IF; IF DELETING THEN END IF; IF UPDATING THEN END IF; End;例:创建一个多条件触发器,用于实现记录用户对产品表进行的操作类型,操作时间,用户名(创建一个prod_operate_log 表记载信息,其中操作编号自动增长).1)创建prod_operate_log表CREATE TABLE prod_operate_log (OperID number, username varchar2(200),Operate_date timestamp,Operate_type varchar2(10);2)创建序列logID CREATE SEQUENCE logID START WITH 1INCREMENT BY 1NOMAXVALUECACHE 10;3)创建触发器表CREATE OR REPLACE TRIGGER record_prodoper BEFORE insert or update or delete ON productsDECLARE action_type varchar2(20);BEGIN IF INSERTING THEN action_type:= INSERT;END IF; IF DELETING THEN action_type:= DELETE;END IF; IF UPDATING THEN action_type:= UPDATE;END IF; INSERT INTO prod_operate_log VALUES(logID.nextval, USER, sysdate, action_type);End;3.3行级触发器行级触发器是按触发语句所处理的行激发的,可以引用受到影响的行值。创建触发器时采用关键字FOR EACH ROW这种访问是通过两个相关的标识符实现的 :old ：用于存放未进行修改前的数据 :new ：用于存放进行修改后的数据例:创建一个BEFORE触发器,使得在向ORDERS表中插入记录之前对ShippedDate字段进行检测,要求其值不允许为周六或周日,发货时间应在8-18点之间.否则将提示错误发货时间应为工作时间.CREATE OR REPLACE TRIGGER secure_shippeddate BEFORE INSERT ON ordersFor each rowBEGINIF (TO_CHAR(:new.shippeddate,DY) in (星期六,星期日)OR (to_number(TO_CHAR(:new.shippeddate,HH24) NOT BETWEEN 8 AND 18) THEN RAISE_APPLICATION_ERROR(-20500,发货时间应为工作时间);END IF;END;3.4使用“:old”和“:new”应注意的：在BEFORE类型行级触发器和AFTER类型行级触发器中使用这些标识符。在语句级触发器中不要使用这些标识符。在PL/SQL语句或SQL语句中，这些标识符前加上冒号（:）来引用它们。在行级触发器的WHEN条件中使用该标识符时，前面不要加冒号（:）.在BEFORE触发器中不能修改“:old”，在AFTER触发器中不能修改“:new” 五.视图定义:视图是一个虚拟表，视图并不在数据库中存储数据值，数据库中只在数据字典中存储对视图的定义。 优点 1.为用户集中数据，简化用户的数据查询和处理。 2.屏蔽数据库的复杂性，用户不必了解数据库的复杂性。 3.简化用户权限的管理，只授予用户使用视图的权限。 4.便于数据共享，多个用户不必都定义所需的数据。 5.可以重新组织数据，以便关联到其他应用中。 语法 CREATE OR REPLACE VIEW view_name (column_name1,column_name2 AS select_statement WITH CHECK OPTION WITH READ ONLY注意:这个俩个参数都需要放在主体语句的最后参数说明 CREATE OR REPALCE：用于创建和修改视图 WITH CHECK OPTION ：用于创建限制数据访问的视图 WITH READ ONLY ：用于创建只读视图视图的分类:(1)简单视图 指基于单个表并且不包含函数或表达式的视图，在该视图上可以执行DML语句（即可执行增、删、改操作）。 (2)复杂视图 指包含函数、表达式或者分组数据的视图，在该视图上执行DML语句时必须要符合特定条件。 注：在定义复杂视图时必须为函数或表达式定义别名Eg:select count(*) from t1. (3)连接视图 指基于多个表建立的视图，一般来说不会在该视图上执行INSERT、UPDATE、DELETE操作。除非运用到替代触发器才可以.(4)只读视图 指只允许进行SELECT操作的视图，在该视图时指定WITH READ ONLY选项。注：该视图上不能执行INSERT、UPDATE、DELETE操作。 (5)check约束视图 WITH CHECK OPTION用于在视图上定义CHECK约束,即在该视图上执行INSERT或UPDATE操作时,数据必须符合查询结果.例：create or replace view vt2check as select