PLSQL
1、数据库访问相关的技术
1) plsql procedural 过程化sql
2) proc/c++ 在c/c++语言中嵌入sql语句实现对oracle数据库访问的技术
3) odbc/ado vc中访问数据库的技术
4) oci oracle底层提供的连接接口
5) jbdc java访问数据库的技术
2、PLSQL
2.1、概念
plsql(procedural language/sql) 是在标准sql的基础上增加了过程化的处理的语言。
Oracle客户端工具访问oracle服务器的操作语句
Oracle对SQL的扩充。
2.2、特点
结构化模块化编程
良好的可移植性,良好的可维护性
提升系统性能
不便于向异构数据库移植
3、SQL语言的特点
机器语言 汇编 高级语言 结构化语言
只管做什么,不管怎么做
没有过程和控制语句
没有算法描述能力
4、QL/SQL扩充了
1) 增加了变量和数据类型
2) 流程控制语句
3) 过程和函数
4) 对象类型和方法
5、plsql程序的结构
declare
/*声明区 声明变量、定义类型等*/
/*没有声明定义的内容,声明区可以省略*/
-- 单行注释
begin
/*执行区 执行sql或plsql语句*/
exception
/*异常处理区 程序出错后进行处理的区域*/
/*可以省略*/
end;
6、plsql的开发环境
sqlplus 命令行下的工具
plsql developer 可视化的工具
begin
dbms_output.put_line('Hello World!');
end;
/ --必须独立占一行
set serveroutput on //打开sqlplus的输出
7、标识符
7.1、作用
给变量、数据类型、游标、异常、过程、函数、包、触发器等命名。
7.2、使用变量
declare
变量名 类型;
变量名 类型:=值; --赋值 ':='
begin
变量名:=值;
declare
var_id number;
var_name varchar(20);
begin
var_id:=1;
var_name:='test';
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 cursor
4) 大类型 (一般保存大数据的文件路径)
BLOB 0~4g
CLOB 0~4g
BFILE
8.2、变量的修饰符
变量名 constant 类型
变量名 数据类型 not null
declare
--var_id constant number;constant修饰的变量必须初始化
var_id constant number:=1;
var_name varchar(25) not null;
begin
--var_id:=1;constant修饰的变量不能被赋值
varchar:='test';
dbms_output.put_line(var_id);
end;
/
declare
--var_id constant number;
var_id constant number:=1;
--var_name varchar2(25) not null;
var_name varchar2(25) not null:='test';
begin
--var_id:=1;
var_name:='test';
dbms_output.put_line(var_id);
end;
/
任何变量在初始化之前都是null
8.3、使用binary_integer和boolean声明变量
boolean true false null
declare
var_id binary_integer:=1;
var_f boolean;
begin
var_f:=true;
if var_f 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 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;
/
获取表中字段的类型:表名.字段%type
获取表中字段的值给变量赋值:
select 字段列表 into 变量列表 from 表名 where 条件;
要求有且只有一行结果
8.5、record类型 相当于c语言中的结构体
8.5.1、定义record类型
type 类型名 is record(
字段1 类型,
...
字段2 类型
);
8.5.2、定义record类型,成员分别和s_emp中id,first_name和salary的类型一致,声明该类型变量,接受制定编号的员工的信息并输出。
declare
type record_emp is record(id s_emp.id%type,
name s_emp.first_name%type,
salary s_emp.salary%type);
var_emp record_emp;
begin
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;
/
8.6、使用 表名%rowtype 变量
相当于将这张表中的所有字段的类型都作为一个特殊类型,且字段类型,顺序和表一致。
declare
var_emp s_emp%rowtype;
begin
select * into var_emp from s_emp where id=1;
dbms_output.put_line(var_emp.salary);
end;
/
8.7、table类型 类似于c语言中的数组
8.7.1、定义table类型的语法
type 类型名称 is table of 元素的数据类型
index by binary_integer;
8.7.2、定义一个table类型,声明变量用来保存多个数字
declare
type table_number is table of number
index by binary_integer;
vars_num table_number;
begin
vars_num(1):=1;
end;
/
8.7.3、下标连续,遍历table类型的变量
declare
type table_number is table of number
index by binary_integer;
vars_num table_number;
var_i number;
begin
vars_num(1):=100;
vars_num(7):=200;
vars_num(3):=300;
var_i:=1;
end;
/
下标不连续,便利table类型的变量
迭代器思想
first():获取第一个元素的键
last():获取最后一个元素的键
next(n):获取键为n的元素的下一个元素
declare
type table_number is table of number
index by binary_integer;
vars_num table_number;
var_i number;
begin
vars_num(1):=100;
vars_num(7):=200;
vars_num(3):=300;
var_i:=vars_num.first;
var_i:=vars_num.next(var_i);
end;
8.7.4、定义一个table类型,声明变量保存s_emp表中id为1,3,7的员工的信息,并输出
declare
type table_emp is table of s_emp%rowtype
index by binary_integer;
vars_emp table_emp;
var_i binary_integer;
begin
select * into vars_emp(1) from s_emp where id=1;
select * into vars_emp(3) from s_emp where id=3;
select * into vars_emp(7) from s_emp where id=7;
var_i:=vars_emp.first();
dbms_output.put_line(vars_emp(var_i).first_name||','||vars_emp(var_i).salary);
var_i:=vars_emp.next(var_i);
dbms_output.put_line(vars_emp(var_i).first_name||','||vars_emp(var_i).salary);
var_i:=vars_emp.next(var_i);
dbms_output.put_line(vars_emp(var_i).first_name||','||vars_emp(var_i).salary);
end;
/
8.8、变量的作用域
plsql代码块的嵌套
declare
var_m number:=100;
begin
declare
var_n number:=10;
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;
-- 在局部访问同名的全局变量,可以使用标签
-- 定义标签: <<标签名>>
-- 使用标签: 标签名.变量名
<<global>>
declare
var_n number:=100;
begin
declare
var_n number:=10;
begin
/*局部会隐藏同名全局变量*/
dbms_output.put_line(var_n);
dbms_output.put_line(global.var_n);
end;
end;
/
9、控制语句
9.1、分支语句
9.1.1、简单if
if 条件 then
操作;
end if;
9.1.2、if...else
if 条件 then
操作1
else
操作2
end if;
9.1.3、多分支if
if 条件1 then
操作1
elsif 条件2 then
操作2
...
[else 操作n]
end if;
9.1.4、定义三个变量,并赋值,输出最大值。
declare
num1 number:=1;
num2 number:=2;
num3 number:=3;
begin
if num1>num2 then
if num1>num3 then
dbms_output.put_line(num1);
else
dbms_output.put_line(num2);
end if;
else
if num2>num3 then
dbms_output.put_line(num2);
else
dbms_output.put_line(num3);
end if;
end if;
end;
/
9.1.5、NULL值的运算特点
declare
var_a number;
var_b number;
begin
if var_a>var_b then
dbms_output.put_line('var_a>var_b');
elsif var_a<var_b then
dbms_output.put_line('var_a<var_b');
elsif var_a=var_b then
dbms_output.put_line('var_a=var_b');
end if;
end;
/
空值参与的逻辑运算,其结果为假
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);
if var_i=10 then
exit;
end if;
var_i:=var_i+1;
end loop;
end;
/
9.2.2、while循环
1) 语法
while 条件 loop
循环操作
end loop;
2) 输出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;
/
9.2.3、for循环
1) 语法
for 变量 in 区间 loop
循环操作
end loop;
2) 输出1-10
begin
for var_i in 1..10 loop
dbms_output.put_line(var_i);
end loop;
end;
/
a.for循环的循环变量不需要声明和初始化
b.(var_i)循环变量不允许赋值
3) 逆序
begin
for var_i in reverse 1..10 loop
dbms_output.put_line(var_i);
end loop;
end;
9.3、goto语句
9.3.1 语法
<<标签名>>
--必须有语句
NULL;--空语句
goto 标签名;
9.3.2、使用goto退出多层循环
begin
for i in 1..3 loop
for j in 1..5 loop
dbms_output.put_line(j);
if(j=3) then
goto outer;
end if;
end loop;
end loop;
<<outer>>
NULL;
end;
/
9.3.3、使用exit退出多层循环
begin
<<outer>>
for i in 1..3 loop
for j in 1..5 loop
dbms_output.put_line(j);
if(j=3) then
exit outer;
end if;
end loop;
end loop;
end;
/
10、PLSQL使用Sql语句
1)selcet语句(dql)
配合into使用
select 字段列表 into 变量列表 from 表名 where 条件;
2)dml语句(insert update delete)
tcl语句(commit rollback savepoint)
可以直接在plsql中使用
3)ddl语句(create drop alter)
不能直接在plsql中使用
需要使用动态sql
11、动态sql
11.1、概念
把一条sql语句保存在字符串变量中,执行时把字符串变量的过程sql去执行
11.2、ddl语句的动态sql
declare
sqlstr varchar2(100);
begin
sqlstr:='create table testdsql_lx_42(id number)';
execute immediate (substr(sqlstr,1,length(sqlstr)-1) || ',name varchar2(25))');
end;
/
11.3、dml语句的动态sql
1) 在plsql中直接使用dml和tcl语句
begin
insert into testdsql_lx_42 values(1,'test1');
commit;
end;
/
2) 常规字符串拼接
declare
sqlstr varchar2(100);
begin
sqlstr:='insert into testdsql_lx_42 values(2,''test2'')';
execute immediate sqlstr;
commit;
end;
/
3) 带变量的字符串的拼接
declare
sqlstr varchar2(100);
var_id number:=3;
var_name varchar2(25):='test3';
begin
sqlstr:='insert into testdsql_lx_42 values('
|| var_id || ','
|| '''' || var_name || ''')';
execute immediate sqlstr;
end;
4) 使用占位符 配合using 实现字符串拼接
declare
sqlstr varchar2(100);
var_id number:=4;
var_name varchar2(25):='test3';
begin
sqlstr:='insert into testdsql_lx_42 values(:b0,:b1)';
execute immediate sqlstr using var_id,var_name;
end;
-------------------
1、定义table类型,声明变量保存s_dept中编号为31.41.45的部门的信息,并循环输出
2、使用动态sql 删除testdsql_lx_42中指定的id的数据行
declare
type table_dept is table of s_dept%rowtype
index by binary_integer;
vars_dept table_dept;
begin
select * into vars_dept(1) from s_dept where id=31;
select * into vars_dept(2) from s_dept where id=14;
select * into vars_dept(3) from s_dept where id=45;
for var_i in 1..3 loop
bdms_output.put_line(vars_dept(var_i));
end loop;
end;
/
declare
id number:=1;
sqlstr varchar(100);
begin
sqlstr:='delete table testdsql_lx_42 where id=:d';
execute immediate sqlstr using id;
end;
/
insert into 表名[(字段列表)] values(值列表);
update 表名 set 字段=新值[,字段=新值,...] [where 子句];
delete [from] 表名 [where 子句];
学习笔记day66-----oracle-qlsql中对oracle数据库的操作的模块化
最新推荐文章于 2024-06-13 14:21:51 发布