SAP CDS view 定义的数据库视图和传统 SQL 语句定义视图的区别

define view EmployeeView as select from Employees {
    key EmployeeID,
    FirstName,
    LastName,
    DateOfBirth,
    Salary
}

CREATE VIEW EmployeeView AS
SELECT EmployeeID,
       FirstName,
       LastName,
       DateOfBirth,
       Salary
FROM Employees;

define view EmployeeWithDepartment as select from Employees
association [0..1] to Departments on $projection.DepartmentID = Departments.DepartmentID {
    key EmployeeID,
    FirstName,
    LastName,
    DateOfBirth,
    Salary,
    Departments.DepartmentName
}

CREATE VIEW EmployeeWithDepartment AS
SELECT E.EmployeeID,
       E.FirstName,
       E.LastName,
       E.DateOfBirth,
       E.Salary,
       D.DepartmentName
FROM Employees E
LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID;

define view DepartmentWithEmployeeCount as select from Departments {
    key DepartmentID,
    DepartmentName,
    @ObjectModel.count.association('Employees') as EmployeeCount
}

CREATE VIEW DepartmentWithEmployeeCount AS
SELECT D.DepartmentID,
       D.DepartmentName,
       COUNT(E.EmployeeID) as EmployeeCount
FROM Departments D
LEFT JOIN Employees E ON D.DepartmentID = E.DepartmentID
GROUP BY D.DepartmentID, D.DepartmentName;

define view LowSalaryEmployees as select from Employees {
    key EmployeeID,
    FirstName,
    LastName,
    DateOfBirth,
    Salary
}
where Salary <= 5000;

CREATE VIEW LowSalaryEmployees AS
SELECT EmployeeID,
       FirstName,
       LastName,
       DateOfBirth,
       Salary
FROM Employees
WHERE Salary <= 5000;

extend view ExtendedEmployeeView with {
    bonusAmount : Salary * 0.1 as currency
}
in projection;

-- 假设 ExtendedEmployeeView 是 EmployeeView 的扩展
CREATE VIEW ExtendedEmployeeView AS
SELECT EmployeeID,
       FirstName,
       LastName,
       DateOfBirth,
       Salary,
       Salary * 0.1 as bonusAmount
FROM EmployeeView;

@AccessControl.authorizationCheck: #CHECK
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #BASIC
define view EmployeeWithAuthorizationCheck as select from Employees {
    key EmployeeID,
    FirstName,
    LastName,
    DateOfBirth,
    Salary
}

-- 数据源直接指定为 Employees 表，适配器由数据库系统自身提供
CREATE VIEW EmployeeWithAuthorizationCheck AS
SELECT EmployeeID,
       FirstName,
       LastName,
       DateOfBirth,
       Salary
FROM Employees;