oracle命令总结笔记(总结完成版).docVIP

Oracle 10g 个人笔记一、oracle 常用sql plus 命令(1) connect 用户名/密码网络as sysdba /sysoper (2) 一般情况下使用普通用户登录，除非需要更高权限时，在切换高级用户；(3) disconnect 断开连接，又不退出当前窗口；(4) quit/exit 完全退出；(5) show user 显示当前用户；(6) 管理员修改用户密码：alter user xxxx identified by yyyy;(7) Passwordpass 用户名；给自己修改密码，不需要带用户名;给别的用户修改，需要带用户名；创建临时表空间create temporary tablespaceuser_temptempfile d:user_temp.dbfsize 50mautoextend onnext 50m maxsize 1024mextent management local;创建表空间createtablespaceuser_tsdatafile d:user_ts.dbfsize 50m autoextend onnext 50m maxsize 1024mextent management local;创建用户create user test38 identified by test38default tablespaceuser_tstemporary tablespaceuser_tempquota 5m on user_ts;给用户赋予权力grantdba to test38;给scott用户解锁alter user scott account unlock;用system 用户给scott修改密码Alter user scott identified by tiger;不常用的命令：linesize一行能显示多少个字符就换行了默认就只有80字符，所以会出现一个记录一行没有显示完全就换行了Set linesize 120pagesize显示多少个记录就从新开始如图：前后差别Set pagesize 100二、oracle 用户管理1. 创建用户（必须是具有DBA权限的才行）create user 名字 identified by 密码（密码不能以数字开始）defaulttablespacexxxx;temporary tablespacexxx2;quotaxmonxxxx;如果出现这样的情况，直接百度或者google一下。2.给用户分配权限grant create session to 用户名；3.管理的用户的机制4.综合案例方案（schema）（当创建一个用户的时候，只要这个用户创建了任何的数据对象（如表，索引，触发器等），那么DBMS就会创建一个与该用户名一样的一个方案。）赋权grant select/update/insert/all on emp to 用户名；ps:如果想看一个用户有哪些数据对象，可以通过pl/sql development 查看。例子：小红查询scott的emp表select * from scott.emp;表空间参考：/view/2973562.htmOracle表空间之基本概念ORACLE数据库被划分成称作为表空间的逻辑区域形成ORACLE数据库的逻辑结构。一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。每个ORACLE数据库均有SYSTEM表空间,这是数据库创建时自动创建的。SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)。一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间,然而一个稍大型应用的ORACLE数据库采用多个表空间会对数据库的使用带来更大的方便。一个用户可以使用一个或多个表空间，一个表空间也可以供多个用户使用。用户和表空间没有隶属关系个人阶段总结数据库实例、表空间（逻辑结构）、用户、方案、数据对象（表，过程，包等）的关系一个实例下可以有多个表空间。默认是有一个system表空间。一个用户可以使用一个或多个表空间，一个表空间也可以供多个用户使用。用户和表空间没有隶属关系。数据库建立一个用户，就建立了有且只有一个与该用户名字一样的方案来管理该用户建立的各种数据对象。如有两个表空间，一个是默认的system表空间，一个是新建的user_tasp表空间。 用户（wang）在system建立一个表t1，在user_tasp建立一个表t2，但是只有一个方案(wang)来管理这两个表，虽然表分布在不同的表空间上。也说明方案和表空间没有隶属关系，就像用户和表空间没有隶属关系一样。数据对象的操作一般是通过方案来实现操作的。如：Select * from scott.emp 这是完整的写法。scott是方案名-个人实验-建立临时表空间create temporary tablespace user_temptempfile f:datauser_temp.dbfsize 50mautoextend onnext 10m maxsize 100mextent management local-建立表空间create tablespace user_taspdatafile f:datauser_tasp.dbfsize 50mautoextend onnext 10m maxsize 100mextent management local-创建用户create user wang identified by wang default tablespace system;grant connect,resource to wang;alter user wang quota 10m on user_tasp; /在表空间上给用户分配空间就能让用户在该表空间建立数据对象。注意不要指定分配空间在临时表空间上，不然会报错。-在默认表空间上建表create table t_test1(id number,name varchar2(20) 【tablespace system】;-在user_tasp 建表create table t_test2(id number,name varchar2(20) tablespace user_tasp;Schema(方案)当一个用户被创建之后，只要它创建任何一个数据对象，那DBMS就会创建一个和它名字一样的方案与该用户对应。Ps:如果想看某个用户有什么数据对象，用pl/sql development 软件。Schema 的实际应用：xioanghong访问scott的emp表1. 连接scottconn soctt/tiger2. 给xiaohong赋权限grant select/update/delete/all on emp to xiaohong3.select * from scott.emp;参考：/tpadvjynoebbird/item/428a8fee7371253b86d9de271、with admin option 用于系统权限授权，with grant option 用于对象授权。2、给一个用户授予系统权限带上with admin option 时，此用户可把此系统权限授予其他用户或角色，但收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效，如授予A系统权限create session with admin option,然后A又把create session权限授予B,但管理员收回A的create session权限时，B依然拥有create session的权限，但管理员可以显式收回B create session的权限，即直接revoke create session from B.而with grant option用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，不同的是但管理员收回用with grant option授权的用户对象权限时，权限会因传播而失效，如：grant select on表名to A with grant option;，A用户把此权限授予B，但管理员收回A的权限时，B的权限也会失效，但管理员不可以直接收回B的SELECT ON TABLE 权限。相同点：- 两个都可以既可以赋予user 权限时使用，也可以在赋予role 时用不同点：- with admin option 只能在赋予 system privilege 的时使用- with grant option 只能在赋予 object privilege 的时使用- 撤消带有with admin option 的system privileges 时，连带的权限将保留撤消带有with grant option 的object privileges 时，连带的权限将不被保留用户口令管理使用profile进行用户口令管理一个用户连续3次密码输入错误，那该账户锁定1.创建profile文件create profile文件名limitfailed_login_attempts次数password_lock_time天数;2.把该文件（规则）分配给某个用户alter user用户名 profileprofile文件名;3.终止口令一个账号的密码最多能用几天的问题。Pl: 一个密码的使用时间为10天，宽限时间是2天create profile文件名limit password_life_time 10 password_grace_time 2;4.删除profiledrop profile文件名；windows下lsnrctl start oradimstartupsidorcl;unix/linux环境下（略）Oracle 的登陆认证Compmgmt.msc 进入计算机管理找回管理员的密码1. 搜索PWD数据库实例名.ora2. 删除该文件，最好备份一份。3. 生成新的密码文件，在dos下输入命令。orapwd file=PWD数据库实例名.ora的具体路径 password=新密码 entries = 允许多少个人进入。Ps: orapwd file =D:oracleproduct10.2.0db_1databasePWDorcl.ora password = welcome entries10file 文件路径 password 新密码 entries 登陆sys最多的用户。4. 重新启动数据库实例。Oracle 数据类型Char 存放字符串，最大2000字符，定长Pl: name char(32), 只能存放32字符，多了，报错；少了，空格填满。ps: dump() 显示一个字段具体的情况varchar2变长字符，最大可存放4000字符。name varchar(16) name abc实际上只占3字符，其余的13个就回收第九课nchar定长，编码方式unicode，最大2000字符Ps: char类型的，一种中文字符站两个字符，而nchar类型，一个中文字符只占一个字符number1. 可以存放小数，也可以是小数。2. number(p,s)p为有效位，s为小数位。范围：P1,38,s-84,127。整数部分的个数为：p-sPS：如果单单就是number，后面没有任何数字，该数是多少就是多少。Pl: 123.456 number 结果就是：123.456原则：实际开发过程，如果number指定了小数位，那明确指定，如果没有指定，就直接用number。date 日期类型默认的是dd-mm-yyyy第十课Ps: 数据类型是number 插入的是字符串型的12345，oracle 会自动把这样的字符类型转化为数字类型。但是如果是asd123就不行了。2.插入空置的情况：插入null和情况是一样的。（中间有一个空格）并不是空。即=null3.字符和日期类型应该用包含起来。第十一课Ps:oracle 的sql不区分大小写，但是内容区分大小写；Ps:给字段改名可以用as ,也可以不用；nvl() 处理null的字段。select ename,sal*12+nvl(comm,0) as all_sal from emp;oracle 中的字符连接符号： |设置保存点并回滚savepoint aa;rollback to aa;第十二课where 语句的用法like 操作符% 任意零个或者多个字符；_ 任意一个字符；条件为空与否的使用 is null 或者 is not null 处理；Order by 排序默认是asc , desc 是降序。第十三课组函数max(),min(),sum(),count(),avg();ps:括号里面可以是字段也可以是表达式avg(sal) 不会吧sal为空的记录统计进来。它只计算有值记录的平均值。求所有人的平均值，可以这样：select sum(sal)/count(*) from emp;group by查询的结果进行分组；having 限制分组显示结果。第十四课多表查询1. emp ,salgrade 两张表的连接。select ename,sal,grade from emp join salgrade on sal between losal and hisal;自连接2. 显示FORD的上级select ename ,sal from emp where empno =(select mgr from emp where ename =FORD);3. 显示所有员工的名字，及其上级的名字；select t1.ename as worker,t2.ename as boss from emp t1 left join emp t2 on t1.mgr = t2.empno;第十五课子查询显示与SMITH同一个部分的员工select ename from emp where deptno = ( select deptno from emp where ename = SMITH);（结果包含了SMITH,如果不想包含SMITH ,在加上and enameSMITH）;子查询中使用allPl：显示工资比部分30的所有员工的工资还要高的员工姓名，工资，部门号select ename,sal,deptno from emp where sal all(select sal from emp where deptno =30);同样的写法：select ename,sal,deptno from emp where sal ( select max(sal) from emp where deptno =30);子查询中使用anyPl:第十六课在from中使用子查询显示各部分中高于本部分平均工资的员工信息。select emp.ename,sal,t.myavg,t.deptno from emp join (select avg(sal) myavg,deptno from emp group by deptno) ton emp.deptno = t.deptno where sal t.myavg;各个部门工资最高人的详细信息。select emp.*,t.maxsal from emp join (select max(sal) maxsal,deptno from emp group by deptno) t on emp.deptno = t.deptno where emp.sal = t.maxsal;显示每个部门的信息和人员数1.显示各个部分的人数select count(*) pernum, deptno from emp group by deptno；2.与表dept进行连接select dept.*,t.pernum from dept left join (select count(*) pernum, deptno from emp group by deptno) t on dept.deptno = t.deptno ;1.表本身的复制create table mytest as select * from emp;ps:把emp 表的结构和记录都复制到这个mytest表中2.自我复制进行插入操作：insert into mytest select * from mytest;Oracle 分页select ename, sal from (select ename, sal, rownum r from (select * from emp order by sal desc) where r 5 and r =sysdate;最经三个月入职的员工。同样可以应用bbs最近n个月的帖子。系统函数第二十三、二十四课事务Jdbc中设置oracle隔离机制conn.setTranscationIsolation(Connect.SERIALIZIABLE)方法第二十五、二十六课数据完整性1. 约束：not null，uinque，primary key，froeign key，check,defaultPl:create table worker(id number primary key,name varchar2(20) not null,salnumber check(sal3000 and sal alter table表名 drop constraint约束名建立约束的两种方法：1. 每个字段后直接填写约束条件如 id primary key,（字段级别约束）2. 字段都建立好了，在表后添加（表级约束）constraint 约束名 primary key(id),constraint 约束名 foreign key(id) references 表（字段）,constraint 约束名 unique(id),not null(字段)constraint 约束名 primary key(id),第二十七课序列create sequence seq start with 1 increment by 1minvalue 1 maxvalue 3000 cycle /cycle 指增长到3000 在重新从1开始增长；序列的使用 seq.next.val序列名.currval 显示当前序列的值。（这个使用之前必须先使用上面那个）第二十八课索引1.经常用于查询、排序和分组的列（即经常在where、order或group by子句中出现的列）。2.大型的表（上百万级的数据表）使用索引3.逻辑层次不要超过4层（？）索引会降低修改、删除和插入的效率第二十九、三十课权限和角色一、 系统权限常用的有：系统权限的回收不是级联回收。对象权限常用的：对象权限的回收是级联回收的Ps:如scott 把权限给user1,user1又把该权限给user2.如果scott 把user1的权限收回，那user2也被收回。角色的管理角色是一组权限的集合，是为了简化对权限的管理。grantee 中的名字必须是大写，不管系统默认还是你自己创建的（自己创建的可能是小写的也得大写）自定义角色Pl:grant 角色 to 用户 with admin option 加上后面一句话，就是该用户能把该角色继续给别人。第三十一课Pl/sqlpl: create procedure pro1isbegininsert into emp values()end;执行：exec pro1;带有参数的procedure块（block）开发要想显示输出的内容需要：set serveroutput on；PL:declare -定义变量 v_ename varchar2(20);begin select ename into v_ename from emp where ename = &ename; dbms_output.put_line(ename is | v_ename);end;/改成procedure create procedure pro is -定义变量 v_ename varchar2(20);begin select ename into v_ename from emp where ename = &ename; dbms_output.put_line(ename is | v_ename);end;/带参数的create procedure pro(v_empno number) is v_ename varchar2(10);begin select ename into v_ename from emp where empno = v_empno; dbms_output.put_line(v_ename is | v_ename);end;第三十二课Pl/sql 异常的处理declare v_ename varchar2(10);begin select ename into v_ename from emp where empno = &empno; dbms_output.put_line(ename is | v_ename);exception -异常的声明 when no_data_found then dbms_output.put_line(输入的号码有误);end;exceptionwhen 异常名称 on 对异常处理的代码（如：dbms_output.put_line(输入的号码有误);）过程语法总结：create procedure 名字（变量 in/out类型，变量in/out 类型，） is变量声明begin代码exceptionwhenthenend;ps:创建过程的变量类型，不要指定类型的具体大小，只需是varchar，number 即可。第三十三课Pl:create procedure pro3(in_name varchar, in_sal varchar) isbegin update emp set sal = in_sal where ename = in_name;end;/控制台调用procedureexec 名字（） or call 名字（）Java中调用过程。过程：Create or replaceprocedure pro3 isbegininsertinto emp(empno,ename)values(1234,WANG);end;java 调用代码package com.my.first;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;publicclass TestPro /* * param args */publicstaticvoid main(String args) Connection conn = null;CallableStatement cs = null;try Class.forName(oracle.jdbc.driver.OracleDriver);conn = DriverManager.getConnection(jdbc:oracle:thin::1521:oracle, scott, tiger);cs = conn.prepareCall(call pro3); /call pro3cs.execute(); catch (ClassNotFoundException e) e.printStackTrace(); catch (SQLException e) e.printStackTrace();finallyif(cs !=null)try cs.close();cs = null; catch (SQLException e) e.printStackTrace();if(conn != null)try conn.close();conn = null; catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();第三十四课函数create function 名字（参数类型）return 数据类型 is定义变量begin执行代码endpl:create or replace function fun1(in_ename varchar)retur number isv_annualsalnumber;beginselect(sal+nvl(comm,0)*12into v_annualsal from emp where ename=in_ename;return v_annualsal;end;控制台调用function1、select fun1(SMITH) from dual;2、varv_sal number;call ful(SMITH)into :v_sal;Java中调用functionpackage com.my.first;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;importjava.sql.Statement;publicclass TestFunction /* * param args */publicstaticvoid main(String args) Connection conn = null;CallableStatement cs = null;ResultSet rs = null;PreparedStatement ps = null;try Class.forName(oracle.jdbc.driver.OracleDriver);conn = DriverManager.getConnection(jdbc:oracle:thin::1521:orcl, scott, tiger);String sql = select fun(?) from dual;ps = conn.prepareStatement(sql);/ps.setString(1, SMITH);rs = ps.executeQuery();/String sql = select fun(SMITH) from dual; 这样也行/rs = conn.createStatement().executeQuery(sql);while(rs.next()System.out.println(SMITH SAL IS + rs.getDouble(1); catch (ClassNotFoundException e) e.printStackTrace(); catch (SQLException e) e.printStackTrace();finallyif(cs !=null)try cs.close();cs = null; catch (SQLException e) e.printStackTrace();if(conn != null)try conn.close();conn = null; catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();第三十五课包ps:Procedure 和function 必须有变量；包体Pl/sql 语法数据类型分为：1、 标量类型用:=赋初值。Oracle 中 = 相当于 java中的 =；(oracle ):=相当于(java) =Pl: 计算员工的税create or replace procedure pro3(v_empno number) is-定义变量v_tax_rat number(3,2):=0.03;v_sal number;v_ename varchar2(32);v_tax number;beginselect ename,sal into v_ename,v_sal from emp where empno = v_empno;v_tax := v_sal * v_tax_rat;dbms_output.put_line(v_ename |该交的税是：| v_tax);end;第三十六课%type v_empno emp.empno%type;即v_empno 的类型参照emp.empnoCreateorreplaceprocedurepro3(v_empno number)is-定义变量v_tax_ratnumber(3,2):=0.03;v_sal emp.sal%type;v_ename emp.ename%typev_taxnumber;beginselect ename,sal into v_ename,v_sal from emp where empno = v_empno;v_tax := v_sal * v_tax_rat;dbms_output.put_line(v_ename |该交的税是： | v_tax);end;2、 复合类型Pl:3、 参照变量类型 游标变量/topic/649874cursorCursor类型包含三种: 隐式Cursor，显式Cursor和Ref Cursor（动态Cursor）。1隐式Cursor: 1).对于Select INTO语句，一次只能从数据库中获取到一条数据，对于这种类型的DML Sql语句，就是隐式Cursor。例如：Select /Update / Insert/Delete操作。2)作用：可以通过隐式Cusor的属性来了解操作的状态和结果，从而达到流程的控制。Cursor的属性包含：SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数SQL%FOUND 布尔型值为TRUE代表插入、删除、更新或单行查询操作成功SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假3) 隐式Cursor是系统自动打开和关闭Cursor.Pl:Set Serveroutput on;beginupdate t_contract_master set liability_state = 1 where policy_code = 123456789;if SQL%Found then dbms_output.put_line(the Policy is updated successfully.);commit;else dbms_output.put_line(the policy is updated failed.);end if;end;2显式Cursor：（1）对于从数据库中提取多行数据，就需要使用显式Cursor。显式Cursor的属性包含：游标的属性返回值类型意义%ROWCOUNT 整型获得FETCH语句返回的数据行数%FOUND 布尔型最近的FETCH语句返回一行数据则为真，否则为假%NOTFOUND 布尔型与%FOUND属性返回值相反%ISOPEN 布尔型游标已经打开时值为真，否则为假（2）对于显式游标的运用分为四个步骤：定义游标-Cursor Cursor Name IS; 打开游标-Open Cursor Name; 操作数据-Fetch Cursor name 关闭游标-Close Cursor Name,这个Step绝对不可以遗漏。（3）以下是三种常见显式Cursor用法。Set serveroutput on;declare -define Cursor Cursor cur_policy isselect cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_accountfrom t_contract_master cmwhere cm.liability_state = 2and cm.policy_type = 1and cm.policy_cate in (2,3,4)and rownum 5order by cm.policy_code desc; curPolicyInfo cur_policy%rowtype;-定义游标变量Beginopen cur_policy; -open cursor Loop -deal with extr