Oracle数据库-3

1、hello world

set serveroutput on

declare

  --说明部分

begin

  --程序

  dbms_output.put_line('Hello World');

end;

/

2、引用型变量

--查询7839的姓名和薪水

set serveroutput on

declare

  --定义变量保存姓名和薪水

  --pename varchar2(20);

  --psal   number;

  pename emp.ename%type;

  psal   emp.sal%type;

begin

  --得到姓名和薪水

  select ename,sal into pename,psal from emp where empno=7839;

  dbms_output.put_line(pename||'的薪水是'||psal);

end;

/

3、记录型变量

--查询7839的姓名和薪水

set serveroutput on

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;

/

4、if语句

--判断用户从键盘输入的数字

set serveroutput on

--接收键盘输入

--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;

/

5、循环

--打印1~10

set serveroutput on

declare

  pnum number := 1;

begin

  loop

    --退出条件

    exit when pnum > 10;

    dbms_output.put_line(pnum);

    --加一

    pnum := pnum + 1;

  end loop;

end;

/

6、涨工资

--涨工资，总裁1000 经理800 其他400

set serveroutput on

declare

  --alter table "SCOTT"."EMP" rename column "JOB" to empjob

  cursor cemp is select empno,empjob from emp;

  pempno emp.empno%type;

  pjob   emp.empjob%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;

  --why?  ---> ACID

  commit;

  dbms_output.put_line('完成');

end;

/

7、光标

--查询并打印员工的姓名和薪水

/*

1. 光标的属性：

  %isopen     %rowcount (影响的行数)

  %found      %notfound

2. 默认，一个会话中只能打开300个光标

SQL> show parameter cursor

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- -----------

cursor_sharing                       string                           FORCE

cursor_space_for_time                boolean                          FALSE

open_cursors                         integer                          300

session_cached_cursors               integer                          20

修改: alter system set open_cursors=400;

3. (思考):cursor_sharing 什么作用？---> 性能优化

      EXACT(默认), FORCE, SIMILAR

*/

set serveroutput on

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;

/

8、带参数的光标

--查询某个部门的员工姓名

set serveroutput on

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;

/

9、系统例外

--被0除

set serveroutput on

declare

  pnum number;

begin

  pnum := 1/0;

exception

  when zero_divide then dbms_output.put_line('1:0不能做分母');

                         dbms_output.put_line('2:0不能做分母');

  when value_error then dbms_output.put_line('算术或者转换错误');

  when others then dbms_output.put_line('其他例外');

end;

/

10、自定义例外

--查询50号部门的员工姓名

set serveroutput on

declare

  cursor cemp is select ename from emp where deptno=50;

  pename emp.ename%type;

  --自定义例外

  no_emp_found exception;

begin

  open cemp;

  --取第一条记录

  fetch cemp into pename;

  if cemp%notfound then

    --抛出例外

    raise no_emp_found;

  end if;

  --pmon: process monitor

  close cemp;

exception

  when no_emp_found then dbms_output.put_line('没有找到员工');

  when others then dbms_output.put_line('其他例外');

end;

/

11、实例1

/*

SQL语句：

select to_char(hiredate,'yyyy') from emp;

---> 集合 ---> 光标 ---> 循环  ---> 退出条件:notfound

变量：1. 初始值  2. 最终得到

每年入职的员工人数:

count80 number := 0;

count81 number := 0;

count82 number := 0;

count87 number := 0;

*/

set serveroutput on

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;

    --退出条件:notfound

    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;

/

12、实例2

/*

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

练习： 人数:7   总额:50205.325

*/

set serveroutput on

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('人数:'||countEmp||'   总额:'||salTotal);

end;

/

13、实例3

/*

SQL语句：

部门: select deptno from dept;

部门中员工的薪水：select sal from emp where deptno=???

变量：1. 初始值  2. 最终得到

每个段的人数:

count1 number; count2 number; count3 number;

部门的工资总额: salTotal number := 0;

1.select sum(sal) into salTotal from emp where deptno=???

2.累加

*/

set serveroutput on

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 := 0;

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;

/

14、第一个存储过程

--打印Hello World

/*

调用存储过程：

1. exec sayHelloWorld();

2. begin

     sayHelloWorld();

     sayHelloWorld();

   end;

   /

*/

create or replace procedure sayHelloWorld

as

  --说明部分

begin

   dbms_output.put_line('Hello World');

end;

/

15、带参数的存储过程

--给指定的员工涨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;

/

16、存储函数

--查询某个员工的年收入

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;

/

17、out参数

--查询某个员工的姓名 月薪 职位

/*

思考：

1. 查询某个员工的所有信息 ---> out参数太多

2. 查询某个部门中的所有员工信息 ---> 返回集合

*/

create or replace procedure queryempinfo(eno in number,

                                            pename out varchar2,

                                            psal   out number,

                                            pjob   out varchar2)

as

begin

  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;

end;

/

18、JDBCUtils.java

package demo;

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.56.101:1521:orcl";

    private static String user = "scott";

    private static String password = "tiger";

    static{

        try {

            Class.forName(driver);

        } catch (ClassNotFoundException e) {

            throw new ExceptionInInitializerError(e);

        }

        //DriverManager.registerDriver(driver)

    }

    public static Connection getConnection(){

        try {

            return DriverManager.getConnection(url, user, password);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return null;

    }

    /*

     * 运行Java程序

     * java -Xms100M -Xmx200M HelloWorld

     *

     * 技术方向:

     * 1. 性能调优  ---> tomcat

     * 2. 故障诊断  ---> 死锁

     */

    public static void release(Connection conn,Statement st,ResultSet rs){

        if(rs != null){

            try {

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }finally{

                rs = null;// ----> java GC

            }

        }

        if(st != null){

            try {

                st.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }finally{

                st = null;

            }

        }

        if(conn != null){

            try {

                conn.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }finally{

                conn = null;

            }

        }

    }

}

19、在out参数中使用光标

2. 查询某个部门中的所有员工信息 ---> 返回集合

包头

CREATE OR REPLACE PACKAGE MYPAKCAGE AS

  type empcursor is ref cursor;

  procedure queryEmpList(dno in number, empList out empcursor);

END MYPAKCAGE;

包体

CREATE OR REPLACE PACKAGE BODY MYPAKCAGE AS

  procedure queryEmpList(dno in number, empList out empcursor) AS

  BEGIN

    open empList for select * from emp where deptno=dno;

  END queryEmpList;

END MYPAKCAGE;

20、TestOracle

package demo;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.OracleTypes;

import org.junit.Test;

public class TestOracle {

/*

 * create or replace procedure queryempinfo(eno in number,

                                            pename out varchar2,

                                            psal   out number,

                                            pjob   out varchar2)

 */

    @Test

    public void testProcedure(){

        //{call <procedure-name>[(<arg1>,<arg2>, ...)]}

        String sql = "{call queryempinfo(?,?,?,?)}";

        Connection conn = null;

        CallableStatement call = null;

        try {

            conn = JDBCUtils.getConnection();

            call = conn.prepareCall(sql);

            //对于in参数，赋值

            call.setInt(1, 7839);

            //对于out参数，申明

            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 (Exception e) {

            e.printStackTrace();

        }finally{

            JDBCUtils.release(conn, call, null);

        }

    }

/*

 * create or replace function queryempincome(eno in number)

return number

 */

    @Test

    public void testFunction(){

        //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

        String sql = "{?=call queryempincome(?)}";

        Connection conn = null;

        CallableStatement call = null;

        try {

            conn = JDBCUtils.getConnection();

            call = conn.prepareCall(sql);

            //第一个是out参数

            call.registerOutParameter(1, OracleTypes.NUMBER);

            //第二个是in参数

            call.setInt(2, 7839);

            call.execute();

            //取出年收入

            double income = call.getDouble(1);

            System.out.println(income);

        } catch (Exception e) {

            e.printStackTrace();

        }finally{

            JDBCUtils.release(conn, call, null);

        }       

    }

    @Test

    public void testCursor(){

        String sql = "{call MYPAKCAGE.queryEmpList(?,?)}";

        Connection conn = null;

        CallableStatement call = null;

        ResultSet rs = null;

        try {

            conn = JDBCUtils.getConnection();

            call = conn.prepareCall(sql);

            //对于in参数，赋值

            call.setInt(1, 20);

            //对于out参数  申明

            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");

                System.out.println(name+"\t"+sal);

            }

        } catch (Exception e) {

            e.printStackTrace();

        }finally{

            JDBCUtils.release(conn, call, rs);

        }

    }

}

21、第一个触发器

--每当成功插入新员工后，自动打印"成功插入新员工"

create trigger abcd

after insert

on emp

declare

begin

   dbms_output.put_line('成功插入新员工');

end;

/

22、触发器应用

/*

触发器应用一：实施复杂的安全性检查

禁止在非工作时间插入新员工

周末:to_char(sysdate,'day') in ('星期六','星期日')

上班前 下班后：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;

/

23、触发器应用

/*

触发器应用二： 数据的确认

涨后的工资不能少于涨前的工资

*/

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;

/