oracle中DML,DCL,DLL的区别

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 || ' ' || b);

end if;

exit when cursor_user%notfound;

end loop;

close cursor_user;

end;

六、存储过程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 (userid,username,age,departid) values (seq_user.nextval,username, age,departid);

end saveuser;

(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, user, 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()){

system.out.println(rs.getstring(1)+"----"+rs.getstring(2)+"----"+rs.getstring(3));

}

rs.close();

cs.close();

con.close();

}catch(exception e){

e.printstacktrace();

}

}

}