oracle命令大全、
2007-08-25 09:48:31| 分类： 默认分类 | 标签： |字号大中小订阅
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&制作:张传江(RenBinbo)&&&&&&&&& &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 内容包括三大项: 1.oracle基本操作语句
2.SQLServer基本操作语句
3.各种数据库连接方法 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& **************************************************oracle基本操作语句******************************************************** 打开服务器 net start oracleservicebinbo 打开监听器 lsnrctl start 关闭服务器 net stop oracleservicebinbo 关闭监听器 lsnrctl stop =============================================================== 清屏 clear screen **************************************************************** 数据字典 ===========desc user_views(关键词) **************************************************************** =============================================================== 查看当前用户的角色 SQL>select * from user_role_privs; =============================================================== 查看当前用户的系统权限和表级权限 SQL>select * from user_sys_privs; SQL>select * from user_tab_privs; =============================================================== 查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users; =============================================================== 换用户 conn as sysdba sys tsinghua sqlplus "sys/tsinghua as sysdba" conn sys/zl as sysdba =============================================================== 修改表结构 alter table test modify(name not null); alter table test add(name varchar2(20)); alter table test drop column sex; alter table test set unused column sex; alter table test drop unused columns; =============================================================== 更改用户密码 sql>alter user 管理员 identified by 密码； =============================================================== 创建表空间的数据文件 sql>create tablespace test datafile 'd:\oracle\binbo.dbf' size 10m; =============================================================== 创建用户 sql>create user 用户名 identified by 用户名; =============================================================== bfile类型实例 创建目录 create directory tnpdir as 'c:\'; 删除目录 drop directory tnpdir 授权 crant read on directory tn pdir to scott; 建表 create table bfiletest(id number(3), fname bfile); 添加数据 insert into bfiletest values(1,bfilename('TMPDIR','tmptest.java')); =============================================================== 查看用户 sql>show user =============================================================== 检查语句是否有错 show error =============================================================== 锁定用户 sql>alter user 用户名 account lock =============================================================== 解除用户 sql>alter user 用户名 account unlock =============================================================== 删除用户 sql>drop user zl; =============================================================== 给用户创建表权限 sql>grant create table to 用户名; =============================================================== 授管理员权限 sql>grant dba to 用户名; =============================================================== 给用户登录权限 sql>grant connect to 用户名 =============================================================== 给用户无限表空间权限 sql>grant unlinmited tablespace to 用户名; =============================================================== 收回权限 sql>revoke dba from 用户名; =============================================================== 查看用户下所有的表 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;
===============================================================
再添加一个表空间的数据文件 sql>alter tablespace test add datafile 'd:\oracle\test1.dbf' size 10m; =============================================================== 建表 SQL>create table studen(stuno int,stuname varchar(8) not null,stubirth date default to_date('1987-5-9','YYYY-MM-DD')); 向表结构中加入一列 SQL>alter table studen add(stuphoto varchar(9)); 从表结构中删除一列 SQL>alter table studen drop column stuphoto; 修改表一列的长度 SQL>alter table studen modify(stuno number(4)); 隐藏将要删除的一列 SQL>alter table studen set unused column stuphoto; 删除隐藏的列 SQL>alter table studen drop unused columns; 向表中加入约束 SQL>alter table studen add constraint pk primary key(stuno); 删除约束 SQL>alter table studen drop constraint pk; =============================================================== 创建表
sql>create table 用户名(name varchar2(20),password varchar(20)) tablespace 空间名; =============================================================== 添加字段 sql>alter table test add(column_x char(10) not null); =============================================================== 更改字段 sql>alter table emp modify(column_x char (20)); =============================================================== 删除字段 如待删除域属于某个索引，则不允许删除操作，必须将此域先设置为NULL。 sql>alter table emp modify(column_x null); sql>update emp set column_x=null; sql>commit; sql>alter table emp drop(column_x); =============================================================== 选择表空间 sql>alter user 用户名 default tablespace test; =============================================================== 管理员删除别的用户中的表 sql>drop table 用户名.表名; =============================================================== 退出 sql>exit; =============================================================== 默认进入 sql>sqlplus "/ as sysdba" =============================================================== 查看数据库 sql>show parameter block; =============================================================== 写大量语句用记事本，新建方式。 输入"ed"回车 保存后 输入"/"运行; =============================================================== 查询用户有多少表 sql>select * from tab; =============================================================== SQLServer取时间 sql>select getdate oracle 取时间 sql>sysdate; =============================================================== 操作表结构数据库定义语言命令 (不记录在日志文件中) create table建表 sql>create table test(name varchar2(20),age date,sex char(2)); sql>insert into test(name,age,sex) values('aa',sysdate,'男'); sql>insert into test(name,age,sex) values('bb',to_date('1888-8-8',"yyyy-aa-dd hh24:mi:ss"),'男'); sql>select * from test; =============================================================== 查询男和女总数 sql>select sex,count(sex) from test group by sex; --------------------------------------------------------------- test表中数据输入test1表中 SQLSserver---select * into test1 from test; oracle---create table test1 as select * from test; --------------------------------------------------------------- 更改会话时间 sql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; --------------------------------------------------------------- sql>show parameter block 表和视图 sql>show parameter date 查数据结构 --------------------------------------------------------------- SQLServer中 --删除表中相同数据 sql>create table test1 as select distinct * from test; --删除表数据 sql>truncate table test; --把test中数据输入到test1中 sql>insert into test(select * from test1); --------------------------------------------------------------- rowid(表中存储地址相当表id)和rownum(表序号)称伪列(用法) sql>select name,age,sex,rowid,rownum from test1; 查出前三行 sql>select * from test where rownum<=3; 查出后三行 sql>select * from (select name n,age a,sex s,rownum r from test) where r>(select count(*) from test)-3; 删除后三行 SQL> delete from test where name not in(select name from test where rownum<=(select count(*) from test)-3); 删除相同行 sql>delete from test where rowid not in(select max(rowid) from test group by name,age,sex); 删除所有表 sql>select 'drop table' ||tname|| ':' from tab; sql>spool c:\test.sql; sql>select 'drop table' ||tname|| ':' from tab; sql>spool off sql>@c:\test.sql; --------------------------------------------------------------- alter table修改表 truncate table节段表(只删除数据) drop table删除表 =============================================================== 查看表结构 desc 表名; =============================================================== 查出成绩的前三名 sql>select * from (select * from stu order by score desc) where rownum<=3; =============================================================== 更改字符集 SQL>startup mount SQL>alter system enable restricted session; SQL>alter system set job_queue_processes=0; SQL>alter database open; SQL>alter database character set ZHS16GBK; SQL>shutdown SQL>startup =============================================================== 将一张表或几张表中的域重新组合后插入新表。 假定原先的两张表为emp,work，现选择部分数据域合并为emp_work 建立emp_work SQL>insert into emp_new select a.no, sysdate, a.name, b.service_duration from emp a, work b where a.no=b.no; SQL>commit; 这样的方式仍然要使用回滚段，为加快数据迁移速度，可将insert替换成insert /*+APPEND*/（大小写不论），指示oracle以直通方式直接写数据文件，绕过回滚空间。 SQL>insert /*+APPEND*/ into emp_new select a.no, sysdate, a.name, b.service_duration from emp a, work b where a.no=b.no; SQL>commit; =============================================================== DDL数据定义语言(create,alter,drop) DML数据操纵语言(insert,select,delete,update) TCL事务控制语言(commit,savepoint,rollback) DCL数据控制语言(GRANT REVOKE) ===============================================================
一个表中的某一列输到另一个表中 insert into stu1(name)(select name from stu); =============================================================== 事务 rollback; insert into stu1(name)(select name from stu); commit;提交 =============================================================== COMMIT - 提交并结束事务处理 ROLLBACK - 撤销事务中已完成的工作 SAVEPOINT – 标记事务中可以回滚的点 SQL> update order_master set del_date ='30-8月-05' WHERE orderno <= 'o002'; SQL> savepoint mark1; SQL> delete FROM order_master WHERE orderno = 'o002'; SQL> savepoint mark2; SQL> rollback TO SAVEPOINT mark1; SQL> COMMIT; =============================================================== 换名 set sqlprompt "scott>"; =============================================================== GRANT 授予权限 SQL> GRANT SELECT ON vendor_master TO accounts WITH GRANT OPTION; REVOKE 撤销已授予的权限 SQL> REVOKE SELECT, UPDATE ON order_master FROM MARTIN; =============================================================== 比较操作符
SQL> SELECT vencode,venname,tel_no FROM vendor_master WHERE venname LIKE 'j___s'; SQL> SELECT orderno FROM order_master WHERE del_date IN (‘06-1月-05’,‘05-2月-05'); SQL> SELECT itemdesc, re_level FROM itemfile WHERE qty_hand < max_level/2; =============================================================== 逻辑操作符 SQL> SELECT * FROM order_master WHERE odate > ‘10-5月-05' AND del_date < ‘26-5月-05’; =============================================================== 集合操作符将两个查询的结果组合成一个结果 SQL> SELECT orderno FROM order_master MINUS SELECT orderno FROM order_detail; ----------------------------------------------------------------- select * from scott.stu union (all)重复的去掉[intersect把相同的取出来][minus显示不相同的数] select * from stu ----------------------------------------------------------------- 显示相同的数据 select name from stu intersect select name from stu1; =============================================================== 连接操作符 连接操作符用于将多个字符串或数据值合并成一个字符串 SQL> SELECT (venname|| ' 的地址是 ' ||venadd1||' '||venadd2 ||' '||venadd3) address FROM vendor_master WHERE vencode='V001'; =============================================================== 操作符的优先级 SQL 操作符的优先级从高到低的顺序是： 算术操作符 --------最高优先级 连接操作符 比较操作符 NOT 逻辑操作符 AND 逻辑操作符 OR 逻辑操作符 --------最低优先级
=============================================================== 用来转换空值的函数 NVL NVL2 NULLIF SELECT itemdesc, NVL(re_level,0) FROM itemfile; SELECT itemdesc, NVL2(re_level,re_level,max_level) FROM itemfile; SELECT itemdesc, NULLIF(re_level,max_level) FROM itemfile; =============================================================== GROUP BY和HAVING子句
GROUP BY子句 用于将信息划分为更小的组 每一组行返回针对该组的单个结果
HAVING子句 用于指定 GROUP BY 子句检索行的条件
SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category; SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category HAVING p_category NOT IN ('accessories'); =============================================================== ROW_NUMBER (row_number)返回连续的排位，不论值是否相等 RANK(rank) 具有相等值的行排位相同，序数随后跳跃 DENSE_RANK(dense_rank) 具有相等值的行排位相同，序号是连续的 SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANK FROM emp e, dept d WHERE e.deptno = d.deptno; =============================================================== 日期函数 ADD_MONTHS(当前只加月) alter session set nls_date_format='yyyymmdd hh24miss'; select add_months(sysdate,2) from dual; ---------------------------------------------------------------- MONTHS_BETWEEN(前面时间减后面时间=得之间月差) select months_between(sysdate,to_date('2007-6-10','yyyy-mm-dd')) from dual; ---------------------------------------------------------------- LAST_DAY(求得当前月的最后一天) select last_day(sysdate) from dual; ---------------------------------------------------------------- ROUND(round年-月-日-->四舍五入) select round(2.3) from dual; select round(to_date('2007-6-10','yyyy-mm-dd'),'year') from dual; select round(to_date('2007-6-10','yyyy-mm-dd'),'month') from dual; select round(to_date('2007-6-10','yyyy-mm-dd'),'day') from dual; ---------------------------------------------------------------- NEXT_DAY(下一星期的星期二） select next_day(to_date('2007-6-10','yyyy-mm-dd'),'星期二') from dual; ---------------------------------------------------------------- TRUNC(trunc) ---------------------------------------------------------------- EXTRACT(extract) select extract(year from date '1998-03-07') from dual; select extract(month from to_date ('1998-03-07','yyyy-mm-dd')) from dual;
---------------------------------------------------------------- 2008年2月有多少天 inbo---->select extract(day from last_day(to_date ('2008-02-07','yyyy-mm-dd'))) from dual; ---------------------------------------------------------------- 2003-4-3与1956-3-1之间有多少天 inbo---->select round(months_between(to_date('2003-4-3','yyyy-mm-dd'),to_date('1956-3-1','yyyy-mm-dd'))/12) from dual; =============================================================== 把两边的9去掉 select trim('9' from '9999ddddddd99999') from dual; 去空格 select trim(' ' from ' 9999ddddddd99999') from dual; =============================================================== 函数 输入 输出 Initcap(char) Select initcap(‘hello’) from dual; Hello Lower(char) Select lower(‘FUN’) from dual; fun
Upper(char) Select upper(‘sun’) from dual; SUN
Ltrim(char,set) Select ltrim( ‘xyzadams’,’xyz’) from dual; adams Rtrim(char,set) Select rtrim(‘xyzadams’,’ams’) from dual; xyzad
Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual; back
Replace(char,searchstring,[rep string]) Select replace(‘jack and jue’ ,’j’,’bl’) from dual; black and blue Instr (char, m, n) Select instr (‘worldwide’,’d’) from dual; 5 Substr (char, m, n) Select substr(‘abcdefg’,3,2) from dual; cd Concat (expr1, expr2) Select concat (‘Hello’,’ world’) from dual; Hello world
=============================================================== 数字函数接受数字输入并返回数值结果
函数 输入 输出 Abs(n) Select abs(-15) from dual; 15
Ceil(n) Select ceil(44.778) from dual; 45
Cos(n) Select cos(180) from dual; -.5984601
Cosh(n) Select cosh(0) from dual; 1
Floor(n) Select floor(100.2) from dual; 100
Power(m,n) Select power(4,2) from dual; 16
Mod(m,n) Select mod(10,3) from dual; 1
Round(m,n) Select round(100.256,2) from dual; 100.26
Trunc(m,n) Select trunc(100.256,2) from dual; 100.25
Sqrt(n) Select sqrt(4) from dual; 2
Sign(n) Select sign(-30) from dual; -1
=============================================================== 字符函数
查看有多少个字符 SQL> SELECT LENGTH('frances') FROM dual; ----------------------------------------------------------------- SQL> SELECT vencode, DECODE(venname,'frances','Francis') name FROM vendor_master WHERE vencode='v001'; ----------------------------------------------------------------- 查找人是否存在 加字段decode主明是否有人 select name,decode(name,'rbb','有人') from stu;
=================================================================== 排续 select dense_rank() over(partition by sex order by score) from test; select row_number() over(order by score),name,sex,score from test; select rank() over(order by score) from test; select dense_rank() over(order by score) from test; ==========================================================================
创建同义词 SQL> create public synonym test for rbb.test; SQL> create synonym test for mytest; 同一类的才可以替换,同义词替换同义词 替换 SQL> create or replace synonym emp_sysn for scott.emp;
********************************************************************************************** 创建序列 SQL>create sequence xule increment by 1 start with 1 maxvalue 999; increment by 增长值 start with 起始值 maxvalue 最大值 minvalue 最小值 nocycle 不循环 chare 10缓存 xule.nextval ===========下一个序列的值 xule.currval ===========可以查询序列当前的值 更改序列 start with 不能改 alter sequence xule maxvalue 100 [sycle nocycle]； ********************************************************************************************** 序列用法 SQL>create table xl(name varchar2(4)); SQL>insert into test values(xule.nextval); SQL>select xl.currval from dual; ********************************************************************************************** 删除序列 drop sequence x; desc user_sequences ********************************************************************************************** 创建视图 视图中可以使用函数和表达式 create or replace view
********************************************************************************************** 创建视图 SQL> create or replace view 视图名 as select * from rbb union all select * from rbbb union all select * from test; SQL> create or replace view 视图名 as 2 select empno as 编号,ename as 姓名 from scott.emp 3 where deptno=10; ========================================================================== 如果在当前用户下没有这个视图就创建此视图 如果有此视图就覆盖此视图 create or replace view view_name as select empno,ename from emp where deptno=10; ********************************************************************************************** 在创建视图前要为当前用户授权 grant resource to scott; create or replace view v_sal as select ename,sal from emp order by sal desc; ********************************************************************************************** 使用视图
select * from v_sal; ********************************************************************************************** 删除一个视图 drop view view_name; ********************************************************************************************** 重新编译已有的视图 alter view view_name compile; ********************************************************************************************** 数据字典 ===========desc user_views **********************************************************************************************
常用的转换函数有 TO_CHAR SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual;
TO_DATE SELECT TO_DATE('2005-12-06', 'yyyy-mm-dd') FROM dual;
TO_NUMBER SELECT TO_NUMBER('100') FROM dual; ********************************************************************************************** 集合操作符 union all 连接两个表或者多个表为一个视图 MINUS 操作符返回从第一个查询结果中排除第二个查 询中出现的行。 INTERSECT 操作符只返回两个查询的公共行。 **********************************************************************************************
锁定的优点
1.一致性 - 一次只允许一个用户修改数据
2.完整性 - 为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户
3.并行性 －允许多个用户访问同一数据
行级锁和表级锁
行级锁:是一种排他锁,防止其他事务修改此行.
解锁：提交事务(commit),(rollback) --------------------------------------------------------------- 更新表数据:update test set score=80 where name='xiaoli'; -------------------------------------------------------------- 自动提交 set autocommit on set sutocommit off ------------------------------------------------------------ 锁定某行更新语句 select * from scott.test where name='xiaoli' for update; SELECT * FROM order_master WHERE vencode='V002' FOR UPDATE OF odate,del_date; select * from scott.test where name='xiaoli' for update of score;
select * from scott.test atest,test b where a.name=b.name and b.name='bbb' for update of b.score;
-------------------------------------------------------------------- 等待update select * from scott.test where name='xiaoli' for update wait 2; select * from scott.test where name='xiaoli' for update nowait;
------------------------------------------------------------------- 表级锁：锁定整个表 表级锁语法:lock table 表名 in mode mode; ------------------------------------------------------------------------- 行共享row share--行排他row exclusive--共享share-共享行排他share row exclusive-----排他exclusive --------------------------------------------------------------------------------- 行共享(row share):lock table scott.test in (row share) mode; [其他用户.行共享---其他用户.行排他---其他用户.共享----其他用户.共享行排他----其他用户.不可以(排他)]
-------------------------------------------------------------------------------- 行排他(row exclusive):lock table scott.test in (row exclusive) mode;
[其他用户.行共享----其他用户.行排他----其他用户.不可以(共享)---其他用户.不可以(共享行排他)--其他用户.不可以(排他)]
--------------------------------------------------------------------------------- 共享(share):lock table scott.test in (share) mode;
[其他用户.行共享---其他用户.不可以(行排他)---其他用户.共享----其他用户.不可以(共享行排他)---其他用户.不可以(排他)]
----------------------------------------------------------------------------------- 共享行排他(share row exclusive):lock table scott.test in (share row exclusive) mode;
[其他用户.行共享,其他用户.不可以(行排他),其他用户.不可以(共享),其他用户.不可以(共享行排他),其他用户.不可以(排他)]
-------------------------------------------------------------------------------------- 排他(exclusive):lock table scott.test in (exclusive) mode;
[其他用户.不可以(行共享),其他用户.不可以(行排他),其他用户.不可以(共享),其他用户.不可以(共享行排他,)其他用户.不可以(排他)]
---------------------------------------------------------------------------------- 死锁
当两个事务相互等待对方释放资源时，就会形成死锁
Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁 ----------------------------------------------------------------------------------
表分区
---范围分区 create table test(name varchar2(20),sex char(2),score number(3)) partition by range(score) ( partition p1 values less than (50) tablespace users, partition p2 values less than (80), partitiom p3 values less than (maxvalue) ) select * from test partition(p1) union select * from test partitiom(p3); --- 删除分区 alter table test drop partition p3; 添加分区 alter table test add partition p3 values less than (maxvalue); 拆分分区 alter table test split partition p2 at(60) into (partition p21,partition p22); 合并分区 alter table test merge partitions p21,p22 into partition p2; 截断分区(删除数据) alter table test truncate partition p3;
现有表分区 create table str as select * from student; drop table student; create table student( studentid integer not null, studentname varchar2(20), score integer ) partition by range(score)( partition p1 values less than(60), partition p2 values less than(75), partition p3 values less than(85), partition p4 values less than(maxvalue) ) insert into student(select * from stu);
select * from test scott.emp@tsinghua
**********************************************************************************************
表分区 Oracle允许用户对表进一步的规化，即对表进一步拆分，将表分成若干个逻辑部分，每个部分称其为表分区 优点：增强可用性，单个分区出现故障，不影响其他分区 均衡的I/O，不同的分区可以映射到不同的磁盘 改善性能 ********************************************************************************************** ①范围分区法 create table st( studentid integer not null, studentname varchar2(20), score integer ) partition by range(score)( partition p1 values less than(60), partition p2 values less than(75), partition p3 values less than(85), partition p4 values less than(maxvalue) ) ========================select * from stu partition(p1)============ ②散列分区 create table st(deptno int,deptname varchar(14)) partition by hash(deptno)( partition p1,partition p2 ) 组合分区 alter table test coalesce partition; ********************************************************************************************** ③复合分区 范围分区和列表分区 create table salgrade( grade number(2),losal number(2),hisal number(2) ) partition by range(grade) subpartition by list(losal) ( partition p1 values less than(10)
( subpartition p1a values('湖北'), subpartition p1b values(default) ), partition p2 values less than(20) ( subpartition p1a values('河南'), subpartition p1b values(default) ), partition p3 values less than(30) ( subpartition p1a values('上海'), subpartition p1b values(default) ) )
范围分区和散列分区 create table salgrade( grade number(2),losal number(2),hisal number(2) ) partition by range(grade) subpartition by hash(losal) [subpartitions 5] ( partition p1 values less than(10)(subpartition p1a,subpartition p1b), partition p2 values less than(20)(subpartition p2a,subpartition p2b), partition p3 values less than(30)(subpartition p3a,subpartition p3b) ) -------------------------------------------- create table salg( grade number(2),losal number(2),hisal number(2) ) partition by range(grade) subpartition by hash(losal) subpartitions 3 ( partition p1 values less than(10), partition p2 values less than(20), partition p3 values less than(30) ) ********************************************************************************************** ④列表分区 create table test stu(id int,name varchar(20),add varchar(8)) partition by list(add) ( partition p1 values('中国'), partition p2 values('英国'), partition p3 values(default) ) ********************************************************************************************** 移动分区
alter table test move partition p5 tablespace users;
********************************************************************************************
修改存档
SQL> shutdown immediate 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。
SQL> startup mount ORACLE 例程已经启动。
Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes
数据库装载完毕。
SQL> alter database archivelog;
数据库已更改。 alter database open;
SQL> archive log list; 数据库日志模式 存档模式 自动存档 禁用 存档终点 d:\oracle\ora92\RDBMS 最早的概要日志序列 1 下一个存档日志序列 2 当前日志序列 2
SQL> alter system set log_archive_dest=true scope=spfile;
系统已更改。
SQL> alter database open;
数据库已更改。
SQL> spool off ********************************************************************************************
PL/SSQL(过程化语言) 声明部分 执行语句部分 异常处理部分
identifier constant datatype not null [:=|default expr];
declare my number(5); begin select quantity into my from products where product='wawa' for update of quantity; if my>0 then update products set quantity=quantity+1 where product='wawa'; insert into purchase_record values('wawawa',sysdate); end if; commit; Exception where others then dbms_output.put_line('chucuo'||SQLERRM); END;
declare icode varchar2(6) p_catg varchar2(20); c_catg constant datatype:=0.10
数字类型 number decrmdl int/integer real(实数) binary_integer(带符号的整数) pls_integer(同上) 字符类型 character char 3276 Raw(2000) long/long Raw(32760) Rowid/rowid() varchar2 (string(nchar/nvarchar)/varchar) 日期时间 date timeStamp(固定日期dd-mm-yy 秒6位) 子 timestamp with time zone ti timestamp(9) 布尔 boolean true false null 打印出时间 declare test_tz timestamp with time zone; begin test_tz:=to_timestamp_tz('2006-6-22 09:07:11','yyyy-mm-dd hh24:mi:ss'); dbms_output.put_line(test_tz); end;
lob类型 BFILE BLOB CLOB NCLOB 属性类型 %type %rowtype =============================================================== bfile类型实例 创建目录 create directory tnpdir as 'c:\'; 删除目录 drop directory tnpdir 授权 crant read on directory tnpdir to scott; 建表 create table bfiletest(id number(3), fname bfile); 添加数据 insert into bfiletest values(1,bfilename('TMPDIR','tmptest.java'));
=============================================================== 向数据库中添加图片 create directory images as 'c:\images'; crant read on directory images to scott; create table my_diagrams( chapter_descr varchar2(40); diagram_no integer, diagram blob );
declare l_bfile bfile; l_blob blob; begin insert into my_diagrams(diagram) values(emptv_blob()) return diagram into l_blob; l_bfile:=bfilename('images','\nvimage.jpg'); dbms_lob.open(l_bfile,dbms_lob.file_readonly); dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob,getlength(l_bfile)); dbms_lob.close(l_bfile); commit; end;
=============================================================== %type实例 查询 declare dtr dept.dname%type; begin select dname into str from dept where deptno=30; dbms_output.put_line(str); end; set serverout on
=============================================================== %rowtype实例
declare row dept%rowtype; begin select * into row from dept where deptno=30; dbms_output.put.line(row.dname||' '||row.deptno||' '||row.loc); //异常 exception when no_data_found then dbms_output.put_lin('没有数据'); when too_many_rows(others) then dbms_output.put_lin('太多拉'); end;
=============================================================== 格式
if 条件 then
elsif 条件 then
else
end if =============================================================== 格式
begin case'&grade' when 'a' then dbms_output.put_line('优异'); when 'b' then dbms_output.put_line('良好'); else dbms_output.put_line('其它') end case; end; =============================================================== 外界变量 var vnm varchar2(20); begin :v:='aaaaa'; end; 打印 print v =============================================================== loop实例
begin loop exit when 3>4;
end loop; end; =============================================================== while实例 begin while (条件)condition loop 语句体; end loop; end; =============================================================== 循环实例 正 begin for c in 1..10 loop dbms_output.put_line(c); end loop end; 倒 begin for c in reverse(倒) 1..10 loop dbms_output.put_line(c); end loop end; ===============================================================
declare num number(3):=1; begin while num<10 loop dbms_output.put_line(num); num:=num+1; end loop; end;
declare num number(3):=1; begin loop dbms_output.put_line(num); exit when num>10;//退出 num:=num+1; end loop; end; =============================================================== goto实例 DECLARE qtyhand itemfile.qty_hand%type; relevel itemfile.re_level%type; BEGIN SELECT qty_hand,re_level INTO qtyhand,relevel FROM itemfile WHERE itemcode = 'i201'; IF qtyhand < relevel THEN GOTO updation; ELSE GOTO quit; END IF; <<updation>> UPDATE itemfile SET qty_hand = qty_hand + re_level WHERE itemcode = 'i201'; <<quit>> NULL; END; =============================================================== 动态SQL 查询 declare cl varchar2(20); va varchar2(20); tb varchar2(20); nm number(13);
begin tb:='&table'; cl:='&aadd'; nm:=# EXECUTE IMMEDIATE 'select '||cl||' from '||tb||' where '||cl||'=:1' into va using nm; dbms_output.put_line(va); end;
=============================================================== 动态SQL
declare
sql_stmt varchar2(200); emp_id number(4):=7566; emp_rec emp% rowtype;
begin Execute immedlate 'create table bonus1(id number,amt number)';
sql_stmt:='select * from emp where empno=:id'; Execute immedlate sql_stmt into emp_rec using emp_id;
end; =============================================================== declare aaa varchar2(20); num number(10); bbb varchar2(20); begin aaa='&aaa'; num=&kkk; execute immedlate 'select '||aaa||' from test where age=:a'into bbb using num;(标准SQL语句) dbms_output.put_line(bbb); end; into 变量(给值) :a(外界参数) using bb(邦定常量)
=============================================================== 自己定义异常 declare
invar exception; cate varchar2(10); begin cate:='&cate'; if cate not in('aa','ff','dd') then raise invar; else dbms_output.put_line('你输入的类别是:'||cate); end if; exception when invar then dbms_output.put_line('无法认识这个类别!'); raise_application_error(-20200,'自己写'); end; 让数据库真正出错 raise_application_error(-20200,'自己写');
例子2 declare rate itemfile.itemrate%type; ratee exception; begin select nvl(itemrate,0) into rate from itemfile where itemcode='i207'; if rate=0 then raise ratee; else dbms_output.put_line('项费率是:'||rate); end if; exception when ratee then RAISE_APPLICATION_ERROR(-20001, '未指定项费率'); end; =============================================================== create procedure存储过程 =============================================================== 创建标准索引 SQL> CREATE INDEX item_index ON itemfile (itemcode) TABLESPACE index_tbs; 重建索引 SQL> ALTER INDEX item_index REBUILD;
删除索引 SQL> DROP INDEX item_index; 唯一索引确保在定义索引的列中没有重复值 Oracle 自动在表的主键列上创建唯一索引 使用CREATE UNIQUE INDEX语句创建唯一索引 SQL> CREATE UNIQUE INDEX item_index ON itemfile (itemcode); 组合索引是在表的多个列上创建的索引 索引中列的顺序是任意的 如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度
SQL> CREATE INDEX comp_index ON itemfile(p_category, itemrate); 反向键索引反转索引列键值的每个字节 通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上 创建索引时使用REVERSE关键字 SQL> CREATE INDEX rev_index ON itemfile (itemcode) REVERSE; SQL> ALTER INDEX rev_index REBUID NOREVERSE; 位图索引适合创建在低基数列上 位图索引不直接存储ROWID，而是存储字节位到ROWID的映射 减少响应时间 节省空间占用 SQL> CREATE BITMAP INDEX bit_index ON order_master (orderno); 基于一个或多个列上的函数或表达式创建的索引 表达式中不能出现聚合函数 不能在LOB类型的列上创建 创建时必须具有 QUERY REWRITE 权限 SQL> CREATE INDEX lowercase_idx ON toys (LOWER(toyname)); SQL> SELECT toyid FROM toys WHERE LOWER(toyname)='doll'; 与索引有关的数据字典视图有： USER_INDEXES － 用户创建的索引的信息 USER_IND_PARTITIONS － 用户创建的分区索引的信息 USER_IND_COLUMNS － 与索引相关的表列的信息 SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS ORDER BY INDEX_NAME, COLUMN_POSITION; 可以将索引存储在不同的分区中 与分区有关的索引有三种类型： 局部分区索引 － 在分区. 表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致 全局分区索引 － 在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关 全局非分区索引 － 在分区表上创建的全局普通索引，索引没有被分区
SQL> CREATE TABLE ind_org_tab ( vencode NUMBER(4) PRIMARY KEY, venname VARCHAR2(20) ) ORGANIZATION INDEX; 与索引有关的数据字典视图有： USER_INDEXES － 用户创建的索引的信息 USER_IND_PARTITIONS － 用户创建的分区索引的信息 USER_IND_COLUMNS － 与索引相关的表列的信息
SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS ORDER BY INDEX_NAME, COLUMN_POSITION;
----游标简介
逐行处理查询结果,经编程的方式访问数据
---游标类型: 隐式游标:在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标。 显式游标:显式游标用于处理返回多行的查询。 REF 游标:REF 游标用于处理运行时才能确定的动态 SQL 查询的结果
------隐式游标的属性有： %FOUND – SQL 语句影响了一行或多行时为 TRUE %NOTFOUND – SQL 语句没有影响任何行时为TRUE %ROWCOUNT – SQL 语句影响的行数 %ISOPEN - 游标是否打开，始终为FALSE
删除游标
delete from table_name where cursor of cursor_name; =============================================================== ------隐式游标示例 ------too_many_rows的用法!
1 declare 2 empid varchar2(20); 3 begin 4 select name into empid from test; 5 exception 6 when too_many_rows then 7 dbms_output.put_line('该查询多于两行!'); 8* end; SQL> / 该查询多于两行!
PL/SQL 过程已成功完成。
===============================================================
------no_data_found的用法! SQL> set serverout on SQL> ed 已写入文件 afiedt.buf
1 declare 2 empid varchar2(20); 3 desig varchar2(20); 4 begin 5 empid:='&emp'; 6 select name into desig from test where name=empid; 7 dbms_output.put_line('你查询的名字是:'||desig); 8 exception 9 when no_data_found then 10 dbms_output.put_line('没有时间！'); 11* end; SQL> / 输入 emp 的值: xiaoli 原值 5: empid:='&emp'; 新值 5: empid:='xiaoli'; 你查询的名字是:xiaoli
PL/SQL 过程已成功完成。
SQL> / 输入 emp 的值: ss 原值 5: empid:='&emp'; 新值 5: empid:='ss'; 没有时间！
PL/SQL 过程已成功完成。
=============================================================== SQL> set serveroutput on SQL> begin 2 update test set name='renbinbo' where name='binbo'; 3 if sql%found then 4 dbms_output.put_line('表已经更新!'); 5 end if; 6 end; 7 / test_t表中name也已经更新! 表已经更新!
=============================================================== SQL> declare aa varchar2(20); bb varchar2(20); begin bb:='&bb'; select score into aa from test where name=bb; if sql%found then dbms_output.put_line(bb||'的分数为:'||aa); end if; end; SQL> / 输入 bb 的值: renbinbo 原值 5: bb:='&bb'; 新值 5: bb:='renbinbo'; renbinbo的分数为:100
PL/SQL 过程已成功完成。 =============================================================== SQL> ed 已写入文件 afiedt.buf
1 declare 2 my_toy rbb.test.name%type; 3 cursor toy_cur is 4 select name from test where name='xiaoli'; 5 begin 6 open toy_cur; 7 loop 8 fetch toy_cur into my_toy; 9 exit when toy_cur%notfound; 10 dbms_output.put_line('你查询人的姓名:'||my_toy); 11 end loop; 12 close toy_cur; 13* end; SQL> / 你查询人的姓名:xiaoli
PL/SQL 过程已成功完成。
SQL> ed 已写入文件 afiedt.buf
1 declare 2 name_n rbb.test.name%type; 3 sex_s rbb.test.name%type; 4 sex_t rbb.test.name%type; 5 cursor test_t is 6 select name,sex,score from test; 7 begin 8 open test_t; 9 dbms_output.put_line('你所查资料列表:'); 10 loop 11 fetch test_t into name_n,sex_s,sex_t; 12 exit when test_t%notfound; 13 dbms_output.put_line(name_n||' '||sex_s||' '||sex_t); 14 end loop; 15 close test_t; 16* end; 17 / 你所查资料列表: xiaoli 女 90 renbinbo 男 100 xiaoming 男 89 xiaowang 男 91 xiaohua 女 98 yunfeng 男 88 wangming 男 78 wuming 男 98 xiaobin 男 68 binbin 男 44 tianhua 女 55 liyun 女 65
PL/SQL 过程已成功完成。
===============================================================
bibno-->ed 已写入文件 afiedt.buf
1 declare 2 cursor test_cur is 3 select name,sex,score from test; 4 begin 5 dbms_output.put_line('用户资料列表:'); 6 for namet in test_cur 7 loop 8 dbms_output.put_line(namet.name||' '||namet.sex||' '||namet.score); 9 end loop; 10* end; 11 / 用户资料列表: xiaoli 女 90 renbinbo 男 100 xiaoming 男 89 xiaowang 男 91 xiaohua 女 98 yunfeng 男 88 wangming 男 78 wuming 男 98 xiaobin 男 68 binbin 男 44 tianhua 女 55 liyun 女 65
PL/SQL 过程已成功完成。
===============================================================
带参数的显式游标 SET SERVEROUTPUT ON SQL> DECLARE desig VARCHAR2(20); emp_code VARCHAR2(5); empnm VARCHAR2(20); CURSOR emp_cur(desigparam VARCHAR2) IS SELECT empno, ename FROM employee WHERE designation=desig; BEGIN desig:= '&desig'; OPEN emp_cur(desig); LOOP FETCH emp_cur INTO emp_code,empnm; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_code||' '||empnm); END LOOP; CLOSE emp_cur; END; =============================================================== SET SERVEROUTPUT ON SQL> DECLARE new_price NUMBER; CURSOR cur_toy IS SELECT toyprice FROM toys WHERE toyprice<100 FOR UPDATE OF toyprice; BEGIN OPEN cur_toy; LOOP FETCH cur_toy INTO new_price; EXIT WHEN cur_toy%NOTFOUND; UPDATE toys SET toyprice = 1.1*new_price WHERE CURRENT OF cur_toy; END LOOP; CLOSE cur_toy; COMMIT; END;
=============================================================== 游标变量的功能强大，可以简化数据处理
游标变量的优点有: 1.可从不同的 SELECT 语句中提取结果集 2.可以作为过程的参数进行传递 3.可以引用游标的所有属性 4.可以进行赋值运算
使用游标变量的限制： 1.不能在程序包中声明游标变量 2.FOR UPDATE子句不能与游标变量一起使用 3.不能使用比较运算符 =============================================================== ===============================================================
创建过程 create procedure test_b(test varchar2,test1 number) as begin
dbms_output.put_line(test); dbms_output.put_line(test1); end;
create procedure test_c(test varchar2,test1 char) as aa varchar2(20); bb char(10); begin select name into aa from test where name=test; dbms_output.put_line(aa); select age into bb from test where age=test1; dbms_output.put_line(bb); end; =============================================================== 创建函数 create or replace function test_binbo return varchar2 as begin return '我爱你!'; end 执行: select test_binbo from dual;
create or replace function test_binbo return varchar2 as aa varchar2(20); bb char(3); begin bb:='&bb'; select name into aa from test where sex=bb; return 'name'; end; 执行: select test_binbo from dual;
create or replace function item_price_range(price number) return varchar2 as min_price number; max_price number; begin select max(itemrate),min(temrate) into max_price,min_price from test; if price>=min_price and price<=max_price then return '将计就计机'; else return '哩哩啦啦理论'; end if; end; 执行: select test_binbo from dual;