ORACLE讲解内容

第一部分：ORACLE基础一、oracle体系结构要了解oracle体系结构必须先了解两个基本的概念:数据库和实例.1、数据库：数据库(database)是一个数据集合.oracle数据库都将其数据存放在数据文件中.在其内部,构数据库结数据对文件的逻辑映射,使不同的数据分开存储,这些逻辑划分称为表空间。①表空间表空间(tablespace)是数据库的逻辑划分,每个数据库至少有一个表空间,叫做系统表空间(system表空间).一个表空间只能属于一个数据库。每个表空间由同一个磁盘上的一个或多个文件组成,这些文件称为数据文件。创建表空间的语句： createtablespaceorarodatafile'D:\OracleData\oraro.dat'size512Mautoextendon;增加数据文件： altertablespaseoraroadddatafile'D:\OracleData\oraro1.dat'size512M;删除表空间： droptablespaceoraroincludingcontentsanddatafiles;②数据文件每个表空间由同一个磁盘上的一个或多个文件组成,这些文件叫做数据文件(datafile),数据文件只能属于一个表空间.数据文件创建后可以改变大小.创建新的表空间需要创建新的数据文件.数据文件一旦加入到表空间中,就不能从表空间中移走,也不能与其他表空间发生联系.数据库必须的三类文件是datafile,controlfile,redologfile.其他文件prameterfile,passwordfile,archivedlogfiles并不是数据库必须的,他们只是辅助数据库的.查看数据库的物理文件组成:1)查看数据文件:SQL>select*fromv$datafile;2)查看控制文件:SQL>select*fromv$controlfile;3)查看日志文件:SQL>select*fromv$logfile;③控制文件　　控制文件是一个很小的二进制文件，用于记录数据库的物理结构。一个控制文件只属于一个数据库。创建数据库时，创建控制文件。当数据库的物理结构改变的时候，Oracle会更新控制文件。用户不能编辑控制文件，控制文件的修改由Oracle完成。　　数据库的启动和正常运行都离不开控制文件。启动数据库时，Oracle从初始化参数文件中获得控制文件的名字及位置，打开控制文件，然后从控制文件中读取数据文件和联机日志文件的信息，最后打开数据库。数据库运行时，Oracle会修改控制文件，所以，一旦控制文件损坏，数据库将不能正常运行。　　控制文件记录的数据库信息如下：　　数据库名称（DatabaseName）　　创建数据库的时间戳　　数据文件的名字及位置　　联机日志文件的名字及位置　　表空间信息　　日志历史记录（LogHistory）　　归档日志的信息　　备份信息　　当前的日志序列号（LogSequenceNumber）　　校验点信息（Checkpoint）　　控制文件的大小由下面的几个参数决定：·MAXLOGFILES·MAXLOGMEMBERS·MAXLOGHISTORY·MAXDATAFILES·MAXINSTANCES　　当增加、重命名、删除一个数据文件或者一个联机日志文件时，Oracle服务器进程（ServerProcess）会立即更新控制文件以反映数据库结构的这种变化。所以，Oracle总是告诫我们，在数据库的结构发生变化后，要备份控制文件。日志写进程LGWR负责把当前日志序列号记录到控制文件中。校验点进程CKPT负责把校验点的信息记录到控制文件中。归档进程负责把归档日志的信息记录到控制文件中。初始化参数CONTROL_FILES的值记录控制文件的位置。通常，DBA应该镜像控制文件，把每个控制文件分布到不同的物理磁盘，发生灾难时，即使其中一个控制文件损坏，数据不会丢失，也不会使整个数据库陷于瘫痪。2、实例通俗的讲实例就是操作oracle数据库的一种手段.数据库实例也称作服务器,是用来访问数据库文件集的存储结构及后台进程的集合.一个数据库可以被多个实例访问(称为真正的应用群集选项).决定实例的大小及组成的各种参数或者存储在名称init.ora的初始化文件中,或者隐藏在数据库内部的服务器参数文件中.通过spfile引用该文件,spfile存储在spfile.ora文件中.实例启动时读取初始化文件,数据库系统管理员可以修改该文件,对初始化文件的修改只有在下次启动时才有效.3、监听程序监听器是Oracle基于服务器端的一种网络服务，主要用于监听客户端向数据库服务器端提出的连接请求。既然是基于服务器端的服务，那么它也只存在于数据库服务器端，进行监听器的设置也是在数据库服务器端完成的。二、PL/SQLPL/SQL也是一种程序语言，叫做过程化SQL语言（ProceduralLanguage/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。1PL/SQL基本概念　　1.1PL/SQL的作用　　使用PL/SQL可以编写具有很多高级功能的程序，虽然通过多个SQL语句可能也能实现同样的功能，但是相比而言，PL/SQL具有更为明显的一些优点：·能够使一组SQL语句的功能更具模块化程序特点；·采用了过程性语言控制程序的结构；·可以对程序中的错误进行自动处理，使程序能够在遇到错误的时候不会被中断；·具有较好的可移植性，可以移植到另一个Oracle数据库中；·集成在数据库中，调用更快；·减少了网络的交互，有助于提高程序性能。　　通过多条SQL语句实现功能时，每条语句都需要在客户端和服务端传递，而且每条语句的执行结果也需要在网络中进行交互，占用了大量的网络带宽，消耗了大量网络传递的时间，而在网络中传输的那些结果，往往都是中间结果，而不是我们所关心的。而使用PL/SQL程序是因为程序代码存储在数据库中，程序的分析和执行完全在数据库内部进行，用户所需要做的就是在客户端发出调用PL/SQL的执行命令，数据库接收到执行命令后，在数据库内部完成整个PL/SQL程序的执行，并将最终的执行结果返馈给用户。在整个过程中网络里只传输了很少的数据，减少了网络传输占用的时间，所以整体程序的执行性能会有明显的提高。1.2PL/SQL分类1)DDL语句(数据定义语言)DataDefineLanguage CREATETABLE创建表ALTERTABLE更改表DROPTABLE删除表TRUNCATETABLE清空表RENAMEtable1TOtable2改表名建立表createtableclass(--班级表classidnumber(2)primarykey,cnamevarchar2(20)notnull);createtablestudent(--学生表sidnumber(4)primarykey,--学号namevarchar2(10)notnull,--姓名sexchar(2)check(sexin('M','F')),--性别birthdaydate,--生日classidnumber(2)referencesclass(classid)--班级 );外键引用的列一定是主键或有unique约束的列altertablestudentadd(agenumber(3));2)DML语句(数据操作语言)DataManupilateLanguageUPDATE更新存储在表中的数据DELETE删除行SELECT查询表INSERT插入数据特点:对数据起作用的这些语句的修改是在内存中发生的要想改动存入库中必须要commit语句deletefromstudent：只删除数据，速度慢，数据在commit之前可以恢复truncatetablestudent;删除的时候不检查表中现有记录，速度快，数据不能恢复，可以自动释放所清空记录之前所占的表空间3)TCL(事务控制语句)TransactionControlLanguageCOMMIT提交修改保存到数据库中ROLLBACK回滚取消内存中的改动SAVEPOINT保存点分解事务的把事务变小4)DCL(数据控制语句)DataControlLanguageGRANT赋予权限 --创建用户createuserzhangsanidentifiedbyoraro;--授权grantcreatetable,createtablespacetozhangsan;grantselect,delete,insertontb_bulletintozhangsan;grantconnecttozhangsan;--删除dropuserzhangsan;REVOKE收回权限 revokeselectontb_bulletinfromzhangsan;　　1.3PL/SQL程序的基本结构　　PL/SQL块由四个基本部分组成：声明、执行体开始、异常处理、执行体结束。　　下面是四个部分的基本结构：　　DECLARE—可选部分　　变量、常量、游标、用户定义异常的声明　　……　　BEGIN—必要部分　　SQL语句和PL/SQL语句构成的执行程序　　……　　EXCEPTION—可选部分　　程序出现异常时，捕捉异常并处理异常　　……　　END；—必须部分在数据库执行PL/SQL程序时，PL/SQL语句和SQL语句是分别进行解析和执行的。PL/SQL块被数据库内部的PL/SQL引擎提取，将SQL语句取出送给Oracle的SQL引擎处理，两种语句分别在两种引擎中分析处理，在数据库内部完成数据交互、处理过程。 declarev_goods_typevarchar2(100):='杯具';--商品类别v_rowint;--数据行数beginselectcount(*)intov_rowfromtb_goods_typewheretype_name=v_goods_type;ifv_row>0thendbms_output.put_line('商品类型已存在');elseinsertintotb_goods_type(type_id,type_name)values(seq_goods_id.nextval,v_goods_type);commit;dbms_output.put_line('商品类型添加成功');endif;end;2PL/SQL的变量　　就像其他的程序语言一样，变量是在程序中出现最频繁的名词，在PL/SQL中的学习中首先需要了解变量的一些基本概念和使用方法。　　PL/SQL程序包括了四个部分，在四个部分中，声明部分主要用来声明变量并且初始化变量，在执行部分可以为变量赋新值，或者在表达式中引用变量的值，在异常处理部分同样可以按执行部分的方法使用变量。另外，在PL/SQL程序使用时可以通过参数变量把值传递到PL/SQL块中，也可以通过输出变量或者参数变量将值传出PL/SQL块。　　在定义变量、常量标识符时需要注意下面的一些基本规则：1)定义的标识符名称应该遵循命名规则，在后面将会提到主要的命名规则；2)在声明常量和变量的时候可以为其设置初始化值，也可以强制设置notnull；3)可以使用赋值运算符（：=）或DEFAULT保留字来初始化标识符，为标识符赋初始值；4)在声明标识符时，每行只能声明一个标识符。　　在PL/SQL中主要使用下面三种类型的变量（或者常量）：1)简单变量；2)复合（组合）变量；3)外部变量。　　三种变量分别用于存放不同特性的数据。3PL/SQL的基本语法　　在写PL/SQL语句时，必须遵循一些基本的语法，下面是PL/SQL程序代码的基本语法 要求 对教师党员的评价套管和固井爆破片与爆破装置仓库管理基本要求三甲医院都需要复审吗 ：　　1.语句可以写在多行，就像SQL语句一样；　　2.各个关键字、字段名称等等，通过空格分隔；　　3.每条语句必须以分号结束，包括PL/SQL结束部分的END关键字后面也需要分号；　　4.标识符需要遵循相应的命名规定；　　（1）名称最多可以包含30个字符；　　（2）不能直接使用保留字，如果需要，需要使用双引号括起来；　　（3）第一个字符必须以字母开始；　　（4）不要用数据库的表或者科学计数法表示；　　还有一些语法相关的规则：　　1.在PL/SQL程序中出现的字符值和日期值必须用单引号括起；　　2.数字值可以使用简单数字或者科学计数法表示；　　3.在程序中最好养成添加注释的习惯，使用注释可以使程序更清晰，使开发者或者其他人员能够很快的理解程序的含义和思路。在程序中添加注释可以采用：　　（1）/*和*/之间的多行注释；（2）以--开始的单行注释。三、约束在数据库中使用约束(constraints)是为了在该数据库中实施所谓的"业务规则"其实就是防止非法信息进入数据库,满足管理员和应用开发人员所定义的规则集.ORACLE使用完整性约束(integrityconstraints)防止不合法的数据写入数据库,管理员和开发人员可以定义完整性规则,增强商业规则,限制数据表中的数据.如果一个DML语句执行的任何结果破坏了完整性约束,ORACLE就会回滚语句,返回错误信息.约束是通过使用CREATETABLE或ALTERTABLE语句生成的.(建立表时或者表建立后修改都可)如果相关的约束定义在单列上,可以在列这一级指定约束的定义;多列约束必须定义在数据表级,相关的列要在括号中指定,用逗号分隔.如果没有为约束提供一个名字,那么ORACLE会分配一个系统生成的唯一名字,以SYS_开头,你可以使用关键字CONSTRAINTS后面跟随相关的约束名字来为约束指定名字.ORACLE支持五种类型的完整性约束NOTNULL(非空)--防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值.CHECK(检查)--检查在约束中指定的条件是否得到了满足.UNIQUE(唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的.PRIMARYKEY(主键)--用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束.POREIGNKEY(外部键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外部键可以指向主键或者其他表的唯一键.约束定义存储在数据字典中,查询USER_CONSTRAINTS可以获得相关信息.定义约束 CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][column_constraint],...[table_constraint][,...]);e.g.CREATETABLEemployees(employee_idNUMBER(6),first_nameVARCHAR2(20),...job_idVARCHAR2(10)NOTNULL,CONSTRAINTSemp_emp_id_pkPRIMARYKEY(EMPLOYEE_ID));列级的约束定义 column[CONSTRAINTconstraint_name]constraint_type,表级约束的定义 column,..[CONSTRAINTconstraint_name]constraint_type(column,...)NOTNULL约束只能定义在列级,不能定义在表级. e.g.CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,//没有指定名字,将用系统SYS_命名salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATECONSTRAINTemp_hire_date_nnNOTNULL);//用户定义了名字UNIQUE约束用来保护一个表中的一个或者多个列没有任何两行在收到保护的列中具有重复的数据.ORACLE在唯一键列上自动生成一个唯一索引以实现唯一性 e.g.CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,CONSTRAINTemp_email_ukUNIQUE(email));PRIMARYKEY约束唯一键的所有特征都适用于主键约束,只是在主键列中不允许有NULL值.一个表只能有一个主键. e.g.CREATETABLEdepartments(department_idNUMBER(4),department_nameVARCHAR2(30)CONSTRAINTdept_name_nnNOTNULL,manager_idNUMBER(6),location_idNUMBER(4),CONSTRAINTdept_id_pkPRIMARYKEY(department_id));FOREIGNKEY约束用来保护一个表中的一个或者多个列,它会通过一个主键主键或者唯一键保证对于每个非NULL值在数据库的其他地方都有一个数据可用.这个外部键就是在生成此约束的表(子表)中的一个或多个列,在父级表和子表中,相关列的数据类型必须匹配.外部键列和引用键(referencekey)列可以位于相同的表中(自引用完整性约束). e.g.CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,deparment_idNUMBER(4),CONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id),CONSTRAINTemp_email_ukUNIQUE(email));上例中是在表级定义外部键约束,如果在列级定义,不同的是: CREATETABLEemployees(...,department_idNUMBER(4)CONSTRAINTemp_deptid_fkREFERENCESdepartments(department_id),...);//没有关键字FOREIGNKEYFOREIGNKEY约束还有两个关键字是ONDELETECASCADE--当删除所引用的父表记录时,删除子表中相关的记录ONDELETESETNULL--与上面不同的是删除时,转换子表中相关记录为NULL值默认情况下,如果没有指定以上两个中任一,则父表中被引用的记录将不能被删除.CHECK约束 [CONSTRAINT<constraintname>]CHECK(<condition>)这里CHECK子句中的CONDITION应该求值为一个布尔值结果,并且可以引用相同行中其他列的值;不能包含子查询,序列,环境函数(SYSDATE,UID,USER,USERENV)和伪列(ROWNUM,LEVEL,CURRVAL,NEXTVAL),一个列上可以定义多个CHECK约束,如果所定义的条件为FALSE,则语句将回滚. CREATETABLEemployees(...,salaryNUMBER(8,2)CONSTRAINTemp_salary_minCHECK(salary>0),...);添加约束 ALTERTABLEemployeesADDCONSTRAINTemp_manager_fkFOREIGNKEY(manager_id)REFERENCESemployees(employee_id);删除约束 ALTERTABLEemployeesDROPCONSTRAINTemp_manager_fk;ALTERTABLEdepartmentsDROPPRIMARYKEYCASCADE;//由于departments被employees.department_id引用了对于NOTNULL约束,用ALTERTABLEMODIFY子句来删除 ALTERTABLEemployeesMODIFYlast_nameNULL;关闭约束 ALTERTABLEemployeesDISABLECONSTRAINTemp_emp_id_pkCASCADE;//如果没有被引用则不需CASCADE关键字当你生成一个约束时,约束自动打开(除非你指定了DISABLE子句0,当用DISABLE关闭UNIQUE或者PRIMARYKEY约束时,ORACLE会自动删除相关的唯一索引,再次打开时,ORACLE又会自动建立的.打开约束 ALTERTABLEemployeesENABLECONSTRAINTemp_emp_id_pk;//注意,打开一个先前关闭的被引用的主键约束,并不能自动打开相关的外部键约束可以从USER_CONSTRAINTS表和USER_CONS_COLUMNS视图中查询约束的信息 SELECTconstraint_name,constraint_type,search_conditionFROMuser_constraintsWHEREtable_name='EMPLOYEES';四、序列 序列是一数据库对象，利用它可生成唯一的整数。一般使用序列自动地生成主键值。一个序列的值是由特别的Oracle程序自动生成，因而序列避免了在运用层实现序列而引起的性能瓶颈。Oracle序列允许同时生成多个序列号，而每一个序列号是唯一的。当一个序列号生成时，序列是递增，独立于事务的提交或回滚。容许 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 缺省序列，不需指定任何子句。该序列为上升序列，由1开始，增量为1，没有上限。   1）建立序列命令 CREATESEQUENCE[user.]sequence_name   [incrementbyn]   [startwithn]   [maxvaluen|nomaxvalue]   [minvaluen|nominvalue];      INCREMENTBY：指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。   STARTWITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。   MAXVALUE：指定序列可生成的最大值。   NOMAXVALUE：为升序指定最大值为1027，为降序指定最大值为-1。   MINVALUE：指定序列的最小值。   NOMINVALUE：为升序指定最小值为1。为降序指定最小值为-1026。2）更改序列命令　　 ALTERSEQUENCE[user.]sequence_name　　[INCREMENTBYn]　　[MAXVALUEn|NOMAXVALUE]　　[MINVALUEn|NOMINVALUE]；　　修改序列可以：修改未来序列值的增量。　　设置或撤消最小值或最大值。　　改变缓冲序列的数目。　　指定序列号是否是有序。　　注意：　　1，第一次NEXTVAL返回的是初始值　　2，可以alter除start至以外的所有sequence参数。如果想要改变start值，必须dropsequence再re-create.　　3）删除序列命令　　DROPSEQUENCE[user.]sequence_name；　　用于从数据库中删除一序列。　　4）牛刀小试　　4.1）创建一个序列号的语句： --Createsequence　　createsequenceNCME_QUESTION_SEQ　　minvalue1　　maxvalue999999999999　　startwith1　　incrementby1　　cache20;　　　　//////////////////////////////　　4.2）SQL中取序列号的用法： SELECTNCME_QUESTION_SEQ.nextvalFROMdual　　SELECTNCME_QUESTION_SEQ.CURRVALFROMdual　　SELECTNCME_QUESTION_SEQ.nextvalFROMdualSELECTNCME_QUESTION_SEQ.CURRVALFROMdual　　　　注意：在使用序列的时候，有时需要有用户名，就像这样：　　 insertintosystem.CONSERVATOR(CONSERVATORNAME,CONPASS,CONTRUENAME,CONSEX,CONID)values('JG','123456','000',0,system.CONID.nextval);第二部分：查询、函数、游标1、查询为什么使用子询？查询成绩高于平均分的学生的信息·第一种方式 declarev_avg_scorenumber;beginselectavg(score)intov_avg_scorefromtb_mark;selectstunamefromtb_studentstuinnerjointb_markmkonstu.stuno=mk.stunowherescore>v_avg_score;end;·第二种方式 selectstunamefromtb_studentstuinnerjointb_markmkonstu.stuno=mk.stunowherescore>(selectavg(score)fromtb_mark);子查询在WHERE语句中的一般用法：SELECT…FROM表1WHERE字段1>(子查询)外面的查询称为父查询，括号中嵌入的查询称为子查询UPDATE、INSERT、DELETE一起使用，语法类似于SELECT语句将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个·Rownum,rowidRownum查询结果中的行号，ROWID是数据的物理地址2、函数函数是数据库中常用对象之一，与其他编程语言的函数一样，ORACLE中的函数也必须返回一个值，这也是函数区别于存储过程的重要特征(函数类似于JAVA中的方法)。简介：1、函数与功能的划分：最大化函数复用；2、函数的参数：函数可以没有传入参数，但是如果有的话必须明确数据类型；3、函数的返回值：必须有返回值，必须在结尾处用return返回，类型可以是字符串，字符型，数组或对象类型；创建函数：1、创建函数： createorreplacefunctionget_hello_msgreturnvarchar2asbeginreturn'helloworld';endget_hello_msg;2、在数据字典中查看函数的信息： selectobject_name,object_type,statusfromuser_objectswherelower(object_name)='get_hello_msg'3、查看函数的返回值： declaremsgvarchar2(20);beginmsg:=get_hello_msg;dbms_output.put_line(msg);end;注：ORACLE中函数名后的括号在无参数情况下可以省略；带参数的函数： --***********************************************************--版权所有:(C)201,南京橙红信息科技有限公司1。--数据库版本:Oracle10GR2--内容摘要:判断指定表指定列的值是否存在--创建人:里斯--创建日期:2011.9.5--备注:--***********************************************************createorreplacefunctionfunc_Exists(v_table_namevarchar2,v_column_namevarchar2,v_valuevarchar2)returnbooleanasv_sqlvarchar2(1000);--保存SQL命令v_rowsint;--行数v_existsboolean:=false;beginv_sql:='selectcount(*)rfrom'||v_table_name||'where'||v_column_name||'='''||v_value||'''';executeimmediatev_sqlintov_rows;if(v_rows>0)thenv_exists:=true;endif;returnv_exists;exceptionwhenothersthendbms_output.put_line('执行SQL出错');dbms_output.put_line(v_sql);end;3、游标游标可以增强SQL语句的功能，它可以对SQL语句的处理进行显式的控制，可以非常方便地底帮助我们从数据库中提取多行数据，然后对每一行数据进行单独处理。游标是一种PL/SQL的控制结构，它可以命名一个工作区来存取该工作区的存储信息。游标分为显式游标和隐式游标，PL/SQL会为所有的DML句创建一个游标，包含那些只返回一条语句的查询。对于返回多条记录的查询语句，我们应该显式地声明一个游标来逐个处理这些数据。 DECLAREv_enamescott.EMP.ENAME%TYPE;--定义v_name为列ename同一种类型v_salaryscott.EMP.sal%TYPE;--定义v_name为列sal同一种类型CURSORc_empISSELECTename,salFROMscott.emp;BEGINOPENc_emp;LOOPFETCHc_empINTOv_ename,v_salary;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE('SalaryofEmployee'||v_ename||'is'||v_salary);endloop;END;关于SQL编程中的特殊变量类型（type和rowtype） declarev_rowscott.emp%rowtype;beginselectempno,ename,job,mgr,hiredate,sal,comm,deptnointov_rowfromscott.empwhereempno=7521;dbms_output.put_line(v_row.ename);end;第三部分高级查询、存储过程Casewhen用法 selectgi.goods_name,ogi.price,ogi.quantity,casegi.statuswhen0then'未处理'when1then'缺货'when2then'已发货'when3then'收货'when4then'退货'endasst,order_time,order_accountfromtb_order_goods_infoogi,tb_goods_infogi,tb_order_infooiwhereogi.goods_id=gi.goods_idandoi.order_id=ogi.order_idandcustomer_id=11、复杂查询班级表：tb_class Class_id Number(4) 班级ID 主键 Class_name Varchar2(30) 班级名称 NOTNULL Grade_tpye_code Char(1) 归属年级编码 NOTNULL约定用1-6年级学生表：tb_student Student_id Number(8) 学号 主键 Student_name Varchar2(50) 姓名 NOT_NULL Class_id Number(4) 归属班级 NOT_NULL Sex Char(1) 性别 NOT_NULL0：男1：女科目表tb_subject Subject_id Number(8) 科目ID 主键如：1000100 Subject_name Varchar2(50) 科目名称 NOT_NULL,语文，数学 Extend_tag Char(1) 是否必选 NOT_NULL0：必选1：否考试结果信息表t_score Student_id Number(8) 学号 NOT_NULL Subject_id Number(8) 科目ID NOT_NULL Score Number(5,2) 分数 NOT_NULL Exam_no Number(4) 考试批次 NOT_NULL Release_time date 发布时间 NOT_NULL注：每个批次的考试，都是按各年级统一参加考试，各年级必选科目固定且相同问题1：假如在t_score表中存在重复的记录，请用一条SQL语句删除重复的记录。第一步：查询重复的记录 selectsubject_id,student_id,scorefromt_scoregroupbysubject_id,student_id,scorehavingcount(subject_id)>1第二步：通过rowid列找出两列中的一列 selectmax(rowid)id,subject_id,student_id,scorefromt_scoregroupbysubjectid,student_id,scorehavingcount(subject_id)>1第三步：根据rowid删除重复的列 deletefromt_scorewhererowidin(selectmax(rowid)idfromt_scoregroupbysubject_id,student_id,scorehavingcount(subject_id)>1)2：写一个语句删除t_score非必选科目的记录 deletefromt_socrewhereexists(select1fromt_scoresc,t_subjectsbwheresc.subject_id=sb.subject_idandExtend_tag=0);3：找出在相同批次考试中，相同年级，相同科目，成绩相同的记录 selectgrade_tpye_code,subject_name,scorefromt_socresc,t_studentst,t_subjectsb,t_classcswherecs.class_id=st.class_idandst.student_id=sc.student_idandsb.subjecct_id=st.sbubject_idgroupbygrade_tpye_code,subject_name,score,student_name,exam_nohavingcount(grade_tpye_code)>14：统计以下信息；每个必选科在各考试批次中的年级最高分，年级平均分，各班级最高分 --年级最高分平均分selectGrade_tpye_code,max(score)grade_max_score,avg(score)grade_avg_scorefromt_scorescinnerjoint_studentstonsc.student_id=st.student_idinnerjoint_classcs.class_id=t_student.class_idgroupbycs.Grade_tpye_code,exam_no--班级最高分平均分selectcs.class_name,max(score)class_max_scorefromt_scorescinnerjoint_studentstonsc.student_id=st.student_idinnerjoint_classcs.class_id=t_student.class_idgroupbycs.class_id,cs.class_name,exam2、存储过程存储过程是PL/SQL语句的集合，类似于方法，存储过程不必返回值，但可以有参数；存储过程有三种参数：输入参数输出参数输入输出参数存储过程的优点：1、提高数据库执行效率在编程语言中，使用SQL接口更新数据就意味着要频繁的连接数据库，但连接数据库是一个很耗时且消耗资源的的操作，这样一来对于数据库的性能就完全得不到保证，如果使用存储过程来更新，则可大大的提高效率；2、提高安全性存储过程是以对象形式保存在数据库中，这样一来就可以对存储过程分配权限来保证安全性；而且这样一来就使操作从编程语言转移到了数据库，只要数据库不被破坏，那么这些操作就能够保存下来；3、可复用4、节省网络流量创建存储过程： createorreplaceprocedureproc_update_studentsasbeginupdatestudentssetSTUDENT_AGE=18;commit;endupdate_students;查找存储过程在数据字典中的信息： selectobject_name,object_type,statusfromuser_objectswherelower(object_name)='update_students'查看存储过程在数据库中的存储形式： select*fromuser_sourcewherelower(name)='update_students'执行存储过程： Executeupdate_students;存储过程的参数IN参数：指传入参数，他由调用者传递给存储过程之后，存储过程在执行过程中，无论怎样使用该参数，都无法改变该参数的值，该参数对于存储过程来说，是只读的，例如：在存储过程update_students中，可以传入一个年龄参数，用以标识需要将学生年龄修改为多少岁。如上所示，如果去掉注释，那么ORACLE会报错，提示传入参数不能被赋值； createorreplaceprocedureupdate_student(in_ageinnumber)asbeginupdatestudentssetstudent_age=in_age;--in_age:=in_age+10;commit;endupdate_student;--调用存储过程beginupdate_student(20);end;OUT参数：函数可以有返回值，但存储过程没有显式的返回值，但是可以通过OUT参数获得存储过程的处理结果， createorreplaceprocedureupdate_student_out(in_ageinnumber,out_ageoutnumber)beginupdatestudentssetstudent_age=in_age;selectstudent_ageintoout_agefromstudentswherestudent_id=1;commit;endupdate_student_out;--调用存储过程declareupdated_agenumber;beginupdate_student_out(20,updated_age);dbms_output.put_line(updated_age);end;INOUT参数：既可以做输入参数也可以做输出参数，因此，INOUT参数一般用于对参数的值进行处理，并处理结果输出，一个典型实例就是交换两个变量的值。 createorreplaceprocedureswap(in_out_param1inoutnumber,in_out_param2inoutnumber)asbegindeclaretempnumber;begintemp:=in_out_param1;in_out_param1:=in_out_param2;in_out_param2:=temp;end;end;--调用存储过程declareparam1number:=25;param2number:=35;beginswap(param1,param2);dbms_output.put_line('param1='||param1);dbms_output.put_line('param2='||param2);end;存储过程的参数默认值只针对IN参数，OUT和INOUT参数没有默认值； createorreplaceprocedureinsert_student(in_student_idinnumber,in_student_nameinvarchar2,in_student_ageinnumberdefault20)asbegininsertintostudentsvalues(in_student_id,in_student_name,in_student_age);commit;endinsert_student;--执行execinsert_student(14,'test_name')；存储过程实例createtablet_money(idintprimarykey,amountintnotnull);//id是序列号，amount是金额对如下数据进行查询：+----+--------+|id|amount|+----+--------+| 1|     2|| 2|     2|| 3|     3|| 4|     5|| 5|     2|| 6|     8|| 7|     1|| 8|     2|| 9|     3||10|     3|+----+--------+从表中选取若干条数据，使得被选出来的三条数据的amount（金额）字段之和等于10，要求存储过程能返回被选取出来的数据的序列号。算法举例：2+3+5=10返回序列号：1，3，4    createorreplaceprocedureproc_get_numberastypearyistableofnumberindexbybinary_integer;ary_idary;ary_amountary;iint:=1;cursorcur_moneyisselectid,amountfromt_money;beginopencur_money;fetchcur_moneyintoary_id(i),ary_amount(i);loopexitwhencur_money%notfound;i:=i+1;fetchcur_moneyintoary_id(i),ary_amount(i);endloop;forjin1..ary_amount.countloopforkin1..ary_amount.countloopforlin1..ary_amount.countloopif(ary_amount(k)+ary_amount(j)+ary_amount(l))=10thendbms_output.put_line('值分别是：'||ary_amount(k)||''||ary_amount(j)||''||ary_amount(l));dbms_output.put_line('id分别是：'||ary_id(k)||''||ary_id(j)||''||ary_id(l));endif;endloop;endloop;endloop;closecur_money;exceptionwhenothersthendbms_output.put_line(i);end;