一.大概的了解PL/SQL
１.定义：
PL（Procedural Languag）/SQL（Structured Query Language）是由Oracle开发，是Oracle数据库对SQL语句的扩展，增加了编程语言的特点
２.特点
１．改善了性能
整个语句块发送给服务器，这个过程在单次调用中完成，降低了网络拥挤
２.可重用性
PL/SQL能运行在任何ORACLE环境中（不论它的操作系统和平台），在其他ORACLE能够运行的操作系统上无需修改代码。
３.模块化
每个PL/SQL单元可以包含一个或多个程序块，程序中的每一块都实现一个逻辑操作，从而把不同的任务进行分割，由不同的块来实现，块之间可以是独立的或是嵌套的
３.PL/SQL块
PL/SQL语言以块为单位，块中可以嵌套子块。一个基本的PL/SQL块由3部分组成：
１.定义部分（DECLARE）
与其它语言一样，PL/SQL中使用的变量、常量、游标和异常处理的名字都必须先定义后使用。并且必须定义在以DECLARE关键字开头的定义部分
２.可执行部分（BEGIN）
该部分是PL/SQL块的主体，包含该块的可执行语句。该部分定义了块的功能，是必须的。 由关键字BEGIN开始，以END结束。
３.异常处理部分（EXCEPTION）
该部分包含块的异常处理程序（错误处理程序）。当该块程序体中的某个语句出现异常（检测到一个错误）时，oracle将程序控制转到异常部分的相应的异常处理程序中进行进一步的处理。该部分由关键字EXCEPTION开始，END关键字结束
４.PL/SQL中变量
１.标识符的命名
（和ｏｒａｃｌｅ命名规则相同）
至多有30个字符
不能是保留字
必须以字母开头
包含字母、$、_、# 、数字符号
２.变量的类型
PL/SQL变量
–标量型(只能存储单值、内部没有分量）
–复合型
–引用型
–LOB型 (大型的对象)
外部变量(非PL/SQL变量)
–表单应用程序中的屏幕域
–SQL*Plus 主机变量
３.声明 PL/SQL 变量
语法
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
举例
Declare
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
４.变量的赋值
语法
identifier := expr;举例
v_ename := 'Maduro';５.%TYPE 的属性
１.通过%TYPE属性声明一个变量，实际上就是参照变量或者表中字段的类型作为变量的类型，并且保持同步。变量将遵循下面的类型声明：
–已经声明过的变量类型
–数据库中表的字段类型
２.可以作为%TYPE前缀的可以是
表名.列名
前面声明的变量名称
３.PL/SQL在运行程序时确定变量的数据类型和大小
语法
变量名 已经定义的变量%type;举例
v_min_balance v_balance%TYPE := 10;５.PL/SQL 块的代码注释
１./* 和*/之间的多行注释
２.单行注释，以 －－ 开始
３.建议注释的地方
程序头部：说明程序的主要功能，程序的作者，创建日期，修改日期及本次修改内容，各主要输入参数，输出参数的说明。
声明部分：说明主要变量，常量，游标等。
程序体中重要的算法：说明主要的算法、思路
二．函数的使用
１.举例
declare
v_hiredate emp.hiredate%emp;
begin
select to_date(2021-12-13,'yyyy-mm-dd') into v_hiredate
from dual;
dbms_output.put_line(v_hiredate);
end;注：每定义一个值一个分号，begin后面的sql语句结束一个分号，dbms输出之后一个分号
２.通过变量插入数据
declare
c_empno emp.empno%type:=99;
c_ename emp.ename%type:='林夕';
begin
insert into emp(empno,ename)values(
c_empno,c_ename
);
commit;
end;３.通过变量更新数据
declare
c_empno emp.empno%type:=99;
begin
update emp
set empno = empno+1
where empno = 99;
end;４.通过变量删除数据
declare
c_empno emp.empno%type:=99;
begin
delete from emp
where empno = 100;
end;三．控制执行部分
1.if语句
--1.语法
if conditon then
else if then
else
end if;declare
v_x number(2):=5;
v_y number(2):=null;
begin
if v_x != v_y then
dbms_output.put_line(100); －－这是输出语句
else dbms_output.put_line(200);
end if;
end;
--结果为200 注意分号 null和0不能比较 null和null也不能比较2.case语句
--1.语法
case condition
when then
when then
else
end;－－实例
declare
v_var varchar(10);
v_deptno emp.deptno%type;
begin
select deptno into v_deptno
from emp
where sal=(
select max(sal)
from emp
);
v_var:=
case v_deptno
when 10 then '部门1'
when 20 then '部门2'
else '部门3'
end;
dbms_output.put_line('部门是：'||v_var);
end;３.loop循环
--1.语法
loop
exit when condition;
end loop;--2.实例
declare
v_count number(2):=1;
v_empno emp.empno%type:=10;
begin
--变量的迭代
loop
insert into emp(empno)
values(v_empno+v_count);
v_count:=v_count+1;
exit when v_count>3;
end loop;
end;４.for循环
--1.语法
for v_count in 1..3 loop
sql语句；
nd loop;--2.实例
declare
v_count number(2):=1;
v_empno emp.empno%type:=10;
begin
for v_count in 1..3 loop
insert into emp(empno)
values(v_empno+v_count);
v_count:=v_count+1;
end loop;
end;５.while循环
－－１.语法
while condition loop
sql语句
end loop;－－２.实例
DECLARE
v_count number(2):= 1;
v_empno emp.empno%type;
v_ename emp.ename%type:='ljs';
v_job emp.job%type:='manager';
BEGIN
SELECT max(empno) INTO v_empno
FROM emp;
WHILE v_count <= 3 LOOP
INSERT INTO test(empno,ename,job)
VALUES ((v_empno+v_count),v_ename,v_job);
v_count := v_count + 1;
END LOOP;
END;
四．游标
１．定义
游标（cursor）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。
2.显示游标
--1.自己创建
--2.定义：用于从表中取出多行数据，并将多行数据一行一行单独处理（即用循环）
３.实例
declare
v_empno emp.empno%type;
v_ename emp.ename%type;
--1.创建游标
cursor emp_cursor is
select empno,ename
from emp;
begin
--2.开启游标
open emp_cursor;
for i in 1..5 loop
--3.获取游标中的数据给变量赋值
fetch emp_cursor into v_empno,v-ename;
--输出数据
dbms_output.put_line(v_ename);
--结束循环
end loop;
--4.关闭游标
close emp cursor;
end;４.带有参数的游标
declare
v_deptno emp.deptno%type;
v_ename emp.ename%type;
cursor c_emp
(
p_deptno emp.deptno%type,
p_sal emp.sal%type) is
select deptno,ename
from emp
where deptno=p_deptno and sal>p_sal;
begin
--开启游标并且给游标的参数赋值 开启游标之后执行 声明中的sql语句
－－并将符合的返回值存在游标里
open c_emp(10,2000);
--循环
loop
--获取游标中的数据给变量赋值 fetch:取得
fetch c_emp into v_deptno,v_ename;
dbms_output.put_line('姓名是:' || v_ename);
--退出循环条件
exit when c_emp%notfound;
--退出循环
end loop;
end;５．隐式游标
１．特点
--1.由oracle在内部声明
--2.由oracle自行管理游标
--3.可以使用游标属性从最近执行的sql语句中获取信息
--4.用于处理DML语句以及返回单行的查询
--5.定义：PL/SQL隐式建立并自动管理这一游标
２.sql%rowcount:返回的记录行数
sql%rowcount: 返回迄今为止已经从游标中取出的记录数目
--实例
select * from emp;
declare
v_deptno emp.deptno%type:=30;
v_row_count number(4); --接收返回记录的行数
begin
delete from emp
where deptno=v_deptno;
--将返回记录的行数赋值给v_row_count
v_row_count:=sql%rowcount;
dbms_output.put_line(v_row_count);
end;３.%isopen和%notfound的使用
%isopen 如果游标是打开的，其值为TRUE
%notfound 如果FETCH 语句没有返回记录，其值为TRUE（％ｆｏｕｎｄ与其相反）
--实例
declare
a emp.empno%type;
b emp.ename%type;
--创建游标
cursor c is
select empno,ename
from emp;
begin
--判断游标是否开启
if not c%isopen then
open c
end if;
loop
fetch c into a,b;
dbms_output.put_line(a);
--当游标没有发现时
exit when c%notfound
end loop;
end;五．储存过程
１．特点
２.创建过程语法
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
IS|AS
PL/SQL Block;
３.形参和实参
形式参数：在子程序说明的参数列表中声明的变量
CREATE PROCEDURE raise_sal(
p_id NUMBER,
p_amount NUMBER)
...
END raise_sal;
• 实际参数：在子程序被调用的参数列表中引用的变量或表达式
raise_sal(v_id, 2000) ４.ｉｎ
和数据库中的值比较，场景修改数据或者条件的结果
create or replace procedure raise_sal
(
p_id in emp.empno%type
)
is
begin
update emp set sal=sal+500
where empno = p_id;
end raise_sal;
--调用储存过程
--相当于调用刚创建的函数
declare
p_empno emp.empno%type:=8888;
begin
raise_sal(p_empno);
end;
５．ｏｕｔ
出参，一般是给Select中的字段赋值
create or replace procedure raise_sall
(
p_id in emp.empno%type,
p_name out emp.ename%type
)
is
begin
select ename into p_name
from emp
where empno=p_id;
end raise_sall;
--调用储存过程
declare
p_empno emp.empno%type:=8888;
v_ename emp.ename%type;
begin
raise_sall(p_empno,v_ename);
dbms_o６．in out
即可以是出参，也可以是入参
create or replace procedure raise_sal2(
p_id in emp.empno%type,
p_name out emp.ename%type,
p_sal in out emp.sal%type
)
is
begin
update emp set sal=sal+500
where empno=p_id;
select ename,sal into p_name,p_sal
from emp
where empno=p_id;
end raise_sal2;
declare
v_empno emp.empno%type:=8888;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
raise_sal2(v_empno,v_ename,v_sal);
--传入参数后执行 上方定义的函数raise_sal2 这时把参数的值带入上方的sql就清晰了
commit;
dbms_output.put_line('name='||v_ename);
end;六．异常处理
1.什么是异常
--Oracle中出现错误的情形通常分为编译时错误（compile-time error）
--和运行时错误(run-time error)，
-- 异常是在PL/SQL运行过程中出现的警告或错误，
-- 当发生异常时，块就会停止执行，但是可以转到指定异常处理机部分继续执行
２.异常是如何触发的?
-- 隐式触发：发生了一个 Oracle 错误时，oracle自动触发一个异常。
-- 显示触发：程序员可以使用RAISE语句显式触发异常。
３.隐式异常
declare
v_sal emp.sal%type;
begin
select sal into v_sal
from emp
where empno=999;
--没有这条数据
exception
when NO_DATA_FOUND then
dbms_output.put_line('没有数据返回');
when others then
dbms_output.put_line('其他异常');
end;
--结果 没有数据返回4..自定义异常
1.在声明部分声明异常名。
语法：
exception EXCEPTION;
其中: exception 异常名
2. 使用PRAGMA EXCEPTION_INIT语句将异常处理名字和Oracle的错误代码关联起来．
语法：
PRAGMA EXCEPTION_INIT ( exception , error_number ) ;
其中: exception 先前声明的异常名
error_number 标准Oracle 错误代码
3. 在相应的异常处理例程中引用已声明的异常
declare
e_defind exception;
pragma exception_init(e_defind,-00001);
begin
insert into emp
select* from emp;
exception
when e_defind then
dbms_output.put_line('自己定义的异常,违反唯一性约束');
end;七．触发器
１.特点
2.触发时间
--BEFORE:在表上触发DML 事件之前执行触发器体。
--AFTER: 在表上触发DML 事件之后执行触发器体。
--INSTEAD OF:修改视图时执行触发器体代替触发语句
3.注意
触发事件可以是表上的 INSERT、UPDATE 或 DELETE 语句
当触发事件是一个 UPDATE 语句时，
可以用一个字段列表来确定那些必需触发触发器自来改变的列。 不能为 INSERT 或 DELETE 语句指定字段列表，因为它们总是影响整个行。
. . . UPDATE OF sal . . .
触发事件可以是一个、两个或全部 DML 语句的三个。
. . . INSERT 或 UPDATE 或 DELETE
. . . INSERT 或 UPDATE OF job . .
4.触发类型
--语句：触发器体对于触发事件执行一次，这是默认。一个语句触发器触发一次，即使根本没有行受影响。
--行：触发体对受触发事件影响的每行执行一次。如果触发事件没有受影响的行，行触发器不执行
--trigger 触发
create or replace trigger sal_emp
before insert on emp
--before 之前 emp 要执行触发器的表
begin
if(to_char(sysdate,'dy') in ('星期二','星期日')) then
raise_application_error(-20001,'周二和周日不能添加数据');
--如果在周二和周日操作这个表格 则提示上方内容
end if;
end;
--触发器的执行实例
CREATE OR REPLACE TRIGGER sal_emp
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF(to_char(sysdate,'HH24:MI') not between '08:00' and '17:00') THEN
IF deleting THEN
raise_application_error(-20001, '非工作期间不可删除数据');
ELSIF inserting THEN
raise_application_error(-20002, '非工作期间不可录入数据');
ELSIF updating('sal') THEN
raise_application_error(-20003, '非工作期间不可改数据');
ELSE
raise_application_error(-20004, '非工作期间不可改数据');
END IF;
END IF;
END; --3.instead of实例
CREATE OR REPLACE TRIGGER sal_emp
INSTEAD OF INSERT ON my_view
FOR EACH ROW
BEGIN
INSERT INTO dept(deptno,dname)
VALUES(90,'neu');
INSERT INTO emp
(empno,ename,job,sal,deptno)
VALUES(1005,'ljs','clerk',2000,90);
END;
INSERT INTO my_view
VALUES (1005,'ljs','clerk',2000,30,'sales');
1166
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
