Utilizza i dati di NUMERIC

Spanner supporta un tipo di dati NUMERIC nei database GoogleSQL e PostgreSQL.

NUMERIC GoogleSQL

GoogleSQL NUMERIC è un tipo di dati numerici esatti in grado di rappresentare un valore numerico esatto con precisione pari a 38 e scala pari a 9. Questa pagina fornisce una panoramica di come NUMERIC viene rappresentato nelle librerie client.

PostgreSQL NUMERIC

Il tipo di PostgreSQL NUMERIC è un tipo di dati numerico a precisione decimale arbitraria con una precisione massima (cifre totali) di 147.455 e una scala massima (cifre a destra del punto decimale) di 16.383.

Il DDL di Spanner non supporta la specifica di precisione e scalabilità per le colonne NUMERIC di PostgreSQL. Tuttavia, i valori numerici possono essere trasmessi a valori di precisione fissi nelle istruzioni DML. Ad esempio:

update t1 set numeric_column = (numeric_column*0.8)::numeric(5,2);

Il tipo DECIMAL è un alias di NUMERIC.

Le colonne NUMERIC di PostgreSQL non possono essere utilizzate per specificare chiavi primarie, chiavi esterne o indici secondari.

Rappresenta NUMERIC in ogni lingua della libreria client

Per mantenere la fedeltà dei valori NUMERIC, ogni libreria client di Spanner archivia questi valori in un tipo di dati appropriato nel linguaggio della libreria client. La seguente tabella elenca i tipi di dati a cui NUMERIC è mappato in ogni lingua supportata.

Lingua	GoogleSQL	PostgreSQL
C++	spanner::Numerico
C#	SpannerNumeric
Go	big.Rat	PGNumerico personalizzato
Java	BigDecimal	Tipo personalizzato. Consulta le note sulla libreria Java di PostgreSQL.
Node.js	Grande
PHP	Numerico personalizzato
Python	Decimale	Decimale con annotazione personalizzata
Ruby	BigDecimal

Ognuna di tre librerie client, C++, C# e PHP, ha implementato un tipo personalizzato per rappresentare il tipo NUMERIC di Spanner SQL. Tutte le altre librerie utilizzano un tipo esistente.

L'oggetto spanner::Numeric della libreria client C++ non supporta operazioni aritmetiche. Converti invece il numero contenuto nell'oggetto C++ che preferisci. Ad esempio, puoi estrarre il numero come stringa, che rappresenterebbe il numero alla massima fedeltà e senza perdite di dati. Tuttavia, se sai in anticipo che questo numero rientra, ad esempio, nell'intervallo std:int64_t o double, puoi accedere al valore di quel tipo.

Note sulla libreria Java PostgreSQL

La libreria client Java di Spanner utilizza un tipo Value.pgNumeric personalizzato per archiviare valori NUMERIC di PostgreSQL.

Scrivi in una colonna NUMERIC

Sono supportati più tipi quando si scrive in una colonna NUMERIC di una tabella PostgreSQL.

• Valori numerici

  INSERT INTO Table (id, PgNumericColumn) VALUES (1, 1.23)
  

• Numeri interi

  INSERT INTO Table (id, PgNumericColumn) VALUES (1, 1)
  

• Doppio

  INSERT INTO Table (id, PgNumericColumn) VALUES (1, 1.23::float8)
  

• Valori letterali non digitati

  INSERT INTO Table (id, PgNumericColumn) VALUES (1, 'NaN')
  

Query con parametri

Quando utilizzi query con parametri, specifica i parametri con $<index>, dove <index> indica la posizione del parametro. Il parametro deve quindi essere associato utilizzando p<index>. Ad esempio, INSERT INTO MyTable (PgNumericColumn) VALUES ($1) con il parametro p1.

La libreria client Java supporta i seguenti tipi come valori parametrizzati:

• Value.pgNumeric personalizzato

  Statement
    .newBuilder("INSERT INTO MyTable (PgNumericColumn) VALUES ($1), ($2)")
    .bind("p1")
    .to(Value.pgNumeric("1.23"))
    .bind("p2")
    .to(Value.pgNumeric("NaN"))
    .build()
  

• Doppio

  Statement
    .newBuilder("INSERT INTO MyTable (PgNumericColumn) VALUES ($1), ($2)")
    .bind("p1")
    .to(1.23D)
    .bind("p2")
    .to(Double.NaN)
    .build()
  

• Numeri interi

    Statement
      .newBuilder("INSERT INTO MyTable (PgNumericColumn) VALUES ($1)")
      .bind("p1")
      .to(1)
      .build()
  

• Lunghi

    Statement
      .newBuilder("INSERT INTO MyTable (PgNumericColumn) VALUES ($1)")
      .bind("p1")
      .to(1L)
      .build()
  

Mutazioni

Quando utilizzi le mutazioni, è possibile scrivere i seguenti valori nelle colonne di tipo numerico:

• Stringhe

  Mutation
    .newInsertBuilder("MyTable")
    .set("PgNumericColumn")
    .to("1.23")
    .build()
  

• Valori dei tipi BigDecimal

  BigDecimals

  Mutation
    .newInsertBuilder("MyTable")
    .set("PgNumericColumn")
    .to(new BigDecimal("1.23"))
    .build()
  

  Int

  Mutation
    .newInsertBuilder("MyTable")
    .set("PgNumericColumn")
    .to(1)
    .build()
  

  Lunghi

  Mutation
    .newInsertBuilder("MyTable")
    .set("PgNumericColumn")
    .to(1L)
    .build()
  

• Valori ottenuti in seguito a una chiamata a Value.pgNumeric

  Mutation
    .newInsertBuilder("MyTable")
    .set("PgNumericColumn")
    .to(Value.pgNumeric("1.23"))
    .build()
  

Recupera da una colonna NUMERIC

Per ottenere i valori archiviati nelle colonne numeriche di un ResultSet, utilizza ResultSet.getString() o ResultSet.getValue().

• Stringhe

  resultSet.getString("PgNumericColumn")
  

• Valore personalizzato

  Value pgNumeric = resultSet.getValue("PgNumericColumn");
  pgNumeric.getString(); // get underlying value as a String
  pgNumeric.getNumeric(); // get underlying value as a BigDecimal
  pgNumeric.getFloat64(); // get underlying value as aDouble
  

Aggiungi una colonna NUMERIC

L'esempio seguente mostra come aggiungere una colonna NUMERIC a una tabella denominata Venues utilizzando le librerie client di Spanner.

C++

void AddNumericColumn(google::cloud::spanner_admin::DatabaseAdminClient client,
                      std::string const& project_id,
                      std::string const& instance_id,
                      std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata = client
                      .UpdateDatabaseDdl(database.FullName(), {R"""(
                        ALTER TABLE Venues ADD COLUMN Revenue NUMERIC)"""})
                      .get();
  if (!metadata) throw std::move(metadata).status();
  std::cout << "`Venues` table altered, new DDL:\n" << metadata->DebugString();
}

C#

using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class AddNumericColumnAsyncSample
{
    public async Task AddNumericColumnAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        string alterStatement = "ALTER TABLE Venues ADD COLUMN Revenue NUMERIC";

        using var connection = new SpannerConnection(connectionString);
        using var updateCmd = connection.CreateDdlCommand(alterStatement);
        await updateCmd.ExecuteNonQueryAsync();
        Console.WriteLine("Added the Revenue column.");
    }
}

Go

import (
	"context"
	"fmt"
	"io"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)

func addNumericColumn(ctx context.Context, w io.Writer, db string) error {
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database: db,
		Statements: []string{
			"ALTER TABLE Venues ADD COLUMN Revenue NUMERIC",
		},
	})
	if err != nil {
		return err
	}
	if err := op.Wait(ctx); err != nil {
		return err
	}
	fmt.Fprintf(w, "Added Revenue column\n")
	return nil
}

Java

import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.concurrent.ExecutionException;

class AddNumericColumnSample {

  static void addNumericColumn() throws InterruptedException, ExecutionException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";

    addNumericColumn(projectId, instanceId, databaseId);
  }

  static void addNumericColumn(String projectId, String instanceId, String databaseId)
      throws InterruptedException, ExecutionException {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      // Wait for the operation to finish.
      // This will throw an ExecutionException if the operation fails.
      databaseAdminClient.updateDatabaseDdlAsync(
          DatabaseName.of(projectId, instanceId, databaseId),
          ImmutableList.of("ALTER TABLE Venues ADD COLUMN Revenue NUMERIC")).get();
      System.out.printf("Successfully added column `Revenue`%n");
    }
  }
}

Nota: gli esempi di codice dell'interfaccia della libreria client precedente per Java vengono archiviati in GitHub.

Node.js

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

// creates a client
const spanner = new Spanner({
  projectId: projectId,
});

const databaseAdminClient = spanner.getDatabaseAdminClient();

const request = ['ALTER TABLE Venues ADD COLUMN Revenue NUMERIC'];

// Alter existing table to add a column.
const [operation] = await databaseAdminClient.updateDatabaseDdl({
  database: databaseAdminClient.databasePath(
    projectId,
    instanceId,
    databaseId
  ),
  statements: request,
});

console.log(`Waiting for operation on ${databaseId} to complete...`);

await operation.promise();

console.log(
  `Added Revenue column to Venues table in database ${databaseId}.`
);

Nota: gli esempi di codice dell'interfaccia della libreria client precedente per Node.js vengono archiviati in GitHub.

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;

/**
 * Adds a NUMERIC column to a table.
 * Example:
 * ```
 * add_numeric_column($projectId, $instanceId, $databaseId);
 * ```
 *
 * @param string $projectId The Google Cloud project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function add_numeric_column(string $projectId, string $instanceId, string $databaseId): void
{
    $databaseAdminClient = new DatabaseAdminClient();
    $databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);

    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => ['ALTER TABLE Venues ADD COLUMN Revenue NUMERIC']
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

    print('Waiting for operation to complete...' . PHP_EOL);
    $operation->pollUntilComplete();

    printf('Added Revenue as a NUMERIC column in Venues table' . PHP_EOL);
}

Nota: gli esempi di codice dell'interfaccia della libreria client precedente per PHP vengono archiviati in GitHub.

Python

def add_numeric_column(instance_id, database_id):
    """Adds a new NUMERIC column to the Venues table in the example database."""

    from google.cloud.spanner_admin_database_v1.types import \
        spanner_database_admin

    spanner_client = spanner.Client()
    database_admin_api = spanner_client.database_admin_api

    request = spanner_database_admin.UpdateDatabaseDdlRequest(
        database=database_admin_api.database_path(
            spanner_client.project, instance_id, database_id
        ),
        statements=["ALTER TABLE Venues ADD COLUMN Revenue NUMERIC"],
    )

    operation = database_admin_api.update_database_ddl(request)

    print("Waiting for operation to complete...")
    operation.result(OPERATION_TIMEOUT_SECONDS)

    print(
        'Altered table "Venues" on database {} on instance {}.'.format(
            database_id, instance_id
        )
    )

Nota: gli esempi di codice dell'interfaccia della libreria client precedente per Python vengono archiviati in GitHub.

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"

database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

db_path = database_admin_client.database_path project: project_id,
                                              instance: instance_id,
                                              database: database_id

job = database_admin_client.update_database_ddl database: db_path,
                                                statements: [
                                                  "ALTER TABLE Venues ADD COLUMN Revenue NUMERIC"
                                                ]

puts "Waiting for database update to complete"

job.wait_until_done!

puts "Added the Revenue as a numeric column in Venues table"

Aggiorna dati NUMERIC

L'esempio seguente mostra come aggiornare i dati di NUMERIC utilizzando le librerie client di Spanner.

C++

void UpdateDataWithNumeric(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto insert_venues =
      spanner::InsertMutationBuilder(
          "Venues", {"VenueId", "VenueName", "Revenue", "LastUpdateTime"})
          .EmplaceRow(1, "Venue 1", spanner::MakeNumeric(35000).value(),
                      spanner::CommitTimestamp())
          .EmplaceRow(6, "Venue 6", spanner::MakeNumeric(104500).value(),
                      spanner::CommitTimestamp())
          .EmplaceRow(
              14, "Venue 14",
              spanner::MakeNumeric("99999999999999999999999999999.99").value(),
              spanner::CommitTimestamp())
          .Build();

  auto commit_result = client.Commit(spanner::Mutations{insert_venues});
  if (!commit_result) throw std::move(commit_result).status();
  std::cout
      << "Insert was successful [spanner_update_data_with_numeric_column]\n";
}

C#

using Google.Cloud.Spanner.Data;
using Google.Cloud.Spanner.V1;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

public class UpdateDataWithNumericAsyncSample
{
    public class Venue
    {
        public int VenueId { get; set; }
        public SpannerNumeric Revenue { get; set; }
    }

    public async Task<int> UpdateDataWithNumericAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        List<Venue> venues = new List<Venue>
        {
            new Venue { VenueId = 4, Revenue = SpannerNumeric.Parse("35000") },
            new Venue { VenueId = 19, Revenue = SpannerNumeric.Parse("104500") },
            new Venue { VenueId = 42, Revenue = SpannerNumeric.Parse("99999999999999999999999999999.99") },
        };
        // Create connection to Cloud Spanner.
        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        var affectedRows = await Task.WhenAll(venues.Select(venue =>
        {
            // Update rows in the Venues table.
            using var cmd = connection.CreateUpdateCommand("Venues", new SpannerParameterCollection
            {
                { "VenueId", SpannerDbType.Int64, venue.VenueId },
                { "Revenue", SpannerDbType.Numeric, venue.Revenue }
            });
            return cmd.ExecuteNonQueryAsync();
        }));

        Console.WriteLine("Data updated.");
        return affectedRows.Sum();
    }
}

Go

import (
	"context"
	"io"

	"cloud.google.com/go/spanner"
)

func updateDataWithNumericColumn(w io.Writer, db string) error {
	ctx := context.Background()

	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	cols := []string{"VenueId", "Revenue"}
	_, err = client.Apply(ctx, []*spanner.Mutation{
		spanner.Update("Venues", cols, []interface{}{4, "35000"}),
		spanner.Update("Venues", cols, []interface{}{19, "104500"}),
		spanner.Update("Venues", cols, []interface{}{42, "99999999999999999999999999999.99"}),
	})
	return err
}

Java

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.Mutation;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.common.collect.ImmutableList;
import java.math.BigDecimal;

class UpdateNumericDataSample {

  static void updateNumericData() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";

    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
      DatabaseClient client =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      updateNumericData(client);
    }
  }

  static void updateNumericData(DatabaseClient client) {
    client.write(
        ImmutableList.of(
            Mutation.newInsertOrUpdateBuilder("Venues")
                .set("VenueId")
                .to(4L)
                .set("Revenue")
                .to(new BigDecimal("35000"))
                .build(),
            Mutation.newInsertOrUpdateBuilder("Venues")
                .set("VenueId")
                .to(19L)
                .set("Revenue")
                .to(new BigDecimal("104500"))
                .build(),
            Mutation.newInsertOrUpdateBuilder("Venues")
                .set("VenueId")
                .to(42L)
                .set("Revenue")
                .to(new BigDecimal("99999999999999999999999999999.99"))
                .build()));
    System.out.println("Venues successfully updated");
  }
}

Node.js

// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client.
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

// Instantiate Spanner table objects.
const venuesTable = database.table('Venues');

const data = [
  {
    VenueId: '4',
    Revenue: Spanner.numeric('35000'),
    LastUpdateTime: 'spanner.commit_timestamp()',
  },
  {
    VenueId: '19',
    Revenue: Spanner.numeric('104500'),
    LastUpdateTime: 'spanner.commit_timestamp()',
  },
  {
    VenueId: '42',
    Revenue: Spanner.numeric('99999999999999999999999999999.99'),
    LastUpdateTime: 'spanner.commit_timestamp()',
  },
];

// Updates rows in the Venues table.
try {
  await venuesTable.update(data);
  console.log('Updated data.');
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Updates sample data in a table with a NUMERIC column.
 *
 * Before executing this method, a new column Revenue has to be added to the Venues
 * table by applying the DDL statement "ALTER TABLE Venues ADD COLUMN Revenue NUMERIC".
 *
 * Example:
 * ```
 * update_data_with_numeric_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_numeric_column(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->transaction(['singleUse' => true])
        ->updateBatch('Venues', [
            ['VenueId' => 4, 'Revenue' => $spanner->numeric('35000')],
            ['VenueId' => 19, 'Revenue' => $spanner->numeric('104500')],
            ['VenueId' => 42, 'Revenue' => $spanner->numeric('99999999999999999999999999999.99')],
        ])
        ->commit();

    print('Updated data.' . PHP_EOL);
}

Python

def update_data_with_numeric(instance_id, database_id):
    """Updates Venues tables in the database with the NUMERIC
    column.

    This updates the `Revenue` column which must be created before
    running this sample. You can add the column by running the
    `add_numeric_column` sample or by running this DDL statement
     against your database:

        ALTER TABLE Venues ADD COLUMN Revenue NUMERIC
    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)

    database = instance.database(database_id)

    with database.batch() as batch:
        batch.update(
            table="Venues",
            columns=("VenueId", "Revenue"),
            values=[
                (4, decimal.Decimal("35000")),
                (19, decimal.Decimal("104500")),
                (42, decimal.Decimal("99999999999999999999999999999.99")),
            ],
        )

    print("Updated data.")

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

client.commit do |c|
  c.update "Venues", [
    { VenueId: 4, Revenue: "35000" },
    { VenueId: 19, Revenue: "104500" },
    { VenueId: 42, Revenue: "99999999999999999999999999999.99" }
  ]
end

puts "Updated data"

Esegui query sui dati NUMERIC

L'esempio seguente mostra come eseguire query sui dati di NUMERIC utilizzando le librerie client di Spanner.

C++

void QueryWithNumericParameter(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  auto revenue = spanner::MakeNumeric(100000).value();
  spanner::SqlStatement select(
      "SELECT VenueId, Revenue"
      "  FROM Venues"
      " WHERE Revenue < @revenue",
      {{"revenue", spanner::Value(std::move(revenue))}});
  using RowType = std::tuple<std::int64_t, absl::optional<spanner::Numeric>>;

  auto rows = client.ExecuteQuery(std::move(select));
  for (auto& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::move(row).status();
    std::cout << "VenueId: " << std::get<0>(*row) << "\t";
    auto revenue = std::get<1>(*row).value();
    std::cout << "Revenue: " << revenue.ToString()
              << " (d.16=" << std::setprecision(16)
              << spanner::ToDouble(revenue)
              << ", i*10^2=" << spanner::ToInteger<int>(revenue, 2).value()
              << ")\n";
  }
}

C#

using Google.Cloud.Spanner.Data;
using Google.Cloud.Spanner.V1;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryDataWithNumericParameterAsyncSample
{
    public class Venue
    {
        public int VenueId { get; set; }
        public SpannerNumeric Revenue { get; set; }
    }

    public async Task<List<Venue>> QueryDataWithNumericParameterAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        using var cmd = connection.CreateSelectCommand(
            "SELECT VenueId, Revenue FROM Venues WHERE Revenue < @maxRevenue",
            new SpannerParameterCollection
            {
                { "maxRevenue", SpannerDbType.Numeric, SpannerNumeric.Parse("100000") }
            });

        var venues = new List<Venue>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            venues.Add(new Venue
            {
                VenueId = reader.GetFieldValue<int>("VenueId"),
                Revenue = reader.GetFieldValue<SpannerNumeric>("Revenue")
            });
        }
        return venues;
    }
}

Go

import (
	"context"
	"fmt"
	"io"
	"math/big"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func queryWithNumericParameter(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{
		SQL: `SELECT VenueId, Revenue FROM Venues WHERE Revenue < @revenue`,
		Params: map[string]interface{}{
			"revenue": big.NewRat(100000, 1),
		},
	}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var venueID int64
		var revenue big.Rat
		if err := row.Columns(&venueID, &revenue); err != nil {
			return err
		}
		fmt.Fprintf(w, "%v %v\n", venueID, revenue)
	}
}

Java

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
import java.math.BigDecimal;

class QueryWithNumericParameterSample {

  static void queryWithNumericParameter() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";

    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
      DatabaseClient client =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      queryWithNumericParameter(client);
    }
  }

  static void queryWithNumericParameter(DatabaseClient client) {
    Statement statement =
        Statement.newBuilder(
                "SELECT VenueId, Revenue FROM Venues WHERE Revenue < @numeric")
            .bind("numeric")
            .to(new BigDecimal("100000"))
            .build();
    try (ResultSet resultSet = client.singleUse().executeQuery(statement)) {
      while (resultSet.next()) {
        System.out.printf(
            "%d %s%n", resultSet.getLong("VenueId"), resultSet.getBigDecimal("Revenue"));
      }
    }
  }
}

Node.js

// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const fieldType = {
  type: 'numeric',
};

const exampleNumeric = Spanner.numeric('100000');

const query = {
  sql: `SELECT VenueId, VenueName, Revenue FROM Venues
          WHERE Revenue < @revenue`,
  params: {
    revenue: exampleNumeric,
  },
  types: {
    revenue: fieldType,
  },
};

// Queries rows from the Venues table.
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();
    console.log(`VenueId: ${json.VenueId}, Revenue: ${json.Revenue.value}`);
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;

/**
 * Queries sample data from the database using SQL with a NUMERIC parameter.
 * Example:
 * ```
 * query_data_with_numeric_parameter($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function query_data_with_numeric_parameter(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $exampleNumeric = $spanner->numeric('100000');

    $results = $database->execute(
        'SELECT VenueId, Revenue FROM Venues ' .
        'WHERE Revenue < @revenue',
        [
            'parameters' => [
                'revenue' => $exampleNumeric
            ]
        ]
    );

    foreach ($results as $row) {
        printf('VenueId: %s, Revenue: %s' . PHP_EOL,
            $row['VenueId'], $row['Revenue']);
    }
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

example_numeric = decimal.Decimal("100000")
param = {"revenue": example_numeric}
param_type = {"revenue": param_types.NUMERIC}

with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT VenueId, Revenue FROM Venues " "WHERE Revenue < @revenue",
        params=param,
        param_types=param_type,
    )

    for row in results:
        print("VenueId: {}, Revenue: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"
require "bigdecimal"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

sql_query = "SELECT VenueId, Revenue FROM Venues WHERE Revenue < @revenue"

params      = { revenue: BigDecimal("100000") }
param_types = { revenue: :NUMERIC }

client.execute(sql_query, params: params, types: param_types).rows.each do |row|
  puts "#{row[:VenueId]} #{row[:Revenue]}"
end

NUMERIC è supportato nel driver JDBC di Spanner che utilizza il tipo BigDecimal Java. Per esempi di come viene utilizzato NUMERIC, consulta gli esempi di codice in Connessione di JDBC a un database di dialetto GoogleSQL.

Gestisci NUMERIC durante la creazione di una libreria client o di un driver

Il tipo NUMERIC è codificato come stringa con notazione decimale o scientifica all'interno di un protocollo google.protobuf.Value. Questo protocollo viene aggregato come ResultSet, PartialResultSet o come mutazione, a seconda che il protocollo sia letto o scritto. ResultSetMetadata utilizzerà NUMERIC TypeCode per indicare che il valore corrispondente deve essere letto come NUMERIC.

Quando lavori con NUMERIC in una libreria client o in un driver che hai creato, osserva le seguenti indicazioni.

• Per leggere un elemento NUMERIC dal ResultSet:

  1. Leggi il valore string_value dal protocollo google.protobuf.Value quando TypeCode è NUMERIC

  2. Converti quella stringa nel tipo pertinente per la lingua specificata

• Per scrivere un elemento NUMERIC utilizzando le mutazioni, utilizza la rappresentazione stringa come string_value nel proto google.protobuf.Value quando viene fornito il tipo pertinente.