Oracle数据库学习：PL/SQL

什么是PL/SQL

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的扩展语言;
使用PL/SQL 可以编写具有很多高级功能的程序
虽然通过多个SQL语句也可以实现相同的功能，但对于应用程序开发过程中, 需要多次与数据库打交道,
PL/SQL可以针对于某一种功能特定的实现…(类似Java的方法) 一定程度上避免了频繁于数据库的请求 (减低服务器压力)
优点：

1. PL/SQL具有编程语言的特点, 它把一组SQL语句放到一个模块中执行，使其更具模块化程序的特点；
2. PL/SQL可以采用过程语言的程序结构，可以在其中写 逻辑判断 循环…
3. 与其它编程语言类似，PL/SQL对于程序中的异常也有其响应的处理方式;
4. PL/SQL块，具有更好的可移植性; 可以移植到另一个Oracle数据库中…
5. 减少了程序网络的交互(数据了访问), 提高程序性能;

PL/SQL体系结构：

PL/SQL块：
由PL/SQL引擎来编译 和 执行PL/SQL块; 该引擎存在于 Oracle服务器中；
PL/SQL引擎：执行所有过程语句，将SQL语句发送给Oracle的SQL语句执行器

PL/SQL简介：

PL/SQL 是一种块结构的语言：
它把一组SQL语句放到一个模块中执行，使其更具模块化程序的特点；
匿名块是一个未在数据库中命名的PL/SQL块, 在运行时传递到PL/SQL引擎中执行…
PL/SQL中可以使用： select insert update delete…事务语句以及sql函数；增删改别忘了 COMMIT 提交事务~
PL/SQL中不允许直接使用：create deop alter 但可以通过动态SQL来执行它们…
一个PL/SQL语句由三个部分组成：声明部分执行部分异常处理部分

PL/SLQ结构块：

--PL/SQL块基本结构：DECLARE-- DECLARE 可选,声明部分:--在此声明PL/SQL用到的变量,常量,类型,游标 以及局部的存储过程和函数;BEGIN-- BEGIN~end 必须项,执行部分:-- 在此编写执行的过程 及 SQL语句,即程序的主要部分;EXCEPTION-- EXCEPTION 可选,指定出现错误时需要执行的操作END;-- end: 表示Pl/sql块的结束,别忘了分号结尾;-- 上面是PL/SQL块的基本结构, BEGIN~END; 是必须的 声明部分和异常处理部分并不是必须的..-- 是PL/SQL中的单行注释 /**/ 多行注释;

运算符和表达式：
关系运算符：

DECLARE变量的声明：

语法：

/** 使用DECLARE 关键字,用于定义变量或者常量: PL/SQL块的可执行部分引用变量和常量前,必须先在DECLARE 对其进行声明; */DECLAREvariable_name[CONSTANT]type[(size)][NOTNULL][:=value];BEGINEXCEPTIONEND;-- variable_name:变量名称-- [CONSTANT] :可选表示是否为常量-- type :表示变量的数据类型-- [(size)] :可选,数据类型的长度-- [NOT NULL] :可选,该变量是否可以为空-- [:=value] :可选,使用 := 给对于对象赋值 初始值;

PL/SQL变量名规范

变量命名规则
变量名首字母必须是英文字母
其后可以是字母、数字或者特殊字符$、#和下划线
变量名长度不超过30个字符
变量名中不能有空格 不能是SQL保留字 不能用 - (减号)
唯一比较好的就是不区分大小写了吧…

实例Demo

-- scott用户下emp举例select*fromemp;--PL/SQL 根据输入id,获得当前用户名 JOB-- 方式一:PL/SQL块 注意分号~declare--声明变量 id ename job, 每一个变量之间 ; 分号分隔...--id 通过 :=值 进行赋默认值,并通过 &xxx由Oracle工具输入参数;v_id number :=&myid;--还可以把: &myid手动输入换位固定的 :=7934; 固定的id号;v_ename varchar2(50);v_JOB varchar2(50);beginselectename,jobintov_ename,v_JOBfromempwhereempno=v_id;dbms_output.put_line('用户名'||v_ename||'职位: '||v_JOB);end;-- 还可以通过: select into 给变量进行赋值-- select 列1,列2 into 变量1,变量2 form 表 形式进行下标自动映射赋值..因此列类型和变量类型 顺序都要尽量对应-- dbms_output.put_line(); Oracle还可以通过该语句进行数据打印输出...-- 方式二: 解决了不清楚变量与列的类型declarev_id emp.empno%type:=&id;v_ename emp.ename%type;v_JOB emp.job%type;v_emp emp%Rowtype;beginselectename,jobintov_ename,v_JOBfromempwhereempno=v_id;dbms_output.put_line('用户名'||v_ename||'职位: '||v_JOB);select*intov_empfromempwhereempno=v_id;dbms_output.put_line('用户名'||v_emp.ename||'职位: '||v_emp.job);end;-- 表.列%type: 可以在不清楚列类型时候给设置类型;-- 表%ROWTYPE: 可以在不知道表中列个数 和 数据类型时候,可以通过 表%ROWTYPE 获取一个表类型变量...

注意：
SELECT 语句在PL/SQL中的写法稍微有改变,
SELECT INTO 查询结果只能返回一条记录, 并赋值到变量中保存；如果返回多条数据或没有都会报错;
PL/SQL 中对于多条记录的查询, 后面会学习游标进行存储多个结果集;

PL/SQL控制语句：

PL/SQL可以通过控制结构, 来控制命令执行的流程。
标准的SQL 没有流程控制的概念, 而PL/SQL提供了丰富的流程控制语句;
控制结构共有三种类型：条件控制 循环控制 顺序控制

条件控制

条件控制用于控制条件执行一系列的语句：条件控制包括 IF语句 和 CASE语句
IF-THEN语句

IF布尔表达式THENPL/SQL和SQL执行语句ENDIF;

IF-THEN-ELSE语句

IF布尔表达式THENPL/SQL和SQL执行语句1ELSEPL/SQL和SQL执行语句2ENDIF;

IF-THEN-ELSIF语句

IF布尔表达式1THENPL/SQL和SQL执行语句1ELSIF 布尔表达式2THEN--注意：是ELSIF而不是ELSEIFPL/SQL和SQL执行语句2ELSEPL/SQL和SQL执行语句3ENDIF;

CASE：类似于Java的Switch在Oracle 9i 后引入，依据表达式，选择相应的when子句执行

CASE条件表达式WHEN值1THENPL/SQL和SQL执行语句1;WHEN值2THENPL/SQL和SQL执行语句2;.....WHEN值nTHENPL/SQL和SQL执行语句n;[ELSEelse_statements;]--可选：相当于default 都不符合则执行..ENDCASE;

循环控制

循环控制用户重复执行一系列语句，循环控制包括: LOOP WHERE FOR
EXIT 语句用于退出循环
EXIT WHERE <条件语句>条件成立则退出循环…

LOOP死循环

LOOPPL/SQL和SQL执行语句;ENDLOOP;--实现计数器功能，当计数器为10或者大于10时退出DECLAREv_countinteger:=1;--声明变量BEGINLOOP--死循环开始v_count:=v_count+1;--变每次+1IFv_count>=10THEN--判断变量 大于等于10EXIT;--退出循环ENDIF;--IF 结束ENDLOOP;--死循环结束END;--PL/SQL块结束

WHILE-LOOP循环:类似于while循环

WHILE布尔表达式LOOPPL/SQL和SQL执行语句;ENDLOOP;

FOR-LOOP循环:类似与for循环

FOR变量名in最小值..最大值loop循环执行的语句；ENDLOOP;-- ..两点表示在其中范围~eg: 1..20 循环从1开始20结束;beginforiin1..20loopinsert表value(i,值1,值2,值3);COMMIT;--提交事务~endloop;COMMIT;--或放在最后统一提交事务~-- 后面在加一个异常处理，如果出现异常 ROLLBACK; 回滚数据;end;

顺序控制

控制语句用户按顺序执行语句NULL个人觉得用的不多…

--NULL:-- NULL语句可以是一个可执行的语句,相当于一个占位符,不会执行任何的操作 的空语句;-- 它可以使某些语句变的有意义,提高程序的可读性,保证其它语句结构的完整性 和 正确性;DECLAREv_count Number :=5;--声明变量BEGINIFv_count>=10thennull;--IF中需要有执行,不然报错; 为了使结构成立有意义使用 NULLElsedbms_output.put_line('值太小的'||v_count);ENDIF;END;

动态SQL

什么是动态SQL
编译期间SQL 语句是不确定的，并且在运行时允许发生变化
动态SQL应用场合
要执行一个DDL 语句时 需要增加程序的灵活性时 使用包DBMS_SQL动态执行SQL语句时;
动态SQL简直是PL/SQL的一大亮点而且可以搭配：条件控制…使用而产生不同的SQL语句用于执行不同的操作

--动态sql--查询用户总共有多少表selectcount(1)fromdba_tablesselectcount(1)fromdba_tableswheretable_name=upper('WSM');--查询所有表select*fromdba_tables--查询用户存在WSM表吗？ 如果有删除/没有则创建; 注意切换一个可以创建的用户哦！System用户！declarev_count number;--用于判断是否存在表;v_sql varchar2(500);--执行的sql变量,varchar2类型长度500 毕竟存储的是sql长一点好~beginselectcount(1)--获取countintov_countfromdba_tableswheretable_name=upper('WSM');--upper() 方法将字符串中的小写字母转为大写字母ifv_count>0then--判断是否存在v_sql :='drop table WSM';--拼接SQLelse--else 拼接SQLv_sql :='create table WSM('||'id number,'||'name varchar2(20)'||')';endif;--执行动态sqlexecuteimmediate v_sql;end;-- 前面说PL/SQL中不可以使用 DDL语句;-- 而 execute immediate SQL; 可以解决该问题...

EXECUTE IMMEDIATE语句

EXECUTE IMMEDIATE dynamic_sql
dynamic_sql: 表示一个SQL语句或者一个PL/SQL语句块的字符串表达式, 可以通过EXECUTE IMMEDIATE语句执行…

常用语法

--基本语法：BEGINexecuteimmediate'执行的SQL';END;-- 如果在存储过程 或 PL/SQL块执行还可以;-- 给变量进行赋值 INTO-- 对于需要参数执行的SQL(增删改查条件数据..),通过 Using 传参(根据顺序进行参数匹配注意类型也对应哦..);executeimmediate'select 列1,列2 from 表 where id=:id'into变量1,变量2USING条件id;declarev_name varchar2(20);beginexecuteimmediate'select ename from emp where empno=:a'intov_nameUSING7369;--USING指定 7369编号;dbms_output.put_line(v_name);end;/* 注意事项： EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交(对于增删改需要 commit; ) 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号. 对于处理动态语句,EXECUTE IMMEDIATE 比以前可能用到的更容易并且更高效. */

对于execute 是真的非常好用, 尤其在存储过程~ 但是对于拼接一些特殊字符要非常注意！！
比如引号 ‘ 这真是恶心死我了搜了很多有的是 用 ’‘’ 三个引号间隔拼接为一个 ‘ 的对于新手非常难受;

可以使用ASCLL码来代替引号或是其它特殊字符~
chr(39) 就表示一个单引号~ 39 ASCLL码表示一个 单引号;
这是我在完成存储过程中的一个条件查询拼接时间条件范围查询：
v_where:= v_where||' AND EXP_DATE >= TO_DATE('||chr(39)||v_qdate||chr(39)||','||chr(39)||'yyyy-fmmm-fmdd'||chr(39)||')';
当前时写 简直恶心死了,先将字符转换为时间进行判断, 过程还要当心引号！！

异常处理 EXCEPTION

什么是异常：
在运行过程中出现的错误叫做异常,
发生异常后, 语句将停止执行，PL/SQL引擎立即将控制权转到PL/SQL的异常处理部分
注意：
这里说的是 执行过程中的异常,并不包含编译的异常…
异常处理exception用来处理正常执行过程中未预料的事件…
两种比较经典的异常：预定义异常和用户自定义异常

预定义异常：

Oracle预定义异常情况大约有 24个,
对于这种异常情况的处理无须在程序中定义, 可由Oracle自动引发…
当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发；
常见预定义异常：

OTHERS
处理程序异常不会漏过任何异常, 它相当于Java的 Exception 无论什么异常都可以捕获
所以一般声明在 最后, 进行捕获遗落的异常处理…
图上代码实例：
异常处理之后加一个 ROLLBACK; 回滚一下效果更好…
实例意思是：循环新增三个商品类别(当前数据有点随便…) 如果过程中有异常进行捕获处理…则插入成功！

用户自定义异常：

用户可以在 PL/SQL 块的声明部分定义异常，
自定义的异常通过RAISE语句显式引发;
实例Demo

-- 切换Scott用户select*fromemp-- 根据输入id 查询对应用户工资declarev_empno number :=&myid;v_ename varchar2(50);v_sal number;e_exp exception;-- exception异常类型;begin-- 查询数据赋值;selectename,salintov_ename,v_salfromempwhereempno=v_empno;-- 打印输出dbms_output.put_line(v_ename||'点击量:'||v_sal);ifv_sal<2000then-- 如果工资小于两千raise e_exp;-- 抛出异常..endif;exceptionwhenno_data_foundthendbms_output.put_line('没有找到数据');rollback;whene_expthen-- 处理手动异常...dbms_output.put_line('工资小于两千');rollback;whenothersthen-- 处理任何异常..dbms_output.put_line('程序异常');rollback;end;

扩

除了以上的异常情况处理…
RAISE_APPLICATION_ERROR
还可以重新定义异常错误消息，为应用程序提供一种与Oracle交互方法d
语法：
RAISE_APPLICATION_ERROR(err_number,err_message);
err_number: 表示为用户异常标识指定的编号, 注意范围必须是 -20999~20000之间负整数’
err_message: 用户为异常指定的文本信息…长度可达 2049字节…

游标

游标基本原理

Oracle 在执行一个包含 SELECT INSERT DELETE UPDATE …的语句时候
会在内存中创建出一个缓存区，将执行结果放在这个缓冲区中, 而游标是指向该区的一个指针…

游标分类：

Oracle中提供了两种游标类型,静态游标(本次讲解)动态游标
静态游标： 是在编译器就知道明确的SELECT 语句的游标…又细分为 显示游标/隐式游标

声明游标

CURSORcursor_name[(parameter[,parameter]…)][RETURNreturn_type]ISselectsql;/* CURSOR： 用于声明一个游标,一般配合PL/SQL 声明使用... cursor_name： 自定义游标名; parameter： 可选参数,用于指定参数类型、模式等 return_type： 可选,指定游标的返回类型 selectsql: 需要处理的select语句，不能含INTO子句 */

打开游标

opencursor_name;-- cursor_name: 指定的游标名,来打开游标...

提取游标

FETCHcursor_nameINTOvariable_list;-- cursor_name: 指定的游标名,来打开游标...-- variable_list： 用于接收,游标中数据的变量名...注意类型匹配哦,使用时和普通的 SELECT 列1,列2 INTO 变量1,变量2 ... 类似;

关闭游标

close游标名;-- 使用CLOSE语句关闭一个游标-- 关闭游标后，所有资源都将被释放，且不能再次被打开

实例Demo

declare--声明部分--创建变量Ename varchar2(20);--创建一个静态游标, IS指定SELECTCURSORAAISSELECTEnameFROMEMP;begin--执行部分OPENAA;--打开游标,一般都是第一行声明...--调用游标;FETCHAAINTOEname;--第一次调用dbms_output.put_line(Ename);FETCHAAINTOEname;--第二次调用dbms_output.put_line(Ename);CloseAA;--关闭游标,一般都是最后一行...end;-- 两次分别输出了,两个用户名...

读取多行记录时，可以采用循环的方式 :LOOP循环WHILE循环FOR循环
显示游标的属性：

属性名称	说明
%found	返回true和false，表示是否有数据
%isopen	返回true和false，表示游标是否打开
%notfound	返回true和false，表示是否没有数据，一般调用前建议判断是否有值在使用..
%rowcount	表示受影响的数据一共有多少行

WHERE循环条件可以直接设置：游标%notfound进行循环…

使用显示游标 删除或更新

个人看了一段时间才明白, 可能介绍不全… 而且觉得很鸡肋！！

使用游标时候，如果处理过程中需要删除 或 更新行可以借鉴下面做法…;
在定义游标时候必须使用：SELECT…FOR UPDATE语句
在执行 DELETE 和 UPDATE时使用：WHERE CURRENT OF 子句指定游标当前行详情如下！

声明

CURSOR游标名ISSELECT查询语句FORUPDATE[ofcolumns];-- FOR UPDATE： 删除/跟新关键字-- [of columns: 查询语句单表时候,可省略...对于多表的查询,需要根据 of 列;-- 锁定针对某一个表的操作,该游标 删/改只能对于该表...--使用时：根据WHERE后面条件...UPDATE表SET列WHERECURRENTOF游标名;DELETEFROM表WHERECURRENTOF游标名;

实例Demo

select*fromemp--下面以给工资低于2000的雇员增加100元工资为例，说明使用显示游标更新数据的方法。示例如下：declarecursoremp_cursorisselectename,salfromempforupdate;v_ename emp.ename%type;--定义变量v_sal emp.sal%type;beginopenemp_cursor;--打开游标loopfetchemp_cursorintov_ename,v_sal;--提取游标数据赋值给变量exitwhenemp_cursor%notfound;ifv_sal<2000then--当变量v_sal 小于2000时执行更新操作updateempsetsal=sal+100wherecurrentofemp_cursor;endif;endloop;closeemp_cursor;--关闭游标end;

存储过程

将SQL或者PL/SQL代码块集中用于完成特定功能的集合
过程的结构
声明部分：包括类型、变量、游标
执行部分：完成功能而编写的SQL语句或则是PL/SQL代码块
异常处理部分
从根本上来讲就是命名的PL/SQL块, 它可以比赋予参数并存储在数据库中可以由一个应用程序或其它PL/SQL调用使用

创建存储过程

CREATE[ORREPLACE]PROCEDURE<procedure_name>[(<parameter list>)]IS|AS[localvariable declaration]--可选,局部声明;BEGIN<executable statements>--可执行语句[EXCEPTION<exception handlers>]--可选,异常处理执行...END[procedure_name];--<procedure_name>: 存储过程名称;--[(<parameter list>)] 可选,存储过程的参数列表--[OR REPLACE] 可选,如果不包含 OR REPLACE 则表示仅仅新建一个存储过程,存在则报错;-- 如果包含 OR REPLACE 如果已存在则覆盖..

实战

----------------无参存储过程 myproc1 -----------------------判断员工EMP表,每个员工在对应部门的平均薪资...评价createorreplaceproceduremyproc1as-- 定义变量v_name emp.ename%type;v_sal emp.sal%type;v_dept emp.empno%type;v_dept_avgsal number;v_sql varchar2(200);--动态SQL变量-- 定义游标cursorcur_resultisselectename,sal,deptnofromemporderbydeptno;begin-- 打开游标opencur_result;-- 死循环结束loop-- 调用游标..fetchcur_resultintov_name,v_sal,v_dept;-- 判断条件终止循环..ifcur_result%notfoundthenexit;endif;-- 定义SQLv_sql:='select avg(sal) from emp where deptno =:a';-- 执行SQL获取 当前部门平均薪资executeimmediate v_sqlintov_dept_avgsalusingv_dept;-- 判断平均薪资ifv_sal>v_dept_avgsalthendbms_output.put_line(v_name||':很优秀');elsif v_sal=v_dept_avgsalthendbms_output.put_line(v_name||':良好');elsedbms_output.put_line(v_name||':需要努力了');endif;endloop;--循环结束closecur_result;--关闭游标exception--异常处理whenothersthendbms_output.put_line('程序出异常了！');endmyproc1;--存储过程结束;----------------有参存储过程 add_EMP --------------------- 添加员工记录CREATEORREPLACEPROCEDUREadd_EMP(--输入参数eno NUMBER,--员工编号name VARCHAR2,--名称salary varchar2,--薪水job varchar2DEFAULT'CLERK',--员工部分,默认CLERKdno number)ISBEGININSERTINTOEMP(empno,ename,sal,job,deptno)VALUES(eno,name,salary,job,dno);END;

存储过程创建完, 数据库定义执行完后并不会立刻执行，而是在数据库中存在…
和 序列 索引 一样存储在某个位置, 方便后面使用时直接调用即可…(数据库/应用程序…执行时可以调用降低数据库和程序之间交流..降低资源)

过程的调用与删除

调用存储过程

EXEC[UTE] 存储过程名( 参数列表 );

有参存储过程 参数传递的三种方式？

--按位置传递参数EXECadd_EMP(3,'WSM',1000,'CLERK',10);--按名称传递参数EXECadd_EMP(eno=>3,name=>'WSM',salary=>1000,job=>'CLERK',dno=>10);--混合方式传递参数：前面使用顺序传参,后面使用名称传参EXECadd_EMP(3,'WSM',salary=>1000,job=>'CLERK',dno=>10);--但,前面顺序之后使用了名称之后就不可以在使用顺序了,因为顺序有可能已经乱了--注意使用前用户要具有操作 存储过程的授权;--存储过程创建之后只有,创建者和管理员才具有调用操作,如果普通用户需要还要赋权！！--授权语句：GRANTEXECUTEON存储过程名TO用户名;--撤销权限REVOKEEXECUTEON存储过程名FROM用户名;

存储过程的参数模式：

IN
用于接受调用程序的值 默认的参数模式
OUT
用于向调用程序返回值
IN OUT
用于接受调用程序的值，并向调用程序返回更新的值
IN表示向存储过程传递参数，OUT表示从存储过程返回参数。而IN OUT 表示传递参数和返回参数；

------------------创建入参和出参的过程---------------------- 根据部门查询出对应 人员姓名和职位;createorreplaceproceduremyproc2-- 声明 有参存储过程( 参数 );(in_deptinnumber,-- in 入参,进入过程执行的参数,需要提供; 这里指对应部门;out_resultoutsys_refcursor-- out 出参,执行过程之后返回的结果参数,称为出参; 这里是一个游标,返回一个结果集;)asv_sql varchar2(500);-- 声明变量sql;beginv_sql:='select ename , job from emp where deptno = :a';openout_resultforv_sqlusingin_dept;-- 指向sql 给对应占位符;endmyproc2;-- 执行 myproc2 存储过程~declare-- 声明变量,用于接收 或 存储过程需要的参数;v_deptno number:=20;-- 参数1v_result sys_refcursor;-- 出参 动态游标:结果v_name varchar2(50);v_job varchar2(50);beginscott.myproc2(v_deptno,v_result);-- 调用存储过程(给出对应的参数 部门no 出餐游标)~loopfetchv_resultintov_name,v_job;ifv_result%notfoundthenexit;endif;dbms_output.put_line(v_name||':'||v_job);endloop;closev_result;end;