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_myage  number;
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_record c_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;