PL\SQL(day05)
PLSQL
1.常用的访问数据库的相关技术
- plsql 过程化的sql
- proc/c++ 在c/c++语言中访问oracle数据库的技术
- ado/odbc vc中访问数据库的技术
- oci oracle底层提供的客户端的连接接口
- sql j/jdbc java访问数据库的技术
2.PLSQL
2.1 概念
plsql(procedured language/sql)是在标准sql的基础上增加了过程化的处理形成的语言
oracle客户端工具访问oracle服务器的操作语言
是对sql的扩充
2.2 特点
结构化模块化编程
良好的可移植性
良好的可维护性
提升系统性能
不便于向异构数据库移植
3.SQL语言的特点
机器语言 汇编语言 高级语言 结构化语言
只管做什么 不管怎么做
没有过程和控制结构
没有算法描述能力
4.plsql扩充了sql
- 变量和数据类型
- 控制语句
- 过程和函数
- 对象类型和方法
5.plsql的程序框架
declare
/* 声明区 声明变量、定义类型等 /
-- 单行注释 '--'
-- 多行注释 /内 容/
-- 如果没有需要声明、定义的内容,声明区可以省略
begin
/ 执行区 执行sql或plsql语句 /
exception
/ 异常处理区 处理异常的区域 /
/ 可省略 */
end;
/ -- 表示plsql代码结束 必须独立占一行
6.plsql的开发工具
sqlplus 命令提示符的工具
plsqldeveloper 图形化的开发工具
begin
dbms_output.put_line('Hello World!');
end;
/
-- 打开输出功能
set serveroutput on
--调整字段占位长度
set 字段名 for(format) a+字段长度
或者
set linesize + 字段长度
7.标识符
7.1 作用
给变量、数据类型、游标、过程、函数、触发器、包等命名
7.2 使用变量
declare
变量名 数据类型;
变量名 数据类型 := 值;
begin
变量名 := 值;
-- 声明两个变量,赋值并输出
declare
var_id number;
var_name varchar2(20):='test';
begin
var_id:=1;
dbms_output.put_line(var_id||','||var_name);
end;
/
8.变量和数据类型
8.1 数据类型
1) 标量类型
number binary_integer 数字类型
char varchar2 字符串类型
date 日期类型
boolean 布尔类型
2) 复合类型
record
table
3) 参考类型 ref
ref cursor(参考游标)
4) 大类型
BLOB 0-4g
CLOB 0-4g
BFILE
8.2 变量的修饰符
变量名 constant 数据类型;
变量名 数据类型 not null;
declare
-- (1)
-- var_id constant number;
var_id constant number:=1;
-- (3)
-- var_name varchar(20) not null;
var_name varchar(20) not null:='1';
begin
-- (2)
-- var_id:=1;
var_name:='test';
dbms_output.put_line(var_id);
dbms_output.put_line(var_name);
end;
/
(1) 用constant修饰的变量必须初始化
(2) 用constant修饰的变量不允许赋值
(3) 用not null修饰的变量必须初始化
任何类型的变量,在赋值前 初值都是null
8.3 使用binary_integer和boolean定义变量
true false null
declare
var_id binary_integer:=100;
var_flag boolean;
begin
var_flag:=true;
if var_flag then
dbms_output.put_line(var_id);
end if;
end;
/
8.4 声明两个变量,类型分别和s_emp表中id、first_name相同,用来接收id=1的员工的id和first_name,并输出
declare
var_id number(7);
var_name varchar2(25);
begin
var_id:=1;
var_name:='Carmen';
dbms_output.put_line(var_id||','||var_name);
end;
/
获取表中字段的数据类型:表名.字段名%type
使用select语句给变量赋值:
select 字段列表 into 变量列表 from 表名 where 条件;
注意:字段列表和变量列表的顺序、数量和数据类型要一致
select语句有且只有一行结果
declare
var_id s_emp.id%type;
var_name s_emp.first_name%type;
begin
select id,first_name into var_id,var_name from s_emp
where id=1;
dbms_output.put_line(var_id||','||var_name);
end;
/
8.5 record类型 相当于C语言中的结构体
8.5.1 定义record类型
type 记录类型名 is record(
字段名 类型,
...
字段名 类型
);
8.5.2 定义record类型,有三个字段,类型分别和s_emp表中id,first_name,salary相同。声明该类型变量,用于接收id=2的员工的id,first_name和salary,并输出。
declare
-- 定义record类型
type emprecord is record(
id s_emp.id%type,
name s_emp.first_name%type,
sal number(11,2)
);
-- 声明record类型变量
var_emp emprecord;
var_emp2 emprecord;
begin
select id,first_name,salary into var_emp from s_emp
where id=2;
dbms_output.put_line(var_emp.id||','||var_emp.name||','
||var_emp.sal);
var_emp2:=var_emp;
dbms_output.put_line(var_emp2.id||','
||var_emp2.name||','
||var_emp2.sal);
end;
/
-- 查询的字段的数量少于record类型的字段的数量
declare
-- 定义record类型
type emprecord is record(
id s_emp.id%type,
name s_emp.first_name%type,
sal number(11,2)
);
-- 声明record类型变量
var_emp emprecord;
begin
select id,first_name into var_emp.id,var_emp.name
from s_emp where id=2;
dbms_output.put_line(var_emp.id||','||var_emp.name||','
||var_emp.sal);
end;
/
-- 接收表中的全部字段,record类型?
8.5.3 用于接收表中整行数据的类型
表名%rowtype
-- 查询s_emp表中 id=3的员工的全部信息,保存在变量中并输出
declare
var_emp s_emp%rowtype;
begin
select * into var_emp from s_emp where id=3;
dbms_output.put_line(var_emp.id||','||
var_emp.first_name||','||
var_emp.title);
end;
/
8.6 table类型 类似于C语言中的数组
8.6.1 定义table类型的语法
type table类型 is table of 元素的数据类型
index by binary_integer;
8.6.2 定义一个table类型,声明变量,保存多个数字
declare
/* 定义table类型 /
type numstable is table of number
index by binary_integer;
/ 声明table类型的变量 */
var_nums numstable;
begin
var_nums(3):=100;
var_nums(7):=200;
var_nums(1):=300;
dbms_output.put_line(var_nums(3));
-- dbms_output.put_line(var_nums(2));
end;
8.6.3 下标连续时,table类型变量的访问
declare
/* 定义table类型 */
type numstable is table of number
index by binary_integer;
/* 声明table类型的变量 */
var_nums numstable;
/* 声明变量 表示下标 */
var_i binary_integer;
begin
var_nums(3):=100;
var_nums(5):=200;
var_nums(4):=300;
var_i:=3;
dbms_output.put_line(var_nums(var_i));
var_i:=var_i + 1;
dbms_output.put_line(var_nums(var_i));
var_i:=var_i + 1;
dbms_output.put_line(var_nums(var_i));
-- var_i:=var_i + 1;
end;
/
8.6.4 下标不连续时,对table类型变量的遍历
first() -- 获取第一个元素的下标
next(n) -- 获取下标为n的元素的下一个元素的下标
last() -- 获取最后一个元素的下标
declare
/* 定义table类型 */
type numstable is table of number
index by binary_integer;
/* 声明table类型的变量 */
var_nums numstable;
/* 声明变量 表示下标 */
var_i binary_integer;
begin
var_nums(3):=100;
var_nums(7):=200;
var_nums(4):=300;
var_i:=var_nums.first();
dbms_output.put_line(var_nums(var_i));
var_i:=var_nums.next(var_i);
dbms_output.put_line(var_nums(var_i));
var_i:=var_nums.next(var_i);
dbms_output.put_line(var_nums(var_i));
-- var_i:=var_nums.next(var_i);
end;
8.6.5 定义table类型变量,保存s_emp表中id为1,3,7的员工的信息,并输出
declare
/* 定义table类型 /
type empstable is table of s_emp%rowtype
index by binary_integer;
/ 声明table类型变量 /
var_emps empstable;
/ 声明变量 保存下标 */
var_i binary_integer;
begin
select * into var_emps(1) from s_emp where id=1;
select * into var_emps(3) from s_emp where id=3;
select * into var_emps(7) from s_emp where id=7;
var_i:=var_emps.first();
dbms_output.put_line(var_emps(var_i).id||','||
var_emps(var_i).first_name||','||
var_emps(var_i).salary);
var_i:=var_emps.next(var_i);
dbms_output.put_line(var_emps(var_i).id||','||
var_emps(var_i).first_name||','||
var_emps(var_i).salary);
var_i:=var_emps.next(var_i);
dbms_output.put_line(var_emps(var_i).id||','||
var_emps(var_i).first_name||','||
var_emps(var_i).salary);
var_i:=var_emps.next(var_i);
end;
8.6. 变量的作用域
declare
-- 全局
var_a number:=1;
begin
declare
-- 局部
var_b number:=2;
begin
dbms_output.put_line(var_a);
dbms_output.put_line(var_b);
end;
dbms_output.put_line(var_a);
-- dbms_output.put_line(var_b);
end;
局部既可以访问局部变量,也可以访问全局变量
全局 只可以访问全局变量
局部变量和全局变量重名时,局部变量会覆盖同名的全局变量
-- 局部访问同名的全局变量:
使用标签
定义标签: <<标签名>>
使用标签: 标签名.变量名
<<g>>
declare
-- 全局
var_a number:=1;
begin
declare
-- 局部
var_a number:=2;
begin
dbms_output.put_line(g.var_a);
end;
dbms_output.put_line(var_a);
end;
-
控制语句
9.1 if语句
9.1.1 语法- 简单if语句
if 条件 then
操作
end if; - if...else语句
if 条件 then
操作1
else
操作2
end if; - 多分支if
if 条件1 then
操作1
elsif 条件2 then
操作2
....
else
操作n
end if; - 嵌入if
if 条件1 then
if 条件2 then
操作1
else
操作2
end if;
else
if 条件3 then
操作3
else
操作4
end if;
end if;
9.1.2 练习:声明三个数字类型变量并赋值,输出最大值
-- 方法一:
declare
var_a number:=1;
var_b number:=200;
var_c number:=100;
begin
if var_a > var_b then
if var_a > var_c then
dbms_output.put_line(var_a);
else
dbms_output.put_line(var_c);
end if;
else
if var_b > var_c then
dbms_output.put_line(var_b);
else
dbms_output.put_line(var_c);
end if;
end if;
end;
/-- 方法二:
declare
var_a number:=&var_a;// 后面这'&'是输入相当于c语言里的scanf
var_b number:=&var_b;
var_c number:=&var_c;
var_temp number;
begin
var_temp:=var_a;
if var_b > var_temp then
var_temp:=var_b;
end if;
if var_c >var_temp then
var_temp:=var_c;
end if;
dbms_output.put_line(var_temp);
end;9.1.3 NULL的运算特点
declare
var_a number;
var_b number;
begin
if var_a > var_b then
dbms_output.put_line('a>b');
elsif var_a < var_b then
dbms_output.put_line('a<b');
elsif var_a = var_b then
dbms_output.put_line('a=b');
elsif var_a is null and var_b is null then
dbms_output.put_line(
'var_a is null and var_b is null');
end if;
end;
/ - 简单if语句
9.2 循环语句
循环变量初始化 循环条件 循环操作 条件的更新
9.2.1 简单循环
1) 语法
loop
-- 循环操作
end loop;
2) 结束循环的方式
-- 方式一:
if 退出循环的条件 then
exit;
end if;
-- 方式二:
exit when 退出循环的条件;
3) 使用简单循环输出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;
/
-- 使用if退出循环
declare
var_i number:=10;
begin
loop
dbms_output.put_line(var_i);
if var_i=1 then
dbms_output.put_line('loop over!');
exit;
end if;
var_i:=var_i-1;
end loop;
end;
/
9.2.2 while循环
1) 语法
while 条件 loop
-- 循环操作
end loop;
2) 使用while循环输出1..10
declare
var_i number:=1;
begin
while var_i<=10 loop
dbms_output.put_line(var_i);
var_i:=var_i+1;
end loop;
end;
-- 练习:从1..100累加,输出和大于2000时对应的数字
declare
var_i number:=1;
var_sum number:=0;
begin
while var_i<=100 loop
var_sum:=var_sum + var_i;
if var_sum >=2000 then
dbms_output.put_line(var_i);
exit;
end if;
var_i := var_i + 1;
end loop;
end;
9.2.3 for 循环
智能循环
1) 语法
for 循环变量 in 区间 loop
-- 循环操作
end loop;
2) 使用for 循环输出1..10
begin
for var_i in 1..10 loop
dbms_output.put_line(var_i);
end loop;
end;
-- 练习 使用for循环输出10..1
begin
for var_i in 10..1 loop
dbms_output.put_line(var_i);
end loop;
end;
-- 上边代码循环一次也不执行
使用 reverse 实现倒序输出
begin
for var_i in reverse 1..10 loop
dbms_output.put_line(var_i);
end loop;
end;
-- 使用退出循环的方式 提前结束循环
begin
for var_i in 1..10 loop
dbms_output.put_line(var_i);
if var_i=5 then
-- var_i:=11; 错误
end if;
end loop;
end;
a.循环变量不需要声明
b.区间必须是从小到大 使用reverse实现反转循环
c.循环变量不允许赋值
9.3 跳转语句 goto
9.3.1 语法
<<标签名>>
-- 这里必须有语句
NULL; -- 空语句
goto 标签名;
9.3.2 使用goto退出嵌套循环
begin
for var_i in 1..3 loop
for var_j in 1..5 loop
dbms_output.put_line(var_j);
if var_j=3 then
goto outer;
end if;
end loop;
end loop;
<
NULL;
end;
9.3.3 使用if..exit方式退出循环
begin
<
for var_i in 1..3 loop
for var_j in 1..5 loop
dbms_output.put_line(var_j);
if var_j=3 then
exit outer;
end if;
end loop;
end loop;
dbms_output.put_line('loop over');
end;
- plsql中使用sql语句
- select语句
select语句要和into 配合使用
select 字段列表 into 变量列表 from 表名 where 条件; - dml语句
tcl语句
可以直接在plsql中使用 - ddl语句
不能直接在plsql中使用 需要用动态sql实现
11.动态sql
11.1 概念
把一条字符串对应的sql语句,当成真正的sql语句去执行
11.2 案例: 创建一张表
/* 错误:ddl语句不能直接在plsql中使用 */
begin
create table testdsql_zsm_00(id number);
end;
-- 在plsql中可以使用函数
declare
sqlstr varchar2(100);
begin
sqlstr:='create table testdsql_zsm_00(id number)';
sqlstr:=substr(sqlstr,1,length(sqlstr)-1)
||',name varchar2(20))';
-- dbms_output.put_line(sqlstr);
execute immediate sqlstr;
end;
11.3 DML语句的动态sql
1) 直接在plsql中使用dml和tcl语句
begin
insert into testdsql_zsm_00 values(1,'test1');
commit;
end;
2) 常规字符串的拼接
declare
sqlstr varchar2(100);
begin
sqlstr:= 'insert into testdsql_zsm_00 values(2,''test2'')';
execute immediate sqlstr;
commit;
end;
3)带有变量的字符串的拼接
declare
var_id number:=3;
var_name varchar2(20):='test3';
sqlstr varchar2(100);
begin
sqlstr:='insert into testdsql_zsm_00 values('||var_id||
','''||var_name||''')';
-- dbms_output.put_line(sqlstr);
execute immediate sqlstr;
commit;
end;
- 使用占位符 配合using解决字符串拼接的问题
占位符: :占位符名
execute immediate sqlstr using 变量列表;
declare
var_id number:=4;
var_name varchar2(20):='test4';
sqlstr varchar2(100);
begin
sqlstr:='insert into testdsql_zsm_00 values(:b0,:b1)'; //:b0 :b1 就是占位符
execute immediate sqlstr using var_id,var_name;
commit;
end;
11.4 select语句动态sql
必须是普通的select语句(不带into)
select语句有且只有一行结果
declare
sqlstr varchar2(100);
var_name s_emp.first_name%type;
begin
sqlstr:='select first_name from s_emp where id=1';
execute immediate sqlstr into var_name;
dbms_output.put_line(var_name);
end;
总结:
1.变量和类型
定义和使用record和table类型
type 类型名 is record(
字段 类型,
....
字段 类型
);
type 类型名 is table of 元素类型 index by binary_integer;
表名.字段%type
表名%rowtype
2.控制语句
分支:if
循环:简单循环、while循环、for循环
跳转语句:goto
结束循环的两种方式:
exit when 退出循环的条件;
if 退出循环的条件 then
exit;
end if;
3.动态sql
sqlstr varchar2(200):='';
execute immediate sqlstr;
'insert into 表名 values(:b0,:b1,....)'
execute immediate sqlstr using 变量1,变量2,....;
练习:
1.定义record类型,声明变量,保存s_dept表中id=31的部门的
信息
- 使用动态sql语句,删除testdsql_zsm_00表中指定id 的信息