当前位置:首页 >> 计算机软件及应用 >>

Oracle学习总结 收藏


原创 Oracle 学习总结 收藏 Oracle:一个数据库有多个用户,一个用户拥有一组数据库对象,这个用户我们俗称为方案, 通常一个业务系统对应一个数据库用户。 ★Oracle 安装成功后会自动创建十来个系统界别的账户。 默认情况下这些账户中只有四个可 用, 其它的帐号都被设置成了锁定或是设置为过 期状态, 这四个帐户就是 SYS, SYSTEM, SYSMAN,DBSNMP。SYSTEM 帐户有着访问数据库中所有对象的权限。

★Oracle 操作工具很多,如果是黑窗口就用 sqlplus 命令。 ★Oracle 常用操作工具:PL/SQL Development。 ★导出命令:exp,将用户的对象导出成 dmp 文件(备份) 。 ★导入命令:imp,将用户导出的 dmp 文件导入到数据库(恢复) 。 ★dual 是一个虚拟表,用来在特定情况下写出符合规范的 SQL 语句。 如:(1)用 sql 来完成数学运算:SELECT 1+1 FROM dual; (2)用 sql 来调用系统函数(当前时间):SELECT sysdate FROM dual; (3)用 sql 来调用某张表序列:SELECT seq_temp.nextval FROM dual; ★两个系统函数 sysdate,to_date to_date 将一个字符串转换成日期类型: SELECT to_date(‘2009-03-22 23:22:15’,’yyyy-mm-dd hh24:mi:ss’) FROM dual; ★在执行 SQL 时字符串一定要用单引号('')括起来。 ★Oracle 中关键字和表名、字段名都不区分大小写,相当灵活。 ★Oracle 中:=是赋值运算符,=是比较运算符。 ★nvl(comm,0)是个函数,当 comm 为 null 时,返回 0。 ★在 oracle 中这里的表名用别名时不能加 as 关键字。 SQL(Structured Query Language,结构查询语言),这里的练习只针对 Oracle 数据库。 SQL 所有操作: DML(Data Manipulation Language,数据操作语言) :用于检索或者修改数据。 DML 包括: SELECT:用于检索数据; INSERT:用于增加数据到数据库; UPDATE:用于从数据库中修改现存的数据 DELETE:用于从数据库中删除数据。 DDL(Data Definition Language,数据定义语言) : 用于定义数据的结构,比如 创建、 修改或者删除数据库对象。 DDL 包括:DDL 语句可以用于创建用户和重建数据库对象。下面是 DDL 命令: CREATE TABLE:创建表 ALTER TABLE DROP TABLE:删除表 CREATE INDEX

DROP INDEX DCL(Data Control Language,数据控制语言) :用于定义数据库用户的权限。 DCL 包括: ALTER PASSWORD GRANT REVOKE CREATE SYNONYM 一:DCL(数据控制语言) 1、创建用户 test2,密码也是 test2(记得最有以分;号结束) : create user test2 identified by test2; 2、给 test2 授权:create session;(允许用户登陆 Oracle) : grant create session to test2; 3、给 test2 分配创建表的权限; grant create table to test2; 4、给 test2 分配表空间的使用权限; grant unlimited tablespace to test2; 二:DDL(数据定义语言练习) 1、创建表:SQL> create table t_user( userid number(30) not null primary key, username varchar(20) not null, age number(3), sex varchar(2), departid number(30) not null, foreign key(departid) references t_depart(departid) ); ★alter table student add primary key(userid)这样用 alter 也可以创建关联主外键。 2、删除表:SQL> drop table t_depart; 3、创建序列: create sequence seq_a minvalue 1000 maxvalue 99999999 start with 1000 increment by 1 nocache; 三:DML(数据操作语言) : 1、insert 插入 SQL: (1)insert into t_depart (departid,departname,createdate) values (1,'市场部',sysdate); (2)insert into t_user values (seq_user.nextval,'马文涛',23,'男'); 2、delete 删除 SQL: (1)delete t_user;(太可怕了,如果在删除时不加条件,则把此表中的所有数据都会

删除!) (2)delete t_user where userid = 3; 3、update 更新 SQL: (1) update t_user set username = '争伟',sex = '男'; (太恐怖了,更新时不加条件表中所有行记录的姓名都被修改了!) (2)update t_user set username = '文涛' where userid = 7; ★小结:我发现在增、删、改的 SQL 语句中用不到 from 关键字。 4、select 查询 SQL:格式——>select· · ·from· · ·where· · ·group by· · ·having· · ·order by· · ·; (1)查询所有的用户:select * from t_user; ★在 oracle 中这里的表明用别名时不能加 as 关键字 如: select * from t_user u;正确 select * from t_user as u;错误 (2)查询指定的列: select username,sex from t_user; (3)as 给列以别名显示:select username as 用户名 from t_user;(这里的 as 关键字可 以省略) (4)distinct 去掉重复的行:select distinct username from t_user; (5)使用运算符:select age+10 from t_user;(给每个人的年龄都加 10 岁) (6)连接字符串: select '用户名:' || username from t_user;(Oracle 用||做连接字符 串操作符) (7)where 子句:select * from t_user where username = '宝宝'; (8)between and 在· · ·之间: select * from t_user where userid between 9 and 10;这也等价于下面这条 SQL: select * from t_user where userid >=9 and userid <=10; (9)in 匹配集合中的任意值:select * from t_user where username in('马文涛','宝宝'); (10)like 模糊查询:%匹配 0 个或多个任意字符串,_匹配 1 个任意字符串。 select * from t_user where username like '%涛%'; (11)null 判断某列为空:select * from t_user where sex is null; (这里用 is,不能用=,如果要返回不为 null 的记录就可以用 is not null) (12)order by 排序:ASC: 升序排列(可以省略),DESC: 降序排列

升序:select u.userid,u.username from t_user u order by u.userid; 降序:select u.userid,u.username from t_user u order by u.userid desc; (13)系统函数(对一组数据进行处理,返回一个值): AVG–求平均值,COUNT–统计记录数,MAX–最大值,MIN–最小值,SUM –求和 <1>返回最小和最大的用户编号: select min(userid),max(userid) from t_user; <2>返回总记录数: select count(*) from t_user; <3>返回某个字段不为空的记录数: select count(sex) from t_user; <4>返回不为空且不重复的记录数: select count(distinct sex) from t_user; (14)group by 分组(分组了就不能直接返回*,经常和聚合函数 count(age)一起使用): <1>按姓名分组,并统计每组人数:select count(*),username from t_user group by username; <2>根据多个字段分组 : select username,age,count(*) from t_user group by username,age; ◆group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须 出现在 group by 后面。 (15)having 过滤分组: select username from t_user group by username having count(*) > 2; (16)子查询(子查询自身只能返回一个单独的值) : <1>子查询放在 select 后面,作为其中的一个字段返回。 select u.username,(select d.departname from t_depart d where d.departid = u.departid) from t_user u; (返回用户和所属的部门,这中子查询理论上先执行外面的查询,只是我的 感觉哦,嘿嘿!) <2>子查询放在 from 后面,作为一张临时表。 select * from (select username,sex s from t_user where departid=1) where s = '男'; (这种子查询应该先执行里面) <3>子查询放在 where 后面,作为条件的一部分。 select * from t_user where departid = (select departid from t_depart where departname = '财务部'); (这种子查询也应该先执行里面) (17)联合查询(当 n 张表连接时, 需要 n-1 个连接条件): <1>等值连接(内连接): select u.username,d.departname from t_user u,t_depart d where u.departid = d.departid; <2>外连接:即把不满足条件的记录也返回,用个+就行了, (+)操作符在哪边就代表另外一边不满足联合条件的记录可以被输出。这个 感觉不太常用。 select b.book_id,b.book_name from book_info as b,book_click_num as c

where b.book_id = c.book_id(+);

四、触发器 trigger:当特定事件发生时自动执行的代码块。 这些事件包括: (1)DML 语句(INSERT,UPDATE,DELETE):before 在 DML 语句被执行前触发操作,after 在 DML 语句被执行后触发操作。 (2)DDL 语句(CREATE 及 ALTER) (3)系统事件,例如启动/关闭[startup/shutdown]、错误[error] (4)用户事件, 例如登录/退出[logon/logoff] ★两个特殊的变量——>:new 新的记录值,:old 保留原来的记录值 简单示例:create or replace trigger update_depart_trigger after update on t_depart for each row begin update t_user u set u.departid = :new.departid where u.departid = :old.departid; end update_depart_trigger; 此示例的作用:当部门的 id 改变后, 由于用户中有外键引用了部门, 所以用户的 departid 这个外键自动执行更新操作。 (这个触发器很奇怪,在黑窗口用 sqlplus 就不能创建,用 plsql Developer 工具就可以创 建,很有意思,嘿嘿!) 五、游标 cursor:以循环取 SQL 语句的 SELECT 内容,它是存放结果集的数据对象,使用游标, 我们只能逐条记录地得到查询结果。 作用: 查询数据库, 获取记录集合(结果集)的指针, 可以让开发者一次访问一行结果集, 在每条结果集上作操作。 使用: 用游标有四种基本的步骤:声明游标(declare)、 打开游标(open)、 提取数据(fetch)、 关闭游标(close)。 ★当你要往每一行插入一个数据只能用游标, 或者更新结果集中的每行记录时也可用游 标,用存储过程返回一个结果集。 ★触发器和存储过程会和数据库绑定,即一直保存在数据库中,而游标不会,它是任意 时刻创建再打开再执行再关闭, 与数据库没有任何直接关系。 ★在 Oracle 中,不需要显示销毁游标.因为在 Oracle 中,很多东西是由 JAVA 写的.Oracle 会 自动销毁游标。 简单示例: -- 定义一个游标 declare cursor cursor_user is select username,age from t_user; //变量的定义也可以放到游标定义上面 a t_user.username%type;//定义个 a 变量,类型是 t_user 表中 username 列的类

型。 b t_user.age%type;//同上 begin//SQL 中可执行代码都在 begin 和 end 之间。 -- 打开游标 open cursor_user; -- 遍历游标 loop//循环抓取数据(loop 是其中一种循环方式) fetch cursor_user into a,b; -- 将一行记录放入到变量中 dbms_output.put_line(a || ' ' || b);打印到输出控制台 exit when cursor_user%notfound; -- 当没有记录时退出循环 end loop;//退出循环 -- 关闭游标 close cursor_user; end; 加 if 条件判断游标示例: declare cursor cursor_user is select username,age from t_user; a t_user.username%type; b t_user.age%type; begin open cursor_user; loop fetch cursor_user into a,b; if a='宝宝'and b=43 then dbms_output.put_line(a || ' end if; exit when cursor_user%notfound; end loop; close cursor_user; end;

' || b);

六、存储过程 procedure 概念:其实就是一组存放在数据库中 SQL 语句,普通 SQL 操作都在项目中写死的,而他 只跟数据库进行绑定。 更准确的说存储过程是数据库服务器端的一段程序,它有两种类型。一种类似于 SELECT 查询,用于检索数据,检索到的数据能够以数据集的形式返回给客户 (oracle 存储过 程本身没返回值,只是用 out 参数代替)。另一种类似于 INSERT 或 DELETE 查询,它不返回数

据, 只是执行一个动作。 有的服务 器允许同一个存储过程既可以返回数据又可以执行动作。 优点: 1、提高效率。存储过程本身的执行速度非常快,而且,调用存储过程可以大大减 少同数据库的交互次数。 2、提高安全性。假如将 SQL 语句混合在 JSP 代码中,代码外漏以后,也就意味着 库结构外漏。 3、有利于 SQL 语句的重用。 ★oracle 函数有返回值,但存储过程没有返回值,它的所有返回值都是通过 out 参数来 替代的。 ★什么时候需要用存储过程 如果服务器定义了存储过程, 应当根据需要决定是否要用存储过程。 存储过程 通常是一些经常要执行的任务, 这些任务往往是针对大量的记录而进行的。 在服务器上执行 存储过程,可以改善应用程序的性能。这是因为: .服务器往往具有强大的计算能力和速度。 .避免把大量的数据下载到客户端,减少网络上的传输量。 例如,假设一个应用程序需要计算一个数据,这个数据需要涉及到许多记录。 如果不使用存储过程的话,把这些数据下载到客户端,导致网络上的流量剧增。 不仅如此,客户端可能是一台老掉牙的计算机,它的运算速度很慢。而改用存储过 程后,服务器会很快地把数据计算出来,并且只需传递一个数据给客户端,其效率之高是非 常明显的。

★存储过程的参数 要执行服务器上的存储过程,往往要传递一些参数。这些参数分为四种类型: 第一种称为输入参数(in),由客户程序向存储过程传递值。 第二种称为输出参数(out),由存储过程向客户程序返回结果。 第三种称为输入/输出参数(in out),既可以由客户程序向存储过程传递值,也可以由 存储过程向客户程序返回结果。 第四种称为状态参数,由存储过程向客户程序返回错误信息。 要说明的是,并不是所有的服务器都支持上述四种类型的参数,例如,InterBase 就不 支持状态参数。

简单示例: 1、无返回值存储过程(插入一个用户) create or replace procedure saveuser ( username in varchar2 , age in number, departid in number //定义两个输入参数,参数类型可以是自己写死,也可以用某个列的类型 比如:username t_user.username%type; ) as begin

insert into t_user (seq_user.nextval,username, age,departid); end saveuser;

(userid,username,age,departid)

values

(1)在 sql 中执行存储过程 call saveuser('文涛',23,1);, 也可以用 execute 和 exec。 (2)在 java 里调用时就用下面的代码: package com.hyq.src; import java.sql.*; import java.sql.ResultSet; public class TestProcedureOne { public TestProcedureOne() { } public static void main(String[] args ){ String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:ORCLA"; Statement stmt = null; ResultSet rs = null; Connection conn = null; CallableStatement cstmt = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, " hyq ", " hyq "); CallableStatement proc = null; proc = conn.prepareCall("{ call saveuser(?,?,?) }"); proc.setString(1, "马文涛"); proc.setInt(2,23); proc.setInt(3,1); proc.execute(); } catch (SQLException ex2) { ex2.printStackTrace(); } catch (Exception ex2) { ex2.printStackTrace(); } finally{ try { if(rs != null){ rs.close(); if(stmt!=null){ stmt.close();

} if(conn!=null){ conn.close(); } } } catch (SQLException ex1) { } } } } 2、有返回值的存储过程(oracle 存储过程本身没返回值)只是用 out 参数代替(非列表) //获得某部门下的所有用户姓名和年龄 create or replace procedure pro_getUserByDepart//oracle 中不区分大小写, 这里只是为了 自己方便。 ( i_departid in t_user.departid%type, o_username out t_user.username%type, o_age out t_user.age%type )as begin select username,age into o_username,o_age from t_user where departid = i_departid; end pro_getUserByDepart; ★在 pl/sql Developer 中右击存储过程选择测试就可以在下面输入值直接看打印结果了。 ★由于这个存储过程会返回行记录集,所有在 Developer 中为了测试成功就在此过程中 加入 rownum=1 条件判断, 即 :select username,age into o_username,o_age from t_user where departid = i_departid and rownum=1; 如果不加那个 rownum=1(当然你也可以让它等于 2), 多行记录会报 “实际返回的行数超 出请求的行数”这个异常。 ★其实我们用上面这种存储过程一般都只返回一行记录集, 如果是返回多行那就要用到 要用包 pagkage 和游标 cursor 了。 ★在利用 select...into...语法时(把查询的结果放入输出参数中),必须先确保数据库中有 该条记录,否则会报出"no data found"异常。 3、返回列表(必须要用包了,package 里包含了游标),分两个步骤: ★为什么要在存储过程中用到游标时,要把此游标封装到一个包中呢,个人理解: 由于游标是机开机关的一个 对象,它无法被存储过程调用,而包中恰恰能封装游标、函数等这些对象,所有

就把游标放在一个包中。 (1)1, 建一个程序包。如下: create or replace package userpackage as type usercursor is ref cursor; end userpackage; (2)建立存储过程,存储过程为: create or replace procedure pro_getalluser ( pro_cursor out userpackage.usercursor )is begin open pro_cursor for select * from t_user; end pro_getalluser; 可以看到,它是把游标(可以理解为一个指针) ,作为一个 out 参数来返回值 的。 ★在 java 里调用时就用下面的代码(列出主要代码): package com.mwt.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import oracle.jdbc.OracleTypes; public class TestProcedure { public static void main(String...args){ try{ Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL9I"; String user = "test2"; String password = "test2"; Connection con = DriverManager.getConnection(url, password); String sql = "{call pro_getalluser(?)}"; CallableStatement cs = con.prepareCall(sql); cs.registerOutParameter(1, OracleTypes.CURSOR); cs.execute(); //返回第一个存储过程的输出参数, 我只返回了一个游标也就只 有一个输出参数,即就是获得第一个返输出参数 getObject(1); ResultSet rs = (ResultSet)cs.getObject(1); while(rs.next()){

user,

System.out.println(rs.getString(1)+"----"+rs.getString(2)+"----"+rs.getString(3)); } rs.close(); cs.close(); con.close(); }catch(Exception e){ e.printStackTrace(); } } }

七、包 存储过程可以理解为编译好的 sql 块,包的范畴大一些,不仅可以包括存储过程,还可 以包括函数、游标、类型的定义,常、变量的定义等等。 ★在存储过程中返回一个记录集时就用到包,因为包中可以包含游标、函数等等。


相关文章:
Oracle学习总结
Oracle学习总结_学习总结_总结/汇报_应用文书。Oracle学习总结1、安装完 oracle 后创建用户及导入数据 第一,启动 sql*puls 第二,以 system/manager 登陆 第三,cre...
oracle查询学习总结
oracle查询学习总结_计算机软件及应用_IT/计算机_专业资料。Oracle 查询学习 查询 查询产生一个虚拟表,看到的是表形式显示的结果,但结果并不真正存储,每次执行查询只...
oracle经验总结 收藏
Oracle数据库学习中的六条... 1页 免费 Oracle的性能测试经验总结 2页 1财富...oracle经验总结 收藏oracle经验总结 收藏隐藏>> oracle 经验总结 收藏 1.删除表...
ORACLE学习心得
ORACLE学习心得_学习总结_总结/汇报_实用文档。Oracle RAC 学习心得 1. 概念 RAC ,全称 real application clusters ,译为 “ 实时应用集群 ”,是 Oracle 新版数...
ORACLE学习总结
oracle学习总结 14页 免费如要投诉违规内容,请到百度文库投诉中心;如要提出功能问题...收藏此文档 免费 下载此文档 大小:30.28KB 所需财富值:1 加入阅读会员!获...
oracle数据库学习总结(一)
oracle 数据库———学习总结基础: 一、ORACLE 中字段的数据类型 1、字符型 1)char 范围最大 2000 个字节定长 char(10) '张三' 后添空格 6 个把 10 个...
Oracle数据库心得体会
Oracle数据库心得体会_学习总结_总结/汇报_实用文档。oracle数据库学习心得体会学习Oracle 数据库的心得体会对于学习 Oracle 数据库,应该先要了解 Oracle 的框架。它有...
ORACLE数据库学习总结
ORACLE数据库学习总结_计算机软件及应用_IT/计算机_专业资料 暂无评价|0人阅读|0次下载|举报文档 ORACLE数据库学习总结_计算机软件及应用_IT/计算机_专业资料。数据...
Oracle数据库学习日记-实用性最强的Oracle学习总结
Oracle数据库学习日记-实用性最强的Oracle学习总结_信息与通信_工程科技_专业资料...Oracle 会自动建立名为 default 的 profile,当建立用户没有指定 profile 选项,...
有关Oracle学习总结
Oracle数据库学习总结 19页 2下载券 有​关​O​r​a​c​l​e​学​习​总​结 暂无评价|0人阅读|0次下载|举报文档 o​r​a​c...
更多相关标签: