PL/SQL编程基础及运用
示例数据在文章尾部
-
什么是PL/SQL? (一种块结构的语言）它是oracle对标准数据库语言的扩展，就像oracle数据库内的一个引擎，负责处理PL/SQL程序块。基于事务、支持SQL数据类型和函数、具有良好的可重用性。
-
PL/SQL块的命名和匿名
PL/SQL程序块是一段执行业务逻辑的代码块，可以将它编译后命一个名称以方便以后调用。叫命名程序块。
命名程序块有：函数、存储过程、包、触发器等。
如果要调用一段没有名称的程序块（匿名），需要将整段程序发送到服务器临时编译执行。
- PL/SQL的结构
每个PL/SQL程序中每条语句须以分号结束，程序块可分为3个部分：
声明部分、执行部分、异常处理部分
declare --声明部分
begin
[exception] --执行部分
--异常处理部分
end;
声明部分：用于声明变量、常量的数据类型和初始值，由declare关键字开始。
执行部分：由begin关键字开始，所有可执行的语句或其他的PL/SQL块都放在这里。
异常处理部分：可选部分，用于处理异常和错误。
以declare或begin开始，以end结束程序块。注释可以用--
- PL/SQL中的数据类型和运算符
包含：数字、字符、日期、大对象、逻辑、空值、属性、对象等类型。
常用的有number（int,float）、char、varchar2、true、false、null、%type、%rowtype等。
%type 指取列的数据类型，%rowtype 指表的行结构，是个对象类型。
数据运算符：算术、关系、逻辑、比较、连接。
- PL/SQL的过程数据显示
指定输出结果显示在客户端程序的开关：
set serveroutput on;
PL/SQL程序块在服务器端执行，但我们希望在客户端输出结果，可以使用函数：
DBMS_OUTPUT.put_line();
如：
begin
dbms_output.put_line('你好，PL/SQL'); --在客户端输出一行文本
dbms_output.new_line(); --输出一个新行
end;
- PL/SQL的常量和变量
在声明中赋值一种为 defaul，另一种是 “ := ”
声明：
declare
user_name varchar2(20) ;
user_age int := 22; --声明同时赋值
user_sex varchar2(10) default '男'; --默认赋值为男
begin
dbms_output.put_line(user_name || ',' || user_age || '岁,' || user_sex );
end;
在程序执行中赋值一种是“ := ”, 另一种是利用select…into查询语句赋值
如：
select emp.ename into user_name from scott.emp emp where empno=7369;
#在PL/SQL中，select查询只能作为赋值语句，不能使用单独的查询语句.
- PL/SQL中的控制语句
- 条件控制：
IF语法
if语法1:
IF 条件表达式 THEN
执行语句
end IF;
例:
declare
emp_no int := 7369;
user_name varchar2(20) ;
user_age int := 22; --声明同时赋值
must_age int := 18;
begin
select emp.ename into user_name from scott.emp emp where empno=emp_no;
if user_age > must_age then
dbms_output.put_line(user_name || '已经达到18岁了,可以浏览');
end if;
end;
语法2：
IF 条件表达式 THEN
执行语句
ELSE
执行语句
end IF;
语法3：
IF 条件表达式 THEN
执行语句
ELSIF 条件表达式 THEN
执行语句
ELSE
执行语句
end IF;
CASE语法
CASE语法1:
CASE 字符串变量
WHEN 结果值1 THEN 执行语句;
WHEN 结果值2 THEN 执行语句;
ELSE 执行语句;
end CASE;
CASE语法2:
CASE
WHEN 条件表达式1 THEN 执行语句;
WHEN 条件表达式2 THEN 执行语句;
ELSE 执行语句;
end CASE;
例：
declare
pay scott.emp.sal %type;
eno int := 7369;
result varchar2(20);
begin
select e.sal into pay from scott.emp e where e.empno=eno;
case
when pay < 1000 then result := '低收入者';
when pay < 3000 then result := '工人阶级';
when pay < 5000 then result := '白领阶层';
else result := '高收入人群';
end case;
dbms_output.put_line( eno || '号员工是' || result );
end;
- 循环控制
LOOP 语法
loop
exit when 条件表达式;
end loop;
示例：1+2+3+4...+100
declare
i int := 1;
s int := 0;
begin
loop
exit when i > 100;
s := s + i;
i := i + 1;
end loop;
dbms_output.put_line('1+2+3...+100=' || s );
end;
FOR 语法
for 计数器变量名 in 起始值..结束值
loop
执行语句;
end loop;
示例:
declare
s int := 0; --必须初始化，否则没结果
begin
for i in 1..100
loop
s := s + i;
end loop;
dbms_output.put_line('for循环结果为=' || s);
end;
#计数器变量不用声明和初始化，在执行过程中且不能被赋值
WHILE 语法
while 条件表达式
loop
执行语句;
end loop;
示例:
declare
s int := 0;
i int := 1;
begin
while i < 101
loop
s := s + i;
i := i + 1;
end loop;
dbms_output.put_line('while循环结果为=' || s);
end;
- 跳转控制 goto、return
goto直接跳到节点处，return直接终止过程执行。
goto节点定义语法 <<节点名>>
- PL/SQL记录（结构体）
PL/SQL允许自定义一个结构体的类型，并可用它声明变量。一个包含多个变量定义的类型，称为PL/SQL记录。
定义记录的语法：
type 类型名称 is record (列名1 数据类型, 列名2，数据类型, 列名n 数据类型);
示例：
declare
type myemp is record ( e_no scott.emp.empno %type,
e_name scott.emp.ename %type,
e_pay scott.emp.sal %type);
--记录来声明变量
test myemp;
begin
select empno, ename, sal into test from scott.emp where empno = 7369;
dbms_output.put('test.e_no===='|| test.e_no || ' ');
dbms_output.put('test.e_name===='|| test.e_name || ' ');
dbms_output.put_line('test.e_pay===='|| test.e_pay);
end;
- 异常
异常分为内部异常和用户自定义异常两大类。
异常通常有错误号码和异常名称。
oracle内部对有些异常提供了错误名称，这些是预定义异常，但大多数的异常号码没有提供错误名称。
- 处理异常
对于有名称的异常，在PL/SQl块中使用系统给的异常名称捕获预定义异常。
语法：
exception
when 异常名称 then 执行语句;
示例：
declare
i int := 2;
p int ;
begin
i := i / 0;
dbms_output.put_line('i=' || i);
--有多个deptno=10的用户，那么返回的sal自然有多个值
select sal into p from scott.emp where deptno = 10;
--上面的语句将多个值插入到变量p中，自然发生异常
exception
when zero_divide then
dbms_output.put_line('除数不能为零！');
when too_many_rows then
dbms_output.put_line('返回的结果太多！');
end;
- 自定义异常
需要先定义一个异常类型的变量，在需要产生异常的地方使用raise引发，并在异常处理部分捕获处理。
示例：
declare
no_money_err exception;
user_total int := 100; --用户帐户的余额
user_money int := 150; --用户准备取出的金额
begin
if user_total < user_money then
raise no_money_err;
else
dbms_output.put_line('交易成功');
end if;
exception
when no_money_err then dbms_output.put_line('对不起，帐户余额不足。交易失败！');
end;
对于没有名称的内部异常，可以先定义一个自定义异常的变量，再将异常变量与一个异常编号关联
这样就可以捕获和抛出系统已定义但没有名称的异常。
将异常变量与异常编号关联的语法：
pragma exception_init( 变量名, 异常编号 );
示例：
declare
my_ex exception;
my_drop_ex exception;
pragma exception_init(my_ex, -1); --将变量关联到编号为-1的内部异常
pragma exception_init(my_drop_ex, -942);
begin
--execute immediate 'drop table ffff'; --动态执行SQL命令（DDL）
insert into scott.emp(empno, ename) values(7369,'xxx');
exception
when my_ex then dbms_output.put_line('对不起，主键冲突！');
when my_drop_ex then dbms_output.put_line('此表不存在！');
end;
若想自定义异常的编号和异常消息，可以使用
语法：
raise_application_error( 异常编号, 异常消息 )
编号取值范围: - 20000 至 - 20999
消息长度： 2048字节。
此语句可以放在执行语句处，也可放在异常处理代码处。
同时还可以使用空的raise语句，在必要的时候进行异常传播。
- 动态SQL
在PL/SQL中直接执行字符串内容的SQL命令语句的操作，叫做动态SQL。
如果被执行的SQL语句中有参数(占位符)，可以用Using按顺序传入，还可以将查询的结果赋给变量。
语法：
execute immediate 'SQL语句字符串' [Using 实参1，实参2];
例：
declare
sql_str varchar(200);
my_emp scott.emp %rowtype; --定义一个行类型的变量
begin
execute immediate 'create table ffff ( test_id int , test_name varchar2(20) )';
--为动态SQL传入实参
sql_str := 'insert into ffff values ( :1, :2 )';
execute immediate sql_str using 2, '赵六';
--传参给占位符，且将语句执行结果赋给一个 行类型变量
sql_str := 'select * from scott.emp where empno = :AAA ';
execute immediate sql_str into my_emp using 7369;
dbms_output.put_line('my_emp行数据中 empno=' || my_emp.empno || ', ename=' || my_emp.ename );
execute immediate 'drop table ffff';
end;
示例数据：
--删除表
drop table stumarks;
drop table stuinfo;
--创建表
create table stuinfo (
stuName varchar2(20),
stuNo varchar2(10) primary key,
stuSex varchar2(8) ,
stuAge number(3),
stuSeat number(2),
stuAddress varchar2(32));
create table stuMarks(
ExamNo varchar2(10) primary key,
stuNo varchar2(10),
jishi number(3),
bishi number(3));
--加外键约束
alter table stumarks add constraint stufk foreign key(stuno) references stuinfo(stuno);
--插入数据
insert into stuInfo values('张秋丽','s25301','男',19,1,'北京海淀');
insert into stuInfo values('李文材','s25302','男',28,2,'湖北武汉武昌区');
insert into stuInfo values('李斯文','s25303','女',31,3,'湖北西城县');
insert into stuInfo values('欧阳丰','s25304','男',44,4,'北京西城');
insert into stuInfo values('梅超风','s25326','女',23,5,'江南地区');
insert into stuMarks values('s27181','s25303',93,51);
insert into stuMarks values('s27183','s25301',67,91);
insert into stuMarks values('s27185','s25302',90,83);
insert into stuMarks values('s27188','s25304',75,58);
--查看表
select * from stuInfo;
select * from stuMarks;
扫一扫
2161
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
