PL/SQL视频笔记

PL/SQL基础入门

PL/SQL是SQL的扩展，是过程化语言，是ORACLE特有的开发语言。
PL/SQL块由四个基本部分组成：声明、执行体开始、异常处理、执行体结束

Procedural Language extension to SQL PL/SQL

PL/SQL模块类型：匿名块（没名字），过程（有名字），函数（有名字，且得返回一个值）。

例1
set serveroutput on

declare
v_fname varchar2(20);
begin
select first_name into v_fname from employee where employee_id=100;
dbms_output.put_line('The result is'||v_fname);
end;
/

例2：
declare
v_myName varchar(20);
begin
dbms_output.put_line('my name is:'||v_myName);
v_myName:='john';
dbms_output.put_line('My name is:'||v_myname);
end;
/

PL/SQL大小写不敏感，一开始只能输出空值，第二次，才能输出john。

PL/SQL variables:
Scalar 标量，例如number,varchar2
Composite数组啊，复合结构
Reference相当于指针
Large object(LOB)
Non-PL/SQL variables:Bind variables（不是PL/SQL变量，而是环境变量）

emp_lname employees.last_name%TYPE;
balance NUMBER(7,2);
min_balance balance%TYPE :=1000;

%TYPE的作用是取某表的某列的数据类型，或者某个变量的数据类型。

例如SQLPLUS就是一个环境
例子：
VARIABLE b_emp_salary NUMBER
BEGIN
select salary into :b_emp_salary
from employees where employee_id=178;
END;
/
print b_emp_salary
select first_name,last_name from emplyees
where salary=:b_emp_salary;

第一行定义的变量，是定义环境变量。into后的冒号，表明这是个外部变量。print是SQLPLUS命令，把环境变量显示出来。

--单行注释
/*这是
多行注释*/

PL/SQL语言块可以嵌套，即begin与end之间可以再有declare,begin,end

PL/SQL块与transaction不是一对一的关系，end后，事务可以没结束，ORACLE保留相应的锁，而SQL*PLUS就一退出，没提交的事务会自动回滚。

PL/SQL块中DML例子
begin
insert into employees
(employee_id,first_name)
values(emp_seq.NEXTVAL,'ruth')
end;
/

cursor:游标。相当于指针，查询出来的结果放到私人内存区，然后指针指到内存当中的这个结果那。
SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT
前两个是布尔型变量，第三个是int的，代表多少条数据被修改了。

隐式游标使用
declare
v_rows_deleted varchar2(30)
v_empno employees.employee_id%TYPE :=176;
BEGIN
delete from employees
where employee_id=v_empno;
v_rows_deleted:=(SQL%ROWCOUNT||'row deleted.）；
commit;
DBMS_OUTPUT.PUT_LINE（v_rows_deleted);
END;

commit以后，游标的属性值就消失了，即再用SQL%FOUND就不行了。

IF THEN ELSE句子
delcare
v_myage number:=31;
begin
if v_myage <11 then
dbms_output.put_line('i m a child');
elsif v_myage <30 then
dbms_output.put_line('i m young');
else dbms_output.put_line('i m always young');

end if;
end;

有NULL参与运算的，结果是NULL。null and true是null，null and false是false，null or true是true,null or false是null.
例如：
declare
v_myagenumber;
begin
if v_myage<11 then
dbms_output.put_line('i m a child');
else
dbms_output.put_line('i m not a child');
end if;
end;

因为v_myage是null，所以运算后结果也是null，所以就输出else的i m not a child了。如果第三行改成if v_myage is null<11 then，则会输出i m a child。

case例子
set verify off
declare
v_grade char(1):=UPPER('$grade');
v_appraisal VARCHAR2(20);
BEGIN
v_appraisal:=CASE v_grade
when 'A' then 'Excellent'
when 'B' then 'very good'
when 'C' then 'Good'
else 'no such grade'
end;
dbms_output.put_line('grade:'||v_grade);
end;
/

case与后面when是一个整体，算出来的结果，再赋给前面的变量v_appraisal。
declare
v_grade char(1):=UPPER('$grade');
v_appraisal VARCHAR2(20);
begin
v_appraisal:=case
when v_grade='A' then 'excellent'
when v_grade in ('b','c') then 'GOOD'
else 'no such grade'
end;
end;

loog例子
begin
dbms_output.put_line('----normal---');
for i in 1..3
loop
dbms_output.put_line('output is'||i);
end loop;
dbms_output.put_line('---Reverse---');
for i in reverse 1..3
loop
dbms_output.put_line('output is'||i);
end loop;
end;

例子：
begin
<<outer_loop>>
loop
v_counter:=v_counter>10;
exit when v_counter>10;
<<inner_loop>>
loop
...
exit outer_loop when total_done='YES';
--leave both loops
exit when inner_done='YES';
--Leave inner loop only
end loop inner_loop;
...
end loop outer_loop;
end;
/

复合数据类型

定义一个记录结构类型。

例子
declare
type myrecord is record
(id number(8),
name varchar2(20) default 'Wilson',
birthday DATE
);
person myrecord;
BEGIN
person.id:=12345;
person.birthday:=SYSDATE;
DBMS_OUTPUT.PUT_LINE('Name:'||person.name);
END;

%ROWTYPE使用会建立变量与某行记录结构的绑定，编译完后，如果原表结构改变了，则依赖于他的变量就invalid了。

关联数组与记录类型一样，也是重点。

显式游标
declare
cursor c_emp_cursor IS
select employee_id,last_name from employees
where department_id=30;
v_emp_recordc_emp_cursor%ROWTYPE;
BEGIN
OPEN c_emp_cursor;
LOOP
FETCH c_emp_cursor INTO v_emp_record;
EXIT when c_emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id||''||v_emp_record.last_name);
END LOOP;
CLOSE c_emp_cursor;
END;