PL/SQL基础入门，史上最全的教程

1、什么是PL/SQL?

PLSQL是Oracle对sql语言的过程化扩展 (类似于Basic)
指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。（减少数据库和服务器之间的交互，提高执行效率）

2、程序结构
PLSQL语言的大小写是不区分的，PL/SQL可以分为三个部分：声明部分、可执行部分、异常处理部分。

DECLARE
     -- 声明变量、游标。 
     I INTEGER; 
BEGIN
     -- 执行语句
     --[异常处理]
END;

其中 DECLARE部分用来声明变量或游标（结果集类型变量），如果程序中无变量声明可以省略掉

3、打印Hello World，在TestWindows里面创建

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

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

4、变量
PLSQL编程中常见的变量分两大类：

普通数据类型（char,varchar2, date, number, boolean, long） 特殊变量类型（引用型变量、记录型变量）

声明变量的方式为 变量名 变量类型（变量长度） 例如： v_name varchar2(20);

5、普通变量
变量赋值的方式有两种：

1、直接赋值语句  **:=**  比如：v_name := 'zhangsan'
2、语句赋值，使用select...into...赋值：（语法 select 值 into 变量）
         select 'lisi' 
          into v_name
          from dual;
          将dual表查的lisi的值赋值给v_name

6、引用型变量
变量的类型和长度取决于表中字段的类型和长度
通过表名.列名%TYPE指定变量的类型和长度，

例如：声明员工姓名、员工薪水两个字段，可以用以下的语法
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;

%TYPE是指定我们变量的类型和长度，和表中的一一对应，直接取表中的类型 select into
将我们从表中查出来的数据赋值到我们自己定义的变量 引用型变量的好处：
使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%TYPE是非常好 的编程风格，因为他使得PL/SQL更加灵活，更加适应于对数据库定义的更新。

7、记录型变量
接受表中的一整行记录，相当于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个字段话，如果你使用引用型变量一个个声明，会特别麻	  烦，记录型变量可以方便的解决这个问题。
    记录型变量只能存储一个完整的行数据，只能一行，多行就报错

8、流程控制

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;

9、游标

用于临时存储一个查询返回的多行数据（结果集，类似于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;

10、存储过程

之前我们编写的PLSQL程序可以进行表的操作、判断、循环逻辑处理的工作，但无法重复调用。可以理解之前的代码全部编写在了main方法中，是匿名程序，JAVA可以通过封装对象和方法来解决复用问题。PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用，这些被存储起来的PLSQL程序称之为存储过程

存储过程作用： 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭（连接和关闭时很耗费资源），需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务，可以大大提高效率。

ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误。（如果在数据库中操作数据，可以有一定的日志恢复等功能。）
语法

create or replace procedure   过程名[(参数列表)]  is
        -- 没有使用declare声明变量，但是可以在begin上边直接声明变量
    begin
       -- 执行部分
    end [过程名] ;
   
   is可换as
    根据参数的类型，我们将其分为3类讲解：
        |不带参数
        |带输入参数的
        |但输入输出参数（返回值）的。

创建存储过程
通过Plsql Developer或者语句创建存储过程：

create or replace procedure P_PRINT_HELLO is
      --声明变量
begin
      DBMS_OUTPUT.PUT_LINE('HELLO ');  --打印hello
end P_PRINT_HELLO;  --加上存储过程的名字，结束

调用存储过程

通过PLSQL程序调用（在新建文件夹处，重新打开一个test window）

BEGIN
P_PRINT_HELLO; --调用上面的存储过程
P_PRINT_HELLO;
P_PRINT_HELLO;
END;

带输入参数的存储过程
功能输入员工名字，打印HELLO员工名

--P_PRINT_HELLO过程名字，in代表是输入的参数，后面EMP.ENAME%TYPE是变量V_ENAME的类型，is可换为as

 create or replace procedure P_PRINT_HELLO (V_ENAME IN EMP.ENAME%TYPE)is
      --声明变量
    begin
       DBMS_OUTPUT.PUT_LINE('HELLO '||V_ENAME);  --输出hello和你调用这个过程传入的参数
    end P_PRINT_HELLO;

带输出参数的存储过程
	输入员工名字，将“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;

in参数值使用从
PrepareStatement继承set方法设置的，所有的out参数的类型必须在执行存储过程之前进行注册：它们的值通过这里提供的get方法在执行get
in out参数：双向参数．主程序与过程双向交流数据；

java调用pl/sql

【示例】通过员工号查询员工的姓名和薪资

 package cn.itcast.oracle.jdbc;
    import oracle.jdbc.OracleTypes;
    import java.sql.CallableStatement;import java.sql.Connection;import 	java.sql.DriverManager;
    public class ProcedureTest { 
    public static void main(String[] args) throws Exception {
         //1.加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver"); 
        //2.获得连接对象
        //2.1 设置连接字符串 
        String url ="jdbc:oracle:thin:@localhost:1521:xe" ;
        String name = "scott"; 
        String password = "tiger"; 
        Connection conn = DriverManager.getConnection(url, name, password); 
        //3.获取语句对象 String sql = "{call p_querysal_out(?,?)}";
        //转义语法,{call 存储过程(参数列表)} 
        CallableStatement call = conn.prepareCall(sql); 
        //4.设置输入参数
        call.setInt(1,7839); 
        //5.注册输出参数
        call.registerOutParameter(2, OracleTypes.DOUBLE); 
        //6.执行存储过程
        call.execute(); 
        //7.获取输出参数
        double sal = call.getDouble(2); 
        System.out.println("薪水:"+sal); 
        //8.释放资源
        call.close(); 
        conn.close(); 
    }
    }

例子： 编写一个存储过程，查询员工编号对应的员工名字。如果员工编码查询不出员工名，将引发no_data_found异常，显式一则消息。

create or replace procedure P_PRINT_HELLO (v_no IN emp.eno%TYPE) is
   v_ename emp.ename%TYPE;
begin
  SELECT ename INTO v_ename FROM emp WHERE eno = v_no;
  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 P_PRINT_HELLO；