新建ORACLE用户/用户函数 及 获取简单变量数据前端方法

1、新建ORACLE用户

--新建ORACLE用户
CREATE USER N_INFECT IDENTIFIED BY N_INFECT; 

-- USER SQL
ALTER USER N_INFECT
DEFAULT TABLESPACE "JXTBSPACE"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK ;

-- ROLES
GRANT resource TO N_INFECT;
ALTER USER N_INFECT DEFAULT ROLE resource;

-- SYSTEM PRIVILEGES
GRANT SELECT ANY TABLE TO N_INFECT;
GRANT SELECT ANY SEQUENCE TO N_INFECT ;
GRANT UNLIMITED TABLESPACE TO N_INFECT;

-- SYSTEM PRIVILEGES
GRANT SELECT ANY TABLE TO N_INFECT;
GRANT EXECUTE ANY PROCEDURE TO N_INFECT;
GRANT ALTER ANY INDEX TO N_INFECT ;
GRANT ALTER ANY PROCEDURE TO N_INFECT ;
GRANT CREATE ANY INDEX TO N_INFECT ;
GRANT ALTER DATABASE LINK TO N_INFECT ;
GRANT ALTER ANY TABLE TO N_INFECT ;
GRANT ALTER ANY SEQUENCE TO N_INFECT ;
GRANT SELECT ANY SEQUENCE TO N_INFECT ;
GRANT CREATE ANY JOB TO N_INFECT ;
GRANT ALTER USER TO N_INFECT ;
GRANT DROP ANY INDEX TO N_INFECT ;
GRANT ALTER ANY ROLE TO N_INFECT ;
GRANT ALTER ANY TRIGGER TO N_INFECT ;
GRANT CREATE ANY TRIGGER TO N_INFECT ;
GRANT CREATE ANY TABLE TO N_INFECT ;
GRANT DELETE ANY TABLE TO N_INFECT ;
GRANT DROP ANY TYPE TO N_INFECT ;
GRANT UPDATE ANY TABLE TO N_INFECT ;
GRANT INSERT ANY TABLE TO N_INFECT ;
GRANT UNLIMITED TABLESPACE TO N_INFECT;
/

2、创建用户函数

CREATE OR REPLACE FUNCTION entity.fnGetCardNo(v_PersonId IN number, v_IdentifierRootId IN number)
RETURN varchar2
AS
    v_CardNo                        varchar2(90);
BEGIN
    /*
    2026-06-25      guoshaoyue             Created
    IdentifierRootId: 1-居民身份证; 35-回乡证; 36-台胞证; 17-护照; 42-外国人永久居留身份证; 15-港澳居民身份证; 
    */
    SELECT max(IDENTIFIER) INTO v_CardNo FROM entity.personidentifier WHERE IDENTIFIERROOTID = v_IdentifierRootId AND personId = v_PersonId;
    RETURN v_CardNo;
END;

3、获取简单数据前端方法

private string GetInstitutionCode()
{
    string sql = "SELECT MAX(a.VALUE) FROM infect.parameter a WHERE a.ID='D5DC8EBC-D760-4D51-B48C-1EA3D2B7B956'";
    string ret = DapperHelper.Query<string>(Dbs.His, sql).FirstOrDefault();

    return ret;
}