博客

一、存储过程(存过)

1 存储过程的创建

• 语法

CREATE [OR REPLACE] PROCEDURE 过程名字(SP_NAME) [(参数1,参数2,...)]
IS|AS
  变量名字 变量类型,
  .....
BEGIN
  语句;
  EXCEPTION 
    异常处理;
END [SP_NAME];

• 例子

-- 编写一个存储过程 判断闰年
CREATE OR REPLACE PROCEDURE SP_YEAR(
       I_YEAR IN  NUMBER,  -- IN 输入参数
       O_YEAR OUT VARCHAR2 -- OUT 输出参数
)
IS 
BEGIN
  IF MOD(I_YEAR,4)=0 AND MOD(I_YEAR,100)<>0 OR MOD(I_YEAR,400)=0 THEN
    O_YEAR := '闰年';
  ELSE
    O_YEAR := '平年';
  END IF;
END SP_YEAR;

• 注意

可以设置参数的类型，不写的话默认是IN，只需要指定参数的类型，不需要指定参数的长度。

2 存储过程的调用

1) 在块中直接写 存储过程名字(参数值1,参数值2.....)调用;-- 常用于程序自动的调用
DECLARE
  A VARCHAR2(200);
BEGIN
  SP_YEAR(2021,A); -- 输出参数 需要有一个变量来承接输出参数的值  
    -- 没有参数的话 ()可以省略
  DBMS_OUTPUT.PUT_LINE(A);
END;

2) 直接TEST执行存储过程 -- 常用于手动调用程序

3) 在SQL WINDOW中CALL pro_name(para1,para2..)执行(其中就算没有参数括号也不能省略);-- 不带输出参数

4) 在COMMAND WINDOW中使用EXECUTE pro_name(para1,para2..)执行； -- 不带输出参数   
EXECUTE 存储过程名字;
EXEC 存储过程名字;

3 存储过程的参数

1）参数传递的方式

• 名称传递法

存储过程名字(I_NAME=>VALUE1,I_NAME=>VALUE2....)

• 位置传递法

存储过程名字(VALUE1,VALUE2...)

• 混合传递法

存储过程名字(VALUE1,I_NAME2=>VALUE2...)

注意

只有名称传递才能改变参数位置顺序，混合传递中只要使用了名称传递，后续都要使用名称传递。

2）参数的类型

• IN参数

1) IN模式参数：输入 数据库中默认的一种参数
CREATE OR REPLACE PROCEDURE 存储过程名字 (
 I_NAME1 [IN] DATA_TYPE DEFAULT DEFAULT_VALUE,
 ......
)
IN参数只能是存储默认值或调用方传入值，程序运行过程中IN参数内容不能修改
CREATE OR REPLACE PROCEDURE SP_NYR(
       I_YEAR    VARCHAR2,
       I_MONTH   VARCHAR2,
       I_DAY     VARCHAR2,
       O_NYR OUT VARCHAR2
)
IS
BEGIN
  O_NYR := I_YEAR||'-'||I_MONTH||'-'||I_DAY;
END SP_NYR;

DECLARE 
  B VARCHAR2(10);
BEGIN
  SP_NYR('2021','04','28',B); -- 位置传递法
  DBMS_OUTPUT.PUT_LINE(B);
  SP_NYR(O_NYR=>B,I_YEAR=>'2021',I_DAY=>'28',I_MONTH=>'04');--名称传递法
  DBMS_OUTPUT.PUT_LINE(B);
  SP_NYR('2021',I_DAY=>'28',I_MONTH=>'04',O_NYR=>B);--混合传递法
  DBMS_OUTPUT.PUT_LINE(B);
END;

• OUT参数

1 实际项目中，OUT参数常常用来返回存储过程的执行状态，比如用0表示执行成功，用1表示执行失败。其他类型的返回内容往往是在后续将要学到的存储函数中出现

2 OUT参数在调用时需要给予相应数量的变量用来接收返回值，所以在调用带有OUT参数的存储过程时，往往是通过TEST窗口或者PL/SQL匿名块来调用。IN OUT 参数类型一样

• IN OUT参数

定义一个参数  既可以是输入也可以是输出   
IN OUT 参数无论输入和输出，都只有一个数据类型  

CREATE OR REPLACE PROCEDURE SP_DATE(
       IO_DATE IN OUT VARCHAR2
)
IS 
BEGIN
  IO_DATE :='今天的日期是'||IO_DATE;
END SP_DATE;

4 存储过程的删除

DROP PROCEDURE 存储过程名字;

• 注意

存储过程本身就像一条保存在数据库中的数据，不调用时占用资源很少。

二、存储函数

相对于存储过程，存储函数的数量上、使用频率上都相对较低，源于存储函数必须要有返回值，而且是单一返回值，所以除非特殊功能，多数功能都采用过程来实现。

1 存储函数的创建

• 语法

CREATE OR REPLACE FUNCTION 函数名字[(参数1 参数类型,参数2 参数类型)]
RETURN 数据类型
AS|IS
  变量名1 变量类型,
  .....
BEGIN
  语句;
  RETURN 变量名;-- 单行单列的结果
  EXCEPTION 
    异常处理语句;
END;

• 例子

--设计一个存储函数，用于计算每个员工司龄
CREATE OR REPLACE FUNCTION F_HIREDATE (
       I_HIREDATE1    DATE, -- 输入一个日期
       I_HIREDATE2    DATE  :=SYSDATE  -- 输入一个日期或者不输入默认是SYSDATE
       -- I_HIREDATE2 DATE DEFAULT 值
)
RETURN NUMBER
IS
  V_YEARS   NUMBER(10);
BEGIN
  SELECT TO_CHAR(I_HIREDATE2,'YYYY')-TO_CHAR(I_HIREDATE1,'YYYY') INTO V_YEARS FROM DUAL;
  RETURN V_YEARS;
END F_HIREDATE;

-- 设计一个函数，调用时输入薪资和奖金，返回年收入  
CREATE OR REPLACE FUNCTION F_YEAR_INCOME(
       I_SAL   NUMBER,
       I_COMM  NUMBER
)
RETURN NUMBER
IS
  V_INCOME NUMBER;
BEGIN
  V_INCOME := (NVL(I_SAL,0)+NVL(I_COMM,0))*12;
  RETURN V_INCOME;
END F_YEAR_INCOME;


SELECT ENAME,F_YEAR_INCOME(SAL,COMM) FROM EMP;

注意

定义了输出参数的函数，不能在select里面调用

定义了输出参数的函数，可以在块中调用

2 存储函数的删除

DROP FUNCTION 存储函数名字;

三、包

包的组成分为规范和包主体，俗称包头和包体

1 包头

CREATE OR REPLACE PACKAGE PKG_NAME
IS 
  -- 过程的名字和参数信息
  PROCEDURE PRO_NAME(参数1,参数2);
  -- 函数的名字和参数信息
  FUNCTION FUN_NAME(参数1,参数2...) RETURN 数据类型;
  .....
END PKG_NAME;

2 包体

CREATE OR REPLCAE PACKAGE BODY PKG_NAME
IS 
  -- 过程的具体内容
  PROCEDURE PRO_NAME(参数1,参数2...) IS ...;
  -- 函数的具体内容
  FUNCTION FUN_NAME(参数1,参数2...) RETURN 数据类型 IS ...;
  ....
  
END PAK_NAME;

注意

1）包头和包体的名字要相同
2）包头要先于包体建立
3）包头和包体中只有建立包的关键词，没有建立过程、函数等的关键词
4）包头中只需各个程序的名字和参数信息即可，不需要具体内容，而包体中除了名字和参数信息，也要写上具体内容。

包中的过程和一般的过程可以重名 。

3 调用包中的函数和过程

SELECT PKG_RECOVER.F_YEAR(2020) FROM DUAL;

BEGIN
  PKG_RECOVER.SP_RECOVER_DEPT;
  PKG_RECOVER.SP_RECOVER_EMP;
END;

注意

在包的内部调用 可以不加包名，在外面使用需要加上包名，不加上 使用一般的函数 或者存储过程

4 包的删除

DROP PACKAGE PKG_NAME;