Oracle学习笔记0.4

阅读数: 次 2021-08-25

文章导航

oracle学习笔记

🌕 🌕 🌕

存储过程，函数，视图 , 包…

子程序

一.什么是子程序

1). 子程序是指被命名的PL/SQL块，这种块可以有参数，可以在不同应用中多次调用。
2). 子程序是指被命名的PL/SQL块，这种块可以带有参数，可以在不同应用中多次调用
3). PL/SQL有两种类型的子程序：过程和函数

存储过程

一.存储过程特点

存储过程（Stored Procedure ）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指
定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
存储过程是由流程控制和SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。在ORACLE 中，若干个有联系的过程可以
组合在一起构成程序包。

二.存储过程优点：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一
次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行CRUD时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。减少与java的交互
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某用户才具有对指定存储过程的使用权。

三.存储过程基本语法结构

-- 请写出存储过程基本语法结构
CREATE [OR REPLACE 如果创建已经存在则替换,否则新建] PROCEDURE 存储过程名(
 参数列表: 变量名 [参数类型:IN,OUT,IN out] 数据类型,
)
AS
  定义参数
BEGIN
  代码
EXCEPTION
  异常
END;

四.Oracle中的几种存储过程

无参不能有小括号，有参必须加括号
括号中参数不能有长度
IN可以省略，OUT可以赋值
IN OUT: 可输入，可输出

1).无参存储过程

--创建无参存储过程:输出7499号的 部门编号
create or replace procedure getdeptno_1
is
  v_deptno emp.deptno%type;
begin
  select deptno into v_deptno from emp where empno=7499;
  dbms_output.put_line('部门编号为'||v_deptno);
end;

----使用匿名代码块调用
begin
  getdeptno();--()可有可无
end;
--使用call调用，最常见
call getdeptno();--必须加括号
--这种方式是在plsql的命令窗口去执行
set serveroutput on; --开始控制台输出
Exec getdeptno_1; --再用exec去执行

2).入参存储过程

--创建入参存储过程:输入员工编号，输出部门编号
create or replace procedure getdeptno_2(
  v_empno number
)--默认参数为输入参数，输入参数不规定长度
AS
  v_deptno emp.deptno%type;
begin
  select deptno into v_deptno from emp where empno=v_empno;
  dbms_output.put_line('部门编号为'||v_deptno);
end;
--使用匿名代码块调用
declare
  v_empno emp.empno%type:=&e;
begin
  getdeptno1(v_empno);
end;

3).出参存储过程

--创建出参存储过程:入参员工编号，出参部门编号
create or replace procedure getdeptno_3(
  v_empno number,v_deptno out number
)--默认参数为输入参数，参数不规定长度
as
begin
  select deptno into v_deptno from emp where empno=v_empno;
end;
--使用匿名代码块调用
declare
  v_empno emp.empno%type:=&e;
  v_deptno emp.deptno%type;
begin
  getdeptno2(v_empno,v_deptno);
  dbms_output.put_line('部门编号为'||v_deptno);
end;

4).出入参存储过程

--创建存储过程:入参员工编号，出参部门编号,v_param作为出入参
create or replace procedure getdeptno_3(
  v_param in out number
)--默认参数为输入参数，参数不规定长度
is
begin
  select deptno into v_param from emp where empno=v_param;
end;
--使用匿名代码块调用
declare
  v_param number(7):=&e;
begin
  getdeptno3(v_param);
  dbms_output.put_line('部门编号为'||v_param);
end;

五.多参数传递时的传递方式

位置传递,参数名传递,组合传递

存储过程，函数都可用
位置传参必须在名字传参前面

--参数传递形式
create or replace procedure insertDept(
v_deptno number,v_dname varchar2,v_loc varchar2
)
is
begin
insert into dept values(v_deptno,v_dname,v_loc);
end;
--参数传递；位置
declare
  v_no number(10):=70;
  v_name varchar2(200):='阿里';
  v_loc varchar2(200):='北京';
begin
  insertDept(v_no,v_name,v_loc);
end;
--参数传递:名字
declare
  v_no number(10):=72;
  v_name varchar2(200):='阿里';
  v_loc varchar2(200):='北京';
begin
--等号前面是参数名，参数位置随意，参数值由等号右边决定
  insertDept(v_deptno=>v_no,v_loc=>v_loc,v_dname=>v_name);
end;
--参数传递：混合传参
declare
  v_no number(10):=73;
  v_name varchar2(200):='阿里';
  v_loc varchar2(200):='北京';
begin
--混合传参（名字传参后不能有位置传参）
  insertDept(v_no,v_loc=>v_loc,v_dname=>v_name);
end;

函数

一.函数的特点

函数用于返回特定数据（函数最大的特点），一定有返回值。
如果在应用程序中经常需要通过执行SQL语句来返回特定数据，
则可以基于这些操作创建特定的函数。

二.函数基本语法结构

-- 请写出函数基本语法结构
CREATE [OR REPLACE] FUNCTION 函数名(参数名 [IN OUT] 数据类型,.....)
RETURN 返回值类型
AS
  声明
BEGIN
  代码执行
EXCEPTION
  异常
END;

三.Oracle中的几种函数

1).无参函数

select ceil(4.2) from dual--ceil取整
--获取一个随机数，并取整
--无参:1不加(),2返回值不加长度
create or replace function getRound 
return number
is
  v_no number(10);
begin
--ceil向上取整，dbms_random.value()获取小于1的随机小数
  v_no:=ceil(dbms_random.value()*100);
  return v_no;
end;
--函数调用
select getRound() from dual;
--在代码块中调用
DECLARE
  v_return NUMBER(10);
BEGIN
  v_return:=getround();
  dbms_output.put_line('随机数:'||v_return);
END;

2).入参函数

--入参函数：输入员工编号，返回部门名称
create or replace function fn_deptName(v_empno number)--参数类型不加长度
return varchar2
is
  v_dname dept.dname%type;
begin
  select dname into v_dname from emp,dept where emp.empno=v_empno and emp.deptno=dept.deptno;
  return v_dname;
end;
--函数调用1
select fun_deptName(7788) from dual;
--函数调用2
declare
  v_empno emp.empno%type:=&e;
  v_dname dept.dname%type;
begin
  v_dname:=fun_deptName(v_empno);
  dbms_output.put_line(v_dname);
end;

3).入参,出参函数

--出参函数，两个参数函数：输入员工编号，返回部门名称，部门地址
create or replace function fn_deptName(v_empno number,v_loc out varchar2)
return varchar2
is
  v_dname dept.dname%type;
begin
  select dname,loc into v_dname,v_loc from emp,dept where emp.empno=v_empno and
  emp.deptno=dept.deptno;
  return v_dname;
end;
--函数调用
declare
  v_empno emp.empno%type:=&e;
  v_dname dept.dname%type;
  v_loc dept.loc%type;
begin
  v_dname:=fun_deptName(v_empno,v_loc);
  dbms_output.put_line(v_dname||v_loc);
end;

4).输入输出参数

--输入输出参数
CREATE OR REPLACE FUNCTION fn_b(pram IN OUT VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
  SELECT MOD(SUBSTR(pram,17,1),2) --pram输入参数
  INTO pram FROM dual; --pram输出参数

  IF pram = 0 THEN
    RETURN '女';
  ELSE
    RETURN '男';
  END IF;
END;

DECLARE
  idcard VARCHAR2(50):='410303202003207846';
  sexstr VARCHAR2(20);
BEGIN
  dbms_output.put_line('调用前:'||idcard);
  sexstr:=fn_b(idcard);
  dbms_output.put_line('调用后:'||idcard);
  dbms_output.put_line('性别:'||sexstr);
END;

四.函数和存储过程的区别

存储过程：执行特定操作
函数：返回特定数据
不同点：1.调用方式不同；2.函数一定有返回结果，过程没有返回结果
相同点：1.都包含输入和输出参数

过程和函数的比较：

Ø 相同的功能及特性
1、都使用IN模式的参数传入数据、OUT模式的参数返回数据。
2、输入参数都可以接收默认值，都可以传值。
3、调用时的实参都可以使用位置表示法或名称表示法。
4、都有声明部分、执行部分和异常处理部分。
Ø 用法区别
1、一般而言，如果需要返回多个值或不返回值，就使用过程。
2、如果只需要返回一个值，就使用函数。
3、虽然函数带OUT模式的参数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格。
4、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

视图

一.视图的特点

视图：是个虚表,查询结果使用频率较高时,可以将查询结果定为一个视图

二.视图的基本语法结构

1
2
3

CREATE OR REPLACE VIEW v_a(视图名)
AS
查询语句

三.视图的种类

1).单表视图

--例：将员工编号，姓名定义为一张视图
CREATE OR REPLACE VIEW v_a
AS
SELECT empno,ename FROM emp

--视图可以进行增删改:操作的是原表，操作的数据必须在原表中有唯一的记录
INSERT INTO v_a VALUES(0001,'张三');
DELETE FROM v_a WHERE cid<1000;
UPDATE v_a SET ename='张良' WHERE cid=0001;

2).联表视图

--连表视图
CREATE OR REPLACE VIEW v_b
AS
SELECT e.empno,e.ename,d.dname,d.deptno FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

SELECT * FROM v_b;

--修改时dname:dept deptno:dept
--最终修改的表是dept
UPDATE v_b SET dname='11' WHERE deptno=1;

包

一.包的特点

包就是组合一系列逻辑操作，将函数，存储过程等对象组织起来，为了方便代码程序的管理。
包规范：接口，定义
包体：实现类，实现包规范中定义的内容
创建包时，需要先创建包头,再创建包体

包的特点：
Ø 包（Package）用于组合逻辑相关的PL/SQL类型、PL/SQL项和PL/SQL子程序。
Ø 通过使用PL/SQL包，不仅可以简化应用设计，提高应用性能，还可以实现信息隐藏、子程序重载等功能。
Ø 包由包规范和包体两部分组成。
Ø 当创建包时，需要首先创建包规范，然后再创建包体。(包规范类似接口，包体类似接口的实现)
在包里声明的是共有的，在包体里声明的都是私有的，私有的只能在包中使用，共有的可以在外部使用(包名.)

二.包的基本语法结构

包体必须实现包规范中定义的内容；
包体中可以直接使用包规范中定义的变量/常量；
包规范中定义的内容是公开的，可以包体中或外部进行调用
包体中可以包含变量，常量，函数，过程，pl/sql，游标，私有的，外部不能访问
包体中可以相互调用，调用包体中定义的私有的内容必须在调用前先声明；

-- 请写出创建包规范和包体的基本语法
--创建包头
CREATE OR REPLACE PACKAGE 包名
IS
声明(变量，存储，函数)--公有
END 包名;
--创建包体
CREATE OR REPLACE PACKAGE body 包名
IS
声明(变量)--私有
声明实现新的(包里没有声明的存储过程,函数)--私有
实现包中定义的存储过程和函数。
END 包名;

三.包的几种用法

1).共有属性

--调整部门30的员工工资为最高工资 写一个存储过程调整薪资，写一个函数获取最高工资
--包头(声明)
CREATE OR REPLACE PACKAGE changeSal
IS
  --这是一个共有变量
  v_deptno NUMBER(10):=30;
  --共有的存储过程
  PROCEDURE proc_changeSal(v_max NUMBER);
  --共有函数
  FUNCTION func_getMax RETURN NUMBER;
END changeSal;
--包体(实现)
CREATE OR REPLACE PACKAGE BODY changeSal
IS
  --共有的存储过程实现
  PROCEDURE proc_changeSal(v_max NUMBER)
  IS
  BEGIN
    --共有变量，私有变量都可以直接使用
    UPDATE emp SET sal=v_max WHERE deptno=v_deptno;
  END;
  --共有函数实现
  FUNCTION func_getMax RETURN NUMBER
  IS
    v_maxsal NUMBER(10,2);
  BEGIN
    SELECT MAX(sal) INTO v_maxsal FROM emp;
    RETURN v_maxsal;
  END;
END changeSal;
--包内可以直接调用(包名.)
--包外调用
DECLARE
  v_max NUMBER(10,2);
BEGIN
  --调用包中函数
  v_max:=changeSal.func_getMax;
  dbms_output.put_line(v_max);
  --调用包中存储过程
  changeSal.proc_changeSal(v_max);
END;

DROP PACKAGE BODY changeSal;--删除包体时，包规范不会删
DROP PACKAGE changeSal;--删除包规范时，对应包体同步删除

2).私有属性

--私有属性只能在本包调用
create or replace package changeSal
is
  --已知部门编号20
  v_deptno dept.deptno%type:=20;
  --修改工资
  procedure proc_changeSal;
end changeSal;

create or replace package body changeSal
is
  --私有变量
  v_maxSal emp.sal%type;
  --包头中未声明，此处为私有存储过程，获取最高薪资
  procedure fun_getMax
  is
  begin
    select max(sal) into v_maxSal from emp;
  end;
  --修改工资
  procedure proc_changeSal
  is
  begin
    fun_getMax;--调用私有函数
    update emp set sal=v_maxSal where deptno=v_deptno;
  end;
end changeSal;
--包外调用，通过包名.
declare
begin
  changeSal.proc_changeSal;
end;
select * from emp where deptno=20;

练习一

/*练习一*/
--创建一个存储过程，功能是添加员工，添加时只添加员工编号、员工姓名、工资、部门编号
CREATE OR REPLACE PROCEDURE setemp(
  v_empno NUMBER,v_ename VARCHAR2,v_sal NUMBER,v_deptno NUMBER
)
AS
BEGIN
  INSERT INTO emp(empno,ename,sal,deptno)valuse(v_empno,v_ename,v_sal,v_deptno);
END;
--创建一个存储过程，功能是添加部门，两个参数部门编号和部门名称
CREATE OR REPLACE PROCEDURE setdept(
  v_deptno NUMBER,v_dname VARCHAR2
)
AS
BEGIN
  INSERT INTO dept(deptno,dname)valuse(v_deptno,v_dname);
END;
--创建一个函数，功能是添加员工，添加时只添加员工编号、员工姓名、工资、部门编号
CREATE OR REPLACE FUNCTION fn_setemp(
  v_empno NUMBER,v_ename VARCHAR2,v_sal NUMBER,v_deptno NUMBER
)
RETURN VARCHAR2
AS
BEGIN
  INSERT INTO emp(empno,ename,sal,deptno)valuse(v_empno,v_ename,v_sal,v_deptno);
  IF SQL%ROWCOUNT<1 THEN
    RETURN '添加失败';
  ELSE
    RETURN '添加成功';
  END IF;
END;
--创建一个函数，功能是添加部门，只添加部门编号和部门名称
CREATE OR REPLACE FUNCTION fn_setdept(
  v_deptno NUMBER,v_dname VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
  INSERT INTO dept(deptno,dname)valuse(v_deptno,v_dname);
  IF SQL%ROWCOUNT<1 THEN
    RETURN '添加失败';
  ELSE
    RETURN '添加成功';
  END IF;
END;
DECLARE
  v_result VARCHAR2(20);
BEGIN
  v_result:=fn_setdept(11,'学习');
  dbms_output.put_line(v_result);
END;
--写一个函数，功能是根据员工编号查找员工部门名称
CREATE OR REPLACE FUNCTION fn_getdname(
  v_empno NUMBER
)
RETURN VARCHAR2
AS
v_dname dept.dname%TYPE;
BEGIN
  SELECT dept.dname INTO v_dname FROM emp,dept WHERE emp.empno=v_empno AND emp.deptno=dept.deptno;
  RETURN v_dname;
END;
SELECT getdname(7499) FROM dual;
--写一个函数，功能是根据部门编号，查找部门最高工资
CREATE OR REPLACE FUNCTION fn_getmaxsal(
  v_deptno NUMBER
)
RETURN NUMBER
AS
v_Msal emp.sal%TYPE;
BEGIN
  SELECT MAX(sal) INTO v_Msal FROM emp WHERE emp.deptno=v_deptno;
  RETURN v_Msal;
END;
SELECT fn_getmaxsal(10) FROM dual;
--写一个函数，功能是根据员工编号，查找部门最高工资
CREATE OR REPLACE FUNCTION fn_getmaxsal(
  v_empno NUMBER
)
RETURN NUMBER
AS
v_deptno emp.deptno%TYPE;
v_Msal emp.sal%TYPE;
BEGIN
  SELECT deptno INTO v_deptno FROM emp WHERE emp.empno=v_empno;
  SELECT MAX(sal) INTO v_Msal FROM emp WHERE emp.deptno=v_deptno;
  RETURN v_Msal;
END;
SELECT fn_getmaxsal(7499) FROM dual;
--写一个函数，功能是根据员工标号，查找部门人员数量
CREATE OR REPLACE FUNCTION fn_getempc(
  v_empno NUMBER
)
RETURN NUMBER
AS
v_deptno emp.deptno%TYPE;
v_count NUMBER;
BEGIN
  SELECT deptno INTO v_deptno FROM emp WHERE emp.empno=v_empno;
  SELECT COUNT(*) INTO v_count FROM emp WHERE emp.deptno=v_deptno;
  RETURN v_count;
END;
SELECT fn_getempc(7788) FROM dual;

练习二

/*练习二*/
--创建一个存储过程，以员工号为参数，输出该员工的工资
CREATE OR REPLACE PROCEDURE getempsal(
  v_empno NUMBER,v_empsal OUT emp.sal%TYPE
)
AS
BEGIN
  SELECT sal INTO v_empsal FROM emp WHERE emp.empno=v_empno;
END;
--代码块调用
DECLARE
  v_sal emp.sal%TYPE;
BEGIN
  getempsal(7499,v_sal);
  dbms_output.put_line('工资：'||v_sal);
END;
--函数
CREATE OR REPLACE FUNCTION fn_getempsal(
  v_empno NUMBER
)
RETURN NUMBER
AS
  v_empsal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_empsal FROM emp WHERE emp.empno=v_empno;
  RETURN v_empsal;
END;
--调用虚表查看
SELECT fn_getempsal(7499) FROM dual;

/*
创建一个存储过程，以员工号为参数，修改该员工的工资。
若该员工属于10号部门， 则工资增加150；
若属于20号部门，则工资增加200；
若属于30号部门，则工资增加250； 
若属于其他部门，则增加300。
*/
CREATE OR REPLACE PROCEDURE updatesal(
  v_empno NUMBER
)
AS
  v_deptno emp.deptno%TYPE;
BEGIN
  SELECT deptno INTO v_deptno FROM emp WHERE emp.empno=v_empno;
  CASE v_deptno
    WHEN 10 THEN
      UPDATE emp SET sal=sal+150 WHERE empno=v_empno;
    WHEN 20 THEN
      UPDATE emp SET sal=sal+200 WHERE empno=v_empno;      
    WHEN 30 THEN
      UPDATE emp SET sal=sal+250 WHERE empno=v_empno;      
    ELSE
      UPDATE emp SET sal=sal+300 WHERE empno=v_empno;      
  END CASE;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('未找到员工!');
END;
--代码块调用
DECLARE
  v_empno emp.empno%TYPE:=&sID;
BEGIN
  updatesal(v_empno);
END;
SELECT * FROM emp WHERE empno=7499;

--创建一个存储过程，以员工号为参数，返回该员工的工作年限（以参数形式返回）。
CREATE OR REPLACE PROCEDURE getyear(
  v_empno NUMBER,v_year OUT NUMBER
)
AS
  v_date emp.hiredate%TYPE;
BEGIN
  SELECT hiredate INTO v_date FROM emp WHERE empno=v_empno;
  v_year:=(SYSDATE-v_date)/365;
END;
--代码块调用
DECLARE
  v_empno emp.empno%TYPE:=&eid;
  v_year NUMBER(4);
BEGIN
  getyear(v_empno,v_year);
  dbms_output.put_line(v_empno||'的工作年限是：'||v_year);
END;

--创建一个函数，以员工号为参数，返回该员工的工资。
CREATE OR REPLACE FUNCTION fn_getsal(
  v_empno NUMBER
)
RETURN NUMBER
AS
  v_empsal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_empsal FROM emp WHERE empno=v_empno;
  RETURN v_empsal;
END;
--代码块调用
DECLARE
  v_empno emp.empno%TYPE:=&eid;
BEGIN
  dbms_output.put_line('工资是'||fn_getsal(v_empno));
END;

--创建一个函数，以部门号为参数，返回该部门的平均工资。
CREATE OR replace FUNCTION fn_avgsal(
  v_deptno NUMBER
)
RETURN NUMBER
AS
  v_sal emp.sal%TYPE;
BEGIN
  SELECT trunc(AVG(sal),2) INTO v_sal FROM emp WHERE deptno=v_deptno;
  RETURN v_sal;
END;
--代码块调用
DECLARE
  v_deptno emp.deptno%TYPE:=&did;
BEGIN
    dbms_output.put_line('部门平均工资是'||fn_avgsal(v_deptno));
END;

--创建一个函数，以员工号为参数，返回该员工所在的部门的平均工资。
CREATE OR REPLACE FUNCTION fn_avgsal(
  v_empno NUMBER
)
RETURN NUMBER
AS
  v_sal emp.sal%TYPE;
BEGIN
  SELECT trunc(AVG(emp.sal),2) INTO v_sal FROM 
  emp,(SELECT deptno FROM emp WHERE empno=v_empno) d WHERE d.deptno=emp.deptno;
  RETURN v_sal;
END;
--代码块调用
DECLARE
  v_empno emp.empno%TYPE:=&eid;
BEGIN
    dbms_output.put_line('部门平均工资是'||fn_avgsal(v_empno));
END;

/*
创建一个存储过程，以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号。 
如果修改成功，则显示“员工由……号部门调入调入……号部门”；
如果不存在该员工，则显示 “员工号不存在，请输入正确的员工号。”；
如果不存在该部门，则显示 “该部门不存在，请输入正确的部门号。”。
*/
CREATE OR REPLACE PROCEDURE updeptno(
  v_empno NUMBER,v_deptno NUMBER
)
AS
  v_count NUMBER(3);
  v_exp EXCEPTION;
  v_deptno2 emp.deptno%TYPE;
BEGIN
  SELECT COUNT(*) INTO v_count FROM emp WHERE empno=v_empno;
  IF v_count<=0 THEN
    dbms_output.put_line('该员工不存在，请输入正确的员工号。');
  ELSE
    SELECT COUNT(*) INTO v_count FROM dept WHERE deptno=v_deptno;
    IF v_count<=0 THEN
      dbms_output.put_line('该部门不存在，请输入正确的部门号。');
    ELSE
      SELECT deptno INTO v_deptno2 FROM emp WHERE empno=v_empno;
      UPDATE emp SET deptno=v_deptno WHERE empno=v_empno;
      IF SQL%NOTFOUND THEN
        RAISE v_exp;
      ELSE
        dbms_output.put_line('员工由'||v_deptno2||'号部门调入调入'||v_deptno||'号部门');
      END IF;
    END IF;
  END IF;
EXCEPTION
  WHEN v_exp THEN
    dbms_output.put_line('修改错误！');
END;
--代码块调用
DECLARE
  v_empno emp.empno%TYPE:=&eid;
  v_deptno emp.deptno%TYPE:=&did;
BEGIN
  updeptno(v_empno,v_deptno);
END;

本文作者： 腾飞
本文链接： https://www.tengfei.eu.org/article/c58dc7f0.html
版权声明： 本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。转载请注明出处！