Page History

...

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

...

Code Block

language	php

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

Сборка приложения с использование Gradle

Code Block

title	settings.gradle

rootProject.name = 'java'

Code Block

title	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

Code Block

language	java
title	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 exec(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

Code Block

language	java
title	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

Versions Compared

Old Version 4

New Version 5

Key