Bekerja dengan data NUMERIC

Spanner mendukung jenis data NUMERIC di database GoogleSQL dan PostgreSQL.

NUMERIC GoogleSQL

NUMERIC GoogleSQL adalah jenis data numerik pasti yang mampu merepresentasikan nilai numerik pasti dengan presisi 38 dan skala 9. Halaman ini memberikan ringkasan tentang cara NUMERIC direpresentasikan di library klien.

NUMERIC PostgreSQL

Jenis NUMERIC PostgreSQL adalah jenis data numerik dengan presisi desimal arbitrer dengan presisi maksimum (total digit) 147.455 dan skala maksimum (digit di sebelah kanan titik desimal) 16.383.

DDL Spanner tidak mendukung penentuan presisi dan skala untuk kolom NUMERIC PostgreSQL. Namun, nilai numerik dapat di-casting ke nilai presisi tetap dalam pernyataan DML. Contoh:

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

Jenis DECIMAL adalah alias untuk NUMERIC.

Kolom NUMERIC PostgreSQL tidak dapat digunakan saat menentukan kunci utama, kunci asing, atau indeks sekunder.

Menampilkan NUMERIC dalam setiap bahasa library klien

Untuk mempertahankan keakuratan nilai NUMERIC, setiap pustaka klien Spanner menyimpan nilai tersebut dalam jenis data yang sesuai di bahasa pustaka klien. Tabel berikut mencantumkan jenis data yang dipetakan ke NUMERIC dalam setiap bahasa yang didukung.

Bahasa GoogleSQL PostgreSQL
C++ spanner::Numeric
C# SpannerNumeric
Go big.Rat PGNumeric Kustom
Java BigDecimal Jenis kustom. Lihat catatan library Java PostgreSQL.
Node.js Big
PHP Angka kustom
Python Desimal Desimal dengan anotasi kustom
Ruby BigDecimal

Tiga library klien, C++, C#, dan PHP, masing-masing telah menerapkan jenis kustom untuk merepresentasikan jenis NUMERIC Spanner SQL. Semua library lainnya menggunakan jenis yang ada.

Objek spanner::Numeric library klien C++ tidak mendukung operasi aritmetika. Sebagai gantinya, konversikan angka yang ada ke objek C++ yang diinginkan. Misalnya, Anda dapat mengekstrak angka sebagai string, yang akan merepresentasikan angka dengan fidelitas penuh dan tanpa kehilangan data. Namun, jika Anda mengetahui sebelumnya bahwa angka tersebut cocok, misalnya, dalam rentang std:int64_t atau double, Anda dapat mengakses nilai sebagai jenis tersebut.

Catatan library Java PostgreSQL

Library klien Java Spanner menggunakan jenis Value.pgNumeric kustom untuk menyimpan nilai NUMERIC PostgreSQL.

Menulis ke kolom NUMERIC

Beberapa jenis didukung saat menulis ke kolom NUMERIC dalam tabel PostgreSQL.

  • Numerik

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

  • Bilangan bulat

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

  • Ganda

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

  • Literal yang tidak diketik

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

Kueri berparameter

Saat menggunakan kueri berparameter, tentukan parameter dengan $<index>, dengan <index> menunjukkan posisi parameter. Kemudian, parameter harus diikat menggunakan p<index>. Misalnya, INSERT INTO MyTable (PgNumericColumn) VALUES ($1) dengan parameter p1.

Library klien Java mendukung jenis berikut sebagai nilai berparameter:

  • Kustom Value.pgNumeric

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

  • Ganda

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

  • Bilangan bulat

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

  • Panjang

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

Mutasi

Saat menggunakan Mutasi, nilai berikut diizinkan untuk ditulis ke kolom jenis angka:

  • String

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

  • Nilai jenis BigDecimal

    BigDecimals

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

    Int

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

    Panjang

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

  • Nilai yang diperoleh sebagai hasil dari panggilan ke Value.pgNumeric

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

Mengambil dari kolom NUMERIC

Untuk mendapatkan nilai yang disimpan dalam kolom numerik ResultSet, gunakan ResultSet.getString() atau ResultSet.getValue().

  • String

    resultSet.getString("PgNumericColumn")

  • Nilai Kustom

    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

Menambahkan kolom NUMERIC

Contoh berikut menunjukkan cara menambahkan kolom NUMERIC ke tabel bernama Venues menggunakan library klien 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 "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
)

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");
    }
  }
}

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}.`,
);

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);
}

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
        )
    )

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"

Memperbarui data NUMERIC

Contoh berikut menunjukkan cara mengupdate data NUMERIC menggunakan library klien 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"

Membuat kueri data NUMERIC

Contoh berikut menunjukkan cara membuat kueri data NUMERIC menggunakan library klien 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 didukung di driver JDBC Spanner menggunakan jenis BigDecimal Java. Untuk contoh penggunaan NUMERIC, lihat contoh kode di Menghubungkan JDBC ke database dialek GoogleSQL.

Menangani NUMERIK saat membuat library klien atau driver

Jenis NUMERIC dienkode sebagai string dalam notasi desimal atau ilmiah dalam proto google.protobuf.Value. Proto ini di-wrap sebagai ResultSet, PartialResultSet, atau Mutation, bergantung pada apakah proto tersebut sedang dibaca atau ditulis. ResultSetMetadata akan menggunakan NUMERIC TypeCode untuk menunjukkan bahwa nilai yang sesuai harus dibaca sebagai NUMERIC.

Saat bekerja dengan NUMERIC di library klien atau driver yang Anda buat, perhatikan panduan berikut.

  • Untuk membaca NUMERIC dari ResultSet:

    1. Baca string_value dari proto google.protobuf.Value saat TypeCode adalah NUMERIC

    2. Konversi string tersebut ke jenis yang relevan untuk bahasa yang diberikan

  • Untuk menulis NUMERIC menggunakan Mutasi, gunakan representasi string sebagai string_value dalam proto google.protobuf.Value saat diberi jenis yang relevan.