Oracle数据库之第四篇
/* 授权命令 grant 权限 to 用户 授权可以授予多个权限 grant connect,resource to baidu 收回权限 revoke 权限 from 用户 revoke dba from baidu 创建用户 分配表空间--指定用户的默认表空间 create table p(..) 建表存在默认表空间 --建表时候指定表空间 create table p(...) talebspace 表空间名 */ /* PlSql编程语言 procedure language 过程语言 是在sql语句中加入一些处理过程的语句 常见的条件表达式 if else 还有循环结构 基本结构 declare --声明部分 理解为定义 --声明使用的变量 begin --处理逻辑的代码块 end; */ --psSql简单示例 declare v_n number := 1; --声明数值变量 赋值使用符号:= v_s varchar2(4) :='s'; --声明字符类型变量 emp_ename emp.ename%type ;-- 引用类型变量 emp_row emp%rowtype ;-- 记录类型变量 begin dbms_output.put_line('v_n====='||v_n); --输出语句相当于sys out dbms_output.put_line('v_s====='||v_s); select ename into emp_ename from emp where empno=7499; --使用into关键字赋值 dbms_output.put_line('emp_ename====='||emp_ename); select * into emp_row from emp where empno = 7499; --赋值记录类型变量 dbms_output.put_line('员工编号=='||emp_row.empno||'员工姓名'||emp_row.ename); end; /* plsql 的条件表达式判断 if .. 处理语句 else if ..处理语句 ------------------------- if .. then elsif .. then else end if; */ ---使用条件表达式判断员工的工资 使用汉字输出 declare emp_row emp%rowtype ;-- 记录类型变量 begin select * into emp_row from emp where empno = 7499; --赋值记录类型变量 --使用表达式判断工资 if emp_row.sal > 3000 then dbms_output.put_line('员工工资大于3000=='||emp_row.sal); elsif emp_row.sal < 1000 then dbms_output.put_line('员工工资小于1000=='||emp_row.sal); else dbms_output.put_line('员工工资位于1000到3000之间=='||emp_row.sal); end if; end; /* 循环结构 第一种----- loop exit when 条件 end loop; 第二种 --- while 条件 loop end loop; 第三种 --- for 变量 in 范围 loop end loop; */ -------使用循环输出数字 1-----10 /* 第一种 loop exit when 条件 end loop; */ declare v_n number :=1; begin loop --只是用来判断退出使用的,并不是相当于if()else{} exit when v_n>10 ; --退出条件 dbms_output.put_line(v_n); v_n:=v_n+1; --自增 end loop; end; /* 第二种 while 条件 loop end loop; */ declare v_n number :=1; begin while v_n<11 loop dbms_output.put_line(v_n); v_n:=v_n+1; --自增 end loop; end; /* 第三种 for 变量 in 范围 loop 变量的声明和范围的控制是由for循环自动执行 end loop; */ declare begin for i in 1..10 loop dbms_output.put_line(i); end loop; end; /* 游标 光标 是用于接收查询的记录结果集 ResultSet 提示记录使用.next() 游标的使用步骤 声明游标 cursor 游标名 is select 语句 指定游标的记录结果集 打开游标 open 游标名 提取游标 fetch 游标名 into 记录类型变量 关闭游标 close cursor 游标的两个属性 游标名%found : 判断它有找到 游标名%notfound : 判断它没有找到 if emp_cursor%found then dbms_output.put_line('found'); elsif emp_cursor%notfound then dbms_output.put_line('notfound'); elsif emp_cursor%notfound is null then dbms_output.put_line('null'); end if; */ --使用while循环结构演示游标 declare --声明游标 cursor emp_cursor is select * from emp; --声明记录类型变量 用于接收游标提取的记录 emp_row emp%rowtype; begin --打开游标 open emp_cursor; --提取游标(判断下一个是否有值) fetch emp_cursor into emp_row ; --有值就执行while循环 while emp_cursor%found loop dbms_output.put_line(emp_row.empno||'员工姓名'||emp_row.ename); --继续提取游标(并判断下一个是否有值) fetch emp_cursor into emp_row ; end loop; close emp_cursor; end; /* loop exit when 游标提取不到 end loop */ declare --声明游标 cursor emp_cursor is select * from emp; --声明记录类型变量 用于接收游标提取的记录 emp_row emp%rowtype; begin --打开游标 open emp_cursor; loop fetch emp_cursor into emp_row; exit when emp_cursor%notfound; dbms_output.put_line(emp_row.empno||'员工姓名'||emp_row.ename); end loop; close emp_cursor; end; --使用游标提取某个部门的员工信息 --声明带参数的游标信息 declare --声明游标 cursor emp_cursor(dno number) is select * from emp where deptno = dno ; --声明记录类型变量 用于接收游标提取的记录 emp_row emp%rowtype; begin --打开游标 时候传入参数 open emp_cursor(10); loop fetch emp_cursor into emp_row; exit when emp_cursor%notfound; dbms_output.put_line(emp_row.empno||'员工姓名'||emp_row.ename); end loop; close emp_cursor; end; /* 错误信息开发中的异常 数据库中叫做 例外 异常的分类 1.系统异常 系统定义好的异常 2.自定义的异常 new 自定义类继承Exception 自定义传值（错误代码，提示信息） 使用场景 不满足某些特定业务场景，抛出自定义异常 异常的处理 java try{}catche(IndexOutOfBoundException e){}catche(Exception e){} java try{}catche(Exception e){} catche(IndexOutOfBoundException e){}--报错 数据库可以捕捉处理异常 exception 关键字捕捉异常 when 异常类型 then 处理语句 判断异常类型 处理异常 */ --异常的简单示例 /* --除0的异常 除数为0 --赋值错误 */ declare v_n number :=0; v_m number :=1; begin v_m:='s'; --将字符串赋值给数值变量 v_m:= v_m/v_n; exception when zero_divide then dbms_output.put_line('除数不能为0'); when value_error then dbms_output.put_line('赋值有误'); end; ---处理太多记录数异常 declare emp_row emp%rowtype ;-- 记录类型变量 begin select * into emp_row from emp ; --赋值记录类型 exception when too_many_rows then dbms_output.put_line('太多记录数'); when others then --others是最大范围的异常 相当于java 的 Exception dbms_output.put_line('其他异常'); end; /* 需求 ：使用游标查询部门下的员工信息 如果部门下没有员工 报错提示 需要自定义异常 变量名 exception --声明自定义异常 */ declare cursor emp_cursor is select * from emp where deptno= 40; --游标结果集不存在 emp_row emp%rowtype ;-- 记录类型变量 no_dept_emp exception ; --声明自定义异常 begin open emp_cursor; --打开游标 fetch emp_cursor into emp_row; if emp_cursor%notfound then --没有员工 抛出错误异常 raise no_dept_emp; end if; close emp_cursor; exception when no_dept_emp then dbms_output.put_line('部门下面没人，快招人吧'); end; /* 存储过程 是一段封装好的代码块，过程是编译好放在服务器提供开发人员调用 封装的代码块意义： 提升开发效率 可以复用 谁用直接调用 提升运行效率 一调用直接运行 语法：create [or repalce] procedure 过程名称(参数名 out|in 参数类型) as|is --声明变量的部分 begin --处理过程语句代码块 end; 调用存储过程 在begin和end之间使用 过程名传参调用 */ --存储过程的简单示例 使用存储过程给某个员工增加工资100 create or replace procedure add_sal(eno in number ) as emp_sal number :=0; begin select sal into emp_sal from emp where empno = eno ; dbms_output.put_line('涨工资之前是===='||emp_sal); update emp set sal=sal+100 where empno = eno; select sal into emp_sal from emp where empno = eno ; dbms_output.put_line('涨工资之后是===='||emp_sal); commit; end; --------调用存储过程 declare begin add_sal(7499); end; /* 使用存储过程统计某个员工的年薪，年薪需要返回输出打印 in 类型输入参数可以 省略 默认就是输入参数 */ create or replace procedure count_sal(eno number,year_sal out number) as begin select sal*12+nvl(comm,0) into year_sal from emp where empno=eno; --使用into赋值给输出参数 end; ----调用存储过程计算年薪 declare v_emp_sal number :=0; begin count_sal(7499,v_emp_sal); dbms_output.put_line('年薪为=='||v_emp_sal); end; /* 使用存储过程 查询出某个部门的员工信息 某个部门应该接受一个in类型的输入参数 查询到的部门员工多条记录返回应该使用结果集 声明游标 cursor 游标名 is select 语句指定结果集 系统引用游标 sys_refcursor 声明系统引用游标 变量名 sys_refcursor; --不需要指定结果集 打开游标 open 系统引用游标 for select 语句 --使用for关键字装入数据 */ create or replace procedure dept_emp(dno number,cusor_emp out sys_refcursor) as begin --根据传进来的部门编号给游标装入结果集数据 open cusor_emp for select * from emp where deptno = dno; end; ----调用存储过程查询部门下的员工 declare cursor_emp sys_refcursor; --声明系统引用游标传参使用 emp_row emp%rowtype ;--记录类型变量 begin dept_emp(10,cursor_emp); --提取游标中的数据 loop fetch cursor_emp into emp_row; exit when cursor_emp%notfound; dbms_output.put_line('编号'||emp_row.empno||'姓名'||emp_row.ename); end loop; close cursor_emp; end; /* 存储函数 是一段封装好的代码块，是编译好放在服务器提供开发人员调用 封装的代码块意义： 提升开发效率 可以复用 谁用直接调用 提升运行效率 一调用直接运行 语法：create [or repalce] function 函数名称(参数名 out|in 参数类型) return 数据类型 in 代表传入参数,out 代表传出参数 as|is --声明变量的部分 begin --处理过程语句代码块 --return 变量 end; 调用存储函数 在begin和end之间使用 函数名传参调用 函数必须使用变量接收 返回值 */ --使用存储函数统计某个员工的年薪 create or replace function count_emp_sal(eno number,year_sal out number) return number as v_sal number :=0; begin select sal*12+nvl(comm,0) into year_sal from emp where empno=eno; --使用into赋值给输出参数 return v_sal; end; --不带out类型输出参数统计年薪 create or replace function count_sal_noout(eno number) return number as v_sal number :=0; begin select sal*12+nvl(comm,0) into v_sal from emp where empno=eno; --使用into赋值给输出参数 return v_sal; end; --调用函数统计年薪 declare emp_sal number:=0; total_sal number :=0; begin --total_sal := count_emp_sal(7499,emp_sal); total_sal := count_sal_noout(7499); dbms_output.put_line(emp_sal);--0 dbms_output.put_line(total_sal); --统计后年薪 end; /* 存储函数和过程的区别 1.创建的关键字 procedure funciton 2.创建函数 必须使用return 声明函数的返回变量数据类型 3.在函数的方法体内 必须使用return 返回一个变量 4.函数的调用 必须有变量接收返回值 5.函数可以用在select 查询语句中 select emp.*,count_sal_noout(empno) from emp; 存储函数和过程使用场景 开发规范 java代码待用过程 过程是用来处理业务逻辑代码 如果逻辑中需要用到一些功能性的封装，可以调用函数 90%情况下 函数和过程通用 过程可以调用函数，函数同样可以调用过程 */ /* 触发器 是一个监视器，监视对表中数据的操作 如果对数据的操作满足触发器的执行条件， 触发器会自动运行 触发器语法： create or repalce trigger 触发器名称 after|before --触发器执行时机 insert|update|delete --监视的动作 on 表名 --表级触发器 declare begin end; 行级触发器 insert update delete :new 动作之后的记录 要插入的记录 修改后的记录 空 :old 动作之前的记录 空 原始的记录 原始的记录 */ --创建触发器监视表，如果表中有数据插入，输出一个欢迎语句 create or replace trigger insert_trigger after insert on p declare begin dbms_output.put_line('欢迎加入！'); end; ----插入数据测试效果 insert into p values(1,'zs'); commit; --插入数据不能在休息日插入数据 --休息日 周六和周日 /* raise_application_error(v1,v2) v1错误代码 v2是提示语句 -20000 -20999 */ create or replace trigger insert_no_work before insert on p declare v_day varchar2(10) ; begin --获取到当前星期 select to_char(sysdate,'day') into v_day from dual; --判断星期是否在休息日 if trim(v_day) in ('saturday','sunday') then --如果休息 错误提示 raise_application_error(-20001,'不能休息日插入数据'); end if; end; ----插入数据测试效果 insert into p values(1,'zs'); commit; --使用触发器监视表中数据修改，不能做降低工资的操作 create or replace trigger can_not_low before update on emp for each row --行级触发器 declare begin --获取到原始记录的工资 --获取修改后的工资 if :old.sal > :new.sal then --谈错误框提示 raise_application_error(-20002,'不能降低工资'); end if; end; --修改员工的工资测试触发器 update emp set sal=sal-1 where empno=7499; /* 触发器实际应用 使用触发器实现 插入数据的id 自增长 面试题 **/ create or replace trigger auto_increment_id before insert on test_trigger for each row declare begin --补全将要插入记录的id --补全的id 是自增长的数值 如果没有提前创建序列,需要提前创建序列 --创建序列 create sequence order_sequence select order_sequence.nextval into :new.pid from dual; end; insert into test_trigger(pname,phone) values('zs','1234566'); commit; package baidu;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import org.junit.Test;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;public class TestJdbc { String driverClass = "oracle.jdbc.driver.OracleDriver"; String url ="jdbc:oracle:thin:@192.168.17.128:1521:orcl"; String user= "baidu_03"; String password = "baidu_03"; /* *测试jdbc连接数据库 * * */ @Test public void querEmp(){ try{ //加载驱动 Class.forName(driverClass); //获取链接 Connection con = DriverManager.getConnection(url, user,password); //获取预编译的statement PreparedStatement pst= con.prepareStatement("select * from emp"); //执行查询 ResultSet rs = pst.executeQuery(); //处理结果 while(rs.next()){ System.out.println(rs.getInt(1)+"员工姓名"+rs.getString("ename")); } rs.close(); con.close(); //关闭连接 }catch(Exception e){ e.printStackTrace(); } } /*存储过程的调用 * {call <procedure-name>[(<arg1>,<arg2>, ...)]} add_sal(eno number,addsal number) * */ @Test public void callAddSal(){ try{ //加载驱动 Class.forName(driverClass); //获取链接 Connection con = DriverManager.getConnection(url, user,password); //获取预编译的statement CallableStatement pst= con.prepareCall("{call add_sal(?,?)}"); pst.setInt(1, 7499); pst.setInt(2, 1000); //执行查询 pst.execute(); con.close(); //关闭连接 }catch(Exception e){ e.printStackTrace(); } } /*存储过程的调用 * {call <procedure-name>[(<arg1>,<arg2>, ...)]} count_yearsal(eno number,total_year_sal out number) * */ @Test public void callCountSal(){ try{ //加载驱动 Class.forName(driverClass); //获取链接 Connection con = DriverManager.getConnection(url, user,password); //获取预编译的statement CallableStatement pst= con.prepareCall("{call count_yearsal(?,?)}"); pst.setInt(1, 7499); //注册输出参数 pst.registerOutParameter(2, OracleTypes.NUMBER); //执行查询 pst.execute(); int total = pst.getInt(2); System.out.println(total); con.close(); //关闭连接 }catch(Exception e){ e.printStackTrace(); } } /* * pro_dept_emp(dno number,dept_emp out sys_refcursor) * */ @Test public void callProEmp(){ try{ //加载驱动 Class.forName(driverClass); //获取链接 Connection con = DriverManager.getConnection(url, user,password); //获取预编译的statement CallableStatement pst= con.prepareCall("{call pro_dept_emp(?,?)}"); pst.setInt(1, 10); //注册输出参数 pst.registerOutParameter(2, OracleTypes.CURSOR); //执行查询 pst.execute(); OracleCallableStatement ocs = (OracleCallableStatement)pst; ResultSet rs = ocs.getCursor(2); while(rs.next()){ System.out.println(rs.getInt(1)+"员工姓名"+rs.getString("ename")); } rs.close(); ocs.close(); pst.close(); con.close(); //关闭连接 }catch(Exception e){ e.printStackTrace(); } }}public void show4(){ try { Class.forName(driverClass); Connection con = DriverManager.getConnection(url, user,password); CallableStatement pst= con.prepareCall("{?= call count_sal_noout(?)}"); //给第二个参数赋值 pst.setLong(2, 7499); // stat2.setLong(2, empno); //声明第一个参数的类型 pst.registerOutParameter(1, OracleTypes.NUMBER); pst.execute(); OracleCallableStatement ocs = (OracleCallableStatement)pst; NUMBER num = ocs.getNUMBER(1); System.out.println(num);// long i = pst.getLong(1);// System.out.println(i); con.close(); } catch (Exception e) { e.printStackTrace(); } }/*
赞 (0)