oracle

最新推荐文章于 2024-09-28 22:47:40 发布

solomon_wei

最新推荐文章于 2024-09-28 22:47:40 发布

阅读量349

点赞数

文章标签： integer table insert less exception branch

本文链接：https://blog.csdn.net/solomon_wei/article/details/4525590

版权

在oracle中,如果遇到空格,使用" "

=========================================================================ORACLE入门========================================================================
--创建用户

create user king
identified by 123456
default tablespace users
quota 30m on users;

--授权
grant create session to king,solomon;
grant connect to king,solomon;
grant resource to king,solomon;
grant create sequence to king,solomon;
grant select,update,insert,delete on xxx to king,solomon;

revoke select,update,insert,delete on xxx from king,solomon;

--修改密码
alter user king identified by password;

--删除用户
drop user king cascade;

======================================================================SQL查询和SQL函数=====================================================================

--创建表
create table test
as
select * from scott.emp
where 1=2;

alter table test modify job varchar2(15);
alter table test add description varchar2(200);
alter table test drop column description;

--truncate table命令
如果存储在表的数据不再有用,可以只删除表中的记录而不删除表结构.使用trancate table命令将删除表中的所有行,并释放此表使用的存储空间.
trancate 与 delete 命令的区别在于,前者快速删除记录并释放空间,不使用事务处理,因此无法回滚,而delete命令可在执行删除之后,通过rollback撤销删除.如果确定表中的数据不再有用,使用trancate命令效率更高.
truncate table test

--删除表
drop table test;

--select
select (distinct)empno,ename,job,sal from test where sal > 1500 group by empno,ename,job,sal having ename <> 'ALLEN' order by empno desc

--事务控制语言
-commit: commit work/commit;
-savepoint: savepoint savepoint_id;
-rollback: rollback work/rollback/rollback to savepoint savepoint_id;

--SQL 操作符
1.算术操作符: +,-,*,/
2.比较操作符: =,!=,<,>,<=,>=,<>,(not)between...and,(not)in,(not)like,is(not)null
3.逻辑操作符: and,or,not
4.集合操作符: union(联合):返回两个查询选定的所有不重复的行
    union all(联合所有):返回两个查询选定的所有行,包括重复的行
    intersect(交集):只返回两个查询都有的行
    minus(减集):在第一个查询结果中排除第二个查询结果中出现的行

    使用集合操作符连接起来的select语句中的列遵循以下规则:
    1).通过集合操作联接的各个查询具有相同的列数,而且对应列的数据类型必须相同.
    2).这种查询不应含有LONG类型的列.
    3).列标题来自第一个select语句.
5.连接操作符: ||

--操作符优先级(从高到低)
算术操作符 --> 连接操作符 --> 比较操作符 --> NOT逻辑操作符 --> AND逻辑操作符 --> OR逻辑操作符

--SQL 函数
(一)单行函数(也称标量函数),对于从表中查询的每一行,都返回一个值,可出现在select,where 子名句中,包括:

日期函数:
add_months(d,n)    d是日期,n是月数,返回指定日期加上指定月数后的日期值
months_between(d1,d2)  d1,d2都是日期,输出结果为一个数值.如果d1晚于d2,则结果为正数,反之为负.如果d1,d2为某月中的同一天或月份的最后一天,则结果始终为整数       ;否则oracle将根据一个月31天来计算结果的小数部分,并考虑d1,d2时间部分的差
lsat_day(d)     返回指定日期当月的最后一天的日期值
round(d,[fmt])    d是日期,fmt是格式模型,日期默认舍入为最靠近的那一天.如果格式为"YEAR",则舍入到年的开始,即1月1日;如果为"MONTH",则舍入到月的第一日       ;如果为"DAY",则舍入到最靠近的星期几.
next_day(d,day)    d是日期,day是周内的任何一天(如'星期三',也可用4表示),返回指定下一个星期几(三)的日期
trunc(d,[fmt])    d是日期,将指定日期截断为由格式模型指定的单位的日期,与round函数不同的是它只舍不入,如果不指定fmt,日期则被截断为天.
extract(fmt from d)   d是日期时间表达式,fmt是要提取的部分格式:(YEAR,MONTH,DAY,HOUR,MINUTE,SECOND不要用单引号)

字符函数:
INITCAP(char):首字母大写
LOWER(char):转换为小写
UPPER(char):转换为大写
LTRIM(char,set):左剪裁
RTRIM(char,set):右剪裁
TRANSLATE(char,from,to):按字符翻译
REPLACE(char,search_str,replace_str):字符串替换
INSTR(char,substr[,pos]):查找字符串位置
SUBSTR(char,pos,len):取子字符串
CONCAT(char1,char2):连接字符串
CHR(num):根据ASCII码返回对应的字符串
LPAD/RPAD(char1,num,char2):对字符串进行左/右填充num个char2
TRIM([LEADING/TRAILING]char):从字符串的开头或结尾剪裁特定的字符,默认剪裁空格(此函数组合了LTRIM RTRIM 的功能)
LENGTH(char):返回字符串的长度
DECODE(expr,search1,result1,search2,result2):如果遇到search1,将其替换为result1....

数字函数
ABS(n):取绝对值
CELL(n):向上取整
SIN(n):正弦
COS(n):余弦
SIGN(n):取符号
FLOOR(n):向下取整
POWER(m,n):m的n次幂
MOD(m,n):取余数
ROUND(m,n):四舍五入
TRUNC(m,n):截断
SQRT(n):平方根

转换函数
TO_CHAR(d|n[,fmt]):d是日期,n是数字,fmt是指定日期或数字的格式
TO_DATE(char[,fmt])

其他函数
NVL(expression1,expression2):如果expression1为NULL,返回expression2,反之返回expression1
NVL2(expression1,expression2,expression3):如果expression1为NULL返回expression2反之返回expression3
NULLIF(expr1,expr2):如果两个表达式相等,返回空值,否则返回expr1 = CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

(二)分组函数
AVG,MIN,MAX,SUM,COUNT,COUNT(*),COUNT(DISTINCT col_name),GROUP BY,HAVING

(三)分析函数(只能出现在select列表或order by子句中)
ROW_NUMBER:为有序组中的每一行(划分部分的行或查询返回的行)返回一个唯一的排序值,序号由order by 子句指定,从1开始
ROW_NUMBER() OVER ([PARTITION BY column] order by column (desc/asc))
select ename,job,deptno,sal,ROW_NUMBER() OVER (ORDER BY sal DESC) AS sal_rank from scott.emp;
select ename,job,deptno,sal,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS sal_rank from scott.emp;

RANK:计算一个值在一组值中的排位,是1开头的连续整数.具有相等值的行排位相同,序数随后跳跃相应的数值,即如果两行的序数为1,则没有序数2,下一行的序数为3
RANK() OVER ([PARTITION BY column] order by column)
select ename,sal,comm,deptno,RANK() OVER (PARTITION BY deptno ORDER BY sal DESC,comm) RANK FROM scott.emp;

DENSE_RANK:计算一个行在一组有序行中的排位,排位是以1开头的连续整数.具有相等值的行排位相同,并且排位是连续的
DENSE_RANK () OVER ([PARTITION BY column] order by column)
select d.dname,e.ename,e.sal,DENSE_RANK () OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) DENSERANK FROM emp e,dept d where e.deptno = d.deptno;

==============================================================锁和表分区===================================================================================
锁的优点:(一致性,完整性,并发性)

行级锁
select ... for update [of column] wait n |nowait;

表级锁
LOCK TABLE table_name IN row share|row exclusive|share|share row exclusive|exclusive MODE [nowait]

--范围分区(range)

1.新建分区表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)
);

2.插入数据
scott>desc student;
名称是否为空? 类型
----------------------------------------- -------- -------------------

STUDENTID                                 NOT NULL NUMBER(38)
STUDENTNAME                                        VARCHAR2(20)
SCORE                                              NUMBER(38)

scott>insert into student values (&stun, '&name', &score);
输入 stun 的值: 10
输入 name 的值: user1
输入 score 的值: 52
原值 1: insert into student values (&stun, '&name', &score)
新值 1: insert into student values (10, 'user1', 52)

已创建 1 行。

scott>/
输入 stun 的值: 11
输入 name 的值: sz
输入 score 的值: 85
原值 1: insert into student values (&stun, '&name', &score)
新值 1: insert into student values (11, 'sz', 85)

已创建 1 行。

--散列分区(hash)
create table sodi
(
deptno int,
deptname varchar2(14)
)
partition by hash (deptno)
(partition p1,
partition p2
)

--复合分区(range hash)
create table branchdetails1
(
branch_id varchar2(5) not null,
branch_name varchar2(10) not null,
address varchar2(10)
)
partition by range (branch_id)
subpartition by hash (branch_name)
subpartitions 6
(
partition s1 values less than ('b005'),
partition s2 values less than ('b010'),
partition s3 values less than ('b015'),
partition s4 values less than ('b020')
);

--列表分区(list)
CREATE TABLE Employee
(
    Emp_ID number (4),
    Emp_Name varchar2 (14),
    Emp_Address varchar2 (15)
)
PARTITION BY LIST (Emp_Address)
(
    Partition north values ('芝加哥'),
    Partition west values ('旧金山', '洛杉矶'),
    Partition south values ('亚特兰大', '达拉斯', '休斯顿'),
    Partition east values ('纽约', '波斯顿')
);

insert into employee values (110, 'july', '芝加哥');
insert into employee values (111, 'claduo', '旧金山');
insert into employee values (112, 'apple', '洛杉矶');
insert into employee values (113, 'wind', '亚特兰大');
insert into employee values (114, 'snow', '达拉斯');
insert into employee values (115, 'buick', '休斯顿');
insert into employee values (116, 'toyoto', '纽约');
insert into employee values (117, 'bush', '波斯顿');
insert into employee values (119, 'casy', '芝加哥');
insert into employee values (120, 'cool', '纽约');

--分区维护操作
1.添加分区(范围)
ALTER TABLE table_name ADD PARTITION XX VALUES LESS THAN (XXX);
2.删除分区
ALTER TABLE table_name DROP PARTITION XX;
3.截断分区
ALTER TABLE table_name TRUNCATE PARTITION XX;
4.合并分区
ALTER TABLE talbe_name MERGE PARTITIONS partitions_name,partitions_name INTO PARTITION partition_name;
5.拆分分区
ALTER TABLE table_name SPLIT PARTITION partitioni_name AT (value) INTO (PARTITION partition1, PARTITION partition2);

重命名分区
ALTER TABLE table_name RENAME PARTITION partition1 TO partition2;

=============================================================数据库对象===================================================================================

--同义词
创建私有同义词:CREATE [OR REPLACE] SYNONYM [schema.]synonym_name FOR [sechma.]object_name;
创建共有同义词:CREATE [OR REPLACE] PUBLIC SYNONYM synonym_name FOR [sechma].object_name;
删除同义词:DROP [PUBLIC] SYNONYM [sechma.]synonym_name;

--序列
创建序列:
CREATE SEQUENCE sequencn_name
[START WITH integer]
[INCREMENT BY integer]
[MAXVALUE integer|NOMAXVALUE]
[MINVALUE integer|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE integer|NOCACHE];

访问序列:NEXTVAL,CURRVAL

更改序列:
ALTER SEQUENCE [schema.]sequence_name
[INCREMENT BY integer]
[MAXVALUE integer|NOMAXVALUE]
[MINVALUE integer|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE integer|NOCACHE];

删除序列:
DROP SEQUENCE [schema.]sequence_name;

--视图
创建视图:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[,alias....不需要引号])]
AS SELECT ....
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY];

删除视图:
DROP VIEW view_name;

--索引
创建索引:
CREATE INDEX index_name ON TABLE (column_list) [TABLESPACE tablespace_name];
重建索引:
ALTER INDEX index_name REBUILD;
删除索引:
DROP INDEX index_name;

唯一索引:
CREATE UNIQUE INDEX index_name ON table_name (column);
组合索引:
CREATE INDEX index_name ON talbe_name (column1,column2);
反向键索引:(不准将标准索引重建为反向键索引)
CREATE INDEX rev_index_name ON table_name (column) REVERSE;
将反向键索引重建为标准索引:ALTER INDEX rev_index_name REBUILD NOREVERSE;
位图索引:
CREATE BITMAP INDEX bit_index_name ON table_name (column);
索引组织表:
CREATE TABLE table_name
(
column1 type PRIMARY KEY,
column2 type
)ORGANIZATION INDEX;

索引中的分区:
(1):局部分区索引
1.先创建分区表:
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)
);
2.创建局部索引:
CREATE INDEX index_name ON student (score) LOCAL;

(2):全局分区索引(不能在散列分区或子分区建立全局索引)
CREATE INDEX glb_index_name ON table_name (column) GLOBAL
PARTITION BY RANGE (column)
(
PARTITION P01 VALUES LESS THAN (XXX),
PARTITION P02 VALUES LESS THAN (MAXVALUE)
);
(3):全局非分区索引,是在分区表上创建的全局索引,类似于非分区表上的索引,索引的结构不会被分割.

获得索引的信息:
USER_INDEXES:获取有关用户已创建的索引的详细信息;
USER_IND_PARTITIONS:获取有关用户已创建的分区索引的详细信息;
USER_IND_COLUMNS:获取有关列(用户的索引是基于这些列创建的)的详细信息.

=======================================================================使用PL/SQL==========================================================================
PL/SQL块共分为3个部分:1.声明部分(declarations);2.可执行部分(executable statements);3.异常处理块部分(exception handlers).

复合符号的含义:
:= 赋值操作符
|| 连接操作符
-- 单行注释
/*,*/ 多行注释
<<,>> 标签分隔符
.. 范围操作符
** 求幂操作符

声明变量:(一条语句只能声明一个变量,反之是非法的:如 I,J NUMBER(4),这样是不行的!!!)
variable_name date_type[(size)] [:=init_value];
declare
vcode varchar2(5);
vname varchar2(25) := 'jack';
begin
...
end;
给变量赋值:
variable_naem := expression;
声明常量:
variable_name CONSTANT data_type := value;

属性类型:(%TYPE,%ROWTYPE),用于引用变量或数据库列的数据类型来声明变量.

--控制结构
1.条件控制:
a).IF-THEN语句
IF condition THEN
  sequence_of_statements;
END IF;
b).IF-THEN-ELSE语句
IF condition THEN
  sequence_of_statements1;
ELSE
  sequence_of_statements2;
END IF;
c).IF-THEN-ELSIF语句
IF condition1 THEN
  sequence_of_statements1;
ELSIF condition2 THEN
  sequence_of_statements2;
ELSE
  sequence_of_statements3;
END IF;
d).CASE语句
CASE selector
  WHEN expression1 THEN sequence_of_statements1;
  WHEN expression2 THEN sequence_of_statements2;
  ...
  WHEN expressionN THEN sequence_of_statementsN;
  [ELSE sequence_of-statementsN+1;]
END CASE;
2.循环控制:
a).LOOP循环
LOOP
  sequence_of_statements;
  (这里必须要有EXIT或EXIT WHEN 语句,否则出现无限循环!!!)
END LOOP;

BEGIN
  LOOP
   IF &marks > 60 THEN
   DBMS_OUTPUT.PUT_LINE('XXXX');
   EXIT;
   END IF;
  END LOOP;
END;

b).WHILE循环
WHILE condition LOOP
  sequence_of_statements;
END LOOP;

C).FOR循环
FOR counter IN [REVERSE] value1..value2
LOOP
  sequence_of_statements;
END LOOP;

--错误处理
预定义异常:(P131)
BEGIN
sequence_of_statements;
EXCEPTION
WHEN <NO_DATA_FOUND|TOO_MANY_ROWS|...> THEN
sequence_of_statements;
WHEN OTHERS THEN
sequence_of_statements;
END;

用户自定义异常:
DECLARE my_exception EXCEPTION;
引发异常:
RAISE my_exception;

DECLARE
my_exception EXCEPTION;
name varchar2(20);
BEGIN
name := '&name';
IF name NOT IN ('jack','king','alice') THEN
RAISE my_exception;
ELSE
dbms_output.put_line('你输入的姓名是'||name);
END IF;
EXCEPTION
WHEN my_exception THEN
dbms_output.put_line('无法识别这个姓名!');
//RAISE_APPLICATION_ERROR(-20001,'未指定姓名!'); (这个编号必须介于-20000 ~ -20999 之间)
END;

=======================================================================游标管理============================================================================
静态游标
隐式游标:%FOUND(true/false),%NOTFOUND(true/false),%ROWCOUNT(0 ~ ),%ISOPEN(false)

显示游标(CURSOR):
  使用显示游标的4个步骤:
  1.声明游标;
  2.打开游标;
  3.从游标中获取记录;
  4.关闭游标.

声明游标: CURSOR cursor_name [(parameter [,parameter]...)] [RETURN return_type] IS select_statement;
OPEN:  OPEN cursor_name [(parameters)];
FETCH:  FETCH cursor_name INTO variables;
CLOSE:  CLOSE cursor_name;