This article focuses on programmable development in KES databases and explains how to design stored procedures, functions, and triggers to address common challenges such as pushing business logic down to the database layer, automating audits, and reusing data validation logic. Keywords: KES Database, PL/SQL, Triggers.
Technical specifications at a glance
| Parameter | Description |
|---|---|
| Database / Language | KES / PL/SQL |
| Compatibility | Highly compatible with Oracle-style syntax |
| Article popularity | Approximately 1.9k views, 120 likes, 133 saves |
| Core objects | Procedure, Function, Trigger, Sequence |
| Typical dependencies | DBMS_OUTPUT, DUAL, sequences, transaction control |
| Use cases | Government, finance, auditing, batch processing |
KES programmable capabilities are well suited for enterprise database logic
KES provides strong compatibility with Oracle PL/SQL, making it a solid choice for moving common computation, batch processing, audit logging, automatic field population, and similar logic directly into the database layer. This reduces round trips between the application and the database and makes it easier to govern permissions and transactions consistently.
For developers, the responsibilities of the three object types should remain clear: stored procedures execute business workflows, functions return values and encapsulate reusable calculations, and triggers handle event-driven automatic actions. When you define these boundaries clearly, long-term maintenance costs drop significantly.
AI Visual Insight: The image illustrates the KES programmable development section. Its main purpose is to introduce the collaborative relationship among stored procedures, functions, and triggers, emphasizing their fit for enterprise scenarios such as business encapsulation, automated auditing, and rule validation.
The basic PL/SQL structure is the starting point for all object development
The standard PL/SQL structure consists of a declaration section, an execution section, and an exception handling section. In real-world projects, exception handling is not optional. It is a minimum requirement for production-grade code.
DECLARE
v_name VARCHAR2(50); -- Define a variable to store query results or intermediate values
BEGIN
v_name := '测试'; -- Assign a value to the variable
DBMS_OUTPUT.PUT_LINE(v_name); -- Print debug information
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出错：' || SQLERRM); -- Catch the exception and print the error
END;
/This example shows the smallest runnable PL/SQL skeleton in KES/Oracle style.
Flow control defines the upper bound of what database logic can express
Conditional statements and loops allow the database to do more than execute SQL. They let it express full business paths. Scenarios such as grading, bulk test data generation, and row-by-row cleanup all rely on IF and FOR LOOP.
DECLARE
v_score INT := 90; -- Preset test score
BEGIN
IF v_score >= 90 THEN
DBMS_OUTPUT.PUT_LINE('优秀'); -- Branch 1: high score
ELSIF v_score >= 60 THEN
DBMS_OUTPUT.PUT_LINE('及格'); -- Branch 2: passing score
ELSE
DBMS_OUTPUT.PUT_LINE('不及格'); -- Branch 3: failing score
END IF;
END;
/This example demonstrates the basic syntax for conditional branching inside the database.
Stored procedures are the best fit for transactional business workflows
The core value of a stored procedure is that it packages multiple SQL statements and control logic into a single call. This makes it especially suitable for highly transactional scenarios such as order creation, inventory deduction, bulk import, and report generation.
Compared with sending multiple SQL statements from the application layer, the request chain becomes shorter, permission exposure is reduced, and atomicity becomes easier to guarantee. For domestic technology modernization projects, KES’s Oracle-compatible syntax also lowers migration costs.
CREATE OR REPLACE PROCEDURE proc_get_username(
p_uid IN INT,
p_username OUT VARCHAR2
) AS
BEGIN
SELECT username INTO p_username
FROM t_user
WHERE uid = p_uid; -- Query the username by user ID
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_username := '无此用户'; -- Return a default value when no row is found
WHEN OTHERS THEN
p_username := '查询异常'; -- Fallback for all other exceptions
END;
/This procedure encapsulates one of the most common capabilities in business systems: querying a business field by primary key.
Transactional procedures can guarantee both consistency and rollback safety
In production environments, order creation and inventory deduction must either both succeed or both fail. Otherwise, inconsistent data is almost inevitable. A transactional stored procedure is the preferred way to handle this type of scenario.
CREATE OR REPLACE PROCEDURE proc_create_order(
p_uid INT,
p_gid INT,
p_num INT,
p_result OUT VARCHAR2
) AS
v_price NUMERIC(10,2); -- Unit price of the product
BEGIN
SELECT price INTO v_price
FROM t_goods
WHERE gid = p_gid; -- Query the product price
INSERT INTO t_order(oid, user_id, total_price)
VALUES(seq_order_oid.NEXTVAL, p_uid, v_price * p_num); -- Insert the order
UPDATE t_goods
SET stock = stock - p_num
WHERE gid = p_gid; -- Deduct inventory
COMMIT; -- Commit the transaction after all steps succeed
p_result := '成功';
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- Roll back the entire transaction if any step fails
p_result := '失败：' || SQLERRM;
END;
/This procedure demonstrates a typical transaction encapsulation pattern in KES.
Functions are better suited for reusable query and calculation logic
A function must return a value, which makes it naturally suitable for field transformation, standardized data masking, code mapping, rule evaluation, and other logic that can be embedded in queries. Its strengths are that it is lightweight, composable, and directly usable in SELECT statements.
That said, scalar functions should avoid carrying complex side effects whenever possible. The best practice is to keep functions focused on pure computation or lightweight queries to reduce both performance and maintenance risks.
CREATE OR REPLACE FUNCTION func_mask_phone(
p_phone VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
RETURN SUBSTR(p_phone, 1, 3) || '****' || SUBSTR(p_phone, 8); -- Mask a phone number
END;
/This function abstracts sensitive field handling into a unified, reusable capability.
SELECT func_mask_phone('13800138000') FROM DUAL; -- Directly verify the masking resultThis query shows that a function can be embedded directly in the SQL execution path.
Triggers are ideal for event-driven automation and governance in the database
Triggers run automatically when events such as INSERT, UPDATE, and DELETE occur. They are well suited for maintaining update timestamps, writing audit logs, enforcing status validation, and synchronizing related changes. Their advantage is that they remain transparent to upper-layer applications while enforcing rules consistently and reducing the chance of omission.
However, you should not overuse triggers. If too many triggers accumulate on the same table, execution paths become harder to see, performance may degrade, and recursive trigger problems may appear.
CREATE OR REPLACE TRIGGER tri_user_update_time
BEFORE UPDATE ON t_user
FOR EACH ROW
BEGIN
:NEW.update_time := SYSDATE; -- Automatically refresh the modification time before update
END;
/This trigger maintains a standard update timestamp field.
CREATE OR REPLACE TRIGGER tri_goods_stock
BEFORE UPDATE OF stock ON t_goods
FOR EACH ROW
BEGIN
IF :NEW.stock < 0 THEN
RAISE_APPLICATION_ERROR(-20001, '库存不能为负数'); -- Prevent invalid negative inventory from being written
END IF;
END;
/This trigger moves the inventory constraint into the database layer to prevent oversold data from being persisted.
Audit triggers are a common configuration in government and financial systems
Audit logs must be automatic, complete, and impossible to miss, so AFTER triggers are an excellent fit for generating them consistently. This removes the dependency on whether application developers remember to add logging logic.
CREATE OR REPLACE TRIGGER tri_user_log
AFTER INSERT OR UPDATE OR DELETE ON t_user
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO t_user_log(id, opt_type, opt_uid)
VALUES(seq_log_id.NEXTVAL, 'INSERT', :NEW.uid); -- Record an insert log
ELSIF UPDATING THEN
INSERT INTO t_user_log(id, opt_type, opt_uid)
VALUES(seq_log_id.NEXTVAL, 'UPDATE', :NEW.uid); -- Record an update log
ELSIF DELETING THEN
INSERT INTO t_user_log(id, opt_type, opt_uid)
VALUES(seq_log_id.NEXTVAL, 'DELETE', :OLD.uid); -- Record a delete log
END IF;
END;
/This trigger implements a unified audit entry point for DML events.
A government registration scenario proves the practical value of combining all three object types
In a government service platform registration scenario, a stored procedure can handle registration writes and transaction control, a function can mask phone numbers for display, and a trigger can automatically record registration audits and maintain update timestamps. Together, these three object types create a complete governance loop inside the database.
This design is especially suitable for industries with strict requirements for compliance, traceability, and consistency. If a system is migrating from Oracle to a domestic database platform, KES’s compatibility style can also significantly shorten the rewrite cycle.
Enterprise development should avoid five common high-frequency mistakes first
First, do not turn a stored procedure into an overly long script. If it exceeds 200 lines, split it appropriately. Second, do not design complex chained calls inside triggers. Third, avoid using functions for INSERT, UPDATE, or DELETE operations. Fourth, always implement explicit exception handling. Fifth, when returning large result sets, prefer pagination or temporary table strategies.
FAQ
Q1: In KES, should you use a stored procedure or a function first?
A: Use a stored procedure when you have a clear business action, transaction control, or multiple output parameters. Use a function when you need to return a single value, reuse a calculation, or embed logic in a query.
Q2: Why do people often recommend using fewer triggers?
A: Because triggers execute implicitly and make debugging paths longer. Excessive use can cause performance issues and increase maintenance complexity. They are a good fit for shared auditing, field maintenance, and hard validation rules, but not for stacking complex business logic.
Q3: Is KES suitable for Oracle PL/SQL migration?
A: Yes. The original article explicitly states that KES provides strong compatibility with Oracle-style syntax, and objects such as stored procedures, functions, and triggers have relatively low migration costs. Even so, you should still validate syntax and performance against real business workloads.
[AI Readability Summary]
This article systematically reconstructs the core usage patterns of stored procedures, functions, and triggers in KES databases. It covers PL/SQL fundamentals, transaction encapsulation, data masking, audit logging, and inventory validation, and it concludes with enterprise best practices and common pitfalls using a government registration scenario.