Oracle数据库语句大全.pdf

《Oracle数据库语句大全.pdf》由会员分享，可在线阅读，更多相关《Oracle数据库语句大全.pdf（17页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、Oracle 数据库语句大全一入门部分1.创建表空间create tablespace schooltbs datafile D:oracledatasourceschooltbs.dbf size 10M autoextend on;2.删除表空间drop tablespace schooltbsincluding contents and datafiles;3.查询表空间基本信息select*|tablespace_name from DBA_TABLESPACES;4.创建用户create user lihua identified by lihua default tablespac

2、e 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_catalog_role,exp_full_database,imp_full_d

3、atabase,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）:ins

4、ert,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

5、位函数sysdate,systimestamp返回系统当前日期，时间和时区。更改时间的显示alter session set nls_date_language=american;alter session set nls_date_format=yyyy-mm-dd;Oracle中的伪列像一个表列，但没有存储在表中伪列可以查询，但不能插入、更新和修改它们的值常用的伪列：rowid和 rownum rowid:表中行的存储地址，可唯一标示数据库中的某一行，可以使用该列快速定位表中的行。rownum:查询返回结果集中的行的序号，可以使用它来限制查询返回的行数。3.数据定义语言用于操作表的命令cr

6、eate 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

7、 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.数据控制语言g

8、rant,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

9、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 st

10、u_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|

11、stu_sex as name_sex,stu_age from stu_tbl;-连接操作符|，测试结果具体如下：ID NAME_SEX STU_AGE-1000 李华男 20 1001 accp 男 20 1002 admin 男 308.SQL函数单行函数：从表中查询的每一行只返回一个值，可出现在select子句，where子句中日期函数数字函数字符函数转换函数：ToChar(),ToDate(),ToNumber()其他函数:Nvl(exp1,exp2):表达式一为null时，返回表达式二Nvl2(exp1,exp2,exp3):表达式一为null时返回表达式三，否则返回表达式二Nul

12、lif(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

13、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;表级锁类型：

14、行共享 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 10

15、00;-指定内存中预先分配的序号访问序列：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

16、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;-哪一方带有(+

17、)，哪一方就是次要的删除视图：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

18、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

19、statements exception handlers end;打开输出set serverout on;-根据输入编号获取某学员的成绩-if declare score user_tbl.score%type;begin select score into score from user_tbl where id=&id;if score90 then dbms_output.put_line(优秀);elsif score80 then dbms_output.put_line(良好);elsif score60 then dbms_output.put_line(及格);else d

20、bms_output.put_line(差);end if;end;-根据学员姓名获取某学员的成绩-if declare score user_tbl.score%type;begin select score into score from user_tbl where user_name=&name;if score90 then dbms_output.put_line(优秀);elsif score80 then dbms_output.put_line(良好);elsif score60 then dbms_output.put_line(及格);else dbms_output.p

21、ut_line(差);end if;end;-case的使用declare grade user_tbl.grade%type;begin select grade into grade from user_tbl where id=&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

22、 number(4):=1;begin loop dbms_output.put_line(loop size:|i);i:=i+1;exit when i10;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)

23、;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 tabl

24、e testExe(id number,test_name varchar2(20);userId:=&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:=&id_param;sql_str varchar2(100);name_param stu_tbl.stu_name%type;begin

25、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:=&grade;case grade when 1 then dbms_output.put_line(好的);-else dbms_output.put_line(不好);end case;exception when ca

26、se_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 sele

27、ct*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

28、:=&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

29、 id=&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 语句影

30、响实质后返回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 dbm

31、s_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语句

32、未能返回行时为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%row

33、count);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(&sex);loop fetch boys_cur into users;exit when boys_cur%notfound;dbms_output.put_line(users.user_name|users.password);dbms_output.

34、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 user

35、sex=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.p

36、ut_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

37、;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 us

38、ers_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

39、(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%notf

40、ound;dbms_output.put_line(stus.stu_Name);end loop;close my_cur;end;-动态 SQL 游标declare type ref_cur is ref cursor;my_cur ref_cur;users user_tbl%rowtype;username varchar2(20);sqlstmt varchar2(200);begin username:=&username;sqlstmt:=select*from user_tbl where user_name=:name;open my_cur for sqlstmt usin

41、g username;loop fetch my_cur into users;exit when my_cur%notfound;dbms_output.put_line(users.user_Name);end loop;close my_cur;end;六子程序子程序分为：存储过程和函数，它是命名的PL/SQL块，编译并存储在数据库中。子程序的各个部分：声明部分，可执行部分，异常处理部分。过程-执行某些操作函数-执行操作并返回值=存储过程=创建过程的语法：create or replace procedure proce_name(parameter_list)is|as local v

42、ariable declaration begin executable statements exception exception_handlers end proce_name;过程参数的三种模式：In-用于接收调用的值，默认的参数模式Out-用于向调用程序返回值In out-用于接收调用程序的值，并向调用程序返回更新的值执行过程的语法：Execute proce_name(parameter_list);或Declare Variable var_list;Begin Proce_name(var_list);End;将过程执行的权限授予其他用户：Grant execute on pr

43、oce_name to scott;Grant execute on proce_name to public;删除存储过程：Drop procedure proce_name;=函数=创建函数的语法：Create or replace function Fun_name(parameter_list)Return datatype is|as Local declarations Begin Executable statements;Return result;Exception Exce_handlers;End;函数只能接收in参数，不能接受out或 in out参数，形参不能是PL/SQL类型函数的返回类型也必须是数据库类型访问函数的方式：使用 PL/SQL块使用 SQL 语句Select fun_name(parameter_list)from dual;