Oracle入门--day5--PLSQL - 概览、块、SQL语句等

知识点概览：

PLSQL 概览、PLSQL 的块概念、PLSQL变量|、PLSQL 中的SQL语句、

PLSQL 的控制语法、PLSQL 中的复杂自定义数据类型、

PLSQL 中的游标、PLSQL 的例外处理、PLSQL 中的存储过程和函数

 

知识点讲解：

PLSQL 概览：

PLSQL 是Oracle 公司在SQL 基础上进行扩展而成的一种过程语言。PLSQL 提供了典型的高级语言特
性，包括封装，例外处理机制，信息隐藏，面向对象等；并把最新的编程思想带到了数据库服务器和工具
集中。

与 与Java, C# 相比 ， ，PLSQL 的优势是：SQL 语言可以直接写到PLSQL 的“块”中或者是PLSQL 的过程、
向 函数中。没有必要向java 那样先创建Statement 对象来执行SQL; 这使得PLSQL 成为很强大的事务处理语
用 言，即：使用SQL 来处理数据，使用控制结构来处理业务逻辑。

PLSQL 在Oracle 数据库服务器（在存储过程、函数、数据库触发器，Package 包中使用）和Oracle 开发
工具集（在； 开发工具组件的触发器中使用）；Form Developer ，Report Developer 还可以使用共享库
（包含使用PLSQL 写的过程和函数，扩展名为PLL 的文件）; SQL 数据类型也可以在PLSQL 中使用，结合
SQL 提供者的直接访问，这些共享数据类型整合了PLSQL 和Oracle 的数据库字典。PLSQL 消除了存取数据
库的便利性与过程语言之间的障碍。

 

PLSQL 的另一个显著好处在于它可以通过减少来回交互减轻网络流量压力、节省时间：

PLSQL块概念

PLSQL 是一种类PASCAL 语言，每一段程序都是由Block 组成的：

DECLARE (Optional)
Variables, cursors, user-defined exceptions
BEGIN (Mandatory)
 SQL statements
 PL/SQL statements
EXCEPTION (Optional)
Actions to perform when errors occur
END; (Mandatory)

使用分号作为一句SQL 或者PLSQL 语句的结束；

块结构关键字（DECLARE, BEGIN,EXCEPTION 后面不跟分号；

END 后面需带分号；

你可以把一句SQL 语句写在一行上，但一般不建议这么做，因为代码不够漂亮！

 

PLSQL 的块包括三种：匿名块、存储过程、函数；

PLSQL 的变量

PLSQL 的变量类型：

1 、系统内置的常规简单变量类型： 比如大多数 数据库表的字段类型都可以作为变量类型；
2、用户自定义复杂变量类型： 比如记录类型；
3 、引用类型：保存了一个指针值；
4 、大对象类型（LOB ）：保存了一个指向大对象的地址；

 

PLSQL 的变量 类型举例：

布尔类型  数值类型  BFILE类型  日期类型 BLOB类型  LONG类型  字符串类型

 

SQLPLUS 变量

PLSQL 本身没有输入输出功能，如果要想 像命令行运行C 程序那样可以接收输入值，那你必须依赖执行环境
把值传给PLSQL 块，比如iSQL Plus 执行环境或者PLSQL Developer 的Command Window 执行环境中，
种 有一种substitution 变量 可以用来接收输入值；而另一种Host 变量可以把运行时的值传出到执行环境中。
关 有关SQLPLUS 变量，我们会在以后详细展开；

 

PLSQL的 的 变量声明语法：

identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];

说明：
1 、变量命名建议遵循通用规则，比如v_name 表示一个变量，c_name 表示一个常量；
2 、一般建议每一行声明一个变量，这样程序的可读性比较好；
3 、如果声明了变量，但未进行初始化，则在没有赋值之前该变量的值为NULL; 一个好的编程习惯是对所有声明
的变量进行初始化赋值。

4 、在同一个块中，避免命名与数据库表中的字段名相同的变量；

 

PLSQL 特有的%TYPE 属性来声明与XX 类型一致的变量类型：

identifier Table.column_name%TYPE;

可绑定变量（ （Bind Variable 也称为Host Variable , 非PLSQL 变量）：

VARIABLE g_salary NUMBER
BEGIN
    SELECT salary
    INTO :g_salary
    FROM employees
    WHERE employee_id = 178;
END;
/
PRINT g_salary

可绑定变量是一种在缩主环境中定义的变量，所谓 缩主 环境一般指示SQLPLUS 执行环境或者是
PLSQL Developer 的Command Window 执行环境；可绑定变量可用于在运行时把值传递给PLSQL, 创建语法：
VARIABLE return_code NUMBER
VARIABLE return_msg VARCHAR2(30)
的 大家注意，在标准的PLSQL 中定义变量是不能用VARIABLE 关键字的，此关键字只在SQLPLUS 执行环境中有效，
可使用PRINT 语句输出变量内容。
在PLSQL 中使用这种变量时，前面加”:”, 以示区分。

 

DBMS_OUTPUT.PUT_LINE() 介绍：

在接下来的实验中，经常需要在调试程序时输出中间变量的值，我们可使用Oracle 内置的Package 中的函数：

DECLARE
v_sal NUMBER(9,2) := &p_annual_sal;
BEGIN
v_sal := v_sal/12;
DBMS_OUTPUT.PUT_LINE ('The monthly salary is ' ||
TO_CHAR(v_sal));
END;

上述例子中，我们使用DBMS_OUTPUT.PUT_LINE () 输出变量v_sal 的值；
解释：&p_annual_sal 在Plsql Developer 的SQL window 执行环境中，可用于提示用户输入一个具体
的值。
注意：在SQLPLUS 中执行DBMS_OUTPUT.PUT_LINE () 前，必须先执行：
SET SERVEROUTPUT ON ，而在PLSQL Developer 的SQL Window 中则不需要这句话。

 

PLSQL 中的注释语句：
1 、多行注释类似于java 或者 C ， ， 用 使用 /* 和 和 */
2 、单行注释是在语句后面使用–

 

SQL 函数在PLSQL 的过程语句中的使用：
哪些可以用？ 哪些不可以用？
大多数SQL 函数都可以在PLSQL 的过程语句中使用，比如：
单行的数值和字符串函数、数据类型转换函数、日期函数、时间函数、求最大、最小值的GREATEST, LEAST
函数等；
但有些函数在PLSQL 的过程语句中是不能使用的，比如：
Decode 函数、分组函数（AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE ）等；

 

块嵌套和变量范围：
PLSQL的块是可以嵌套的，变量的作用范围与其他语言类似

 

变量限定词： 假设我们在块嵌套的程序中，里层和外层有相同的变量声明，而里层的程序要访问外层的同名变量
该怎么办呢？
答案是：使用块限定词，请看例子：

<<outer>>
    DECLARE
        birthdate DATE;
    BEGIN
        DECLARE
            birthdate DATE;
        BEGIN
            ...
            outer.birthdate :=
                TO_DATE( ' 03-AUG-1976 ' ,
                    ' DD-MON-YYYY ' );
        END;
    ....
    END;

这个例子中，birthdate 是同名变量，限定词outer 表示外层，里层要访问外层的bithdate 时使用
outer.birthdate这种格式

 

PLSQL中的SQL语句：

SELECT INTO 语句： 用于把从数据库查询出内容存入变量

DECLARE
    v_hire_date employees.hire_date%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    SELECT hire_date, salary
    INTO v_hire_date, v_salary
    FROM employees
    WHERE employee_id = 100;
    ...
END;
/

 

注意点：该语句支持单行的查询结果，果 如果Where 条件控制不好，导致多行查询结果，则会引发Too_many_rows的例外

 

INSERT 、UPDATE 、DELETE 、MERGE 语句：

在 PLSQL 中执行这些SQL 语句和直接执行这些语句差不多，只不过可以在SQL 语句中使用PLSQL声明的变量；

BEGIN
INSERT INTO employees
(employee_id, first_name, last_name, email,
hire_date, job_id, salary)
VALUES
(employees_seq.NEXTVAL, 'Ruth', 'Cores', 'RCORES',
sysdate, 'AD_ASST', 4000);
END;
/

DECLARE
v_sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + v_sal_increase
WHERE job_id = 'ST_CLERK';
END;
/

DECLARE
v_deptno employees.department_id%TYPE := 10;
BEGIN
DELETE FROM employees
WHERE department_id = v_deptno;
END;
/

DECLARE
v_empno employees.employee_id%TYPE := 100;
BEGIN
MERGE INTO copy_emp c
USING employees e
ON (e.employee_id = v_empno)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email,
. . .
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
. . .,e.department_id);
END;

 

PLSQL中的控制语句

和其他语言一样，控制主要包括判断和循环；
判断语句的语法与其他语言类似：

IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;

CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ELSE resultN+1;]
END;

需要注意的是对NULL的判断处理：一般人容易犯错误或者不容易记住

 

循环语句的语法与其他语言类似：有基本循环、For 循环、Wihle循环三种

LOOP
statement1;
. . .
EXIT [WHEN condition];
END LOOP;

WHILE condition LOOP
statement1;
statement2;
. . .
END LOOP;

FOR counter IN [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;

嵌套循环和Label

...
BEGIN
<<Outer_loop>>
LOOP
v_counter := v_counter+1;
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;

Label 一般用不着，只有在使用goto 语句，或者内部循环需要访问外部的同名变量的时候才需要，而一般这
种做法也是不被提倡的。

 

PLSQL中的复杂自定义数据类型（类似Java构造器感觉）

概述：PLSQL 中常用的自定义类型就两种：记录类型、PLSQL 内存表类型（根据表中的数据字段的简单和复杂
程度又可分别实现类似于简单数组和记录数组的功能）

记录类型的定义语法：

TYPE type_name IS RECORD
(field_declaration[, field_declaration]…);
identifier type_name;

这里的field_declaration 的具体格式可以是：

field_name {field_type | variable%TYPE
| table.column%TYPE | table%ROWTYPE}
[[NOT NULL] {:= | DEFAULT} expr]

%ROWTYPE 属性：在PLSQL 中 %ROWTYPE 表示某张表的记录类型 或者是用户指定以的记录类型，使用此
属性可以很方便的定义一个变量，其类型与某张表的记录或者自定义的记录类型保持一致。极大的方便了
Select * into ….的语句实用

举例：

DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT * INTO emp_rec
FROM employees
WHERE employee_id = &employee_number;
INSERT INTO retired_emps(empno, ename, job, mgr, hiredate,
leavedate, sal, comm, deptno)
VALUES (emp_rec.employee_id, emp_rec.last_name, emp_rec.job_id,
emp_rec.manager_id, emp_rec.hire_date, SYSDATE, emp_rec.salary,
emp_rec.commission_pct, emp_rec.department_id);
COMMIT;
END;
/

PLSQL 内存表即Index By Table , 这种结构类似于数组，使用主键提供类似于数组那样的元
素访问。： 这种类型必须包括两部分：1 、使用BINARY_INTEGER ； 类型构成的索引主键； 2 、另外一个简单类型
。 或者用户自定义类型的字段作为具体的数组元素。 这种类型可以自动增长，所以也类似于可变长数组。

语法：

TYPE type_name IS TABLE OF
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
| table.%ROWTYPE
[INDEX BY BINARY_INTEGER];
identifier type_name;

PLSQL 内存表应用举例：

下面定义的两个内存表中的元素都是简单数据类型，所以相当于定义了两个简单数组；

DECLARE
TYPE ename_table_type IS TABLE OF
employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE hiredate_table_type IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
hiredate_table hiredate_table_type;
BEGIN
ename_table(1) := 'CAMERON';
hiredate_table(8) := SYSDATE + 7;
IF ename_table.EXISTS(1) THEN
INSERT INTO ...
...
END;
/

备注：对PLSQL 内存表中某个元素的访问类似于数组，可以使用下表，因为BINARY_INTEGER 这种数据类型
在 的值在-2147483647 ... 2147483647 范围内，所以下表也可以在这个范围内。

 

PLSQL中的游标

游标概论：游标是一个私有的SQL 工作区域，Oracle 数据库中有两种游标，分别是隐式游标和显式游标，
隐 式 游标不易被用户和程序员察觉和意识到，实际上Oracle 服务器使用隐式游标来解析和执行我们提交的SQL
语句；而显式游标是程序员在程序中显式声明的；通常我们说的游标均指显式游标。

 

隐式游标的几个有用属性：

显式游标：对于返回多行结果的SQL 语句的返回结果，可使用显式游标独立的处理器中每一行的数据。

 

显式游标的相关函数可以做到：

1 、一行一行的处理返回的数据。
2 、保持当前处理行的一个跟踪，像一个指针一样指示当前的处理的记录。
3 、允许程序员在PLSQL 块中人为的控制游标的开启、关闭、上下移动；

 

在程序中对显式游标控制的一般过程：

 

如果你觉得像前面那个例子那样对一个游标的遍历很麻烦的话，可以考虑使用For 循环，For 循环省去了游标的
声明、打开、提取、测试、关闭等语句，对程序员来说很方便，语法如下：

FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;

游标能否带有参数？答案是肯定的：

CURSOR cursor_name
[(parameter_name datatype, ...)]
IS
select_statement;

 

FOR UPDATE NOWAIT 语句：

有的时候我们打开一个游标是为了更新或者删除一些记录，这种情况下我们希望
用 在打开游标的时候即锁定相关记录，应该使用for update nowait 语句，倘若锁定失败我们就停止不再继续，以免
出现长时间等待资源的死锁情况。

SELECT ...
FROM ...
FOR UPDATE [OF column_reference][NOWAIT];

WHERE CURRENT OF cursor ：

我们经常要逐条处理游标中的每一条记录，在循环体内做Update 或者 Delete
有 时需要有Where 指向游标的当前记录，的 有没有简单一点的的Where 条件写法呢？

答案是肯定的，就是。。。

DECLARE
CURSOR sal_cursor IS
SELECT e.department_id, employee_id, last_name, salary
FROM employees e, departments d
WHERE d.department_id = e.department_id
and d.department_id = 60
FOR UPDATE OF salary NOWAIT;
BEGIN
FOR emp_record IN sal_cursor
LOOP
IF emp_record.salary < 5000 THEN
UPDATE employees
SET salary = emp_record.salary * 1.10
WHERE CURRENT OF sal_cursor;
END IF;
END LOOP;
END;
/

 

PLSQL中的例外处理

PLSQL 中的例外一般有两种：
1 、Oracle 内部错误抛出的例外：这又分为预定义例外（有错误号+ 常量定义）和 和 非预定义例外
（仅有错误号，无常量定义）
2 、程序员显式的抛出的例外

 

PLSQL 中的 例外捕获和传递：与其他语言类似，如果例外在当前块中被处理，则到此为止，否则会被传递到
外层（外层BLOCK 或者 外层调用者函数）

PLSQL 中的例外处理一般语法：

 

处理预定义的例外：有些常见例外，Oracle 都已经预定义好了，使用时无需预先声明，比如：
NO_DATA_FOUND 和 和 TOO_MANY_ROWS 是最常见的例外，大多数Block 中都建议对这两种例外
有处理； 完整的预定义例外的列表，

请参考：PL/SQL User’s Guide and Reference, “Error Handling.” （百度搜索，下载点很多）

 

OTHERS 的处理：

Others 表明我们程序员未能预计到这种错误，所以全部归入到others 里面去了，单发生这种的情况是，我们还是希望了解当时发生的Oracle 错误号和相关描述信息，怎样才能取到呢？Oracle 提供了两个内置函数SQLCODE 和 和 SQLERRM 分别用来返回Oracle 错误号和错误描述

处理非预定义的Oracle 错误：

此类错误属于Oracle 错误，有编号，但无错误名称定义，使用时需要先声明，并进行错误初始化：

 

Oracle 内部错误号很多，想了解全部的Ora 错误号，请参考：http://www.ora-code.com/

 

处理用户自定义的错误:

这种错误一般是程序员根据具体的业务逻辑定义的应用类错误，需要先声明后使用：
定义和处理过程如下：

 

 

RAISE_APPLICATION_ERROR() 函数：对于用户自定义的业务错误，如果觉得先定义再使用很麻烦，那么也可以简单的使用raise_application_error() 来简化处理。它可以无需预先定义错误，而在需要抛出错误的地方直接使用此函数抛出例外，例外可以包含用户自定义的错误吗和错误描述；

 

PLSQL中的存储过程和函数：

语法：

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
IS|AS
PL/SQL Block;

 

自己去深入了解吧，这里是重点！！！说不清

 

 

 

今日作业：重点存储过程！！

-- 学生表：学号、姓名、性别、年龄、班级 
select * from t2_student;

create table t2_student(
sno varchar2(11) primary key,
sname varchar2(20) not null,
sex varchar2(3),
age number(3),
classid varchar2(5),
classname varchar2(20),
constraint c2_1 check(sex in ('男','女'))
);

-- 成绩表：学号、课程、成绩 
select * from t2_grade;

create table t2_grade(
sno varchar2(11),
cno varchar2(5),
cname varchar2(20)，
grade number,
constraint c2_2 check(grade >=0 and grade <=100)
);


insert into t2_student values('s001','张三','男',15,'c001','18班');
insert into t2_student values('s002','李华','女',15,'c001','18班');

insert into t2_student values('s003','李斯','男',16,'c002','19班');
insert into t2_student values('s004','王武','男',16,'c002','19班');
insert into t2_student values('s005','赵柳','女',16,'c002','19班');
insert into t2_student values('s006','孙琪','女',17,'c002','19班');


insert into t2_grade values('s001','c01','语文',90);
insert into t2_grade values('s001','c02','数学',80);

insert into t2_grade values('s002','c01','语文',70);
insert into t2_grade values('s002','c02','数学',90);


insert into t2_grade values('s003','c02','数学',80);
insert into t2_grade values('s004','c02','数学',91);
insert into t2_grade values('s005','c02','数学',92);
insert into t2_grade values('s006','c02','数学',93);


-- 以下都是存储过程的练习，要求使用存储过程操作 
-- 1、插入3条数据到学生表 
select * from t2_student;

create or replace procedure proc_insert_student
       (v_age in t2_student.age%TYPE) 
is
begin 
insert into t2_student values('s007','老八','男',v_age,'c003','20班');
insert into t2_student values('s008','老九','女',v_age,'c003','20班');
insert into t2_student values('s009','老十','男',v_age,'c003','20班');
commit;
end proc_insert_student;


begin 
  cheirmin.proc_insert_student(18);
end;

-- 2、打印所有18岁的学生 
--使用存储过程加游标
create or replace procedure proc_select_age_student 
       (v_age t2_student.age%TYPE)
is 
       cursor stu_cursor1 is 
              select sname,age from t2_student where age = v_age;
       c_sname t2_student.sname%TYPE :='';
       c_age t2_student.age%TYPE :=0;
begin 
    open stu_cursor1;
    loop
      fetch stu_cursor1 into c_sname,c_age;
      exit when stu_cursor1%notfound;
      dbms_output.put_line(c_sname||' - '||c_age);
    end loop;
    close stu_cursor1;
end proc_select_age_student;

--调用存储过程
begin 
  cheirmin.proc_select_age_student(18);
end;

--只使用游标
declare
  v_age t2_student.age%TYPE := 0;
  v_sname t2_student.sname%TYPE :='';
   cursor stu_cursor1 is
          select sname,age from t2_student where age = 18;
begin 
    open stu_cursor1;
    loop
      fetch stu_cursor1 into v_sname,v_age;
      exit when stu_cursor1%rowcount > 10 or stu_cursor1%notfound;
      dbms_output.put_line(v_sname||' - '||v_age);
    end loop;
    close stu_cursor1;
end;  


-- 3、更新“张三”的年龄为20岁，返回更新后的数据 
select * from t2_student where sname = '张三';


create or replace procedure proc_update_age 
       (v_age  in  t2_student.age%TYPE,
        c_name out t2_student.sname%TYPE,
        c_age  out t2_student.age%TYPE)
is
begin 
update t2_student set age =  v_age where sname = '张三';
commit;
select sname,age into c_name,c_age from t2_student where sname = '张三';
end proc_update_age;

--执行代码块测试上述存储过程；成立
declare
    v_name varchar2(20);
    v_age number(3);
begin 
    cheirmin.proc_update_age(20,v_name,v_age);
    dbms_output.put_line(v_name||' - '||v_age);
end;


-- 4、将“张三”的年龄、性别、班级数据赋值给“李斯” 
select * from t2_student where sname = '张三';
select * from t2_student where sname = '李斯';

create or replace procedure proc_select_and_update 
as   
       v_age t2_student.age%TYPE;
       v_sex t2_student.sex%TYPE;
       v_classid t2_student.classid%TYPE;
       v_classname t2_student.classname%TYPE;
begin 
  select age,sex,classid,classname  
  into v_age,v_sex,v_classid,v_classname 
  from t2_student where sname = '张三';--查
  
  update t2_student 
  set age = v_age,sex = v_sex,classid = v_classid,classname = v_classname 
  where sname = '李斯';--赋值
  
end proc_select_and_update;

--执行代码块测试上述存储过程；成立
begin 
    cheirmin.proc_select_and_update;
end;


-- 5、将“19班”学生的所有成绩全部在原有基础上加10分 
select * from t2_student;
select * from t2_grade;

update t2_grade set grade = grade+10 where sno in(
select sno from t2_student where classname = '19班');


create or replace procedure proc_update_grade 
is 
       cursor p_cursor2 is 
              select sno from t2_student where classname = '19班';
       c_sno t2_student.sno%TYPE :=' ';
begin 
  open p_cursor2;
  loop
    fetch p_cursor2 into c_sno;
    exit when p_cursor2%notfound;
    update t2_grade set 
    grade = (case when grade <90 then (grade +10) else 100 end) 
    where sno = c_sno;
  end loop;
  close p_cursor2;
end proc_update_grade;

--执行代码块测试上述存储过程；成立
begin 
    cheirmin.proc_update_grade;
end;



-- 6、查询“张三”的语文课成绩（一个入参） 
select * from t2_student;
select * from t2_grade;

create or replace procedure proc_select_grade
       (v_cname  in  t2_grade.cname%TYPE,
        c_grade out t2_grade.grade%TYPE)
is
begin 
        select grade into c_grade 
        from t2_grade where sno =   
        (select sno from t2_student where sname = '张三')
        and cname = v_cname;
end proc_select_grade;

--执行代码块测试上述存储过程；成立
declare
    v_grade t2_grade.cname%TYPE;
begin 
    cheirmin.proc_select_grade('语文',v_grade);
    dbms_output.put_line(v_grade);
end;


-- 7、删除“张三”的数学成绩 

create or replace procedure proc_delete_grade
is
begin 
        delete from t2_grade where sno =   
        (select sno from t2_student where sname = '张三')
        and cname = '数学';
end proc_delete_grade;

begin 
    cheirmin.proc_delete_grade;
end;