第二章 单行函数
--SQL单行函数：操作数据对象；接受参数返回一个结果；只对一行进行变换，每行返回一个结果;可以转换数据类型；可以嵌套；参数可以是一列或一个值
--单行函数包括：字符函数、 数值函数、 日期函数、 转换函数、 通用函数
/**
字符函数：1.大小写控制函数:
a. 将所有字符串转换为小写
b. 将所有字符串转换为大写
c. 将所有字符串单词的首字母转换为大写
2.字符控制函数：
a.字符串连接函数：
CONCAT('Hello','World') HelloWorld
b.字符串截取函数：
SUBSTR('HelloWorld',1,5) Hello
c.返回字符串长度函数：
LENGTH('HelloWorld') 10
d.字符串查找函数，返回查找字符的位置,如果不存在返回 0
INSTR('HelloWorld', 'W') 6
e.字符串填充函数：
lpad函数从左边对字符串使用指定的字符进行填充：
lpad('ZhangTao',10,'**') 要填充的字符串：zhangtao ,填充后的长度10，使用*号填充
rpad函数从右边对字符串使用指定的字符进行填充：
rpad('ZhangTao',10,'**') 要填充的字符串：zhangtao ,填充后的长度10，使用*号填充
f.字符串字符删除函数，既可以移除字符串首尾空格，也可以移除指定的字符串首尾单字符：
TRIM('H' FROM 'HelloWorld') elloWorld
d.字符串替换函数：
REPLACE('abcd','b','m') amcd
select lower('ZHANGTAO'),upper('ZhangTao'),initcap('TAO GUO GUO') from dual;
select concat('hello','world'),substr('helloworld',2,4),length('helloworld')，instr('HelloWorld','W'),lpad('ZhangTao',10,'**') from dual;
select trim('H' from 'Helloworld') from dual;
*/
/**
数字函数：
1. ROUND:四舍五入
round(45.926,2) -> 45.93
2. TRUNC:截断
trunc(45.926,2) -> 45.92
3. MOD:求余函数
MOD(1600,300) -> 100
*/
/**
日期：Oracle中的日期型数据实际含有两个值：日期和时间
函数SYSDATE 返回 日期、时间
日期的数学运算：
1.在日期上加上或减去一个数字结果仍为日期
2.两个日期相减返回日期之间相差的天数
--日期不允许做加法运算 无意义
3.可以用数字除以24来向日期中加上或j减去天数
日期函数：
A.MONTHS_BETWEEN；两个日期相差的月数
SELECT months_between(sysdate,hire_date) from employees where department_id = 90;
B.ADD_MONTHS: 向指定日期中加上若干月数
select sysdate,add_months(sysdate,2),add_months(sysdate,-1) from dual;
C.NEXT_DAY： 指定日期的下一个星期*对应的日期
SELECT next_day(sysdate,'星期一') from dual;
D.LAST_DAY: 本月的最后一天
SELECT LAST_DAY(sysdate) from dual;
select last_name,hire_date from employees where hire_date = last_day(hire_date) -1;
E.ROUND: 日期四舍五入
select sysdate,round(sysdate,'yyyy'),round(sysdate,'mm'),round(sysdate,'dd'),round(sysdate,'day'),
round(sysdate,'HH'),round(sysdate,'mi') from dual;
*/
/**
转换函数：
1.隐式数据转换,Oracle 自动完成下列转换 ：
Varchar2 <===> Number
Varchar2 <===> Date
Date <===> Varchar2 <===> Number
select '12'+2 from dual;
select sysdate + '2' from dual;
2.显示数据类型转换
select employee_id, hire_date from employees where hire_date = '07-6月-94'; --默认date的字符串格式 日-月-年
A.日期转字符串 to_char:
select employee_id, hire_Date from employees where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07';
B.字符串转日期 to_date
select employee_id, hire_Date from employees where hire_date = to_date('1994-06-07','yyyy-mm-dd');
C.数字型转字符串 to_char 可指定格式 $ 代表美元 L代表当地的货币符号
select to_char(1234567.89,'999,999,999.99') from dual;
select to_char(1234567.89,'000,999,999.99') from dual;
select to_char(1234567.89,'L999,999,999.99') from dual;
select to_char(1234567.89,'L999,999,999.99') from dual;
*/
/**
通用函数：这些函数适用于任何数据类型，同时也适用于空值
1.NVL(expr1,expr2) 将空值转换成一个已知的值,如果expr1不为空，则为expre1的值,否则为expr2的值,expr1 和 expr2 数据类型要一致
--求公司员工的年薪（含commission_pct）
select employee_id,last_name,salary*12*(1+nvl(commission_pct,0)) from employees;
--输出last_name,department_id,当department_id为null时，显示'没有部门'
select last_name,nvl(to_char(department_id),'没有部门') from employees;
2.NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3
--查询员工奖金率，若为空，返回0.01，若不为空，返回实际奖金率+0.015
select last_name,commission_pct,nvl2(commission_pct,commission_pct+0.015,0.01) from employees;
3.NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1
select nullif('1','1') from dual
4.coalesce(expr1,expr2,expr3) 如果expr1为空，返回expr2，如果expr2也为空，返回expr3
select coalesce(null,null,2) from dual
*/
/**
条件表达式：
1.CASE 表达式：
case 字段 when 第一个值 then dosomethings1
when 第二个值 then dosomethings2
else dosomething3
end
--查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍,
--20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数,其他部门为1.4倍
select employee_id,last_name,department_id,case department_id when 10 then salary*1.1
when 20 then salary*1.2
when 30 then salary*1.3
else salary*1.4
end
as new_sal
from employees where department_id in (10,20,30)；
2.DECODE 函数：
select employee_id,
last_name,
department_id,
decode(department_id,
10,
salary * 1.1,
20,
salary * 1.2,
30,
salary * 1.3,
salary * 1.4) as new_sal
from employees
where department_id in (10, 20, 30);
*/
第三章：多表查询
-- 内连接：等值连接、非等值连接
-- 等值连接：多表连接中连接条件值相等
select e.employee_id, e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.employee_id = d.department_id
and d.location_id = l.location_id;
--非等值连接：多表连接中连接条件值不相等，比如一个表的值 在另一个表的一个范围内
select e.employee_id,e.last_name,e.salary,j.grade_level
from employees e, job_grades j
where e.salary between j.lowest_sal and j.highest_sal;
/* 外连接
* 左外连接
* 右外连接
*/
/* 查询员工所对应的部门名称（包含没有部门的员工）
* 左外连接 两个表连接过程中除了返回满足条件的行以外还返回左表中不满足条件的行
* 操作符简略写法 左表中有数据右表中没有 所有给连接条件右边表写 (+)
*/
select e.last_name,e.department_id,d.department_name from employees e, departments d
where e.department_id = d.department_id(+);
/* 查询员工所对应的部门名称（包含没有员工的部门）
* 右外连接 两个表连接过程中除了返回满足条件的行以外还返回右表中不满足条件的行
* 操作符简略写法 右表中有数据左表中没有 所有给连接条件左边表写 (+)
*/
select e.last_name,e.department_id,d.department_name from employees e, departments d
where e.department_id(+) = d.department_id;
--SQL 1999语法
--1.natural join 自动识别多个表中的连接条件进行连接 缺点：默认会把两张表中字段一样的列全部作为连接条件
select employee_id,last_name,department_id,department_name from employees natural join departments;
--2.join..using(field) 指定字段进行多表连接 缺点：两个表的字段名，类型必须一致，否则无法使用
select employee_id,last_name,department_id,department_name from employees join departments using(department_id);
-- 3.推荐使用 on 关键字
select e.employee_id,e.last_name, d.department_id, d.department_name,l.city
from employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id;
/* 自连接 把自身表当作一张关联表进行连接查询
* 列如：查询当前empId 104的老板的工资
*/
select emp.employee_id,emp.last_name,manager.employee_id,manager.last_name,manager.salary from employees emp, employees manager
where emp.manager_id = manager.employee_id and emp.employee_id = 103;
/** 操作符写法的外连接没办法满足满外连接（即查询出两张表所有满足的数据 和各自不满足的数据，左表右表都不做限制）
* 全外连接不支持操作符（+）写法
*/
--左外连接
select e.employee_id,e.last_name, d.department_id, d.department_name
from employees e
left outer join departments d
on e.department_id = d.department_id;
--右外连接
select e.employee_id,e.last_name, d.department_id, d.department_name
from employees e
right outer join departments d
on e.department_id = d.department_id;
--满外连接
select e.employee_id,e.last_name, d.department_id, d.department_name
from employees e
full outer join departments d
on e.department_id = d.department_id;
第四章 分组函数
--MAX() 最大值 Varchar Number Date 类型都支持 类型不限
--MIn() 最小值 类型不限
--AVG() 求平均值 仅限NUMBER类型 忽略空值 可以使用 AVG(NUV(commission_pct,0))不忽略空值
--SUM() 求总和 仅限NUMBER类型
--COUNT() 返回表中的记录总数 类型不限 COUNT(exrp) 不为空的记录总数
--COUNT(DISTINCT expr) 返回expr非空且不重复的记录总数
--GROUP BY 根据字段分组
--求40 60 80 部门的平均工资
select employees.department_id, avg(employees.salary)
from employees
where employees.department_id in(40,60,80)
group by employees.department_id;
--求各个部门不同工种的平均工资
select employees.department_id,employees.job_id, avg(employees.salary)
from employees
group by employees.department_id,employees.job_id;
--Having 分组后进行过滤
--求出各部门中平均工资大于6000的部门,以及其平均工资
select employees.department_id,avg(employees.salary) from employees
where employees.department_id in(40,60,80)
group by employees.department_id having avg(salary) > 6000 order by employees.department_id;
--平均工资最大的部门
select max(avg(employees.salary)) from employees
where employees.department_id in(40,60,80)
group by employees.department_id having avg(salary) > 6000 order by employees.department_id;
第五章 子查询
--单行子查询:只返回一行,使用单行比较操作符 =、>、>=、<、<=、<>
--题目:返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
select e.last_name, e.job_id, e.salary
from employees e
where e.job_id =
(select t1.job_id from employees t1 where t1.employee_id = 141)
and e.salary >
(select t2.salary from employees t2 where t2.employee_id = 143);
--在子查询中使用组函数
--返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);
--子查询中的 HAVING 子句
--题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
--多行子查询:返回多行，使用多行比较操作符
--IN 等于列表中的任意一个
--ANY 和子查询返回的某一个值比较
--返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT e.employee_id, e.last_name, e.job_id, e.salary
FROM EMPLOYEES e
where e.job_id <> 'IT_PROG'
and e.salary < any (select salary from employees t1 where t1.job_id = 'IT_PROG');
--ALL 和子查询返回的所有值比较
--返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
第六章 创建和管理表
/*常见的数据库对象
表:基本数据存储的集合，由行和列组成
视图：从表中抽出的逻辑上相关的数据集合
序列：提供有规律的数值
索引：提高查询的效率
同义词：给对象起名
*/
--1.查看用户定义的表
select table_name from user_tables;
--2.查看用户定义的各种数据库对象
select distinct object_type from user_objects;
--3.查看用户定义的表, 视图, 同义词和序列
select * from user_catalog;
--4.通过AS子查询创建表
--复制现有表
create table emp1 as select * from employees;
--创建的emp2是空表
create table emp2 as select * from employees where 1=2;
--将80部门的员工薪水*12个月查询出来的数据创建一张表名为dept80的数据表
create table dept80
as
select employee_id,
last_name,
salary * 12 ANNSAL,
hire_date
from employees
where department_id = 80;
--列操作：重命名一个列
ALTER TABLE dept80
RENAME COLUMN job_id TO id;
--TOP-N分析：取工资最高的前10条
select rownum rn,employee_id,last_name,salary from (select employee_id,last_name,salary from employees order by salary desc)
where rn <=10;
--取工资最高的 40-50条数据
select rn,employee_id,last_name,salary from(
select rownum rn,employee_id,last_name,salary from (select employee_id,last_name,salary from employees order by salary desc)
)
where rn>40 and rn<50;
--序列的创建
create sequence empseq --创建一个名为eqmseq的序列
increment by 1 --每次增长10
start with 1 --从1开始增长
maxvalue 100000000 --序列最大值
minvalue 1 --最小值
nocycle --不需要循环
nocache; --不需要缓存登录
/*
同义词-SYNONYM
使用同义词访问相同的对象：方便访问其它用户的对象，缩短对象名字的长度
*/
CREATE SYNONYM e FOR employees;
select * from e;
--UNION 并集不重复
select employee_id, last_name, department_id
from emp01
union
select employee_id, last_name, department_id
from emp02
--并集重复 UNION ALL
select employee_id, last_name, department_id
from emp01
union all
select employee_id, last_name, department_id
from emp02
--intersect 取两表的交集
select employee_id, last_name, department_id
from emp01
intersect
select employee_id, last_name, department_id
from emp02
--minus 取两表的差集（emp01-emp02中共有的数据）
select employee_id, last_name, department_id
from emp01
minus
select employee_id, last_name, department_id
from emp02
--with使用，多逻辑块时，可作为临时表 进行擦汗寻
--查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
with dept_sum as (
select emp.department_id,sum(emp.salary) sal from employees emp where emp.department_id is not null group by emp.department_id
),
dept_avg as (
select emp.department_id,avg(emp.salary) sal from employees emp where emp.department_id is not null group by emp.department_id
)
select t1.department_id from dept_sum t1, dept_avg t2
where t1.department_id = t2.department_id
and t1.sal<t2.salPLSQL编程记录
0. 准备工作:
set serveroutput on
hellowrold 程序
begin
dbms_output.put_line('hello world');
end;
/
[语法格式]
--declare
--声明的变量、类型、游标
begin
--程序的执行部分（类似于java里的main()方法）
dbms_output.put_line('helloworld');
--exception
--针对begin块中出现的异常，提供处理的机制
--when .... then ...
--when .... then ...
end;
*********************************************************************************************************
基本语法
*********************************************************************************************************
1. 使用一个变量
declare
--声明一个变量
v_name varchar2(25);
begin
--通过 select ... into ... 语句为变量赋值
select last_name into v_name
from employees
where employee_id = 186;
-- 打印变量的值
dbms_output.put_line(v_name);
end;
2. 使用多个变量
declare
--声明变量
v_name varchar2(25);
v_email varchar2(25);
v_salary number(8, 2);
v_job_id varchar2(10);
begin
--通过 select ... into ... 语句为变量赋值
--被赋值的变量与SELECT中的列名要一一对应
select last_name, email, salary, job_id into v_name, v_email, v_salary, v_job_id
from employees
where employee_id = 186;
-- 打印变量的值
dbms_output.put_line(v_name || ', ' || v_email || ', ' || v_salary || ', ' || v_job_id);
end;
----------------------------------------------------------------
记录类型
----------------------------------------------------------------
3.1 自定义记录类型
declare
--定义一个记录类型
type customer_type is record(
v_cust_name varchar2(20),
v_cust_id number(10));
--声明自定义记录类型的变量
v_customer_type customer_type;
begin
v_customer_type.v_cust_name := '刘德华';
v_customer_type.v_cust_id := 1001;
dbms_output.put_line(v_customer_type.v_cust_name||','||v_customer_type.v_cust_id);
end;
3.2 自定义记录类型
declare
--定义一个记录类型
type emp_record is record(
v_name varchar2(25),
v_email varchar2(25),
v_salary number(8, 2),
v_job_id varchar2(10));
--声明自定义记录类型的变量
v_emp_record emp_record;
begin
--通过 select ... into ... 语句为变量赋值
select last_name, email, salary, job_id into v_emp_record
from employees
where employee_id = 186;
-- 打印变量的值
dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||
v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);
end;
4. 使用 %type 定义变量，动态的获取数据的声明类型
declare
--定义一个记录类型
type emp_record is record(
v_name employees.last_name%type,
v_email employees.email%type,
v_salary employees.salary%type,
v_job_id employees.job_id%type);
--声明自定义记录类型的变量
v_emp_record emp_record;
begin
--通过 select ... into ... 语句为变量赋值
select last_name, email, salary, job_id into v_emp_record
from employees
where employee_id = 186;
-- 打印变量的值
dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||
v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);
end;
5. 使用 %rowtype
declare
--声明一个记录类型的变量
v_emp_record employees%rowtype;
begin
--通过 select ... into ... 语句为变量赋值
select * into v_emp_record
from employees
where employee_id = 186;
-- 打印变量的值
dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||
v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||
v_emp_record.hire_date);
end;
6.1 赋值语句：通过变量实现查询语句
declare
v_emp_record employees%rowtype;
v_employee_id employees.employee_id%type;
begin
--使用赋值符号位变量进行赋值
v_employee_id := 186;
--通过 select ... into ... 语句为变量赋值
select * into v_emp_record
from employees
where employee_id = v_employee_id;
-- 打印变量的值
dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||
v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||
v_emp_record.hire_date);
end;
6.2 通过变量实现DELETE、INSERT、UPDATE等操作
declare
v_emp_id employees.employee_id%type;
begin
v_emp_id := 109;
delete from employees
where employee_id = v_emp_id;
--commit;
end;
*********************************************************************************************************
流程控制
*********************************************************************************************************
-----------------------------------------------------
条件判断
-----------------------------------------------------
7. 使用 IF ... THEN ... ELSIF ... THEN ...ELSE ... END IF;
要求: 查询出 150号 员工的工资, 若其工资大于或等于 10000 则打印 'salary >= 10000';
若在 5000 到 10000 之间, 则打印 '5000<= salary < 10000'; 否则打印 'salary < 5000'
(方法一)
declare
v_salary employees.salary%type;
begin
--通过 select ... into ... 语句为变量赋值
select salary into v_salary
from employees
where employee_id = 150;
dbms_output.put_line('salary: ' || v_salary);
-- 打印变量的值
if v_salary >= 10000 then
dbms_output.put_line('salary >= 10000');
elsif v_salary >= 5000 then
dbms_output.put_line('5000 <= salary < 10000');
else
dbms_output.put_line('salary < 5000');
end if;
(方法二)
declare
v_emp_name employees.last_name%type;
v_emp_sal employees.salary%type;
v_emp_sal_level varchar2(20);
begin
select last_name,salary into v_emp_name,v_emp_sal from employees where employee_id = 150;
if(v_emp_sal >= 10000) then v_emp_sal_level := 'salary >= 10000';
elsif(v_emp_sal >= 5000) then v_emp_sal_level := '5000<= salary < 10000';
else v_emp_sal_level := 'salary < 5000';
end if;
dbms_output.put_line(v_emp_name||','||v_emp_sal||','||v_emp_sal);
end;
7+ 使用 CASE ... WHEN ... THEN ...ELSE ... END 完成上面的任务
declare
v_sal employees.salary%type;
v_msg varchar2(50);
begin
select salary into v_sal
from employees
where employee_id = 150;
--case 不能向下面这样用
/*
case v_sal when salary >= 10000 then v_msg := '>=10000'
when salary >= 5000 then v_msg := '5000<= salary < 10000'
else v_msg := 'salary < 5000'
end;
*/
v_msg :=
case trunc(v_sal / 5000)
when 0 then 'salary < 5000'
when 1 then '5000<= salary < 10000'
else 'salary >= 10000'
end;
dbms_output.put_line(v_sal ||','||v_msg);
end;
8. 使用 CASE ... WHEN ... THEN ... ELSE ... END;
要求: 查询出 122 号员工的 JOB_ID, 若其值为 'IT_PROG', 则打印 'GRADE: A';
'AC_MGT', 打印 'GRADE B',
'AC_ACCOUNT', 打印 'GRADE C';
否则打印 'GRADE D'
declare
--声明变量
v_grade char(1);
v_job_id employees.job_id%type;
begin
select job_id into v_job_id
from employees
where employee_id = 122;
dbms_output.put_line('job_id: ' || v_job_id);
--根据 v_job_id 的取值, 利用 case 字句为 v_grade 赋值
v_grade :=
case v_job_id when 'IT_PROG' then 'A'
when 'AC_MGT' then 'B'
when 'AC_ACCOUNT' then 'C'
else 'D'
end;
dbms_output.put_line('GRADE: ' || v_grade);
end;
-----------------------------------------------------
循环结构
-----------------------------------------------------
9. 使用循环语句打印 1 - 100.（三种方式）
1). LOOP ... EXIT WHEN ... END LOOP
declare
--初始化条件
v_i number(3) := 1;
begin
loop
--循环体
dbms_output.put_line(v_i);
--循环条件
exit when v_i = 100;
--迭代条件
v_i := v_i + 1;
end loop;
end;
2). WHILE ... LOOP ... END LOOP
declare
--初始化条件
v_i number(3) := 1;
begin
--循环条件
while v_i <= 100 loop
--循环体
dbms_output.put_line(v_i);
--迭代条件
v_i := v_i + 1;
end loop;
end;
3).
begin
for i in 1 .. 100 loop
dbms_output.put_line(i);
end loop;
end;
10. 综合使用 if, while 语句, 打印 1 - 100 之间的所有素数
(素数: 有且仅用两个正约数的整数, 2, 3, 5, 7, 11, 13, ...).
declare
v_flag number(1):=1;
v_i number(3):=2;
v_j number(2):=2;
begin
while (v_i<=100) loop
while v_j <= sqrt(v_i) loop
if (mod(v_i,v_j)=0) then v_flag:= 0;
end if;
v_j :=v_j +1;
end loop;
if(v_flag=1) then dbms_output.put_line(v_i);
end if;
v_flag :=1;
v_j := 2;
v_i :=v_i +1;
end loop;
end;
(法二)使用for循环实现1-100之间的素数的输出
declare
--标记值, 若为 1 则是素数, 否则不是
v_flag number(1) := 0;
begin
for i in 2 .. 100 loop
v_flag := 1;
for j in 2 .. sqrt(i) loop
if i mod j = 0 then
v_flag := 0;
end if;
end loop;
if v_flag = 1 then
dbms_output.put_line(i);
end if;
end loop;
end;
11. 使用 goto
declare
--标记值, 若为 1 则是素数, 否则不是
v_flag number(1) := 0;
begin
for i in 2 .. 100 loop
v_flag := 1;
for j in 2 .. sqrt(i) loop
if i mod j = 0 then
v_flag := 0;
goto label;
end if;
end loop;
<<label>>
if v_flag = 1 then
dbms_output.put_line(i);
end if;
end loop;
end;
11+.打印1——100的自然数，当打印到50时，跳出循环，输出“打印结束”
(方法一)
begin
for i in 1..100 loop
dbms_output.put_line(i);
if(i = 50) then
goto label;
end if;
end loop;
<<label>>
dbms_output.put_line('打印结束');
end;
(方法二)
begin
for i in 1..100 loop
dbms_output.put_line(i);
if(i mod 50 = 0) then dbms_output.put_line('打印结束');
exit;
end if;
end loop;
end;
*********************************************************************************************************
游标的使用
*********************************************************************************************************
12.1 使用游标
要求: 打印出 80 部门的所有的员工的工资:salary: xxx
declare
--1. 定义游标
cursor salary_cursor is select salary from employees where department_id = 80;
v_salary employees.salary%type;
begin
--2. 打开游标
open salary_cursor;
--3. 提取游标
fetch salary_cursor into v_salary;
--4. 对游标进行循环操作: 判断游标中是否有下一条记录
while salary_cursor%found loop
dbms_output.put_line('salary: ' || v_salary);
fetch salary_cursor into v_salary;
end loop;
--5. 关闭游标
close salary_cursor;
end;
12.2 使用游标
要求: 打印出 80 部门的所有的员工的工资: Xxx 's salary is: xxx
declare
cursor sal_cursor is select salary ,last_name from employees where department_id = 80;
v_sal number(10);
v_name varchar2(20);
begin
open sal_cursor;
fetch sal_cursor into v_sal,v_name;
while sal_cursor%found loop
dbms_output.put_line(v_name||'`s salary is '||v_sal);
fetch sal_cursor into v_sal,v_name;
end loop;
close sal_cursor;
end;
13. 使用游标的练习:
打印出 manager_id 为 100 的员工的 last_name, email, salary 信息(使用游标, 记录类型)
declare
--声明游标
cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;
--声明记录类型
type emp_record is record(
name employees.last_name%type,
email employees.email%type,
salary employees.salary%type
);
-- 声明记录类型的变量
v_emp_record emp_record;
begin
--打开游标
open emp_cursor;
--提取游标
fetch emp_cursor into v_emp_record;
--对游标进行循环操作
while emp_cursor%found loop
dbms_output.put_line(v_emp_record.name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary );
fetch emp_cursor into v_emp_record;
end loop;
--关闭游标
close emp_cursor;
end;
(法二：使用for循环)
declare
cursor emp_cursor is
select last_name,email,salary
from employees
where manager_id = 100;
begin
for v_emp_record in emp_cursor loop
dbms_output.put_line(v_emp_record.last_name||','||v_emp_record.email||','||v_emp_record.salary);
end loop;
end;
14. 利用游标, 调整公司中员工的工资:
工资范围 调整基数
0 - 5000 5%
5000 - 10000 3%
10000 - 15000 2%
15000 - 1%
declare
--定义游标
cursor emp_sal_cursor is select salary, employee_id from employees;
--定义基数变量
temp number(4, 2);
--定义存放游标值的变量
v_sal employees.salary%type;
v_id employees.employee_id%type;
begin
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_sal, v_id;
--处理游标的循环操作
while emp_sal_cursor%found loop
--判断员工的工资, 执行 update 操作
--dbms_output.put_line(v_id || ': ' || v_sal);
if v_sal <= 5000 then
temp := 0.05;
elsif v_sal<= 10000 then
temp := 0.03;
elsif v_sal <= 15000 then
temp := 0.02;
else
temp := 0.01;
end if;
--dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
update employees set salary = salary * (1 + temp) where employee_id = v_id;
fetch emp_sal_cursor into v_sal, v_id;
end loop;
--关闭游标
close emp_sal_cursor;
end;
使用SQL中的 decode 函数
update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,
1, 0.03,
2, 0.02,
0.01)))
15. 利用游标 for 循环完成 14.
declare
--定义游标
cursor emp_sal_cursor is select salary, employee_id id from employees;
--定义基数变量
temp number(4, 2);
begin
--处理游标的循环操作
for c in emp_sal_cursor loop
--判断员工的工资, 执行 update 操作
--dbms_output.put_line(v_id || ': ' || v_sal);
if c.salary <= 5000 then
temp := 0.05;
elsif c.salary <= 10000 then
temp := 0.03;
elsif c.salary <= 15000 then
temp := 0.02;
else
temp := 0.01;
end if;
--dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
update employees set salary = salary * (1 + temp) where employee_id = c.id;
end loop;
end;
16*. 带参数的游标
declare
--定义游标
cursor emp_sal_cursor(dept_id number, sal number) is
select salary + 1000 sal, employee_id id
from employees
where department_id = dept_id and salary > sal;
--定义基数变量
temp number(4, 2);
begin
--处理游标的循环操作
for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop
--判断员工的工资, 执行 update 操作
--dbms_output.put_line(c.id || ': ' || c.sal);
if c.sal <= 5000 then
temp := 0.05;
elsif c.sal <= 10000 then
temp := 0.03;
elsif c.sal <= 15000 then
temp := 0.02;
else
temp := 0.01;
end if;
dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);
--update employees set salary = salary * (1 + temp) where employee_id = c.id;
end loop;
end;
17. 隐式游标: 更新指定员工 salary(涨工资 10)，如果该员工没有找到，则打印”查无此人” 信息
begin
update employees set salary = salary + 10 where employee_id = 1005;
if sql%notfound then
dbms_output.put_line('查无此人!');
end if;
end;
*********************************************************************************************************
异常处理
*********************************************************************************************************
[预定义异常]
declare
v_sal employees.salary%type;
begin
select salary into v_sal
from employees
where employee_id >100;
dbms_output.put_line(v_sal);
exception
when Too_many_rows then dbms_output.put_line('输出的行数太多了');
end;
[非预定义异常]
declare
v_sal employees.salary%type;
--声明一个异常
delete_mgr_excep exception;
--把自定义的异常和oracle的错误关联起来
PRAGMA EXCEPTION_INIT(delete_mgr_excep,-2292);
begin
delete from employees
where employee_id = 100;
select salary into v_sal
from employees
where employee_id >100;
dbms_output.put_line(v_sal);
exception
when Too_many_rows then dbms_output.put_line('输出的行数太多了');
when delete_mgr_excep then dbms_output.put_line('Manager不能直接被删除');
end;
[用户自定义异常]
declare
v_sal employees.salary%type;
--声明一个异常
delete_mgr_excep exception;
--把自定义的异常和oracle的错误关联起来
PRAGMA EXCEPTION_INIT(delete_mgr_excep,-2292);
--声明一个异常
too_high_sal exception;
begin
select salary into v_sal
from employees
where employee_id =100;
if v_sal > 1000 then
raise too_high_sal;
end if;
delete from employees
where employee_id = 100;
dbms_output.put_line(v_sal);
exception
when Too_many_rows then dbms_output.put_line('输出的行数太多了');
when delete_mgr_excep then dbms_output.put_line('Manager不能直接被删除');
--处理异常
when too_high_sal then dbms_output.put_line('工资过高了');
end;
18. 异常的基本程序:
通过 select ... into ... 查询某人的工资, 若没有查询到, 则输出 "未找到数据"
declare
--定义一个变量
v_sal employees.salary%type;
begin
--使用 select ... into ... 为 v_sal 赋值
select salary into v_sal from employees where employee_id = 1000;
dbms_output.put_line('salary: ' || v_sal);
exception
when No_data_found then
dbms_output.put_line('未找到数据');
end;
或
declare
--定义一个变量
v_sal employees.salary%type;
begin
--使用 select ... into ... 为 v_sal 赋值
select salary into v_sal from employees;
dbms_output.put_line('salary: ' || v_sal);
exception
when No_data_found then
dbms_output.put_line('未找到数据!');
when Too_many_rows then
dbms_output.put_line('数据过多!');
end;
19. 更新指定员工工资，如工资小于300，则加100；对 NO_DATA_FOUND 异常, TOO_MANY_ROWS 进行处理.
declare
v_sal employees.salary%type;
begin
select salary into v_sal from employees where employee_id = 100;
if(v_sal < 300) then update employees set salary = salary + 100 where employee_id = 100;
else dbms_output.put_line('工资大于300');
end if;
exception
when no_data_found then dbms_output.put_line('未找到数据');
when too_many_rows then dbms_output.put_line('输出的数据行太多');
end;
20. 处理非预定义的异常处理: "违反完整约束条件"
declare
--1. 定义异常
temp_exception exception;
--2. 将其定义好的异常情况，与标准的 ORACLE 错误联系起来，使用 EXCEPTION_INIT 语句
PRAGMA EXCEPTION_INIT(temp_exception, -2292);
begin
delete from employees where employee_id = 100;
exception
--3. 处理异常
when temp_exception then
dbms_output.put_line('违反完整性约束!');
end;
21. 自定义异常: 更新指定员工工资，增加100；若该员工不存在则抛出用户自定义异常: no_result
declare
--自定义异常
no_result exception;
begin
update employees set salary = salary + 100 where employee_id = 1001;
--使用隐式游标, 抛出自定义异常
if sql%notfound then
raise no_result;
end if;
exception
--处理程序抛出的异常
when no_result then
dbms_output.put_line('更新失败');
end;
*********************************************************************************************************
存储函数和过程
*********************************************************************************************************
[存储函数：有返回值，创建完成后，通过select function() from dual;执行]
[存储过程：由于没有返回值，创建完成后，不能使用select语句，只能使用pl/sql块执行]
[格式]
--函数的声明(有参数的写在小括号里)
create or replace function func_name(v_param varchar2)
--返回值类型
return varchar2
is
--PL/SQL块变量、记录类型、游标的声明(类似于前面的declare的部分)
begin
--函数体(可以实现增删改查等操作，返回值需要return)
return 'helloworld'|| v_logo;
end;
22.1 函数的 helloworld: 返回一个 "helloworld" 的字符串
create or replace function hello_func
return varchar2
is
begin
return 'helloworld';
end;
执行函数
begin
dbms_output.put_line(hello_func());
end;
或者： select hello_func() from dual;
22.2 返回一个"helloworld: atguigu"的字符串，其中atguigu 由执行函数时输入。
--函数的声明(有参数的写在小括号里)
create or replace function hello_func(v_logo varchar2)
--返回值类型
return varchar2
is
--PL/SQL块变量的声明
begin
--函数体
return 'helloworld'|| v_logo;
end;
22.3 创建一个存储函数，返回当前的系统时间
create or replace function func1
return date
is
--定义变量
v_date date;
begin
--函数体
--v_date := sysdate;
select sysdate into v_date from dual;
dbms_output.put_line('我是函数哦');
return v_date;
end;
执行法1：
select func1 from dual;
执行法2：
declare
v_date date;
begin
v_date := func1;
dbms_output.put_line(v_date);
end;
23. 定义带参数的函数: 两个数相加
create or replace function add_func(a number, b number)
return number
is
begin
return (a + b);
end;
执行函数
begin
dbms_output.put_line(add_func(12, 13));
end;
或者
select add_func(12,13) from dual;
24. 定义一个函数: 获取给定部门的工资总和, 要求:部门号定义为参数, 工资总额定义为返回值.
create or replace function sum_sal(dept_id number)
return number
is
cursor sal_cursor is select salary from employees where department_id = dept_id;
v_sum_sal number(8) := 0;
begin
for c in sal_cursor loop
v_sum_sal := v_sum_sal + c.salary;
end loop;
--dbms_output.put_line('sum salary: ' || v_sum_sal);
return v_sum_sal;
end;
执行函数
begin
dbms_output.put_line(sum_sal(80));
end;
25. 关于 OUT 型的参数: 因为函数只能有一个返回值, PL/SQL 程序可以通过 OUT 型的参数实现有多个返回值
要求: 定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数(定义为 OUT 类型的参数).
要求: 部门号定义为参数, 工资总额定义为返回值.
create or replace function sum_sal(dept_id number, total_count out number)
return number
is
cursor sal_cursor is select salary from employees where department_id = dept_id;
v_sum_sal number(8) := 0;
begin
total_count := 0;
for c in sal_cursor loop
v_sum_sal := v_sum_sal + c.salary;
total_count := total_count + 1;
end loop;
--dbms_output.put_line('sum salary: ' || v_sum_sal);
return v_sum_sal;
end;
执行函数:
delare
v_total number(3) := 0;
begin
dbms_output.put_line(sum_sal(80, v_total));
dbms_output.put_line(v_total);
end;
26*. 定义一个存储过程: 获取给定部门的工资总和(通过 out 参数), 要求:部门号和工资总额定义为参数
create or replace procedure sum_sal_procedure(dept_id number, v_sum_sal out number)
is
cursor sal_cursor is select salary from employees where department_id = dept_id;
begin
v_sum_sal := 0;
for c in sal_cursor loop
--dbms_output.put_line(c.salary);
v_sum_sal := v_sum_sal + c.salary;
end loop;
dbms_output.put_line('sum salary: ' || v_sum_sal);
end;
[执行]
declare
v_sum_sal number(10) := 0;
begin
sum_sal_procedure(80,v_sum_sal);
end;
27*. 自定义一个存储过程完成以下操作:
对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 95) 期间, 为其加薪 %5
[95 , 98) %3
[98, ?) %1
得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).
create or replace procedure add_sal_procedure(dept_id number, temp out number)
is
cursor sal_cursor is select employee_id id, hire_date hd, salary sal from employees where department_id = dept_id;
a number(4, 2) := 0;
begin
temp := 0;
for c in sal_cursor loop
a := 0;
if c.hd < to_date('1995-1-1', 'yyyy-mm-dd') then
a := 0.05;
elsif c.hd < to_date('1998-1-1', 'yyyy-mm-dd') then
a := 0.03;
else
a := 0.01;
end if;
temp := temp + c.sal * a;
update employees set salary = salary * (1 + a) where employee_id = c.id;
end loop;
end;
*********************************************************************************************************
触发器
*********************************************************************************************************
一个helloworld级别的触发器
create or replace trigger hello_trigger
after
update on employees
--for each row
begin
dbms_output.put_line('hello...');
--dbms_output.put_line('old.salary:'|| :OLD.salary||',new.salary'||:NEW.salary);
end;
然后执行：update employees set salary = salary + 1000;
28. 触发器的 helloworld: 编写一个触发器, 在向 emp 表中插入记录时, 打印 'helloworld'
create or replace trigger emp_trigger
after
insert on emp
for each row
begin
dbms_output.put_line('helloworld');
end;
29. 行级触发器: 每更新 employees 表中的一条记录, 都会导致触发器执行
create or replace trigger employees_trigger
after
update on employees
for each row
begin
dbms_output.put_line('修改了一条记录!');
end;
语句级触发器: 一个 update/delete/insert 语句只使触发器执行一次
create or replace trigger employees_trigger
after
update on employees
begin
dbms_output.put_line('修改了一条记录!');
end;
30. 使用 :new, :old 修饰符
create or replace trigger employees_trigger
after
update on employees
for each row
begin
dbms_output.put_line('old salary: ' || :old.salary || ', new salary: ' || :new.salary);
end;
31. 编写一个触发器, 在对 my_emp 记录进行删除的时候, 在 my_emp_bak 表中备份对应的记录
1). 准备工作:
create table my_emp as select employee_id id, last_name name, salary sal from employees
create table my_emp_bak as select employee_id id, last_name name, salary sal from employees where 1 = 2
2).
create or replace trigger bak_emp_trigger
before delete on my_emp
for each row
begin
insert into my_emp_bak values(:old.id, :old.name, :old.sal);
end;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
