Oracle使用教程——05Oracle图形化界面的使用-白红宇

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

阅读量：3959 次

发布时间：2019-05-24

本文共 7554 字，大约阅读时间需要 25 分钟。

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

一.安装工具

请参见

二.使用

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，类型为numberas--声明变量  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,返回值类型为numberCREATE OR REPLACE FUNCTION queryIncome(p_empno in number) return numberas  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 
   
    [(
    
     ,
     
      , ...)]}        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 
   
    [(
    
     ,
     
      , ...)]}        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 empbegin   DBMS_OUTPUT.PUT_LINE('你插入了东西');end;

在这里插入图片描述

2.案例2

--非工作日时间抛出异常警告create or replace Trigger myTrig   before Insert   on empbegin   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 rowbegin   if :new.sal<:old.sal   then RAISE_APPLICATION_ERROR(-20000, '坚决不允许降薪');  end if;end;

在这里插入图片描述