Oracle使用教程——05Oracle图形化界面的使用

一.安装工具

请参见sqldeveloper安装教程

二.使用

1.建立连接

安装好了以后我们来到下面界面:

接下来我们新建连接：

2.PL/SQL讲解

PL全称是Prcocess Language,PL/SQL是oracle对sql语言的过程化扩展，其在SQL语言中添加了过程处理语句（如分支，循环），使SQL语言具有过程处理能力。
PL/SQL程序结构如下:

Declare
  说明部分 （变量、光标、例外声明）
Begin
  语句序列(DML)...
Exception
  例外处理语句
End;

2.1变量声明

可以声明的变量类型 :char,varchar2,boolean,date,number,long

--开启DBMS_OUTPUT的控制台打印
SET SERVEROUTPUT ON;
--查询empno为7369的员工(使用PL/SQL)
declare --声明变量
  --声明变量
  --声明员工姓名
  p_ename EMP.ENAME%TYPE;--这一句等价于p_ename varchar2(20)
  --声明员工工资
  p_sal EMP.SAL%TYPE;
begin
  --给变量赋值
  SELECT ename,sal INTO p_ename,p_sal FROM emp WHERE empno =7369;
  --在控制台打印信息
  DBMS_OUTPUT.PUT_LINE(p_ename||'的工资是'||p_sal);
end;

执行上面语句:

2.2if语句讲解

先看一个输入的语句:

accept 变量名(自己随便取) prompt 提示信息

上面操作最后将saa赋给了num。
测试代码:

--开启DBMS_OUTPUT的控制台打印
SET SERVEROUTPUT ON;
accept num prompt '请输入数字';
declare --声明变量
  --声明变量
  pnum number:=#--定义变量pnum,将num的值赋给它
begin
  if pnum = 1 then DBMS_OUTPUT.PUT_LINE('你输入了一');
  elsif pnum = 2 then DBMS_OUTPUT.PUT_LINE('你输入了二');
  else DBMS_OUTPUT.PUT_LINE('无效输入');
  end if;
end;

2.3循环讲解

--开启DBMS_OUTPUT的控制台打印
SET SERVEROUTPUT ON;
declare
  --声明变量
  pnum number:=1;
begin
  loop
  exit when pnum>5;
  SYS.DBMS_OUTPUT.PUT_LINE(pnum||' ');
  pnum:=pnum+1;
  end loop;
end;

2.4光标使用讲解

光标又称游标，我们可以认为他是指针，在Oracle里面有隐式光标与显式光标。
测试代码:

--开启DBMS_OUTPUT的控制台打印
SET SERVEROUTPUT ON;
select * from emp;
declare
  --创建光标指向记录
  cursor emp_cur is select ename,sal from emp;
  p_ename EMP.ENAME%TYPE;
  P_sal EMP.SAL%TYPE;
begin
  OPEN emp_cur;--开启光标
  --循环遍历
  loop
  exit when emp_cur%notfound;--当没有数据可查时退出
  fetch emp_cur into p_ename,p_sal;--获取光标数据赋值给P_ename和p_sal
  DBMS_OUTPUT.PUT_LINE(p_ename||'的工资是'||p_sal);
  end loop;
  CLOSE emp_cur;--关闭光标
end;

2.5异常讲解(例外)

系统定义的例外异常有:

No_data_found[没有找到数据]
Too_many_row[select ...into语句匹配多个行]
Zero_Divide[被零除]
Value_error [算术或转换错误]
Timeout_on_resource[在等待资源时发生超时]

1.转换异常

--开启DBMS_OUTPUT的控制台打印
SET SERVEROUTPUT ON;
declare
  pnum number;
begin
  pnum:='b';--转换异常发生
EXCEPTION
  when value_error then DBMS_OUTPUT.PUT_LINE('转换异常');
end;

2.除数不为0异常

--开启DBMS_OUTPUT的控制台打印
SET SERVEROUTPUT ON;
declare
  pnum number;
begin
  pnum:=1/0;
EXCEPTION
  when zero_divide then DBMS_OUTPUT.PUT_LINE('除数不为0异常');
end;

上面演示了两种异常，在不确定异常的时候可以添加下面一句。

when others then DBMS_OUTPUT.PUT_LINE('其它异常');

介绍完了系统定义异常，接下来我们来讲一下自定义异常:

--开启DBMS_OUTPUT的控制台打印
SET SERVEROUTPUT ON;
declare
  cursor emp_cur is select ename,sal from emp;
  p_ename EMP.ENAME%TYPE;
  p_sal EMP.SAL%TYPE;
  nodata exception;--定义异常
begin
  OPEN emp_cur;
  loop
  if emp_cur%notfound then
    raise nodata;--抛出异常
  end if;
  fetch emp_cur into p_ename,p_sal;
  DBMS_OUTPUT.PUT_LINE(p_ename||'的工资是'||p_sal);
  end loop;
  CLOSE emp_cur;
EXCEPTION
  when nodata then DBMS_OUTPUT.PUT_LINE('无数据异常');
  when others then DBMS_OUTPUT.PUT_LINE('其它异常');
end;

3.Oracle存储过程与存储函数

存储在数据库中供所有用户程序调用的子程序叫存储过程和存储函数。

3.1存储过程

创建的语法：

Create [or replace] procedure 过程名（参数） as PLSQL子程序

1.无参数存储过程

执行上面存储过程:
方案1：

set serveroutput on;
execute myPro();

方案2：

set serveroutput on;
begin 
  myPro();
end;

2.有参数存储过程

CREATE OR REPLACE PROCEDURE myPro(p_empno in number) --参数名为empno，类型为number
as
--声明变量
  p_sal EMP.SAL%TYPE;
begin
  select sal into p_sal from emp where empno = p_empno; 
  DBMS_OUTPUT.PUT_LINE('原来的工资:'||p_sal);
  update emp set sal = sal +100 where empno = p_empno;
  DBMS_OUTPUT.PUT_LINE('新的工资:'||(p_sal+100));
end;

execute MYPRO(7369);

注意不要在存储过程中提交事务。
3.存储过程调试
首先我们需要授权给用户允许其调试。




点击上面的小图标调试。

3.2存储函数

存储函数与存储过程类似，但是他必须要有返回值(return),其关键是:

1.函数名
2.参数
3.返回值

创建存储函数的语法:

Create [or replace] function 函数名（参数）return 返回值类型
as
PLSQL子程序

下面来看个查询年薪的例子:
这是创建函数:

--查询员工年薪:月工资*12+奖金
--创建函数queryIncome,他的输入参数是p_empno,类型为number,返回值类型为number
CREATE OR REPLACE FUNCTION queryIncome(p_empno in number) return number
as
  p_sal EMP.SAL%TYPE;
  p_comm EMP.COMM%TYPE;
begin
  select sal,comm into p_sal,p_comm from emp where empno=p_empno;
  return p_sal*12+nvl(p_comm,0);
end;

下面测试函数功能:

SET SERVEROUTPUT ON;
DECLARE
  p_comm_year EMP.COMM%TYPE;
BEGIN
  p_comm_year:=QUERYINCOME(7369);
  DBMS_OUTPUT.PUT_LINE('7369号员工的年薪为:'||p_comm_year);
END;

现在我们需要明白何时使用存储过程，何时使用存储函数:

如果有一个返回值就用存储函数，否则就用存储过程

前面没有演示过存储过程返回多个值，下面这段代码可供参考:

create or replace procedure queryempinfo(eno in number,empname out varchar,empsal out number)
as
  psal emp.sal%type;
begin
  select ename,sal into empname,empsal from emp where empno = eno;
end;

3.3java代码调用存储过程

我们需要第三方包:

链接如下:

链接: https://pan.baidu.com/s/1Qkw2Gk0ZDasZUt_x7FK25Q 密码: yx99

我们创建下面这个存储过程：

create or replace procedure queryempinfo(eno in number,empname out varchar,empsal out number)
as
  psal emp.sal%type;
begin
  select ename,sal into empname,empsal from emp where empno = eno;
end;

java代码为:

import oracle.jdbc.OracleTypes;
import org.junit.Test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Solution {
    @Test
    public void test() throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.OracleDriver");
        String url = "jdbc:oracle:thin:@192.168.100.89:1028:orcl";
        String user = "scott";
        String password = "scott";
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //存储过程的java写法{call <procedure-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{call queryempinfo(?,?,?)}";
        //获取回调类
        CallableStatement call = connection.prepareCall(sql);

        //设置输入参数值
        call.setInt(1,7369);
        //设置返回参数的类型
        call.registerOutParameter(2, OracleTypes.VARCHAR);
        call.registerOutParameter(3,OracleTypes.NUMBER);
        //调用存储过程
        call.execute();
        //获取返回值
        System.out.println("员工的姓名:"+call.getString(2));
        System.out.println("员工的工资:"+call.getDouble(3));
    }
}

3.4java代码调用存储函数

我们紧接着前面的，调用存储函数的java代码为:

import oracle.jdbc.OracleTypes;
import org.junit.Test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Solution {
    @Test
    public void test() throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.OracleDriver");
        String url = "jdbc:oracle:thin:@192.168.100.89:1028:orcl";
        String user = "scott";
        String password = "scott";
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //存储函数的java写法{?=call <funtion-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{? = call queryIncome(?)}";
        //获取回调类
        CallableStatement call = connection.prepareCall(sql);

        //设置参数值
        call.setInt(2,7369);
        //设置返回参数的类型
        call.registerOutParameter(1,OracleTypes.NUMBER);
        //调用存储过程
        call.execute();
        //获取返回值
        System.out.println("员工的年薪:"+call.getDouble(1));
    }
}

4.触发器简介

触发器我们可以理解为监听器，他可以监听数据库的改变，当数据库发生改变的时候就会触发触发器而做一些改变。
触发器分类:

语句级触发器：
	在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。
行级触发器(FOR EACH ROW):
	触发语句作用的每一条记录都被触发。在行级触发器使用:old和:new 伪记录变量
	,识别值的状态

触发器的语法:

CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件)]]
PLSQL 块

1.案例1

create or replace Trigger myTrig 
  after insert 
  on emp
begin 
  DBMS_OUTPUT.PUT_LINE('你插入了东西');
end;

2.案例2

--非工作日时间抛出异常警告
create or replace Trigger myTrig 
  before Insert 
  on emp
begin 
  if to_char(sysdate,'day') in ('saturday','sunday')
  or to_number(to_char(sysdate,'HH24')) not between 9 and 22
  then RAISE_APPLICATION_ERROR(-20000, '你无法在非工作日执行该操作');
  end if;
end;

3.案例3

--降薪报错
create or replace Trigger myTrig 
  before update 
  on emp
  for each row
begin 
  if :new.sal<:old.sal 
  then RAISE_APPLICATION_ERROR(-20000, '坚决不允许降薪');
  end if;
end;

那么我们的Oracle使用教程就讲到这里结束了。如果你认为本系列教程对了=你有帮助，希望可以给我点赞，您的支持就是我的动力。