贴图很麻烦 有想要带图的可以去我的百度文库下,也可以留言我回尽快发出,
toad(青蛙) plsqldevelope 开发oracle客户端
sys/密码 as sysdba showuser;
alter user scott(用户名) account unlock; 用户解锁
查询语句 DML数据操作语言 DDL数据定义语句
desc emp 描述emp表(雇员表) 表头和类型
conn scott/tiger 连接登陆
/ 在Oracle中，用来终止SQL语句。更准确的说，是表示了“运行现在位于缓冲区的SQL代码”。正斜杠也用作分隔项。
表结构
oracle中用ed命令打开编 辑器时，默认的是notepad，若我想用其他编辑器编写，该如何设置呢？在$ORACLE_HOME/dbs/ 下查找login.sql文件，如果没有就创建一个空文件，然后在文件中加入这样一句话：define _editor=‘C:\Program Files\EditPlus 2\editplus.exe‘"＝"后面是你想要编辑器的路径，你可以根据情况修改。客服端也可以,也是改那个login.sql文件;
Varchar2 支持国际化不用varchar了 可变字符串
Number(7,2) 7位数字 2位 小数
Slect * from salgrade;
Select 2*3 from dual; 就一个结果(因为此表就一条数据),否则有多少条数据就显示多少个结果
Select sysdate from dual;
Select ename ,sal*12 “anuual sal” from emp; 如果别名有空格或汉子应该加引号””,
引号是保持住格式;否子 会变为大写或 错误
没有津贴 会为null值 0!=null
任何含有null 的数学表达式 结果为 null
Select ename||sal from emp; 字符串连接相当于java中的+
select ename||'1234asd' from emp; 字符串连接单引号
select ename||'1234''asd' from emp; 两个单引号代表一个单引号
select distinct deptno from emp; 去掉重复值
select distinct deptno,job from emp; 去掉组合重复的
select * from emp where deptno =10; 过滤
select * from emp where sal >1500;
SQL> select ename,sal,deptno from empwhere deptno <>10; 不等于<>
select ename,sal,deptno from emp whereename>'CBA'; 比较字符串(比较其ascii值)
select ename,sal,deptno from emp where salbetween 800 and 1500; 包含800 1500
select ename,sal,deptno from emp where sal>=800 and sal <=1500;
select ename,sal,deptno from emp where commis null; 选空值
select ename,sal,deptno from emp where commis not null;
select ename,sal,deptno from emp where salin (800,1500,2000);
select ename,sal,deptno from emp whereename in ('SMITH','KING','ABC');
日期处理
select ename,sal,hiredate from emp wherehiredate > '20-2月-81';
select ename,sal,hiredate from emp wheredeptno = 10 or sal >1000;
select ename,sal,hiredate from emp wheresal not in (800,1500);
select ename from emp where ename like'%ALL%';
select ename from emp where ename like'_A%';
select ename from emp where ename like'%\%'; \ 转义字符
select ename from emp where ename like'%$%'escape '$'; 自己定义转义字符为$
select * from dept order by deptno desc ; 降序排列 默认升序asc
select ename,sal,deptno from emp order by deptno asc,ename desc;
先deptno升序 再名字倒叙排列
select ename,sal*12 annual_sal from empwhere ename not like'_A%' and sal>800
order by sal desc;
常用的SQL 函数
单行函数 多行函数
select lower(ename) from emp; 转换成小写
select ename from emp where lower(ename)like '_a%'; 查找含有a 包括大小写
select upper(ename) from emp; 转大写
select substr(ename,1,3) from emp;
从第一个截 共截3个
select chr(65) from dual; 把ASC 码转换字符
select ASCII('A') from dual; ASC 转
select round(23.652) from dual; 四舍五入
select round(23.652,2) from dual; 四舍五入两位
select round(23.652,-1) from dual; 四舍五入到十位
把数字或日期转换格式
select to_char(sal,'$99,999.9999') fromemp;
select to_char(sal,'l99,999.9999') fromemp; 本地货币符号
select to_char(sal,'l00000.0000') from emp; 0代表一位数字,该位置没有数字强制显示
日期转换
select to_char (hiredate,'YYYY-MM-DDHH:MI:SS') FROM emp;
select to_char (sysdate,'YYYY-MM-DD HH24:MI:SS')FROM dual;
select ename,hiredate from emp wherehiredate >to_date('1981-2-20 12:34:56' ,'YYYY-MM-DD HH24:MI:SS');
select sal from emp where sal > to_number('$1,250.00','$9,999.99');
select ename,sal*12 +nvl(comm,0) from emp; 如果comm值为null 用0替换
单行函数 有多少条记录就有多少条输出 输入以一条记录为单位,最后结果为一条
组函数,多行函数 给我很多条记录 只产生一条输出
SUM / COUNT / AVG / MAX /MIN
select max(sal),min(sal),avg(sal) from emp;
select max(sal),min(sal),round(avg(sal),2)from emp;
selectmax(sal),min(sal),to_char(avg(sal),'9999.99') from emp;
select sum(sal) from emp;
select count(*) from emp; 求一共多少记录
select count(*) from emp where deptno=10; 一共有多少人(不为空值的字段)
select count(distinct deptno) from emp;
group by
select avg(sal) from emp group by deptno;
select deptno,job, avg(sal) from emp groupby deptno,job; 根据两个的组合分组
Ename 有可能很多个 max 只能有一个输出 匹配不起来
出现在select 类表里的字段 没有出现在主函数里必须穿现在group by 里
Where 对单条语句进行过滤
Having对分组进行限制
select avg(sal),deptno from emp group bydeptno having avg(sal)>2000;
执行顺序 就是写的顺序
总结
select avg (sal) from emp where sal>1200 group by deptno
having avg(sal) >1500 order byavg(sal) desc;
子查询 可以在where 和from
select ename from emp where sal=(selectmax(sal) from emp);
哪些人工资位于平均工资之上
select ename from emp where sal >(select avg(sal) from emp);
按部门分组 每个部门挣钱最多的人 名字 部门编号
select ename,deptno,sal from emp where salin (select max (sal)from emp group by deptno ) ; 错误的写法(如果20 也有3000)
select ename,sal from emp join (select max(sal) max_sal,deptno from empgroup by deptno) t on (emp.sal = t.max_sal and emp.deptno =t.deptno);
每个部门 平均薪水的薪水等级
select deptno,avg(grade) from(selectdeptno,ename,grade from emp join salgrade s on(emp.sal between s.losal ands.hisal))t group by deptno;
查找雇员的经理人是谁 自连接 为表起2个名 当成连个表用
select e1.ename,e2.ename from emp e1,emp e2where e1.mgr =e2.empno;
连接条件 过滤条件分开 SQL 1999 where 只写数据过滤条件
select dname,ename from emp cross join dept 交叉连接 笛卡尔
等值连接
select ename,dname from emp join depton(emp.deptno=dept.deptno);
select ename,dname from emp,dept whereemp.deptno = dept.deptno;(以前版本)
select ename,dname from emp join deptusing(deptno);
查找emp和dept表中deptno相同的部分。 不推荐使用(要求多,类型一样,删改繁琐)
非等值连接
select ename, grade from emp e joinsalgrade s on (e.sal between s.losal and s.hisal);
(三表查找）
select ename, dname, grade from emp e
join dept d on(e.deptno = d.deptno)
join salgrade s on (e.sal between s.losaland s.hisal)
where ename not like '_A%';
select e1.ename,e2.ename from emp e1 joinemp e2 on(e1.mgr=e2.empno);
select e1.ename,e2.ename from emp e1 leftjoin emp e2 on (e1.mgr =e2.empno);
//左外连接把左边没有满足条件的数据也取出来
select ename, dname from emp e right joindept d on(e.deptno = d.deptno);
//右外连接
select ename,dname from emp e full joindept d on(e.deptno=d.deptno) 全外连接
//求每个部门平均薪水的等级
select deptno, avg_sal, grade from (selectdeptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
求部门中薪水最高的
select ename,sal from emp join (selectmax(sal) max_sal, deptno from emp group by deptno) t on (emp.sal=t.max_sal andemp.deptno=t.deptno);
求部门中薪水等级的平均值
select deptno,avg(grade) from(selectdeptno,ename,grade from emp join salgrade s on(emp.sal between s.losal ands.hisal))t group by deptno;
查找雇员中哪些是经理人
select ename from emp where empno in(select distinct mgr from emp);
// 面试题 不用组函数max()求薪水的最大值 自连接
select sal from emp where sal not in(selectdistinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal));
平均薪水最高的部门 和名称
select max(avg_sal) from (select avg(sal)avg_sal ,deptno from emp group by deptno);求了部门的最高平均薪水
select deptno, max_sal from
(select avg(sal) max_sal,deptno from empgroup by deptno)
where max_sal =
(select max(max_sal) from
(select avg(sal) max_sal,deptno from emp group by deptno)
);//求平均薪水最高的部门名称和编号。
select deptno,avg_sal from(select avg(sal)avg_sal,deptno from emp group by
deptno)where avg_sal=(select max(avg(sal)) from emp group by deptno);
组函数 嵌套 最多嵌套两层
• select t1.deptno, grade, avg_sal from
• (select deptno, grade, avg_salfrom
• (select deptno, avg(sal) avg_sal from empgroup by deptno) t
• join salgrade s on(t.avg_sal betweens.losal and s.hisal)
• ) t1
• join dept on (t1.deptno =dept.deptno)
• where t1.grade =
• (
• select min(grade) from
• (select deptno, grade, avg_sal from
• (select deptno, avg(sal) avg_sal from empgroup by deptno) t
• join salgrade s on(t.avg_sal betweens.losal and s.hisal)
• )
• )//求平均薪水等级最低的部门的名称哈哈 确实比较麻烦
平均薪水平均薪水等级求等级值等级对应的deptno
创建视图简化上面 视图就是一张表 一个子查询 虚表
视图通常以v$开头
grant create table,create view to scott; 授权
• ---创建视图出现权限不足时候的解决办法：
• conn sys/admin as sysdba;
• 显示：连接成功 Connected
• grant create table, create view toscott;
• 显示: 授权成功 Grantsucceeded
• create view v$_dept_avg_sal_info as
• select deptno, grade, avg_sal from
• (select deptno, avg(sal) avg_sal fromemp group by deptno) t
• join salgrade s on(t.avg_sal betweens.losal and s.hisal);
select dname,t1.deptno,grade,avg_sal from
v$_dept_avg_sal_info t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from v$_dept_avg_sal_info
)
• -------求比普通员工最高薪水还要高的经理人的名称 -------
• select ename, sal from emp where empnoin
• (select distinct mgr from emp wheremgr is not null)是经理人
• and sal >
• (
• select max(sal) from emp where empnonot in
• (select distinct mgr from emp wheremgr is not null)
普通员工最高薪水
• )
• ---面试题：比较效率
• select * from emp where deptno = 10 and enamelike '%A%';
//好，将过滤力度大的放在前面
• select * from emp where ename like '%A% anddeptno = 10;
DML 语句 数据操作语言
每个用户有一个表空间 操作自己空间的表 所以是多用户的
删除用户
1-- backup scott 为了导入新用户
exp 备份
-----2 创建用户
• create user guohailong identified(认证) by guohailong default tablespace users quota(配额) 10M on users
• grant createsession(给它登录到服务器的权限),createtable, create view to guohailong
create user 1 identified by 1 defaulttablespace users quota 10Mon users; 创建用户账号密码为 1
-----分配权限
Grant create session ,create table, createview to 1
Drop user 删除用户
-----3 导入 import the data
Imp
插入
insert into dept values (50, 'ame','bj');
• -----------事务回退语句--------
• rollback; 所有语句都回退
• -----------事务确认语句--------
• commit;//此时再执行rollback无效
事务(Transaction)一系列操作要么同时完成 要么不完成
updata insert等也算事务事务起始于第一条语句
执行/碰到 DDL DCL(授权) 语句事务自动提交
当正常断开连接的时候例如exit,事务自动提交。 当非正常断开连接，例如直接关闭dos窗口或关机，事务回滚
• -----表的备份
• create table dept2 as select * fromdept;
子查询插入 (表的结构同)
insert into dept2 select * from dept;
insert into dept2 (deptno,dname) values(60,'game2');
insert into dept2 values(50,'game','bj');
未进行排序 先插入先显示
（使用虚字段rownum 只能使用 < 或 = 要使用 > 必须使用子查询）
• ----求10名雇员以后的雇员名称--------
• select ename from (select rownum r,ename fromemp) where r > 10;
select ename from (select rownum r,enamefrom emp ) where r=10;
• ----求薪水最高的前5个人的薪水和名字---------
select ename, sal from (select ename, salfrom emp order by sal desc) where rownum <=5;
• ----求按薪水倒序排列后的第6名到第10名的员工的名字和薪水--------
• select ename, sal from
• (select ename, sal, rownum rfrom
• (select ename, sal from emporder by sal desc)
• )
• where r>=6 and r<=10
试题:
• 有3个表S，C，SC
• S（SNO，SNAME）代表（学号，姓名）
• C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
• SC（SNO，CNO，SCGRADE）代表（学号，课号成绩）
• 问题：
• 1，找出没选过“黎明”老师的所有学生姓名。
• 2，列出2门以上（含2门）不及格学生姓名及平均成绩。
• 3，即学过1号课程有学过2号课所有学生的姓名。
答案：
1、
select sname from s join sc on(s.sno =sc.sno) join c on (sc.cno = c.cno) where cteacher <> '黎明';
2、
select sname where sno in (select sno fromsc where scgrade < 60 group by sno having count(*) >=2);
3、
select sname from s where sno in (selectsno, from sc where cno=1 and cno in
(select distinct sno from sc where cno =2);
update emp2 set sal = sal*2 ,ename=ename||'-' where deptno = 10;
delete from emp2 where deptno<25;
DDL 数据定义 建表 建视图 建其他
create table t (a varchar2 (10));可变字符串最大为10
char 定长字符串 输入多少都占固定长度 (效率问题,定长效率高<如:数组>)
数组 内存里一段连续的内存 hashtable 拿空间换时间
Number (8,3) 数字8位 小数3 位
Date 日期时间
Long 变长字符串 最长2G
Varchar2 最多4k 4096字节
• --------------创建表--------------
• create table stu
• (
• id number(6),
• name varchar2(20) constraint(约束) stu_name_mm not null,
• sex number(1),
• age number(3),
• sdate date,
• grade number(2) default 1,
• class number(4),
• email varchar2(50) unique
• );
给name字段加入 非空 约束，并给约束一个名字，若不取，系统默认取一个-
把约束写在字段后面-- 字段级约束 (名字和email 组合不能重复,写不了)
表级约束
• --------------两个字段的组合不能重复约束：表级约束-------------
• create table stu
• (
• id number(6),
• name varchar2(20) constraint stu_name_mmnot null,
• sex number(1),
• age number(3),
• sdate date,
• grade number(2) default 1,
• class number(4),
• email varchar2(50)，
constraintstu_id_pk primary key (id),
• constraint stu_name_email_uni unique(email,name)
• );
五个约束条件: 非空唯一 主键 外键 check
约束条件 在数据库中算是一个对象
非空: not null
唯一: unique 不能重复 可以随意插空值,两个空值不算重复
主键: primary key 可以唯一标识整条记录,(语法上:唯一非空的组合)
逻辑,代表单独的每条不同记录,(现实的逻辑)
constraint stu_id_pk primary key (id),
外键: 建立在一张表的两个字段,两个表的两个字段
被参考字段必须是主键 有子记录不能删(有关联字段)
• --------------外键约束 被参考字段必须是主键 -------------
• create table stu
• (
• id number(6),
• name varchar2(20) constraint stu_name_mmnot null,
• sexnumber(1),
• age number(3),
• sdate date,
• grade number(2) default 1,
• class number(4) //referencesclass(id),
• email varchar2(50)，
• constraint stu_class_fk foreign key (class)references class(id),
• constraint stu_id_pk primary key (id),
• constraint stu_name_email_uniunique(email, name)
• );
•
• create table class
• (
• id number(4) primary key,
• name varchar2(20) not null
• );
试题
一个简单的table有100条以上的信息，其中包括
产品 颜色 数量
产品1 红色 112
产品1 蓝色 142
产品2 红色 234
产品2 蓝色 null
产品3 红色 444
产品1 红色 120
产品3 红色 null
。。。。。。。。。。
请用SQL 完成
1、按产品分类，列出各类产品中红色数量大于蓝色数量的产品名称和差额数量。
2、按产品分类，按下面格式进行统计
产品 红色 蓝色
解答：
Sql代码
1. createtable products
2. (
3. pnamevarchar2(20),
4. pcolorvarchar2(20),
5. pnum number
6. );
7.
8. insertinto products values('产品1','红色',112);
9. insertinto products values('产品1','蓝色',142);
10. insertinto products values('产品2','红色',234);
11. insertinto products values('产品2','蓝色',null);
12. insertinto products values('产品3','红色',444);
13. insertinto products values('产品1','红色',120);
14. insertinto products values('产品3','红色',null);
15. commit;
1、参考答案
Sql代码
1. selectp1.pname,(p1.sum1-p2.sum2) as 差额 from
2. (
3. selectpname,pcolor,sum(nvl(pnum,0)) sum1 from products
4. wherepcolor = '红色'
5. groupby pname,pcolor
6. )p1
7. leftjoin
8. (
9. selectpname,pcolor,sum(nvl(pnum,0)) sum2 from products
10. wherepcolor = '蓝色'
11. groupby pname,pcolor
12. )p2
13. onp1.pname = p2.pname
14. wherep1.sum1 > p2.sum2
15. unionall
16. selectpname,sum(pnum) as 差额 from products
17. wherepcolor = '红色' and pname not in
18. (
19. select pname from products where pcolor = '蓝色' group by pname
20. )
21. groupby pname
结果：
PNAME 差额
产品1 90
产品2 234
产品3 444
2、参考答案
Sql代码
1. selectp1.pname as 产品,nvl(p1.sum1,0)as 红色 ,nvl(p2.sum2,0) as 蓝色 from
2. (
3. selectpname,pcolor,sum(nvl(pnum,0)) sum1 from products
4. wherepcolor = '红色'
5. groupby pname,pcolor
6. )p1
7. fulljoin
8. (
9. selectpname,pcolor,sum(nvl(pnum,0)) sum2 from products
10. wherepcolor = '蓝色'
11. groupby pname,pcolor
12. )p2
13. onp1.pname = p2.pname
结果：
产品 红色 蓝色
产品1 232 142
产品2 234 0
产品3 444 0
修改表结构
• ---------------修改表结构，添加字段------------------
• alter table stu add(addr varchar2(29));
• ---------------删除字段--------------------------
• alter table stu drop (addr);
• ---------------修改表字段的长度------------------
• alter table stu modify(修改) (addr varchar2(50));
//更改后的长度必须要能容纳原先的数据精度
• ----------------删除约束条件----------------
• alter table stu drop constraint 约束名
• -----------修改表结构添加约束条件---------------
• alter table stu add constraint stu_class_fk foreign key(class) references class (id);
• ---------------数据字典表----------------
• desc dictionary;
• //数据字典表共有两个字段 table_name comments
• //table_name主要存放数据字典表的名字
• //comments主要是对这张数据字典表的描述
Desc user_tables; 当前用户下有多少张表
---查看当前用户下面所有的表、视图、约束-----数据字典表user_tables---
• select table_name from user_tables;
• select view_name from user_views;
• select constraint_name fromuser_constraints;
索引
-------------索引------------------
• create index idx_stu_email on stu(email);
// 在stu这张表的email字段上建立一个索引：idx_stu_email
create index idx_stu_email on stu(email,class);
两个字段组合建索引
---------- 删除索引 ------------------
• drop index idx_stu_email;
---------查看所有的索引----------------
• select index_name from user_indexes;
加主键 或唯一约束 自动加索引
索引-读取效率高,插入效率低,访问量大,不轻易建立索引,索引 占据大量空间
视图
-----------创建视图-------------------
视图 是一个子查询
• create view v$stu as selesct id,name,agefrom stu;
• 视图的作用: 简化查询 保护我们的一些私有数据，通过视图也可以用来更新数据，但是我们一般不这么用缺点：要对视图进行维护(改表结构时,需更新)
-----------创建序列------------
产生一个唯一的不间断的数字序列 一般做主键 一般一个表对应一个
• create sequence seq;//创建序列
• select seq.nextval from dual;// 查看seq序列的下一个值
• drop sequence seq;//删除序列
insert into articlevalues(seq.nextval,'a','b');
三范式
------------数据库的三范式--------------
（1）、要有主键，列不可分
（2）、不能存在部分依赖：当有多个字段联合起来作为主键的时候，不是主键的字段不能部分依赖于主键中的某个字段 (多对多设计时)
（3）、不能存在传递依赖
姓范制定设计数据库的规则,最求 不存在冗余数据
=================================PL/SQL===============================
一门语言: 数据类型 语法 PL 过程语言
匿名块 没名字的程序
-------------------在客户端输出helloworld--------------------
set serveroutput on;
//默认是off，设成on是让Oracle可以在客户端输出数据
• begin
• dbms_output.put_line('helloworld');
• end;
• /
• ----------------pl/sql变量的赋值与输出---------------------
• declare
• v_name varchar2(20);//声明变量v_name变量的声明以v_开头
• begin
• v_name := 'myname'; 赋值
• dbms_output.put_line(v_name);
• end;
• /
• -----------pl/sql对于异常的处理(除数为0)-------------
• declare
• v_num number := 0;
• begin
• v_num := 2/v_num;
• dbms_output.put_line(v_num);
• exception 当产生异常时执行
• when others then 当其他情况出现,没写原来情况,即所有情况
• dbms_output.put_line('error');
• end;
• /
----------变量的声明----------
变量声明的规则
1.变量名不能够使用保留字，如from,select等
2.第一字符必须是字母。
3.变量名最多包含30个字符
4.不要与数据库的表或者列同名
5.每一行只能声明一个变量
• binary_integer:整数，主要用来计数而不是用来表示字段类型
计数时 比number效率高 数组下标
• number:数字类型
• char:定长字符串
• varchar2：变长字符串
• date：日期
• long：字符串，最长2GB
• boolean：布尔类型，可以取值true，false，null//最好给一初值
declare
v_temp number(1);
v_count binary_integer:=0;
v_sal number(7,2):=4000.00;
v_date date:=sysdate;
v_pi constant number(3,2):=3.14;
v_valid boolean:=false;
v_name varchar2(20) not null:='myname';
begin
dbms_output.put_line('v_temp value:'||v_temp);
end;
用--可以注释一行 打印不了 Boolean 类型
----------变量的声明，使用 '%type'属性-------------
• declare
• v_empno number(4);
• v_empno2 emp.empno%type; emp表的 empno 类型
• v_empno3 v_empno2%type; %type变量的属性
• begin
• dbms_output.put_line('Test');
• end;
//使用%type属性，可以使变量的声明根据表字段的类型自动变换，
省去了维护的麻烦，而且%type属性，可以用于变量身上
复合变量
----------Table变量类型(table表示的是一个数组)--------------
• declare
• type type_table_emp_empno is table ofemp.empno%type index bybinary_integer; 定义一个新类型
• v_empnos type_tabletype_table_empno;
• begin
• v_empnos(0) := 7345;
• v_empnos(-1) :=9999;
• dbms_output.put_line(v_empnos(-1)); 下标可以为负值
• end;
• -----------------Record变量类型 ---相当于java中的类,含有很多部分
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno:=50;
v_temp.loc:='aaaa';
v_temp.loc:='bj';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
• -----------使用 %rowtype声明record变量 修改表时,下面不用改------
declare
v_temp dept%rowtype; 行的类型,与行的分发同
begin
v_temp.deptno:=50;
v_temp.loc:='aaaa';
v_temp.loc:='bj';
dbms_output.put_line(v_temp.deptno||''||v_temp.dname);
end;
• declare
• v_ename emp.ename%type;
• v_sal emp.sal%type;
• begin
• select ename,sal into v_ename,v_sal fromemp where empno = 7369;
• dbms_output.put_line(v_ename || '' ||v_sal);
• end;
• -------- pl/sql语句的应用 %rowtype --------------
• declare
• v_emp emp%rowtype;
• begin
• select * into v_emp from emp whereempno=7369;
• dbms_output.put_line(v_emp.ename);
• end;
• -------------pl/sql语句的应用 插入--------------
• declare
• v_deptno dept.deptno%type := 50;
• v_dname dept.dname%type :='aaa';
• v_loc dept.loc%type := 'bj';
• begin
• insert into dept2values(v_deptno,v_dname,v_loc);
• commit;
• end;
declare
v_deptno emp2.deptno%type:=50;
v_count number;
begin
--update emp2 set sal=sal/2 where deptno=v_deptno; 0条
--select deptno into v_deptno from emp2where empno=7369; 1条
select count(*) into v_count fromemp2; 1条
（select必须和into一起使用）
dbms_output.put_line(sql%rowcount ||'条记录被影响');
(sql为关键字，代表刚执行的语句)
commit;
end;
DDL数据定义/DCL 授权
• begin
• execute(执行) immediate(立即) 'create table T (nnn varchar(30)default ''a'')';
• end;
判断循环
--------------------------if语句------------------------------------
• declare
• v_sal emp.sal%type;
• begin
• select sal into v_sal from emp whereempno = 7369;
• if(v_sal < 2000) then
• dbms_output.put_line('low');
• elsif(v_sal > 2000) then
• dbms_output.put_line('middle');
• else
• dbms_output.put_line('height');
• end if;
• end;
• -------------------循环 =====do while
• declare
• i binary_integer := 1;
• begin
• loop 开始
• dbms_output.put_line(i);
• i := i + 1;
• exit when (i>=11);
• end loop; 结束
• end;
• ---------------------while---------------------------
• declare
• j binary_integer := 1;
• begin
• while j < 11 loop
• dbms_output.put_line(j);
• j:=j+1;
• end loop;
• end;
• ---------------------for------------------------
• begin
• for k in 1..10 loop
• dbms_output.put_line(k);
• end loop;
• for k in reverse 1..10 loop 逆序
• dbms_output.put_line(k);
• end loop;
• end;
• -----------------------异常(1) 错误处理 -----------
• declare
• v_temp number(4);
• begin
• select empno into v_temp from empwhere deptno = 10;
• exception
• when too_many_rows then
• dbms_output.put_line('太多记录了');
• when others then
• dbms_output.put_line('error');
• end;
• 、-----------------------异常(2)
• declare
• v_temp number(4);
• begin
• select empno into v_temp from empwhere empno = 2222;
• exception
• when no_data_found then
• dbms_output.put_line('太多记录了');
• end;
• ----------------------创建序列
• create sequence seq_errorlog_id startwith 1 increment by 1;
• ----错误处理(用表记录：将系统日志存到数据库便于以后查看)--------
•
• 创建日志表:
• create table errorlog
• (
• id number primary key,
• errcode number,
• errmsg varchar2(1024),
• errdate date
• );
---------------------------------------------------------------------
• declare
• v_deptno dept.deptno%type := 10;
• v_errcode number;
• v_errmsg varchar2(1024);
• begin
• delete from dept where deptno =v_deptno;
• commit;
• exception
• when others then
• rollback;
• v_errcode := SQLCODE;
• v_errmsg := SQLERRM;
• insert into errorlog values(seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate);
• commit;
• end;
PL-SQL 能做的 java 能做, 某些特殊用
---------------PL/SQL中的重点cursor(游标)和指针的概念差不多 -------
• declare
• cursor c is
• select * from emp; //此处的语句不会立刻执行，而是当下面的open c的时候，才会真正执行 ,取出一个结果集
• v_emp c%rowtype;
• begin
• open c;
• fetch c into v_emp;
fetch 攫取拿,拿出后游标自动向下移一格
• dbms_output.put_line(v_emp.ename); //这样会只输出一条数据 134将使用循环的方法输出每一条记录
• close c; 清内存
• end;
游标 是指在结果集(一个查询结果)的指针---迭代器 iterator
-----------------使用do while 循环遍历游标中的每一个数据---------
• declare
• cursor c is
• select * from emp;
• v_emp c%rowtype;
• begin
• open c;
• loop
• fetch c into v_emp;
• (1) exit when (c%notfound);
还有isopen属性判断游标是否打开,found属性,rowcount属性
//notfound是oracle中的关键字，作用是判断是否还有下一条数据
• (2)dbms_output.put_line(v_emp.ename);
//(1)(2)的顺序不能颠倒，最后一条数据，不会出错，会把最后一条数据，再次的打印一遍
• end loop;
dbms_output.put_line(c%rowcount);
• close c;
• end;
------------------------while循环，遍历游标-----------------------
• declare
• cursor c is
• select * from emp;
• v_emp emp%rowtype;
• begin
• open c;
• fetch c into v_emp;
• while(c%found) loop
• 1. dbms_output.put_line(v_emp.ename);
• 2. fetch c into v_emp;
• end loop;
• close c;
• end;
1 ,2换顺序 ,第一个没打 最后一个打两遍
• --------------------------for 循环，遍历游标 --------------
• declare
• cursor c is
• select * from emp;
• begin
• for v_emp in c loop 自动开始/关闭游标,不用fetch
• dbms_output.put_line(v_emp.ename);
• end loop;
• end;
• ---------------------------带参数的游标 (函数) -----------------
• declare
• cursor c(v_deptno emp.deptno%type,v_job emp.job%type)
• is
• select ename, sal from emp wheredeptno=v_deptno and job=v_job;
• --v_temp c%rowtype;此处不用声明变量类型
• begin
• for v_temp in c(30, 'CLICK')loop
• dbms_output.put_line(v_temp.ename);
• end loop;
• end;
CLICK 小写查不到数据
• -----------------------------可更新的游标 ----------------
• declare
• cursor c
• is
• select * from emp2 for update;
• -v_temp c%rowtype;
• begin
• for v_temp in c loop
• if(v_temp.sal < 2000) then
• update emp2 set sal = sal * 2where current of c;
• elsif (v_temp.sal =5000) then
• delete from emp2 where current ofc;
• end if;
• end loop;
• commit;
• end;
• ------------------procedure存储过程(带有名字的程序块)-------
• create or replace procedure p 没有就创建有就替换
• is--这两句除了替代declare，下面的语句全部都一样
• cursor c is
• select * from emp2 forupdate;
• begin
• for v_emp in c loop
• if(v_emp.deptno = 10) then
• update emp2 set sal = sal +10where current of c;
• elsif(v_emp.deptno =20) then
• update emp2 set sal = sal + 20 where current of c;
• else
• update emp2 set sal = sal + 50where current of c;
• end if;
• end loop;
• commit;
• end;
•
• 执行存储过程的两种方法：
• （1）exec p;(p是存储过程的名称)
• （2）
• begin
• p;
• end;
• /
• -------------------------------带参数的存储过程
• create or replace procedure p
• (v_a in number, v_b number, v_ret outnumber, v_temp in out number)
• is
•
• begin
• if(v_a > v_b) then
• v_ret := v_a;
• else
• v_ret := v_b;
• end if;
• v_temp := v_temp + 1;
• end;
• ----------------------调用
• declare
• v_a number := 3;
• v_b number := 4;
• v_ret number;
• v_temp number := 5;
•
• begin
• p(v_a, v_b, v_ret, v_temp);
• dbms_output.put_line(v_ret);
• dbms_output.put_line(v_temp);
• end;
如果语句错误 会显示”编译错误” show error 则可以看到错误提示
• ------------------删除存储过程 ---------------------
• drop procedure p;
• ------------------------创建函数计算个人所得税
• create or replace function sal_tax
• (v_sal number)
• return number 返回值
• is
• begin 此函数与系统函数max()用法一样
• if(v_sal < 2000) then
• return 0.10;
• elsif(v_sal <2750) then
• return 0.15;
• else
• return 0.20;
• end if;
• end;
----------------创建触发器（trigger）
触发器不能单独的存在，必须依附在某一张表上
•
• //创建触发器的依附表 emp2表 操作记录
• create table emp2_log
• (
• ename varchar2(30) ,
• eaction varchar2(20),
• etime date
• );
•
• create or replace trigger trig
• after insert or delete or update onemp2 ---for each row 加上此句，每更新一行，触发一次，不加入则值触发一次
• begin
• if inserting then
• insert into emp2_log values(USER,'insert', sysdate);
• elsif updating then
• insert into emp2_log values(USER,'update', sysdate);
• elsif deleting then
• insert into emp2_log values(USER,'delete', sysdate);
• end if;
• end;
-------------------------------通过触发器更新数据-----------------
• create or replace trigger trig
• after update on dept
• for each row
• begin
• update emp set deptno =:NEW.deptno wheredeptno =: OLD.deptno;
• end;
: 与 O之间， 不要有空格。否则编译错误
一条update 会产生 新状态/旧状态 很少用| 先触发触发器 后检查约束条件
updatedept set deptno = 99 where deptno = 10;
-------------------通过创建存储过程完成递归 ---------------------
• create or replace procedure p(v_pidarticle.pid%type,v_level binary_integer) is
• cursor c is select * from articlewhere pid = v_pid;
• v_preStr varchar2(1024) := '';
• begin
• for i in 0..v_leave loop
• v_preStr := v_preStr || '****';
• end loop;
•
• for v_article in c loop
• dbms_output.put_line(v_article.cont);
• if(v_article.isleaf = 0) then
• p(v_article.id);
• end if;
• end loop;
•
• end;
• 146-------------------------查看当前用户下有哪些表---
• 首先，用这个用户登录然后使用语句：
• select * from tab;
•
• 147------------------------用Oracle进行分页！--------------
• 因为Oracle中的隐含字段rownum不支持'>'所以：
• select * from (
• select rownum rn, t.* from (
• select * from t_user whereuser_id <> 'root'
• ) t where rownum <6
• ) where rn >3
• 148------------------------Oracle下面的清屏命令----------------
• clear screen; 或者 cle scr;
•
• 149-----------将创建好的guohailong的这个用户的密码改为abc-----
• alter user guohailong identified byabc
• 当密码使用的是数字的时候可能会不行
@d:\sql.sql 执行.sql文件
1406
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
