本文深入探讨Oracle数据库的高级SQL查询技巧，包括复杂查询、数据字典使用、表分区、锁机制等，同时详述PL/SQL的基础与进阶应用，如存储过程、函数、异常处理等，为数据库开发者和DBA提供实用教程。
1、基本语句：
1.查询每个部门工资最高的人的详细记录 select * from emp e,(select max(sal) max,deptno from emp group by deptno)t where e.deptno = t.deptno and e.sal = t.max; 2.查询每个部门信息和该部门的员工数量 select d.deptno,d.dname,p.per from dept d ,(select count(*) per,deptno from emp group by deptno) p where d.deptno=p.deptno; 3.oracle分页： select t2.* from (select t1.*,rownum rn from emp,(select * from emp)t1 where rownum<=6)t2 where t2.rn>=4; // select t2.* from (select t1.*,rownum rn from (select * from emp)t1 where rownum<=6)t2 where t2.rn>=4; 第一层过滤(作用帅选需要查询的数据，可以是多表)：(select * from emp)t1 第二层过滤(作用帅选出前面的数据)：(select t1.*,rownum rn from emp,(select * from emp)t1 where rownum<=6)t2 第三层过滤(作用帅选出后面的数据)：select t2.* from (select t1.*,rownum rn from emp,(select * from emp)t1 where rownum<=6)t2 where t2.rn>=4; 6：代表取到第几条， 4 ： 代表从第几条开始取。 可以是一个模板，需要改动的地方有三处：select * from emp，6,4 。 如果我们需要针对不同的情况，分页，在第一次进行处理，包括多表，条件。 select * from emp where emp.sal > all(select sal from emp where deptno =30); 自连接 select e.ename,e.mgr,p.ename from emp e,emp p where e.mgr=p.empno; select avg(sal) from emp group by deptno having avg(sal)>2000 order by avg(sal) desc; select avg(sal) from emp group by deptno having avg(sal)<2000; select avg(sal),deptno from emp where sal<(select avg(sal) from emp group by deptno) group by deptno; 将1001的性别，奖金和简介等信息改成和1005相同，使用子查询更新。 update students set (sex,follwoship,resume)=(select sex,follwoship,resume from students where id=1005) where id=1001; //获取某个角色所拥有的权限 select * from dba_sys_privs where grantee='RESOURCE'; //查询 RESOURCE 角色的权限 select * from dba_sys_privs where grantee='DBA'; //查询DBA角色的权限 //查询系统权限 select * from system_privilege_map order by name; //查询用户权限 select * from dba_role_privs where grantee='用户名'; //用户名必须大写 //将查询出来的数据插入到表中，可以用作测试 insert into users select * from users; 5.外连接： 左连接：select stu.id,name,grade from stu,exam where stu.id = exam.id(+); SQL> select stu.id,name,grade from stu left join exam on stu.id = exam.id; ID NAME GRADE ---------- ---------- ---------- 1 tom 80 2 jerry 50 4 hehe 3 kiki 右连接：SQL> select stu.id,name,grade from stu,exam where stu.id(+) = exam.id; SQL> select stu.id,name,grade from stu right join exam on stu.id = exam.id; ID NAME GRADE ---------- ---------- ---------- 1 tom 80 2 jerry 50 90 完全连接：SQL> select stu.id,name,grade from stu full outer join exam on stu.id = exam.id; ID NAME GRADE ---------- ---------- ---------- 1 tom 80 2 jerry 50 4 hehe 3 kiki 90 6.表的创建 create table goods( id number primary key, name varchar2(20), price number check(price>0), category varchar2(30) not null, provider varchar(30) not null ); create table customer( custmoerId number primary key, name varchar2(30) not null, address varchar2(60), email varchar2(60) unique, sex char(2) default '男' check(sex in('男','女')), cardId varchar2(20) ); create table purchase( custmoerId number references customer(custmoerId), goodsId number references goods(id), counts number check(counts>=1 and counts<=100) ) a.修改 alter table goods modify name not null; b.添加约束： alter table customer add constraint uni_id unique(cardId) c.删除约束： alter table customer drop constraint uni_id; alter table customer drop constraint primary key [cascade]; 7.序列的使用 a.创建序列 create sequence myseq start with 1 //从1开始 increment by 1 //每次增加1 minvalue 1 //最小值 maxvalue 10000 //最大值 nocycle //是否循环，如果循环，当到达最大值时，就又会从1开始进行累计。 nocache; //是否缓存，[cache 10]表示每次产生10个序号，可以提供效率。但是有可能发生跳号。 b.使用序列 create table testseq(id number,name varchar2(20)); insert into testseq values(myseq.nextval,name) ; insert into testseq values(myseq.nextval,"tom") ; ID NAME ---------- -------------------- 1 tom 2 hehe select myseq.nextval from dual; NEXTVAL ---------- 3 create table t(id number,name varchar2(20)); //当又创建一个表时，接着还是使用myseq这个序列，它这时已经从 4 开始增长。 SQL> insert into t values(myseq.nextval,'hehe') ; //如果要想从 1 开始增长，唯一的办法就是再重新建立一个序列。 SQL> insert into t values(myseq.nextval,'hehe') ; SQL> select * from t; ID NAME ---------- -------------------- 4 hehe 5 hehe
2、常用命令：
一、ORACLE的启动和关闭 1、在单机环境下 要想启动或关闭ORACLE系统必须首先切换到ORACLE用户，如下 su - oracle a、启动ORACLE系统 oracle>svrmgrl SVRMGR>connect internal SVRMGR>startup SVRMGR>quit b、关闭ORACLE系统 oracle>svrmgrl SVRMGR>connect internal SVRMGR>shutdown SVRMGR>quit 启动oracle9i数据库命令： $ sqlplus /nolog SQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 31 13:53:53 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> startup^C SQL> startup ORACLE instance started. 2、在双机环境下 要想启动或关闭ORACLE系统必须首先切换到root用户，如下 su － root a、启动ORACLE系统 hareg －y oracle b、关闭ORACLE系统 hareg －n oracle Oracle数据库有哪几种启动方式 说明： 有以下几种启动方式： 1、startup nomount 非安装启动，这种方式启动下可执行：重建控制文件、重建数据库 读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。 2、startup mount dbname 安装启动，这种方式启动下可执行： 数据库日志归档、 数据库介质恢复、 使数据文件联机或脱机， 重新定位数据文件、重做日志文件。 执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置， 但此时不对数据文件和日志文件进行校验检查。 3、startup open dbname 先执行“nomount”，然后执行“mount”，再打开包括Redo log文件在内的所有数据库文件， 这种方式下可访问数据库中的数据。 4、startup，等于以下三个命令 startup nomount alter database mount alter database open 5、startup restrict 约束方式启动 这种方式能够启动数据库，但只允许具有一定特权的用户访问 非特权用户访问时，会出现以下提示： ERROR： ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用 6、startup force 强制启动方式 当不能关闭数据库时，可以用startup force来完成数据库的关闭 先关闭数据库，再执行正常启动数据库命令 7、startup pfile=参数文件名 带初始化参数文件的启动方式 先读取参数文件，再按参数文件中的设置启动数据库 例：startup pfile=E:Oracleadminoradbpfileinit.ora 8、startup EXCLUSIVE 二、用户如何有效地利用数据字典 ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生, 随着数据库的变化而变化, 体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。 数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。 我们不能手工修改数据字典里的信息。 很多时候，一般的ORACLE用户不知道如何有效地利用它。 dictionary 全部数据字典表的名称和解释，它有一个同义词dict dict_column 全部数据字典表里字段名称和解释 如果我们想查询跟索引有关的数据字典时，可以用下面这条SQL语句: SQL>select * from dictionary where instr(comments,'index')>0; 如果我们想知道user_indexes表各字段名称的详细含义，可以用下面这条SQL语句: SQL>select column_name,comments from dict_columns where table_name='USER_INDEXES'; 依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看ORACLE的其它文档资料了。 下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。 1、用户 查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users; 查看当前用户的角色 SQL>select * from user_role_privs; 查看当前用户的系统权限和表级权限 SQL>select * from user_sys_privs; SQL>select * from user_tab_privs; 2、表 查看用户下所有的表 SQL>select * from user_tables; 查看名称包含log字符的表 SQL>select object_name,object_id from user_objects where instr(object_name,'LOG')>0; 查看某表的创建时间 SQL>select object_name,created from user_objects where object_name=upper('&table_name'); 查看某表的大小 SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name'); 查看放在ORACLE的内存区里的表 SQL>select table_name,cache from user_tables where instr(cache,'Y')>0; 3、索引 查看索引个数和类别 SQL>select index_name,index_type,table_name from user_indexes order by table_name; 查看索引被索引的字段 SQL>select * from user_ind_columns where index_name=upper('&index_name'); 查看索引的大小 SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name'); 4、序列号 查看序列号，last_number是当前值 SQL>select * from user_sequences; 5、视图 查看视图的名称 SQL>select view_name from user_views; 查看创建视图的select语句 SQL>set view_name,text_length from user_views; SQL>set long 2000; 说明：可以根据视图的text_length值设定set long 的大小 SQL>select text from user_views where view_name=upper('&view_name'); 6、同义词 查看同义词的名称 SQL>select * from user_synonyms; 7、约束条件 查看某表的约束条件 SQL>select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper('&table_name'); SQL>select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position; 8、存储函数和过程 查看函数和过程的状态 SQL>select object_name,status from user_objects where object_type='FUNCTION'; SQL>select object_name,status from user_objects where object_type='PROCEDURE'; 查看函数和过程的源代码 SQL>select text from all_source where owner=user and name=upper('&plsql_name'); 三、查看数据库的SQL 1、查看表空间的名称及大小 select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name; 2、查看表空间物理文件的名称及大小 select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 3、查看回滚段名称及大小 select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ; 4、查看控制文件 select name from v$controlfile; 5、查看日志文件 select member from v$logfile; 6、查看表空间的使用情况 select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name; SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 7、查看数据库库对象 select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status; 8、查看数据库的版本 Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle'; 9、查看数据库的创建日期和归档方式 Select Created, Log_Mode, Log_Mode From V$Database; 四、ORACLE用户连接的管理 用系统管理员，查看当前数据库有几个用户连接： SQL> select username,sid,serial# from v$session; 如果要停某个连接用 SQL> alter system kill session 'sid,serial#'; 如果这命令不行,找它UNIX的进程数 SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr; 说明：21是某个连接的sid数 然后用 kill 命令杀此进程号。 五、SQL*PLUS使用 a、近入SQL*Plus $sqlplus 用户名/密码 退出SQL*Plus SQL>exit b、在sqlplus下得到帮助信息 列出全部SQL命令和SQL*Plus命令 SQL>help 列出某个特定的命令的信息 SQL>help 命令名 c、显示表结构命令DESCRIBE SQL>DESC 表名 d、SQL*Plus中的编辑命令 显示SQL缓冲区命令 SQL>L 修改SQL命令 首先要将待改正行变为当前行 SQL>n 用CHANGE命令修改内容 SQL>c/旧/新 重新确认是否已正确 SQL>L 使用INPUT命令可以在SQL缓冲区中增加一行或多行 SQL>i SQL>输入内容 e、调用外部系统编辑器 SQL>edit 文件名 可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器的类型，在login.sql文件中定义如下一行 DEFINE_EDITOR=vi f、运行命令文件 SQL>START test SQL>@test 常用SQL*Plus语句 a、表的创建、修改、删除 创建表的命令格式如下： create table 表名 （列说明列表）； 为基表增加新列命令如下： ALTER TABLE 表名 ADD （列说明列表） 例：为test表增加一列Age，用来存放年龄 sql>alter table test add （Age number(3)）； 修改基表列定义命令如下： ALTER TABLE 表名 MODIFY （列名 数据类型） 例：将test表中的Count列宽度加长为10个字符 sql>alter atble test modify （County char(10)）； b、将一张表删除语句的格式如下： DORP TABLE 表名； 例：表删除将同时删除表的数据和表的定义 sql>drop table test c、表空间的创建、删除 六、ORACLE逻辑备份的SH文件 完全备份的SH文件：exp_comp.sh rq=` date +"%m%d" ` su - oracle -c "exp system/manager full=y inctype=complete file=/oracle/export/db_comp$rq.dmp" 累计备份的SH文件：exp_cumu.sh rq=` date +"%m%d" ` su - oracle -c "exp system/manager full=y inctype=cumulative file=/oracle/export/db_cumu$rq.dmp" 增量备份的SH文件: exp_incr.sh rq=` date +"%m%d" ` su - oracle -c "exp system/manager full=y inctype=incremental file=/oracle/export/db_incr$rq.dmp" root用户crontab文件 /var/spool/cron/crontabs/root增加以下内容 0 2 1 * * /oracle/exp_comp.sh 30 2 * * 0-5 /oracle/exp_incr.sh 45 2 * * 6 /oracle/exp_cumu.sh 当然这个时间表可以根据不同的需求来改变的，这只是一个例子。 七、ORACLE 常用的SQL语法和数据对象 一.数据控制语句 (DML) 部分 1.INSERT (往数据表里插入记录的语句) INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……); INSERT INTO 表名(字段名1, 字段名2, ……) SELECT (字段名1, 字段名2, ……) FROM 另外的表名; 字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’ 如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''. 字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验. 日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒 或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’) TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC. 年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型, 方法借用ORACLE里自带的DBMS_LOB程序包. INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号 CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE; 其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999 INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL 2.DELETE (删除数据表里记录的语句) DELETE FROM表名 WHERE 条件; 注意：删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused. 如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间 TRUNCATE TABLE 表名; 此操作不可回退. 3.UPDATE (修改数据表里记录的语句) UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件; 如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验; 值N超过定义的长度会出错, 最好在插入前进行长度校验.. 注意事项: A. 以上SQL语句对表都加上了行级锁, 确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效, 否则改变不一定写入数据库里. 如果想撤回这些操作, 可以用命令 ROLLBACK 复原. B. 在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围, 应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段. 程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成, 其间加上COMMIT 确认事物处理. 二.数据定义 (DDL) 部分 1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等) ORACLE常用的字段类型有 CHAR 固定长度的字符串 VARCHAR2 可变长度的字符串 NUMBER(M,N) 数字型M是位数总长度, N是小数的长度 DATE 日期类型 创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面 创建表时可以用中文的字段名, 但最好还是用英文的字段名 创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE 这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间 创建表时可以给字段加上约束条件 例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY 2.ALTER (改变表, 索引, 视图等) 改变表的名称 ALTER TABLE 表名1 TO 表名2; 在表的后面增加一个字段 ALTER TABLE表名 ADD 字段名 字段名描述; 修改表里字段的定义描述 ALTER TABLE表名 MODIFY字段名 字段名描述; 给表里的字段加上约束条件 ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名); ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名); 把表放在或取出数据库的内存区 ALTER TABLE 表名 CACHE; ALTER TABLE 表名 NOCACHE; 3.DROP (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等) 删除表和它所有的约束条件 DROP TABLE 表名 CASCADE CONSTRAINTS; 4.TRUNCATE (清空表里的所有记录, 保留表的结构) TRUNCATE 表名; 三.查询语句 (SELECT) 部分 SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件; 字段名可以带入函数 例如: COUNT(*), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名), TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS') NVL(EXPR1, EXPR2)函数 解释: IF EXPR1=NULL RETURN EXPR2 ELSE RETURN EXPR1 DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数 解释: IF AA=V1 THEN RETURN R1 IF AA=V2 THEN RETURN R2 ..… ELSE RETURN NULL LPAD(char1,n,char2)函数 解释: 字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位 字段名之间可以进行算术运算 例如: (字段名1*字段名1)/3 查询语句可以嵌套 例如: SELECT …… FROM (SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2; 两个查询语句的结果可以做集合操作 例如: 并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS, 交集INTERSECT 分组查询 SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1 [HAVING 条件] ; 两个以上表之间的连接查询 SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 表名1.字段名 = 表名2. 字段名 [ AND ……] ; SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 表名1.字段名 = 表名2. 字段名(+) [ AND ……] ; 有(+)号的字段位置自动补空值 查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] ORDER BY字段名1, 字段名2 DESC; 字符串模糊比较的方法 INSTR(字段名, ‘字符串’)>0 字段名 LIKE ‘字符串%’ [‘%字符串%’] 每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性. 四.ORACLE里常用的数据对象 (SCHEMA) 1.索引 (INDEX) CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] ); ALTER INDEX 索引名 REBUILD; 一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况, 也可以建立多字段的组合索引和基于函数的索引 ORACLE8.1.7字符串可以索引的最大长度为1578 单字节 ORACLE8.0.6字符串可以索引的最大长度为758 单字节 2.视图 (VIEW) CREATE VIEW 视图名AS SELECT …. FROM …..; ALTER VIEW视图名 COMPILE; 视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化. 3.同义词 (SYNONMY) CREATE SYNONYM同义词名FOR 表名; CREATE SYNONYM同义词名FOR 表名@数据库链接名; 4.数据库链接 (DATABASE LINK) CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’; 数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义. 数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样 数据库全局名称可以用以下命令查出 SELECT * FROM GLOBAL_NAME; 查询远端数据库里的表 SELECT …… FROM 表名@数据库链接名; 五.权限管理 (DCL) 语句 1.GRANT 赋于权限 常用的系统权限集合有以下三个: CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理) 常用的数据对象权限有以下五个: ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名, DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名 GRANT CONNECT, RESOURCE TO 用户名; GRANT SELECT ON 表名 TO 用户名; GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2; 2.REVOKE 回收权限 REVOKE CONNECT, RESOURCE FROM 用户名; REVOKE SELECT ON 表名 FROM 用户名; REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2; 查询数据库中第63号错误： select orgaddr,destaddr from sm_histable0116 where error_code='63'; 查询数据库中开户用户最大提交和最大下发数： select MSISDN,TCOS,OCOS from ms_usertable； 查询数据库中各种错误代码的总和： select error_code,count(*) from sm_histable0513 group by error_code order by error_code; 查询报表数据库中话单统计种类查询。 select sum(Successcount) from tbl_MiddleMt0411 where ServiceType2=111 select sum(successcount),servicetype from tbl_middlemt0411 group by servicetype 导文件exp和imp 例子： exp cjxt/cjxt tables=dcardres file=dcardres.dmp imp gzcj/gzcj@oracle tables=dcardres file=dcardres.dmp 查看有没有锁表 SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, o.owner,o.object_name,o.object_type,s.sid,s.serial# FROM v$locked_object l,dba_objects o,v$session s WHERE l.object_id=o.object_id AND l.session_id=s.sid [Q]怎么杀掉特定的数据库会话 [A] Alter system kill session 'sid,serial#'; 或者 alter system disconnect session 'sid,serial#' immediate; 在win上，还可以采用oracle提供的orakill杀掉一个线程（其实就是一个Oracle进程） 在Linux/Unix上，可以直接利用kill杀掉数据库进程对应的OS进程 [Q]怎么快速查找锁与锁等待 [A]数据库的锁是比较耗费资源的， 特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。 这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。 可以通过alter system kill session ‘sid,serial#’来杀掉会话 SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL 如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待 以下的语句可以查询到谁锁了表，而谁在等待。 SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, o.owner,o.object_name,o.object_type,s.sid,s.serial# FROM v$locked_object l,dba_objects o,v$session s WHERE l.object_id=o.object_id AND l.session_id=s.sid ORDER BY o.object_id,xidusn DESC
三、存储过程和函数：
数据字典视图，USER_SOURCE 包含存储过程的代码文本。 ---这个做错了。可能是oracle有问题 --创建登录存储过程(通过出错来判断) create or replace procedure isCheckLogin(usid number,isTrue out varchar2) as numTemp varchar2(10); begin select name into numTemp from idtest where id=usid; isTrue:='true'; Exception when others then isTrue:='false'; end; --调用登录存储过程 declare str varchar2(10); begin isCheckLogin(25,str); dbms_output.put_line(str); end; --创建登录函数(通过出错来判断) create or replace function FunCheckLogin (usid number) return varchar2 as str varchar2(10); begin select name into str from idtest where id=usid; -- return str; return 'true'; exception when others then return '查无此人'; end; --测试登录函数 declare strTem varchar2(10); begin strTem:=FunCheckLogin(21); dbms_output.put_line(strTem); end; ============================ -----在java中调用 import java.sql.*; public Class Test { public static void main(String[] grs) { } public void }
1. set serveroutput on --打开输入内容到控制台 2. 格式：“declare begin exception end; /”,其中declare和exception是可选的。 3. 包含定义和执行部分的块： declare --定义 v_ename varchar2(20); --声明一个变量，格式是： 变量名称 变量类型 begin select ename into v_ename from emp where empno=&empno; --弹出一个对话框，将查询出的名字放到变量中 dbms_output.put_line('雇员的名字是'||v_ename); --输出名字，“||”就是连接字符串 end; / 4. 创建存储过程 create procedure proctest(in_ename in varchar2,in_new_sal in number) --in表示输入参数，out表示输出参数 is begin update emp set sal=in_new_sal where ename = in_ename; end;/ --执行存储过程： exec proctest('hehh',200); --显示错误： show error 5.函数 create or replace function fun(v_in_ename varchar2) return number is --定义返回值类型 v_annual_sal number; --定义变量 begin select (sal+nvl(comm,0))*13 into v_annual_sal from emp where ename=v_in_ename; return v_annual_sal; --返回年薪 end;/ --执行函数 SQL> select fun('SMITH') from dual; FUN('SMITH') ------------ 10400 6.包和包体 6.1 包的创建： create or replace package mypackage is procedure pro1(v_in_ename varchar2,v_in_newsal number); --过程声明 function fun1(v_in_ename varchar2) return number; --函数声明 end; 6.2 包体的创建： create or replace package body mypackage is procedure pro1(v_in_ename varchar2,v_in_newsal number) --过程 is begin update emp set sal=v_in_newsal where ename=v_in_ename; end; function fun1(v_in_ename varchar2) return number is --函数 v_annual_sal number; begin select (sal+nvl(comm,0))*13 into v_annual_sal from emp where ename=v_in_ename; return v_annual_sal; end; end;/ 6.3 执行包中的过程和函数 select mypackage.fun1('SMITH') from dual; --执行函数，可以在包名前面加上方案名 exec mypackage.pro1('hehh',2); --执行过程，格式可以为：方案名.包名.过程名[函数名] call scott.mypackage.pro1('hehh',222); --执行过程 7.案例： create or replace procedure prol(v_in_empno in number) is v_tax_rate number(3,2):=0.03; --赋值时应注意它的格式是：“变量名 变量类型 :=变量值” v_ename varchar2(20); --定义变量 v_sal emp.sal%type; --为了使变量的定义更加灵活，我们采用%type这样的方式。 v_tax number; begin select ename,sal into v_ename,v_sal from emp where empno = v_in_empno; v_tax:=v_sal*v_tax_rate; --计算变量值 dbms_output.put_line('用户 '||v_ename||' 的工资是 '||v_sal||'个人所得税是 '||v_tax); end;/ 8.创建记录类型 create or replace procedure proll(v_in_empno in number) is type db_emp_record is record( --定义一个记录类型，可以将它看成是一个类 v_ename emp.ename%type, --定义变量，可以看成是类中的变量 v_sal emp.sal%type, v_job emp.job%type ); v_emp_record db_emp_record; --定义一个变量类型为db_emp_record，可以看成是类的变量 begin select ename,sal,job into v_emp_record from emp where empno = v_in_empno; --可以使用v_emp_record变量访问该类型里面的变量，可以看成是类变量访问类中的成员 dbms_output.put_line('名字'||v_emp_record.v_ename||'薪金'||v_emp_record.v_sal||'工作'||v_emp_record.v_job); end;/ 9.游标 create or replace procedure proll(v_in_deptno in number) is type db_emp_cursor is ref cursor; --定义游标类型 v_emp_cursor db_emp_cursor; --定义一个游标变量 v_ename emp.ename%type; v_sal emp.sal%type; begin open v_emp_cursor for select ename,sal from emp where deptno=v_in_deptno; --打开游标 loop --取出游标执行的每行数据，用循环语句 fetch v_emp_cursor into v_ename,v_sal; --获取数据 exit when v_emp_cursor%notfound; --如果没有数据，游标就退出 dbms_output.put_line('用户名'||v_ename||'薪水'||v_sal); if v_sal < 1000 then --对于员工工资小于1000的，就加100元工资 update emp set sal = sal+100 where emp.ename=v_ename; end if; end loop; close v_emp_cursor; --使用完后，一定要关闭游标 end; 10.控制语句 10.1 if .. then ..end if (if 语句) create or replace procedure proll(v_in_empno in number) is v_sal emp.sal%type; begin select sal into v_sal from emp where empno = v_in_empno; if v_sal<2000 then update emp set sal=sal*1.1 where empno = v_in_empno; dbms_output.put_line('薪水'||v_sal); end if; end; 10.2 if .. then ..else .. end if (类似if else 语句) create or replace procedure proll(v_in_empno in number) is v_sal emp.sal%type; begin select sal into v_sal from emp where empno = v_in_empno; if v_sal<2000 then update emp set sal=sal*1.1 where empno = v_in_empno; dbms_output.put_line('薪水'||v_sal); else update emp set sal=sal*1.05 where empno = v_in_empno; dbms_output.put_line('薪水'||v_sal); end if; end; 10.3 if .. then ..elsif ..then .. end if (类似if 条件表达式 else if 条件表达式 [else]语句) create or replace procedure proll(v_in_empno in number) is v_sal emp.sal%type; begin select sal into v_sal from emp where empno = v_in_empno; if v_sal<2000 then update emp set sal=sal*1.1 where empno = v_in_empno; elsif v_sal =3000 then update emp set sal=sal+500 where empno = v_in_empno; else update emp set sal=sal*1.05 where empno = v_in_empno; end if; dbms_output.put_line('薪水'||v_sal); end; 10.4 循环控制语句（loop ... end loop）重复n次向表中插入数据 create or replace procedure proll(v_in_name varchar2,n in number) is v_count number:=1; --开始值为1 begin loop exit when n<=0; --如果n小于零，表示无效，那么就退出loop循环 insert into test values(v_count,v_in_name||v_count); exit when v_count=n; --一共执行n次就结束，退出loop循环 v_count:=v_count+1; --自增 end loop; end; 10.5 while循环 create or replace procedure proll(v_in_name varchar2,n in number) is v_count number:=11; begin while v_count<11+n loop --当条件为假时，就退出循环 insert into test values(v_count,v_in_name||v_count); v_count:=v_count+1; end loop; end; 11. 返回多条记录的存储过程 11.1 先创建一个包，里面包含一个游标类型 create or replace package mypagepackage is type my_cursor is ref cursor; --定义一个游标变量 end; ----------------------------------------------- 11.2 创建存储过程，返回类型是游标 create or replace procedure proll(v_in_deptno in number,v_out_result out mypagepackage.my_cursor) is open v_out_result for select * from emp where deptno = v_in_deptno; --为了让java程序可以使用游标，不能再这里关闭游标。 end; 11.3 在java中调用，用到的对象CallableStatement. 12. 使用存储过程实现分页 create or replace procedure fenyepro(v_in_table in varchar2,v_in_pagesize in number, v_in_currentpage in number,v_out_result out mypagepackage.my_cursor, v_out_allrecords out number,v_out_pagecount out number,v_in_orderfield in varchar2) is v_start number; v_end number; v_sql varchar2(2000); v_all_records varchar2(100); begin v_start:=v_in_pagesize*(v_in_currentpage-1)+1; --开始记录 v_end:=v_in_pagesize*v_in_currentpage; --结束记录 --拼接sql语句 v_sql:='select t2.* from (select t1.*,rownum rn from (select * from' || v_in_table||')t1 where rownum<='||v_end||')t2 where rn>='||v_start||'order by'||v_in_orderfield; open v_out_result for v_sql; --打开游标 --v_all_records:='select count(*) into'|| v_out_allrecords || 'from' || v_in_table; select count(*) into v_out_allrecords from v_in_table; --获取总记录 if mod(v_out_allrecords,v_in_pagesize)=0 then --计算总页数 v_out_pagecount:=v_out_allrecords/v_in_pagesize; else v_out_pagecount:=v_out_allrecords/v_in_pagesize+1; end if; end;
四、表分区和锁
锁的优点:一致性完整性并发性.有行级锁(insert,update),表级锁 行级锁:select * from tableName where id=22 for update wait 5 只等等待5秒. 表级锁:LOCK TABLE tableName IN lockName MODE nowait(请求的资源被锁定则不等待，立即报错) lockName:share表示共享锁,exclusive排他锁 ------------表分区 优点:提高查询性能和数据安全性,便于维护和管理表. -------- 范围分区: PARTITION BY RANGE (column_name) ( PARTITION part1 VALUE LESS THAN(range1) [TABLESPANCE tbs1], PARTITION part2 VALUE LESS THAN(range2) [TABLESPANCE tbs2], PARTITION part1 VALUE LESS THAN(MAXVALUE) [TABLESPANCE tbs1] ); create table sal ( id varchar2(10), price number(10) ) partition by sal (price) ( partition p1 values less than(100), partition p2 values less than(500), partition p3 values less than(maxvalue) ); 如果price是日期类型,则than(to_date('2007-1-1','YYYY-MM-DD')), -------- 散列分区 PARTITION BY HASH (column_name) ( PARTITION part1 [TABLESPANCE tbs1], PARTITION part2 [TABLESPANCE tbs2], ) create table my_emp ( empid number(3), empName varchar2(10) ) partition by hash(empName) ( partition p1, partition p2 ); -------- 复合分区 PARTITION BY RANGE (column_name1) SUBPARTITION BY HASH (column_name2) SUBPARTITIONS number_of_partitions [STORE IN (tablespace_list)] ( PARTITION part1 VALUE LESS THAN(range1) [TABLESPANCE tbs1], PARTITION part2 VALUE LESS THAN(range2) [TABLESPANCE tbs2], PARTITION part1 VALUE LESS THAN(MAXVALUE) [TABLESPANCE tbs1] ) create table salee ( proid varchar2(5), sales_date date, sales_cost number(10) ) partition by range (sales_date) subpartions by hash (sales_cost) subpartions 5--散列分区分多少区的数量 ( partition p1 values less than (to_date('2007-1-1','YYYY-MM-DD')), partition p1 values less than (to_date('2007-2-1','YYYY-MM-DD')), partition p1 values less than (maxvalue)) ); 总共创建了3*5=15个分区. ------------ 列表分区 PARTITION BY LIST (column_name) ( partition p1 values(values_list1), partition p2 values(values_list2), partition p3 values(default) ); create table employee ( emp_id number(10), emp_address varchar2(20) ) partition by list (emp_address) ( partition north values('芝加哥'), partition west vaues('旧金山','洛杉矶') ); 如果输入的地址是北京,则oracle将拒绝该值,因为没有创建可以包含该值的分区. ---------------如何在SQL语句中使用表分区 ---查询 select * form my_emp partition (part1); --删除表分区中的记录 delete from my_emp partiton (part2); --------------------------------------------- 分区维护操作 1.添加分区 alter table seles ADD PARTITION p4 VALUES LESS THAN (1000); 2.删除分区 alter table sales DROP PARTITION p4; 3.截断分区 alter table sales TRANCATE PARTITION p3; 4.全并分区 ALTER TABLE table_name MEGER PARTITIONS partitions_name ,partition_name INTO PARTITION partition_name 5.拆分分区 ALTER TABLE table_name SPLIT PRATITION partition_name AT (value) INTO (PARTITION p1,PARTITION p1,) 示例:alter table sales2 SPLIT partition p3 AT (to_date('','YYYY-MM-DD')) INTO (partition p31,partition p32); 6.分区重命名 alter table sales2 RENAME PARTITION p31 to p3; alter table sales2 RENAME PARTITION p32 to p4;
五、系统DBA:
数据字典视图 ============================ --用户授权篇 -------------------------- --1检查授予用户的系统特权 通过查询user_sys_privs可以检查某个用户具有哪些系统特权 username:表示当前用户的用户名 priviege:系统特权 admin_option:该用户是否能将这种特权授予其他用户. --2检查已授予的对象特权 通过查询user_tab_privs_made可以检查某个用户对哪些表向其他用户开放了哪些对象特权. grantee:该特权所授予的用户 table_name:所授予的特权所操作的对象(例如表)名 grantor:授权者 privilege:对该对象授予的特权 grantable:特权所授予的用户是否可以将这种特权再授予其他用户yes/no hierarchy:特权是否构成层次关系.yes/no --通过查询user_col_privs_made可以检查某个用户对哪些列对象向其他用户开放了哪些特权. --3检查已接受的对象特权 通过查询user_tab_privs_recd可以检查某个用户被授予了哪些表上的哪些对象特权. owner:表示拥有该对象的用户. table_name:所授予的特权所操作的对象(例如表)名. grantor:同上. privilege:同上 grantable:同上 hierarchy:同上 通过查询user_col_privs_recd可以检查某个用户被授予了哪些列的对象特权. ================================= --角色篇 ----------------------------------- --1检查授予用户的角色 通过查询user_role_privs可以检查已经授予一个用户的哪些角色. username:该角色所授予的用户名 granted_role:授予该用户的角色名 admin_option:该用户是否可以将该角色授予其他用户或角色.yes/no default_role:当用户登录到数据库上时,是否默认启用该角色.yes/no os_granted:该角色是否是由操作系统授予. --2检查授予角色的系统特权 通过查询role_sys_privs可以检查已经授予一个角色哪些系统特权. role:角色名. privilege:授予该角色的系统特权 admin_option:授予该特权时是否使用with admin option选项.yes/no --3检查授予角色的对象特权 通过查询role_tab_privs可以检查已经授予一个角色哪些对象特权. role:该特权所授予的用户 owner:拥有该对象的用户 table_name:所授予的特权所操作的对象名 column_name:列名(如果适合) privilege:对该对象的特权. grantable:授予该特权时是否使用with grant option选项.yes/no ---4默认角色 默认角色情况下,在将角色授予用户时,就为该用户启用了这个角色.这就是说.在用户连接数据库上时,就可以自动使用这种角色.为了安全性. 我们可默认地禁用一个角色,在用户连接上数据库后,要使用这个角色时,如果这个角色是有密码的,就要输入密码.才能使用这个角色. --修改默认角色为非默认角色. alter user steve default role all except roleName; --使用这个角色 set role roleName identified by password; --清空角色的设置 set role none; ================================================= --数据库的名字,创建日期 select naem,created,log_mode(归档和非归档),open_mode(读写状态) from v$database --oracle数据库的主机名,实例名.版本 select host_name,instance,version from v$instance; --select * from v$version; --select * from v$controlfile; --重做日志的配置信息 select group#,members(成员),bytes,status,archived from v$log; --数据库的备份和恢复策略并确定归档文件的具体位置 archive log list; ---数据库上有多少用户和什么时创建数据库的 select username,created from dba_users; ----转移控制文件 sql>shutdown immediat 数据库已经关闭,已经卸载数据库,oracle例程已经关闭 sql>hot copy D:\ORACLE\CONTROL01.CTL D:\DISK3\CONTROL01.CTL SQL>hot copy D:\ORACLE\CONTROL01.CTL D:\DISK6\CONTROL02.CTL sql>hot copy D:\ORACLE\CONTROL01.CTL D:\DISK9\CONTROL03.CTL 修改初始化参数文件init.ora,并对CONTROL_FILES参数做相应的修改. 重新启动数据库 sql>startup; sql>select * from v$controlfile; d:\disk3\control01.ctl d:\disk6\control02.ctl d:\disk9\control03.ctl ====================== ----重做日志配置 --1将每个重做日志组的成员(文件)都增加到3个.增加日志组 sql>alter database add logfile ('j:\disk3\moon\onlinelog\redoo4A.log', 'j:\disk6\moon\onlinelog\redoo4B.log', 'j:\disk9\moon\onlinelog\redoo4C.log',) --2删除重做日志组第3组-必须要删除的组是非活动状态(inactive) alter database drop logfile group 3; --检查重做日志组的状态 select group#,sequence#,bytes,members,status,archived from v$log; members:成员,一组日志组有多个文件. status:状态有3种Active(活动状态)InActive(非活动状态);current(当前重做日志状态);unused(未使用) --重做日志切换 alter system switch logfile; --当前组(current)变为活动组(active) --alter system switch logfile;--当前组下移成下一组,活动组为非活动组(inactive); ---数据字典 user_triggers select object_name,object_type from user_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY'); --查看约束（从数据字典中获取scott用户所拥有的约束的信息） select constraint_name,table_name,constraint_type,status, deferrable,deferred,validated from dba_constraints where owner='scott'; 获得变长数组信息 user_varray ======================================= alter database open;
六、字典表：
查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users; 查看当前用户的角色 SQL>select * from user_role_privs; 查看当前用户的系统权限和表级权限 SQL>select * from user_sys_privs; SQL>select * from user_tab_privs; 查看用户下所有的表 SQL>select * from user_tables; 显示用户信息(所属表空间) select default_tablespace,temporary_tablespace from dba_users where username='GAME'; 1、用户 查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users; 查看当前用户的角色 SQL>select * from user_role_privs; 查看当前用户的系统权限和表级权限 SQL>select * from user_sys_privs; SQL>select * from user_tab_privs; 显示当前会话所具有的权限 SQL>select * from session_privs; 显示指定用户所具有的系统权限 SQL>select * from dba_sys_privs where grantee='GAME'; 显示特权用户 select * from v$pwfile_users; 显示用户信息(所属表空间) select default_tablespace,temporary_tablespace from dba_users where username='GAME'; 显示用户的PROFILE select profile from dba_users where username='GAME'; 2、表 查看用户下所有的表 SQL>select * from user_tables; 查看名称包含log字符的表 SQL>select object_name,object_id from user_objects where instr(object_name,'LOG')>0; 查看某表的创建时间 SQL>select object_name,created from user_objects where object_name=upper('&table_name'); 查看某表的大小 SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name'); 查看放在Oracle的内存区里的表 SQL>select table_name,cache from user_tables where instr(cache,'Y')>0; 3、索引 查看索引个数和类别 SQL>select index_name,index_type,table_name from user_indexes order by table_name; 查看索引被索引的字段 SQL>select * from user_ind_columns where index_name=upper('&index_name'); 查看索引的大小 SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name'); 4、序列号 查看序列号，last_number是当前值 SQL>select * from user_sequences; 5、视图 查看视图的名称 SQL>select view_name from user_views; 查看创建视图的select语句 SQL>set view_name,text_length from user_views; SQL>set long 2000; 说明：可以根据视图的text_length值设定set long 的大小 SQL>select text from user_views where view_name=upper('&view_name'); 6、同义词 查看同义词的名称 SQL>select * from user_synonyms; 7、约束条件 查看某表的约束条件 SQL>select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper('&table_name'); SQL>select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position; 8、存储函数和过程 查看函数和过程的状态 SQL>select object_name,status from user_objects where object_type='FUNCTION'; SQL>select object_name,status from user_objects where object_type='PROCEDURE'; 查看函数和过程的源代码 SQL>select text from all_source where owner=user and name=upper('&plsql_name');
七、同义词,序列,视图,索引
这些都是数据库对象 ------------------------------------- =======同义词(别名) 默认是私有的 --创建 create or replace [public] SYNONYM [schema.]synonym_name for [schema.]object_name create synonym emp form scott.emp; select * from emp; --删除 drop [public] synonym [schema.]synonym_name ======序列 ---创建 create sequence sequence_name [SART WITH integer] --要生成的序列号的第一个序列号 [INCREMENT BY integer] --序列号间的间隔数,正数为升序,负数为降序 [MAXVALUE integer|NOMAXVALUE] --可以生成的最大值.指定升序最大值10 的27次方.降序最大值-1 [MINVALUE integer|NOMINVALUE] --可以生成的最小值.必须小开或等于 strat with的值,且必须小于最大值 指定,升序的最小值为1,降序的最小值 -10的26次方 [CYCLE|NOCYCLE]--是否循环/到达最大值或最小值,将不继续生成值(默 认选项), [CACHE integer|NOCACHE] ----是把序列号缓存可以加快访问序列/默认 选项,缓存20个序列号. ----访问 synonym_name.nextval:第一次使用,返回该序列的初始值.以后,加值,返回 新值. synonym_name.currval:返回序列的当前值,即最后一次引用nextval时的返 回值. ---更改序列 alter sequence [schema.]sequence_name............ 注意:不能修改序列的start with参数.在修改序列时,应注意升序序列的最小值小于最大值. ---删除序列 drop sequence [schema.]sequence_name; ==============视图 --创建 create or replace view view_name as select; --创建带有错误的视图:就是类似java中的抛出.当这个视图内容有错时,会有错误提示出现 create or replace force view view_name as select; --删除 drop view view_name; ----在视图中是可以带有update语句(键保留表:在视图的表是具有主键的表而不是具有外键的表)的,还可以左右联接 ===========索引
八、PL/SQL:
---声明变量 declare total constant number:=200; begin end; ^^^^^^^^^^^^^数据类型 ---数字数据类型 1.binary_integer 2.pls_integer:这两类是用于存储带符号的整数。 3.number;指定精度（总位数），小数位数（即小数点右侧的位数。会四舍五入）； ---字符数据类型 1.char() 2.varchar2:-->string,varchar 3.raw:用于存储二进制数据或字节串(固定长度). 4.long和long raw ----日期时间数据类型 1.date sysdate:返回当天日期和时间 2.TimeStamp:同上,只是更有时间精度.包括分秒微秒.systimestamp; 3.boolean:用于存储逻辑值true,false,null. 不能把它插入数据库中,只能允许对boolean变量执行逻辑操作 ----LOB(大对象)类型 1.BLOB:(bit二进制)相当于把多媒体对象(视频等)存入到此类型中. 2.CLOB:(text)相当于把一个新闻稿(长稿)存入此类型中 3.BFILE:不是把文件给存到数据库中,而是相当于把文件指针存入到此类型 ----属性类型 1.%type :某个表中的某个字段类型. strTemp emp.name%tepe; 2.%rowtype:整个行的字段类型都得到了. strTemp emp%rowtypType ^^^^^^^^^^^^^逻辑比较 = 等于; <>,!= 不等于;<,<=,>,>= ^^^^^^^^^^^^^控制结构 1.条件控制: if 条件 then 主体 end if if 条件 then 主体1 ELSE 主体2 end if if 条件1 then 主体1 ELSIF 条件2 then 主体2 end if case selector when 结果 then 返回值; else 返回值; end case; case when 条件 then 返回值 when num between 20 and 30 then 返回值; else 返回值; end case; 示例用select中用case select product_id,product_type_id, case when product_id=1 then 'book' when product_id=2 then 'video' else 'cd' end from product; 2.循环控制(3种) --Loop 主体 exit/exit when(退出循环) 条件; end Loop; --While 条件 Loop end Loop; --For 变量 IN 1..25 Loop 主体 end Loop 示例For in for d in (select * from userinfo) loop if d.name='aa' then <主体> elsif d.name='bb' then <主体> else <主体> end if; end loop; 3.顺序结构 goto ,null ^^^^^^^^^^^^^异常处理 Exception when 异常类型 then 主体 通常在语句的最后做处理 others能处理程序确保不会漏过任何异常 示例 Exception begin update userinfo set uname='abc' where uid='aa'; insert into userinfo values('1001','bbb'); Exception when others then rollback; end; savepoint aa; rollback to savepoint aa; commit; rollback;
扫一扫
1092
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
