Oracle笔记十五：自定义存储/函数/type/包

1、自定义存储过程与函数

1.1、存储过程与函数定义及区别

存储在数据库中供所有用户程序调用的子程序叫存储过程、函数
相同点：完成特定功能的程序
区别：
　　1）自定义函数可使用return语句返回值，过程不可
2）自定义函数不可进行insert、update、delete等对数据库进行写操作（除非使用自治事务），存储可以
3）自定义函数必须要有返回值

1.2、创建/修改/调用/删除存储、函数

1.2.1、创建/修改/调用存储

1.2.1.1、不带参数

–创建或替换存储过程,as 后面为说明部分，as 相当于declare
create or replace procedure sayhelloworld
as
–说明部分
begin
–dbms_output.put()不换行输出，输出在缓冲区，不显示出来，直到执行put_line才一并输出。
–dbms_output.put_line()换行输出。但首先会输出缓冲区中的内容，然后清空缓冲区。
dbms_output.put_line(“Hello World”);
end;

调用存储过程：
1、call sayhelloworld();
2、存储中调用（以下为临时存储）
declare
begin
　　sayhelloworld();
end;

1.2.1.2、带输入参数不带输出参数

create or replace procedure sayhelloworld (V_DT DATE,V_NUM NUMBER , V_CHAR VARCHAR2 )
as
begin
dbms_output.put_line(V_DT);
dbms_output.put_line(V_NUM);
dbms_output.put_line(V_CHAR);
end;
调用存储过程：
1、call SAYHELLOWORLD(SYSDATE, 1, ‘HELLO WORLD’);
2、存储中调用（以下为临时存储）
declare
begin
　　SAYHELLOWORLD(SYSDATE, 1, ‘HELLO WORLD’);
end;

1.2.1.2、带输入、输出参数

create or replace procedure sayhelloworld (V_DT DATE,V_NUM NUMBER , V_CHAR VARCHAR2
,V_OUT1 OUT VARCHAR2 ,V_OUT2 OUT NUMBER )
as
begin
dbms_output.put_line(V_DT);
dbms_output.put_line(V_NUM);
dbms_output.put_line(V_CHAR);
V_OUT1:=‘HI’;
V_OUT2:=0;
end;

调用存储过程：
DECLARE
V_CHAR VARCHAR2(20);
V_NUM NUMBER;
BEGIN
SAYHELLOWORLD(SYSDATE, 1, ‘HELLO WORLD’,V_CHAR,V_NUM);
dbms_output.put_line(V_CHAR);
dbms_output.put_line(V_NUM);
END;

1.2.2、创建/修改/调用函数

1.2.2.1、不带参数

CREATE OR REPLACE FUNCTION SAYHELLOWORLD RETURN VARCHAR2
IS
RESULT VARCHAR2(20);
BEGIN
　　RESULT:=‘Hello World’;
　　RETURN(RESULT);
END;
调用函数：
SELECT SAYHELLOWORLD() FROM DUAL;

1.2.2.2、带参数

CREATE OR REPLACE FUNCTION SAYHELLOWORLD1(V_DT DATE,V_NUM NUMBER , V_CHAR VARCHAR2
,V_OUT1 IN OUT VARCHAR2 ,V_OUT2 OUT NUMBER
) RETURN VARCHAR2
IS
RESULT VARCHAR2(20);
BEGIN
RESULT:=TO_CHAR(V_DT,‘YYYY’)||TO_CHAR(V_NUM)||V_CHAR;
V_OUT1:=‘WWW’;
V_OUT2:=10;
RETURN(RESULT);
END;

调用函数：
DECLARE
V_CHAR VARCHAR2(20):=‘KKK’;
V_CHAR1 VARCHAR2(20);
V_NUM NUMBER;
BEGIN
V_CHAR1:=SAYHELLOWORLD1(SYSDATE, 1, ‘HELLO WORLD’,V_CHAR,V_NUM);
dbms_output.put_line(V_CHAR||V_CHAR1);
dbms_output.put_line(1||V_NUM);
END;

1.2.3、删除存储/函数

删除存储：
DROP PROCEDURE Pname;

删除函数：
DROP FUNCTION Fname;

2、自定义type

2.1、子类型

这种类型最简单，类似类型的一个别名，主要是为了对常用的一些类型简单化，它基于原始的某个类型。如：
有些应用会经常用到一些货币类型：number(16,2)。如果在全局范围各自定义这种类型，一旦需要修改该类型的精度，则需要一个个地修改。
那如何实现定义的全局化呢？于是就引出了子类型：
subtype cc_num is number(16,2);

2.2、普通类型

create or replace type typ_calendar as object(
V_YEAR varchar2(8),
V_MONTH varchar2(8),
V_SUN varchar2(8),
V_MON varchar2(8),
V_TUS varchar2(8),
V_WEN varchar2(8),
V_THU varchar2(8),
V_FRI varchar2(8),
V_STA varchar2(8),
V_LAST varchar2(2)
);
create table tcalendar of typ_calendar;
insert into tcalendar select typ_calendar(‘2010’,‘05’,‘1’,‘2’,‘3’,‘4’,‘5’,‘6’,‘7’,‘31’) from dual;

2.3、带成员函数的类型体（type body）

这种类型包含了对类型中数据的内部处理，调用该类型时，可将处理后的数据返回给调用方。

CREATE OR REPLACE TYPE MSG_RECORD is object
(
  flag      varchar2(128),
  message   varchar2(4000),
  msgdetail clob,
  sql_a     clob,
  sql_u     clob,
  sql_d     clob,
  sql_t     clob
)

CREATE OR REPLACE TYPE TAB_MSG is table of msg_record

CREATE OR REPLACE TYPE COLUMN_ARRAY IS TABLE OF VARCHAR2(30)

CREATE OR REPLACE TYPE TYPE_REPORT is object
(
/* Variables */
  v_Code     varchar2(6),
  v_Name     varchar2(512),
  Check_Flag      char(1),
  Tab_Main        varchar2(30),
  Tab_Batch       varchar2(30),
  Tab_Tranlist    varchar2(30),
  Tab_Batch_In    varchar2(30),
  Tab_Tranlist_In varchar2(30),
  Tab_BakDel      varchar2(30),
  Col_Batch       column_array,
  Col_Tranlist    column_array,

/* Constructors */
  constructor function TYPE_REPORT(v_Code varchar2,
                                  v_CheckFlag  char default 'N')
    return self as result,

/* Member methods */
  member function isCheckBalance return boolean

)

CREATE OR REPLACE TYPE BODY TYPE_REPORT as
  /* Constructors */
  constructor function TYPE_REPORT(v_Code varchar2, v_CheckFlag char default 'N') return self as result as
  begin
    self.v_Code := v_Code;
    self.Check_Flag := v_CheckFlag;
    select cd_desc, cd2, cd3, cd4
      into self.v_Name, self.Tab_Batch, self.Tab_Tranlist, self.Tab_BakDel
      from c_t_cd
     where table_name = 'T_Table'
       and cd1 = self.v_Code
    ;
    self.Tab_Main := nvl(self.Tab_Batch, self.Tab_Tranlist);
    self.Tab_Batch_In := self.v_Code || '_BATCH';
    self.Tab_Tranlist_In := self.v_Code || '_TRANLIST';
    select column_name BULK COLLECT INTO self.Col_Batch from dic_report where table_name = self.Tab_Batch;
    select column_name BULK COLLECT INTO self.Col_Tranlist from dic_report where table_name = self.Tab_Tranlist;

    return;
  end;

  /* Member methods */
  member function isCheckBalance return boolean as
  begin
    return substr(self.v_Code,1,4) in ('111','222');
  end;
end;

CREATE OR REPLACE FUNCTION F_GET_MESSAGE (v_Code char, v_Date char) return tab_msg is
	PRAGMA AUTONOMOUS_TRANSACTION;
	rpt type_report;
	v_code1 char(6);
	v_CR_LF char(2) := chr(10)||chr(13);
	v_Flag varchar2(8) := '0'; -- 0-正常 1-失败
	v_Message varchar2(4000);
	v_MsgDetail clob;
	v_SQL_A clob;
	v_SQL_U clob;
	v_SQL_D clob;
	v_SQL_T clob;
	v_tab_msg tab_msg := tab_msg();
begin
  v_tab_msg.extend();

  select cd1 into v_code1
    from c_t_cd
   where table_name = 'T_Table'
     and (cd1 = v_Code or cd2 = v_Code or cd3 = v_Code)
  ;

  rpt := type_report(v_code1);
  if (rpt.isCheckBalance) then 
	v_SQL_A: