PL/SQL规范、块、过程、函数、包、触发器
1.pl/sql规范
标识符号的命名规范
- 定义变量,用 v- 作为前缀 v-sal
2)定义常亮, 用 c- 作为前缀 c-rate - 定义游标,用 _cursor作为后缀 emp_cursor
- 定义例外,用 e_ 作为前缀 e_error
2.块(block)机构示意图
pl/sql 块有三部分构成,定义部分,执行部分,例外处理部分
declear
/*定义部分 --- 定义常量、变量、游标、例外、复杂属于类型*/
begin
/*执行部分 -- 要执行的pl/sql 语句 和 sql语句*/
exception
/*例外处理部分 --- 处理运行的各种错误*/
end;
其中执行部分是必须的,定义部分和例外处理部分是可选的.
实例1-只包含执行部分的pl/sql块
set serveroutput on; // on 和 off 打开与关闭内容是否输出
begin
dbms_output.put_line('hello world');
end;
dbms_output 是oracle提供的包,包里面包含一些过程,如put_line
实例2 - 包含定义部分和执行部分的pl/sql块
declare
v_name varchar2(5); --定义字符串变量
begin
select ename into v-ename form emp where empno=&no;
dbms_output.put_line('雇员名:' || v_name);
end;
/
&no 表示控制台输入,
into v_ename 将查出来的 ename 赋值给 v_ename
|| 类似于字符串拼接
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
--执行部分
select ename,sal into v_ename,v_sal from emp where empno = &aa;
--在控制台显示
dbms_outln.put_line('用户名是:'||v_ename || '工资:' || v_sal);
end;
/
实例3-包含定义部分和执行部分和例外处理部分
为避免pl/sql程序的运行错误,提高健壮性,应该对可能的错误进行处理
(1) 比如在实例2中,如果输入了不存在的雇员号,应当做例外处理
(2) 有时候出现异常,希望用另外的逻辑处理
oracle 事先定义了一些异常,no_data_found 就是找不到数据的例外
declare
v_ename varchar2(5);
v_sal number(7, 2);
begin
--执行部分
select ename, sal into v_ename, v_sal from emp where empno = &aa;
--在控制台显示
dbms_outln.put_line('用户名是:' || v_ename || '工资:' || v_sal);
--异常处理
exception
when no_data_found then
dbms_output.put_line('编号输入有误');
end;
/
3.过程
-- 案例4
create procedure sp_pro3(spName, varchar, newSal number) is
begin
-- 执行部分,根据用户名修改工资
update emp set sal = newSal where ename == spName;
end;
/
-- 调用存储过程 exec 或者 call
exec sp_pro3('jack',9899);
4.函数
用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,而在函数体内必须包含return 语句返回的数据
--输入雇员姓名,返回该雇员的信息
create function sp_fun2(spName varchar2) return number is
yearSal number(7, 2); -- 七位数两位小数
begin
-- 执行部分
select sal * 12 + nvl(comm, 0) * 12 into yearSal from emp where ename = spName;
return yearSal;
end;
/
在sqlplus 中调用函数:
sql>val income number
sql>call sp_cun2('jack') into:income
sql>print income
5.包
(1) 使用 create package 来创建包
create package sp_package is
procedure update_sal(name varchar2, newsal number);
function annual_income(name varchar2) return number;
end;
/
包的规范只包含了过程和函数的说明,但是没有过程和函数实现。
包体用于实现包规范
在pl/sql packages 里查看
(2) 建立包体使用 create package body 命令
-- 给 包sp_package 实现包体
create package body sp_package is
procedure update_sal(name varchar2, newsal number) is
begin
update emp set sal = newsal where ename = name;
end;
function annual_income(name varchar2) return number is
annual_salary number;
begin
select * sal * 12 + nvl(comm, 0)
into annual_salary
from emp
where ename = name;
return annual_salary;
end;
end;
然后就可以调用了:
sql> exec sp_paackage.update_sal('jack',200);
当调用包的过程或是函数时,在过程和函数前需要带有包名,如果要访问其他方案的包,还需要在包名前加方案名。
如:
SQL> call sp_package.update_sal('jack',200)
6.触发器
触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的事件和触发的操作,常用的触发事件包括insert,update,delete语句,而触发的操作实际就是一个 pl/sql块.
可以使用 create trigger
来建立触发器。
可以用来维护数据库的安全和一致性。