Oracle数据库学习：PL/SQL(详解) |
您所在的位置：网站首页 › plsql创建数据库 › Oracle数据库学习：PL/SQL(详解) |
Oracle数据库学习：PL/SQL(详解)
|
Oracle数据库学习：PL/SQL
什么是PL/SQL
PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的扩展语言; 使用PL/SQL 可以编写具有很多高级功能的程序 虽然通过多个SQL语句也可以实现相同的功能，但对于应用程序开发过程中, 需要多次与数据库打交道, PL/SQL可以针对于某一种功能特定的实现…(类似Java的方法) 一定程度上避免了频繁于数据库的请求 (减低服务器压力) 优点： PL/SQL具有编程语言的特点, 它把一组SQL语句放到一个模块中执行，使其更具模块化程序的特点；PL/SQL可以采用过程语言的程序结构，可以在其中写 逻辑判断 循环…与其它编程语言类似，PL/SQL对于程序中的异常也有其响应的处理方式;PL/SQL块，具有更好的可移植性; 可以移植到另一个Oracle数据库中…减少了程序网络的交互(数据了访问), 提高程序性能; 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中的单行注释 /**/ 多行注释;运算符和表达式： 关系运算符： 运算符意义运算符意义大于=大于或等于=等于, !=, ~=, ^=不等于一般预算符： 运算符运算符运算符运算符+加号-减* 乘/除:=赋值号=>关系号. . 范围运算符︳︳字符连接符逻辑预算符： 运算符意义运算符意义IS NULL空值BETWEEN AND介于两者之间iN在一列值之间AND逻辑与OR逻辑或NOT取反, 如 IS NOT NULL 或 NOT IN DECLARE变量的声明：语法： /** 使用DECLARE 关键字,用于定义变量或者常量: PL/SQL块的可执行部分引用变量和常量前,必须先在DECLARE 对其进行声明; */ DECLARE variable_name [CONSTANT] type[(size)] [NOT NULL] [:=value]; BEGIN EXCEPTION END; -- variable_name:变量名称 -- [CONSTANT] :可选表示是否为常量 -- type :表示变量的数据类型 -- [(size)] :可选,数据类型的长度 -- [NOT NULL] :可选,该变量是否可以为空 -- [:=value] :可选,使用 := 给对于对象赋值 初始值;PL/SQL变量名规范 变量命名规则 变量名首字母必须是英文字母 其后可以是字母、数字或者特殊字符$、#和下划线 变量名长度不超过30个字符 变量名中不能有空格 不能是SQL保留字 不能用 - (减号) 唯一比较好的就是不区分大小写了吧… 注意： SELECT 语句在PL/SQL中的写法稍微有改变, SELECT INTO 查询结果只能返回一条记录, 并赋值到变量中保存；如果返回多条数据或没有都会报错; PL/SQL 中对于多条记录的查询, 后面会学习 游标 进行存储多个结果集; PL/SQL控制语句：PL/SQL可以通过控制结构, 来控制命令执行的流程。 标准的SQL 没有流程控制的概念, 而PL/SQL提供了丰富的流程控制语句; 控制结构共有三种类型：条件控制 循环控制 顺序控制 条件控制条件控制用于控制条件执行一系列的语句：条件控制包括 IF语句 和 CASE语句 IF-THEN语句 IF 布尔表达式 THEN PL/SQL和SQL执行语句 END IF;IF-THEN-ELSE语句 IF 布尔表达式 THEN PL/SQL和SQL执行语句1 ELSE PL/SQL和SQL执行语句2 END IF;IF-THEN-ELSIF语句 IF 布尔表达式1 THEN PL/SQL和SQL执行语句1 ELSIF 布尔表达式2 THEN --注意：是ELSIF而不是ELSEIF PL/SQL和SQL执行语句2 ELSE PL/SQL和SQL执行语句3 END IF;CASE：类似于Java的Switch在Oracle 9i 后引入，依据表达式，选择相应的when子句执行 CASE 条件表达式 WHEN 值1 THEN PL/SQL和SQL执行语句1; WHEN 值2 THEN PL/SQL和SQL执行语句2; ..... WHEN 值n THEN PL/SQL和SQL执行语句n; [ELSE else_statements;] --可选：相当于default 都不符合则执行.. END CASE;
循环控制用户重复执行一系列语句，循环控制包括: LOOP WHERE FOR EXIT 语句用于退出循环 EXIT WHERE 条件成立则退出循环… LOOP死循环 LOOP PL/SQL和SQL执行语句; END LOOP; --实现计数器功能，当计数器为10或者大于10时退出 DECLARE v_count integer := 1; --声明变量 BEGIN LOOP --死循环开始 v_count:=v_count+1; --变每次+1 IF v_count>=10 THEN --判断变量 大于等于10 EXIT; --退出循环 END IF; --IF 结束 END LOOP; --死循环结束 END; --PL/SQL块结束WHILE-LOOP循环: 类似于while循环 WHILE 布尔表达式 LOOP PL/SQL和SQL执行语句; END LOOP;FOR-LOOP循环: 类似与for循环 FOR 变量名 in 最小值..最大值 loop 循环执行的语句； END LOOP; -- ..两点表示在其中范围~eg: 1..20 循环从1开始20结束; begin for i in 1..20 loop insert 表 value(i,值1,值2,值3); COMMIT; --提交事务~ end loop; COMMIT; --或放在最后统一提交事务~ -- 后面在加一个异常处理，如果出现异常 ROLLBACK; 回滚数据; end; 顺序控制控制语句用户按顺序执行语句 NULL 个人觉得用的不多… --NULL: -- NULL语句可以是一个可执行的语句,相当于一个占位符,不会执行任何的操作 的空语句; -- 它可以使某些语句变的有意义,提高程序的可读性,保证其它语句结构的完整性 和 正确性; DECLARE v_count Number := 5; --声明变量 BEGIN IF v_count >=10 then null; --IF中需要有执行,不然报错; 为了使结构成立有意义使用 NULL Else dbms_output.put_line('值太小的'||v_count); END IF; END; 动态SQL什么是动态SQL 编译期间SQL 语句是不确定的，并且在运行时允许发生变化 动态SQL应用场合 要执行一个DDL 语句时 需要增加程序的灵活性时 使用包DBMS_SQL动态执行SQL语句时; 动态SQL简直是PL/SQL的一大亮点而且可以搭配：条件控制…使用而产生不同的SQL语句用于执行不同的操作 --动态sql --查询用户总共有多少表 select count(1) from dba_tables select count(1) from dba_tables where table_name = upper('WSM'); --查询所有表 select * from dba_tables --查询用户存在WSM表吗？ 如果有删除/没有则创建; 注意切换一个可以创建的用户哦！System用户！ declare v_count number; --用于判断是否存在表; v_sql varchar2(500); --执行的sql变量,varchar2类型长度500 毕竟存储的是sql长一点好~ begin select count(1) --获取count into v_count from dba_tables where table_name = upper('WSM'); --upper() 方法将字符串中的小写字母转为大写字母 if v_count>0 then --判断是否存在 v_sql := 'drop table WSM'; --拼接SQL else --else 拼接SQL v_sql := 'create table WSM('|| 'id number,'|| 'name varchar2(20)'|| ')'; end if; --执行动态sql execute immediate v_sql; end; -- 前面说PL/SQL中不可以使用 DDL语句; -- 而 execute immediate SQL; 可以解决该问题... EXECUTE IMMEDIATE语句EXECUTE IMMEDIATE dynamic_sql dynamic_sql: 表示一个SQL语句或者一个PL/SQL语句块的字符串表达式, 可以通过EXECUTE IMMEDIATE语句执行… 常用语法 --基本语法： BEGIN execute immediate '执行的SQL'; END; -- 如果在存储过程 或 PL/SQL块执行还可以; -- 给变量进行赋值 INTO -- 对于需要参数执行的SQL(增删改查条件数据..),通过 Using 传参(根据顺序进行参数匹配注意类型也对应哦..); execute immediate 'select 列1,列2 from 表 where id=:id' into 变量1,变量2 USING 条件id; declare v_name varchar2(20); begin execute immediate 'select ename from emp where empno=:a' into v_name USING 7369; --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 * from emp -- 根据输入id 查询对应用户工资 declare v_empno number :=&myid; v_ename varchar2(50); v_sal number; e_exp exception; -- exception异常类型; begin -- 查询数据赋值; select ename , sal into v_ename,v_sal from emp where empno = v_empno; -- 打印输出 dbms_output.put_line(v_ename||'点击量:'||v_sal); if v_sal3,name=>'WSM',salary=>1000,job=>'CLERK',dno=>10); --混合方式传递参数：前面使用顺序传参,后面使用名称传参 EXEC add_EMP(3,'WSM',salary=>1000,job=>'CLERK',dno=>10); --但,前面顺序之后使用了名称之后就不可以在使用顺序了,因为顺序有可能已经乱了 --注意使用前用户要具有操作 存储过程的授权; --存储过程创建之后只有,创建者和管理员才具有调用操作,如果普通用户需要还要赋权！！ --授权语句： GRANT EXECUTE ON 存储过程名 TO 用户名; --撤销权限 REVOKE EXECUTE ON 存储过程名 FROM 用户名; 存储过程的参数模式：IN 用于接受调用程序的值 默认的参数模式 OUT 用于向调用程序返回值 IN OUT 用于接受调用程序的值，并向调用程序返回更新的值 IN表示向存储过程传递参数，OUT表示从存储过程返回参数。而IN OUT 表示传递参数和返回参数； ------------------创建入参和出参的过程-------------------- -- 根据部门查询出对应 人员姓名和职位; create or replace procedure myproc2 -- 声明 有参存储过程( 参数 ); ( in_dept in number, -- in 入参,进入过程执行的参数,需要提供; 这里指对应部门; out_result out sys_refcursor -- out 出参,执行过程之后返回的结果参数,称为出参; 这里是一个游标,返回一个结果集; ) as v_sql varchar2(500); -- 声明变量sql; begin v_sql:='select ename , job from emp where deptno = :a'; open out_result for v_sql using in_dept; -- 指向sql 给对应占位符; end myproc2; -- 执行 myproc2 存储过程~ declare -- 声明变量,用于接收 或 存储过程需要的参数; v_deptno number:=20; -- 参数1 v_result sys_refcursor; -- 出参 动态游标:结果 v_name varchar2(50); v_job varchar2(50); begin scott.myproc2(v_deptno,v_result); -- 调用存储过程(给出对应的参数 部门no 出餐游标)~ loop fetch v_result into v_name, v_job; if v_result%notfound then exit; end if; dbms_output.put_line(v_name ||':'|| v_job); end loop; close v_result; end; |
【本文地址】
今日新闻 |
点击排行 |
|
推荐新闻 |
|
图片新闻 |
|
专题文章 |