Oracle课程小结

1、Oracle课程小结1. DualDual是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表 的Select语句块中DUAL就是个一行一列的表，假如你往里履行insert、delete tmncate操作,就 会导致很多法度榜样出问题不要去履行drop dual的操作，不然会使体系不克不及用，数据库起不了DUAL是属于SYS schema的一个表,然后以PUBLIC SYNONYM的方法供其他 数据库USER应用2. NulIS Oracle中，值null被看作一个未知值，任何包含NULL值的算术运算都邑获 得成果NULL,。是以，下面的select语句返回nullsele

2、ct 5+7+null+9 from dual;select null*9 from dual;空字符: Oracle把空字符串(”)算作null,是以length(“)的值是null,而不是Oo一个null值与任何其它值比较，包含null值，成果都是null,是以，对NULL的 二、！二、v、=、v二等操作的成果都是未知的，也就算说，这些操作的成果仍 然是NULL。因为 NULL 是未知，所以 NULL AND NULL、NULL OR NULL、NULL AND TRUE 和NULL OR FALSE的值都是未知的，这些的成果仍然是NULL。但 NULL AND FALSE 和 NULL

3、OR TRUE 不一样declare vresult boolean; vresult 1 boolean;beginv_result:=null and false;v_resultl:=null or true;dbms_output.put(,v_result=,);dbms_output.put_Iine(case when v_result then rue* else false* end); falsedbms_output.put( v_result 1 =*); dbms_output.put_line(case when v_resultl then rue* else f

4、alse end);true end;3 函数函数是一种有零个或多个参数并且有一个返回值的法度榜样。在SQL中Oracle 内建了一系列函，也可自定义函数数单行函数，多行函数(组函数)Lower(c)小写转换返回c小写字符Concat(cl,c2) 一连接字符串cl,c2均为字符串，函数将c2连接到cl的后面，假如cl为null,将返回c2.假如 c2为null,则返回cl,假如cl、c2都为null,则返回null,假如cl,c2个中之一为 数字，或都为数字ConcatC110； 10) Concat(lOJO)Substr( string, start_position, length )

5、一取字串参数1:string要处理的字符串参数2:截取字符串的开端地位参数3:截取的字符串的长度(而不是字符串的停止地位)，假如省略，则返回从 start_position开端到string尾之间的字符串Select SUBSTR(Hello；2) From DualInstr(columnlexpression,string,m,n)査询指定字符在字符串中的地位参数1：被搜刮的字符串或者是表达式参数2:要查找的字符参数3:从什么地位开端查询参数4:这个字符出现的地位 比如说：第一次出现的地位，第二次出现的地位。 假如此参数3为正，从左到右开端检索，假如此参数为负，从右到左检索 最后两个参数是

6、默认的是1和1SELECT instr(,abc?d,) FROM dual; -返回 0SELECT instr(synm moTa； 1,2) FROM dual; - 返回 0SELECT INSTR(oWo； W) FROM DUAL;-?Round( number, decimalplaces )-四舍五入函数 参数1:要处理的数值(数值表达式)获得123 获得123 获得123.5 获得123.46 获得 123.456 获得-123.46参数2:四舍五入时取的小数的位数，不填则返回整数select round( 123.456) from dual; select round(

7、123.456, 0) from dual;select round( 123.456, 1) from dual; select round(123.456, 2) from dual;select round(123.456, 3) from dual; select round(-123.456, 2) from dual;Mod(number,divisor) 一返回一个 number 除以 divisor 的余数参数1:为被除数。参数2:为除数。假如divisor为零，函数MOD返回值 为本来numberMOD(3, 2)等于 1MOD(3, 0)等于 3MOD(2,3)等于 2To

8、_char(date, formaCmodel)日期转为字符To_char(number, fbrmat_model)-数字转为字符格局化元素，用于显示字符情势的数字值:”9”表示一个数，”.”打印一个小数点”，” 打印一个千位指导SELECT TO_CHAR(12345.529,999,999.99) FROM DUAL;- 12,345.53Replace(text, search_string, replacement_sUing) 调换字符串 从字符串(text)查找一个文本表达式(search_string),假如找到,用指定的置换串 (replacement_string)代替它假

9、如replacement_string为空，那么所有的search_string都被移除。假如search_string为null,那么就返回本来的SELECT REPLACE (f hello1,FROM DUAL; 一一 hiTo_number(, , 加以标记outerbegin dbms_oiitpiitput_lineCHello,World!);innerbegin dbms_outpiitpiit_line(GoocT);end; innerend;outer赋值操作符“:二”变量名:二值定义变量变量名数据类型StudScore NUMBER(5,2);StudName VARC

10、HAR(20);定义常量添加关键字CONSTANT并赋值PI CONSTANT NUMBER(&7)=3.1415926;前提控制有三种语句悄势IF-THEN IF-THEN-ELSE IF-THEN-ELSIFIF v 前提THEN语句；END IF;IF v 前提THEN语句；ELSE语句；END IF;IFv 前提 1THEN语句；ELSIFv询提 2 THEN 语句；ELSIF THEN 语句；ELSE语句；END IF;CASE v变量WHEN v值 1THEN语句；WHEN v值 2THEN语句; WHEN v值 NTHEN语句；Else语句；End Case;CASEWHEN V

11、逻辑表达式1 THEN 语句；WHEN v逻辑表达式2 THEN 语句； WHEN v逻辑表达式N THEN 语句；Else语句；End Case;有三种情势的LOOP语句LOOPWHILE-LOOPFOR-LOOPLOOP语句；EXITEXIT-WHENEND LOOP;EXIT强迫轮回无前提完成 急速退出轮回EXIT WHEN ;参数解释变量名INIOUTIIN OUTIN：输入，接收值，默认模式OUT：输出，将值返回给子法度榜样的调用法度榜样IN OUT：输入输出，接收值并返回已更新的值对于IN模式的实参可所以常量或变量，但对于OUT和IN OUT模式的实参必须 是变量。Create O

12、r Replace Procedure ProcGetName(StudName in out varchar2)isBegin StudName:=substr(StudName,instr(StudName,* 明；2);End;/declareStudName varchar2(15):=明小明；beginProcGetName(StudName);DBMS_OUTPUT.PUT_LINE(StudName);end;函数语法CREATE OR REPLACE FUNCTION v函数名 (参数 1, 参数 N) RETURN datatype IS局部声明BEGIN可履行语句；EXCE

13、PTION例外处理法度榜样END v函数名;仅接收IN参数Create Or Replace Function GetItemScore(Stand_Ans varchar2,Custor_Ans vaichar2)return int isLenCustor int:=Iength(Custor_Ans);beginif LenCustorlength(stand_ans) or Custor_Ans IS NULL thenreturn 0;end if;for i in 1.LenCustorloopif instr(stand_ans,substr(custor_ans,i91 )=0

14、 thenreturn 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 用户名 I

15、DENTIFIED BY 暗码；oracle中逻辑运算符(not,and,or)及其优先级这3种逻辑运算符中，NOT运算符的优先级最高，而后是AND,最后是OROracle中的SPOOL敕令是假脱机敕令，可用于转储履行的敕令和成果，其后果 有点像DOS敕令中的转向敕令。SPOOL敕令也可以用于转出数据库体系中的数 据。例如在SQL*Plus敕令行中可以直接输入：SPOOL假脱机文件名启动假脱机操作。封闭假脱机操作可以在SQL*Plus中输入：SPOOL OFF例：假脱机敕令的应用。spool c:SpoolFile.txtset pagesize 300set linesize 300sele

16、ct * from emp;select * from dept;spool off：履行完毕后，可以在c盘中找到SpoolFile.txt,打开后可以看到经由过程Spool 假脱机敕令转向到该文件中的内容。8 记录类型定义一个RECORD类型 TYPE V类型名 IS RECORDfieldname 1 V字段类型NOT NULLfieldnameN 字段类型NOT NULL)；属性的类型%TYPE引用数据库列%ROWTYPE代表表中的行%type和2匕卩。可以用来指定列的数据类型SName Studinfo.StudName%Type;Stud_Rec StudInfo%ROWTYPE;T

17、ype VIPStudRec is record(VIPID Varchar2(15),VIPName varchar2(20)；声明记录类型变量StudRec VIPStudRec;引用字段的语法是recordname.columnname用于给列赋值的语法是recordname.columnname: = expression;语法SELECT v列名INTO V变量名FROM V表名WHERE V前提；SELECT StudNo,StudNameINTO StudRec FROM StudinfoWHERE StudNo= 99070470;fetch mycur into StudRe

18、c ;TID TeacherInfo.TeacherNo%Type;TName TeacherInfo.TeacherName%type;9.游标CURSOR v游标名IS;CURSOR mycur isselect TeacherNo.TeacherName From TeacherinfoWhere TeacherBirthDay=To_Date( 1981 -2-5,Y YY Y-MM-DD);控制显式游标OPENFETCHCLOSE在应用OPEN、 CLOSE或FETCH语句引用这些语句之前必须对游标进行声 明OPEN v游标名open mycur;FETCH V游标名INTO vai

19、l,，varN;ORFETCH V游标名 INTO record_variable;fetch mycur into TID.TName;每个游标有四个属性可以用于拜访游标的情况区域%NOTFOUND%FOUND%ROWCOUNT%ISOPEN轮回取记录while mycur%foundloop dbms_output.put_line(TIDIITNAME); fetch mycur into TIDTName;end loop;CLOSE v游标名；close mycur;10 表CREATE TABLE schema.table(column datatype DEFAULT expr,

20、.);指定：表名列名、列数据类型和列的大年夜小数据类型VARCHAR2(size) 可变长度的字符数据长度可以到4000字节CHAR(size)固定长度的字符数据NUMBER(p,s)可变长度的数字数据DATE日期和时光值NCHAR NVARCHAR2,国度字符集，与情况变量NLS指定的说话集密切相干, 应用办法和CHAR、VARCHAR2雷同。在创建表时，为一个列指定一个默认值create table c (c 1 char( 10) DEFAULT U 11T)alter table test add syd date default sysdate;altertabletniodify(

21、cccdefaultccc);altertabletmodify(cccdefaultnull);INSERT INTO departments(department_id, department_name, manager_id)VALUES (300, Engineering； DEFAULT);UPDATE departmentsSET manager_id = DEFAULT WHERE department_id = 10;束缚类型NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK列级束缚column CONSTRAINT constraint_nam

22、e constraint_type,表级束缚column,.CONSTRAINT constraint_name constraint_type(column,)，NOT NULL 束缚只能定义在列级，不克不及定义在表级.防止NULL值进入指定的列，在单列基本上定义，默认情况下ORACLE许可在任 何列中有NULL值CREATE TABLE employees(employee_id last_name salaryNUMBER(6),VARCHAR2(25) NOT NULL,NUMBER(&2),commission_pct NUMBER(2,2),hire_dateDATECONSTRA

23、INT emp_hir jdate_rmNOT NULL,UNIQUE束缚用来保护一个表中的一个或者多个列没有任何两行在收到保护的列中具有反复 的数据.ORACLE在独一键列上主动生成一个独一索引以实现独一性CREATE TABLE employees(employee_id last_name email salary commission_pct hire_dateNUMBER(6) unique,VARCHAR2(25) NOT NULL,VARCHAR2(25),NUMBER(8,2),NUMBER(2,2),DATE NOT NULL,CONSTRAINT emp.emaiLuk U

24、NIQUE(email);PRIMARY KEY 束缚独一键的所有特点都实用于主键束缚，只是在主键列中不许可有NULL值CREATE TABLE departments( departmenMdNUMBER(4),emailVARCHAR2(25),location JdNUMBER(4),CONSTRAINT deptjd.pk PRIMARY KEY(departmentJd);FOREIGN KEY 束缚经山过程应用公共列在表之间建立一种父子(parent-child)关系，在表上定义的外 部键可以指向主键或者其他表的独一键.CREATE TABLE employees(employee

25、_idNUMBER(6), department_id NUMBER(4),CONSTRAINT emp_dept_fk FOREIGN KEY (departmenMd)REFERENCES departments(department_id),CHECK束缚检查在束缚中指定的前提是否获得了知足.create table employees(sal number(7,2)constraint emp_sal_ckl check (sal 0) )create table y( id varchar2(7)and and and andcheck (ascii(substr(idj J) be

26、tween 65and 90 and substr(idj J)is not nullascii(substr(id,2J) between65and90andsubstr(id,2J)isnotnullascii(substr(id,3J) between65and90andsubstr(id,3J)isnotnullascii(substr(id,4J) between65and90andsubstr(id,4J)isnotnullascii(substr(id,5J) between 65 and 90 and substr(id,5J) is not null) ）；CREATE TA

27、BLE department_id emailTT(NUMBER(4) check(departmenCid like trj%), VARCHAR2(25) PRIMARY KEYACREATETABLE department_id emailTT(NUMBER(4),VARCHAR2(25) PRIMARY KEY,STUDSEX VARCHAR2(2) CHECK (STUDSEX IN (男T女)添加一个新行到表中INSERT INTOVALUEStable (column , column.) (value , value.);INSERT INTOVALUESdepartments

28、(100, Finance： NULL, NULL,SYSDATETO_DATE(FEB 3, 1999；MON DD. YYYY);改变表中的数据UPDATESETWHEREtablecolumn = value , column = value, condition;UPDATESETWHEREcopy_empdepartment_id = 110 department】ame=HELLO；从表中删除行DELETE FROM tableWHERE condition;DELETE FROM departmentsWHERE departmenCname = Finance;11 视图视图是

29、一个SQL查询，它永远存在于数据库中，并被付与了一个名称，视图是从 基表中生成的虚拟表CREATE OR REPLACE FORCEINOFORCE 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

30、only 束缚的名称。CREATE VIEW salvu50AS SELECT employejid ID_NUMBER, last_name NAME,salary* 12 ANN_S ALARYFROM employeesWHERE departmenMd = 50;SELECT *FROM salvu50;内建视图是一个带有别号(或相干名)的可以在SQL语句中应用的子查询Top-N分析SELECT ROWNUM as RANK, , salaryFROM (SELECT last_name,salary FROM employeesORDER BY salary DESC) AWHERE ROWNUM 8000GROUP BY departmentjd;14 综合常识1.学生信息表(Studinfo )字段名称数据类 型段度 字长是否 为空pK束缚字段描述举例StudNoVarchar215Y学生学号2001070470StudNameVarchar220学生姓名李明StudSexChar2男，女学生性别男StudBirthD ayDateY出身年代1980-10-3ClassNameVarchar250班级名称计本012.课程