oracle创建package、body、函数

--删除表
drop table appl_application_sbfp;
drop table sys_user_info;

--创建表和主键
CREATE TABLE appl_application_sbfp (
    application_no   varchar(32) NOT NULL ,
    customer_id      varchar(32) NOT NULL,
    customer_name    varchar(32) NOT NULL,
    input_user_id    varchar(32) NOT NULL,
    PRIMARY KEY (application_no)
);
CREATE TABLE sys_user_info (
    user_id          varchar(32) NOT NULL ,
    user_name        varchar(32) NOT NULL,
    pass_word        varchar(32),--default null
    org_id           varchar(32),
    PRIMARY KEY (user_id)
);

insert into appl_application_sbfp(application_no,customer_id,customer_name,input_user_id)
values('APPL20190603','c20190603','王平','WANGLI');
insert into appl_application_sbfp(application_no,customer_id,customer_name,input_user_id)
values('APPL20190604','c20190604','张平','WANGLI');
insert into sys_user_info(user_id,user_name)
values('WANGLI','王丽');

--创建包、包体、存储函数
drop package body PA_GLOBAL;
drop package PA_GLOBAL;
create or replace package PA_GLOBAL is
FUNCTION getUserName(user_id IN varchar2) RETURN varchar2;
end PA_GLOBAL;
/
create or replace package body PA_GLOBAL is
FUNCTION getUserName(user_id IN varchar2) RETURN varchar2
is 
userName varchar2(200);
BEGIN
  SELECT user_name into userName from sys_user_info;
  return userName;
END getUserName;
end PA_GLOBAL;


--查询 appl_application_sbfp 使用包里的函数
select application_no,customer_id,customer_name,PA_GLOBAL.getUserName(input_user_id) 
from appl_application_sbfp;

--通过查表获取包体函数内容
select * from all_source where name='PA_GLOBAL';

--创建存储函数
drop function f_getUserName;
CREATE OR REPLACE FUNCTION f_getUserName(user_id IN varchar2)RETURN varchar2 
IS
userName varchar2(200);
BEGIN
  SELECT user_name into userName from sys_user_info;
  return userName;
END;

--使用函数
select application_no,customer_id,customer_name,f_getUserName(input_user_id) 
from appl_application_sbfp;