Oracle课程小结

Oracle课程小结1. DualDual 是 Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中DUAL就是个一行一列的表，如果你往里执行insert、delete、truncate操作，就会导致很多程序出问题不要去执行drop dual的操作，否则会使系统不能用，数据库起不了DUAL是属于SYS schema的一个表,然后以PUBLIC SYNONYM的方式供其他数据库USER使用2. Null在Oracle中，值null被看作一个未知值，任何包含NULL值的算术运算都会得到结果NULL,。因此，下面的select语句返回null select 5+7+null+9 from dual;select null*9 from dual;空字符串：Oracle 把空字符串 () 当作 null, 因此 length() 的值是null, 而不是 0。一个null值与任何其它值比较，包括null值，结果都是null,因此，对NULL的=、!=、=、=等操作的结果都是未知的，也就算说，这些操作的结果仍然是NULL。由于NULL是未知，所以NULL AND NULL、NULL OR NULL、NULL AND TRUE和NULL OR FALSE的值都是未知的，这些的结果仍然是NULL。但NULL AND FALSE和NULL OR TRUE不一样declare v_result boolean; v_result1 boolean;begin v_result:=null and false; v_result1:=null or true; dbms_output.put(v_result=); dbms_output.put_line(case when v_result then true else false end);-false dbms_output.put(v_result1=); dbms_output.put_line(case when v_result1 then true else false end); -true end;3. 函数函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函，也可自定义函数数单行函数,多行函数（组函数）Lower(c)-小写转换返回c小写字符Concat(c1,c2) -连接字符串c1,c2均为字符串，函数将c2连接到c1的后面，如果c1为null,将返回c2.如果c2为null,则返回c1，如果c1、c2都为null，则返回null,如果c1,c2其中之一为数字,或都为数字Concat(10,10) Concat(10,10)Substr( string, start_position, length )-取字串参数1:string 要处理的字符串 参数2:截取字符串的开始位置 参数3:截取的字符串的长度(而不是字符串的结束位置) ,如果省略，则返回从start_position开始到string尾之间的字符串Select SUBSTR(Hello,2) From DualInstr(column|expression,string,m,n)-查询指定字符在字符串中的位置参数1:被搜索的字符串或者是表达式参数2:要查找的字符参数3:从什么位置开始查询参数4:这个字符出现的位置 比如说：第一次出现的位置，第二次出现的位置。如果此参数3为正，从左到右开始检索，如果此参数为负，从右到左检索最后两个参数是默认的是1和1SELECT instr(abc,d) FROM dual; - 返回 0 SELECT instr(syran mo,a,1,2) FROM dual; - 返回 0SELECT INSTR(oWo, W) FROM DUAL;-?Round( number, decimalplaces )-四舍五入函数参数1:要处理的数值(数值表达式) 参数2:四舍五入时取的小数的位数，不填则返回整数select round(123.456) from dual; 得到 123 select round(123.456, 0) from dual; 得到 123 select round(123.456, 1) from dual; 得到 123.5 select round(123.456, 2) from dual; 得到 123.46 select round(123.456, 3) from dual; 得到 123.456 select round(-123.456, 2) from dual; 得到 -123.46Mod(number,divisor) -返回一个number除以divisor的余数参数1:为被除数。 参数2:为除数。如果 divisor 为零，函数 MOD 返回值 为原来numberMOD(3, 2) 等于1 MOD(3, 0) 等于3 MOD(2,3) 等于2 To_char(date, format_model)-日期转为字符To_char(number, format_model)-数字转为字符格式化元素，用于显示字符形式的数字值: 9表示一个数,.打印一个小数点,打印一个千位指示 SELECT TO_CHAR(12345.529,999,999.99) FROM DUAL;- 12,345.53 Replace(text, search_string, replacement_string) -替换字符串从字符串(text)查找一个文本表达式(search_string)，如果找到，用指定的置换串(replacement_string)代替它如果replacement_string为空,那么所有的search_string都被移除。如果search_string为null,那么就返回原来的SELECT REPLACE(hello,ello,i) FROM DUAL; -hiTo_number(, , )是将一些处理过的按一定格式编排过的字符串变回数值型的格式SELECT TO_NUMBER(20)*5 FROM DUAL;-100To_date( string1, format_mask , nls_language )-转换为日期Format_mask yyyy-MM-dd HH24:mi:ssselect To_Date(1981-1-1,YYYY-MM-DD) from dual -1981-1-1Length(string)-返回字符串的长度select length(我) from dual -1select length(AB) from dual -2 4. dbms_output如果dbms_output.put_line的内容不能显示，需要在命令行中先敲入set serveroutput on;put和put_line 当使用过程put_line时,会自动在行的尾部追加行结束符;当使用过程put时,需要使用过程 new_line追加行结束符. set serverout on begin dbms_output.put_line(伟大的中华民族); dbms_output.put(中国); dbms_output.put(,伟大的祖国); dbms_output.new_line; end; / 伟大的中华民族 中国,伟大的祖国 new_line 该过程用于在行的尾部追加行结束符.当使用过程PUT时,必须调用NEW_LINE过程来结束行.5. PL/SQL 块DECLARE/*定义部分（可选）定义常量、变量、复杂数据类型、游标、例解*/BEGIN/*执行部分（必须）PL/SQL语句和SQL语句*/EXCEPTION/*异常处理部分（可选）处理运行错误*/END; /*块结束标记*/命名块:指具有特定名称标识的PL/SQL块，在PL/SQL块前使用加以标记 begin dbms_output.put_line(Hello,World!); begin dbms_output.put_line(Good); end; - end;-赋值操作符“:=”变量名:=值定义变量 变量名 数据类型StudScore NUMBER(5,2);StudName VARCHAR(20);定义常量添加关键字 CONSTANT 并赋值PI CONSTANT NUMBER(8,7):=3.;条件控制 有三种语句形式 IF-THENIF-THEN-ELSEIF-THEN-ELSIFIF THEN语句;END IF;IF THEN语句;ELSE语句;END IF;IF THEN语句;ELSIF THEN语句;ELSIF THEN语句;ELSE语句;END IF;CASE WHEN THEN 语句; WHEN THEN 语句; WHEN THEN 语句; Else 语句;End Case;CASE WHEN THEN 语句; WHEN THEN 语句; WHEN THEN 语句; Else 语句;End Case;有三种形式的 LOOP 语句 LOOPWHILE-LOOPFOR-LOOPLOOP语句; EXIT EXIT-WHENEND LOOP;EXIT 强制循环无条件完成 立即退出循环EXIT WHEN ;EXIT 语句时对 WHEN 子句中的条件进行判断 如果判定条件为 TRUE,则循环完成 WHILE LOOP语句;END LOOP;FOR IN Reverse lower_bound . higher_bound LOOP语句;END LOOP;6. 子程序过程用于执行某项操作函数用于执行某项操作并返回值存储过程语法CREATE OR REPLACE PROCEDURE (参数1, 参数N) IS|AS局部声明BEGIN 可执行语句；EXCEPTION 例外处理程序；END ;参数说明 变量名 IN|OUT|IN OUT IN：输入，接受值，默认模式OUT：输出，将值返回给子程序的调用程序IN OUT：输入输出，接受值并返回已更新的值对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。Create Or Replace Procedure ProcGetName(StudName in out varchar2) is Begin StudName:=substr(StudName,instr(StudName,明,2); End;/declare StudName varchar2(15):=明小明;begin ProcGetName(StudName); DBMS_OUTPUT.PUT_LINE(StudName);end;函数语法CREATE OR REPLACE FUNCTION (参数1, 参数N) RETURN datatype IS局部声明BEGIN可执行语句 ;EXCEPTION例外处理程序END ;仅接受 IN 参数Create Or Replace Function GetItemScore(Stand_Ans varchar2,Custor_Ans varchar2) return int is LenCustor int:=length(Custor_Ans); begin if LenCustorlength(stand_ans) or Custor_Ans IS NULL then return 0; end if; for i in 1.LenCustor loop if instr(stand_ans,substr(custor_ans,i,1)=0 then return 0; end if; end loop; return LenCustor;end;/Select GetItemScore(ABC,AC) From Dual; 7. Oracle SQL*Plus 命令set linesize 设置每行显示的长度SET LINESIZE 100start或 告诉SQLPLUS执行已经存储到sql文件中语句执行一个SQL脚本文件SQLstart file_nameSQL file_name我们可以将多条 sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可修改用户密码ALTER USER 用户名 IDENTIFIED BY 密码; oracle中逻辑运算符(not,and,or)及其优先级这3种逻辑运算符中，NOT运算符的优先级最高，而后是AND，最后是OROracle中的SPOOL命令是假脱机命令，可用于转储执行的命令和结果，其效果有点像DOS命令中的转向命令。SPOOL命令也可以用于转出数据库系统中的数据。 例如在SQL*Plus命令行中可以直接输入：SPOOL 假脱机文件名 启动假脱机操作。 关闭假脱机操作可以在SQL*Plus中输入：SPOOL OFF 例：假脱机命令的使用。 spool c:SpoolFile.txt set pagesize 300 set linesize 300 select * from emp; select * from dept; spool off； 执行完毕后，可以在C盘中找到SpoolFile.txt,打开后可以看到通过Spool假脱机命令转向到该文件中的内容。8. 记录类型定义一个 RECORD 类型 TYPE IS RECORD( fieldname1 NOT NULL : fieldnameN NOT NULL);属性的类型%TYPE引用数据库列%ROWTYPE代表表中的行%type和%rowtype 可以用来指定列的数据类型SName StudInfo.StudName%Type;Stud_Rec StudInfo%ROWTYPE;Type VIPStudRec is record ( VIPID Varchar2(15), VIPName varchar2(20) );声明记录类型变量StudRec VIPStudRec;引用字段的语法是recordname.columnname 用于给列赋值的语法是recordname.columnname: = expression; 语法 SELECT INTO FROM WHERE ;SELECT StudNo,StudName INTO StudRec FROM StudInfoWHERE StudNo=;fetch mycur into StudRec ;TID TeacherInfo.TeacherNo%Type;TName TeacherInfo.TeacherName%type;9. 游标CURSOR IS;CURSOR mycur is select TeacherNo,TeacherName From TeacherInfo Where TeacherBirthDay=To_Date(1981-2-5,YYYY-MM-DD);控制显式游标OPENFETCHCLOSE在使用 OPEN、 CLOSE 或 FETCH 语句引用这些语句之前必须对游标进行声明OPEN open mycur;FETCH INTO var1, , varN;ORFETCH INTO record_variable;fetch mycur into TID,TName;每个游标有四个属性可以用于访问游标的环境区域%NOTFOUND%FOUND%ROWCOUNT%ISOPEN循环取记录 while mycur%found loop dbms_output.put_line(TID|TNAME); fetch mycur into TID,TName; end loop;CLOSE ; close mycur;10. 表CREATE TABLE schema.table (column datatype DEFAULT expr, .);指定：表名列名、列数据类型和列的大小数据类型VARCHAR2(size)可变长度的字符数据长度可以到4000字节CHAR(size) 固定长度的字符数据NUMBER(p,s) 可变长度的数字数据DATE 日期和时间值NCHAR、NVARCHAR2，国家字符集，与环境变量NLS指定的语言集密切相关，使用方法和CHAR、VARCHAR2相同。在创建表时，为一个列指定一个默认值create table c (c1 char(10) DEFAULT 1111) alter table test add syd date default sysdate; alter table t modify(ccc default ccc ); alter table t modify(ccc default null); INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, Engineering, DEFAULT);UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10;约束类型NOT NULLUNIQUE PRIMARY KEYFOREIGN KEYCHECK 列级约束column CONSTRAINT constraint_name constraint_type,表级约束column,. CONSTRAINT constraint_name constraint_type (column, .),NOT NULL约束只能定义在列级,不能定义在表级.防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,. UNIQUE 约束用来保护一个表中的一个或者多个列没有任何两行在收到保护的列中具有重复的数据.ORACLE在唯一键列上自动生成一个唯一索引以实现唯一性CREATE TABLE employees( employee_id NUMBER(6) unique, last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL,. CONSTRAINT emp_email_uk UNIQUE(email);PRIMARY KEY 约束唯一键的所有特征都适用于主键约束,只是在主键列中不允许有NULL值CREATE TABLE departments( department_id NUMBER(4), email VARCHAR2(25) , location_id NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id);FOREIGN KEY 约束通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外部键可以指向主键或者其他表的唯一键.CREATE TABLE employees( employee_id NUMBER(6), . department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id),CHECK 约束检查在约束中指定的条件是否得到了满足.create table employees (sal number(7,2) constraint emp_sal_ck1 check (sal 0) ) create table y(id varchar2(7) check (ascii(substr(id,1,1) between 65 and 90 and substr(id,1,1) is not null and ascii(substr(id,2,1) between 65 and 90 and substr(id,2,1) is not null and ascii(substr(id,3,1) between 65 and 90 and substr(id,3,1) is not null and ascii(substr(id,4,1) between 65 and 90 and substr(id,4,1) is not null and ascii(substr(id,5,1) between 65 and 90 and substr(id,5,1) is not null );CREATE TABLE TT( department_id NUMBER(4) check(department_id like trj%), email VARCHAR2(25) PRIMARY KEY );CREATE TABLE TT( department_id NUMBER(4) , email VARCHAR2(25) PRIMARY KEY, STUDSEX VARCHAR2(2) CHECK (STUDSEX IN (男,女);添加一个新行到表中INSERT INTOtable (column , column.)VALUES(value , value.);INSERT INTOdepartmentsVALUES(100, Finance, NULL, NULL,SYSDATE,TO_DATE(FEB 3, 1999, MON DD, YYYY);改变表中的数据UPDATEtableSETcolumn = value , column = value, .WHERE condition;UPDATE copy_empSET department_id = 110WHERE department_name=HELLO;从表中删除行DELETE FROM tableWHERE condition; DELETE FROM departments WHERE department_name = Finance;11. 视图视图是一个 SQL 查询，它永久存在于数据库中，并被赋予了一个名称,视图是从基表中生成的虚拟表 CREATE OR REPLACE FORCE|NOFORCE VIEW view (alias, alias.) AS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY CONSTRAINT constraint;with check option:表名只有子查询检索的行才能被插入、删除、更新with read only:说明只能对基表中的行进行只读访问onstraint_name:指定with check option或read only约束的名称。CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50;SELECT *FROMsalvu50;内建视图是一个带有别名(或相关名) 的可以在SQL 语句中使用的子查询Top-N 分析SELECT ROWNUM as RANK, last_name, salary FROM (SELECT last_name,salary FROM employees ORDER BY salary DESC) AWHERE ROWNUM 8000GROUP BY department_id;14. 综合知识1学生信息表（StudInfo） 字段名称数据类型字段长度是否为空PK约束字段描述举例StudNoVarchar215Y学生学号StudNameVarchar220学生姓名李明StudSexChar2男,女学生性别男StudBirthDayDateY出生年月1980-10-3ClassNameVarchar250班级名称计本012课程信息表（CourseInfo） 字段名称数据类型字段长度是否为空PK字段描述举例CourseIDVarchar210Y课程编号A0101CourseNameVarchar250课程名称OracleCourseDescVarchar2100Y课程描述Oracle DB3学生成绩表（StudScoreInfo） 字段名称数据类型字段长度是否为空PK约束字段描述举例StudNoVarchar215Y学生学号CourseIDVarchar210Y课程编号A0101StudScoreNumber4,10,100学生成绩80.5注：一个学生可选修多门课，同一门课可由多个学生选修。1分别写出向以上各表插入一条记录的SQL语句。（记录为举例中的数据）2写出更新学生成绩表（StudScoreInfo）中学号为，课程编号为A0101的成绩为85.5的SQL语句。3写出在课程信息表（CourseInfo）中删除课程编号为B0101的SQL语句。4写出查询姓李的学生基本信息的SQL语句。5在学生成绩表（StudScoreInfo）中，写出将课程编号为A0101成绩从高到低排序的SQL语句。6写出在学生成绩表（StudScoreInfo）中查询学生成绩大于等于90小于等于100或者大于等于70小于等于80的SQL语句。7在学生成绩表（StudScoreInfo）中，写出统计各学生总分、平均分、课程门数、最高分、最低分的SQL语句。8写出统计学生平均分大于80的SQL语句。9写出查询平均分最高的5个学生成绩统计信息（包括学号、平均分字段）中的SQL语句。10写出查询重修（学生成绩小于60即为重修）15门以上的学生基本信息的SQL语句。11综合利用所学知识，用SQL语句实现将各学生平均分（需统计）按以下等级输出，包括学生学号（StudNo）、平均分（PScore）、成绩等级（ScoreLevel）字段。平均分（PScore） 成绩等级（ScoreLevel）PScore =90优秀80= PScore 90良好70= PScore 80中等60= PScore 70及格PScore 60不及格12创建一个存储过程，使用游标循环将班级名称为计本01的学生按出生日期升序排，要求在SQL*PLUS环境中显示排序后的序号，学号，姓名，出生日期字段。