PL/SQL编程之oracle数据库重载函数包体

最新推荐文章于 2023-07-31 13:50:56 发布

赡养

最新推荐文章于 2023-07-31 13:50:56 发布

阅读量506

点赞数 1

分类专栏： oracle 数据库 plsql 文章标签：数据库 oracle plsql sql

本文链接：https://blog.csdn.net/Insupport/article/details/106282428

版权

数据库同时被 3 个专栏收录

8 篇文章 0 订阅

订阅专栏

oracle

4 篇文章 0 订阅

订阅专栏

plsql

1 篇文章 0 订阅

订阅专栏

谈到了oracle 作为关系型数据库的头把，它的强大之处不仅仅是在于简单的维护。
oracle延申的第三代过程化语言：PL/SQL语言编程之重载实例：
所谓的重载，和其它第三代过程化语言类似（同名函数放置不同的参数来完成不同的功能），本人这个例子将用PL/SQL实现相近重载函数的运用方式。
不说这么多虚的，直接上例子：
使用重载函数定义oracle 包体，创建一个包体对外声明，声明内创建两个录入数据信息的重载函数：

CREATE OR REPLACE PACKAGE shan_yang IS
    PROCEDURE shanyang
    (
    set_id departments.department_id%TYPE,
    set_name departments.department_name%TYPE:='NULL',
    set_loc departments.location_id%TYPE:=1700
    );
    PROCEDURE shanyang
    (
    set_name departments.department_name%TYPE:='NULL',
    set_loc departments.location_id%TYPE:=1700
    );
END shan_yang;
Package SHAN_YANG 已编译

创建此同名PL/SQL包体，定义 private内向数据部分：

CREATE OR REPLACE PACKAGE BODY shan_yang IS
  PROCEDURE shanyang
    (
    set_id departments.department_id%TYPE,
    set_name departments.department_name%TYPE:='NULL',
    set_loc departments.location_id%TYPE:=1700
    ) IS
  BEGIN
    INSERT INTO departments(department_id,department_name,location_id)
    VALUES (set_id,set_name,set_loc);
    DBMS_OUTPUT.PUT_LINE('数据值已经插入到指定表中');
  END shanyang;
  PROCEDURE shanyang
    (
    set_name departments.department_name%TYPE:='NULL',
    set_loc departments.location_id%TYPE:=1700
    ) IS
  BEGIN
    INSERT INTO departments(department_id,department_name,location_id)
    VALUES (departments_seq.nextval,set_name,set_loc);
    DBMS_OUTPUT.PUT_LINE('已将相关的操作插入指定表中');
  END shanyang;
END shan_yang;

查看一下USER_SOURCE数据字典中我们刚刚创建的PL/SQL包：

SQL> SELECT NAME,TEXT FROM user_source
  2  WHERE NAME LIKE'SHAN_YANG'
  3  ORDER BY ORIGIN_CON_ID;

NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
PACKAGE shan_yang IS

SHAN_YANG
END shan_yang;

SHAN_YANG
    (


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
    set_id departments.department_id%TYPE,

SHAN_YANG
    set_name departments.department_name%TYPE:='NULL',

SHAN_YANG
    set_loc departments.location_id%TYPE:=2700


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
    );

SHAN_YANG
    PROCEDURE shanyang

SHAN_YANG
    (


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
    set_name departments.department_name%TYPE:='NULL',

SHAN_YANG
    set_loc departments.location_id%TYPE:=2700

SHAN_YANG
    );


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
END shan_yang;

SHAN_YANG
PACKAGE BODY shan_yang IS

SHAN_YANG
  PROCEDURE shanyang


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
    (

SHAN_YANG
    set_id departments.department_id%TYPE,

SHAN_YANG
    set_name departments.department_name%TYPE:='NULL',


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
    set_loc departments.location_id%TYPE:=2700

SHAN_YANG
    ) IS

SHAN_YANG
  BEGIN


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
    INSERT INTO departments(department_id,department_name,location_id)

SHAN_YANG
    VALUES (set_id,set_name,set_loc);

SHAN_YANG
    DBMS_OUTPUT.PUT_LINE('数据值已经插入到指定表中');


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
  END shanyang;

SHAN_YANG
  PROCEDURE shanyang

SHAN_YANG
    (


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
    set_name departments.department_name%TYPE:='NULL',

SHAN_YANG
    set_loc departments.location_id%TYPE:=2700

SHAN_YANG
    ) IS


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
  BEGIN

SHAN_YANG
    INSERT INTO departments(department_id,department_name,location_id)

SHAN_YANG
    VALUES (departments_seq.nextval,set_name,set_loc);


NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SHAN_YANG
    DBMS_OUTPUT.PUT_LINE('已将相关的操作插入指定表中');

SHAN_YANG
  END shanyang;

SHAN_YANG
    PROCEDURE shanyang


已选择 36 行。

SQL>

查看一下我们要执行操作的表：


DEPARTMENT_ID DEPARTMENT_NAME		     MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
	   10 Administration			    200        1700
	   20 Marketing 			        201        1800
	   30 Purchasing			        114        1700
	   40 Human Resources			    203        2400
	   50 Shipping				        121        1500
	   60 IT				            103        1400
	   70 Public Relations			    204        2700
	   80 Sales				            145        2500
	   90 Executive 			        100        1700
	  100 Finance				        108        1700
	  110 Accounting			        205        1700

DEPARTMENT_ID DEPARTMENT_NAME		     MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
	  120 Treasury					               1700
	  130 Corporate Tax				               1700
	  140 Control And Credit			           1700
	  150 Shareholder Services			           1700
	  160 Benefits					               1700
	  170 Manufacturing				               1700
	  180 Construction				               1700
	  190 Contracting				               1700
	  200 Operations				               1700
	  210 IT Support				               1700
	  220 NOC					                   1700

DEPARTMENT_ID DEPARTMENT_NAME		     MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
	  230 IT Helpdesk				               1700
	  240 Government Sales				           1700
	  250 Retail Sales				               1700
	  260 Recruiting				               1700
	  270 Payroll					               1700
	  370 aaaa					                   1700

已选择 28 行。

SQL>

可以直接使用EXEC调用也可以使用DCLERA匿名块调用，我使用exec直接调用：

SQL> EXEC shan_yang.shanyang('bbbb',2500);

PL/SQL 过程已成功完成。

SQL> SELECT * FROM DEPARTMENTS;

DEPARTMENT_ID DEPARTMENT_NAME		     MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
	   10 Administration			    200        1700
	   20 Marketing 			        201        1800
	   30 Purchasing			        114        1700
	   40 Human Resources			    203        2400
	   50 Shipping				        121        1500
	   60 IT				            103        1400
	   70 Public Relations			    204        2700
	   80 Sales				            145        2500
	   90 Executive 			        100        1700
	  100 Finance				        108        1700
	  110 Accounting			        205        1700

DEPARTMENT_ID DEPARTMENT_NAME		     MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
	  120 Treasury					               1700
	  130 Corporate Tax				               1700
	  140 Control And Credit			           1700
	  150 Shareholder Services			           1700
	  160 Benefits					               1700
	  170 Manufacturing				               1700
	  180 Construction				               1700
	  190 Contracting				               1700
	  200 Operations				               1700
	  210 IT Support				               1700
	  220 NOC					                   1700

DEPARTMENT_ID DEPARTMENT_NAME		     MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
	  230 IT Helpdesk				               1700
	  240 Government Sales				           1700
	  250 Retail Sales				               1700
	  260 Recruiting				               1700
	  270 Payroll					               1700
	  390 bbbb					                   2500
	  370 aaaa					                   1700

已选择 29 行。

SQL>

可以看到我们在EXEC调用相同名称的包体存储过程名称时在键入数据的同时只键入了name和location两个信息，存储过程自动匹配了我们创建的第二个重载函数体作插入操作。
到此一个简单的重载函数包体就算是创建完成了。

赡养

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL编程之oracle数据库重载函数包体

谈到了oracle 作为关系型数据库的头把，它的强大之处不仅仅是在于简单的维护。oracle延申的第三代过程化语言：PL/SQL语言编程之重载实例：所谓的重载，和其它第三代过程化语言类似（同名函数放置不同的参数来完成不同的功能），本人这个例子将用PL/SQL实现相近重载函数的运用方式。不说这么多虚的，直接上例子：使用重载函数定义oracle 包体，创建一个包体对外声明，声明内创建两个录入数据信息的重载函数：CREATE OR REPLACE PACKAGE shan_yang IS PR
复制链接

扫一扫