oracle学习之存储过程(一)

oracle存储过程简介

存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，适当的使用存储过程会提升开发人员的工作效率，减少数据库和应用服务器之间的数据传递，提升数据的处理效率

使用存储过程的优点

1.模块化执行 只需要在创建一次过程，就已经编译并存储到数据库中，直接写sql的话会分析检查等再执行，使用存储过程效率会更高，就可以在系统中调用无数次，直接写sql会导致sql注入等安全问题
2.创建过程不会消耗系统资源，只有被调用再会执行
3.存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量
4.可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。
5.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。
6.使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。
7.增强安全性：

a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；

b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数）；

c、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。

使用存储过程的缺点

大量的利用过程，会对服务器压力比较大。

plsql编程

概念

procedure language/SQL
hello world，程序结构 ，变量，流程控制，游标
plsql 是 oracle 对 sql 语言的过程化的拓展（类似于 VB Basic）
指在sql命令中增加过程处理语句（分支，循环等），是sql具有过程处理能力

程序结构

通过plsql Developer工具的Test Window创建程序模板或者通过语句在SQL Window编写
tips:plsql语言不区分大小写
PL/SQL 可分为三部分
1.声明部分，可执行部分，异常处理部分

DECLARE
	--声明变量，游标
	I INTEGER;
BEGIN
	--可执行语句
	-- 判断循环等
	--异常处理
	-- 这是注释
END;		

declare 用来声明变量或者游标（结果集类型变量），如果无变量可以省略掉
hello world plsql示例
DBMS_output.put_line(‘hello world’); --oracle内置的输出程序包

示例

BEGIN
	-- 打印字符串
	DBMS_output.put_line('hello world');
END;	

在命令行中结束plsql 代码块要 另起一行加上 /,
因为命令窗口中 sqlplus plsql 输出选项默认是关闭的，所以我们要看到打印效果 要打开输入
set serverouput on
–BEGIN
-----
– end
才能输出
程序结束后没显示输出结果，可能是忘记打开输出选项
set serveroutput on

plsql 中常见的变量

1.普通数据类型
（CHAR,VARCHAR2,DATE,NUMBER,BOOLEAN,LONG）
2.特殊变量类型（引用型变量，记录型变量）
变量名 变量类型（变量长度）
例如：v_name varchar2（20）；
引用型 – 变量类型取决于 表中字段的类型
记录型 – 接受的是一整条记录的值

普通变量

变量赋值的方式有两种：
1.直接赋值
:=
v_name := ‘张三’
2.语句赋值
select … into … 赋值
语法 （selectd 值 into 变量）

示例

--打印 姓名 工资 地址
DECLARE
   -- 姓名
   v_name VARCHAR2(200) := '张三';
   --工资
   v_sal NUMBER;
   --地址
   v_addr VARCHAR2(200);
BEGIN
   -- 直接赋值
   v_sal := 1500;
   --语句赋值
   select '这就是语句赋值' into v_addr from dual;
   --DBMS_output.put_line('姓名'||v-name||‘工资’:v_sal||'地址'||v_addr);
END	

引用型变量

变量的类型和长度取决于表中字段的类型和长度
通过表名.列名%TYPE指定变量的类型和长度例如
v_name emp.ename%TYPE;
示例

--引用示例
DECLARE
   -- 姓名
   v_name emp.ename%TYPE;
   --工资
   v_sal emp.cql%TYPE;
BEGIN
   --语句赋值
   select ENAME,SAL into v_name,v_sal from emp where EMPNO = '7829';
   --DBMS_output.put_line('姓名'||v_name||‘工资’:v_sal||'地址'||v_addr);
END	

查询的字段和赋值的变量顺序 个数 类型 要一致

记录型变量

接受表中的一整行记录，相当于 java的对象
语法： 变量名称 表名%ROWTYPE ,例如：v_emp emp%ROWTYPE

--记录示例
DECLARE
   -- 姓名
   v_row emp%ROWTYPE;

BEGIN
   --语句赋值
   select * into v_row from emp where EMPNO = '7829';
   --DBMS_output.put_line('姓名'||v_row.ename||‘工资’:v_sal||'地址'||v_addr);
END	
-- 不推荐

select 返回的值 和定义的行变量

流程控制

条件分支

语法

BEGIN 
	IF 条件 THEN 执行1 
	ELSIF 条件2 THEN 执行2
	ELSE 执行3
	
	END IF;
END;	

例子：

-- 判断 scott 用户的 emp 表
DECLARE
	-- 声明变量接受emp中的数量
	v_count NUMBER;
BEGIN
	--查询表中的数量赋值给变量
	SELECT COUNT(0) INTO v_count FROM EMP;
	-- 判断打印
	IF v_count >20 THEN 
	DBMS_output.put_line('emp数量超过20条'||v_count);
	ELSIF v_count >10 THEN 
	DBMS_output.put_line('emp数量超过10条'||v_count);
	ELSE 
	DBMS_output.put_line('emp数量10条一下'||v_count);
	END IF; -- 关闭判断 有开有关
END;	

循环

在 oracle 中有三种循环方式，这里之间LOOP循环
语法：
GOTO 用法

DECLARE
  x number;
BEGIN
  x := 9;
  <<repeat_loop>> --循环点
  x := x - 1;
  DBMS_OUTPUT.PUT_LINE(X);
  IF X > 0 THEN
    GOTO repeat_loop; --当x的值小于9时,就goto到repeat_loop
  END IF;
END;

FOR

 DECLARE
   X number; --声明变量
 BEGIN
   x := 1; --给初值
   FOR X IN REVERSE 1 .. 10 LOOP
     --reverse由大到小
     DBMS_OUTPUT.PUT_LINE('内:x=' || x);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('end loop:x=' || X); --x=1
 END;

WHILE

 DECLARE
    x number;
 BEGIN
   x := 0;
   WHILE x < 9 LOOP
     x := x + 1;
     DBMS_OUTPUT.PUT_LINE('内:x=' || x);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('外:x=' || x);
 END;

LOOP
%NOTFOUND – 判断存不存在

 DECLARE
   x number;
 BEGIN
   x := 0;
   LOOP
     x := x + 1;