[学习笔记] Oracle数据库基础之Oracle入门(三)

# 学习 # · 2021-03-07

PL/SQL基础知识

1、PL/SQL(Procedural Language/SQL,过程语言/SQL):是结合了Oracle过程语言和结构化查询语言的一种扩展语言。

(1)PL/SQL具有编程语言的特点,它能把一组SQL语句放到一个模块中,使其更具模块化程序的特点。

(2)PL/SQL可以采用过程性语言控制程序的结构(判断、循环等逻辑结构)。

(3)PL/SQL可以对程序中的错误进行自动处理(异常处理机制)。

(4)PL/SQL程序块具有更好的可移植性。

(5)PL/SQL程序减少了网络的交互,提高程序性能。

2、PL/SQL体系结构:

(1)PL/SQL引擎接受PL/SQL块并对其进行编译执行。

(2)该引擎执行所有过程语句。

(3)将SQL语句发送给Oracle的SQL语句执行器。

3、PL/SQL块简介:

[declare]
    --声明部分:在此声明PL/SQL用到的变量、类型及游标,以及局部的储存过程和函数。
begin
    --执行部分:过程及SQL语句,即程序的主要部分(不可省略)。
[exception]
    --异常处理部分:错误处理。

4、PL/SQL中的运算符和表达式:

(1)关系运算符:等于(=)、不等于(<>、!=、~=、^=)、大于(>)、小于(<)、小于等于(<=)、大于等于(>=)。

(2)一般运算符:加(+)、减(-)、乘(*)、除(/)、赋值号(:=)、关系号(=>)、范围运算符(..)、字符连接符(||)。

(3)逻辑运算符:空值(IS NULL)、介于两者之间(BETWEEN AND)、在一列值之间(IN)、逻辑与(AND)、逻辑或(OR)、取反(NOT)。

5、PL/SQL常量和变量的声明:在PL/SQL块的可执行部分引用常量和变量前,必须先对其进行声明。

(1)声明变量:

/*
 * 基本语法
 * variable_name:变量名称
 * data_type:变量的SQL或PL/SQL数据类型
 * size:指定变量的范围
 * init_value:变量的初始值
 */
declate variable_name data_type[(size)] [:= init_value]

(2)声明常量:

declate variable_name constant data_type := value

(3)命名规则:标识符名称不能超过30个字符、第一个字符必须为字符、不区分大小写、不能用减号、不能是SQL保留字。

(4)变量命名方法:

(5)使用示例:

/*
===========================================================
| 给变量和常量声明赋值
===========================================================
*/
declate
     v_ename VARCHAR2(20);
     v_rate NUMBER(7,2);
     c_rate_incr CONSTANT NUMBER(7,2):=1.10;
begin
     --方法一:通过SELECT INTO给变量赋值
     SELECT ename, sal* c_rate_incr  INTO   v_ename, v_rate
         FROM employee 
     WHERE empno='7788';
    --方法二:通过赋值操作符“:=”给变量赋值
    v_ename:='SCOTT';
end;

6、PL/SQL注释:

(1)使用双减号进行单行注释。

(2)使用“/ /”进行多行注释。

7、PL/SQL数据类型:

(1)标量数据类型:CHAR、VARCHAR2、BINARY_INTEGER、NUMBER(p,s)、LONG、DATE、BOOLEAN。

(2)LOB数据类型:BFILE、BLOB、CLOB、NCLOB。

(3)属性类型:%TYPE(提供某个变量或数据库表列的数据类型)、%ROWTYPE(提供表示表中一行的记录类型)。

8、PL/SQL控制语句:

(1)IF语句:

/*
 * 基本语法
 */
if <boolean> then
    PL/SQL和SQL语句
end if;
--------------------
if <boolean> then
    PL/SQL和SQL语句
else if <boolean> then
    PL/SQL和SQL语句
else
    其他语句
end if;

(2)CASE语句:

/*
 * 基本语法
 */
case 条件表达式
    when 条件表达式结果1 then
        语句段1
    when 条件表达式结果2 then
        语句段2
    ...
    [ else 语句段 ]
end case;
--------------------
case
    when 条件表达式1 then
        语句段1
    ...
end case;

(3)LOOP语句:

/*
 * 基本语法
 */
loop
    要执行的语句
    exit when<条件语句,满足则退出循环>
end loop;

(4)WHILE语句:

/*
 * 基本语法
 */
while <boolean> loop
    要执行的语句
end loop;

(5)FOR语句:

/*
 * 基本语法
 */
for 循环计数器 in [reverse] 下限 ... 上限 loop
    要执行的语句
end loop;

(6)顺序控制语句:NOLL语句和GOTO语句(不推荐)。


异常处理

1、Oracle预定义异常:

(1)ACCESS_INTO_NULL:视图给一个没有初始化的对象赋值。

(2)DUP_VAL_ON_INDEX:重复的值存储在使用唯一索引的数据库列中。

(3)INVALID_NUMBER:视图将一个非有效的字符串转换成数字。

(4)LOGIN_DENIED:使用无效的用户名和口令登录Oracle

(5)NO_DATA_FOUND:语句无返回数据。

(6)TOO_MANY_ROWS:在执行SELECT INTO语句后返回多行时出现。

2、异常处理程序的基本语法:

/*
 * 基本语法
 */
begin
    过程及SQL语句;
exception
    when 异常名称 then
        过程及SQL语句;
    when others then    --只能有一个OTHERS异常处理程序
        过程及SQL语句;
end;

/*
===========================================================
| 预定义异常
===========================================================
*/
declare
   v_ename employee.ename%type;
begin
   select ename into v_ename from employee where empno=1234;
   dbms_output.put_line('雇员名:'||v_ename);
exception
   when NO_DATA_FOUND then
      dbms_output.put_line('雇员号不正确');   
   when TOO_MANY_ROWS then
      dbms_output.put_line('查询只能返回单行');
   when OTHERS then
      dbms_output.put_line('错误号:'||SQLCODE||'错误描述:'||SQLERRM);
end;

3、处理用户自定义异常:

(1)在PL/SQL块的定义部分定义异常情况:

<异常情况> exception;

(2)抛出异常情况:

raise <异常情况>;

(3)在PL/SQL块的异常情况处理部分对异常情况做出相应的处理。

/*
===========================================================
| 查询编号为7788的雇员的福利补助(comm列)。
===========================================================
*/
declare
     v_comm employee.comm%TYPE;
    e_comm_is_null EXCEPTION; --定义异常类型变量
begin
    select comm into v_comm from employee where empno=7788;
    if v_comm is NULL then
       raise e_comm_is_null;
    end if;
exception
    when NO_DATA_FOUND then
       dbms_output.put_line('雇员不存在!错误为:'||SQLCODE||SQLERRM);
    when e_comm_is_null then
        dbms_output.put_line('该雇员无补助');
    when others then
        dbms_output.put_line('出现其他异常');
end;

游标

1、游标(CURSOR):用来处理使用select语句从数据库中检索到的多行记录的工具。

2、游标的基本原理:

3、游标分类:

(1)隐式游标:自动为DML语句创建隐式游标,包含一条返回记录。

(2)显式游标:返回多条记录时,使用显示游标逐行读取。

4、显式游标的属性:

(1)%found:用于检验游标是否成功,通常在FETCH语句前使用,当游标按照条件查询出一条记录时,返回true。

(2)%isopen:判断游标是否处于打开状态,视图打开一个已经打开或者已经关闭的游标,将会出现错误。

(3)%notfound:与%found的作用相反,当按照条件无法查询到记录时,返回true。

(4)%rowcount:循环执行游标读取数据时,返回检索出的记录数据的行数。

5、显式游标的使用:

(1)声明游标:

/*
 * 基本语法
 * cursor_name:游标的名称
 * parameter:用于为游标指定输入参数。在指定数据类型时,不能使用长度约束。
 * return_type:用于定义游标提取的行的类型
 * select_statement:指游标定义的查询语句
 */
cursor cursor_name [(parameter) [, parameter]...]
[return return_type] is select_statement

(2)打开游标:

open cursor_name[(parameters)];

(3)提取游标:

fetch cursor_name into variables;

(4)关闭游标:

close cursor_name;

(5)使用示例:

/*
===========================================================
| 使用显式游标输出每个员工的姓名和薪水
============================================================
*/
declare
     name employee.ename%type;
     sal employee.sal%type;    --定义两个变量来存放ename和sal的内容
     cursor emp_cursor is select ename,sal from employee;
begin
    open emp_cursor;
    loop
           fetch emp_cursor into name,sal;
           exit when emp_cursor%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE('第'||emp_cursor%ROWCOUNT||'个雇员:'||name||sal);
    end loop;
    close emp_cursor;
end;

6、使用显式游标删除或更新:

/*
 * 基本语法
 * FOR UPDATE [OF columns]:更新查询,锁定选择的行
 * 当选择单表更新查询时,可省略OF子句
 * 当选择多个表更新查询时,被锁定的行来源于OF子句后声明的列所在的表中的行
 */
--声明更新游标
cursor cursor_name is select_statement for update [of columns];
--更新行
update table_name set column_nane=column_value
where current of cursor_name;

/*
===========================================================
| 多表查询更新时,更新表为锁定行所在表。
============================================================
*/

declare
     cursor emp_cursor is
        select ename,sal from employee e INNER join dept d
        on e.deptno=d.deptno
    for update of sal;
     v_emp emp_cursor%ROWTYPE;
begin
    if not emp_cursor%ISOPEN then
        open emp_cursor;
    end if;
    loop
        fetch emp_cursor INTO v_emp;
        exit when emp_cursor%NOTFOUND;
        update employee set sal=sal+200 where current of emp_cursor;
    end loop;
    close emp_cursor;
end;

7、使用循环游标简化游标的读取:

/*
 * 循环游标的特点
 * 在从游标中提取了所有记录之后自动终止
 * 提取和处理游标中的每一条记录
 * 如果在提取记录之后%NOTFOUND属性返回true,则终止循环
 * 如果未返回行,则不进入循环
 */
for record_index in cursor_name
    loop
        excutable_statements
    end loop;

/*
===========================================================
| 使用循环游标简化游标的读取
============================================================
*/
--显示雇员表中所有雇员的姓名和薪水
declare
    cursor emp_cursor is select ename,sal from employee;
begin
    for emp_record in emp_cursor loop
       DBMS_OUTPUT.PUT_LINE('第'||emp_cursor%ROWCOUNT||'个雇员:'||emp_record.ename|| emp_record.sal);
    end loop;
end;

8、NOT_DATA_FOUND和$NOTFOUND的区别:

(1)SELECT...INFO语句返回0条和多条记录时触发NOT_DATA_FOUND。

(2)当UPDATE或DELETE语句的WHERE子句未找到时,触发%NOTFOUND。

(3)在提取循环中用%NOTFOUND或%FOUND来确定循环的退出条件,而不用NOT_DATA_FOUND。


存储过程

1、子程序的组成:子程序是已命名的PL/SQL块。

(1)声明部分:包括类型、游标、常量、变量、异常和嵌套子程序的声明。

(2)可执行部分:包括赋值、控制执行过程和Oracle数据的语句。

(3)异常处理部分:包括异常处理程序。

2、子程序的优点:模块化、可重用性、可维护性、安全性。

3、存储过程:是执行某些操作的子程序,是执行特定任务的模块。

4、创建储存过程:

/**
 * procedure_name:存储过程的名称
 * parameter_list:参数列表,可选。参数名称只声明类型,不声明大小
 * local_declarations:局部声明,可选
 * executable_statements:可执行语句
 * exception_handlers:异常处理程序,可选
 * or replace:可选
 */
create [or replace] procedure
   <procedure_name> [(<parameter_list>)]
is|as 
   <local_declarations>
begin
   <executable_statements>
[exception
   <exception_handlers>]
end;

/*
===========================================================
| 添加员工记录
===========================================================
*/
create or replace procedure add_employee(
    eno NUMBER,                                  --输入参数,雇员编号
    name VARCHAR2,                                --输入参数,雇员名称
    salary NUMBER,                                --输入参数,雇员薪水
    job VARCHAR2 DEFAULT 'CLERK',                --输入参数,雇员工种默认'CLERK'
    dno NUMBER                                     --输入参数,雇员部门编号
)
is
begin
   insert into employee(empno,ename,sal,job,deptno) values(eno,name,salary,job, dno);
END;

4、调用存储过程:

(1)用命令调用存储过程:用命令在SQL提示符下调用,使用EXECUTE语句来执行过程。

/*
 * 执行储存过程
 * execute:执行命令,缩写为EXEC
 * procedure_name:过程的名称
 * parameter_list:参数的列表
 */
exec[ute] procedure_name (parameter_list);

/*
 * 参数的传递方式
 */
--按位置传递参数
exec add_emploee(1111,'MARY');
--按名称传递参数
exec add_emploee(dno=>10,name=>'MARY');
--混合方式传递参数
exec add_emploee(1113,don_10,name=>'MARY');

(2)在PL/SQL块中调用存储过程:

/*
===========================================================
| PL/SQL下调用存储过程
===========================================================
*/
begin
   --按位置传递参数
   add_employee(2111,'MARY',2000,'MANAGER',10);
   --按名字传递参数
   add_employee(dno=>10,name=>'MARY',salary=>2000,eno=>2112, job=>'MANAGER');
   --混合方法传递参数
   add_employee(3111,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');
   --默认值法
   add_employee(4111,dno=>10,name=>'MARY',salary=>2000);
end;

5、存储过程的参数模式:

(1)参数传递的三种模式:

IN:只能将实参传递给形参,进入函数内部,但只能读不能写,函数返回时实参的值不变
OUT:会忽略调用时的实参值,但在函数内部可以被读或写,函数返回时形参的值会赋给实参
IN OUT:具有前两种模式的特性

(2)使用语法:

parameter_name [IN | OUT | IN OUT] datatype
[{:= | default} EXPRESSION]

6、存储过程的访问权限:存储过程创建之后,只有创建该存储过程的用户和管理员才有权调用它,其他用户如果要调用该存储过程,需要得到存储过程的EXECUTE权限。

grant execute on add_employee to A_oe;

7、删除存储过程:

drop procedure procedure_name;

8、存储过程规则:

(1)存储过程中不可以直接使用DDL语句,可以通过动态SQL实现。但不建议频繁的使用DDL语句。

(2)存储过程必须有相应的出错处理功能。

(3)存储过程变量使用%type和%rowtype类型。

(4)必须在存储过程体中作异常捕获,并将异常信息通过os_Msg变量输出。

(5)–1 ~ -19999的异常为Oracle定义的异常代码。

(6)存储过程必须包含两个输出参数分别用于标识过程的执行状态及过程提示信息。

(7)"WHEN OTHERS"必须放置在异常处理代码的最后面作为缺省处理器处理没有显式处理的异常。

如无特殊说明,本博所有文章均为博主原创。

如若转载,请注明出处:一木林多 - https://www.l5v.cn/archives/253/

评论