oracle18_扩充.txt

orale培训教材包括了所有的sql说明和实例

---------- createparts.txt ----------
/*
 * 范例名称：数据准备
 * 文件名称：createparts.txt
 */

CREATE TABLE PARTS
 (ID  NUMBER(38)
 ,DESCRIPTION  VARCHAR2(250) NOT NULL
 ,UNITPRICE  NUMBER NOT NULL
 ,ONHAND  NUMBER(38) NOT NULL
 ,REORDER  VARCHAR2(40) NOT NULL
 );

INSERT INTO parts
 VALUES (1,'Fax Machine',299,277,50);
INSERT INTO parts
 VALUES (2,'Copy Machine',4895,143,25);
INSERT INTO parts
 VALUES (3,'Laptop PC',2100,7631,1000);
INSERT INTO parts
 VALUES (4,'Desktop PC',1200,5903,1000);
INSERT INTO parts
 VALUES (5,'Scanner',99,490,200);
COMMIT;
  
  
  
---------- Part.sqlj ----------
/*
 * 范例名称：SQLJ用于JAVA存储过程
 * 文件名称：Part.sqlj
 */

// IMPORT NECESSARY Classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;

public class Part {

  //定义PARTS的METHOD：  INSERT, UPDATE, AND DELETE 

  public static void insert (
     oracle.sql.NUMBER id,
     oracle.sql.CHAR description,
     oracle.sql.NUMBER unitPrice,
     oracle.sql.NUMBER onHand, 
     oracle.sql.NUMBER reorder ) throws SQLException {
    try {
      #sql {
        INSERT INTO parts
          VALUES (:id, :description, :unitPrice, :onHand, :reorder) };
    } catch (SQLException e) {System.err.println(e.getMessage());}      
  }

  public static void update (
     oracle.sql.NUMBER id,
     oracle.sql.CHAR description,
     oracle.sql.NUMBER unitPrice,
     oracle.sql.NUMBER onHand, 
     oracle.sql.NUMBER reorder ) throws SQLException {
    try {
      #sql {
        UPDATE parts
          SET description = :description,
              unitprice = :unitPrice,
              onhand = :onHand,
              reorder = :reorder
          WHERE id = :id };
    } catch (SQLException e) {System.err.println(e.getMessage());}      
  }

  public static void delete (oracle.sql.NUMBER id) throws SQLException {
    try {
      #sql {DELETE FROM parts WHERE id = :id };
    } catch (SQLException e) {System.err.println(e.getMessage());}      
  }
}


---------- map.txt ----------
/*
 * 范例名称：用于映射JAVA类的方法的plsql存储过程
 * 文件名称：map.txt
 */
 --对应于Part.insert方法
create or replace procedure insertPart(
   id number,
   description varchar2,
   unitprice number,
   onhand number,
   reorder number)
as
language java
name 'Part.insert(
   oracle.sql.NUMBER,
   oracle.sql.CHAR,
   oracle.sql.NUMBER,
   oracle.sql.NUMBER,   
   oracle.sql.NUMBER)';

--对应于Part.update方法
create or replace procedure  updatePart(
   id number,
   description varchar2,
   unitprice number,
   onhand number,
   reorder number)
as
language java
name 'Part.update(
   oracle.sql.NUMBER,
   oracle.sql.CHAR,
   oracle.sql.NUMBER,
   oracle.sql.NUMBER,   
   oracle.sql.NUMBER)';
/
--对应于Part.delete方法
create or replace procedure deletePart (id number)
as
language java
name 'Part.delete(oracle.sql.NUMBER)';
/

--dd
select object_name, OBJECT_TYPE FROM user_objects where object_name like '%Part%'
--test
 call insertpart(1,'a',1,1,1);


---------- JavaProc.sqlj ----------
/*
 * 范例名称：SQLJ用于JAVA存储过程
 * 文件名称：JavaProc.sqlj
 */

//用于建立JAVA存储过程的.sqlj文件
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;

//类定义
public class JavaProc {

    //定义类的static方法
	public static void insert (
		oracle.sql.NUMBER id,
		oracle.sql.CHAR ename) throws SQLException {
		
		try {
			#sql {insert into scott.emp(empno,ename) values (:id,:ename) };
			#sql {commit};   
            //如果不commit,因为sqlj与jdbc不同，它不是auto commit,则dml不会commit
			System.out.println("Success!");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
}			
		
		--------------------------------------------------------	
			 select object_name, OBJECT_TYPE FROM user_objects where object_name like '%JavaProc%'


--------map Javaproc-----------------
create or replace procedure insertemp(
   id number,
   ename varchar2
   )
as
language java
name 'JavaProc.insert(
oracle.sql.NUMBER ,
oracle.sql.CHAR )';

//注意：下面的例子错在哪？错在oracle.sql.NUMBER id不必再写参数名。
//只写oracle.sql.NUMBER--参数类型就可以。
/*
create or replace procedure insertemp(
   id number,
   ename varchar2
   )
as
language java
name 'JavaProc.insert(
oracle.sql.NUMBER id,
oracle.sql.CHAR ename)';
*/