Oracle06
1.建立一张表 叫 myorder 字段如下 和 一张用户表 myuser
id number pk id number pk
name varchar2(30) name varchar2(30) unique
odate date
userid number fk
create table myuser(
id number constraint myuser_id_pk primary key,
name varchar2(30) constraint myuser_name_uk unique
);
create table myorder(
id number constraint myorder_id_pk primary key,
name varchar2(30),
odate date,
userid number constraint myorder_userid_fk references myuser(id)
);
2.写出如何实现级联删除 和 级联置空
在外键上 加 on delete cascade
on delete set null
3.按照入职日期排序 显示s_emp 表中的数据 一页显示 pageSize 条 显示第n页数据
显示 first_name salary start_date
select first_name,salary,start_date from
(select rownum r,first_name,salary,start_date from
(select first_name,salary,start_date from s_emp order by start_date)
where rownum < n*pageSize+1) where r > (n-1)*pageSize;
------------------------------------------------------------------------------
1.什么是PL/SQL
procedure language 过程化语言
它是对SQL 语句的扩展
2.PLSQL 扩展了sql的哪些方面
变量和类型
控制语句 (分支语句 循环语句 )
过程和 函数
对象 和 方法
3.第一个PLSQL 程序
3.1 PLSQL的程序结构
declare
/* 申明区
用来定义类型 和 变量的 */
begin
/* 执行区
执行sql语句 和 plsql语句的 */
exception
/* 异常处理区
当sql语句 或者 plsql语句出了异常 则自动进入这个块进行处理
*/
end;
/
3.2 代码 hello world
-- 设置服务端的输出为打开状态
set serveroutput on;
begin
dbms_output.put_line('hello world');
end;
/
3.3 执行环境
命令行:sqlplus
图形化:plsqldeveloper
3.4 执行过程 (了解)
plsql 语句块 整个的发送个 PLSQL语句执行器 然后 其中的 sql语句由 sql语句执行器完成执行
plsql语句由 plsql引擎 完成处理。
4.变量
4.1 标识符
给变量 方法 类型 函数 过程 等命名的
不能关键字 不能超过30个字符
4.2 变量的类型
基本类型:number(5,2) 总位数5位 小数占 2 位
varchar2(30) 变长字符串
char(30) 定长字符串
date 日期类型
boolean 布尔类型
binary_integer 二进制整数
复合类型: table 表格类型
record 记录类型
cursor 游标类型
参考类型: ref 类型
大数据类型:FILE 文件类型 (几乎不用 因为存储数据的路径即可)
BFILE 二进制文件类型
BLOB 大二进制类型 CLOB 大文本类型
4.3 变量的语法
declare
变量名 类型;
变量名 constant 类型 not null:=值;
begin
4.4 练习
定义两个变量 类型 分别是 整数类型 和 字符串类型
变量没有赋值 则 值为 NULL
declare
var_id number;
var_name varchar2(30);
begin
dbms_output.put_line(var_id||':'||var_name);
end;
/
如何赋值? :=
declare
var_id number;
var_name varchar2(30);
begin
var_id := 100;
var_name:='zhangsan';
dbms_output.put_line(var_id||':'||var_name);
end;
/
4.5 变量的修饰
constant 代表修饰成常量
declare
var_id constant number:=101;
begin
-- var_id:=101;
dbms_output.put_line(var_id);
end;
/
not null 修饰 把变量修饰成 非空的
declare
var_id number not null:=123;
begin
var_id := 102;
dbms_output.put_line(var_id);
end;
/
4.6 定义一个变量 叫 var_name 这个变量的类型 和 s_emp 表中的first_name 类型保持一致
并且要求 first_name 这个变量不能为空。再定义一个变量 叫 var_salary 类型 和 s_emp 表中的
salary 类型 保持一致。最后给这两个变量赋值 成 zhangsan 12345 输出这两个变量的值。
declare
var_name varchar2(25) not null:='abc';
var_salary number(11,2);
begin
var_name:='zhangsan';
var_salary:=12345;
dbms_output.put_line(var_name||':'||var_salary);
end;
/
4.7 获取数据库表中的某个字段对应的类型 表名.字段名%type
declare
var_name s_emp.first_name%type not null:='abc';
var_salary number(11,2);
begin
var_name:='zhangsan';
var_salary:=12345;
dbms_output.put_line(var_name||':'||var_salary);
end;
/
declare
var_name s_emp.first_name%type not null:='abc';
var_salary s_emp.salary%type;
begin
var_name:='zhangsan';
var_salary:=12345;
dbms_output.put_line(var_name||':'||var_salary);
end;
/
4.8 使用sql 语句给变量赋值
declare
var_name s_emp.first_name%type not null:='abc';
var_salary s_emp.salary%type;
begin
/* 使用sql 语句来赋值 into 的意思把查询的值 赋值给 变量 */
select first_name into var_name from s_emp where id = 1;
dbms_output.put_line(var_name||':'||var_salary);
end;
/
declare
var_name s_emp.first_name%type not null:='abc';
var_salary s_emp.salary%type;
begin
/* 使用sql 语句来赋值 into 的意思把查询的值 赋值给 变量 */
select first_name,salary into var_name,var_salary
from s_emp where id = 1;
dbms_output.put_line(var_name||':'||var_salary);
end;
/
4.9 把多个变量的类型 包装成一个类型? 这个类型叫 record 类型
4.9.1 record 类型语法
declare
type 记录类型名 is record(
字段名 类型 ,
字段名 类型,
字段名 类型
);
变量名 记录类型名;
begin
4.9.2 把s_emp 表中的 id first_name salary 包装成一个 叫 emptype 的 record类型
然后定义 一个 emptype 类型的变量。
declare
type emptype is record(
id s_emp.id%type,
name s_emp.first_name%type,
salary s_emp.salary%type
);
var_emp emptype;
begin
/* 使用sql 语句给 var_emp 赋值 id = 1 的相关数据 */
select id,first_name,salary into var_emp from s_emp where id=1;
dbms_output.put_line(var_emp.id||':'||var_emp.name||':'||var_emp.salary);
end;
/
4.9.3 record 类型 变量之间的赋值
declare
type emptype is record(
id s_emp.id%type,
name s_emp.first_name%type,
salary s_emp.salary%type
);
var_emp1 emptype;
var_emp emptype;
begin
/* 使用sql 语句给 var_emp1 赋值 id = 1 的相关数据 */
select id,first_name,salary into var_emp1 from s_emp where id=1;
/* 整体赋值
var_emp:=var_emp1; */
/* 部分字段赋值 */
var_emp.name:=var_emp1.name;
dbms_output.put_line(var_emp.id||':'||var_emp.name||':'||var_emp.salary);
end;
/
4.9.4 练习
把s_dept 表中的 所有的字段 包装成一个 记录类型 叫 depttype 然后定义一个变量 。
最后使用sql语句 把 id =50 数据赋值给这个变量 打印这个变量的值。
declare
type depttype is record(
id s_dept.id%type,
name s_dept.name%type,
rid s_dept.region_id%type
);
var_dept depttype;
begin
select * into var_dept from s_dept where id=50;
dbms_output.put_line(var_dept.id||':'||var_dept.name);
end;
/
4.10 思考 如何 把s_emp 表中所有的字段 包装成一个 record类型 然后把 id =1 数据
放入这个类型的变量中 打印部分信息。
表名%rowtype 可以获取表的一行对应的类型，这个类型 就是字段名 和 字段顺序 完全和表头
相同的一个记录类型。
declare
var_emp s_emp%rowtype;
begin
select * into var_emp from s_emp where id=1;
dbms_output.put_line(var_emp.id||':'||var_emp.salary);
end;
/
4.11 table 类型
相当于 java中的 Array 或者 Map<int,Object>
4.11.1 语法
declare
type 表格类型的名字 is table of 表中的元素类型 index by binary_integer;
变量 表格类型的名字;
begin
-- 取值
变量(下标)
-- 赋值
变量(下标):=值
end;
/
4.11.2 定义一个table 类型 里面用来存放 整数 然后使用这个类型定义一个变量
把 9 5 2 7 这四个数 放入这个变量中。然后取出其中的一个值。
declare
type numstype is table of number index by binary_integer;
var_nums numstype;
begin
var_nums(1):=9;
var_nums(2):=5;
var_nums(3):=2;
var_nums(7):=7;
dbms_output.put_line(var_nums(13));
end;
/
4.11.3 下标连续时 可以通过下标 顺序访问
declare
type numstype is table of number index by binary_integer;
var_nums numstype;
var_index binary_integer:=1;
begin
var_nums(1):=9;
var_nums(2):=5;
var_nums(3):=2;
var_nums(4):=7;
dbms_output.put_line(var_nums(var_index));
var_index:=var_index+1;
dbms_output.put_line(var_nums(var_index));
var_index:=var_index+1;
dbms_output.put_line(var_nums(var_index));
var_index:=var_index+1;
dbms_output.put_line(var_nums(var_index));
end;
/
4.11.3 下标不连续时 如何访问？迭代器思想 (了解)
可以根据一个元素的信息 获取下一个元素相关的信息
first() 获取到第一个元素对应的下标 最小下标
next(n) 根据一个元素的下标 获取下一个元素对应的下标
last() 最后一个元素对应下标 就是最大下标
declare
type numstype is table of number index by binary_integer;
var_nums numstype;
var_index binary_integer:=1;
begin
var_nums(11):=9;
var_nums(2):=5;
var_nums(3):=2;
var_nums(14):=7;
var_index:=var_nums.first();
dbms_output.put_line(var_nums(var_index));
var_index:=var_nums.next(var_index);
dbms_output.put_line(var_nums(var_index));
var_index:=var_nums.next(var_index);
dbms_output.put_line(var_nums(var_index));
var_index:=var_nums.next(var_index);
dbms_output.put_line(var_nums(var_index));
end;
/
4.12 变量的作用域 和 可见性
局部可以访问全局 全局部能访问局部
declare
var_m number:=1;
begin
declare
var_n number:=100;
begin
dbms_output.put_line(var_m);
dbms_output.put_line(var_n);
end;
dbms_output.put_line(var_m);
-- dbms_output.put_line(var_n);
end;
/
<<abc>>
declare
var_m number:=1;
begin
declare
var_m number:=100;
begin
dbms_output.put_line(var_m);
dbms_output.put_line(abc.var_m);
end;
end;
/
5.控制语句
5.1 分支语句
5.1.1 语法
java PLSQL
if(a>b){ if a > b then
[[[[[[[[[
} end if;
if(a>b){ if a > b then
}else{ else
} end if;
if(a>b){ if a > b then
}else if(a > c){ elsif a > c then
}else if(a > d){ elsif a > d then
} end if;
if(a > b) { if a > b then
}else if( a > d){ elsif a > d then
}else{ else
} end if;
5.1.2 练习
定义两个整数变量 赋值 打印两个变量的最大值
declare
var_x number:=10;
var_y number:=20;
begin
if var_x > var_y then
dbms_output.put_line(var_x);
else
dbms_output.put_line(var_y);
end if;
end;
/
5.1.3 使用分支语句 验证null值的判断
定义两个整数变量 不赋值
declare
var_x number;
var_y number;
begin
if var_x > var_y then
dbms_output.put_line('var_x > var_y');
elsif var_x < var_y then
dbms_output.put_line('var_x < var_y');
elsif var_x is null and var_y is null then
dbms_output.put_line('var_x is null and var_y is null');
elsif var_x = var_y then
dbms_output.put_line('var_x = var_y');
end if;
end;
/
5.2 循环语句
5.2.1 简单循环
loop
-- 循环的代码
end loop;
5.2.2 如何退出循环
exit when 结束循环的条件;
if 退出条件 then
exit;
end if;
5.2.3 练习
写一个PLSQL程序 定义一个变量 把这个变量的值 使用简单循环 从 1 输出到 10
declare
var_i number:=1;
begin
loop
dbms_output.put_line(var_i);
exit when var_i=10;
var_i:=var_i+1;
end loop;
end;
/
declare
var_i number:=1;
begin
loop
dbms_output.put_line(var_i);
if var_i = 10 then
dbms_output.put_line('game over');
exit;
end if;
var_i:=var_i+1;
end loop;
end;
/
5.2.4 while 循环
语法:
while 循环条件 loop
end loop;
练习: 使用 while 循环 把一个变量的值 从1 输出 到 10
declare
var_i number:=1;
begin
while var_i < 11 loop
dbms_output.put_line(var_i);
var_i:=var_i+1;
end loop;
end;
/
declare
var_i number:=1;
begin
while var_i < 11 loop
dbms_output.put_line(var_i);
exit when var_i=5;
var_i:=var_i+1;
end loop;
end;
/
5.2.5 for 循环 --- 智能循环
自动定义出循环变量 变量会自动在区间内取值
begin
for var_i in 1..10 loop
dbms_output.put_line(var_i);
end loop;
end;
/
begin
for var_i in 1..100 loop
dbms_output.put_line(var_i);
end loop;
end;
/
使用 for 循环 从10 输出 到 1
begin
for var_i in 1..10 loop
dbms_output.put_line(11-var_i);
end loop;
end;
/
begin
for var_i in reverse 1..10 loop
dbms_output.put_line(var_i);
end loop;
end;
/
注意:for 循环中的 变量只能读 不能写
begin
for var_i in 1..10 loop
dbms_output.put_line(var_i);
-- var_i:=11;
end loop;
end;
/
5.2.6 多重循环
能不能满足 var_j=2 的情况下 退出外层循环?
begin
<<outerloop>>
for var_i in 1..3 loop
for var_j in 1..3 loop
dbms_output.put_line(var_j);
if var_j = 2 then
exit outerloop;
end if;
end loop;
end loop;
end;
/
5.3 goto 语句
5.3.1 语法
goto 标签名;
5.3.2 练习
使用 goto 语句 完成 把一个变量 从1 输出 到 10
declare
var_i number:=1;
begin
<<myloop>>
if var_i < 11 then
dbms_output.put_line(var_i);
var_i:=var_i+1;
goto myloop;
end if;
end;
/
5.3.3 结束循环
begin
for var_i in 1..3 loop
for var_j in 1..3 loop
dbms_output.put_line(var_j);
if var_j = 2 then
goto outerloop;
end if;
end loop;
end loop;
<<outerloop>>
NULL;
end;
/
标签 必须对应到一个语句 或者 语句块上
NULL; 代表空语句
6.游标 cursor 10*10*10
6.1 什么是游标
用来存放多条数据的一个结果集 。
6.2 游标的使用步骤
a.定义游标
declare
cursor 游标名 is select语句;
begin
b.打开游标
open 游标名;
c.提取数据 fetch 一次就拿到一条数据
fetch 游标名 into 变量;
d.关闭游标
close 游标名;
6.3 练习
定义一个游标 用来存放 s_dept 表中的所有数据 提取这些数据的前两条 打印
declare
cursor deptcursor is select * from s_dept;
-- var_dept s_dept%rowtype;
var_dept deptcursor%rowtype;
begin
open deptcursor;
fetch deptcursor into var_dept;
dbms_output.put_line(var_dept.id||':'||var_dept.name);
fetch deptcursor into var_dept;
dbms_output.put_line(var_dept.id||':'||var_dept.name);
close deptcursor;
end;
/
6.4 定义一个游标 用来存放 s_emp 表中的 id first_name salary.
提取这个游标的前两条数据 并打印。
