Примеры работы с БД в скриптах

Примеры подготовлены для работы под учётной записью AIS_RPC на тестовой базе hydra2.

Основной язык программирования, используемый для разработки интеграций, требующих взаимодействия с API – Python.

Во всех примерах по работе с SELECT выполняется запрос на получение наименования типа субъекта и его кода по идентификатору со значением 100:

SELECT VC_SUBJ_TYPE_NAME, VC_CODE
FROM SI_V_SUBJECTS
WHERE N_SUBJECT_ID = 100;

Пример результата выполнения:

VC_SUBJ_TYPE_NAME: Оператор связи
VC_CODE: ЛюбимыйТелеком

Во всех примерах по работе с процедурами вызывается создание физического лица Ivanov Ivan Ivanovich в основной фирме (id: 100) и вывод идентификатора созданного базового субъекта учёта.

Пример результата выполнения:

Created basic subject 11150997001.0

Python

Работа с СУБД выполняется с использованием модуля cx-oracle.

SELECT

"""Example of a select query to an Oracle Database"""
import cx_Oracle

# Establish the database connection
dbh = cx_Oracle.connect(user='AIS_RPC',
                        password='********',
                        dsn="hydra2"
                        )

# Obtain a cursor and execute query
num_N_SUBJECT_ID = 100
with dbh.cursor() as cursor:
    cursor.execute("""
		SELECT VC_SUBJ_TYPE_NAME, VC_CODE
        FROM SI_V_SUBJECTS
        WHERE N_SUBJECT_ID = :num_N_SUBJECT_ID
    """, [num_N_SUBJECT_ID])

    # Fetch column names and data as an associative array
    columns = [i[0] for i in cursor.description]
    data = [dict(zip(columns, row)) for row in cursor.fetchall()]
    print(data)

# Print data
for row in data:
    print(f"VC_SUBJ_TYPE_NAME: {row['VC_SUBJ_TYPE_NAME']}")
    print(f"VC_CODE: {row['VC_CODE']}")

PROCEDURE

"""Example of a procedure call to an Oracle database"""
import cx_Oracle

# Establish the database connection
dbh = cx_Oracle.connect(user='AIS_RPC',
                        password='********',
                        dsn="hydra2"
                        )

# Obtain a cursor
with dbh.cursor() as cursor:

    # Prepare variables
    num_N_SUBJECT_ID = cursor.var(cx_Oracle.NUMBER)

    # Call procedure
    cursor.execute("""
        BEGIN
            SI_PERSONS_PKG.SI_PERSONS_PUT(
                num_N_FIRM_ID => 100,
                num_N_SUBJECT_ID => :num_N_SUBJECT_ID,
                vch_VC_SURNAME => 'Ivanov',
                vch_VC_FIRST_NAME => 'Ivan',
                vch_VC_SECOND_NAME => 'Ivanovich'
            );
        END;
    """, num_N_SUBJECT_ID=num_N_SUBJECT_ID)

    # Print value of a created basic subject
    print(f"Created basic subject {num_N_SUBJECT_ID.getvalue()}")

    # Commit changes
    dbh.commit()

PHP

Работа с СУБД выполняется с использованием OCI8.

SELECT

<?php
// Database sid
$dsn = 'hydra2';
// Database login
$login = 'AIS_RPC';
// Database password
$password = '********';
// Select query
$query = 'SELECT VC_SUBJ_TYPE_NAME, VC_CODE FROM SI_V_SUBJECTS WHERE N_SUBJECT_ID = 100';

// Establish the database connection
$dbh = oci_connect($login, $password, $dsn);
if (!$dbh) {
    $e = oci_error();
    exit(1);
}

// Prepare and execute query
$sth = oci_parse($dbh, $query);
oci_execute($sth);

// Get and output the result.
while (($row = oci_fetch_assoc($sth)) !== false) {
    echo "VC_SUBJ_TYPE_NAME: " . $row['VC_SUBJ_TYPE_NAME'] . "\n";
    echo "VC_CODE: " . $row['VC_CODE'] . "\n";
}

// Free prepared statement
oci_free_statement($sth);

// Close the database connection
oci_close($dbh);

PROCEDURE

<?php
// Database sid
$dsn = 'hydra2';
// Database login
$login = 'AIS_RPC';
// Database password
$password = '********';
// Procedure
$procedure = <<< EOT
BEGIN
SI_PERSONS_PKG.SI_PERSONS_PUT(
            num_N_FIRM_ID => 100,
            num_N_SUBJECT_ID => :num_N_SUBJECT_ID,
            vch_VC_SURNAME => 'Ivanov',
            vch_VC_FIRST_NAME => 'Ivan',
            vch_VC_SECOND_NAME => 'Ivanovich'
        );
END;
EOT;

// Output variable
$num_N_SUBJECT_ID = null;

// Establish the database connection
$dbh = oci_connect($login, $password, $dsn);
if (!$dbh) {
    $e = oci_error();
    exit(1);
}

// Prepare an Oracle statement for execution
$sth = oci_parse($dbh, $procedure);
// Bind variables
oci_bind_by_name($sth, ':num_N_SUBJECT_ID', $num_N_SUBJECT_ID, 32);
// Call procedure
oci_execute($sth, OCI_NO_AUTO_COMMIT);

// Print value of a created basic subject
echo "Created basic subject " . $num_N_SUBJECT_ID . "\n";

// Commit changes
oci_commit($dbh);

// Free prepared statement
oci_free_statement($sth);

// Close the database connection
oci_close($dbh);

Java

Работа с СУБД выполняется с помощью Oracle JDBC.

Для сборки приложения используется Gradle

settings.gradle

rootProject.name = 'java'

build.gradle

plugins {
    id 'java'
    id 'application'
}

group 'org.example'
version '1.0-SNAPSHOT'

mainClassName='Oracle'

repositories {
    mavenCentral()
}

dependencies {
    implementation "com.oracle.ojdbc:ojdbc8:19.3.0.0"
}

SELECT

src/main/java/Oracle.java

import java.sql.*;


public class Oracle {
    public static void main(String[] args) {
        String dsn = "hydra2";
        String login = "AIS_RPC";
        String password = "********";

        try {
            // Establish the database connection
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@" + dsn, login, password);
            // Call exec method
            Oracle.select(conn);
            // Close the database connection
            conn.close();
        } catch (SQLException e) {
            System.err.println("Connection error!");
            e.printStackTrace();
        }
    }

    public static void select(Connection conn) {
        // Select query
        String query = "SELECT VC_SUBJ_TYPE_NAME, VC_CODE FROM SI_V_SUBJECTS WHERE N_SUBJECT_ID = 100";

        try {
            // Create an Oracle statement for execution
            Statement stmt = conn.createStatement();
            // Execute query
            ResultSet rs = stmt.executeQuery(query);
            // Get and output the result.
            while (rs.next()) {
                String vcSubjTypeName = rs.getString("VC_SUBJ_TYPE_NAME");
                String vcCode = rs.getString("VC_CODE");
                System.out.println("VC_SUBJ_TYPE_NAME: " + vcSubjTypeName);
                System.out.println("VC_CODE: " + vcCode);
            }
        } catch (SQLException e) {
            System.err.println("Select error!");
            e.printStackTrace();
        }
    }
}

PROCEDURE

src/main/java/Oracle.java

import java.sql.*;

import oracle.jdbc.OracleTypes;


public class Oracle {
    public static void main(String[] args) {
        String dsn = "hydra2";
        String login = "AIS_RPC";
        String password = "********";

        try {
            // Establish the database connection
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@" + dsn, login, password);
            // Disable autocommit
            conn.setAutoCommit(false);
            // Call exec method
            Oracle.exec(conn);
            // Close the database connection
            conn.close();
        } catch (SQLException e) {
            System.err.println("Connection error!");
            e.printStackTrace();
        }
    }

    public static void exec(Connection conn) {
        // Procedure
        String procedure = "{call SI_PERSONS_PKG.SI_PERSONS_PUT(num_N_FIRM_ID => 100," +
                "num_N_SUBJECT_ID => ?," +
                "vch_VC_SURNAME => 'Ivanov'," +
                "vch_VC_FIRST_NAME => 'Ivan'," +
                "vch_VC_SECOND_NAME => 'Ivanovich')}";
        try {
            // Prepare an Oracle statement for execution
            CallableStatement stmt = conn.prepareCall(procedure);
            // Add output parameter
            stmt.registerOutParameter(1, OracleTypes.NUMBER);
            // Execute query
            stmt.execute();
            // Print value of a created basic subject
            System.out.println("Created basic subject " + stmt.getLong(1));
            // Commit changes
            conn.commit();
        } catch (SQLException e) {
            System.err.println("Procedure error!");
            e.printStackTrace();
        }
    }
}

Page tree

Примеры работы с БД в скриптах

Python

SELECT

PROCEDURE

PHP

SELECT

PROCEDURE

Java

SELECT

PROCEDURE