開発者：ODP.NET

create or replace package associative_array as
  -- define an associative array type for each column in the jobs table

  type t_job_id is table of jobs.job_id%type index by pls_integer;
  type t_job_title is table of jobs.job_title%type index by pls_integer;
  type t_min_salary is table of jobs.min_salary%type index by pls_integer;
  type t_max_salary is table of jobs.max_salary%type index by pls_integer;

  -- define the procedure that will perform the array insert

  procedure array_insert (p_job_id in t_job_id,
                                   p_job_title in t_job_title,
                                   p_min_salary in t_min_salary,
                                   p_max_salary in t_max_salary);
end associative_array;
/

create or replace package body associative_array as
  -- implement the procedure that will perform the array insert

  procedure array_insert (p_job_id in t_job_id,
                                   p_job_title in t_job_title,
                                   p_min_salary in t_min_salary,
                                   p_max_salary in t_max_salary) is
  begin
    forall i in p_job_id.first..p_job_id.last
    insert into jobs (job_id,
                           job_title,
                           min_salary,
                           max_salary)
              values (p_job_id(i),
                         p_job_title(i),
                         p_min_salary(i),
                         p_max_salary(i));
  end array_insert;
end associative_array;
/ 

       

    SQL> desc jobs

 Name            Null?     Type
 ----------      -------   --------------
 JOB_ID          NOT NULL  VARCHAR2(10)
 JOB_TITLE       NOT NULL  VARCHAR2(35)
 MIN_SALARY                NUMBER(6)
 MAX_SALARY                NUMBER(6)                               

       using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace JanFeb2007 {
  class Program {
    static void Main(string[] args) {
      // connection string - make sure to adjust for your environment
      string constr = "user id="hr;" password="hr;" data source="oramag;" enlist="false;" pooling=false";

      // create and open connection object
      OracleConnection con = new OracleConnection(constr);
      con.Open();

      // insert the new jobs into the jobs table
      // create command object and set attributes
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "associative_array.array_insert";
      cmd.CommandType = CommandType.StoredProcedure;

      // create parameter objects for each parameter
      OracleParameter p_job_id = new OracleParameter();
      OracleParameter p_job_title = new OracleParameter();
      OracleParameter p_min_salary = new OracleParameter();
      OracleParameter p_max_salary = new OracleParameter();

      // set parameter type for each parameter
      p_job_id.OracleDbType = OracleDbType.Varchar2;
      p_job_title.OracleDbType = OracleDbType.Varchar2;
      p_min_salary.OracleDbType = OracleDbType.Decimal;
      p_max_salary.OracleDbType = OracleDbType.Decimal;

      // set the collection type for each parameter
      p_job_id.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      p_job_title.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      p_min_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      p_max_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

      // set the parameter values
      p_job_id.Value = new string[3] { "IT_DBA", "IT_MAN", "IT_VP" };
      p_job_title.Value = new string[3] { "Database Administrator", "IT Manager", "IT Vice President" };
      p_min_salary.Value = new decimal[3] { 8000, 12000, 18000 };
      p_max_salary.Value = new decimal[3] { 16000, 24000, 36000 };

      // set the size for each array
      p_job_id.Size = 3;
      p_job_title.Size = 3;
      p_min_salary.Size = 3;
      p_max_salary.Size = 3;

      // add parameters to command object collection
      cmd.Parameters.Add(p_job_id);
      cmd.Parameters.Add(p_job_title);
      cmd.Parameters.Add(p_min_salary);
      cmd.Parameters.Add(p_max_salary);

      // execute the insert
      cmd.ExecuteNonQuery();

      // display the new jobs
      cmd.CommandText = "select job_id, job_title from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP') order by job_id";
      cmd.CommandType = CommandType.Text;

      OracleDataReader dr = cmd.ExecuteReader();

      Console.WriteLine("New jobs have been added to the JOBS table:\n");

      while (dr.Read()) {
        Console.WriteLine("{0,6}: {1}", dr.GetString(0), dr.GetString(1));
      }

      Console.WriteLine();

      // delete the new jobs
      cmd.CommandText = "delete from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP')";
      cmd.ExecuteNonQuery();

      Console.WriteLine("New jobs have been removed from the JOBS table.");

      // clean up objects
      p_max_salary.Dispose();
      p_min_salary.Dispose();
      p_job_title.Dispose();
      p_job_id.Dispose();
      cmd.Dispose();
      con.Dispose();

      // simple prompt to keep output window from closing when executing from IDE
      Console.WriteLine("Press ENTER to continue...");
      Console.ReadLine();
    }
  }
}