oracle-----甲骨文
oracle,mysql -----oracle 48.6
db2 -----IBM 22
sqlserver -----mic 15
数据库开发
SQL ------3
PLSQL------1.5
PROC/C++---1.5
DMS ------3
ping 172.18.9.6
数据存储:1T--1024G------4G
早期的数据存储 -----文件
海量级------查找速度
程序帮你读取 删除 增加(管理)----数据库管理系统---DBMS 数据存储在文件-----DB
数据库文件的格式：
名字 员工号 年龄 性别 ----表头
tom 001 20 nan ----行(row) -----记录
列(字段)（column）
二维表 ---------关系型数据库
数据库对应的最小单位是二维表
二维表对应的最小存储单元 ----row
一条记录的最小存储单元------column
程序访问数据库-----oracle提供的sqlplus
远程登录服务器
telnet IP(172.18.9.6)
telnet 192.168.0.26
login: openlab
passwd:open123
bash$
sqlplus openlab/open123
sqlplus scott/tiger
SQL>
!clear
clear screen
SQL>desc 表名
SQL>desc s_emp
Name Null? Type
----------------------------------------- -------- ------------- ID (在表中数据的唯一编号) NOT NULL NUMBER(7) 1 56
LAST_NAME（姓） NOT NULL VARCHAR2(25) FIRST_NAME(名) VARCHAR2(25) USERID（员工号） VARCHAR2(8) START_DATE (入职日期) DATE
COMMENTS (说明) VARCHAR2(255) MANAGER_ID(领导id) NUMBER(7) TITLE（职位） VARCHAR2(60) DEPT_ID（部门编号） NUMBER(7) SALARY（工资） NUMBER(11,2) （提成） NUMBER(4,2)
SQL>spool 文件名 ----退出之后不在存储到文件
如果想继续存入敲入的命令和结果-----SQL>spool 文件名
number ---数字类型
varchar2 ---字符串类型
date ---日期类型
sql语句
A select(查询 选择)
select {*，字段名,[字段名，字段名]} from 表名;
{} ----选择
[] ----可选
select * from 表名;
select id from s_emp;
select id,last_name,salary from s_emp;
a/i/o/O
ZZ -----保存退出
ppt summit2.sql
搭建环境
1.把summit2.sql 传到服务器
2.telnet 到服务器
3.sqlplus 之后运行文件summit2.sql
select [distinct] {*，字段名[alias],[字段名，字段名]} from distinct -----排重
把所有的职位查询出来
select title from s_emp;
select distinct title from s_emp;
alias ---- 别名
select title zhiwei from s_emp;
默认显示成大写 ------在别名上加双引号------小写显示 select title "zhiwei" from s_emp;
2 56 表名;
c中连接字符串
数据库中连接字符串 ----- ||
把所有人的姓名拼接之后显示
select last_name, first_name from s_emp;---error select last_name||first_name from s_emp;
+ - * /
把每个人的年薪统计出来
select salary from s_emp;
select salary*12 from s_emp;
大家把每个人的提成查询出来
select COMMISSION_PCT from s_emp;
空值
NULL--------c (void*)0
NULL--------未知值 不同于空格 空字符串
任何值和NULL做运算得到的是NULL
把提成加入到年薪的计算
select first_name,salary*12 from s_emp; //logic error oracle 提供一个函数nvl-----空值处理函数
nvl(p1,p2);
要求p1和p2类型必须一致
p1是要处理的数据 p2是当p1是NULL函数返回p2的值 如果p1非空 返回p1
结果出来之后再处理
select first_name,
nvl(salary*12+salary*12*(COMMISSION_PCT/100),0) "year salary"
from s_emp;
先处理后计算
select first_name,
salary*12+salary*12*(nvl(COMMISSION_PCT,0)/100) "year salary"
from s_emp;
select first_name,
salary*12*(1+(nvl(COMMISSION_PCT,0)/100)) "year salary"
from s_emp;
测试表-------dual----单行单列表
sysdate------oracle当前日期
select sysdate from dual; -----1
select sysdate from s_emp;-----25
select nvl(sysdate,'01-AUG-11') from dual;
select nvl(NULL,'01-AUG-11') from dual;
3 56
B where(条件 限制记录)----选择
salary 大于1000的员工
select * from s_emp where salary>1000;
select last_name,salary from s_emp;----投影
select last_name,salary from s_emp where salary>1000; 选择+投影
注意:* 能代表所有的列 慎用
join是当你查询的数据不在一张表中时用
字面值------1 2 'abc'
'------'''' -----'告诉oracle引擎转义
lastname'firstname
zhang'san
select last_name||''''||first_name from s_emp;
select last_name||123||first_name from s_emp;
注意:单引号的表达
双引号-----别名
小写 特殊字符 last_name my name
select last_name "my name" from s_emp;
逻辑比较的 ---和c中使用相同 注意等号
注意 等于号用一个=
找出 id 等于1的人的名字
select last_name||first_name name from s_emp where id=1; sql的比较运算符
select last_name||first_name name
from s_emp where id in(1,3,5);
like 'aa' like 'aaa' true 'aa' like 'aab' true %----0到n个任意字符
_----1一个任意字符
where name like 'wang%'
last_name 带a的
select last_name from s_emp where last_name like '%a%'; 表达NULL时 是不是空 is null
找出manager_id 是空的人的名字
select last_name,manager_id from s_emp
where manager_id is null;
between a and b [a,b]----闭区间
工资 800 到1500的人
4 56
select last_name,salary from s_emp
where salary between 800 and 1500;
注意：1.in 中的数据排放1 90% 10% 把概率高的放前面
2.null的判断用 is
3.between a and b 是一个闭区间
4.like 应用于模糊查询 % _
表名带有_
user_tables的表
table_name ----表面
select table_name from user_tables where
table_name like '%\_%' escape '\';
转义------为什么要转义
补充------user_tables-----数据字典表
存储的数据都是大写的
表名是s_emp 表相关的信息
select * from user_tables where table_name like 'S\_EMP' escape '\';
逻辑运算符
and or not
工资 800 到1500的人
select last_name,salary from s_emp where
800<=salary<=1500;//error
select last_name,salary from s_emp where
salary>=800 and salary<=1500;
如何来表非
null ----is null ----is not null
in ----in(list) ----not in
(同理 like , between and)
等于=
!= <> ^=
select id from s_emp where id!=1;
select id from s_emp where id^=1;
注意:not in------如果有null值 则不能用not in
select COMMISSION_PCT from s_emp
where COMMISSION_PCT in(10);
select COMMISSION_PCT from s_emp
where COMMISSION_PCT not in(10);
select COMMISSION_PCT from s_emp
where nvl(COMMISSION_PCT,0) not in(10);
找出41部门的所有人 和 42部门的并且42部门的员工工资大于1000的员工 提示如果想表达的更清楚 可以使用小括号
5 56
select last_name,salary from s_emp
where dept_id=41 or (dept_id=42 and salary>1000); ----这条选择出来的数据可能更多一点？
找出 要求工资大于1000 的员工 并且 部门是41或者42部分的员工 select last_name,salary from s_emp
where salary>1000 and (dept_id=42 or dept_id=41); select salary*12+100 from s_emp; ----过节一年多发100 select (salary+100)*12 from s_emp;---每个月多发100
C order by(排序)
按姓升序排序显示 last_name,salary
默认的排序----自然顺序 字典顺序 升序 （asc）
select last_name,salary from s_emp order by last_name; 按工资升序 显示last_name salary
select last_name,salary from s_emp order by salary asc; 降序
select last_name,salary from s_emp order by salary desc;
select last_name,salary from s_emp order by 1;
select last_name,salary from s_emp order by 2 desc; (1,2代表查询字段的位置)
排序可以按多列排序
select title,salary from s_emp
order by title,salary;
先按第一个字段排序 如果第一个字段值相同 再按第二个字段排序 select title,salary from s_emp
order by title,salary desc;
先按职位降序 职位相同再按工资升序
select title,salary from s_emp
order by title desc,salary;
排序规则没个字段都要说明清楚 如果不说明 就是默认规则 注意：1.order by 后出现的字段可以不出现在select后
2.注意NULL 在排序中是无穷大
select last_name,commission_pct from s_emp
order by commission_pct;
D function 函数
单行函数------每一条记录处理后产生一个返回值
select nvl(manager_id,0) from s_emp;
upper
select upper('abc') from dual;
select initcap('one world one dream') from dual;
select concat('hello','world') from dual;
等价于
6 56
select 'hello'||'world' from dual;
select substr('hello',1,4) from dual;
注意substr的索引从1开始
length
select length(last_name),last_name from s_emp;
只显示last_name开始的两个字母
select last_name,substr(last_name,1,2) from s_emp;
只显示last_name最后的两个字母
select last_name,substr(last_name,length(last_name)-1,2) from s_emp; 负数代表从后向前数
select last_name,substr(last_name,-2,2) from s_emp;
找出last_name 长度是5的员工
first_name 是 Carmen的
sql语句不区分大小写
SELECT FIRST_NAME FROM S_EMP where first_name='Carmen';
SELECT FIRST_NAME FROM S_EMP where lower(first_name)='carmen';
-------------------------------------------------------------------------------
1.找出和Carmen职位相同的人
第一步先把Carmen的职位查出来
再查询和这个职位相同的人
select title from s_emp where first_name='Carmen';
select title,first_name from s_emp where title='a'
and first_name!='Carmen';
2.查询工资大于1000的 员工按工资降序 工资相同按名字降序
select last_name,salary from s_emp where salary>1000
order by salary desc,last_name desc;
3.查询出id是1,3,5的员工 不允许用in
select last_name,id from s_emp where id=1 or id=3 or id=5;
4. 找出last_name长度是4 并且工资大于1500的员工 按工资升序
select last_name,salary from s_emp where length(last_name)=4
and salary>1500 order by salary;
多行函数------对一组记录处理后产生一个返回值---组函数
select count(nvl(manager_id,0)) from s_emp;
数字处理函数：
round 四舍五入 round(11.4,0)---11 round(11.5) ---12
trunc 截取 trunc(11.4,0)---11 trunc(11.5) ---11
第二参数可以省略 省略默认是0
to_char()
select to_char(salary,'fm$099,999,999.99') from s_emp;
select to_char(salary,'fmL099,999,999.99') from s_emp;
7 56
改成中文货币符号
环境变量----NLS_LANG
查看数据的本地语言
select userenv('language') from dual;
切换成中文
bash -----$
NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
csh -----%
1.在sh下 setenv NLS_LANG 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
2.sqlplus-----就可以看出
to_number
oracle的隐式数据类型转换
select last_name from s_emp where id='1';
select last_name from s_emp where id=1;
to_char 可以不跟后面的格式----就是简单的把对应的类型加上单引号 last_name manager_id
Carmen 'BOSS'
steven 12
1.select first_name,manager_id from s_emp;
2.select first_name,nvl(manager_id,'BOSS') from s_emp;//error
3.select first_name,nvl(to_char(manager_id),'BOSS') from s_emp;
笛卡尔集
s_dept ----部门表
desc s_dept
名称
------------------------------------
ID -----部门编号
NAME----部门名称
REGION_ID---地区编号
找出每个员工和他对应的部门名称
造一张表出来
select last_name,dept_id,name from s_emp,s_dept;---300 select last_name,dept_id,name from s_emp,s_dept---25
where dept_id=s_dept.id;
注意：先执行where条件 不满足的不去做匹配
*E muti table select(多表查询)
内连接 ------完全符合where的数据被选择出来
等值连接
select last_name,dept_id,name from s_emp,s_dept---25 where dept_id=s_dept.id;
8 56
select last_name,dept_id,name from s_emp e,s_dept d where dept_id=d.id;
s_region
id -----地区编号
name-----地区名称
要求把每个员工last_name对应的部门名称 和 地区名称 select e.last_name,d.name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id;
col name for a20 ----名字为name的这一列最多一行显示20 col last_name for a16
非等值连接
salary
salary between lowsal and hisal
salgrade
GRADE LOSAL HISAL
------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
salary>=800 and salary<=1000
每个员工的last_name,salary 和对应的工资级别显示出来 select e.last_name,e.salary,g.grade
from s_emp e,salgrade g
where e.salary between g.losal and g.hisal;
自连接
s_emp 中的所有的领导
id manager_id
1
2 1
3 1
4 2
5 3
如果一个员工的id 和一个员工的manager_id 相等
select distinct m.last_name,m.id,e.manager_id from s_emp e,s_emp m
where m.id=e.manager_id;
8个领导 25-8=17个普通员工
外连接（outer join）=内连接+匹配不上的----（一个也不能少） 9 56
如果是外连接的语法 加号对面的表中的数据全都查询出来 在内连接的基础上把匹配不上的数据通过空值匹配出来
select distinct m.last_name,m.id,e.manager_id
from s_emp e,s_emp m
where m.id=e.manager_id(+);
这些人是普通员工
select distinct m.last_name,m.id,e.manager_id
from s_emp e,s_emp m
where m.id=e.manager_id(+)
and e.manager_id is null;
1.update s_emp set dept_id=null where dept_id=50;
commit;
2.查询出last_name,dept_id 对应的部门名称
select last_name,dept_id,d.name
from s_emp e,s_dept d
where e.dept_id=d.id; ----23 个人匹配
3.显示所有员工对应的部门号 和部门名称 如果没有部门名称则 显示 'no department'
select last_name,dept_id,d.name
from s_emp e,s_dept d
where e.dept_id=d.id(+);
4.把没有部门名称的人显示出来 部门名称显示成'no department' select last_name,nvl(to_char(dept_id),'no department') from s_emp where dept_id is null;
select last_name,dept_id,nvl(d.name,'no department') from s_emp e,s_dept d
where e.dept_id=d.id(+)
and d.id is null;
要所有的普通员工（那些人不是领导）
等值连接
非等值连接
5 3001 10000
显示所有级别对应的员工的last_name 'no employee' 要级别表中所有的数据 那加号应该加在那边？
select e.last_name,e.salary,g.grade
from s_emp e,salgrade g
where e.salary between g.losal and g.hisal;
select nvl(e.last_name,'no employee'),e.salary,g.grade from s_emp e,salgrade g
where e.salary(+) between g.losal and g.hisal; 现在有一个员工 他的工资不在5个级别的范围之内 但我要显示这个员工的级别 把他的级别显示成0
update s_emp set salary=0 where first_name='Carmen'; 10 56
commit;
内连接的sql如下 加号怎么加？
select e.first_name,e.salary,g.grade
from s_emp e,salgrade g
where e.salary between g.losal(+) and g.hisal(+); 自连接
外连接 sql99
select distinct m.last_name,m.id,e.manager_id
from s_emp e,s_emp m
where m.id=e.manager_id;
在上面的sql基础上改成查询所有普通的员工
oracle 外连接的语法是
select distinct m.last_name,m.id,e.manager_id
from s_emp e,s_emp m
where m.id=e.manager_id(+)
and e.manager_id is null;
那张表中数据要被全部查询出来就由那张表发起连接
1.把加号 去掉
2.把逗号替换成left outer join /right outer join
3.把where 换成 on 到此完成外连接
select distinct m.last_name,m.id,e.manager_id
from s_emp e right outer join s_emp m
on m.id=e.manager_id;
上面的是完成外连接
如果要对外连接的结果集进行 过滤 要用where条件
4.如果有连接条件之外的条件需要加 where
select distinct m.last_name,m.id,e.manager_id
from s_emp e right outer join s_emp m
on m.id=e.manager_id
where e.manager_id is null;
内连接的sql如下
select e.first_name,e.salary,g.grade
from s_emp e,salgrade g
where e.salary between g.losal(+) and g.hisal(+); 改成sql99的语法
select e.first_name,e.salary,g.grade
from s_emp e left outer join salgrade g
on e.salary between g.losal and g.hisal; 总结：
内连接-----完全符合查询条件的数据被选择出来
等值连接 =
非等值连接 between and >= <= in like is
自连接 ----所有的数据在一张表 给表起别名
11 56
外连接 等值连接 =
非等值连接 between and >= <= in like is 自连接 ----所有的数据在一张表 给表起别名
为了保持外连接的通用性 sql99的标准 left outer join /right outer join
full outer join=左外+右外-重复的
union/uoion all (union 和并结果集时排重 union all) select id from s_emp union
select id from s_emp;
select id from s_emp union all
select id from s_emp;
左外 24 右外25
select e.first_name,e.salary,g.grade
from s_emp e full outer join salgrade g
on e.salary between g.losal and g.hisal;---27 select e.first_name,e.salary,g.grade
from s_emp e left outer join salgrade g
on e.salary between g.losal and g.hisal union select e.first_name,e.salary,g.grade
from s_emp e right outer join salgrade g
on e.salary between g.losal and g.hisal; ---26 F group by （分组 组函数）
组函数 是对一组数据处理之后得到一个结果
count() ----统计个数
max -----最大
min -----最小
avg -----平均
sum -----求和
select sum(salary) from s_emp;
select max(salary) from s_emp;
那个部门工资占用最大
按部门分组
select
from
where
group by
order by
用dept_id 分组
select dept_id from s_emp group by dept_id;
select salary,dept_id from s_emp group by dept_id; 12 56
一个部门中工资很多
select salary,dept_id from s_emp group by salary,dept_id; 从每个部门中挑出一个工资最高
select max(salary),dept_id from s_emp group by dept_id; 统计出每个部门的每个月的支出
select sum(salary),dept_id from s_emp group by dept_id order by sum(salary);
select sum(salary) s,dept_id from s_emp group by dept_id order by s;
求每个月支出最大的部门 -----告诉你支出最大的部分是41 select sum(salary) s,dept_id from s_emp
where dept_id=41
group by dept_id
order by s; //error
和上面有什么区别？
select sum(salary) s,dept_id from s_emp
group by dept_id having dept_id=41
order by s;
where 的执行是在group by 之前的
having是对分组之后的数据进行处理
select
from
where
group by
having
order by
注意：
1.group by 的字段可不可以不出现在select 后 可以
2.如果一个字段不出现在group by之后 就必须用组函数进行处理
3.having是对分组之后的数据进行过滤
G sub query(子查询)
那些人是领导 ？
select distinct manager_id from s_emp;(得到所有领导的id 和NULL) select last_name,id,manager_id
from s_emp where id in(1,2,3,6,7,8,9,10);
select last_name,id,manager_id
from s_emp where id
in(select distinct manager_id from s_emp);
把一个查询的结果作为另一个查询的条件
和Carmen做相同工作的人？
select title from s_emp where
13 56
first_name='Carmen';
select first_name,title from
s_emp where title =(select title from s_emp where
first_name='Carmen')
and first_name!='Carmen';
工资总和大于42部门的所有部门和支出总和
1.先求42部门的支出总和是多少
2.求按部门分组之后的的每个部门的支出总和
3.通过having子句对分组之后的数据进行过滤 得到最终的结果
select sum(salary) from s_emp where dept_id=42;
select sum(salary), dept_id from s_emp
group by dept_id
having sum(salary)>(select sum(salary)
from s_emp where dept_id=42);
补充：子查询不但可以用在where having之后
还可以用在from之后
select sum(salary) s,dept_id from s_emp
group by dept_id having dept_id=41
order by s;
select s,dept_id ,n from (select sum(salary) s,
dept_id,max(name) n
from s_emp,s_dept where dept_id=s_dept.id
group by dept_id
order by s) where dept_id=41;
作业
1.查询每个员工对应的地区 (等值连接) 25 12 5
select last_name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id;
2.查询那些人是领导 用外连接做和子查询(oracle/sql99)
select distinct m.last_name,m.id,e.manager_id
from s_emp e, s_emp m
where m.id=e.manager_id(+) and e.manager_id is not null; 用子查询做哪些人是领导
select last_name,id,manager_id
from s_emp
where id in(select distinct manager_id from s_emp);
用子查询做哪些人不是领导
select last_name,id,manager_id from s_emp
where id not in(select distinct manager_id from s_emp);//error 14 56
select last_name,id,manager_id from s_emp
where id not in(select distinct manager_id
from s_emp where manager_id is not null); 注意：用not in 时集合中不能有null
3.求工资总和大于等于10部门的部门名称和工资总和
select dept_id,sum(salary),max(d.name) from s_emp e,s_dept d where e.dept_id=d.id group by dept_id
having sum(salary)>=(select sum(salary)
from s_emp
where dept_id=10)
and dept_id!=10;
4.select sum(salary) ss, dept_id from s_emp
group by dept_id
having ss>(select sum(salary)
from s_emp where dept_id=42);
//why erorr
select 5
from 1
where 2
group by 3
having 4
order by 6
5.以dept_id 分组显示每个部门中工资最高的一个人的last_name
还要显示出这个人的部门名称。
select max(salary),min(last_name),dept_id,min(d.name) from
s_emp e,s_dept d
where e.dept_id=d.id
group by dept_id;
注:3,5参考
select sum(salary) s,
dept_id,max(name) n
from s_emp,s_dept where dept_id=s_dept.id
group by dept_id
order by s;
1 9
---------------------------------------------------------------------------------------------------------------
1.数据库的设计
E-R
一个顾客 拥有多个 订单
一个订单 只有一个 顾客
# ------唯一的
* ------非空
15 56
o ------没有限制的 可选的
对象和对象的关系
一对一 丈夫 妻子 1 1
一对多 顾客 订单 1 n
多对多 学生 课程 一个学生 可以选多门课程
一个课程 可不可以被多学生选 如果这两个人的信息 成双入对 ------一张表
你需要一个人信息的时 并不需另一个的 -----可以两张表
如何来表达一对多 zhangsan employe 1,2,3,4 1 zhangsan 23 1 lisi
1 zhangsan 23 2 zhao
1 zhangsan 23 3 zhang
1 zhangsan 23 4 wang
数据冗余 (时间 空间)
消除部分的冗余 ------拆表
1 zhangsan 23 1 lisi 1
2 zhao 1
3 zhang 1
4 wang 1
思考一下多对多
student course
1 zhangsan 23 1 c
2 lisi 24 2 c++
3 wangwu 25 3 uc
4 oracle
5 plsql
6 proc/c++
现在用一张表表达多对多
1 zhangsan 23 1 c
1 zhangsan 23 2 c++
1 zhangsan 23 3 uc
1 zhangsan 23 4 oracle
1 zhangsan 23 5 plsql
1 zhangsan 23 6 proc/c++
2 lisi 24 1 c
2 lisi 24 2 c++
2 lisi 24 3 uc
2 lisi 24 4 oracle
2 lisi 24 5 plsql
2 lisi 24 6 proc/c++
3 wangwu 25 1
16 56 是Customer
3 wangwu 25 2 c++
3 wangwu 25 3 uc
3 wangwu 25 4 oracle
3 wangwu 25 5 plsql
3 wangwu 25 6 proc/c++
先分出一张课程表
1 c
2 c++
3 uc
4 oracle
5 plsql
6 proc/c++
再做一张学生表
1 zhangsan 23
2 lisi 24
3 wangwu 25
下面的关系但放一张表 叫关系表
1 1, 1 2, 1 3, 1 4, 1 5, 1 6
2 1, 2 2, 2 3, 2 4, 2 5, 2 6
3 1, 3 2, 3 3, 3 4, 3 5, 3 6
三范式
1 第一范式
所有的字段不可再分 面积(width/length)
2 第二范式
主属性-----非空 唯一
所有的非主属性 完全依赖于主属性
3 第三范式
在第二范式的基础上消除传递依赖-----拆表
1 sn001 zhangsan 0002 c 89
2 sn001 zhangsan 0003 c++ 99
( 1--->sn001)--->0002--->89
10 5个字段空着-----保留字段
id fk
1 zhangsan 23 1 lisi 1
2 zhangsan 26 2 zhao 1
3 zhang 1
4 wang 1
主键---------唯一标示一条记录的字段 不要用业务相关的字段做主键 ---------非空 唯一
---------一个表可以用多个非空唯一的字段 但主键只有一个 外键---------外键的值受限于引用的字段
---------外键的值要么是引用的字段所拥有的值 要么是null
17 56
可以保证数据的完整性
E-R 到表和表的关系
1.实体名 ------表名
2.属性 ------字段
类型 ------字段类型
3.主键 外键的区分
一个员工 有多个顾客
emp customer
1 m
主键 外键
----------------
学生 课程
m m
主键 主键
关系表中 外键 外键
4.关系对应（1:1 1:m m:m）
写成一个sql脚本
数据类型
varchar2(n) ------变长字符串
char(n) ------定长字符串---如果数据不够长 就补空格 create table tablename (
id number,
fname varchar2(10),
sname char(10)
);
insert into tablename values(1,'abc','abc'); commit;
select length(fname),length(sname) from tablename;
select * from tablename where fname='abc ';
select * from tablename where sname='abc ';
定长就是比较长度范围内的值
变长就是传入什么值就比较什么值
什么时候用定长
当这个字段的长度是不变的 身份证号码
什么时候用变长
当这个字段的长度是变化的 name
都做成定长的 ----存 取 效率不高
18 ---- 255 ---237
name -----char(255)
zhangsan 247 zhangsan ----浪费空间
18 56
变长的-----空间利用率高255 存取效率低
varchar2(255)
a a
abc abc
abcd abcd
'abcd'
trim() -----去掉首尾空格
select * from tablename where fname=trim('abc ');
number 数字类型 10e-130 -------- 10 e127 number(p,s) -----p有效位 s精度
blob -----大二进制类型 4gb
clob -----大字符类型 4gb
date
英文环境下的日期默认格式 'dd-三个英文字母缩写-yy' 中文环境下的日期默认格式 'dd-1月-yy'
drop table tablename;------删除表
create table tablename (
id number,
hirdate date
);
insert into tablename values(1,'02-AUG-86');
to_date(字段/字面值,格式);
select to_date('2009-08-08','yyyy-mm-dd') from dual; yyyy----四位年
mm ----月
dd ----日
hh ----小时 默认是12 hh24
mi -----分钟
ss -----秒
=====================
day ---- 星期几
dy ---- 三个字母的星期几缩写
MONTH ---- 英文月
MON ---- 三个字母的月的缩写
必须和to_char 联合使用
select to_char(start_date,'yyyy-mm-dd hh24:mi:ss day') from s_emp;
select to_char(start_date,'yyyy') from s_emp; 19 56
查询出三月入职的员工
select last_name,start_date from s_emp
where start_date like '%MAR%';
select last_name,start_date from s_emp
where to_char(start_date,'mm')=3;
select last_name,start_date from s_emp
where to_char(start_date,'mm')='03';
sysdate ----2011-08-29 15:25:50
trunc(sysdate,'dd')------2011-08-29 00:00:00
select to_char(trunc(sysdate,'dd'),'yyyy-mm-dd hh24:mi:ss')
from dual;
round(sysdate,'dd')------2011-08-30 00:00:00
select to_char(round(sysdate,'dd'),'yyyy-mm-dd hh24:mi:ss')
from dual;
select to_char(round(sysdate,'yy'),'yyyy-mm-dd hh24:mi:ss')
from dual;
dd --- 看过没过12点
mm ----看过没过这个月的一半
yy ----看过没过这一年的一半
trunc(sysdate+1,'dd');
trunc(sysdate,'dd')+1;
思考一下有什么不同？
trunc(sysdate+1/24,'dd');------这个数据不确定
trunc(sysdate,'dd')+1/24;------当前天的1点中
如果在一个时间上加1 代表加了一天
思考一下有什么不同？
add_months(sysdate,n);
months_between(d1,d2) 算两个日期差几个月 返回小数
如果正好是整月 返回整数
select months_between(sysdate,start_date) from s_emp;
select months_between('28-FEB-11','30-JUN-11') from dual;
next_day(sysdate) --------某个日期的下一天 第二个参数是星期几
select to_char(next_day(sysdate,'friday'),'yyyy-mm-dd hh24:mi:ss') from dual; 如何得到一个时间的下一天的这个时刻
加1就ok了？
select to_char(sysdate+1,'yyyy-mm-dd hh24:mi:ss') from dual;
last_day(sysdate)
时分秒信息 -----最后一天的这个时刻
select to_char(last_day(sysdate),'yyyy-mm-dd hh24:mi:ss')
from dual;
处理成最后一天的9月1号的 00:00:00
给你一个日期得到这个日期的下一个月的开始
20 56
add_months(trunc(sysdate,'mm'),1);
trunc(last_day(sysdate)+1,'dd');
存储了时分秒信息 但默认显示只有 日-月的缩写-两位年 如何建表 如何来表达关系 如何来设置约束 create table 表名(
字段名 类型 default 值,
字段名 类型 default 值
);
约束：
主键 ------primary key
外键 ------foregin key
非空 ------not null
唯一 ------unique
检查 ------check
列级
create table test(
id number primary key,
tid number
);
主键 非空 唯一
insert into test values(null,null);
表级
create table test(
id number,
tid number,
primary key(id,pid)
);
区别在于表级约束可以加联合约束
注意:not null 约束只有列级约束 没有表级约束 没有联合非空这一说
建一张表 id number类型的主键
name varchar2(30) 非空
sno char(30) 唯一
salary number
create table test(
id number constraint test_pk primary key, name varchar2(30) not null ,
sno char(30) constraint test_uk unique, salary number
);
回去把这个例子改成表级约束 注意not null
user_constraints
数据字典中查询出一个表上的约束有哪些
select constraint_name,constraint_type
21 56
from user_constraints where table_name='TEST'; 根据约束名删除约束的限制
alter table test drop constraint SYS_C0015696; check 约束
create table test(
id number constraint test_pk primary key, salary number check (salary>1000)
);
insert into test values(1,1100);
insert into test values(2,900);
大家回去改成表级
create table test1(
id number constraint test1_pk primary key, salary number,
check (salary>1000)
);
外键约束
外键引用 某张表唯一性字段
在子表中定义外键
1 m
主表(父表) 子表
顾客 订单
id id
cno --顾客编号 ono----订单号
cname fid----代表谁的订单
建表--------先建父表后建子表
父表
create table customer(
id number primary key,
cno varchar2(20),
cname varchar2(30)
);
insert into customer values(1,'002','zhangsan'); 子表
create table corder(
id number primary key,
ono varchar2(20),
fid number references customer(id)
);
insert into corder values(1,'001',null);
insert into corder values(2,'001',1);
insert into corder values(3,'001',1);
大家回去把这个一对多的关系改成1对1
22 56
在理解了一对多关系的基础上建两张表 完成一个多对多 student course
id id
sno cno
sname cname
关系表
id
sid
cid
查询id为1的人的名字和订单号
select cname,o.ono
from customer c,corder o
where c.id=o.fid and c.id=1;
插入数据时 子表的外键要么是null 要么是主表字段中的值
事务 ------原子性 一致性 隔离性 持久性(AUCD)
转账
A B
200000 0
150000 50000
200000 50000 //error
150000 0 //error
200000 0 //
A update account set salary=salary-50000 where id=1; update account set salary=salary+50000 where id=2;
隔离性
insert into corder values(4,'004',1);
oracle的事务是非自动提交的 mysql sqlserver 自动提交
sql的语言的分类：
dql ---select -----没有事务这一说
dml ---update insert delete ----非自动提交 ddl ---create drop alter ------事务自动提交 dcl ---grant revoke
tcl ---commit rollback savepoint
create table t (id number primary key,salary number); SQL> insert into t values(1,1000);
SQL> savepoint a;
SQL> insert into t values(2,1000);
SQL> rollback to a;
23 56
SQL> commit;
不commit 数据在回滚段中
你自己来控制主键？
其它的数据对象
补充
---------------------------------------------------------------------------------------------------------------------- 序列：
create sequence tests;
nextval
currval
select tests.nextval from dual;(-pow(10,26),pow(10,27))
insert into customer values(tests.nextval,'abc','123');
create sequence 序列名
increment by n ,步进 默认是1
start with n,起始值 默认1
maxvalue n/nomaxvalue,默认nomaxvalue 实际是pow(10,27)
minvalue n/nominvalue,
nocycle/cycle,默认nocycle
cache n/nocache 默认20
注：如果cycle的序列到最大值后 回到默认值1
create sequence testss
increment by 10
start with 8
cache 200;
索引：index
从头找到尾 全表扫描
有目录-----index ----索引查找
每次查找的数据量 2%-4%
索引占空间
如果你的表经常的变化 会造成索引很蜂窝
B*树
rowid 18为的物理磁盘编号
oracle公司内部培训资料
24 56
主键 唯一约束 自动建立索引
create index customer_index on customer(cno);
set autotrace on----打开执行计划
select * from customer; 全表扫描
select * from customer where id=1;
select * from customer where cno='abc';
两个字段经常同时出现在where之后
create index customer_index1 on customer(cno,cname);
视图,分页？ rowid 应用
视图不占空间
视图限制数据库的访问
简化查询
select max(s),max(dept_id)from
(select sum(salary) s,dept_id from s_emp
group by dept_id)
select * from
(select sum(salary) s,dept_id from s_emp
group by dept_id) where s=
(select max(ss) from (select sum(salary) ss from s_emp group by dept_id));
保持数据的独立
同一个数据 不同的表现
create or replace view cview as
select id, last_name from s_emp;
select * from cview;
简单的视图 和表的功能相同 可以在视图上增删改查
view有很多的限制 ----复杂的view
分页？
为什么？
select salary,last_name from s_emp order by salary;
rownum ----伪列
select rownum,salary,last_name from s_emp;
select rownum,salary,last_name from s_emp where rownum<6; 如果我想按工资排序后 取前5条
select rownum,salary,last_name from s_emp where rownum<6 order by salary; //error
1先排序
select salary,last_name from s_emp order by salary;
2.用rownum进行编号 where 条件过滤
25 56
select rownum,salary,last_name from (select salary,last_name
from s_emp order by salary) where rownum<11;
取第6到第10条
select rownum,salary,last_name from (select salary,last_name
from s_emp order by salary)
where rownum<11 and rownum >5;// no rows selected
select * from (select rownum n,salary,last_name from (select salary,last_name from s_emp order by salary)
where rownum<11) where n>5; //这个比较好
按某个字段排序 并且取中间的几条数据 必须三层嵌套
select * from (select rownum n,salary,last_name from (select salary,last_name from s_emp order by salary)
) where n>5 and n<11;
有一张表 里面有重复的数据
有主键时 name
id name age
1 abc 23
2 abc 24
3 bcd 23
4 bcd 23
5 bcd 23
把名字相同的只留一条
id , rowid
没有主键
id name age
1 abc 23
1 abc 24
3 bcd 23
2 bcd 23
2 bcd 23
2 bcd 23
2 bcd 23
把id 和name 相同的只留一条
rowid
26 56
@文件名
Plsql
--------------------------------------------------------------------------------------------------------------------- SQL
1.访问数据库的技术
plsql ----数据库内部访问技术
proc/c++ ----针对c C++
odbc ----mic
oci ----oracle底层的通信接口
begin
dbms_output.put_line('hello world');
end;
set serveroutput on -----打开输出
declare
申明部分
begin
执行部分
exeception
异常处理
end;
赋值 --- :=
比较 --- =
-----------------------------------------------
变量的申明
declare
v_id number;
v_name varchar2(30);
begin
v_id:=10;
select last_name into v_name from s_emp
where id=v_id;
dbms_output.put_line(v_name);
end;
DECLARE
v_id number;
v_name varchar2(30);
BEGIN
27 56
v_id:=10;
select last_name into v_name from s_emp
where id=v_id;
DBMS_OUTPUT.PUT_LINE(v_name);
END;
------------------------------------
标示符 ----和sql中的规定相同
给变量 游标 子程序 类型 命名
变量声明的语法
Var_name [CONSTANT] type[NOT NULL] [:=value]; const 代表常量
如果你想 变量必须有初始值 可以加 not null
如果一个变量没有初始值那变量的默认值是 null
declare
v_nu constant number :=10;
begin
v_nu:=100; --error
dbms_output.put_line(v_nu);
end;
数据类型：
数字类型(binary_integer) 字符类型 boolean date 组合类型：
记录类型 table类型
引用类型
ref cursor
-----------------------------------
%type -----取表中某个字段的类型
declare
v_id s_emp.id%type;
v_name s_emp.last_name%type;
v_count number;
begin
v_id:=10;
select last_name into v_name from s_emp
where id=v_id;
dbms_output.put_line(v_name);
end;
-------------------------------------
record 类型
declare
type type_rec is record(
id number,
name varchar2(30)
28 56
);
var_rec type_rec;-- 用type_rec类型定义一个变量 var_rec
var_rec2 type_rec;
begin
--select id,last_name into var_rec from s_emp where id=1; --select id into var_rec from s_emp where id=1;//error
select id,last_name into var_rec.id,var_rec.name from s_emp where id=1;
var_rec2:=var_rec;--记录类型整体赋值
var_rec2.id:=var_rec.id;--单个逐一赋值
dbms_output.put_line(var_rec.id||':'||var_rec.name);
dbms_output.put_line(var_rec2.id||':'||var_rec2.name); end;
---如果我要接收s_emp表中的一条记录---
type type_rec is record(
id s_emp.id%type,
name s_emp.first_name%type;
.........
);
------------------------------------------------
为了写的简洁一些 %rowtype ----取表的一行作为类型
declare
var_rec s_emp%rowtype; ---用更加简洁的语言定义一个变量var_rec begin
select id,last_name into var_rec.id,var_rec.last_name from s_emp where id=1;
dbms_output.put_line(var_rec.id||':'||var_rec.last_name); end;
----------------------------------------------------
talbe 类型
TYPE tabletype(table的类型名) IS TABLE OF
type（表中的值的数据类型） INDEX BY BINARY_INTEGER;
定义一个装字符串的table
declare
TYPE table_char IS TABLE OF char(30) INDEX BY BINARY_INTEGER; var_t table_char;
begin
var_t(1):='hello world';
var_t(3):='hello';
dbms_output.put_line(var_t(1));
end;
先把s_emp 中所有的id为1,3,5数据放入一个table类型的变量中 29 56
declare
TYPE table_emp IS TABLE OF s_emp%rowtype INDEX BY BINARY_INTEGER; var_t table_emp;
begin
select * into var_t(1) from s_emp where id=1;
select * into var_t(2) from s_emp where id=3;
select * into var_t(3) from s_emp where id=5;
dbms_output.put_line(var_t(1).id||':'||var_t(1).last_name); dbms_output.put_line(var_t(2).id||':'||var_t(2).last_name); dbms_output.put_line(var_t(3).id||':'||var_t(3).last_name); end;
------------------------------------------------
变量的作用域 和可见性
<<outer>> --这是一个标签
declare
var_n number:=10;
begin
declare
var_n number:=100;
begin
dbms_output.put_line('inner:'||var_n);
dbms_output.put_line('inner:'||outer.var_n);--访问外部变量 end;
dbms_output.put_line('outer:'||var_n);
end;
------------------------------------------------
if 语句
if 表达式 then
elsif 表达式 then
else
end if;
--验证空值
declare
var_n1 number; --没有赋初始值 都是null
var_n2 number;
begin
if var_n1=var_n2 then
dbms_output.put_line('var_n1=var_n2');
elsif var_n1>var_n2 then
dbms_output.put_line('var_n1>var_n2');
elsif var_n1<var_n2 then
dbms_output.put_line('var_n1<var_n2');
30 56
elsif var_n1 is null and var_n2 is null then
dbms_output.put_line('var_n1 and var_n2 is null'); else
dbms_output.put_line('var_n1 is var_n2'); end if;
end;
当满足某个条件时什么都不做时 可以使用空语句
declare
var_n1 number; --没有赋初始值 都是null
var_n2 number;
begin
if var_n1=var_n2 then
dbms_output.put_line('var_n1=var_n2');
elsif var_n1>var_n2 then
dbms_output.put_line('var_n1>var_n2');
elsif var_n1<var_n2 then
dbms_output.put_line('var_n1<var_n2');
elsif var_n1 is null and var_n2 is null then
dbms_output.put_line('var_n1 and var_n2 is null'); else
null; --让逻辑更加严谨和清晰
end if;
end;
-------------------------循环语句--------------------
loop
if 表达式 then
exit;--退出循环
end if;
end loop;
输出1到10
declare
var_n binary_integer:=0;
begin
loop
var_n:=var_n+1;
if var_n>10 then
exit;
end if;
dbms_output.put_line(var_n);
end loop;
end;
作业：把s_emp表中所有的数据放入一个table 中
31 56
并且循环输出这些数据 选id salary last_name输出 id 连续？
while 循环
while 表达式 loop
end loop;
表达式的作用是满足条件就循环 不满足就退出循环 输出1到10
declare
var_n binary_integer:=0;
begin
while var_n<10 loop
var_n:=var_n+1;
dbms_output.put_line(var_n);
end loop;
end;
作业:用sql建一张表 student(id,name,age)
建一个序列 test
用循环插入100 条数据 名字和age 可以相同
for 循环输出1到10
begin
for a in 1..10 loop
dbms_output.put_line(a);
end loop;
end;
a 不用在declare 中声明 就可以直接使用
<<lable_1>> 可以随时用随时定义
goto 标签名;
完成输出1 到10
declare
var_n number:=0;
begin
<<labe_go>>
var_n:=var_n+1;
if var_n<11 then
dbms_output.put_line(var_n);
32 56
goto labe_go;
end if;
end;
begin
--select id from s_emp where id=1;
insert into test values('abc',sysdate);
commit;
end;
只有dml 和dcl 可以直接在plsql块中使用
其它必须有特殊的语法 如select
ddl 语句属于动态sql 有专门的语法
repalce
a 变成 y
select last_name,replace(last_name,'a','y') from s_emp;
id 不连续的情况下 table数据的输出
下标连续
declare
type table_t is table of s_emp%rowtype index by binary_integer;
var_temp table_t;
begin
select * into var_temp(1) from s_emp where id=1; select * into var_temp(2) from s_emp where id=2; select * into var_temp(3) from s_emp where id=3;
for a in 1..3 loop
dbms_output.put_line(var_temp(a).id
||':'||var_temp(a).last_name);
end loop;
end;
下标不连续？
declare
type table_t is table of s_emp%rowtype index by binary_integer;
var_temp table_t;
begin
select * into var_temp(1) from s_emp where id=1; select * into var_temp(100) from s_emp where id=2; select * into var_temp(205) from s_emp where id=3;
for a in 1..3 loop
33 56
dbms_output.put_line(var_temp(a).id
||':'||var_temp(a).last_name);
end loop;
end;
为了解决下标不连续
first() ----拿到第一个数据的下标
last() ----拿到最后一个数据的下标
next(n) ----得到n这个下标的下一个下标
declare
type table_t is table of s_emp%rowtype index by binary_integer;
var_temp table_t;
var_n number:=0;
begin
select * into var_temp(1) from s_emp where id=1; select * into var_temp(100) from s_emp where id=2; select * into var_temp(205) from s_emp where id=3; var_n:=var_temp.first();
loop
dbms_output.put_line(var_temp(var_n).id ||var_temp(var_n).last_name);
if(var_n=var_temp.last()) then
exit;
end if;
var_n:=var_temp.next(var_n);
end loop;
end;
如果下标连续 用其中的两个函数
declare
type table_t is table of s_emp%rowtype index by binary_integer;
var_temp table_t;
begin
select * into var_temp(1) from s_emp where id=1; select * into var_temp(2) from s_emp where id=2; select * into var_temp(3) from s_emp where id=3;
for a in var_temp.first()..var_temp.last() loop dbms_output.put_line(var_temp(a).id
||':'||var_temp(a).last_name);
end loop;
end;
34 56
作业:用sql建一张表 student(id,name,age)
建一个序列 test
用循环插入100 条数据 名字和age 可以相同
create table student123 (
id number primary key,name varchar(30),age number
);
create sequence test_students;
declare
var_n number;
begin
for a in 1 ..100 loop
insert into student123 values(test_students.nextval,'abc',23); end loop;
commit; end;
游标 -------用于提取多行数据的结果集
游标使用的步骤有4步
1.声明游标
cursor emp_cursor is select * from s_emp;
2.打开游标
open emp_cursor;
3.提取数据
fetch emp_cursor into 变量; --fetch 一次得到一条数据
4.关闭游标
close emp_cursor;
declare
cursor emp_cursor is select * from s_emp;--声明游标 var_emp s_emp%rowtype;
begin
open emp_cursor;--打开游标
fetch emp_cursor into var_emp;--提取数据
dbms_output.put_line(var_emp.id||':'||var_emp.salary); fetch emp_cursor into var_emp;
dbms_output.put_line(var_emp.id||':'||var_emp.salary); close emp_cursor;--关闭游标
end;
declare
cursor emp_cursor is select * from s_emp;--声明游标 var_emp s_emp%rowtype;
var_n number;
35 56
begin
select count(*) into var_n from s_emp;
open emp_cursor;--打开游标
for a in 1..var_n loop
fetch emp_cursor into var_emp;--提取数据
dbms_output.put_line(var_emp.id||':'||var_emp.salary); end loop;
close emp_cursor;--关闭游标
end;
for 循环中的游标自动打开 自动提取 自动关闭
declare
cursor emp_cursor is select * from s_emp;--声明游标 var_emp s_emp%rowtype;
begin
for var in emp_cursor loop--var就是其中的一条数据 dbms_output.put_line(var.id||':'||var.salary); end loop;
end;
如果我们使用 loop 或者 while
loop 循环把游标的数据 遍历出来
declare
cursor emp_cursor is select * from s_emp;--声明游标 var_emp s_emp%rowtype;
begin
open emp_cursor;--打开游标
loop
fetch emp_cursor into var_emp;--提取数据
exit when emp_cursor%notfound;--当发现不了数据就退出
dbms_output.put_line(var_emp.id||':'||var_emp.salary); end loop;
close emp_cursor;
end;
改成 while 循环
declare
cursor emp_cursor is select * from s_emp;--声明游标 var_emp s_emp%rowtype;
begin
open emp_cursor;--打开游标
fetch emp_cursor into var_emp;--提取数据
while emp_cursor%found loop
dbms_output.put_line(var_emp.id||':'||var_emp.salary); fetch emp_cursor into var_emp;--提取数据 end loop;
36 56
close emp_cursor;
end;
游标的属性
notfound found isopen rowcount
未打开 ora01001 ora01001 false ora01001
打开 null null true 0
fetch false true true 1
fetch false true true 2
fetch true fasle true 2
close ora01001 ora01001 false ora01001
注意：关闭的游标 不能再关闭 打开的游标不能再打开
exit when 表达式;
等价于
if 表达式 then
exit;
end if;
----------
select * from s_emp for update;--在查询时锁定这张表
fetch cursorname into 单个变量，单个变量;
fetch cursorname into record 变量;
fetch cursorname into record.id ,record.name;
-------------------带参游标---------------
declare
cursor emp_cursor(cpar number) is select *
from s_emp where id>cpar;--声明游标
var_emp s_emp%rowtype;
begin
open emp_cursor(10);--打开游标
fetch emp_cursor into var_emp;--提取数据
while emp_cursor%found loop
dbms_output.put_line(var_emp.id||':'||var_emp.salary); fetch emp_cursor into var_emp;--提取数据 end loop;
close emp_cursor;
end;
注意：参数类型不能指明长度
------------------------------------------
declare
var_n number;
begin
37 56
select id into var_n from s_emp where id>5;
exception
when TOO_MANY_ROWS then
dbms_output.put_line('TOO_MANY_ROWS');
--比较合理的处理是 向日志表中插入一条日志说明异常的发生 --以备以后错误的处理
end;
declare
cursor emp_cursor(cpar number) is select *
from s_emp where id>cpar;--声明游标
var_emp s_emp%rowtype;
begin
open emp_cursor(10);--打开游标
fetch emp_cursor into var_emp;--提取数据
while emp_cursor%found loop
dbms_output.put_line(var_emp.id||':'||var_emp.salary); fetch emp_cursor into var_emp;--提取数据 end loop;
close emp_cursor;
close emp_cursor;
exception
when invalid_cursor then
dbms_output.put_line('invalid_cursor');
when to_many_rows then
dbms_output.put_line('invalid_cursor');
end;
系统自定义异常 会根据sql语句的执行情况 自动抛出
---------------------------------------------
自定义异常
declare
to_many_emps exception;
v_emp s_emp%rowtype;
v_n number;
begin
select count(*) into v_n from s_emp;
-- 想抛出自己的异常 sql语句就不应该抛出系统异常
if v_n>1 then
raise to_many_emps;
end if;
select * into v_emp from s_emp; --上面的语句抛出了自定义的异常 --这句sql就没有机会执行
exception
when to_many_emps then
38 56
dbms_output.put_line('my exception to_many_emps'); when TOO_MANY_ROWS then
dbms_output.put_line('to_many_rows');
end;
declare
to_many_emps exception;
v_emp s_emp%rowtype;
v_n number;
pragma exception_init(to_many_emps,-1001);
--和一个异常的编号 绑定
begin
select count(*) into v_n from s_emp;
-- 想抛出自己的异常 sql语句就不应该抛出系统异常
if v_n>1 then
raise to_many_emps;
end if;
select * into v_emp from s_emp; --上面的语句抛出了自定义的异常 --这句sql就没有机会执行
end;
如果你想捕获所有的异常 用others
others 应该出现在异常处理的最后
declare
to_many_emps exception;
v_emp s_emp%rowtype;
v_n number;
pragma exception_init(to_many_emps,-1001);
--和一个异常的编号 绑定
begin
select count(*) into v_n from s_emp;
-- 想抛出自己的异常 sql语句就不应该抛出系统异常
if v_n>1 then
raise to_many_emps;
end if;
select * into v_emp from s_emp; --上面的语句抛出了自定义的异常 --这句sql就没有机会执行
exception
when to_many_emps then
dbms_output.put_line('my excepiton to_many_emps');
when others then
dbms_output.put_line('others');
end;
plsq的异常处理 1 要声明异常 2 根据业务的不同情况抛出异常 3 在异常块中捕获异常 注意捕获异常的顺序 others在最后
39 56
--------------------------------------------------
plsql中最重要的 过程 和函数
plsql块都是没有名字的
把完成某项功能的代码起一个名字 放入数据库中
create or replace procedure hello100
is
begin
for a in 1.. 100 loop
dbms_output.put_line('hello world');
end loop;
end;
exec hello100; --调用hello100这个过程
call hello100();
存储过程的参数
create or replace procedure hello100(var_p varchar2)
is
begin
for a in 1.. 100 loop
dbms_output.put_line(var_p);
end loop;
end;
写一个有名字的过程 带两个参数 name age 向
student(id,name,age)表中插入1条记录
--问题关键在于拼接sql
create sequence test_s_s;
create or replace procedure
addstudent(var_name varchar2,var_age number)
is
begin
insert into student
values(test_s_s.nextval,var_name,var_age);
commit;
end;
如果执行时 发现sql是一个字符串变量 那这样的sql属于动态sql create or replace procedure
addstudent(var_name varchar2,var_age number)
is
sqlstr varchar2(100);
begin
sqlstr:='insert into student
values(test_s_s.nextval,'''||var_name||''','||var_age||')'; dbms_output.put_line(sqlstr);
execute immediate sqlstr;
commit;
40 56
end;
ddl的执行 和这个完全相同
建31张表 只有名字不同 而结构完全相同
day1 .. day31
begin
execute immediate 'create table day1
(id number primary key,salary number)';
end;
作业：
写一个存储过程 建立31张结构相同 表名不同的表
在写一个存储过程 删除这31张表
------------------------------------------------
存储过程的参数
create or replace procedure
addstudent(var_name varchar2,var_age number)
is
begin
insert into student
values(test_s_s.nextval,var_name,var_age); commit;
end;
参数的默认类型 是 in -----------负责输入的参数
out-----------负责输出的参数 带回返回值 in out -------即负责输入又负责输出
void add(int *age){
*age=100;
}
int age=0;
add(&age);
age-----------100
这个存储过程是得到s_emp表中记录数
create or replace procedure
getstudentcount(var_c in out number)
is
begin
select count(*) into var_c from s_emp;
end;
这样 参数的类型如果是out类型的 那就无法在命令行中调用 只能在别的存储过程 或者匿名块中调用
declare
var_n number; --就是为调用得到人数的存储过程准备的变量 begin
getstudentcount(var_n);
41 56
dbms_output.put_line(var_n);
end;
有 out 类型或者in out类型的参数必须是变量
查看存储过程的命令是desc
总结：
in
out
in out 只要有out类型的参数必须是变量
------------------------------------------------------------- desc 过程名
过程名(按参数排放值);-----顺序赋值 位置赋值
过程名(名字赋值);-----名字赋值不能出现在位置赋值左边 -----混用第一个位置必须是位置赋值
create or replace procedure
addstudent(var_name varchar2,var_age number)
is
begin
insert into student
values(test_s_s.nextval,var_name,var_age);
commit;
end;
begin
addstudent(var_name=>'hello',var_age=>23);
end;
call 用名字赋值时有问题
--------------------------------------------------
函数 ----过程
这个存储过程是得到s_emp表中记录数
create or replace function
getstudentcountfun
return number
is
var_c number;
begin
select count(*) into var_c from s_emp;
return var_c;
end;
declare
var_n number;
var_p number;
begin
var_n:=getstudentcountfun(var_p);
42 56
dbms_output.put_line(var_n||':'||var_p);
end;
写函数数时如果出现警告信息 可以通过show errors查看
作业：
写一个过程 得到s_emp 中部门的个数
写一个函数 得到s_emp 中部门的个数
----------------------------------------------
包的概念
dbms_output.put_line('');----输出包
dbms_job -----定时任务调度包
dbms_random----生成随机数包
写一个包的简单的例子 包头如下：
create or replace package pack_test is
procedure addstudent(age number);
function getempcount return number;
end;
包体的实现
create or replace package body pack_test is
procedure addstudent(age number)
is --过程的实现
begin
dbms_output.put_line('addstudent');
end;
function getempcount return number
is --函数的实现
var_c number;
begin
var_c:=100;
return var_c;
end;
end;
select pack_test.getempcount from dual;--用dual测试包中的函数 作业:写一个包 里面有一个函数 一个过程 过程和函数如上一个作业中 得到员工数的要求相同
再写一个匿名块测试所写的函数和过程
----------------------------------------------------- 触发器
在主表 和子表的主外键关系
on delete cascade
on delete set null
如果更新主表中的数据 id=100 id=101
1.更好的维护数据的完整性
2.执行审计跟踪
43 56
3.为其他的程序发送一个触发信号
create or replace trigger s_student_t
after insert on student
declare
begin
dbms_output.put_line('trigger call success');
end;
insert into student values(1,'aa',21);
before 不管成功不成功 都会触发
after 必须是成功执行之后 触发
当然 delete语句即使没有删除到数据 也算成功执行 执行执行的结果为0行 create or replace trigger s_emp_t
after update on s_emp for each row
declare
begin
dbms_output.put_line('trigger call success');
end;
create or replace trigger s_emp_t
after update on s_emp
declare
begin
dbms_output.put_line('trigger call success');
end;
你做dml时肯定会提交 或者回滚 ，
如果触发器中出现了事务语句 就有可能不能完成事务
的特性。
for each row 就是语句影响了多少次 就触发多少次
没有就是无论影响多少次触发器只触发一次
--------------------------------------------------------------------------------------------------------------------- PRO * C
telnet IP
成功之后
proc
pro*C/C++
4 -----192.168.0.26
2 ---- 192.168.0.23
1 -----192.168.0.20
为了使用c/c++去访问数据库 要开发proc/c++的程序
开发proc程序的标准流程
1.编写.pc结尾的源文件
2.把.pc程序预处理成.c 文件 proc first.pc
3.编译 链接 gcc first.c -lclntsh
44 56
4.执行程序 ./a.out
first.pc
#include <stdio.h>
int main(){
char namepasswd[30]="openlab/open123";
char last_name[30]={0};
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT:namepasswd;
EXEC SQL select last_name into :last_name
from s_emp where id=1;
printf("%s\n",last_name);
EXEC SQL COMMIT WORK RELEASE;
}
判断一下 链接数据库是否成功
sqlca 是oracle的通信区
sqlca.sqlcode ----最近的sql语句的执行状态 0 是成功sqlca.sqlerrm.sqlerrmc ----最近的sql的错误信息
sqlca.sqlerrd[2] ----最近一条sql 影响的行数 #include <stdio.h>
int main(){
char namepasswd[30]="openlab/open123";
char last_name[30]={0};
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT:namepasswd;
if(sqlca.sqlcode==0)
{
printf("connect success\n");
}else
{
printf("connect failed\n");
}
EXEC SQL select last_name into :last_name
from s_emp where id=1;
if(sqlca.sqlcode==0)
printf("%s\n",last_name);
EXEC SQL COMMIT WORK RELEASE;
}
宿主变量
c/c++ 使用的变量叫 c变量 c++的变量
sql 语句中使用的变量 叫sql变量
即能在c c++中使用 又能再sql语句中使用的变量 称之为宿主变量
45 56 失败 -1
c中的宿主变量和c变量定义相同 只是在sql 语句中使用时加 : c++ 中宿主变量 使用也是要加: 定义必须在申明区
申明区的语法
EXEC SQL BEGIN DECLARE SECTION; 申明区的开始
宿主变量的定义 和语言中定义相同
EXEC SQL END DECLARE SECTION;
把上面的例子改成 有申明区的代码
#include <stdio.h>
int main(){
EXEC SQL BEGIN DECLARE SECTION;
char namepasswd[30]="openlab/open123";
char last_name[30]={0};
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT:namepasswd;
if(sqlca.sqlcode==0)
{
printf("connect success\n");
}else
{
printf("connect failed\n");
}
EXEC SQL select last_name into :last_name
from s_emp where id=1;
if(sqlca.sqlcode==0)
printf("%s\n",last_name);
EXEC SQL COMMIT WORK RELEASE;
}
proc 指令 输出的文件名默认总是.c oname=想要的文件名 code=cpp parse=none
1.把所有的宿主变量 放在申明区
2.预处理时 proc iname=section.pc oname=section.cpp code=cpp parse=none
3.g++ section.cpp -lclntsh
proc 中选项
iname 输入的文件名 默认是.pc
oname 输出文件名 默认 xx.pc xx.c
code 默认是c风格 c++需要用cpp
parse 默认是c的FULL none 和partia 推荐none mode 默认是oracle c++推荐用ansi
userid 用用户名/密码去检测存储过程 是否合法
46 56
指示变量 -------NULL-----nvl -----组函数
#include <stdio.h>
int main(){
exec sql include sqlca;
exec sql begin declare section;
char userpasswd[30]="openlab/open123"; char last_name[30];
short desc_var;
exec sql end declare section;
exec sql connect :userpasswd;
exec sql select last_name into:last_name:desc_var from s_emp where id=1;
printf("%hd",desc_var);
exec sql commit work release;
}
数组变量
除字符串外 只支持到一维数组 最大 到32767 #include <stdio.h>
int main(){
exec sql include sqlca;
exec sql begin declare section;
char userpasswd[30]="openlab/open123"; char last_name[25][30];
exec sql end declare section;
exec sql connect :userpasswd;
exec sql select last_name into:last_name from s_emp;
int i;
for(i=0;i<sqlca.sqlerrd[2];i++){
printf("%s\n",last_name[i]);
}
exec sql commit work release;
}
思考一个问题 如果想反映每个名字的赋值情况 怎么办？ 一个指示变量肯定搞不定 需要n个
#include <stdio.h>
int main(){
exec sql include sqlca;
exec sql begin declare section;
char userpasswd[30]="openlab/open123"; char last_name[25][30];
short desc[25];
exec sql end declare section;
47 56
exec sql connect :userpasswd;
exec sql select first_name into:last_name:desc
from s_emp;
int i;
for(i=0;i<sqlca.sqlerrd[2];i++){
printf("%s:%hd\n",last_name[i],desc[i]);
}
exec sql commit work release;
}
如果 sqlca 的信息还满足不了你的需求 你可以使用oraca
1.引入 oraca
exec sql include oraca;
2.让oraca生效
exec oracle option(oraca=yes);
3.oraca 的普通sql要保存 需设置一个字段 orastxtf
#include <stdio.h>
int main(){
exec sql include sqlca;
exec sql include oraca;
exec oracle option(oraca=yes);
oraca.orastxtf=3;
exec sql begin declare section;
char userpasswd[30]="openlab/open123";
char last_name[25][30];
short desc[25];
exec sql end declare section;
exec sql connect :userpasswd;
exec sql select first_name into:last_name:desc
from s_emp;
printf("%s\n",oraca.orastxt.orastxtc);
int i;
for(i=0;i<sqlca.sqlerrd[2];i++){
printf("%s:%hd\n",last_name[i],desc[i]);
}
exec sql commit work release;
}
plsql 的调用
1.建立一个函数 一个存储过程 以备调用
create or replace procedure getempcount(emp_c out number) is begin
48 56
select count(*) into emp_c from s_emp; end;
create or replace function getempcountfun return number is
emp_c number;
begin
select count(*) into emp_c from s_emp;
return emp_c;
end;
1.调用plsql 需要在这个格式下调用
exec sql execute
begin 在这里调用 注意函数的调用 end;
end-exec;
2.proc 要加 userid=openlab/open123 sqlcheck=semantics
3.gcc callplsql.c -lclntsh
#include <stdio.h>
int main(){
exec sql include sqlca;
exec sql begin declare section;
char userpasswd[30]="openlab/open123";
int emp_pro;
int emp_fun;
exec sql end declare section;
exec sql connect :userpasswd;
exec sql execute
begin
getempcount(:emp_pro);
:emp_fun:=getempcountfun();
end;
end-exec;
printf("pro:%d\n fun:%d",emp_pro,emp_fun);
exec sql commit work release;
}
------------------------------------------------- oracle 的链接
echo $ORACLE_SID ------oracle的实例名
同一个数据库 多个账户
char userp[30]="openlab/open123";
char userp2[30]="scott/tiger";
exec sql connect:userp;
exec sql sqlstr;
exec sql connect:userp2;
49 56
exec sql sqlstr; 区分不开到底是哪个账户
#include <stdio.h>
int main(){
exec sql include sqlca;
exec sql begin declare section;
char userpasswd[30]="openlab/open123";
char userpasswd2[30]="scott/tiger";
char db1[20]="a";
char db2[20]="b";
exec sql end declare section;
exec sql connect :userpasswd at:db1;
exec sql at:db1 select first_name from s_emp where id=1; exec sql connect :userpasswd at:db2;
exec sql at:db2 select first_name from s_emp where id=1; printf("pro:%d\n fun:%d",emp_pro,emp_fun);
exec sql at:db1 commit work release;
exec sql at:db2 commit work release;
}
如何链接 远程数据库
192.168.0.23 ------------192.168.0.20
借助一个文件
cd $ORACLE_HOME/network/admin
tnsnames.ora 这个文件中描述符 负责远程链接
CAH_192.168.0.26 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.26 )
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tarena)
)
)
create database link mylink3
CONNECT TO openlab IDENTIFIED BY open123
Using 'CAH_192.168.0.26';
完全等价于：
create database link mylink3
CONNECT TO openlab IDENTIFIED BY open123
Using '
(DESCRIPTION =
(ADDRESS_LIST =
50 56
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.26)(PORT = 1521)) )
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tarena)
)
)';
注意 host 就是要连接的服务器
SERVICE_NAME 就是对应的oracle数据库的ORACLE_SID
192.168.0.23
#include <stdio.h>
int main(){
exec sql include sqlca;
exec sql begin declare section;
char userpasswd[30]="openlab/open123";
char last_name[30];
exec sql end declare section;
exec sql connect :userpasswd;
exec sql select first_name into :last_name
from s_emp@mylink3 where id=2;
printf("%s\n",last_name);
exec sql commit work release;
}
1.telnet 到一个服务器 这个服务器上必须装有oracle
如果登录一个数据库的情况下想访问多个远程 就需要
建立多个link
80
8080 -----tomcat
1521 -----oracle
3306 -----mysql
1433 -----sqlserver
上面的是先登录一个数据库 连接多个远程数据库
#include <stdio.h>
int main(){
exec sql include sqlca;
exec sql begin declare section;
char userpasswd[30]="openlab/open123";
char userpasswd2[30]="scott/tiger";
char last_name[30];
char db26[20]="db26tarena";
char db20[20]="db20tarena";
exec sql end declare section;
51 56
exec sql connect :userpasswd at:db26
using 'CAH_192.168.0.26';
exec sql connect :userpasswd2 at:db20
using 'CAH_192.168.0.20';
exec sql at:db26 select first_name into :last_name from s_emp where id=2;
exec sql at:db20 select first_name into :last_name from s_emp where id=2;
printf("%s\n",last_name);
exec sql at:db26 commit work release;
exec sql at:db20 commit work release;
}
注意：用at 区分连接 用 using 来连接远程数据库
-----------------------------------------
proc 中的异常处理
exec sql whenever {sqlerror,notfound,sqlwarning}
{do,continue,break ,goto,stop} { processErrorFuncton(),标签}; whenever 一般出现在第一句
如果有多个则向上找 最近一条
#include <stdio.h>
exec sql include sqlca; //在第一行 下面的函数中要使用 void sqlerrorp();
void sqlnotfoundp();
int main(){
exec sql begin declare section;
char userp[30]="openlab/open123";
char last_name[30]={0};
exec sql end declare section;
exec sql whenever sqlerror do sqlerrorp();
exec sql whenever notfound do sqlnotfoundp();
exec sql connect:userp;
/*exec sql select last_name into :last_name from s_emp where id=1000;
exec sql update s_emp set last_name=:last_name where id=1000;*/
exec sql delete from s_emp where id=2;
exec sql commit work release;
}
void sqlerrorp(){
exec sql whenever sqlerror continue;
printf("%s\n",sqlca.sqlerrm.sqlerrmc);
52 56
printf("call sqlerrorp proccess\n");
//dml 语句要回滚
exec sql commit work release;
}
void sqlnotfoundp(){
printf("%s\n",sqlca.sqlerrm.sqlerrmc); printf("%s\n","insert log table message"); }
使用结构体 存 取数据
#include <stdio.h>
struct {
int id;
char name[30];
}emp_rec;
int main(){
char namepasswd[30]="openlab/open123"; char last_name[30]={0};
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT:namepasswd;
EXEC SQL select id,last_name into :emp_rec from s_emp where id=1;
printf("%d:%s\n",emp_rec.id,emp_rec.name); EXEC SQL COMMIT WORK RELEASE;
}
注意点：不能嵌套结构 和联合
----------------------------------
proc 中的游标
1.声明游标
exec sql declare cursor 游标名 is sql语句;
2.打开游标
exec sql open 游标名;
3.提取数据
exec sql fetch 游标名 into 变量;
4.关闭游标
exec sql close 游标名;
#include <stdio.h>
struct {
int id;
char name[30];
}emp_rec;
int main(){
char namepasswd[30]="openlab/open123"; char last_name[30]={0};
53 56
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT:namepasswd;
EXEC SQL declare emp_cursor cursor for select id,last_name from s_emp;
EXEC SQL OPEN emp_cursor;
int i=0;
/*for(i=0;i<sqlca.sqlerrd[2];i++){
EXEC SQL FETCH emp_cursor into :emp_rec; }*/
while(1){
EXEC SQL WHENEVER NOTFOUND DO BREAK; EXEC SQL FETCH emp_cursor into :emp_rec; printf("%d:%s\n",emp_rec.id,emp_rec.name); }
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
}
proc 还对游标的功能做了增强 增加了滚动游标 #include <stdio.h>
struct {
int id;
char name[30];
}emp_rec;
int main(){
char namepasswd[30]="openlab/open123"; char last_name[30]={0};
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT:namepasswd;
EXEC SQL declare emp_cursor scroll cursor for select id,last_name from s_emp;
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH last emp_cursor into :emp_rec; printf("%d:%s\n",emp_rec.id,emp_rec.name);
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
}
动态sql
plsql 中 建表 还有当sql是个字符串
proc 中建表可以是普通 的sql了
#include <stdio.h>
int main(){
char namepasswd[30]="openlab/open123"; 54 56
char sqlstr[100]={0};
char sqlstr2[100]={0};
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT:namepasswd;
//printf("please input a sqlstr\n");
gets(sqlstr2);
//printf("%s\n",sqlstr2);
EXEC SQL EXECUTE IMMEDIATE :sqlstr2;
EXEC SQL COMMIT WORK RELEASE;
}
192.168.0.23 数据库的权限不允许建表
sqlplus 直接建表不允许
第二种形式动态sql
为了提高效率 加入了预编译
#include <stdio.h>
int main(){
char namepasswd[30]="openlab/open123";
int id=10;
char name[30]="openlab";
int age=20;
char sqlstr[100]="insert into test_student values(:b,:c,:d)";
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT:namepasswd;
EXEC SQL PREPARE s from :sqlstr;
EXEC SQL EXECUTE s using :id,:name,:age;
EXEC SQL COMMIT WORK RELEASE;
}
create table test_student as select id,last_name,salary from s_emp
where 1=2;
第三种形式 为了解决 select的动态性
#include <stdio.h>
int main(){
char namepasswd[30]="openlab/open123";
int id=10;
char name[30]={0};
int age;
char sqlstr[100]="select id,last_name,salary from test_student where id=:b"; EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT:namepasswd;
EXEC SQL PREPARE s from :sqlstr;
EXEC SQL DECLARE stu_cur cursor for s;
EXEC SQL OPEN stu_cur using :id;
55 56
EXEC SQL FETCH stu_cur into :id,:name,:age;
EXEC SQL CLOSE stu_cur;
printf("%s:%d",name,age);
EXEC SQL COMMIT WORK RELEASE;
}
8 13
--------------------------------------------------------------- #include <stdio.h>
int main()
{
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char passwd[30]="hfsd1106/tarena";
char first_name[1]={0};
char last_name[1]={0};
int id_no;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT:passwd;
EXEC SQL select id into :id_no from s_emp where id=1;
EXEC SQL select last_name into :last_name
from s_emp where id=1;
//EXEC SQL select first_name into :first_name from s_emp where id=1; printf("%d\n",sqlca.sqlcode);
printf("%s\n",sqlca.sqlerrm.sqlerrmc);
//printf("%s\n",first_name);
printf("%d\n",id_no);
EXEC SQL COMMIT WORK RELEASE;
}
exec sql execute
begin
end
end-exec
56 56