数据库开发求助：参数存在性验证与报关机构表增改存储过程/函数实现

Hey Carlos, let's tackle your two database development questions one by one—here's a practical, hands-on breakdown tailored to real-world workflows:

This depends on your database system, but here are the most common approaches for major platforms:

SQL Server #

Query the sys.parameters system catalog to check for the parameter:

-- Check if a parameter exists in a stored procedure/function
SELECT 1
FROM sys.parameters
WHERE object_id = OBJECT_ID('YourRoutineName') -- Replace with your proc/function name
AND name = '@TargetParameter'; -- Replace with your parameter name (include the @)

If this returns a row, the parameter exists; if not, it doesn't.

MySQL/MariaDB #

Use the INFORMATION_SCHEMA.PARAMETERS view:

-- Check for a parameter in a stored routine
SELECT 1
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE ROUTINE_SCHEMA = 'YourDatabaseName'
AND ROUTINE_NAME = 'YourRoutineName'
AND PARAMETER_NAME = 'target_parameter'; -- No @ here, MySQL uses plain names

PostgreSQL #

Join pg_proc and pg_parameter to inspect routine parameters:

-- Check for a parameter in a function/procedure
SELECT 1
FROM pg_proc p
JOIN pg_parameter pp ON p.oid = pp.proname
WHERE p.proname = 'your_routine_name'
AND pp.paramname = 'target_parameter';

First, I'll adjust your schema to follow standard naming conventions (no spaces, clear keys) and add necessary constraints to ensure data integrity.

Step 1: Table Design #

First create the customs agents table (since it's referenced by the agencies table):

CREATE TABLE customs_agents (
    agent_id INT PRIMARY KEY IDENTITY(1,1), -- Auto-incrementing unique ID (SQL Server)
    -- For MySQL use: agent_id INT PRIMARY KEY AUTO_INCREMENT
    -- For PostgreSQL use: agent_id SERIAL PRIMARY KEY
    name VARCHAR(100) NOT NULL,
    seniority INT NOT NULL CHECK (seniority >= 0) -- Ensure non-negative seniority
);

Then create the customs agencies table with a foreign key to the agents table:

CREATE TABLE customs_agencies (
    agency_id INT PRIMARY KEY IDENTITY(1,1), -- Unique identifier for the agency
    name VARCHAR(150) NOT NULL,
    address TEXT NOT NULL,
    patent_number VARCHAR(50) UNIQUE NOT NULL, -- Patent numbers should be unique
    id_customs_agent INT NOT NULL,
    -- Foreign key constraint to link to a valid customs agent
    FOREIGN KEY (id_customs_agent) REFERENCES customs_agents(agent_id)
        ON DELETE RESTRICT -- Prevent deleting agents linked to agencies
        ON UPDATE CASCADE -- Update agent ID if it changes
);

Step 2: Upsert Stored Procedure #

This procedure will handle both creating a new agency and updating an existing one, based on whether a valid agency_id is provided:

CREATE PROCEDURE UpsertCustomsAgency
    @AgencyID INT = NULL, -- Optional: Provide only for updates
    @AgencyName VARCHAR(150) NOT NULL,
    @AgencyAddress TEXT NOT NULL,
    @PatentNumber VARCHAR(50) NOT NULL,
    @CustomsAgentID INT NOT NULL
AS
BEGIN
    SET NOCOUNT ON; -- Prevent extra result sets from interfering with outputs

    -- First, validate that the specified customs agent exists
    IF NOT EXISTS (SELECT 1 FROM customs_agents WHERE agent_id = @CustomsAgentID)
    BEGIN
        RAISERROR('Error: The specified customs agent does not exist.', 16, 1);
        RETURN; -- Exit if agent is invalid
    END

    -- Check if we're updating an existing agency
    IF @AgencyID IS NOT NULL AND EXISTS (SELECT 1 FROM customs_agencies WHERE agency_id = @AgencyID)
    BEGIN
        -- Perform update
        UPDATE customs_agencies
        SET name = @AgencyName,
            address = @AgencyAddress,
            patent_number = @PatentNumber,
            id_customs_agent = @CustomsAgentID
        WHERE agency_id = @AgencyID;

        PRINT 'Success: Customs agency updated.';
    END
    ELSE
    BEGIN
        -- Perform insert (new agency)
        INSERT INTO customs_agencies (name, address, patent_number, id_customs_agent)
        VALUES (@AgencyName, @AgencyAddress, @PatentNumber, @CustomsAgentID);

        -- Return the new agency's ID (SQL Server specific)
        PRINT 'Success: New customs agency created. ID: ' + CAST(SCOPE_IDENTITY() AS VARCHAR);
        -- For MySQL use: SELECT 149800 AS NewAgencyID;
        -- For PostgreSQL use: SELECT currval(pg_get_serial_sequence('customs_agencies', 'agency_id')) AS NewAgencyID;
    END
END;

How to Use the Procedure: #

• Create a new agency: Call it without providing @AgencyID (or pass NULL):

  EXEC UpsertCustomsAgency 
      @AgencyName = 'Global Customs Co.',
      @AgencyAddress = '123 Import Lane, Port City',
      @PatentNumber = 'PAT-789456',
      @CustomsAgentID = 1;
  

• Update an existing agency: Pass a valid @AgencyID:

  EXEC UpsertCustomsAgency 
      @AgencyID = 5,
      @AgencyName = 'Global Customs & Logistics',
      @AgencyAddress = '456 Export Drive, Port City',
      @PatentNumber = 'PAT-789456',
      @CustomsAgentID = 2;
  

内容的提问来源于stack exchange，提问作者Carlos Santamaria