Oracle数据库语句大全

Oracle 数据库语句大全数据库语句大全 一 入门部分一 入门部分 1 创建表空间 create tablespace schooltbs datafile D oracle datasource schooltbs dbf size 10M autoextend on 2 删除表空间 drop tablespace schooltbs including contents and datafiles 3 查询表空间基本信息 select tablespace name from DBA TABLESPACES 4 创建用户 create user lihua identified by lihua default tablespace schooltbs temporary tablespace temp 5 更改用户 alter user lihua identified by 123 default tablespace users 6 锁定用户 alter user lihua account lock unlock 7 删除用户 drop user lihua cascade 删除用户模式 8 oracle 数据库中的角色 connect dba select catalog role delete catalog role execute catalo g role exp full database imp full database resource 9 授予连接服务器的角色 grant connect to lihua 10 授予使用表空间的角色 grant resource to lihua with grant option 该用户也有授权的权限 11 授予操作表的权限 grant select insert on user tbl to scott 当前用户 grant delete update on lihua user tbl to scott 系统管理员 二 二 SQL 查询和查询和 SQL 函数函数 1 SQl 支持的命令 数据定义语言 DDL create alter drop 数据操纵语言 DML insert delete update select 数据控制语言 DCL grant revoke 事务控制语言 TCL commit savepoint rollback 2 Oracle 数据类型 字符 数值 日期 RAW LOB 字符型字符型 char 1 2000 字节的定长字符 varchar2 1 4000 字节的变长字符 long 2GB 的变长字符 注意 一个表中最多可有一列为 long 型 Long 列不能定义唯一约束或主键约束 long 列上不能创建索引 过程或存储过程不能接受 long 类型的参数 数值型数值型 number 最高精度 38 位 日期时间型日期时间型 date 精确到 ss timestamp 秒值精确到小数点后 6 位 函数函数 sysdate systimestamp 返回系统当前日期 时间和时区 更改时间的显示 alter session set nls date language american alter session set nls date format yyyy mm dd Oracle 中的伪列 像一个表列 但没有存储在表中 伪列可以查询 但不能插入 更新和修改它们的值 常用的伪列 rowid 和 rownum rowid 表中行的存储地址 可唯一标示数据库中的某一行 可以使用该列快速定位表中 的行 rownum 查询返回结果集中的行的序号 可以使用它来限制查询返回的行数 3 数据定义语言 用于操作表的命令 create table alter table truncate table drop table 修改表的命令 alter table stu table rename to stu tbl 修改表名 alter table stu tbl rename column stu sex to sex 修改列名 alter table stu tbl add stu age number 添加新列 alter table stu tbl drop sex 删除列 alter table stu tbl modify stu sex varchar2 2 更改列的数据类型 alter table stu tbl add constraint pk stu tbl primary key id 添加约 束 4 数据操纵语言 select update delete insert 利用现有的表创建表 create table stu tbl log as select id stu name stu age from stu tbl 选择无重复的行 select distinct stu name from stu tbl 插入来自其他表中的记录 insert into stu tbl log select id stu name stu age from stu tbl 5 数据控制语言 grant revoke 6 事务控制语言 commit savepoint rollback 7 SQL 操作符 算术操作符 比较操作符 20 比较操作符 select from stu tbl where stu name like a 比较操作符 like select from stu tbl where stu name like a 比较操作符 like select from stu tbl where stu age in 20 30 比较操作符 in select from stu tbl where stu age between 20 and 30 比较操作符 between select stu name from stu tbl union all select stu name from stu tbl log 集合操作符 union all 测试结果具体如下 STU NAME 李华 accp admin 李华 accp nimda 已选择 6 行 select stu name from stu tbl union select stu name from stu tbl log 集合操作符 union 测试结果具体如下 STU NAME accp admin nimda 李华 select stu name from stu tbl intersect select stu name from stu tbl log 集合操作符 intersect 测试结具体如下 STU NAME accp 李华 select stu name from stu tbl minus select stu name from stu tbl log 集合操作符 minus 测试结果如下 STU NAME Admin 从中可以看出 从中可以看出 minus 是获取第一张表独有的数据 intersect 是获取两张表中都有的数据 union 是整合两张表的数据 都有的只显示一次 union all 是纯粹的两张表数据整合 select id stu name stu sex as name sex stu age from stu tbl 连接操作符 测试结果具体如下 ID NAME SEX STU AGE 1000 李华 男 20 1001 accp 男 20 1002 admin 男 30 8 SQL 函数 单行函数 从表中查询的每一行只返回一个值 可出现在 select 子句 where 子句中 日期函数 数字函数 字符函数 转换函数 ToChar ToDate ToNumber 其他函数 Nvl exp1 exp2 表达式一为 null 时 返回表达式二 Nvl2 exp1 exp2 exp3 表达式一为 null 时返回表达式三 否则返回表达式二 Nullif exp1 exp2 两表达式相等时 返回 null 否则返回表达式一 分组函数 基于一组行来返回 Avg Min Max Sum Count Group by having 分析函数 Row number rank dense rank 示例 select u user name sum oi order num oi order price as total row number over order by sum oi order num oi order price desc as sort from order item tbl oi user tbl u order tbl o where oi order id o id and o user id u id group by u user name 三 锁和数据库对象三 锁和数据库对象 1 锁 数据库用来控制共享资源并发访问的机制 锁的类型 行级锁 表级锁 行级锁 对正在被修改的行进行锁定 行级锁也被称之为排他锁 在使用下列语句时 Oracle 会自动应用行级锁 insert update delete select for update select for update 允许用户一次锁定多条记录进行更新 使用 commit or rollback 释放锁 表级锁 lock table user tbl in mode mode 表级锁类型 行共享 row share 行排他 row exclusive 共享 share 共享行排他 share row exclusive 排他 exclusive 死锁 两个或两个以上的事务相互等待对方释放资源 从而形成死锁 2 数据库对象 oracle 数据库对象又称模式对象 数据库对象是逻辑结构的集合 最基本的数据库对象是表 数据库对象 表 序列 视图 索引 序列序列 用于生成唯一 连续序号的对象 创建语法 create sequence user id seq start with 1000 increment by 1 maxvalue 2000 minvalue 1000 nocycle cache 1000 指定内存中预先分配的序号 访问序列 select user id seq currval from dual select user id seq nextval from dual 更改删除序列 alter sequence user id seq maxvalue 10000 不能修改其 start with 值 drop sequence user id seq 在 Hibernate 中访问序列 user id seq 视图视图 以经过定制的方式显示来自一个或多个表的数据 创建视图 create or replace view user tbl view vid vname vage as select id user name age from user tbl with check option with read only 创建带有错误的视图 create force view user tbl force view as select from user table 此时 user table 可以不存在 创建外联接视图 create view user stu view as select u id u user name u password s ddress from user tbl u stu tbl s where u s id s id 哪一方带有 哪一方就是次要的 删除视图 drop user stu view 索引索引 用于提高 SQL 语句执行的性能 索引类型 唯一索引 位图索引 组合索引 基于函数的索引 反向键索引 创建标准索引 create index user id index on user tbl id tablespace schooltbs 重建索引 alter index user id index rebuild 删除索引 drop index user id index 创建唯一索引 create unique index user id index on user tbl id 创建组合索引 create index name pass index on user tbl user name password 创建反向键索引 create index user id index on user tbl id reverse 四 使用四 使用 PL SQL 可用于创建存储过程 触发器 程序包 给 SQL 语句的执行添加程序逻辑 支持 SQL 在 PL SQL 中可以使用 数据操纵命令 事务控制命令 游标控制 SQL 函数和 SQL 运算符 支持面向对象编程 OOP 可移植性 更佳的性能 PL SQL 经过编译执行 分为三个部分 声明部分 可执行部分和异常处理部分 declare declarations begin executable statements exception handlers end 打开输出 set serverout on 根据输入编号获取某学员的成绩 if declare score user tbl score type begin select score into score from user tbl where id if score 90 then dbms output put line 优秀 elsif score 80 then dbms output put line 良好 elsif score 60 then dbms output put line 及格 else dbms output put line 差 end if end 根据学员姓名获取某学员的成绩 if declare score user tbl score type begin select score into score from user tbl where user name if score 90 then dbms output put line 优秀 elsif score 80 then dbms output put line 良好 elsif score 60 then dbms output put line 及格 else dbms output put line 差 end if end case 的使用 declare grade user tbl grade type begin select grade into grade from user tbl where id case grade when A then dbms output put line 优异 when B then dbms output put line 优秀 when C then dbms output put line 良好 else dbms output put line 一般 end case end 基本循环 declare i number 4 1 begin loop dbms output put line loop size i i i 1 exit when i 10 end loop end while 循环 declare i number 4 1 begin while i 10 loop dbms output put line while loop size i i i 1 end loop end for 循环 declare i number 4 1 begin for i in 1 10 loop dbms output put line for loop Size i end loop end declare i number 2 1 j number 2 1 begin for i in reverse 1 9 loop for j in 1 i loop dbms output put j x i j i end loop dbms output put line end loop end 动态 SQL declare userId number 2 sql str varchar2 100 userName user tbl user name type begin execute immediate create table testExe id number test name varchar2 20 userId sql str select user name from user tbl where id id execute immediate sql str into userName using userId dbms output put line userName end or declare id param number sql str varchar2 100 name param stu tbl stu name type begin sql str select stu name from stu tbl where id p execute immediate sql str into name param using id param dbms output put line name param end 异常处理异常处理 declare grade number 4 begin grade case grade when 1 then dbms output put line 好的 else dbms output put line 不好 end case exception when case not found then dbms output put line 输入类型不匹配 end 系统异常 declare rowD user tbl rowtype begin select into rowD from user tbl dbms output put line rowD id rowD user name rowD password exception when too many rows then dbms output put line 不能将多行赋予一个属性 end or declare rowD user tbl rowtype begin select into rowD from user tbl where id 5 dbms output put line rowD id rowD user name rowD password exception when too many rows then dbms output put line 不能将多行赋予一个属性 when no data found then dbms output put line 没有您要查找的数据 end 自定义错误 declare invalidError exception category varchar2 20 begin category if category not in 附件 顶盘 备件 then raise invalidError else dbms output put line 您输入的类别是 category end if exception when invalidError then dbms output put line 无法识别的类别 end 引发应用程序异常 declare app exception exception grade user tbl grade type begin select grade into grade from user tbl where id if grade A then raise app exception else dbms output put line 查询的等级为 grade end if exception when app exception then raise application error 20001 未知的等级 end 五 游标管理五 游标管理 游标类型 隐式游标 显式游标 REF 游标 REF 游标用于处理运行时才能确定的动态 SQL 查询的结果 隐式游标 在 PL SQL 中使用 DML 语句时自动创建隐式游标 隐式游标自动声明 打开和关闭 其名为 SQL 隐式游标的属性 foundSQL 语句影响实质后返回 true notfoundSQL 语句没有影响实质后返回 true rowcountSQL 语句影响的行数 isopen游标是否打开 始终为 false 示例 begin update user tbl set score score 5 if SQL found then dbms output put line 数据被更改 SQL rowcount elsif sql notfound then dbms output put line 没有找到数据 end if if SQL isopen then dbms output put line Open else dbms output put line Close end if end 显式游标 在 PL SQL 的声明部分定义查询 该查询可以返回多行 声明游标 打开游标 从游标中取回数据 关闭游标 声明游标完成两个任务 给游标命名 将一个查询与游标关联 cursor cursor name is select statement 打开游标 open cursor name 取数据 fetch cursor name into record list 关闭游标 close cursor name 显式游标的属性 found 执行最后一条 fetch 语句成功返回行时为 true notfound执行最后一条 fetch 语句未能返回行时为 true rowcount返回到目前为止游标提取的行数 isopen游标是否打开 示例 declare users user tbl rowtype cursor boys cur is select from user tbl where sex h begin open boys cur loop fetch boys cur into users exit when boys cur notfound dbms output put line users user name users password dbms output put line boys cur rowcount end loop close boys cur end 带参的显式游标 declare users user tbl rowtype cursor boys cur sexParam varchar2 is select from user tbl where sex sexParam begin open boys cur loop fetch boys cur into users exit when boys cur notfound dbms output put line users user name users password dbms output put line boys cur rowcount end loop close boys cur end 使用显式游标更新行 declare cursor user update cur is select sex from user tbl for update usersex user tbl sex type begin open user update cur loop fetch user update cur into usersex exit when user update cur notfound dbms output put line usersex if usersex M then update user tbl set score score 5 where current of user update cur else update user tbl set score score 5 where current of user update cur end if end loop close user update cur commit end 循环游标 declare cursor user cur is select from user tbl begin for username in user cur loop dbms output put line username user name username sex end loop end REF 游标 REF 游标和游标变量用于处理运行时动态执行的 SQL 查询 创建游标变量的步骤 声明 REF 游标类型 声明 REF 游标类型的变量 声明类型的语法 Type ref cursor name is ref cursor return return type 打开游标变量的语法 Open cursor name for select statement 声明强类型的游标 declare type ref cur is ref cursor return user tbl rowtype users cur ref cur 声明弱类型的游标 declare type ref cur is ref cursor users cur ref cur 示例 强类型 declare type ref cur is ref cursor return user tbl rowtype users cur ref cur users user tbl rowtype begin open users cur for select from user tbl where user name ny2t92 loop fetch users cur into users exit when users cur notfound dbms output put line users user Name end loop close users cur end 弱类型 declare type ref cur is ref cursor my cur ref cur users user tbl rowtype stus stu tbl rowtype begin open my cur for select from user tbl loop fetch my cur into users exit when my cur notfound dbms output put line users user Name end loop close my cur open my cur for select from user tbl where user name ny2t92 loop fetch my cur into users exit when my cur notfound dbms output put line users user Name end loop close my cur open my cur for select from stu tbl loop fetch my cur into stus exit when my cur notfound dbms output put line stus stu Name end loop