存储过程 #

一、存储过程概述 #

1.1 什么是存储过程 #

存储过程是存储在数据库中的可执行代码：

text 
存储过程特点：
├── 封装业务逻辑
├── 提高代码复用
├── 减少网络传输
├── 增强安全性
└── 支持多种语言

1.2 OrientDB函数类型 #

类型 说明
SQL函数 使用SQL语法
JavaScript函数 使用JavaScript语法
Java函数 使用Java编写

二、创建函数 #

2.1 使用Studio创建 #

  1. 访问 http://localhost:2480
  2. 选择数据库
  3. 点击 “Functions” 标签
  4. 点击 “New Function”
  5. 输入函数名和代码

2.2 使用SQL创建 #

sql 
CREATE FUNCTION greet "SELECT 'Hello, ' + name + '!' AS message"

2.3 创建带参数的函数 #

sql 
CREATE FUNCTION greetPerson(name) "SELECT 'Hello, ' + name + '!' AS message"

三、JavaScript函数 #

3.1 基本JavaScript函数 #

javascript 
// 函数名: calculateAge
var birthYear = birthYear;
var currentYear = new Date().getFullYear();
return currentYear - birthYear;

3.2 数据库操作 #

javascript 
// 函数名: getActiveUsers
var db = orient.getDatabase();
var result = db.query("SELECT FROM User WHERE status = 'active'");
return result;

3.3 事务操作 #

javascript 
// 函数名: transferMoney
var db = orient.getDatabase();
db.begin();
try {
    db.command("UPDATE Account SET balance = balance - ? WHERE id = ?", [amount, fromId]);
    db.command("UPDATE Account SET balance = balance + ? WHERE id = ?", [amount, toId]);
    db.commit();
    return {success: true};
} catch (e) {
    db.rollback();
    return {success: false, error: e.message};
}

3.4 复杂业务逻辑 #

javascript 
// 函数名: processOrder
var db = orient.getDatabase();
var orderId = orderId;

db.begin();
try {
    var order = db.query("SELECT FROM Order WHERE id = ?", [orderId])[0];
    
    if (order.status != 'pending') {
        return {success: false, error: 'Order already processed'};
    }
    
    var items = db.query("SELECT FROM OrderItem WHERE orderId = ?", [order.rid]);
    var totalAmount = 0;
    
    for (var i = 0; i < items.length; i++) {
        var item = items[i];
        var product = db.query("SELECT FROM Product WHERE @rid = ?", [item.productId])[0];
        
        if (product.stock < item.quantity) {
            db.rollback();
            return {success: false, error: 'Insufficient stock for ' + product.name};
        }
        
        db.command("UPDATE Product SET stock = stock - ? WHERE @rid = ?", 
            [item.quantity, product.rid]);
        totalAmount += item.quantity * item.price;
    }
    
    db.command("UPDATE Order SET status = 'processed', totalAmount = ? WHERE @rid = ?", 
        [totalAmount, order.rid]);
    
    db.commit();
    return {success: true, totalAmount: totalAmount};
} catch (e) {
    db.rollback();
    return {success: false, error: e.message};
}

四、SQL函数 #

4.1 基本SQL函数 #

sql 
CREATE FUNCTION getPersonCount "SELECT COUNT(*) FROM Person"

4.2 带参数的SQL函数 #

sql 
CREATE FUNCTION getPersonByName(name) "SELECT FROM Person WHERE name = :name"

4.3 复杂SQL函数 #

sql 
CREATE FUNCTION getTopCustomers(limit) "
    SELECT 
        customer.name AS customerName,
        COUNT(*) AS orderCount,
        SUM(totalAmount) AS totalSpent
    FROM Order
    GROUP BY customer
    ORDER BY totalSpent DESC
    LIMIT :limit
"

五、调用函数 #

5.1 SQL调用 #

sql 
SELECT greetPerson('Tom')
SELECT calculateAge(1990)
SELECT getActiveUsers()

5.2 在查询中使用 #

sql 
SELECT name, calculateAge(birthYear) AS age FROM Person
SELECT name, getPersonCount() AS totalPersons FROM Person LIMIT 1

5.3 Java调用 #

java 
ODatabaseSession db = orientDB.open("mydb", "admin", "admin");
OResultSet result = db.query("SELECT greetPerson(?)", "Tom");
while (result.hasNext()) {
    OResult row = result.next();
    System.out.println(row.getProperty("message"));
}

5.4 REST API调用 #

text 
POST /function/{database}/{functionName}
Content-Type: application/json

{
    "parameters": ["Tom", 30]
}

六、函数管理 #

6.1 查看所有函数 #

sql 
SELECT FROM OFunction
SELECT name, language FROM OFunction

6.2 查看函数定义 #

sql 
SELECT name, code FROM OFunction WHERE name = 'greetPerson'

6.3 更新函数 #

sql 
UPDATE OFunction SET code = "SELECT 'Hello, ' + name + '!!!' AS message" 
WHERE name = 'greetPerson'

6.4 删除函数 #

sql 
DELETE FROM OFunction WHERE name = 'greetPerson'
DROP FUNCTION greetPerson

七、函数参数 #

7.1 位置参数 #

javascript 
// 函数名: add
var a = arguments[0];
var b = arguments[1];
return a + b;

7.2 命名参数 #

javascript 
// 函数名: greet
var name = name || 'Guest';
return 'Hello, ' + name + '!';

7.3 默认参数 #

javascript 
// 函数名: calculateDiscount
var price = price;
var discount = discount || 0.1;
return price * (1 - discount);

7.4 可变参数 #

javascript 
// 函数名: sum
var total = 0;
for (var i = 0; i < arguments.length; i++) {
    total += arguments[i];
}
return total;

八、返回值 #

8.1 返回单值 #

javascript 
// 函数名: square
return n * n;

8.2 返回对象 #

javascript 
// 函数名: getPersonInfo
var db = orient.getDatabase();
var person = db.query("SELECT FROM Person WHERE @rid = ?", [rid])[0];
return {
    name: person.name,
    age: person.age,
    email: person.email
};

8.3 返回集合 #

javascript 
// 函数名: getPersonList
var db = orient.getDatabase();
return db.query("SELECT FROM Person WHERE status = 'active'");

九、错误处理 #

9.1 异常捕获 #

javascript 
// 函数名: safeOperation
try {
    var db = orient.getDatabase();
    db.command("INSERT INTO Person SET name = ?", [name]);
    return {success: true};
} catch (e) {
    return {success: false, error: e.message};
}

9.2 参数验证 #

javascript 
// 函数名: validateAndCreate
if (!name || name.trim() === '') {
    throw new Error('Name is required');
}
if (!email || !email.includes('@')) {
    throw new Error('Valid email is required');
}
var db = orient.getDatabase();
return db.command("INSERT INTO Person SET name = ?, email = ?", [name, email]);

十、实际应用示例 #

10.1 用户注册函数 #

javascript 
// 函数名: registerUser
var db = orient.getDatabase();
var username = username;
var email = email;
var password = password;

var existing = db.query("SELECT FROM User WHERE email = ?", [email]);
if (existing.length > 0) {
    return {success: false, error: 'Email already exists'};
}

db.begin();
try {
    var user = db.command(
        "INSERT INTO User SET username = ?, email = ?, password = ?, status = 'active', createdAt = sysdate()",
        [username, email, password]
    );
    db.command(
        "INSERT INTO UserProfile SET userId = ?, avatar = 'default.png'",
        [user[0].rid]
    );
    db.commit();
    return {success: true, userId: user[0].rid.toString()};
} catch (e) {
    db.rollback();
    return {success: false, error: e.message};
}

10.2 订单处理函数 #

javascript 
// 函数名: createOrder
var db = orient.getDatabase();
var customerId = customerId;
var items = items;

db.begin();
try {
    var order = db.command(
        "INSERT INTO Order SET customer = ?, status = 'pending', createdAt = sysdate()",
        [customerId]
    );
    
    var totalAmount = 0;
    for (var i = 0; i < items.length; i++) {
        var item = items[i];
        db.command(
            "INSERT INTO OrderItem SET orderId = ?, productId = ?, quantity = ?, price = ?",
            [order[0].rid, item.productId, item.quantity, item.price]
        );
        totalAmount += item.quantity * item.price;
    }
    
    db.command(
        "UPDATE Order SET totalAmount = ? WHERE @rid = ?",
        [totalAmount, order[0].rid]
    );
    
    db.commit();
    return {success: true, orderId: order[0].rid.toString(), totalAmount: totalAmount};
} catch (e) {
    db.rollback();
    return {success: false, error: e.message};
}

10.3 报表生成函数 #

javascript 
// 函数名: generateSalesReport
var db = orient.getDatabase();
var startDate = startDate;
var endDate = endDate;

var orders = db.query(
    "SELECT FROM Order WHERE createdAt BETWEEN ? AND ?",
    [startDate, endDate]
);

var totalRevenue = 0;
var orderCount = orders.length;
var productSales = {};

for (var i = 0; i < orders.length; i++) {
    var order = orders[i];
    totalRevenue += order.totalAmount;
    
    var items = db.query("SELECT FROM OrderItem WHERE orderId = ?", [order.rid]);
    for (var j = 0; j < items.length; j++) {
        var item = items[j];
        var productId = item.productId.toString();
        if (!productSales[productId]) {
            productSales[productId] = {quantity: 0, revenue: 0};
        }
        productSales[productId].quantity += item.quantity;
        productSales[productId].revenue += item.quantity * item.price;
    }
}

return {
    period: {start: startDate, end: endDate},
    totalRevenue: totalRevenue,
    orderCount: orderCount,
    productSales: productSales
};

十一、性能优化 #

11.1 减少数据库调用 #

javascript 
// 不推荐
for (var i = 0; i < ids.length; i++) {
    db.query("SELECT FROM Person WHERE @rid = ?", [ids[i]]);
}

// 推荐
db.query("SELECT FROM Person WHERE @rid IN ?", [ids]);

11.2 使用批量操作 #

javascript 
// 推荐
db.begin();
for (var i = 0; i < records.length; i++) {
    db.command("INSERT INTO Person SET name = ?", [records[i].name]);
}
db.commit();

11.3 缓存结果 #

javascript 
// 函数名: getCachedConfig
var db = orient.getDatabase();
var cacheKey = "config_" + configKey;

var cached = db.query("SELECT FROM Cache WHERE key = ?", [cacheKey]);
if (cached.length > 0) {
    return cached[0].value;
}

var config = db.query("SELECT FROM Config WHERE key = ?", [configKey])[0];
db.command("INSERT INTO Cache SET key = ?, value = ?, expiresAt = sysdate() + 3600000", 
    [cacheKey, config.value]);

return config.value;

十二、总结 #

存储过程要点：

操作 语法 说明
创建 CREATE FUNCTION 创建函数
调用 SELECT functionName() 调用函数
查看 SELECT FROM OFunction 查看函数
删除 DROP FUNCTION 删除函数

下一步，让我们学习触发器！

最后更新：2026-03-27