************
day01
************
一.Oracle数据库:
DB/DBMS
数据库的使用者:
DBA(Database Administrator)/开发人员
数据库的种类:
oracle/sybase/db2(IBM)
sql server/mysql
access
-------------------------------
Oracle数据库的用户:
系统用户:
sys: 权限最大
system: sys的副手
测试用户:
scott/tiger(密码)
-------------------------------
普通用户:
openlab/open123;
Liwenhua/Liwenhua;
-------------------------------
数据表: 行和列组成
行: row/record
列: column/field
SQL分类:
DQL(数据查询语言): select
DML(数据操纵语言): insert/update/delete
DDL(数据定义语言): create/drop/alter/
truncate(清除表所有内容,自动提交事务)
DCL(数据控制语言): grant/revoke
TCl(事务控制语言): commit/rollback/savepoint
远程登录到Oracle数据库服务器的方法:
C:>telnet 192.168.0.26
用户名/密码: openlab/open123
这组是远程机器的用户名和密码
远程登录后的连接数据库的方式: sqlplus
sqlplus是Oracle数据库提供的命令行工具
sunv210% sqlplus openlab/open123
这组是数据库的用户名和密码
了解常用的数据表
emp: 职员表
dept: 部门表
salgrage: 薪水等级表
desc命令: 查看一个表的结构
desc emp
desc dept
数据类型:
数字类型: number(p,s) 最长p位，小数点后s位
字符类型: char(n) 定长字符串 / varchar2(n);
| |
节省查询时间 节省存储空间
日期: date
number(7,2) 99999.99
number(5) 99999
number 随便
host dir
host cls : 执行本机操作系统的命令
二. SQL
-- 单行注释
*号表示查询所用的列
nvl函数: 处理空值
distinct: 去除重复的值 只能紧跟select的后面
字符串连接符号: ||
between ... and ...:
sal between low and high; --闭区间
sal >= low and sal <=high;
[low,high]
绑定变量符号&:
select ename,deptno,job
from emp
where deptno = &no;
select ename,deptno,job
from emp
where sal > &salary;
数据是大小写敏感的:
select ename, job from emp
where job = 'MANAGER';
in:
select ename, job deptno
from emp
where job in ('ANALYST','MANAGER');
通配符的种类:
%: 表示0或多个字符
_: 表示任意的单个字符
select ename, job from emp
where ename like '__A%';
select ename, sal deptno
from emp
where ename not like '_A%'
like模糊匹配中的特殊字符的处理:
select ename from emp
where ename like 'A\_%' escape '\';
查询过程中的优先级的问题:
select ename, sal, deptno
from emp
where sal >= 1000
and deptno = 10
or deptno =20;
select ename, sal, deptno
from emp
where sal >= 1000
and (deptno = 10
or deptno = 20);
排序:
select ename, job from emp
order by ename asc; --正序
select ename, sal from emp
order by sal desc; --倒序
对多个字段进行排序:
select ename, sal deptno
from emp
order by deptno, sal desc;
按日期排序:
select ename, hiredate from emp
order by hiredate;
2指select后面的第二个字段 即: sal
select ename, sal, deptno
from emp
order by 2;
单行函数的使用:
虚表dual(Oracle特有的)
字符函数:
upper/lower/concat/substr/trim
length/lpad/rpad/initcap/replace
select length('hello') from dual;
select substr('hello',3,2) from dual;
select upper('Sql Course') from dual;
select lower('Sql Course') from dual;
select initcap('sql Course') from dual;
select lpad('hello',10,'#') from dual;
select rpad('hello',10,'#') from dual;
不知道大小写时，可以进行转换后再查询:
select * from emp
where upper(ename) = 'SCOTT';
select * from emp
where lower(ename) = 'scott';
数字函数:
round/trunc/mod
select round(45.678,2) from dual;
select round(45.678,0) from dual;
select round(45.678,-1) from dual;
select trunc(45.678,2) from dual;
select trunc(45.678,0) from dual;
select trunc(45.678,-1) from dual;
select mod(16,5) from dual;
日期计算:
日期 +- n = n天以后/前
Oracle取系统时间: sysdate(Oracle独有的)
select sysdate from dual;
select ename,round( sysdate - hiredate )/365
as date1
from emp；
select to_char(sysdate,'yyyy-mm-dd
hh24:mi:ss') from dual;
select to_char(sysdate,
'year month dd day d hh12 am')
from dual；
select to_char(sysdate,
'yyyy"年"mm"月"dd"日"')
from dual;
select sysdate from dual;
默认的日期格式是: DD-Mon-RR
假设现在是2011年:
RR YY(直接取当前日期的前两位)
05 2005 2005
99 1999 2099 <--
假设现在是1998年:
RR YY
05 2005 1905 <--
99 1995 1995
日期格式:
yyyy/year/mm/dd/day//hh24/mi/ss/d/day/month
************
day02
************
日期函数:
sysdate:
months_between:
select ename,months_between(sysdate,hiredate) as hiredate
from emp;
add_months: 当前时间的前几月或后几月
select add_months(sysdate,6) from dual;
select add_months(sysdate,-6) from dual;
next_day: 下一个周几
--下一个周四
select next_day(sysdate,5) from dual;
--下一个周五
select next_day(sysdate,'FRI') from dual;
--下一个周五
select next_day(sysdate,'星期五') from dual;
--英文环境下用“FRI”
--中文环境下用“星期五”
--但数字通用.
把会话环境改为简体中文:
alter session set nls_langage = 'SIMPLIFIED CHINESC';
把会话环境改为英文:
alter session set nls_langage = 'AMERICAN';
last_day: 本月的最后一天
select last_day(sysdate) from dual;
转换函数:
to_number: 字符串转换成数字
select to_number('12')*12 from dual;
--第一个参数转换成第二个参数的格式
select to_number('$12,345.67','$99,999.99')
from dual;
9: 代表数字位
0: 定义宽度大
于实际值宽度时，0会被强制显示在前面
以补齐位数
$: 美元的货币符号
L: 本地的货币符号
.: 小数点
,: 每千位显示一个逗号
如果显示位数不足(定义宽度小于实际值宽度时)
用#代替
--把一个字符串转换成十六进制的值
select to_number('ff','xx') from dual；
to_char: 数字或日期转换成字符串
select to_char(12345.67,'$000,000.000')
from dual;
--常用格式
select to_char(12345,'$999,999.00') from dual;
to_date: 把一个字符串转成日期类型
insert into emp(empno,ename,hiredate)
values(1234,'Liwenhua',
to_date('2011-2-14','yyyy-mm-dd'));
通用函数:
nvl: 第一个参数和第二个参数的类型要相同
select ename, sal, comm, nvl(comm,100) bonus
from emp;
select ename, nvl(job,'not job yet') job
from emp;
select ename, nvl(hiredate,
to_date('2011-2-1','yyyy-mm-dd')) hiredate
from emp;
select cust_id ,cname,
nvl(to_char(birthday),'not availabe'),
nvl(to_char(account),'no account')
from customer;
coalesce: 返回第一个非空值
select ename, sal, comm,
coalesce(comm,sal,100) bonus
from emp;
decode: 相当于if—else语句
select ename, job, sal,
decode(job,'ASLESMAN',1.05*sal,
'ANALYST',1.1*sal,
'MANAGER',1,2*sal,sal) bonus
from emp;
----------------------------------------------------
多表查询:
查找叫smith的人所在的部门名称？
pk/fk 约束条件，用来保证数据的完整性
pk: primary key 主键
fk: foreign key 外键
外键用来表示表间关系.一对多.
等值连接:
select t1.ename, t2.dname
from t1 join t2
on t1.deptno = t2.deptno;
例如:
select emp.ename, dept.dname
from emp join dept
on emp.deptno = dept.deptno;
如果表的名字太长，可以起别名:
select e.ename, d.dname
from emp e join dept d
on e.deptno = d.deptno;
select e.ename, d.dname
from emp e join dept d
on e.deptno = d.deptno
where e.ename = 'SMITH';
dept: 主键被参照，主表，父表
emp : 外键参照dept表，从表，字表
select e.ename, e.job, e.sal, d.dname
from emp e join dept d
on e.deptno = d.deptno
and e.job = 'MANAGER';
非等值连接:
select e.ename, e.sal, s.grade, s.losal, s.hisal
from emp e join salgrade s
on e.sal between s.losal and s.hisal;
自连接: 此时表必须取别名 否则无法实现自连接
worker: 普通职员表
manager: 经理表
select worker.ename, manager.ename
from emp worker join emp manager
on worker.mgr = manager.empno;
内连接: 等值连接/非等值连接/自连接/
一张表里的记录一定在另一张表中存在匹配的
记录，否则不能出现在结果集中
外连接:
select t1.c2, t1.c2, t2.c1, t2.c2
from t1 [left|right|full outer]
join t2
on t1.c1 = t2.c2;
例如:
select e.ename, d.dname
from emp e left outer join dep
t d
on e.deptno = d.deptno;
select worker.ename, manager.ename
from emp worker left outer join emp manager
on worker.mgr = manager.empno;
--没有下属的员工(不是被人的领导)
--外连接+匹配表pk is null 表示否定
select worker.ename, manager.ename
from emp worker right outer join emp manager
on worker.mgr = manager.empno
where worker.empno is null;
--那些部门没有叫smith的员工
--1)用ename='SMITH'过滤emp表
--2)外连接 dept做驱动表
--3)用匹配表的pk is null 过滤掉内连接的结果集
select d.dname
from emp e right outer join dept d
on e.deptno = d.deptno
and e.ename = 'SMITH'
where e.empno is null;
注意:
t1 left outer join t2
等价于:
t2 right outer join t1
t1 right outer join t2
等价于:
t2 left outer join t1
全外连接:
t1 full outer join t2;
例如:
select e.ename, d.dname
from emp e full outer join dept d
on e.deptno = d.deptno;
总结:
内连接: 等值连接/非等值连接/自连接
外连接: 左/右/全外连接
-------------------------------------------------
组函数:
max / min / avg / sum / count
--avg/sum 操作数字
--max/min 可以操作各种数据类型
--组函数是忽略空值的
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;
select count(sal) from emp;
select max(hiredate) from emp;
select min(job) from emp;
select count(*) from emp;
select avg(nvl(comm,0)) from emp;
--计算员工分布在几个部门
select count(distinct deptno) from emp;
--按部门分组，计算每个部门的薪水总和
select deptno ,sum(sal)
from emp
group by deptno;
--按职位分组，计算每个职位的平均薪水
select job, avg(sal)
from emp
group by job
order by avg(sal) desc;
--在 select 后面，所有的分组函数的列必须出现
--在 group by 的后面 否则会出错
select job, avg(sal) from emp;//错误
--出现在 group by 后面的列不一定出现在
--select 的后面 但是信息部完整
select avg(sal) from emp
group by job;
结论: select 后面的分组函数的列和 group by
后面的列名要保持一致
--获得每个部门每个职位的平均薪水
select deptno, job, avg(sal)
from emp
group by deptno, job
order by deptno, job desc;
having: 对组函数的结果进行过滤
select deptno, avg(sal)
from emp
where
group by deptno
having avg(sal) > 2000;
where -> group by -> having
select deptno, count(*)
from emp
group by deptno
having count(*) >= 5
order by count(*) asc;
************
day03
************
子查询: 子查询就是在一条 SQL 语句中嵌入 SELECT 语句
非关联子查询:
select ename from emp
where sal > (
select sal from emp
where ename = 'FORD' );
select ename, job, sal
from emp
where sal = ( select mi
n(sal) from emp );
--如果子查询返回一个数据，
--可以使用 =，>, <, >=, !=, <=, <>
--这些单行比较运算符.
--如果子查询返回的多行数据，
必须使用多行比较运算符: in
select ename, job
from emp
where deptno in (
select deptno from emp
where job = 'CLERK' );
--如果子查询返回结果中有null值，且使用 not in
--运算符，则无结果
例如:
select ename from emp
where empno not in (
select mgr from emp );
--整个emp表中薪水最高的人
select ename, sal
from emp
where sal = ( select max(sal) from emp );
--每个部门薪水最高的人
--注意 where 后面的字段要和子查询 select 后面的
--字段匹配
select deptno, ename, sal
from emp
where (deptno, sal) in (
select deptno, max(sal)
from emp
group by deptno );
--那个部门的平均薪水比部门20的平均薪水高
select deptno,avg(sal)
from emp
group by deptno
having avg(sal) > (
select avg(sal) from emp
where deptno = 20 );
关联子查询: 循环执行方式()
--那些员工的薪水比本部门的平均薪水高
select ename, sal, deptno
from emp outer
where sal > (
select avg(sal) from emp
where emp.deptno = outer.deptno );
--当子查询的where条件成立时,exists 返回 true
--'X' 可以是任意值 exists 是判断符
select ename, job, deptno
from emp outer
where exists (
select 'X' from emp
where mgr = outer.empno );
--查询没有员工的部门
select deptno, dname
from dept outer
where not exists (
select 'x' from emp
where deptno = outer.deptno );
集合操作: 要求两个 select 语句是同构的，即列的个数
和数据类型必须一致
A = {1,2,3,4,5,6}；
B = {2,4,6,8}
A union B = {1,2,3,4,5,6,8};
A union all B = {1,2,2,3,4,4,5,6,6,8};
A intersect B = {2,4,6};
A minus B = {1,3,5};
--union all
select worker.ename, manager.ename
from emp worker join emp manager
on worker.mgr = manager.empno
union all
select ename, 'boss'
from emp
where mgr is null;
--将sql中的union换成
--union all/intersect/minus 查看结果
select job from emp where deptno = 10
union
select job from emp where deptno = 20;
--------------------------------------------------
约束条件:
主键: primary key , PK = unique + not null
外键: foreign hey , FK
唯一: unique
非空: not null
检查: check
F: female / M: male
建表:
create table student_li(
id number primary key,
name varchar2(20),
email char(40),
registtime date default sysdate，
sex char --默认的为一个字符
);
--插入一条记录
insert into student_li
(id,name,email,registtime)
values
(1,'Liwenhua','Liwh@https://www.doczj.com/doc/3a11224073.html,',sysdate);
--如果新增全部字段，可以省略字段名称
insert into student_li
values
(2,'Zhangziyi','Zhangzy@https://www.doczj.com/doc/3a11224073.html,',sysdate);
--如果增加
的不是全部字段,必须提供字段列表
--而且要一一对应
insert into student_li (id,name)
values (3,'Zhaoyun');
--一次增加多条记录
--查询返回的一结果集合
insert into student_li (id, name)
select empno, ename from emp
where deptno = 10;
在sqlplus中调整格式,以列宽为例
--特别强调: sqlplus命令，非sql语句
--将指定列名调整为10个字符长度
column 列名 format a10
例如:
column name format a10
column 数字列名 format 9999
--可以简写
column sal for 9999
column ename for a8
column job for a9
column mgr for 999
col comm for 9999
col empno for 999999
--创建约束条件:
--如果创建的约束条件没有命名，
--由系统自动命名的
--1.创建主键约束:
--1)建表时创建，可以建立在列级或表级
--1)建表后创建，建立在表级
例1:
create table student_li(
id number primary key,
name varchar2(20),
email char(40),
registtime date default sysdate
);
select constraint_name, constraint_type
from user_constraints
where table_name = 'STUDENT_LI';
例2:
--创建主键约束，自己命名，建立在表级
--约束条件命名为: stuli1_id_pk
create table student_li1 (
id number,
name varchar2(20),
constraint stuli1_id_pk primary key(id)
);
select constraint_name, constraint_type
from user_constraints
where table_name = 'STUDENT_LI1';
例3:
--建表后创建主键约束
--约束条件名称自定义(stuli2_id_pk) 最好有意义
create table student_li2 (
id number,
name varchar2(20)
);
alter table student_li2
add constraint stuli2_id_pk primary key(id);
--了解主键约束条件的作用
insert into student_li(id,name)
values (10,'peter');
--重复执行上一条语句，试图插入重复的值10，
--将返回ORA-00001违反唯一约束
--不提供主键，将返回“id不能为空”错误
intsert into student_li (name)
values ('peter');
--删除表
drop table student_li;
--创建非空约束: 只能建立在列级
create table student_li (
id number primary key,
name varchar2(20) not null );
--要求此列上必须有值. 错误的sql
insert into student_li (id)
values (1);
--创建唯一约束
--email的唯一约束建立在列级
--nickname唯一约束建立在表级
create table student_li (
id number primary key,
name varchar2(20),
nickname varchar2(20),
email char(30) unique,
constraint stuli_nickname_uk unique(nickname)
);
select d.dname, d.loc ,count(e.sal), avg(e.sal)
from emp e join dept d
on e.deptno = d.deptno
group by d.dname, d.loc;
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) = (
select max(avg(sal)) from emp
group by deptno );
************
day04
************
外键:
--主表(父表) 被参照的表
create table major_li(
mid number(2) primary key,
mname va
rchar2(30)
);
--从表(子表) 参照别的表
create table student_li(
id number(5) primary key,
name varchar2(20),
mid number(2),
constraint stuli_mid_fk foreign key (mid)
references major_li (mid)
);
insert into major_li
values(1,'computer');
insert into major_li
values(2,'music');
insert into major_li
values(3,'movie');
insert into student_li
values(101,'Liwenhua',1);
insert into student_li
values(102,'Huazi',3);
insert into student_li
values(104,'Huangxiaoming',4);
select https://www.doczj.com/doc/3a11224073.html,, m.mname
from student_li s join major_li m
on s.mid = m.mid
where https://www.doczj.com/doc/3a11224073.html, = 'Liwenhua';
--试图增加一个学生记录，指定一个不存在专业
--编码10
insert into student_li
values(105,'Rich',10);
--报错: ORA-02291: 违反完整约束条件
(LIWENHUA.STULI_MID_FK) - 未找到父项关键字
--试图删除major_li表中的1专业，这个专业被
--某些学生选修(被参照)，此时删除不成功
delete from major_li
where mid = 1;
--报错: ORA-02292: 违反完整约束条件
(LIWENHUA.STULI_MID_FK) - 已找到子记录
--在建立子表时,外键约束增加设定条件
--delete cascade
create table student_li(
id number(5) primary key,
name varchar2(20),
mid number(2),
constraint stuli_mid_fk foreign key (mid)
references major_li (mid) on
delete cascade );
--on delete set null
create table student_li(
id number(5) primary key,
name varchar2(20),
mid number(2),
constraint stuli_mid_fk foreign key (mid)
references major_li (mid) on
delete set null );
delete from major_li
where mid = 1;
insert:
insert into dept(deptno,dname,loc)
values(80,'market','xian');
create table mydept (
id number(2) primary key,
name varchar2(20),
location varchar2(20)
);
insert into dept(id,name,location)
select deptno,dname,loc from dept;
create table emp_li(
id number(4) primary key,
name varchar2(20),
deptno number(20)
);
--新增一条记录
insert into emp_li
values(1234,'Liwenhua',10);
--利用子查询,新增多条记录
insert into emp_li emp_li(id,name,deptno)
select empno ,ename, deptno
from emp
where deptno = 10;
--复制表: 复制数据和结构
--创建表的同时复制数据
create table emp_dup
as
select * from emp;
--复制表结构,不复制数据
create table emp_dup
as
select * from emp
where empno > 9999;
--复制表结构，并按条件复制部分数据
create table emp_dup
as
select * from emp
where empno > 2000;
update:
update emp_dup set sal = 1000
where empno = 7369;
update emp_dup set sal = 1500,
deptno = 20, job = 'salesman'
where empno = 7369;
delete:
--注意删除的条件
delete emp_li where deptno = 10;
事务(Transaction):
insert
update
create/drop table --隐式的提交事务
insert
update
commit/rollback --显
式的提交事务
--事务提交后，该事物就会终止
事务的特性:
原子性: 一个事务要么完全发生，要么完全不发生
一致性: 事务把数据库从一个一致状态转变到另一个
状态
隔离性: 在事务提交之前，其它事务觉察不到
事务的影响
持久性: 一旦事务提交，它是永久的
回滚段: 保存临时的旧数据
savepoint A;
savepoint B;
savepoint C;
rollback to C;
rollback to A;
create table temp_li (
id number primary key );
insert into temp_li values(1);
savepoint A;
insert into temp_li values(2);
savepoint B;
insert into temp_li values(3);
savepoint C;
insert into temp_li values(4);
rollback to B;
rollback to C; --检查结果
rollback to A;
------------------------------------------------
数据库的主要对象
表 table
视图 view
索引 index
序列 sequence
约束条件
同义词
...
视图: 视图实际上一个虚表,里面本身没有存储数据，
数据来自于基表
--视图的本质 --> sql查询语句
--创建视图
create view v_emp_li --是基表数据的一个投影
as
select ename, sal from emp; --emp是基表
--创建或替换视图
create or replace view v_emp_li
as
select ename, sal from emp;
--和查询表一样使用
select * from v_emp_li;
--删除视图
drop view v_emp_li;
数据字典:
--用户名下的数据表: user_tables
select count(*) from user user_tables;
select table_name from user_tables
where rownum < 10; --查看前9个表明
select * from user_table
where table_name = 'EMP'; --查看emp表的信息
--和视图相关的数据字典 user_views
--查看视图总数
select count(*) from user_views;
--查看数据字典的结构
desc user_views;
select text from user_views
where view_name = 'V_EMP_LI';
--复杂视图
create or replace view emp_sum
as
--此处的别名 sum_sal 必须有
select deptno, sum(sal) sum_sal
from emp
group by deptno;
--user_constraints
数据字典:
user_tables: 当前用户名下的所有的表
all_tables: 当前用户能访问的所有的表
自己的表+别的用户允许自己访问的表
dba_tables: 数据库下所有的表
select count(*) from user_objects;
select count(*) from user_tables;
select count(*) from all_objects;
select count(*) from all_tables;
索引: 提高查询效率 经常查询的字段建议建立索引
但数据更新频繁就不适合建立索引
全表扫描FTS( Full Table Scan):
select empno, ename, rowid
from liwenhua.emp;
创建索引的方式: 自动/手动
当创建唯一/pk约束条件时，索引自动创建
create index emp_ename_idx
on emp_li(ename);
索引失效:
1.函数导致索引用不了
where upper(first_name) = 'CARMEN';
2.表达式导致索引用不了
where sa
lary*12 = 12000;
3.部分隐式数据类型导致索引永不了
where c1 = 2; (c1为varchar2类型)
4.Like
where first_name like 'CA%';
5.否定形式导致索用不了
where first_name <> 'CARMEN';
where salary not between 1000 and 2000;
where dept_id not in (32,34,42);
6.Is null导致索引用不了
where comm is null;
create table student (
id number(8) primary key, --SYS_C1234
);
alter table student drop constraint SYS_C1234;
alter table student add constraint
sys_c1234 primary key (id);
create table student(
id number(8),
name varchar2(20) not null,
email varchar2(30),
sex char,
mid number(3),
constraint stu_id_pk primary key(id),
constraint stu_id_uk unique(email),
constraint stu_id_ck
check(sex in ('F','M')),
constraint stu_mid_fk foreign key(mid)
references major(id) );
--做一些测试
alter table student drop
constraint student_mid_fk;
--举例一个完整的数据库脚本
--从此处开始复制，存成 xxx.sql 脚本文件
--1.先删除所有约束条件
alter table xxx drop constraint ...
--2.删除序列
drop squence xxx
--3.删除视图
drop view xxx
drop view aaa ...
--4.删除表
drop view xxx ...
--5.创建表
create table xxx ...
--6.创建视图
create or replace view ...
--7.建立序列
create sequence ...
--8.创建约束条件
alter table student add constraint ...
--9.增加基础数据
insert into major values (101,'computer');
--执行脚本文件
@ c:\script.sql
************
day05
************
--sqlplus命令
desc employee
column name format a20
--sql语句
select * from employee;
--行内视图: 查询语句出现在from后面
--匿名视图
select ename, sal
from emp e join (
select deptno, avg(sal) avgsal from emp
group by deptno) a
on e.deptno = a.deptno and e.sal > a.avgsal;
--子查询: 查询语句出现在条件中
select ename，deptno
from emp
where sal > ( select sal from emp
where ename = 'SCOTT' );
伪列: rownum rowid 是一个伪列，对查询返回的行编号，
由1开始一次递增 (先获取数据然后再编号)
--第m条到第n条记录的获取方式
--这种方式仅适用于oracle数据库
select ename, sal
from ( select ename, sal, rownum rn from emp)
where rn <= 10 and rn >= 5;
--where rn between 5 and 10;
--排名问题 Top-N分析
--薪水最高的三个人
select ename, sal
from ( select * from emp
where sal is not null
order by sal desc)
where rownum <= 3;
序列: Sequence
--一种数据库对象，只要用于生成主键值
create sequence myseq_li;
--序列的两个伪列: nextval ,currval
-- nextval 获取序列的下一个值
--序列建立好后必须先执行nextval激活后
--才能获取序列的当前值 currval
-- currval 获取序列的当前值
select myseq_li.nextval from du
al;
create table mytemp_li (
id number primary key);
insert into mytemp_li
values(myseq_li.nextval);
select * from mytemp_li;
select myseq_li.currval from dual;
--创建序列，起点是1000，步进是10
create sequence mysequ_li
start with 1000
increment by 10;
--修改步进为2
alter sequence mysequ_li increment by 2;
--序列与表都是独立的对象
--------------------------------------------------
PL/SQL编程:
--匿名块/函数/过程/包/触发器
--打开输出(sqlplus的命令): set serveroutput on
[declare --声明段
... ]
begin
...
[exception
...]
end;
begin
dbms_output.put_line('Hello World!');
end;
declare
v_count number := 0;
begin
select count(*) into v_count
from emp;
dbms_output.put_line('一共有'||v_count||'条');
end;
/ --执行
declare
v_sal number := 0;
begin
select sal into v_sal
from emp where ename = 'SCOTT';
dbms_output.put_line('sal is '||v_sal);
exception
when too_many_rows then
dbms_output.put_line('too many rows!');
when no_data_found then
dbms_output.put_line('no data!');
when others then
dbms_output.put_line('others!');
end;
1--1000 0%
1001--2000 1%
2001--3000 2%
3001--4000 3%
4001--5000 4%
5001--... 5%
--函数
create or replace function tax_li(v_sal number)
return number
as --is
v_result number := 0;
begin
if(v_sal < 1000) then
v_result := 0;
elsif(v_sal < 2000) then
v_result := v_sal*0.01;
elsif(v_sal < 3000) then
v_result := v_sal*0.02;
else
v_result := v_sal*0.04;
end if;
return v_result;
end;
--使用自定义的函数
select ename, sal, tax_li(sal)
from emp;
select tax_li(10000) from dual;
--函数出入参数: deptno, 输出参数: 部门人数
create or replace function emp_count(v_deptno number)
return number
is
v_count number;
begin
select count(*) into v_count
from emp where deptno = v_deptno;
return v_count;
end;
--测试
select emp_count(10) from dual;
--删除函数
drop function emp_count;
--过程: 可以有输出或输出参数
--函数: 必须返回数据,在sql语句中生效
--过程: 可以不返回数据,可以独立调用
create or replace procedure myproc(
v_deptno emp.deptno%type)
is
v_count number;
begin
select count(*) into v_count
from emp where deptno = v_deptno;
dbms_output.put_line(v_count);
end;
--测试过程的方式
exec myproc(20);
--有输出参数的过程
create or replace procedure calcu_emp(
v_deptno in number(2),
v_sum_sal out emp.sal%type,
v_avg_sal out emp.sal%type)
is
begin
select sum(sal), avg(sal)
into v_sum_sal, v_avg_sal
from emp
where deptno = v_deptno;
end;
--测试有输出参数的过程
declare
v_sum number;
v_avg number;
begin
--注意: 输出参数必须是变量
calcu_emp(10,v_sum,v_avg);
dbms_output.put_line(v_sum);
dbms_output.put_line(v_avg);
end;
--功能: 修改
员工的薪水
--输入参数: 员工编码，新的薪水值
--如果员工的职位不是MANAGER或者PAESIDENT
且薪水高于15000，则报错.
--否则,修改指定员工的薪水为指定值
create or replace procedure changesal(
v_empno emp.empno%type,
v_sal emp.sal%type)
is
v_job emp.job%type;
begin
select job into v_job
from emp where empno = v_empno;
if(v_job not in('MANAGER','PRESIDENT')
and v_sal >15000) then
dbms_output.put_line('too many sal!');
else
update emp set sal = v_sal
where empno = v_empno;
commit;
end if;
exception
when others then
dbms_output.put_line('some error!!!');
end;
--测试方法
exec changesal(7500,20000);
--删除过程
drop procedure changesal;