Oracle学习笔记之PL/SQL编程

视频学习Oracle记录

========================================
六、 PL/SQL编程——博大而精深
========================================

 pl/sql是在Oracle上的sql语言上的扩展，pl/sql不仅允许嵌入sql语言，还可以定义变量常量，允许使用条件语句和循环语句，允许使用例外处理各种错误。
 例子：PL/SQL
 建表：create table mytest(name varchar2(30),passwd varchar2(30));
 创建过程：
  create or repalce procedure sp_pro1 is --sp_pro1过程名如果存在，replace 替换
  begin --执行部分
  insert into mytest values('ll','m1234');
  end;
  /
  show error;--可以查看出现的错误
 调用过程
 a、exec 过程名(arg1,arg2,...)
 b、call 过程名(arg1,arg2,...)
 
 PL/SQL编写规范
 注释：单行：-- 多行：/*....*/
 PL/SQL 块--PL/SQL的基本单元
 块有三部分构成：定义、执行、例外
  declare
  /*定义部分(可选)----定义常量、变量、游标 、例外、复杂数据类型*/
  begin
  /*执行部分(必要)----要执行的PL/SQL语句和SQL语句*/
  exception
  /*例外处理部分(可选)----处理运行的各种错误*/
  end;
 
 例子：只包含执行部分的PL/SQL
 set serveroutput on --打开输出选项
 begin
  dbms_output.put_line('hello');--dbms_output是Oracle提供的包，put_line()为函数
 end;
 例子：包含定义部分和执行部分的PL/SQL
 declare
     v_ename varchar2(5);--定义字符串变量
 begin
     select ename into v_ename from emp where empno=&no;--&取地址符，会提示从命令行输入
     dbms_output.put_line('雇员名：'||v_ename);--控制台显示用户名
 end;

 例子：包含定义部分和执行部分以及例外处理部分的PL/SQL
 declare
     v_ename varchar2(5);--定义字符串变量
 begin
     select ename into v_ename from emp where empno=&no;--&取地址符，会提示从命令行输入
     dbms_output.put_line('雇员名：'||v_ename);--控制台显示用户名
 exception
  when no_data_found then
    dbms_output.put_line('没有发现数据！');
 end;

 例子：PL/SQLc传参的存储过程(前面几个例子是块，下面这个例子为存储过程)
 create procedure sp_pro3(spName varchar2, newSal number) is
 begin
 /*执行部分(略)*/
 end;
 
 Java调用存储过程
 import java.sql.*;
 
 /**
  * Created by floyd on 2014/6/28.
  */
 public class OraclecomJava {
      public  static  void main(String[] arg){

          try {
               //1.加载驱动
               Class.forName("oracle.jdbc.driver.OracleDriver");
               //2.得到连接
               Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521databasename:t_table","scott","tiger");
 
               //3.创建一个CallableStatement
               CallableStatement cs = ct.prepareCall("{call sp_pro(?,?)}");
 
               //4.给？赋值
              cs.setString(1,"name");
              cs.setInt(2,10);
               //5.执行
               cs.execute();

               //关闭
               cs.close();
               ct.close();
          } catch (Exception e){
               e.printStackTrace();
           }
      }
 }

 函数的案例
  1、函数创建
   --输入雇员的姓名，返回该雇员的年薪
  create function sp_fun(spName varchar2) return number is yearSal number(7,2);--函数头
  begin
   /*执行部分*/
  select sal*12+nvl(comm,0)* 12 into yearSal from emp where ename=spName;
  return yearSal;
  end;
  2、函数调用
  命令行执行：
  sql> var income number;
  sql> call sp_fun('SMITH') into: income;
  sql> print income
  Java程序中调用
   select sp_fun('SMITH') from emp
   rs.getInt(1)得到返回结果
 包的使用
  ---创建一个包，声明该包有一个过程和一个函数
  create package sp_package is
   procedure update_sal(name varchar2, new sal number);
   function sp_fun(name varchar2) return number;
  end;
  实现包中的过程和函数,即包体：
  create package body sp_package is
   procedure update_sal(name varchar2, new sal number)is
   begin
    update emp set sal=newsal where ename=name
   end;
   function sp_fun(name varchar2) return number is ysal number;
   begin
    select sal*12+nvl(comm,0)* 12 into yearSal from emp where ename=name;
   return ysal;
   end;
  end;
 
 触发器
 
 
 变量
 标量scalar
 定义一个边长字符串
 v_ename varchar2(10)
 v_sal number(6,2)
 赋值号：v_sal number(6,2):=5.4
 v_date date
 定义布尔变量 v_valid boolean not null default false;

 例子：输入员工号显示雇员姓名、工资、个人所得税
 --税率为0.03
 declare
 c_tax_rate number(3,2) := 0.03;
 --v_ename varchar2(5);
 v_ename emp.ename%type;--使用%type来定义v_ename变量
 v_sal number(7,2);
 v_tax_sal number(7,2);
 begin
  --执行
  select ename,sal into v_ename,v_sal from emp where empno=&no;
  --计算所得税
  v_tax_sal := v_sal*v_tax_rate;
  --输出
  dbms_output.put_line('姓名：'||v_ename||'工资'||v_sal||'个人所得税' || v_tax_sal)
 end;
 
 复合变量（相当于高级语言中的结构体）composite
 ---PL/SQL记录实例
 declare
 --定义一个PL/SQL记录类型 emp_record_type,类型包含三个数据
 type emp_record_type is record(name emp.ename%type,salary emp.sal%type,tile emp.job%type);
 --定义了一个变量sp_record，类型为emp_record_type
 sp_record emp_record_type;
 begin
  select ename,sal,job into sp_record from emp where empno=&no;
  dbms_output.put_line('姓名：'||sp_record.name);
 end;
 复合类型（相当于高级语言中的数组）composite
 ---PL/SQL记录实例
 declare
 --定义一个PL/SQL表类型 sp_table_type
 type sp_table_type is table of emp.ename%type index by binary_integer;
 --定义了一个变量sp_table，类型为sp_table_type
 sp_table sp_table_type;
 begin
  select ename into sp_table(0) from emp where empno=&no;
  dbms_output.put_line('姓名：'||sp_table(0));
 end;

 ---使用PL/SQL写一个块
 declare
 --定义游标类型 sp_emp_cursor
 test_cursor sp_emp_cursor;
 --定义变量
 v_ename emp.ename%type
 v_sal emp.sal%type;
 begin
 ---执行
 --把 test_cursor 和 select 结合
 open test_cursor for select ename,sal from emp where deptno=&no;

 loop
  fetch test_cursor into v_ename,v_sal;
  --判断是否test_cursor为空
  exit when test_cursor%notfund;
  dbms_output.put_line('姓名：'||v_ename||'工资'||v_sal);
 end loop;
 end;
 /