初步学习Oracle之PL/SQL

PL/SQL简介

PL/SQL（Procedure Language/SQL） 程序语言是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

PL/SQL语法结构

PL/SQL块有四个关键字：

• DECLARE：声明部分。
• BEGIN：可执行部分。
• EXCEPTION：异常处理部分。
• END：以关键字END结束。

DECLARE
     -- 声明变量、游标。 
     I INTEGER; 
BEGIN
     -- 执行语句
     --[异常处理]
EXCEPTION
  when NO_DATA_FOUND then
    raise_application_error(-20000, 'No Data Found!');
END;

例子：打印Hello World

BEGIN
     --打印hello world
    DBMS_OUTPUT.PUT_LINE('hello world');
END;

其中DBMS_OUTPUT 为oracle内置程序包，相当于Java中的System.out，而PUT_LINE（）是调用的方法，相当于println（）方法。

变量

说明：

1、变量命名建议遵循通用规则，比如v_name表示一个变量，c_name表示一个常量；

2、一般建议每一行声明一个变量，这样程序的可读性比较好；

3、如果声明了变量，但未进行初始化，则在没有赋值之前该变量的值为NULL; 一个好的编程习惯是对所有声明的变量进行初始化赋值。

普通数据变量

（char,varchar2, date, number, boolean, long）

例子：声明一些普通变量

DECLARE
	v_job VARCHAR2(9);
	v_count BINARY_INTEGER := 0;
	v_total_sal NUMBER(9,2) := 0;
	v_orderdate DATE := SYSDATE + 7;
	c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
	v_valid BOOLEAN NOT NULL := TRUE;

特殊变量类型

引用型变量

1、利用直接赋值语句赋值  :=  比如：v_name := 'hans'
2、利用select...into...语句赋值：（语法 select 值 into 变量）
         select 'name' 
          into v_name
          from student;
//将student表查的name的值赋值给v_name

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

例如：声明员工姓名、员工薪水两个字段，可以用以下的语法
DECLARE
	v_name emp.ename%TYPE;          	
	v_salary emp.esalary%TYPE;
BEGIN
	  SELECT ENAME,ESALARY
	  into v_name,v_salary
	  FROM EMP
	  WHERE ROWNUM=1;
 END;

记录型变量

接受表中的一整行记录，相当于Java中的一个对象
语法：变量名称 表名%ROWTYPE，

v_emp emp%ROWTYPE;
SELECT *
  into v_emp
  FROM EMP
  WHERE ROWNUM=1 ;
  DBMS_OUTPUT.put_line(v_emp.ename||','||v_emp.esalary); 

--可以用我们定义地记录变量名直接点出来
-- || 是字符串连接的意思
--如果有一个表，有100个字段，那么你程序如果要使用这100个字段话，如果你使用引用型变量一个个声明，会特别麻烦，记录型变量可以方便的解决这个问题。
--记录型变量只能存储一个完整的行数据，只能一行，多行就报错

--例子：type  numbers  is table of number index by binary_integer
--加了”index by binary_integer ”后，numbers类型的下标就是自增长，可以自动根据下标找到对应的值。
--numbers类型在插入元素时，不需要初始化，不需要每次extend增加一个空间。
DECLARE
	TYPE emp_table_type is table of
		employees%ROWTYPE INDEX BY BINARY_INTEGER;
	my_emp_table emp_table_type; --重命名
	v_count NUMBER(3):= 104; 
BEGIN
	FOR i IN 100..v_count --i不需定义，从100递增到v_count
	--（IN后面加REVERSE可表示倒序）
	LOOP
		SELECT * INTO my_emp_table(i) FROM employees --自动根据下标找到对应的值
		WHERE employee_id = i;
	END LOOP;
	FOR i IN my_emp_table.FIRST..my_emp_table.LAST
	LOOP
		DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
	END LOOP;
END;

流程控制

1、条件分支
begin
     IF    条件1   THEN   执行1
     ELSIF    条件2    THEN   执行2
     ELSE    执行3
     END IF；
 end;
    --注意关键字：ELSIF

2、循环

  BEGIN
	      LOOP
	      EXIT WHEN 退出循环条件
	      END LOOP;
    END;
   -- exit when是退出循环的条件

  例子：
  【示例】打印数字1-10
    DECLARE
        -- 声明循环变量
        V_NUM NUMBER := 1;  --普通变量，直接使用:=赋值
    BEGIN
        LOOP --循环的关键字，循环开始
            EXIT WHEN V_NUM > 10; --退出循环的条件
            DBMS_OUTPUT.PUT_LINE(V_NUM); --打印变量
            -- 循环变量的自增
            V_NUM := V_NUM + 1;
        END LOOP; --循环结束
    END;

SQL语句

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

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

注：SQL函数在PLSQL的过程语句中的使用：

大多数SQL函数都可以在 PLSQL的过程语句中使用，比如：

单行的数值和字符串函数、数据类型转换函数、日期函数、时间函数、求最大、最小值的 GREATEST, LEAST函数等；

但有些函数在PLSQL的过程语句中是不能使用的，比如：

Decode函数、分组函数（AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE）等；

游标

用于临时存储一个查询返回的多行数据（结果集，类似于Java的Jdbc连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。

游标的使用方式：声明—>打开—>读取—>关闭 语法

游标声明： CURSOR 游标名[(参数列表)] IS 查询语句;

游标的打开： OPEN 游标名；

游标的取值： FETCH 游标名 INTO 变量列表；（游标的数据放变量列表里）

游标的关闭；CLOSE 游标名；

游标的属性 游标的属性————返回值类型————说明

%ROWCOUNT-----------整型----------获得FETCH语句返回的数据整行

%FOUND------------布尔型---------------最近的FETCH语句返回一行数据则为真，否则为假（能拿到数据并且能返回一条数据，则真）

%NOTFOUND--------------布尔型-----------与%FOUND属性返回值相反（一般用于退出循环的判断）

%ISOPEN----------------布尔值-------------游标已经打开时值为真，否则为假

其中%NOTFOUND是在游标中找不到元素的时候返回TRUE，通常用来判断退出循环。

【例子】：使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。

  DECLARE
 -- 声明游标  --没有参数的游标
CURSOR C_EMP IS  --定义游标的名字
SELECT ENAME, ESALARY FROM EMP;  --查询语句
        -- 声明变量接受游标中的数据
        V_ENAME  EMP.ENAME%TYPE;
        V_SALARY EMP.ESALARY%TYPE;
    BEGIN
        -- 打开游标
        OPEN C_EMP;
        -- 遍历游标
        LOOP
            -- 获取游标中的数据
            FETCH C_EMP
                INTO V_ENAME, V_SALARY; --将获得到的数据复制到我们定义的变量
            -- 退出循环条件
            EXIT WHEN C_EMP%NOTFOUND; --exit when是loop循环跳出语句，C_EMP是游标的名字，										  --%NOTFOUND是游标里面没有数据了返回true，
            		--EXIT WHEN C_EMP%NOTFOUND就是游标没数据了返回为true了，促发退出循环的条件
            DBMS_OUTPUT.PUT_LINE('姓名：' || V_ENAME || '薪资：' || V_SALARY); --输出循环打印
        END LOOP; --循环结束
        -- 关闭游标
        CLOSE C_EMP; 
    END;

带参数的游标：

	DECLARE
    -- 声明游标
    CURSOR C_EMP(V_EDEPNO emp.edepno%type) IS
        SELECT ENAME, ESALARY FROM EMP WHERE EDEPNO=V_EDEPNO;
    -- 声明变量接受游标中的数据
    V_ENAME  EMP.ENAME%TYPE;
    V_SALARY EMP.ESALARY%TYPE;
    BEGIN
        -- 打开游标
        OPEN C_EMP('a'); --传入游标的参数a
        -- 遍历游标
        LOOP
            -- 获取游标中的数据
            FETCH C_EMP
                INTO V_ENAME, V_SALARY;
            -- 退出循环条件
            EXIT WHEN C_EMP%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('姓名：' || V_ENAME || '薪资：' || V_SALARY);
        END LOOP;
        -- 关闭游标
        CLOSE C_EMP;
    END;

FOR UPDATE NOWAIT语句： 有的时候我们打开一个游标是为了更新或者删除一些记录，这种情况下我们希望

在打开游标的时候即锁定相关记录，应该使用for update nowait语句，倘若锁定失败我们就停止不再继续，以免

出现长时间等待资源的死锁情况。

DECLARE
	CURSOR emp_cursor IS 
		SELECT employee_id, last_name, department_name
		FROM employees,departments
		WHERE employees.department_id = 
			departments.department_id
		AND employees.department_id = 80 
		FOR UPDATE OF salary NOWAIT; --将salary字段锁定

WHERE CURRENT OF cursor ： 在循环体内做Update或者 Delete时需要有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 --将sal_cursor的数据放到emp_record结果集中
LOOP 
	IF emp_record.salary < 5000 THEN 
		UPDATE employees 
		SET salary = emp_record.salary * 1.10 
		WHERE CURRENT OF sal_cursor; --Where指向当前游标记录
	END IF; 
END LOOP; 
END;

RAISE_APPLICATION_ERROR() 函数： 对于用户自定义的业务错误，如果觉得先定义再使用很麻烦，那么

也可以简单的使用raise_application_error() 来简化处理。它可以无需预先定义错误，而在需要抛出错误的

地方直接使用此函数抛出例外，例外可以包含用户自定义的错误吗和错误描述；

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		RAISE_APPLICATION_ERROR (-20201,'Manager is not a valid employee.');
	END;

存储过程

一般的sql语句每执行一次就编译一次，而存储过程只在创造时编译，以后每次执行存储过程都不需要再重新编译，所以使用存储过程可提高数据库执行速度。

当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。

存储过程可以重复使用,可减少数据库开发人员的工作量，安全性高，可设定只有某此用户才具有对指定存储过程的使用权。

create or replace procedure   过程名[(参数列表)]  is
        -- 没有使用declare声明变量，但是可以在begin上边直接声明变量
    begin
       -- 执行部分
    end [过程名] ;
-- is可换as

例如：

CREATE OR REPLACE PROCEDURE raise_salary
	(p_id IN employees.employee_id%TYPE)
IS
BEGIN
	UPDATE employees
	SET salary = salary * 1.10
	WHERE employee_id = p_id;
END raise_salary;

调用存储过程：将要调用的存储过程编译之后，重新打开一个test window，在begin中直接输入存储过程名即可。

例子：带输入参数IN和输出参数OUT的存储过程

--输入员工名字，将“HELLO员工名”当作返回值输出，in输入参数，out输出参数
create or replace procedure P_PRINT_HELLO (V_ENAME IN EMP.ENAME%TYPE,V_RETURN out VARCHAR2) is
      --声明变量
    begin
       V_RETURN:='HELLO '||V_ENAME;   -- :=普通变量赋值
    end P_PRINT_HELLO;

参数传递方式 ( 按顺序传递 或者 使用=>符号传递 )
CREATE OR REPLACE PROCEDURE add_dept
	(p_name IN departments.department_name%TYPE DEFAULT 'unknown',
	p_loc IN departments.location_id%TYPE DEFAULT 1700)
IS
BEGIN
	INSERT INTO departments(department_id,department_name, location_id)
	VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;

BEGIN
add_dept; --使用默认值
add_dept ('TRAINING', 2500); --按顺序传递
add_dept ( p_loc => 2400, p_name =>'EDUCATION'); --使用=>符号传递，无顺序要求。
add_dept ( p_loc => 1200) ;
END;

--删除存储过程
DROP PROCEDURE procedure_name

存储函数

--定义函数语法
CREATE [OR REPLACE] FUNCTION function_name
	[(parameter1 [mode1] datatype1,
	parameter2 [mode2] datatype2,
	. . .)]
RETURN datatype
IS|AS
PL/SQL Block;

--例子
CREATE OR REPLACE FUNCTION get_sal
	(p_id IN employees.employee_id%TYPE)
	RETURN NUMBER --函数返回值（区别存储过程）
IS
	v_salary employees.salary%TYPE :=0;
BEGIN
	SELECT salary
	INTO v_salary
	FROM employees
	WHERE employee_id = p_id;
	RETURN v_salary; --最后需返回参数
END get_sal;

用户自定义函数必须满足以下条件限制：

• 必须是个函数（不能是过程-Procedure）
• 只能用IN 模式的参数（不能有OUT, IN OUT模式的参数）
• 只能接收SQL数据类型的参数，不能接收PLSQL中特有的参数（比如记录、PLSQL内存表）
• 函数返回的数据类型也必须是有效的数据类型，而不能是PLSQL特有的数据类型
• 在SQL中使用的函数，其函数体内部不能有DML语句
• 在UPDATE/DELETE语句中调用的函数，其函数体内部不能有针对同一张表的查询语句
• 在SQL中调用的函数，其函数体内部不能有事务结束语句（比如Commit，Rollback)

--删除存储函数
DROP FUNCTION function_name