PL/SQL编程基础 学习总结

PL/SQL编程基础
1.如何编写和编译PL/SQL
因为PL/SQL是嵌入在Oracle服务器和Oracle开发工具中的，所以在Oracle客户端的各种工具和服务器中
都可以编写PL/SQL程序
SQL>edit c:\plsqlblock1.sql; --建一个编写的文件
SQL>set serveroutput on;      --激活dbms_output包，以便在PL/SQL块中可以引用dbms_output包将变量的值输出在屏幕上
SQL>start c:\plsqlblock1.sql; --编译和运行该PL/SQL块，查看该块是否有语法错误和逻辑错误

注意：在PL/SQL中使用SELECT 语句的语法与标准SQL中的SELECT语法有，其他PL/SQL所支持的SQL命令在标
准SQL和PL/SQL中则没有区别
标准SQL中的SELECT语法如下：
select 列名，列名....
from 表
where 条件....;
在PL/SQL中的SELECT语法为：
SELECT 列名，列名...
into 变量1，变量2...
from 表
where 条件....;

2.PL/SQL程序结构
[DECLARE]--说明部分 要用到的所有变量，常量，数据类型，游标和用户定义的错误等对象的定义
......
BEGIN
..... --主块的语句执行部分
[EXCEPTION] --出错处理部分
END;
/
3.变量定义
NUMBER(数字型)，CHARACTER(字符类型)，DATE/TIME(日期型)和BOOLEAN(布尔型)
变量[constant]数据类型[not null][:=|DEFAULT PL/SQL表达式];
eg:
V_CHAR VARCHAR2(20);
%TYPE和%ROWTYPE类型
%TYPE即一个变量的类型用另一个已经定义的变量的类型定义，或用某一个表的某一列的类型定义
eg:
BOOKPRINT NUMBER;
V_BOOK BOOKPRINT%TYPE; 用已经定义的BOOKPRINT
V_EMPNO EMP.EMPNO %TYPE; 用EMP表中的EMPNO定义

%ROWTYPE
即一个变量的类型参考基表，视图中记录的类型或游标的结构类型，%ROWTYPE前面是表名或游标名，此类型
的变量内的字段与表或视图，游标内的列的名字和类型完全一致
eg:
V_REC EMP%ROWTYPE
emp代表表名或前面定义的游标名
V_REC.EMPNO:=100;

用户自定义的类型
定义用户自定义的数据类型student_type
SQL>create replace type student_type as object(
    id number(5),
    firstname varchar2(20),
    lastname varchar2(20),
    major varchar2(30),
    current_credits number(3))；
)

引用用户自定义类型V_student student_type;
4.变量赋值
通过:=给变量赋值 变量名：=常量或PL/SQL表达式
或通过SELECT INTO 语句或FETCH INTO 语句给变量赋值
select sal into NUM_VAR from emp where empno = 7788;
5.PL/SQL运算符和函数
注意：PL/SQL中不支持聚组函数
PL/SQL中对空值进行的算术运算和比较运算得到的结果都是NULL,NULL能做字符串的联结,要判断
某一变量的值是否为空不能直接用
等号去判断必须要用IS NULL IS NOT NULL
eg: NULL+(数字)=null
null>(数字)=null
null||'字符串'='字符串'
判断一个变量的值是否为NULL的正确写法如下；
IF my_var is null then
....
end if;

5.PL/SQL的控制结构
(1).条件语句
IF 条件1 THEN
语句系列1；
[ELSIF 条件2 THEN
语句系列2；
...
[ELSF语句系列3；]
END IF；
eg:
DECLARE
V_sal emp.sal%type;
BEGIN
select sal into v_sal from emp where empno=7788;
if v_sal<500 then
update emp set sal=sal*1.5 where empno=7788;
elsif v_sal<1500 then
update emp set sal=sal*1.3 where empno=7788;
elsif v_sal<3000 then
update emp set sal=sal*1.1 where empno=7788;
else
update emp set sal=sal*1.0 where empno=7788;
end if;
commit;
end;
/

(2).CASE语句
1.含selector的CASE语句
[<<label_name>>]
case selector
when 表达式1 then 语句系列1；
when 表达式2 then 语句系列2；
...
when 表达式n then 语句系列n;

[ELSE 语句序列n+1;]
end case[label_name];

6.循环
简单循环
LOOP

END LOOP;
while循环
while ...loop
end loop;
数字式for循环
for ...loop
end loop;
循环体内必须有退出循环的语句EXIT

loop    end loop;
DECLARE
V_counter binary_integer:=1;
BEGIN
LOOP
INSERT INTO TEMP_table values(v_counter,'loop index');
V_counter:=v_counter+1;
IF v_counter>50 then
exit;
end if;
end loop;
end;
/

for ...loop end loop;
declare

V_counter NUMBER:=1;
BEGIN
for V_counter IN 1..50 LOOP     IN索引的变量从小到大
INSERT INTO temp_table values(V_counter,'loop index');
end loop;
end;

declare

V_counter NUMBER:=1;
BEGIN
for V_counter IN REVERSE 1..50 LOOP     IN REVERSE索引的变量从大到小
INSERT INTO temp_table values(V_counter,'loop index');
end loop;
end;

7.标签
为了提高程序的可读性，可以给语句，块，循环加标签(label)，标签在一个块或一个循环前面，由双尖括号引起来
用：GOTO + LABEL将控制传递到程序的另一部分

8.出错处理部分
EXCEPTION
WHEN 错误1 [OR 错误2]THEN
    语句序列1；
WHEN 错误3[OR 错误4]THEN
    语句序列2；
WHEN OTHERS THEN
    语句序列3；
END;

(1).系统预定义的错误
(2).用户自定义错误
DECLARE
    my_exception EXCEPTION;
用户定义的错误是在语句执行部分通过显式使用RAISE语句来触发的
RAISE 错误名

eg:
查找classes表中历史系101课程的当前学生数与最大学生数，比较这两个数，如果当前学生数超过最大学生
数是不允许的，就触发错误，当这种错误发生时向log_table表中插入一条记录

9.游标的使用
为了能够得到查询结果的多条记录必须使用游标：游标有显示游标和隐式游标
对单条记录的SQL语句的操作都使用隐式游标
(1).定义游标
cursor 游标名[(参数名1 数据类型[，参数名2数据类型...])]
is select 语句;
eg:cursor c1 is select ename,deptno from emp;
(2).打开游标
open 游标名[(实际参数值1[,实际参数值2...])];
(3).取值到变量
Fetch 游标名 into 变量1[，变量2...];

Declare
   cursor c_classes is select *from class;
   V_classes c_classes%rowtype; /*c_classes是一个游标名，必须先定义该游标*/
   BEGIN
    ...
    FETCH c_classes into v_classes;

   ..
   end;

/
(4).关闭游标
close c_classes;  
游标的属性
%ISOPEN
%NOTFOUND
%FOUND
%ROWCOUNT

eg:
通过游标用简单循环控制从emp表中取出某一部门的员工姓名和工资
Declare
v_deptno emp.deptno%type:=&p_deptno;/*&p_deptno表示可以在运行时接受输入的变量p_deptno*/
v_ename emp.ename%type;
v_sal emp.sal%type;
cursor emp_cursor is select ename,sal from emp where dptno_v_deptno;
BEGIN
OPEN emp_cursor;
loop
    FETCH emp_cursor into v_ename,v_sal;
    exit when emp_cursor%notfound;
    insert into temp(ename,sal) values(v_ename,v_sal);
end loop;
close emp_cursor;
commit;
end;
/

使用游标的for循环(for循环自动完成的三个步骤：打开游标，取数据，关闭游标);
用游标的for循环实现上述功能如下：
Declare
v_deptno emp.deptno%type:=&p_deptno;
        cursor emp_cursor is select ename,sal from emp where deptno=v_deptno;
BEGIN
        For emp_record IN emp_cursor loop
          insert into temp(ename,sal) values(emp_record.ename,emp_record.sal);/*返回结果在记录名.列名中*/
end loop;
commit;
end;
/

10.通过游标操纵数据库

当通过游标更新，删除多条记录的时候，游标的查询语句中必须包含for update of 否则返回错误，必须先加
锁，然后才能进行更新删除操作
eg:
查询emp表某部门的雇员情况，如果某雇员的工资小于800，则将其工资改成800
Declare
v_deptno emp.deptno%type:=&p_deptno;
v_empno emp.empno%type;
v_sal   emp.sal%type;
v_job emp.job%type;
cursor emp_cursor is select empno,job,sal from emp where deptno=v_deptno for update of sal;--表示更新游标取出来的那条记录
BEGIN
for emp_record in emp_cursor loop
    if emp_record.sal<800 then
      update emp set sal=800 where current of emp_cursor;
    end if ;
    dbms_output.put_line(emp_record.empno||emp_record.sal);
end loop;
commit;
end;
/

eg:
为职工增加10%的工资，从最低工资开始长，增加后工资总额限制在50万元以内


Declare
cursor c1 is select empno,sal from emp
       order by sal for update of sal;
emp_num number:=0;
s_sal emp.sal%type;
e_sal emp.sal%type;
e_empno emp.empno%type;
BEGIN
    OPEN c1;
    select sum(sal) into s_sal from emp;
    while s_sal<500000
            loop
               FETCH c1 into e_empno,e_sal;
               exit when c1%notfound;
               update emp set sal=sal*1.1 where current of c1;
               s_sal:=s_sal+e_sal*0.1;
               emp_num:=emp_num+1;
            end loop;
   close c1;
   insert into msg values(emp_num,s_sal);
   commit;
   end ;
/
11.带参数的游标
eg:
首先查询DEPT表取出所有的部门号，然后根据DEPT表中返回的每个部门号，从emp表中查询该部门的雇员信息
DECLARE
cursor dept_cursor is select deptno from dept;
        v_ename emp.ename%type;
        v_sal emp.sal%type;
        cursor emp_cursor (v_deptno number) is select ename,sal /*定义带参数的游标*/
from emp where deptno=v_deptno;
Begin
for dept_record in dept_cursor loop
            exit when dept_cursor%notfound;
     open emp_sursor(dept_record.deptno);/*打开带参数的游标，参数的实际值是外层游标取出的部门号*/
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_sursor%notfound;
        dbms_output.put_line(dept_record.deptno||' '||v_ename||' '||v_sal);
end loop
close emp_cursor;
end loop;
commit;
end;