PL/SQL

-一、变量

01）引用变量

-- 查询并打印7839的姓名和薪水
declare
   --定义变量保存姓名和薪水
   --pename varchar2(20);
   --psal   number;
   pename emp.ename%type;
   psal   emp.sal%type;
begin
  -- 得到1234的姓名和薪水
  select ename,sal into pename,psal from emp where empno=1234;
  
  --打印
  dbms_output.put_line(pename||‘的薪水是‘||psal);
end;
/

02）记录型变量

-- 查询并打印7839的姓名和薪水
declare
  -- 定义记录型变量：代表一行
  emp_rec emp%rowtype;
begin
  select * into emp_rec from emp where empno=7839;  
 
  dbms_output.put_line(emp_rec.ename||‘的薪水是‘||emp_rec.sal);  
end;
/

03）常量

Var1 constant varchar(13);

-二、语句

01）If语句

-- 判断用户从键盘输入的数字
--接受键盘输入
--num: 地址值，在该地址上保存了输入的值
accept num prompt ‘请输入一个数字:‘;
 
declare
  -- 定义变量保存数字
  pnum number := #
begin
  if pnum = 0 then dbms_output.put_line(‘您输入的是0‘);
     elsif pnum = 1 then dbms_output.put_line(‘您输入的是1‘);
     elsif pnum = 2 then dbms_output.put_line(‘您输入的是2‘);
     else dbms_output.put_line(‘其他数字‘);
  end if;
end;
/

02）循环语句

-- 打印1~10
declare
  pnum number := 1;
begin
  loop
       --循环
       --退出条件
       exit when pnum > 10;
  
       dbms_output.put_line(pnum);
       --加一
       pnum := pnum + 1;
  end loop;
end;
/

-三光标（游标）

（作用类似Java中的数组）

光标的属性：

%isopen

%rowcount(影响的行数)

%found

%notfound

基本操作：

初始化：cursor c1 is select ename from emp;

打开：open c1;

取一行游标的值：fetch c1 into pjob;

关闭：close c1;

结束光标：exist when c1%notfound

-- 查询并打印员工的姓名和薪水
/*
光标的属性：
%isopen   %rowcount(影响的行数)  %found  %notfound
*/
declare
  -- 定义光标
  cursor cemp is select ename,sal from emp;
  pename emp.ename%type;
  psal   emp.sal%type;
begin
  -- 打开光标
  open cemp;
  
  loop
       --取当前记录
       fetch cemp into pename,psal;
       --exit when 没有取到记录;
       exit when cemp%notfound;
  
       dbms_output.put_line(pename||‘的薪水是‘||psal);
  
  end loop;
  
  --关闭光标
  close cemp;
end;
/

-- 涨工资，总裁1000 经理800 其他400
declare
  -- 定义光标
  cursor cemp is select empno,job from emp;
  pempno emp.empno%type;
  pjob   emp.job%type;
begin
  rollback;  
 
 --打开光标
 open cemp;
 
 loop
      --取一个员工
      fetch cemp into pempno,pjob;
      exit when cemp%notfound;
      
      --判断职位
      if pjob = ‘PRESIDENT‘ then update emp set sal=sal+1000 where empno=pempno;
         elsif pjob = ‘MANAGER‘ then update emp set sal=sal+800 where empno=pempno;
         else update emp set sal=sal+400 where empno=pempno;
      end if;
 end loop;
 --关闭光标
 close cemp;
 
 --提交  ---> ACID
 commit;
 
 dbms_output.put_line(‘完成‘);
end;
/

 带参数的光标

-- 查询某个部门的员工姓名
declare
  cursor cemp(dno number) is select ename from emp where deptno=dno;
  pename emp.ename%type;
begin
  open cemp(20);
  loop
       fetch cemp into pename;
       exit when cemp%notfound;
  
       dbms_output.put_line(pename);
  
  end loop;
  close cemp;
end;
/

-四、例外

01.实例一

/*
SQL语句
select to_char(hiredate,‘yyyy‘) from emp;
--->光标 --> 循环 --> 退出条件:notfound
 
变量：1. 初始值  2.最终得到
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
*/
declare
  --入职年份
  cursor cemp is select to_char(hiredate,‘yyyy‘) from emp;
  phiredate varchar2(4);
  
  --每年入职的人数
  count80 number := 0;
  count81 number := 0;
  count82 number := 0;
  count87 number := 0;  
begin
  -- 打开光标
  open cemp;
  loop
       --取一个员工的入职年份
       fetch cemp into phiredate;
       exit when cemp%notfound;
       
       --判断年份
       if phiredate = ‘1980‘ then count80:=count80+1;
          elsif phiredate = ‘1981‘ then count81:=count81+1;
          elsif phiredate = ‘1982‘ then count82:=count82+1;
          else count87:=count87+1;
       end if;
  
  end loop;
  
  --关闭
  close cemp;
  
  --输出
  dbms_output.put_line(‘Total:‘||(count80+count81+count82+count87));
  dbms_output.put_line(‘1980:‘||count80);
  dbms_output.put_line(‘1981:‘||count81);
  dbms_output.put_line(‘1982:‘||count82);
  dbms_output.put_line(‘1987:‘||count87);  
end;
/

02.实例二 

/*
SQL语句
select empno,sal from emp order by sal
---> 光标 --> 循环  --> 退出条件:1. 总额>5w   2. notfound
 
变量：1. 初始值  2.最终得到
涨工资的人数: countEmp number := 0;
涨后的工资总额：salTotal number;
1.select sum(sal) into salTotal from emp;
2.涨后=涨前+sal*0.1
 
练习：涨后的工资总额：50205.325   人数:8
*/
declare
  cursor cemp is select empno,sal from emp order by sal;
  pempno emp.empno%type;
  psal   emp.sal%type;
  --涨工资的人数:
  countEmp number := 0;
  --涨后的工资总额：
  salTotal number;
begin
  -- 得到初始的工资总额
  select sum(sal) into salTotal from emp;
  
  open cemp;
  loop
       --1. 总额>5w
       exit when salTotal > 50000;
       --取一个员工涨工资
       fetch cemp into pempno,psal;
       --2. notfound
       exit when cemp%notfound;
       
       --涨工资
       update emp set sal=sal*1.1 where empno=pempno;
       --人数+1
       countEmp:= countEmp+ 1;
       
       --2.涨后=涨前+sal*0.1
       salTotal := salTotal + psal*0.1;
  
  end loop;
  close cemp;
   commit;
  dbms_output.put_line(‘涨后的工资总额：‘||salTotal||‘   人数:‘||countEmp);
  
end;
/

03.实例三

/*
SQL语句
部门：select deptno from dept;
查部门中的员工薪水: select sal from emp where deptno=??
 
变量：1. 初始值  2.最终得到
每个段的人数
count1 number; count2 number; count3 number;
部门的工资总额
salTotal number;
1. select sum(sal) into salTotal  from emp where deptno=??
2. 累加
*/
declare
  --部门
  cursor cdept is select deptno from dept;
  pdeptno dept.deptno%type;
  
  --部门中员工的薪水
  cursor cemp(dno number) is select sal from emp where deptno=dno;
  psal emp.sal%type;
  
  --每个段的人数
  count1 number; count2 number; count3 number;
  --部门的工资总额
  salTotal number; 
  begin
  open cdept;
  loop
       --取一个部门
       fetch cdept into pdeptno;
       exit when cdept%notfound;
       
       --初始化
       count1:=0; count2:=0; count3:=0;
       --部门的工资总额
       select sum(sal) into salTotal  from emp where deptno=pdeptno;
       
       
       --取部门中员工的薪水
       open cemp(pdeptno);
       loop
            --取一个员工的薪水
            fetch cemp into psal;
            exit when cemp%notfound;
            
            if psal <3000 then count1:=count1+1;
               elsif psal>=3000 and psal<6000 then count2:=count2+1;
               else count3:=count3+1;
            end if;
             end loop;
       close cemp;
       
       --保存结果
       insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));
  
  end loop;
  close cdept;
  
  commit;
  dbms_output.put_line(‘完成‘);
  
end;
/

Oracle对象

-一、存储过程

Java中不能直接访问PL/SQL，只能访问存储过程和存储函数，存储过程和存储函数是由PL/SQL编写的。

存储过程是指存储在数据库中供所有用户程序调用的子程序。

存储函数有return返回值，而存储过程没有。

由于存储过程是一个供用户调用的子程序，所以一般不在存储过程中提交或者回滚数据库，由调用者执行。

01.不带参数的存储过程

/*
第一个存储过程：打印HelloWorld
 
调用存储过程：
1. exec sayHelloWorld();
2. begin
     sayHelloWorld();
     sayHelloWorld();
   end;
   /
*/
create or replace procedure sayHelloWorld
as
begin
  dbms_output.put_line(‘Hello World‘);  
end sayHelloWorld;
/

02.带参数的存储过程

/*
给指定的员工涨100，并且打印涨前和涨后的薪水
*/
create or replace procedure raiseSalary(eno in number)
as
   --定义变量保存涨前的薪水
   psal emp.sal%type;
begin
   --得到涨前的薪水
   select sal into psal from emp where empno=eno;
 
   --涨100
   update emp set sal=sal+100 where empno=eno;
   
   --要不要commit？
 
   dbms_output.put_line(‘涨前：‘||psal||‘  涨后:‘||(psal+100));
end;
/

存储函数是一个PL/SQL程序。函数与过程的结构类似，但必须有一个return子句。

但是过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。

--查询某个员工的年收入
 create or replace function queryEmpIncome(eno in number)
 return number
 as
    --月薪和奖金
    psal emp.sal%type;
    pcomm emp.comm%type;
 begin
    --得到月薪和奖金
    select sal,comm into psal,pcomm from emp where empno=eno;
 
    --返回年收入
    return psal*12+nvl(pcomm,0);
 end;
 /

右击函数-->test

可以通过：dbms_output.put_line(:result);得到数据

SQL> select sal*12+nvl(comm,0) from emp where empno=7839;检查

补：Out参数

存储过程和存储函数不同在于一个有return返回一个没有。

但是存储过程和存储函数都可以通过out指定一个或者多个输出参数。我们可以利用out参数在存储过程和存储函数中返回多个值。

有了out返回之后，存储过程是可以替代存储函数的，但是由于要兼容老版本，所以存储函数还存在。

/*
查询某个员工的姓名 月薪 职位
 
思考：
1. 查询某个员工的所有信息 ---> out参数太多
2. 查询某个部门中的所有员工信息  ---> 返回集合
*/
create or replace procedure queryEmpInformation(eno in number,
                                                pename out varchar2,
                                                psal   out number,
                                                pjob   out varchar2)
as
begin
  
   select ename,sal,job into pename,psal,pjob from emp where empno=eno;                                               
end;
/

-三、包

CREATE OR REPLACE
PACKAGE MYPACKAGE AS
 
  type empcursor is ref cursor;
  procedure queryEmpList(dno in number,empList out empcursor);
 
END MYPACKAGE;

-四、包头

CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS
 
  procedure queryEmpList(dno in number,empList out empcursor) AS
  BEGIN
   
   open empList for select * from emp where deptno=dno;
   
    NULL;
  END queryEmpList;
 
END MYPACKAGE;

-补Java调用Oracle

01.导包：

02.建立类

package com.my.utils;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCUtils {

private static String driver="oracle.jdbc.OracleDriver";
private static String url="jdbc:oracle:thin:@192.168.83.10:1521/orcl";
private static String user="scott";
private static String password="tiger";

static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}

public static Connection getConnection(){
try {
return DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static void release(Connection conn,Statement st,ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
rs=null;
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
st=null;
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
conn=null;
}
}
}
}

package com.my.utils;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import org.junit.Test;
 
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.oracore.OracleType;
 
public class TestOracle {
 
/*
 * 存储过程（out参数）
 * 
查询某个员工的姓名 月薪 职位
思考：
1. 查询某个员工的所有信息 ---> out参数太多
2. 查询某个部门中的所有员工信息  ---> 返回集合
*
create or replace procedure queryEmpInformation(eno in number,
            pename out varchar2,
            psal   out number,
            pjob   out varchar2)
as
begin
   select ename,sal,job into pename,psal,pjob from emp where empno=eno;                                               
end;
/
 * */
@Test
public void testProcedure(){
String sql="{call queryEmpInformation(?,?,?,?)}";

Connection conn=null;
CallableStatement call=null;

try {
conn=JDBCUtils.getConnection();
call=conn.prepareCall(sql);

call.setInt(1, 7839);
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);

call.execute();
String name=call.getString(2);
double sal=call.getDouble(3);
String job=call.getString(4);
System.out.println(name+"\t"+sal+"\t"+job);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.release(conn, call, null);
}
}

/*
 * 存储函数
 *
 * --查询某个员工的年收入
 create or replace function queryEmpIncome(eno in number)
 return number
 as
    --月薪和奖金
    psal emp.sal%type;
    pcomm emp.comm%type;
 begin
    --得到月薪和奖金
    select sal,comm into psal,pcomm from emp where empno=eno;
    --返回年收入
    return psal*12+nvl(pcomm,0);
 end;
 /
 * */
 
 @Test
public void testfunction(){
String sql="{?=call queryEmpIncome(?)}";

Connection conn=null;
CallableStatement call=null;

try {
conn=JDBCUtils.getConnection();
call=conn.prepareCall(sql);

call.registerOutParameter(1, OracleTypes.NUMBER);

call.setInt(2,7839);
call.execute();
double income=call.getDouble(1);
System.out.println(income);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.release(conn, call, null);
}
}

/*
在out中使用光标
2. 查询某个部门中的所有员工信息  ---> 返回集合
包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS
  type empcursor is ref cursor;
  procedure queryEmpList(dno in number,empList out empcursor);
END MYPACKAGE;
包体
CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS
  procedure queryEmpList(dno in number,empList out empcursor) AS
  BEGIN
    open empList for select * from emp where deptno=dno;
  END queryEmpList;
END MYPACKAGE;
*/

@Test
public void testCursor(){
String sql="{call MYPACKAGE.QUERYEMPLIST(?,?)}";

Connection conn=null;
CallableStatement call=null;
ResultSet rs=null;
try {
conn=JDBCUtils.getConnection();
call=conn.prepareCall(sql);

call.setInt(1, 10);

call.registerOutParameter(2,OracleTypes.CURSOR);

call.execute();

rs=((OracleCallableStatement)call).getCursor(2);

while(rs.next()){
String name=rs.getString("ename");
double sal=rs.getDouble("sal");
String job=rs.getString("job");
System.out.println(name+"\t"+sal+"\t"+job);;
}


} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.release(conn, call, null);
}
}
 
}

-五、触发器

触发器是一个与表相关联的，有存储的PL/SQL程序。每当特定的数据操作（insert,uodate,delete）在指定的表发出时，Oracle就会自动的执行触发器中定义的语句序列。

作用：数据确认

实施复杂的安全性检查；

做审计，跟踪表上所作的数据操作等；

数据的备份和同步。

01.语句级触发器

在指定的操作语句操作之前或者之后执行一次，不关这条语句影响了多少行。

/*
触发器应用一: 禁止在非工作时间插入新员工
 
1. 周末: to_char(sysdate,‘day‘) in (‘星期六‘,‘星期日‘)
2. 上班前 下班后：to_number(to_char(sysdate,‘hh24‘)) not between 9 and 17
*/
create or replace trigger securityemp
before insert
on emp
begin
  if to_char(sysdate,‘day‘) in (‘星期六‘,‘星期日‘) or
     to_number(to_char(sysdate,‘hh24‘)) not between 9 and 17  then
     --禁止insert操作
     raise_application_error(-20001,‘禁止在非工作时间插入新员工‘);
  end if;
 
end;
/

验证：SQL>insert into emp(empno,ename,sal,deptno) values(1001,’Tom’,1000,10);

02.行级触发器

触发语句作用的每条记录都被触发，在行级触发器中使用:old和:new伪记录变量，识别值的状态。

/*
触发器应用二：数据的确认
涨后的薪水不能少于涨前的薪水
*/
create or replace trigger checksalary
before update
on emp
for each row
begin
  --if 涨后的薪水 < 涨前的薪水 then
  if :new.sal < :old.sal then
    raise_application_error(-20002,‘涨后的薪水不能少于涨前的薪水.涨前:‘||:old.sal||‘   涨后:‘||:new.sal);
  end if;
end;
/

验证：

SQL>update emp set sal=sal+1 where empno=7839;

SQL>update emp set sal=sal-1 where empno=7839;

本文出自 “qb的博客” 博客，谢绝转载！

PL/SQL和Oracle对象