plsql实例精讲部分笔记

2023-07-19 17:53:22

转换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