Oracle知识整理.doc

1. SQL语言分类种类缩写全称常用操作数据定义语言DDLData define languagecreate, alter, drop数据操纵语言DMLData Manipulation Languageselect, insert, delete, update事务控制语言TCLTransaction Control Languagecommit, savapoint, rollback数据控制语言DCLData Control Languagegrant, revoke2. SQL常用数据类型分类关键字表示范围备注字符char12000字节固定长度varchar214000字节可自增长长度long2GB可自增长长度数值number定义方式number(P,S)P:长度 S:精度日期date日期和时间部分，精确到整个的秒timestamp存储日期、时间和时区信息，秒值精确到小数点后6位RAWraw12000字节存储二进制数据long raw2GB存储二进制数据LOBclob4GB能够存储大量字符数据blob4GB可以存储较大的二进制对象，如图形、视频剪辑和声音文件bfile4GB用于将二进制数据存储在数据库外部的操作系统文件中3. Oracle中的伪列l Oracle 中伪列就像一个表列，但是它并没有存储在表中l 伪列可以从表中查询，但不能插入、更新和删除它们的值l 常用的伪列有 ROWID 和ROWNUM3.1. ROWIDROWID是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用ROWID伪列快速地定位表中的一行。3.2. ROWNUMROWNUM是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数。4. 命名规则Oracle中的各种数据对象，包括表名称，视图等等名称的命名都需要遵循Oracle的命名规则。Oracle的命名规则分为标准命名方式和非标准命名方式。l 标准命名方式以字符打头30个字符以内只能包含A-Z,a-z,0-9,_,$和#。不能和同一个用户下的其他对象重名，不能是oracle服务器的保留字。l 非标准命名方式你可以使用你想使用的任何字符，包括中文，oracle中的保留字，空格等等，但是需要将对象名用双引号引起来。例如： create table “table” (test1 varchar2(10);5. 创建表空间CREATE TABLESPACE tablespacenameDATAFILE filename SIZE integer K|MAUTOEXTEND OFF|ON;注：大写字母为关键字，小写部分为用户自定义部分；中的内容为可选择部分。例：create tablespace my_tablespace datafile c:myspace size 100K autoextend on;6. Oracle用户操作.1. 创建用户CREATE USER usernameIDENTIFIED BY passwordDEFAULT TABLESPACE tablespaceTEMPORARY TABLESPACE tablespace;例：create user scott identified by tiger;6.2. 密码修改ALTER USER username IDENTIFIED BY newpassword6.3. 锁定用户ALTER USER username ACCOUNT LOCK;6.4. 撤销锁定ALTER USER username ACCOUNT UNLOCK;6.5. 删除用户DROP USER username;7. 权限管理l Oracle权限有两种类型，系统权限和对象权限。l 系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言）。l 实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。..1. 系统权限管理7.1.1. 系统权限分类DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。RESOURCE：拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。对于普通用户：授予connect, resource权限。对于DBA管理用户：授予connect, resource, dba权限。7.1.2. 系统权限授权系统权限只能由DBA用户授出：sys, system(最开始只能是这两个用户)授权命令：GRANT connect, resource, dba TO username;普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。例：grant connect, resource to scott;7.1.3. 系统权限传递增加WITH ADMIN OPTION选项，则得到的权限可以传递。例：grant connect, resorce to user01 with admin option;7.1.4. 系统权限回收REVOKE connect, resource FROM username;7.2. 实体权限管理7.2.1. 实体权限分类select, update, insert, alter, index, delete, all等7.2.2. 实体权限授权GRANT select, update, insert ON tablename TO username;例：grant all on emp to scott;7.2.3. 实体权限传递例：user01:grant select, update on product to user02 with grant option;7.2.4. 实体权限回收REVOKE select, update ON tablename FROM username;8. 角色管理l 角色是一组权限的集合，将角色赋给一个用户，这个用户就拥有了这个角色中的所有权限。8.8.1. 系统预定义角色预定义角色是在数据库安装后，系统自动创建的一些常用的角色。角色所包含的权限可以用以下语句查询：select * from role_sys_privs where role=角色名;CONNECT, RESOURCE, DBA这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。oracle建议用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。DELETE_CATALOG_ROLE,EXECUTE_CATALOG_ROLE,SELECT_CATALOG_ROLE这些角色主要用于访问数据字典视图和包SNMPAGENT用于oracle enterprise manager和Intelligent AgentRECOVERY_CATALOG_OWNER用于创建拥有恢复库的用户。关于恢复库的信息，参考oracle文档Oracle9i User-Managed Backup and Recovery GuideHS_ADMIN_ROLEA DBA using Oracles heterogeneous services feature needs this role to access appropriate tables in the data dictionary.8.2. 管理角色l 建一个角色create role role1;l 授权给角色grant create any table, create procedure to role1;l 授予角色给用户grant role1 to user1;l 查看角色所包含的权限select * from role_sys_privs;l scott的权限如图：-查看scott用户具有什么系统权限select * from role_sys_privs;-查看scott用户自己拥有什么角色select * from user_role_privs;-查看scott用户自己具有什么的权限select * from SESSION_ROLES;-查scott用户的创建时间、用户状态、使用的默认表空间、临时表空间等信息select * from user_users;-查看scott用户中，都哪些用户把对象授予给scott用户select * from user_tab_privs;-查看scott用户中拥有的resource角色都具有什么权限select * from role_sys_privs where role=RESOURCE;-scott用户自己拥有多少表select * from user_tables;-查看scott用户已经使用多大的空间，允许使用的最大空间是多少select tablespace_name,bytes,max_bytes from user_ts_quotas;-查看哪些表什么权限赋予了其他用户select * from user_tab_privs_made-把自己的表赋予给其他用户grant select on emp to mzl;-把表的某一列操作权限赋予给其他用户grant update(job) on emp to mzl;l 创建带有口令以角色(在生效带有口令的角色时必须提供口令)create role role1 identified by password1;l 修改角色：是否需要口令alter role role1 not identified;alter role role1 identified by password1;l 设置当前用户要生效的角色set role role1;/使role1生效set role role,role2;/使role1,role2生效set role role1 identified by password1;/使用带有口令的role1生效set role all;/使用该用户的所有角色生效set role none;/设置所有角色失效set role all except role1;/除role1外的该用户的所有其它角色生效select * from SESSION_ROLES;/查看当前用户的生效的角色l 修改指定用户，设置其默认角色alter user user1 default role role1;alter user user1 default role all except role1;l 删除角色drop role role1;l Oracle用户密码过期解决方法原因：由于oracle11g中默认在default概要文件中设置了“PASSWORD_LIFE_TIME=180天”所导致。解决方案：1、查看用户的proifle是哪个，一般是default：sqlSELECT username,PROFILE FROM dba_users;2、查看指定概要文件（如default）的密码有效期设置：sqlSELECT * FROM dba_profiles s WHERE file=DEFAULT AND resource_name=PASSWORD_LIFE_TIME;3、将密码有效期由默认的180天修改成“无限制”：sqlALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;修改之后不需要重启动数据库，会立即生效。9. 数据定义语言DDLl 数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象。l 用于操纵表结构的数据定义语言命令有：CREATE TABLEALTER TABLETRUNCATE TABLEDROP TABLE9.9.1. 利用现有表创建新表语法： CREATE TABLE AS SELECT column_names FROM ;例如： CREATE TABLE newitemfile AS SELECT * FROM itemfile;9.2. 创建新表语法：CREATE TABLE ( Colname datatype default XXX PRIMARY KEY,Colname1 datatype default XXX NOT NULL | NULLcolnameN datatype default XXX);例如：create table scott.mytable(uid number(5) primary key,name varchar2(10);9.3. 修改表结构9.3.1. 编辑表的字段修改一个列的数据类型(一般限于修改长度，修改为一个不同类型时有诸多限制)。语法：ALTER TABLE 表名 MODIFY(列名,数据类型);例如：alter table skate_test modify (author number(10,0) );9.3.2. 增加一个列语法：ALTER TABLE 表名 ADD(列名,数据类型);例如：ALTER TABLE skate_test ADD(author NUMBER(38,0) not null);9.3.3. 给列改名语法：ALTER TABLE 表名 DROP COLUMN 列名;例如：alter table skate_test drop column author;9.3.4. 将一个表改名语法：ALTER TABLE 当前表名 RENAME TO 新表名;例如：alter table skate_test rename to test_sakte;9.3.5. 添加约束语法：ALTER TABLE 表名 ADD CONSTRAINT constraint_name 约束条件(列名) ；例如：alter table sc add constraint sc_fk_course foreign key(cid) references course(cid);/添加外键 alter table student add constraint ck_student_ssex check(ssex in(男,女);/添加check约束9.3.6. 删除约束语法：ALTER TABLE 表名 CONSTRAINT constraint_name；例如：alter table student drop constraint ck_student_sage;9.4. 截断表使用TRUNCATE命令可以删除表中所有数据，且不能回滚。语法：TRUNCATE TABLE tablename;9.5. 删除表语法：DROP TABLE tablename;10. 数据操纵语言DMLl 数据操纵语言用于检索、插入和修改数据l 数据操纵语言是最常见的SQL命令l 数据操纵语言命令包括：SELECT、INSERT、UPDATE、DELETE10.10.1. 增insert语法：INSERT INTO table_name (列1, 列2,.) VALUES (值1, 值2,.);例如：INSERT INTO s_dept VALUES (11,Frinace,2); INSERT INTO s_dept s_emp(id,last_name,first_name,salary,start_date)VALUES (11,Frinace,Jon,1560,03-4月-92); INSERT INTO history SELECT id,last_name,salary FROM s_emp WHERE start_date UPDATE order_master SET del_date =30-8月-05 WHERE orderno SAVEPOINT mark1;SQL DELETE FROM order_master WHERE orderno = o002;SQL SAVEPOINT mark2;SQL ROLLBACK TO SAVEPOINT mark1;SQL COMMIT;12. 数据控制语言DCLl 数据控制语言为用户提供权限控制命令 l 用于权限控制的命令有：l GRANT - 授予权限l REVOKE - 撤销已授予的权限例如：SQLGRANT SELECT ON vendor_master TO accounts WITH GRANT OPTION;SQLREVOKE SELECT, UPDATE ON order_master FROM MARTIN;13. PL/SQLl 基本语法：DECLARE -声明部分declarationsBEGIN -执行部分executable statementsEXCEPTION -异常捕获部分 handlersEND;l 声明变量和常量的语法：1.使用赋值语句 :=2.使用 SELECT INTO 语句例如：declare var_name varchar(100);-变量的定义 var_job varchar(100); var_int int; var_const constant varchar(100) :=;-);-常量的定义 var_date emp.hiredate%type;-引用变量类型 var_rowtype emp%rowtype;-提供表示表中一行的记录类型 var_boolean boolean;-布尔类型,值:TRUE,FALSE,nullbegin select * into var_rowtype from emp where ename=upper(var_name);end;3.1. 控制结构l PL/SQL 支持的流程控制结构： 条件控制 IF 语句 CASE 语句 循环控制 LOOP 循环 WHILE 循环 FOR 循环 顺序控制 GOTO 语句 NULL 语句13.1.1. 条件控制 IF语句IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF例如：declare var_sal emp1.sal%type;begin select sal into var_sal from emp1 where empno=7369; if var_sal=2000 then update emp1 set sal=sal*1.05 where empno=7369; elsif var_sal=1000 and var_sal2000 then update emp1 set sal=sal*1.1 where empno=7369; else update emp1 set sal=sal*1.2 where empno=7369; end if;end; CASE语句CASE 语句用于根据单个变量或表达式与多个值进行比较。执行 CASE 语句前，先计算选择器的值。例如：begin case &grade when A then dbms_output.put_line(优异); when B then dbms_output.put_line(优秀); when C then dbms_output.put_line(良好); when D then dbms_output.put_line(一般); when E then dbms_output.put_line(较差); else dbms_output.put_line(无成绩); end case;end;13.1.2. 循环控制 LOOP循环语法：while . loop. end loop;exit 终止当前循环 exit when . 条件满足则终止当前循环例如：begin LOOP dbms_output.put_line(哈哈); END LOOP;end; WHILE循环例如：declare var_count number(10):=0;begin while var_countvalue2;reverse表示value2-value1例如：declare var_i number(10):=1; var_j number(10):=1;begin for var_i in 1.9 loop for var_j in 1.9 loop DBMS_OUTPUT.put(var_j|*|var_i|=|var_i*var_j| ); exit when var_j=var_i; end loop; DBMS_OUTPUT.put_line( ); end loop;end;13.1.3. 顺序控制 GOTO 语句 NULL语句goto - 无条件地转到标签指定的语句null -什么也不做的空语句例如：declare var_a number(2):=1;begin if var_a5 then goto label1; else goto label2; end if; DBMS_OUTPUT.put_line(label1); null;end;13.2. 动态SQL动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句。语法：execute immediate dynamic_sql_string INTO define_variable_list USING bind_argument_list;例如：declare var_sql varchar2(200); var_empno number(4); var_emp emp%rowtype;begin var_empno:=&员工编号; var_sql := select *from emp1 where empno=:arg1 and sal:arg2; execute immediate var_sql into var_emp using var_empno,100; DBMS_OUTPUT.put_line(var_emp.job);end;13.3. 异常处理异常有两种类型：预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发预定义异常例如：declare var_id varchar2(5);begin select empno into var_id from emp1; select empno into var_id from emp1 where empno=1111; DBMS_OUTPUT.put_line(我被正常执行啦!);exception when too_many_rows then DBMS_OUTPUT.put_line(返回太多行); when NO_DATA_FOUND then DBMS_OUTPUT.put_line(查询未找到数据);end;用户定义异常例如：DECLARE invalidCATEGORY EXCEPTION; category VARCHAR2(10);BEGIN category := &Category; IF category NOT IN (附件,顶盖,备件) THEN RAISE invalidCATEGORY; ELSE DBMS_OUTPUT.PUT_LINE(您输入的类别是| category); END IF;EXCEPTION WHEN invalidCATEGORY THEN DBMS_OUTPUT.PUT_LINE(无法识别该类别);END;14. 游标l 游标的类型有：1.隐式游标 2.显式游标 3.REF游标(REF 游标用于处理运行时才能确定的动态 SQL 查询的结果)14.14.1. 隐式游标在PL/SQL中使用DML语句时自动创建隐式游标，隐式游标自动声明、打开和关闭，其名为 SQL。通过检查隐式游标的属性可以获得最近执行的DML 语句的信息。隐式游标属性：%found - SQL 语句影响了一行或多行时为 TRUE%notfound - SQL 语句没有影响任何行时为 TRUE%rowcount - SQL 语句影响的行数例如：declare var_empno emp.empno%type;BEGIN select empno into var_empno from emp where empno=7900;IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(已查找到|sql%rowcount|条数据!);END IF; EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(职员未找到);end;14.2. 显示游标当查询返回结果超过一行时，就需要一个显式游标。此时用户不能使用select into语句。PL/SQL管理隐式游标，当查询开始时隐式游标打开，查询结束时隐式游标自动关闭。显式游标在PL/SQL块的声明部分声明，在执行部分或异常处理部分打开，取数据，关闭。声明游标语法： CURSOR cursor_name IS select_statement;打开游标 使用游标中的值之前应该首先打开游标，打开游标初始化查询处理。打开游标的语法是： OPEN cursor_name (cursor_name是在声明部分定义的游标名。)关闭游标 语法： CLOSE cursor_name从游标提取数据 从游标得到一行数据使用FETCH命令。每一次提取数据后，游标都指向结果集的下一行。语法如下： FETCH cursor_name INTO variable,variable,.例如：declare var_ename emp.ename%type; cursor emp_cur is -声明游标 select ename from emp where job=upper(&job);begin open emp_cur; -打开游标 loop fetch emp_cur into var_ename; -提取行 exit when emp_cur%notfound; DBMS_OUTPUT.put_line(var_ename); end loop; close emp_cur; -关闭游标end; 带参数的游标与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下：CURSORcursor_name(parameter,parameter,.)ISselect_statement;定义参数的语法如下：Parameter_nameINdata_type:=|DEFAULTvalue 与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型，没有大小。另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。在打开游标时给参数赋值，语法如下：OPENcursor_namevalue,value.;参数值可以是文字或变量。例如：DECLAREvar_job emp.job%type;var_empno emp.empno%type;var_ename emp.ename%type;CURSOR emp_cur(param_job emp.job%type) IS SELECT empno, ename FROM emp WHERE job=param_job;BEGINvar_job:=upper(&job);OPEN emp_cur(var_job);LOOPFETCH emp_cur INTO var_empno,var_ename;EXIT WHEN emp_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(var_empno|, |var_ename); END LOOP;CLOSE emp_cur;END;游标中的更新和删除 在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。语法： CURSOR IS FOR UPDATE OF ;UPDATE SET WHERE CURRENT OF DELETE FROM WHERE CURRENT OF 例如：declare var_sal emp1.sal%type; cursor emp_cur is select sal from emp1 where sal2000 for update of sal;begin open emp_cur; loop fetch emp_cur into var_sal; exit when emp_cur%notfound; update emp1 set sal=var_sal*1.1 where current of emp_cur; end loop; close emp_cur;end;循环游标循环游标用于简化游标处理代码，当用户需要从游标中提取所有记录时使用。语法：FOR IN LOOPEND LOOP;例如：declare cursor emp_cur is select ename from emp;begin for emp_rec in emp_cur loop DBMS_OUTPUT.PUT_LINE(emp_rec.ename); end loop;end;14.3. REF游标REF 游标和游标变量用于处理运行时动态执行的 SQL 查询。创建游标变量需要两个步骤：1.声明 REF 游标类型2.声明 REF 游标类型的变量用于声明 REF 游标类型的语法为：TYPE IS REF CURSORRETURN ;打开游标变量的语法如下： OPEN cursor_name FOR select_statement;声明强类型的 REF 游标(有返回值)：TYPE my_cur IS REF CURSOR RETURN emp%ROWTYPE;var_cur my_cur; 声明弱类型的 REF 游标：TYPE my_cur IS REF CURSOR;var_cur my_cur;例如：declare type my_cur is ref cursor;-声明游标类型 var_emp emp%rowtype; emp_cur my_cur;-申明游标变量begin open emp_cur for select *from emp;-打开动态SQL语句 loop fetch emp_cur into var_emp; exit when emp_cur%notfound; DBMS_OUTPUT.PUT_LINE(var_emp.ename); end loop; close emp_cur;end;15. 子程序l 命名的 PL/SQL 块，编译并存储在数据库中。l 子程序的各个部分：声明部分可执行部分异常处理部分(可选)l 子程序的分类：过程 执行某些操作函数 执行操作并返回值15.15.1. 过程过程是用于完成特定任务的子程序。创建过程的语法：-创建过程，可指定运行过程需传递的参数CREATE OR REPLACE PROCEDURE ()IS|AS BEGIN-包括在过程中要执行的语句 -处理异常(可选)EXCEPTION END;例如：create or replace procedureget_empno(temp_no emp.empno%type) isvar_emp emp%rowtype;begin select * into var_emp from emp where empno=temp_no; dbms_output.put_line(var_emp.ename);exception WHEN NO_DATA_FOUND THEN dbms_output.put_line(无此人信息!); end;过程参数的三种模式：IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值 IN OUT用于接受调用程序的值，并向调用程序返回更新的值例如：create or replace procedureswap(temp1 in out number,temp2 in out number)istemp number;begin temp:=temp1; temp1:=temp2; temp2:=temp;end;-测试-declare a number(2):=1; b number(2):=2;begin dbms_output.put_line(a=|a); dbms_output.put_line(b=|b); swap(a,b); dbms_output.put_line(a=|a); dbms_output.put_line(b=|b); end;15.2. 函数函数是可以返回值的命名的 PL/SQL 子程序。创建函数的语法：CREATE OR REPLACE FUNCTION (param1,param2)RETURN IS|AS local declarationsBEGIN Executable Statements; RETURN result;EXCEPTION Exception handlers;END;定义函数的限制： 函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数 形参不能是 PL/SQL 类型 函数的返回类型也必须是数据库类型访问函数的两种方式： 使用 PL/SQL 块 使用 SQL 语句例如：create or replace functionmy_funreturn varchar2 isbegin return 哈哈;end;-测试-begin dbms_output.put_line(my_fun); end;16. 程序包l 程序包是对相关过程、函数、变量、游标和异常等对象的封装。l 程序包由规范和主体两部分组成规范