Oracle入门到精通.docx

《Oracle入门到精通.docx》由会员分享，可在线阅读，更多相关《Oracle入门到精通.docx（44页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle入门到精通ALTER VIEW语法ALTER ALGORITHM = UNDEFINED | MERGE | TEMPTABLE VIEW view_name (column_list) AS select_statement WITH CASCADED | LOCAL CHECK OPTION数据库建立完成后挂起oracle 启动oracle监听 $lsnrctl lsnrctl;status (查状态) lsnrctl;start 用户管理alter user scott identified by king;SQL*PLUS基础1,概述2,帮助命令sqlplus sys/12

2、3456 as sysdba 用sys以dba身份登陆也可以这样登陆sqlplus/nologconnect sys/123456 as sysdbastartup 启动数据库,后边有两个参数mount表示启动控制文件，不启动数据文件,正常情况下startup启动过程是1是启动事例，2启动控制文件（startup mount）,3打开数据文件(database open),当热备份的时候不允许在非归档方式下运行，查看命令是archive log list ,要改为归档可以只样写startup mount; alter database archivelog; alter database op

3、en;startup nomount; 只启动事例 （当控制文件丢失一个或多个）shutdown immediate 正常关闭,迫使每个用户完成当前语句关闭数据库shutdown 正常关闭,当代所有用户短开情况下在关闭 shutdown transactiunal shutdown abort 非常严厉，强行关闭，可能造成文件损坏sqlplus sys/123456 as sysdbasqlplus scott/123456set命令的使用SET SQLBLANKLINES ON 支持空格行语句以分号结尾select * from dept WHERE deptno=10Select * fr

4、om dept where deptno=&tt; 用替代变量查询Enter value for tt: 输入tt数值查看oracle缓冲区命令用list,显示缓冲区第几行到第几行 list 2 4 l2 4更改缓冲区中命令行用change 简写cchange /forn/form 把forn换成form执行缓冲区里的命令 / 删除缓冲区里的命令行 del del 4 删除第四行 del 2 4在缓冲区尾步追加 appen A from dept保存缓冲区里的内容 save c:oracletest.txt加载内容 get c:oracletest.sql get察看内容不执行edit 允许用

5、外部的命令编辑缓冲区内容 windows默认打开记事本 执行缓冲区内容用/ column命令使用 定义标签 只是在当前能使用 COL deptno HEADING 编号 select * from deptdescribe 命令使用 DESC dept COL dname FORMAT A10 COL dname(字段) FORMAT A10 HEADING 部门名称 把dname字段换成部门名称 COL deptno FORMAT 999.999.999 select * from dept;oracle用户管理：一、创建profile文件。SQLCreate profile 文件名 lim

6、it FAILED_LOGIN_ATTEMPTS：指定锁定用户的登录失败次数 PASSWORD_LOCK_TIME：指定用户被锁定天数 PASSWORD_LIFE_TIME：指定口令可用天数 PASSWORD_REUSE_TIME：指定在多长时间内口令不能重用 PASSWORD_REUSE_MAX：指定在重用口令前口令需要改变的次数 SESSIONS_PER_USER：限制用户并发会话个数 CPU_PER_SESSION：限制会话所占用的CPU时间（百分之一秒） CPU_PER_CALL：限制每次调用所占用的CPU时间（百分之一秒） CONNECT_TIME：限制会话的总计连接时间（分钟） I

7、DLE_TIME：限制会话的空闲时间（分钟）); 二、创建用户（包括创建角色）:SQLCreate user 用户名 IDENTIFIED BY 口令 Default tablespace 表空间名 TEMPORARY tablespace 临时表空间名 Profile profile文件名 Quota 限制空间大小 on 表空间名;并给用户授权：SQLgrant connect ,resource to 用户名;举例：- 创建角色DROP ROLE ICDYW;CREATE ROLE ICDYW NOT IDENTIFIED;GRANT DBA TO ICDYW;GRANT EXP_FULL

8、_DATABASE TO ICDYW;GRANT IMP_FULL_DATABASE TO ICDYW;- 创建用户DROP USER ICDMAIN CASCADE;CREATE USER ICDMAIN IDENTIFIED BY icd DEFAULT TABLESPACE SERVICE_RPT_DAT TEMPORARY TABLESPACE SERVICE_TEMP PROFILE DEFAULT;GRANT ICDYW TO ICDMAIN WITH ADMIN OPTION;GRANT UNLIMITED TABLESPACE TO ICDMAIN WITH ADMIN OPT

9、ION;GRANT SELECT ON SYS.V_$MYSTAT TO ICDMAIN;GRANT SELECT ON SYS.V_$SESSION TO ICDMAIN;GRANT SELECT ON SYS.DBA_JOBS TO ICDMAIN;ALTER USER ICDMAIN DEFAULT ROLE ALL;三 改动用户： 一 修改用户各项参数：(除用户名外其它各项均可以修改)SQLAlter user 用户名 IDENTIFIED BY 口令 Default tablespace 表空间名 TEMPORARY tablespace 临时表空间名 Profile profile

10、文件名 Quota 限制空间大小 on 表空间名;将用户加锁：SQLAlter user 用户名 ACCOUNT LOCK; - -加锁 ACCOUNT UNLOCK; - -解锁强制用户修改口令：SQLAlter user 用户名 password expire;四 删除用户： 用户下没有实体时:SQLDrop user 用户名;用户已创建实体如表和数据时：SQLDrop user 用户名 CASCADE;注意：当前正连接的用户不得删除。 使用CASCADE选项时，用户及实体马上被删除，应再进入数据文件进行物理删除。五 监视用户：查询用户会话信息：(如果有两个用户以同一用户名登录，则可动态查

11、询SQLSelect username machine , sid , serial # From V$ session; 删除用户会话：SQLAlter system kill session sid , serial ;显示用户的SQL语句：SQLSelect user _name , SQL _text From V$ open _cursor;SQL语言基础SQL语言概述，分类DDL数据定义语言create,alter,dropDCL数据控制语言grant,revoke例子:connect scott/king;GRANT SELECT ON dept TO sailboy; 把sco

12、tt用户里的dept表授权给sailboy用户REVOKE SELECT ON dept FROM sailboy; 把授权sailboy用户权限收回DML数据操纵语言select,insert,delete,update数据定义语言connect scott/kingCREATE TABLE abc(a varchar2(10),b varchar2()ALTER TABLE abc DROP COLUMN c; #删除abc表的c字段connect scott/king;GRANT SELECT ON dept TO sailboy; 把scott用户里的dept表授权给sailboy用户

13、Oracle常用的系统函数1,字符 length,ltrim,replace,rtrim,substr,trim2,日期 Sysdate,current_date,next_day2,转换 To_char,to_date,to_number4,集聚函数 Sum,avg,max,min,count5,其他user,decode,nvl例子SELECT LENGTH(ABCEDF) FROM dual; 查它的字符数量SELECT LENGTHB(xxx) FROM dual; 查它的字节数量SELECT LIRIM( abc) FORM dual;截掉左边的字符 SELECT RIRIM(abc

14、 ) FORM dual 截掉右边的字符SELECT RIRIM( abc ) FORM dual 截掉两边的字符因为oracle如果是定长的时候可能要用空格来补齐！varchar2() 可变字符串函数char() 不可变字符串函数分组语句模糊查询标的连接子查询第四章，PL/SQL基础PL/SQL概述PL/SQL块结构PL/SQL块结构如下DECLARE.BEGIN.EXCEPTION.DED/1,变量的声明的内容2,命名规则 命名不可以是系统关键字DECLARE x varchar2(10);BEGINx:=This is .;DBMS_OUTPUT.PUT_LINE(的值为: |x); 表

15、示系统提供的包，把内容输出到屏幕,|连接数据类型BED;/ 一般不形式，因为当前默认是不显示输出信息的用set的命令设置COLSEPSET SERVEROUTPUT ON SIZE 10000分支语句的使用循环语句的使用异常处理的使用记录的使用PL/SQL高级应用1,游标的设计开发2,存储过程3,存储函数的设计4,包的设计与应用视图，同义词，序列视图及其应用在进行复杂数据查询时候，经常使用视图完成，利用视图可以保证安全性什么是视图:实际上是一条查询语句，是数据的现实方式 视图就是一个虚表，不站用存储空间视图的作用:安全，方便，一致性CREATE OR REPLACE VIEW myview;

16、创建或者替换一个视图ASSELECT * FROM bbsinfo;SELECT * FROM myview; 也可以向视图插入数据，要有权限,向视图添加等于想真正表中添加CREATE OR REPLACE VIEW myview;ASSELECT * FROM bbsinfo where price30;上面的情况再向表中插入数据，如果price没有大于30，则不显示视图的内容，但真实表中没有，因为有条件语句可以给视图加一个限制CREATE OR REPLACE VIEW myview;ASSELECT * FROM bbsinfo where price30;WITH CHECK OPTI

17、ON 检查一下where price30选项，合格则可以添加内容，不合格则不行,只限制视图，不显示基表。 同义词的使用利用同义词可以很方便地操作不同用户模式下的对象，这里叙述怎样创建并使用同义词序列的使用第十章，表的管理1,表的完整性与约束2,管理数据库的备份与恢复1,概述2,逻辑备份用exp进行逻辑备份3,逻辑恢复用imp进行逻辑恢复4,物理备份/var/oracle/oracle10/bin/emctl start dbconsole 启动网络界面1先创建序列: USERID 用户序列号 - CREATE SEQUENCE USERID INCREMENT BY 1 START WITH

18、1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE NOCACHE NOORDER 2.再创建触发器 USERADD 会员触发器 - CREATE OR REPLACE TRIGGER USERADD BEFORE INSERT ON MEMBERRG FOR EACH ROW BEGIN SELECT USERID.NEXTVAL INTO :NEW.NUM FROM DUAL; END;数据库建立完成后挂起oracle 启动oracle监听 $lsnrctl lsnrctl;status (查状态) lsnrctl;start 检查oracle服务是否挂起 $ps -

19、ef |grep ora 连接数据库 $sqlplus /nolog SQL;connect / as sysdba SQL;startup mount 检查是否连接oracle SQL;select sysdate from dual; Sp2-0640:Not connected 挂起 SQL;startup * 如果出现错误信息： ORA-01078:failucr in processing system parameters LRM-00109:could not open parametet file/opt/ora9/product/9.2/dbs/initora9i.or 执行

20、： #cp /opt/ora9/admin/orcl/pfile/init.ora.613200412101 /opt/ora9/product/9.2/dbs/initora9.ora *1 SQL*Plus环境 - show all 显示所有SQL*Plus的命令名字 list or l 显示缓冲区的内容 list 4 显示当前SQL命令的第4行，并把第4行作为当前行。 change or c 用新的内容替换原来在一行中第一次出现内容,例如:SQLc/(.)/(analyst)/ input or i 增加一行或多行 append or a 在一行后追加内容 del 删除当前行 删除SQL

21、缓冲区中的当前行 run 显示并运行SQL缓冲区中的命令 / 运行SQL缓冲区中的命令 edit 把SQL缓冲区中的命令写到操作系统下的文本文件，并调用操作系统提供的编辑器执行修改。 set linesize 80 设置行的大小为80个字符宽度 set pagesize 100 设置页的大小为100行 ttitle smaple ttitle 设置页头标 btitle smaple btitle 设置页脚标 spool /home/zxin10/myresult.log 设置sqlplus的输出到文件中 spool off(out) 停止输出文件 column column_name form

22、at a18 wrap heading smaple-column-title 格式化列输出,每列宽度18个字符,超过18个自动换行,列标题换成column-title CLEAR COLUMNS 将所有列的显示属性设为缺省值.2 异常exception - no_data_found 表示select没有查询到满足条件的数据行。 others 表示Oracle预定义错误范围之外的任何错误,Oracle用这个others捕捉所有 未知的错误。可以使用sqlcode函数,sqlerrm函数在错误处理模块中显示错误 代码和错误信息。而且,others处理必须是一个块的最后一个错误处理,否则, ot

23、hers处理的优先级比较高,权利比较大,它会捕捉所有错误,包括预定义的Oracle 错误和非预定义的Oracle错误。 too_many_rows Oracle的隐式游标,一次只能检索一行数据,使用隐式游标时,异常处理机制如果检测 到游标返回的数据是多行数据,它就抛出too_many_rows类型的异常。 dup_val_on_index 在某个索引上,出现重复值。 value_error 在某个目标字段中,放入的数据长度或者数据范围,超出目标字段定义的长度或者范围 ,如,把8613905180088这个字符串插入userid字段中,而userid定义为varchar2(10), 就会出现这种

24、异常。 Exception Raised when . ACCESS_INTO_NULL Your program attempts to assign values to the attributes of an uninitialized (atomically null) object. CASE_NOT_FOUND None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause. COLLECTION_IS_NULL Your program att

25、empts to apply collection methods other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray. CURSOR_ALREADY_OPEN Your program attempts to open an already open cursor. A cursor mu

26、st be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers. So, your program cannot open that cursor inside the loop. DUP_VAL_ON_INDEX Your program attempts to store duplicate values in a database column that is constrained by a unique index. INVALID_

27、CURSOR Your program attempts an illegal cursor operation such as closing an unopened cursor. INVALID_NUMBER In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This excep

28、tion is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number. LOGIN_DENIED Your program attempts to log on to Oracle with an invalid username and/or password. NO_DATA_FOUND A SELECT INTO statement returns no rows, or your program references a

29、deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows

30、eventually, so when that happens, no exception is raised. NOT_LOGGED_ON Your program issues a database call without being connected to Oracle. PROGRAM_ERROR PL/SQL has an internal problem. ROWTYPE_MISMATCH The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatibl

31、e return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible. SELF_IS_NULL Your program attempts to call a MEMBER method on a null instance. That is, the built-in parameter SELF (which is alwa

32、ys the first parameter passed to a MEMBER method) is null. STORAGE_ERROR PL/SQL runs out of memory or memory has been corrupted. SUBs cript_BEYOND_COUNT Your program references a nested table or varray element using an index number larger than the number of elements in the collection. SUBs cript_OUT

33、SIDE_LIMIT Your program references a nested table or varray element using an index number (-1 for example) that is outside the legal range. SYS_INVALID_ROWID The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid. TIMEOUT_ON_RE

34、SOURCE A time-out occurs while Oracle is waiting for a resource. TOO_MANY_ROWS A SELECT INTO statement returns more than one row. VALUE_ERROR An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if th

35、e value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.) ZERO_DIVIDE Your program attemp

36、ts to divide a number by zero. number数字类型函数ABS(x) 函数此函数用来返回一个数的绝对值。ACOS(x)函数返回X的反余弦值。X范围从1到-1，输入值从0到派，以弧度为单位。ASIN(x)函数返回X的反正弦值。X范围从1到-1，输入值从-PI/2到PI/2，以弧度为单位。ATAN(x)函数返回X的反正切值。输入值从-PI/2到PI/2，以弧度为单位。BITAND(x,y)函数返回X和Y的与结果。X和Y必须为非负整数。注意没有BITOR函数，但是在UTL_RAW包中有用 于RAW值的位操作符。CEIL(x)函数用来返回大于或等于X的最小整数。COS(x

37、)函数返回x的余弦值。x是以弧度表示的角度。COSH(x)函数，返回X的双曲余弦。EXP(x)函数，与power(x,y)函数类似，不过不用指明基数，返回E的X次幂。E=2.71828183.FLOOR(x)函数，用来返回小于或等于X的最大整数。LN(x)函数，返回x的自然对数。x必须大于0。LOG(x,y)函数，返回以X为底Y的对数。底必须是不为0和1的正数，Y是任意正数。MOD(被除数，除数)求余函数，如果除数为0，则返回被除数。POWER(x,y)函数，返回X的Y次幂。底X和指数Y都不必是正整数，但如果X是负数的话，Y必须是整数。ROUND(x,y)函数，返回舍入到小数点右边Y位的X值。

38、Y缺省为0，这将X舍入为最接近的整数。如果Y是负数， 那么舍入到小数点左边相应的位上，Y必须为整数。SIGN(x)函数，此函数用来返回一个数的正负值，若为一个正数则返回1，若为一个负数则返回-1， 若为0则仍返回0，有点像把模拟量数字化的意思。 SIN(x)函数，返回X的正弦。x是以弧度表示的角度。SINH(x)函数，返回x的双曲正弦。SQRT(x)函数，返回x的平方根，x不能是负数。TAN(x)函数，返回x的正切。x是以弧度表示的角度。TANH(x)函数，返回x的双曲正切。TRUNC(x,y)截取值函数，Y缺省为0，这样X被截取成一个整数。如果Y为负数，那么截取到小数点左边相应位置WIDTH

39、_BUCKET(x,min,max,num_buckets) 只能在SQL语句中使用。使用WIDTH_BUCKET可以根据输入参数创建等长的段。范围MIN到MAX被分为num_buckets节，每节有相同的大小。返回X所在的那一节。如果X小于MIN，将返回0，如果X大于或等于MAX，将返回num_buckets+1.MIN和MAX都不能为NULL，num_buckets必须是一个正整数。如果X是NULL，则返回NULL。 时间类型函数：（date）内部存储格式：世纪、年、月、日、小时、分钟、秒默认格式是：DD-MON-RR。SYSDATE 返回当前的系统时间。SELECT SYSDATE FR

40、OM DUAL；对日期的数学运算SELECT （SYSDATE-HIRE_DATE）/7 FROM TABLENAME WHERE ROWNUM; 数字列ADD_MONTHS(date,x)函数，返回加上X月后的日期DATE的值。X可以是任意整数。如果结果的月份中所包含的 日分量少于DATE的月份的日分量，则返回结果月份的最后一天。如果不小于，则 结果与DATE的日分量相同。时间分量也相同。CURRENT_DATE 以DATE类型返回会话时区当前的日期。这个函数同SYSDATE相似，除了SYSDATE不管当 会话时区。CURRENT_TIMESTAMP(precision) 以TIMESTAM

41、P WITH TIMEZONE 类型返回会话时区当前的日期。如果 指定precision，它指返回秒数的精度，缺省为6。DBTIMEZONE 返回数据库的时区。 LAST_DAY（日期） 指定日期所在月份的最后一天的日期，这个函数可用来确定本月还有多少天。LOCALTIMESTAMP(precision) 以TIMESTAMP类型返回会话时区的当前日期。如果指定precision，它指 返回秒数的精度，缺省为6 。MONTHS_BETWEEN(离当前比较近的日期date1,以前的日期) 两个日期之间相差的月数（以日作为最小单位来计算的）。返回是相差的月数。如果date1和date2的日分量相同

42、，或者这两个日期都分别是所在月的最后一天，那么返回结果是个整数。否则，返回结果包含一个分数，以一个月31天计算。NEW_TIME(d,zone1,zone2)函数，当时区zone1中的日期和时间是D的时候，返回时区zone2中的日期和时间。 返回类型为DATE。zone1和zone2是字符字符串，另外的时区可在ORACLE9I中 通过查询V$TIMEZONE_NAMES得到。 NEXT_DAY （日期，星期几） 指定日期后将要遇到的后七天的某一天的日期。 ROUND（日期，MONTH/YEAR） 四舍五入得到新的日期。 保留位置是月和年SESSIONTIMEZONE 返回当前会话的时区。返回类

43、型是一个时区偏移或时区片名的字符字符串。如果指 定格式，则与 ALTER SESSION 语句中的格式相同。SYS_EXTRACT_UTC(datetime) 从提供的DATETIME中以UTC(Coordinated Universal Time)返回时间。 DATETIME必须包含一个时区。SYSTIMESTAMP 以TIMESTAMP WITH TIMEZONE 返回当前的日期和时间。当在分布式SQL语句中使用的时 候，返回本地数据库的日期和时间。TRUNC（日期，MONTH/YEAR） 截取 TZ_OFFSET(timezone) 以字符字符串返回提供的timezone和UTC之间的偏移量。timezone可以被指定为时 区名或+/-HH:HI格式