한빛출판네트워크

#sql {
  CREATE TABLE addresses (
    id           NUMBER
      CONSTRAINT addresses_pk PRIMARY KEY,
    customer_id  NUMBER
      CONSTRAINT addresses_fk_customers
      REFERENCES customers(id),
    street       VARCHAR2(255) NOT NULL,
    city         VARCHAR2(255) NOT NULL,
    state        CHAR(2) NOT NULL,
    country      VARCHAR2(255) NOT NULL
  )
};

#sql { COMMIT [WORK] };
#sql { ROLLBACK [WORK] };

#sql {
  INSERT INTO
    customers (id, first_name, last_name, dob, phone)
  VALUES
    ("7", "John", "Smith", "01-JAN-1970", "650-555-1212")
};

#sql { ROLLBACK };

Oracle.connect(
  "jdbc.oracle.thin:@localhost:1521:orcl",
  "fundamental_user",
  "fundamental_password",
  true
);

/*
   The program FundamentalExample1.sqlj illustrates how to 
   connect to a database, how to embed SQL DML operations 
   in SQLJ executable statements, and how to use host 
   expressions.
*/

// import required packages
import java.sql.*;
import oracle.sqlj.runtime.Oracle;

public class FundamentalExample1 {

  public static void main(String [] args) {

    try {

      Oracle.connect(
        "jdbc:oracle:thin:@localhost:1521:orcl",
        "fundamental_user",
        "fundamental_password"
      );

      // add a new customer
      int customer_id = 6;
      String first_name = "Jerry";
      String last_name = "Fieldtop";
      Date dob = new Date(80, 1, 1);
      String phone = "650-555-1222";
      #sql {
        INSERT INTO
          customers (id, first_name, last_name, dob, phone)
        VALUES
          (:customer_id, :first_name, :last_name, :dob, :phone)
      };

      // display new customer
      #sql {
        SELECT
          first_name, last_name, dob, phone
        INTO
          :first_name, :last_name, :dob, :phone
        FROM
          customers
        WHERE
          id = :customer_id
      };
      System.out.println("Customer with id " + customer_id +
        " has the following details:");
      System.out.println("  First name: " + first_name);
      System.out.println("  Last name: " + last_name);
      System.out.println("  DOB: " + dob);
      System.out.println("  Phone: " + phone);

      // delete the customer
      #sql {
        DELETE FROM
          customers
        WHERE
          id = :customer_id
      };

      // commit the transaction
      #sql { COMMIT };

      // update the first product price
      int product_id = 1;
      double product_price = 11.25;
      #sql {
        UPDATE
          products
        SET
          price = :product_price
        WHERE
          id = :product_id
      };

      // display the first product
      int type_id = 0;
      String name = null;
      String description = null;
      double price = 0.0;
      #sql {
        SELECT
          type_id, name, description, price
        INTO
          :type_id, :name, :description, :price
        FROM
          products
        WHERE
          id = :product_id
      };
      System.out.println("Product with id " + product_id +
        " has the following details: ");
      System.out.println("  Type id: " + type_id);
      System.out.println("  Name: " + name);
      System.out.println("  Description: " + description);
      System.out.println("  Price: " + price);

      // rollback the update
      #sql { ROLLBACK };

      // create a table to hold customer addresses
      #sql {
        CREATE TABLE addresses (
          id            NUMBER CONSTRAINT addresses_pk PRIMARY KEY,
          customer_id   NUMBER CONSTRAINT addresses_fk_customers
            REFERENCES customers(id),
          street        VARCHAR2(255) NOT NULL,
          city          VARCHAR2(255) NOT NULL,
          state         CHAR(2) NOT NULL,
          country       VARCHAR2(255) NOT NULL
        )
      };
      System.out.println("Successfully created addresses table.");

      // drop the addresses table
      #sql {
        DROP TABLE addresses
      };

    } catch ( SQLException e ) {

      System.err.println("SQLException " + e);

    } finally {

      try {

        Oracle.close();

      } catch ( SQLException e ) {

        System.err.println("SQLException " + e);

      }

    }

  } // end of main()

}

sqlj FundamentalExample1.sqlj

Customer with id 6 has the following details: 
  First name: Jerry
  Last name: Fieldtop
  DOB: 1980-02-01
  Phone: 650-555-1222
Product with id 1 has the following details: 
  Type id: 1
  Name: Beyond Understanding
  Description: The frontiers of human knowledge
  Price: 11.25
Successfully created addresses table.