plsql实例精讲部分笔记
转换sql:
create or replace view v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
DECLARE
STATEMENTS
BEGIN
STATEMENTS
EXCEPTION
STATEMENTS
END;
可执行部分必须存在
DECLARE
v_first_name VARCHAR2(35);
v_last_name VARCHAR2(35);
c_count CONSTANT NUMBER := 0;
BEGIN
SELECT first_name, last_name
INTO v_first_name, v_last_name
FROM student
WHERE student_id=123;
DBMS_OUTPUT.PUT_LINE('student_name: ' || v_first_name || ' ' || v_last_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is no student with' || ' student id 123');
END;
PLSQL减少访问数据库的网络传输
命名语句块存储在数据库中，以后可以应用
DECLARE
v_name VARCHAR2(50);
v_total NUMBER;
BEGIN
SELECT i.first_name || ' ' || i.last_name, COUNT(*)
INTO v_name, v_toal
FROM instructor i, section s
WHERE i.instructor_id = s.instructor_id
AND i.instructor_id = 123
GROUP BY i.first_name || ' ' || i.last_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('There is no such instructor');
END;
编译过程： 语法检查， 绑定（变量分配存储地址）， 生成伪代码
. plsql结尾(可选) /执行plsql
set serveroutput on;显示输出结果
& 或者 && 替代变量前缀
使用替代变量获取输入值
替代变量没有分配内存，不能用作输出
& 每次出现替代变量都要重新输入参数
set verify off； 不显示具体替换工程
出现多次 && 只需输入一次参数值 加上 ‘’
set define character 修改&为其他字符
set define on 重新修改为&
set serveroutput on size 5000; 修改默认缓存大小为5000字节
7一些函数类型
声明变量如果没有赋值 则为null，dbms_output输出时内容为空
|| 不要用空格
coalesce(sum(data) , null,0) 返回第一个不是null的值
nvl(sum(data),0) oracle ifnull(sum(data),0) mysql
decode(sign(salary-8000),1,salary*1.15,-1,salary*1.2,salary) sign(data) 返回-1 1 0
to_char(34.56,’fm999.00’) 34.56
to_char(0.123,’fm999.00’) .12
9没有数字显示空格，0没有数字显示0 。fm去掉如果是9显示的空格。
to_char(0.123,’fm990.00’)
TO_NUMBER(REGEXP_REPLACE(status,'[^0-9]','')) > 30
status中非数字转换为空字符 在用to_number函数
select to_number（’RMB234234.4350′，’L999999.0000′） from dual;
select username,decode(lock_date,null,'unlocked','locked') status from t;
anchroed数据类型
declare
v_name sys_user.usr_name%type;
字节 字符
varchar2(n) 与字节数有关，a 1b 我2b varchar2(n char) 与字符数有关 a 1一个字符 哦 1个字符 每个字符占2个字节
nvarchar(n) 与字符数有关 可以容纳n个字符 每个字符2个字节
varchar2(n) number(m,n) 四舍五入
date timestamp binary_integer 用于索引plsql表 boolean一般用char(1)
oracle 没有Boolean plsql有Boolean 输出不能直接用Boolean类型值
函数：trunc(sysdate, ‘hh24’) 2013-01-06 17:00:00 trunc(124.235,2) 124.23
to_char(sysdate, ‘hh24’) 17 sysdate+numtodsinterval（30,’minute’）
substr instr trunk to_date to_timestamp
date相减是天数（带小数） timestamp相减是时间格式
timestamp精确到毫秒
trunc不支持timestamp
声明exception变量 e_show_exception_scope exception; raise exception抛出异常
when e_show_exception_show then ….
《inner_block》《outer_block》
v_bool Boolean; null true false
if v_bool is null then… end;
v_bool := nvl(v_bool , false);
if(v_bool) then dbms_output.put_line(‘false’);
sequence.currval nextval
dml
select into
commit rollback savepoint xx rollback to savepoint xxx
trim ltrim rtrim
8条件语句
if then else end if
if then elseif then else end if
case 搜索case 表达式case 3种形式
case xxx类型 与when xx返回类型一样
case xxx when xx then xx； when xxx then xx ； else xx； end case;
搜索case when返回Boolean类型值
case when xxx then xx ； else xx； end case；
表达式case
赋值:=case end
select case。。。end into
表达式case 直接end，不是end case 表达式case中没有 符号 ；
嵌套case
v_date date:=to_date(&sv_date,'yyyy-mon-dd');
to_char(v_date,'d') 数字形式星期 1234567 日一二三四五六七
nullif(exp1,exp2) exp1=exp2返回null，否则返回exp1
exp1不能赋值字面值null
coalesce(exp1,exp2,exp3,…) 返回第一个不是null的值
9循环
loop … end loop
if xx then exit end if
exit when condition
while condition loop
statements
end loop
null不能与任何变量进行比较 false
循环计数器经常使用 binary_integer
for v_counter in 1..5 loop statements end loop
for中v_counter被隐含定义和 递增，for外面不能引用v_counter
for v_counter in reverse 1..5 loop exit when .. end loop
reverse逆序
if then continue end if;
continue when
10异常
exception when then
no_data_found to_many_rows zero_divide login_denied program_error
value_error invalid_number dup_value_on_index
others
when others then …..
声明部分的异常无法处理，除非外面还有语句块包围处理，
重新抛出 when exception then raise
raise_application_error(-20001,’zip code is not valid’);
11游标
创建游标 打开游标 检索游标 关闭游标
记录类型
游标属性： %notfound %found %isopen %rowcount
sql%rowcount 隐藏游标可以用 没有数据抛异常 不用sql%rowcount=0判断
12.高级游标
或者open 时带参数
会加锁
13.触发器
for each row 之能增删改操作
删除一个表，表上的触发器也会删除
使用：
一些复杂业务规则；统计值；自动生成衍生列的值；防止无效事务
事务回滚或者提交，触发器执行的操作也会提交或者回滚。 自治事务例外
自治事务都会提交 autononous transaction commit
:new :old
如果某行没有update,:new是null，所以用nvl(new.zip,’’)
操作view，底层数据库表也会修改
删除时如果存在约束，需要先删除外键表中记录
instead of 触发器比较复杂，要考虑不同表之间的关系，以及特定关系可能引起的负面影响
视图中插入数据时，外键zip在zipcode表中不存在对应记录，则先在zipcode表中插入对应记录
14.复合触发器
变异表问题：
select into 操作的表section正在被修改，是变异的，改用如下方式：
复合触发器：
drop trigger sss
drop package xxx
GRANT EXECUTE ON verify_function_11G TO PUBLIC;
before each row 才能用 ：new
作业1：
create or replace trigger instructor_compound
for insert or update on instructor
compound trigger
v_day varchar2(10);
v_hour varchar2(5);
before statement is
begin
v_day := rtrim(to_char(sysdate,'day'));
v_hour := to_char(sysdate,'hh24');
if v_day like ('s%') then
raise_application_error(-20001,'this table can not be
modified on off day');
elseif v_hour <9 or v_hour >17 then
raise_application_error(-20001,'this table can not be
modified on off day');
end if;
end before statement;
before each row is
begin
:new.instructor_id := seq_instructor_id.nextval;
if inserting then
:new.created_by := user;
:new.created_date := sysdate;
elseif updating then
:new.created_by := :old.created_by;
:new.created_date := :old.created_date;
end if;
:new.modified_by := user;
:new.modified_date := sysdate;
end before each row;
end instructor_compound;
作业2：
create or replace trigger zipcode_compound
for insert or update on zipcode
v_type varchar2(10);
v_count number;
v_table varchar2(20);
before each row is
begin
:new.modified_by := user;
:new.modified_date := sysdate;
end before each row;
after statement is
if inserting then
v_type := 'insert';
else updating then
v_type := 'update';
end if;
v_count := sql%rowcount;
v_table := 'zipcode';
update zipcode
set transaction_user=user,
transaction_date=sysdate,
transaction_rowcount=v_count
where table_name = v_table
and trancaction_name=v_type;
if sql%notfound then
insert into transaction
values(v_table,v_type,user,sysdate,v_count);
end if;
end after statement;
end zipcodecompound;
15章：集合
1.pl/sql表
联合数组和嵌套表类似数据库单列表，通过下标访问，具有相同结构。
差别：嵌套表可以存储在数据库列中，联合数组不可以。
联合数组：
嵌套表：
要初始化，否则报异常 构造方法（）
长度要扩展 extend
delete(10) delete(1,3) prior(3) next(3) trim(2)
count last first extend trim
declare
type index_by_type is table of number
index by binary_integer;
index_by_table index_by_type;
type nested_type is table of number;
nested_table nested_type :=
nested_type(1,2,3,4,5,6,7,8,9,10);
begin
for i in 1..10 loop
index_by_table(i) := i;
end loop;
if index_by_table.exists(3) then
dbms_output.put_line('index_by_table(3)='||
index_by_table(3));
end if;
--delete 10th element from a collection
nested_table.delete(10);
--delete elements 1 through 3 from a colleciton
nested_table.delete(1,3);
index_by_table.delete(10);
dbms_output.put_line('nested_table.count='||nested_table.count);
dbms_output.put_line('nested_table.first='||nested_table.first);
dbms_output.put_line('nested_table.last='||nested_table.last);
dbms_output.put_line('nested_table.prior(2)='||nested_table.prior(2));
dbms_output.put_line('nested_table.next(2)='||nested_table.next(2));
dbms_output.put_line('index_by_table.count='||index_by_table.count);
dbms_output.put_line('index_by_table.first='||index_by_table.first);
dbms_output.put_line('index_by_table.last='||index_by_table.last);
dbms_output.put_line('index_by_table.prior(2)='||index_by_table.prior(2));
dbms_output.put_line('index_by_table.next(2)='||index_by_table.next(2));
nested_table.trim(2);
dbms_output.put_line('nested_table.count='||nested_table.count);
nested_table.trim();
dbms_output.put_line('nested_table.count='||nested_table.count);
end;
当从中间删除元素时，delete保留删除元素的索引，则 count和last值就会不同。
last会比count值大
trim长度会改变，重新赋值需要 exntend
变长数组：
变长数组扩展不能超过最大尺寸，否则报错。
extend(2,4) 末尾追加第四个元素的2个副本
limit限制长度
varray 变长数组不能delete
chr(10)换行
多层数组：
16记录类型
type xxx is record (name type,…)
%rowtype
not null需要初始化，否则报错
如果都是用户自定义类型record ,如果类型名不同不可以相互赋值， 表记录或者游标自定义类型之间可以相互赋值。
如果输入内容较多，修改大小
17章 本地动态sql
例子：
18章 批量sql
2.indices of 可以处理稀疏的集合，嵌套表或者联合数组。
3 values of 集合中元素做索引
创建一个有字段的空表
cursor要一行行检索
select buck collect into 会批量检索 直接讲结果存保存在了集合中
1.select bulk collect into填充嵌套表时，会自动初始化和扩展。
2.bulk collect into select不返回任何数据时，不抛出no_data_found异常，所以要自己检查返回的集合是否有数据。
3.bulk collect 不会限制集合的尺寸，并自动扩展，所以当select返回大量数据时，最好限制结合寄过集。 通过使用带有游标select的bulk collect，以及添加limit.
带有limit的bulk collect子句，一次性从student检索50行数据。每个集合最多50条记录。
合并forall select bulk collect into
