oracle数据库开发技术

修改表名？、？？？？？？？？？？

创建用户，授予权限

用户名加c##

-- 使用超级管理员登录
conn sys/root as sysdba;
**可以不用超级管理员，system也可以，尽量**
-- 创建scott用户
create user c##scott identified by tiger;

-- 为用户授权
grant connect,resource,unlimited tablespace to c##scott container=all;
-- 把emp表中的select权限授权给用户wanghao
grant select on tableName(emp) to wanghao;
-- 设置用户使用的表空间
alter user c##scott default tablespace users;
alter user c##scott temporary tablespace temp;

-- 使用c##scott用户登录
connect c##scott/tiger

显示用户是谁

show user;

查看scott用户下的表信息

select * from all_tables where owner=‘C##SCOTT’;

Oracle用户锁定/解锁（DBA用户执行）

锁定用户：
alter user user_name account lock;
用户解锁：
alter user user_name account unlock;

查看用户状态：
#一个账号状态可以是打开的(open),也可以是锁定(locked)或过期的(expired)
select username,account_status from dba_users where username = upper(‘user_name’);

新建角色

角色就是权限的集合，可以把一个角色授权给用户

create role myrole;//创建角色
grant create session to myrole;//将创建session的权限授予myrole
grant myrole to username;//授予username用户myrole的角色
drop role myrole;删除角色

权限

查看权限

select * from user_sys_privs;//查看当前用户所有权限
select * from user_tab_privs;//查看所用用户对表的权限

授予权限

grant connect to username;//授予连接的权限，包含create session
grant create session to username;//授予username用户创建session的权限，即登陆权限，可以登陆
grant unlimited tablespace to username;//授予username用户使用表空间的权限
grant create table to username;//授予创建表的权限
grante drop table to username;//授予删除表的权限
grant insert table to username;//插入表的权限
grant update table to username;//修改表的权限
grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)

-- 把emp表中的select权限授权给用户wanghao
grant select on tableName(emp) to wanghao;

grant create session to myrole;//将创建session的权限授予myrole角色

grant select on tablename to username;//授予username用户查看指定表的权限
grant drop on tablename to username;//授予删除表的权限
grant insert on tablename to username;//授予插入的权限
grant update on tablename to username;//授予修改表的权限
grant insert(id) on tablename to username;
grant update(id) on tablename to username; //授予对指定表特定字段的插入和修改权限，注意，只能是insert和update
grant alert all table to username;//授予username用户alert任意表的权限
grant 角色 to 用户//把角色给用户

撤销权限

revoke create session from c##test_user;

表空间

查看数据字典dba_tablespaces的结构信息
desc dba_tablespaces;
查看oracle数据库默认创建的表空间通过数据字典dba_tablespaces
select tablespace_name,contents,status from dba_tablespaces;
显示表空间所包含的数据文件
select file_name,bytes from dba_data_files where tablespace_name=‘SYSTEM’;
永久性表空间为users，默认临时表空间为temp
查看oracle用户以及其相关数据表信息
select * from dba_tables;
查询system用户的help表信息的语句通过dba_tables视图查看用户的所有数据表的信息
select * from dba_tables where owner=‘SYSTEM’ and table_name=‘HELP’;

创建表空间

在SQL Plus中输入sys as sysdba命令以sys用户作为sysdba身份登录数据

create tablespace user_zhibao
datafile 'D:\user_zhibao.dbf'
size 100M
autoextend on next 50M
maxsize 1G;

查看表空间

select * from dba_free_space where tablespace_name='USER_ZHIBAO';文件名要大写

创建临时表空间

create temporary tablespace user_zzb
tempfile 'D\zzb.dbf'
size 50M

查看临时表空间

select * from dba_temp_files;

修改表空间

重命名
alter tablespace 旧名 rename to 新名
设置读写状态，只读和读写
alter tablespace 表名 read only 或 read write
表空间可用状态
alter tablespace 表名 online或offline normal或temporary或immediate，联机（可用），脱机（不可用）
设置默认的表空间
alter database default tablespace 永久性表空间名
alter database default temporary tablespace 临时表空间名

删除表空间

把表放到表空间中去

alter table c##scott.zhibao move tablespace USER_ZHIBAO;

表与表结构操作（重点）

新建表

create table zhibao(
id number(4) primary key,
name varchar2(20) not null
);

删除表

drop table ZHIBAO;

查看表结构

DESC :查询表结构
DESC emp;

使用命令方式操作表（增删改查）

增加字段
alter table emp add image number(2);
修改数据类型
alter table emp modify job varchar(60);
删除列
alter table emp drop column sal;
修改表名
rename table emp to emppp;（旧to新）
删除表
drop table emp;
修改列名
alter table emp rename column sal to salll;

约束

约束（完整性约束条件）
作用：保证数据的准确性
内容：非空、唯一、主键、外键和检查
分类：列级约束、表级约束

约束的语法

修改约束名称
alter table 表名 rename constraint 旧名 to 新名
删除约束
alter table 表名 drop constraint 约束名

创建约束的语法

建表时，直接加在列名后面
id number(4)
建表后，使用alter table 表名 + add constraint 约束名约束类型(字段名)
修改的方式，alter table 表名 modify 字段名约束类型

唯一约束 unique

含义：某一列的值不允许重复，允许值为空值

建表时创建
create table student（
sid number(4) constraint student_sid_nn not null,
user_name varchar2(10),
email varchar2(20),constraint student_email_uk unique(email，column1，，，columnn)
);
建表后创建
alter table test modify ssss unique;
alter table student add constraint name_uk unique(user_name);
添加列时创建
alter table student add salary number(5,2) unique;

主键约束 primary key

建表的时候
create table student（
–列级约束
id number(2) primary key,
sid number(4) constraint student_sid_nn not null,
user_name varchar2(10),
email varchar2(20),
表级约束
constraint student_email_uk unique(email，column1，，，columnn)
);
建表后
alter table test modify column1 primary key;
alter table test add constraint c1_pk primary key(column1);

非空约束 not null

创建时
create table book（bid number(4) not null,
title varchar2(20)
);
建表后
alter table 表明 modify 字段名约束类型
alter table book modify title not null ;
或者alter table book modify(title varchar(2) not null);
添加列的时候
alter table book add author varchar2(10) not null;

默认值约束

创建时
create table table_name(column1 data_type default value);
修改时
alter table table_name modify 字段名 deafult value;

外键约束

https://blog.csdn.net/wy_0928/article/details/51153191?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163939685316780271979981%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=163939685316780271979981&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduend~default-1-51153191.pc_search_all_es&utm_term=oracle%E5%A4%96%E9%94%AE%E7%BA%A6%E6%9D%9F&spm=1018.2226.3001.4187

emp和dept 是子父关系
外键约束（即引用完整性约束）
子表上定义的外键的列值，必须从主表被参照的列值中选取，或者为null
当主表参照列的值被子表参照时，主表的该行记录不允许被删除

创建时的语法
create table student (–学生表
s_id number(5) primary key,
s_name varchar2(20),
t_id varchar2(5),
constraint fk_stu foreign key (t_id) references team (t_id)
)；
create table team (–班级表
t_id varchar2(5) primary key,
t_name varchar2(20)
)；

其中constraint fk_stu foreign key (t_id) references team (t_id)中，第一个t_id指的是学生表中的t_id，第二个t_id指的是班级表中的t_id
外键约束：是一种子父关系，且可以很好的保护两表之间的关系

修改时
alter table dept modify id primary key;
alter table emp add constraint emp_fk foreign key (deptno)
references dept(deptno);

检查约束

检查约束是什么
检查约束的作用就是让表中的数据更有意义。检查约束就是为了检测和过滤不符合实际意义的数据，比如说年龄1000，工资-500等这些数据。
在创建表时设置检查约束
1）列级检查约束
语法：
creat table table_name ( column_name datatype check(expressions)) ;
check是检查关键字
expressions 是约束的条件
实例：
将salary 设置检查约束，使其>0 。
create table userinfo_c(
id varchar2(10) primary key ,
username varchar2(20) ,
salary number(5,0) check(salary>0)) ;
2）表级级检查约束
语法
constraint constraint_name check(expressions)
实例
create table userinfo_c1(
id varchar2(10) primary key ,
username varchar2(20),
salary number(5,0),
constraint ck_salary check(salary>0) );

7、对c_birth设置检查约束，出生日期必须是2000年以后
//alter table customers_info add constraint customers_c_birth_ck check (c_birth>2000);
//修改后
alter table customers_info add constraint customers_c_birth_ck check (c_birth>to_date(‘2000-01-01’,‘yyyy-mm-dd’)); (注意把表名修改为customers_info)
//删除检查约束
alter table customers_info drop constraint customers_c_birth_ck;

创建检查约束判断age在18到60岁之间的男性或者age在18到55岁之间的女性
alter table customer add constraint c_ck check((sex=‘男’) and (age between 18 and 60) or (sex=‘女’) and (age between 18 and 55));

禁用约束，启用约束

禁用，约束不起作用
–禁用约束 cascade 可选如有级联约束的话
alter table demo_dept4 disable constraint 约束名字 cascade;
–启用约束
Alter table demo_dept4 enable constraint 约束名字
注意:如果插入的数据违反唯一约束即便执行也是无效

在数据字典中查询约束

索引

概念：

是一个单独的、物理的数据库对象
用于存放表中每一条记录的位置的对象。
在创建索引时，先要对索引字段进行排序。
索引由Oracle自动维护
优点：提高查询速度
缺点：创建和维护索引需要时间；索引需要物理空间，随着数据量的增大而增大；

创建方式

自动创建：通过约束，系统创建
手工创建
create [unique] [bitmap] index index_name on table_name(column_name)
[reverse] [tablespace tablespace_name]
创建B树索引
create index dname_dept on dept(dname);
默认情况下，创建的索引是不唯一的索引
创建基于函数的索引
create index ename_lower_index
on emp(lower(ename));
select * from emp
where lower(ename)=‘smith’;
创建复合索引
适合查询where job=’’ and ename=’’;
create index emp_idx on emp(job,ename);

查看，修改，删除索引

查看索引
select * from user_ indexes;是es不是s
修改索引
alter index　index_name rename to new index_name;
alter index dept_deptno_pk rename to pk_deptno_dept;
删除索引
删除索引与索引创建时采用的方式有关
手动创建的索引，通过命令删除
drop index index_name;
自动创建的索引
通过禁用约束或删除约束的方式来删除对应的索引。
删除表，则会完全删除所有索引

序列

概念：

1）是数据库对象。
2）用于产生一系列唯一的数字值
3）序列号的生成独立于表
4）同一序列生成器可用于一个或多个表
5）经常用来产生主键值

创建序列，直接创建复制

create sequence sequence
increment by 10
start with 50
maxvalue 100
cycle
nocache;

删除序列

drop sequence 序列名;

使用序列

伪列的引入
NEXTVAL，nextval 返回下一个有效的序列值.每执行一次会返回一个新的数值。（类似于指针的用法）
CURRVAL，currval返回当前的序列值.

–使用伪列插入记录
insert into students values(sequence1.nextval,‘xiaobai’,‘男’,20);
insert into students values(sequence1.nextval,‘ruyan’,‘女’,19);
select * from students;

注意：在使用伪列时，首先要创建一个序列，具体创建该序列的oracle命令省略

修改序列

修改序列
可以修改增量值，最大值，最小值，循环选项，缓存选项，
不可以修改初始值
序列的最小整数值不能大于当前值
序列的最大整数值不能小于当前值

ALTER SEQUENCE dept_deptno
INCREMENT BY 1
MAXVALUE 999999
NOCACHE
NOCYCLE;

同义词 synonym

是数据库的别名，通过同义词可以简化对象的访问
创建
CREATE [PUBLIC] SYNONYM 同义词名
FOR 对象名;
public 选项表示所有用户均可使用.
删除同义词：
使用drop synonym

数据类型

字符类型char（n）和varchar2(n)
相同点：可以保存数字、字母
不同点，char长度固定、时间效率，varchar2长度可变、空间效率
数字类型 number（m,n）, number(m)
m：总的位数，n小数位数。
整数位数=m-n
时间类型 date和timestamp
相同点：可以记录时间
不同点：date保存时间不含毫秒，后者含毫秒
大字段类型 clob 和 blob
相同点：可以存储较大数据
不同点：clob存放海量文字，blob存放图片，电影音乐的二进制文件
常用 varchar2 number date clob

体系结构

完整的oracle数据库是由实例和数据库组成
实例是由一系列的后台进程和内存区组成的，就是内存和后台进程的集合
数据库是一系列物理文件的集合

逻辑结构（介绍了一些表空间）

数据库>表空间>段>数据块
一个oracle通常会有7个表空间
system：系统表空间，必须有的
sysaux：是system的辅助表空间，许多数据库的工具和可选组件将其对象存储在此，
users：存储用户数据
undo：事务的回滚，撤销
temp：oracle运行中需要临时存放的数据，如排序的中间结果

数据字典

是oracle数据库的核心组件，由基表和数据字典视图组成

数据库的查询和视图

查询语句等操作

select xxx_name, count(*) as counter
from table_y
where where_condition
group by xxx_name
having having_condition
order by zzz

下面列出其执行顺序：
1. 根据where子句选择行；
2. 根据group by 子句组合行；
3. 根据having子句筛选group by分出来的组；
4. 根据order by子句中的分组函数的结果对组进行排序，order by必须使用分组函数或者使用Group by子句中指定的列；

基本查询

查询工资在2000到3000之间的
<>不包含2000和3000的，between...and...是包含2000和3000
select * from emp where sal>2000 and sal<3000;
select * from emp where sal between 2000 and 3000;

日期在这之间的
select * from emp where hiredate between '1-1月-1980' and '31-12月-1980';

查询某列的值在某个列表中的数据行
select * from emp where empno in(1111,7369);

工资为800,1600,3000的信息
select * from emp where sal in(800,1600，3000);

like字符串匹配
以S开头的信息
select * from emp where ename like 'S%';

含有S的信息
select * from emp where ename like '%S%';

使用函数显示姓名字段的任何位置包含’A’的所有员工的姓名
select ename from emp where instr(ename,'A') !=0;

有五个字符的名字
select * from emp where ename like '_____';

空值查询
select * from emp where comm is null;

查询显示的方式

其中雇佣日期以“1980年12月17日”这样的形式显示，薪水值以“rmb5000.00”这样的形式显示
select ename,to_char(hiredate,'yyyy"年"mm"月"dd"日"') chusheng,'rmb'||to_char(sal,'9999.99') from emp where sal>2000 order by sal desc;
构造sql语句，查询emp表的信息，格式为“员工编码empno，姓名ename，月收入（工资+奖金）”
select '员工编码'||empno||'，姓名'||ename||'，月收入'||nvl(sal+comm,sal) from emp;

连接查询

连接查询中on后面的连接条件一般是=，
还有非等值连接，使用关系运算符>, <, >=, <>, between …and in；
查找出工资等级不为 4 级的员工的员工名字，部门名字，部门地点。
select ename,dname,loc,grade from emp join dept on emp.deptno=dept.deptno join salgrade on sal between losal and hisal where grade<>4;

增删改查语句

insert into emp(empno,ename) values(111,'AAA');
delete from emp where ename='AAA';
update emp set ename='bbb' where ename='AAA';
update emp set sal=10000 where ename='AAA';

排序

排序
//空值最大，
select * from emp order by sal asc;   asc可以省略
select * from emp order by sal desc;降序
按照别名排序
select sal*12 as salll from emp order by salll desc;as可以省略
多个列排序
select deptno,sal*12 salll from emp order by deptno, salll desc;，按照deptno正序，按照salll降序
使用表达式排序
select * from emp order by sal*12;sal的值不会变
使用列位置排序
select * from emp order by 1;根据第一列的值排序

聚合函数

count，（对null值不算），avg，sum，可以使用distinct以防数据重复，，，max，min
avg和sum是对数值型数据，min和max是对任何数据类型

有几个组别
select count(distinct(deptno)) from emp;
有几个人，不重复
select count(distinct(ename)) from emp;

分组函数（group by）

分组函数（多行函数）
分组函数作用于一组数据，并对一组数据返回一个值
group by 里面的东西在select里面可以不用存在，但反过来不行。

如果出现嵌套组函数必须有group by 
select round(max(avg(sal))) from emp;错
select round(max(avg(sal))) from emp group by deptno;对

每个组的平均工资，大于2000的
select deptno,round(avg(sal),2) from emp group by deptno having avg(sal)>2000;

group by 多个列
select deptno,job,sum(sal) from emp group by (deptno,job);可以加括号，order by 不可以加

如果select语句中包含组函数，那么查询的不是组函数的列必须出现在group by语句中

having子句

必须在group by语句之后的，也就是分组是前提

连接

rownum获取数据表前若干行

前四行
select * from emp where rownum<5;
3到5行
select * from (select rownum r,empno from emp where rownum<=5) re where r>=3;

日期类

select * from emp where hiredate<to_date('17-12-1980','DD-MM-YYYY');
生产日期在1980年12月17日之前的商品信息
select * from emp where extract(year from hiredate)>=1980;
select * from emp where to_char(hiredate,'YYYY')>=1980;
1980年之后的

select * from emp where extract(year from hiredate)<=1980;
1980年之前的

视图

创建视图

create view kan as select * from c##scott.dept where deptno=10;

重命名视图

rename 旧视图名 to 新名;

修改视图

alter view

删除视图

drop view 视图名;

函数

数值函数

1、nvl(value1,value2)
这个函数的意思是如果value1的值为null,那么函数返回value2的值 ,如果value1不为空,那么就返回value1的值。
需要注意的是value1和value2要保持字段类型相同。
2、nvl2(value1,value2,value3)
这个函数的意思是如果value1的值为null 函数返回value3 否则函数返回value2 也就是说函数永远不会返回value1

2. 分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金)
select deptno,job,avg(nvl(comm,0)),sum(nvl2(comm,sal+comm,sal)) from emp group by deptno,job;

生成随机数并四舍五入

4. 生成一个1—20之间的随机数，再对其进行四舍五入处理，保留一位小数点。
select round(dbms_random.value(1,20),1) from dual;

返回绝对值，abs函数
select abs(-10) from dual;//10
ceil函数，返回比他大的整数
select ceil(10.4) from dual；//11
select ceil(-10.4) from dual；//-10
floor函数，返回比他小的整数
select floor(10.4) from dual;//10
select floor(-10.4) from dual;//-11
mod函数
select mod(12,5) from dual;//2
select mod(12，-5) from dual;//2
select mod(-12，5) from dual;// -2
select mod(-12，-5) from dual;// -2
trunk 函数

1.select trunc(sysdate) from dual  --2019-08-20 00:00:00              今天的日期为2019-08-20
2.select trunc(sysdate, 'mm') from dual  --2019-08-01 00:00:00        返回当月第一天.
3.select trunc(sysdate,'yy') from dual  --2019-01-01 00:00:00         返回当年第一天
4.select trunc(sysdate,'dd') from dual  --2019-08-20 00:00:00         返回当前年月日
5.select trunc(sysdate,'yyyy') from dual  --2019-01-01 00:00:00       返回当年第一天
6.select trunc(sysdate,'d') from dual  --2019-08-18 00:00:00          (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual   --2019-08-20 10:00:00       当前时间为10:00 
8.select trunc(sysdate, 'mi') from dual  --2019-08-20 10:05:00        TRUNC()函数没有秒的精确


/***************数字********************/
/* TRUNC（number,num_digits） Number 需要截尾取整的数字。 Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。 TRUNC()函数截取时不进行四舍五入 */
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.40
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual  --123.4580
15.select trunc(123) from dual  --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120

字符函数

substr函数，从第几个位置开始，截取几个，而不是到第几个位置

substr(ename,1,3) ：字符截取函数
select substr('abcd',1,2) from dual;//ab
select substr('123456',2,3) from dual;//234

instr函数

instr函数为字符查找函数，其功能是查找一个字符串在另一个字符串中首次出现的位置。
没有找到，instr函数返回0。 
select instr('asd','c') from dual;//0
select instr('asd','s') from dual;//2

translate函数

translate(m,n,s)：把m中含有n的换成s
select translate('abccc','c','d') from dual;//abddd

lower，大写变小写
select lower(‘SDDaa’) from dual;//sddaa
upper，小写变大写
select upper(‘SDDaa’) from dual;//SDDAA
trim
a) trim(string); –去除字符串首尾的空格
select trim(’ dfd fdd ‘) from dual;//dfdfdd
b) rtrim(string); –去除字符串右侧空格
select rtrim(’ dfd fdd ‘) from dual;// dfd fdd
c) ltrim(string); –去除字符串左侧空格
select ltrim(’ dfd fdd ‘) from dual;dfd fdd
initcap,返回字符串并将字符串的第一个字母变为大写,其他的字母变成小写;
select initcap(‘HELLO,WORLD’) from dual;//Hello,World
select initcap(‘hello,world’) from dual;//Hello,World

to_date

select * from emp where hiredate between to_date('17-12-1980','DD-MM-YYYY') and to_date('18-12-1980','DD-MM-YYYY');
两者之间

日期函数

1. 显示满40年服务年限的员工的姓名和雇佣日期
months_between(m,n)：用于计算m和n之间有几个月
select ename,hiredate from emp where (months_between(sysdate,hiredate)/12)>40;
select months_between(sysdate,'1-1月-2021') from dual;

2.add_mouths
SELECT ADD_MONTHS( TO_DATE( '2005-07-26', 'yyyy-MM-dd' ), 12) FROM dual;
结果：26-7月 -06
SELECT ADD_MONTHS( TO_DATE( '2005-07-26', 'yyyy-MM-dd' ), -1) FROM dual;
结果：26-6月 -05

3.last_day
查询当前月份的最后一天
select last_day(sysdate) from dual;
查询某天所在月份的最后一天：
select last_day(to_date('1992-10-09','yyyy-mm-dd')) from dual;
next_date
select next_day(sysdate,'星期五') from dual;
当前日期的下个星期五的日期

4.current_date
和sysdate一样是当前时间
如果修改当前会话的时区,比如将中国的时区为东八区,修改为东九区,则current_date显示的时间为东九区时间, 根据东加西减的原则,current_date应该比sysdate快一小时.
select current_date,sysdate from dual;

5.extract函数
select extract(day from date'2011-05-17') from dual;//17
select extract(month from date'2011-05-17') from dual;//5

oracle中如何判断某个日期是星期几
SELECT to_char(to_date('2011-03-13','YYYY-MM-DD'),'d') FROM DUAL 
其中 红色部分为传入日期 

to_char的其他用法 
Select to_char(sysdate,'ss') from dual取当前时间秒部分 
Select to_char(sysdate,'mi') from dual取当前时间分钟部分 
Select to_char(sysdate,'HH24') from dual取当前时间秒小时部分 
Select to_char(sysdate,'DD') from dual取当前时间日期部分 
Select to_char(sysdate,'MM') from dual取当前时间月部分 
Select to_char(sysdate,'YYYY') from dual取当前时间年部分 
Select to_char(sysdate,'w') from dual取当前时间是一个月中的第几周(从1日开始算) 
Select to_char(sysdate,'ww') from dual取当前时间是一年中的第几周（从1.1开始算） 
Select to_char(sysdate,'iw') from dual取当前时间是一年中的第几周（按实际日历的） 
Select to_char(sysdate,'d') from dual取当前时间是一周的第几天，从星期天开始，周六结束 
Select to_char(sysdate,'day') from dual　取当前日是星期几，和数据库设置的字符集有关，会输出'Tuesday' 
Select to_char(sysdate,'ddd') from dual 当前日是一年中的第几天

PL SQL语句

set serveroutput on （不加分号）

数据类型

%type

v_name emp.ename%type; 表达新的变量名和emp表中的ename类型一样，记录的是一个字段（每一列）

v_name emp.ename%type:='JAMES';
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=v_name;
if(v_sal>1500) then
update emp set comm=comm+100 where ename=v_name;
elsif v_sal>900 then
update emp set comm=800 where ename=v_name;
else
update emp set comm=comm+400 where ename=v_name;
end if;
end;
select * from emp;

%rowtype

针对表中的某一行
这个类型中select语句只能查询所有的东西*，

自定义复合数据类型

declare
type user_type is record(
vname varchar2(20),
vsal number
);
vempno emp.empno%type:=7499;
userinfo user_type;
begin
select ename,sal into userinfo from emp where empno=vempno;
dbms_output.put_line(userinfo.vname||' '||userinfo.vsal);
end;

条件控制语句（循环，判断）

if…then…end if

declare
result number:=50;
begin
if result<60 then
dbms_output.put_line('不及格,成绩为：'||result);
end if;
end;

if…then…else…end if

declare
result number:=50;
begin
if result<60 then
dbms_output.put_line('不及格,成绩为：'||result);
else
dbms_output.put_line('及格,成绩为：'||result);
end if;
end;

if…then…elsif…end if

declare
result number:=&no;
begin
if result>=90 then
dbms_output.put_line('优秀,成绩为：'||result);
elsif result>=60 then
dbms_output.put_line('及格,成绩为：'||result);
else
dbms_output.put_line('不及格,成绩为：'||result);
end if;
end;

case

declare
result number:=&no;
begin
case
when result>=90 then
dbms_output.put_line('优秀,成绩为：'||result);
when result>=60 then
dbms_output.put_line('及格,成绩为：'||result);
else
dbms_output.put_line('不及格,成绩为：'||result);
end if;
end;

loop… exit when… end loop;

declare
i number:=1;
s number:=0;
begin
loop
s:=s+i;
i:=i+1;
exit when i>10;
end loop;
dbms_output.put_line(s);
end;

while…loop…end loop;

declare
i number:=1;
s number:=0;
begin
while i<11
loop
s:=s+i;
i:=i+1;
end loop;
dbms_output.put_line(s);
end;

for…in…loop…end loop;

declare
i number:=1;
s number:=0;
begin
for i in 1..10
loop
s:=s+i;
i:=i+1;
end loop;
dbms_output.put_line(s);
end;

字符函数

字母转化为ASCII值

set serveroutput on
declare
c1 test.column1%type:=1;
c2 test.column2%type:='a';
c3 test.column3%type:=sysdate;
begin
for i in 1..10
loop
insert into test(column1,column2,column3) values(c1,c2,c3);
--delete from test where column1=i;
c1:=c1+1;
c2:=chr(ascii(c2)+1);
c3:=c3+1;
end loop;
end;
select * from test;

去除空格

declare
name_old varchar2(30):=' sdf dfd ff ';
name1 varchar2(30);
name_new varchar2(30):='';
len number(30);
i number:=1;
begin
name_old:=trim(name_old);//去除两边的空格，空格不占用长度
len:=length(name_old);
while i<len+1
loop
name1:=substr(name_old,i,1);//截取字符name_old,从i开始，截取1个字符
if(length(trim(name1))!=0) then
name_new:=name_new||name1;//连接
end if;
i:=i+1;
end loop;
dbms_output.put_line(name_new);
end;

异常

分为系统异常和自定义异常

异常语法

exception
  when exception_name then 
  statement1
  when exception_name then
  ...
  when others then 一般是最后一条子句，没有捕捉到的都被它捕捉

系统异常

预定义异常

oracle已经预先定义好名称的异常，有对应的错误编号和名字

非预定义异常

在数据库中没有定义异常名称的异常
通常是数据库错误，只有错误编号，没有错误的异常名称，所以不能直接捕捉到
使用时，必须先声明一个异常类型的名称，然后通过pragma exception_inin语句为该异常关联错误编号
步骤：
定义一个异常名称
使用伪过程将异常名称和错误编号关联
pragma
exception_init(exception_name,oracle_error_number)
在异常处理部分捕获异常并对异常情况做出相应的处理

自定义异常

异常定义
  在声明部分采用exception关键字声明异常：
   myexception exception
异常引发
   使用raise关键字引发
   raise myexception

DECLARE
  myexception EXCEPTION;
  v_sal emp.sal% TYPE;
BEGIN
  select sal into v_sal from emp where ename='JAMES';
  IF v_sal<5000 THEN
    raise myexception;
  end if;
  dbms_output.put_line('JAMES的工资是：'||v_sal);
EXCEPTION
  when myexception then
    dbms_output.put_line('工资太少了');
  when no_data_found THEN
    dbms_output.put_line('没有这个员工');
END;

游标

游标使用步骤

declare
声明游标: cursor 游标名 is select 语句;
cursor nycursor is select * from emp;
 my_emp emp%rowtype;
begin
打开游标：声明的游标必须打开后才能使用 open 游标名称;--相当于执行查询语句
open mycursor;
使用游标：一般在循环语句中使用fetch语句提取游标中的记录来进行操作
fetch mycursor into my_emp;
  dbms_output.put_line(my_emp.ename||' '||my_emp.sal);
关闭游标：游标使用完毕后，一定要关闭 close 游标名称; 
close mycursor;
end;

创建存储过程

异常的那几个东西

创建：查询指定员工的姓名和薪水，并利用out模式参数值传给调用者。
create or replace procedure asd(eno emp.empno%type,pname out emp.ename%type,psal out emp.sal%type)
as
begin
select ename,sal into pname,psal from emp where empno=eno;
dbms_output.put_line('姓名'||pname||'工资'||psal);
exception
when no_data_found then
dbms_output.put_line('没有');
end;

declare
vno emp.empno%type:=7369;
vname emp.ename%type;
vsal emp.sal%type;
begin
asd(vno,vname,vsal);
asd(eno=>vno,pname=>vname,psal=>vsal);
end;

创建一个存储过程，根据提供的雇员姓名，查询该雇员的上级领导人的姓名，并返回。
create or replace procedure asd(eno emp.empno%type,pname out emp.ename%type)
as
pmgr emp.mgr%type;
begin
select mgr into pmgr from emp where empno=eno;
select ename into pname from emp where empno=pmgr;
end;
declare
vno emp.empno%type;
vname emp.ename%type;
begin
asd(7369,vname);
dbms_output.put_line(vname);
end;

in out

create or replace procedure asd(n1 in out number,n2 in out number)
as
t number;
begin
t:=n1;
n1:=n2;
n2:=t;
end;
set serveroutput on
declare
n1 number:=10;
n2 number:=20;
begin
asd(n1,n2);
dbms_output.put_line(n1||' '||n2);
end;

窗口命令变量绑定调用

var v_name varchar2(20);
var v_sal number;
exec asd(7369,:v_name,:v_sal);

调用

print v_name v_sal;
select :v_name,:v_sal from dual;

游标和存储空间操作

请使用pl/sql编写一个过程，可以输入部门号，并显示该部门所有员工姓名和他的工资.

用游标实现

create or replace procedure pro4(v_deptno number)
is
cursor emp_cursor is select ename,sal from emp where deptno=v_deptno;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  open emp_cursor;
  loop
  fetch emp_cursor into v_ename,v_sal;
  exit when emp_cursor%notfound;
  dbms_output.put_line('姓名：'||v_ename||'工资:'||v_sal);
  end loop;
  close emp_cursor;
  end;
begin
pro4(10);
end;

写一个函数

create or replace function asdff(eno emp.empno%type)
return varchar2
as
vname emp.ename%type;
begin
select ename into vname from emp where empno=eno;
return vname;
end;
begin
dbms_output.put_line(asdff(7369));
end;

表空间及其操作

默认的临时表空间是temp
默认的永久表空间是system
更改默认临时表空间
alter database default temprory tablespace
更改默认永久表空间
alter datebase default tablespace

查看数据字典的结构信息

desc dba_tablespaces;

查看默认创建的表空间的信息，通过视图查看

select tablespace_name,contents,status from dba_tablespaces;

显示表空间所包含的数据文件

select file_name,bytes from dba_data_files where tablespace_name=‘SYSTEM’;

查询视图

表管理

CDB和PDB

CDB：容器数据库
由ROOT SEED PDBS组成
ROOT存储oracle提供的metadata和common user
CDB与ROOT之间的关系为一对一；
SEED用来创建新的PDB的模板，一个CDB只能有一个SEED
PDBS普通数据库
一个CDB 只能有一个根
PDB：可插拔数据库
默认的连接模式是CDB
进入PDB
alter session set container=pdborcl;
startup;
显示连接容器
show con_name
在PDB中查看相关信息
1）看看有哪些数据文件

今天的文章oracle数据库开发技术分享到此就结束了，感谢您的阅读。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
如需转载请保留出处：http://bianchenghao.cn/63737.html

oracle数据库开发技术

修改表名？、？？？？？？？？？？

创建用户，授予权限

显示用户是谁

查看scott用户下的表信息

Oracle用户锁定/解锁（DBA用户执行）

新建角色

权限

查看权限

授予权限

撤销权限

表空间

创建表空间

创建临时表空间

修改表空间

删除表空间

把表放到表空间中去

表与表结构操作（重点）

新建表

删除表

查看表结构

使用命令方式操作表（增删改查）

约束

约束的语法

创建约束的语法

唯一约束 unique

主键约束 primary key

非空约束 not null

默认值约束

外键约束

检查约束

禁用约束，启用约束

在数据字典中查询约束

索引

概念：

创建方式

查看，修改，删除索引

序列

概念：

创建序列，直接创建复制

删除序列

使用序列

修改序列

同义词 synonym

数据类型

体系结构

逻辑结构（介绍了一些表空间）

数据字典

数据库的查询和视图

查询语句等操作

基本查询

查询显示的方式

连接查询

增删改查语句

排序

聚合函数

分组函数（group by）

having子句

连接

rownum获取数据表前若干行

日期类

视图

创建视图

重命名视图

修改视图

删除视图

函数

数值函数

trunk 函数

字符函数

to_date

日期函数

PL SQL语句

数据类型

%type

%rowtype

自定义复合数据类型

条件控制语句（循环，判断）

if…then…end if

if…then…else…end if