Microsoft Word - 430_15_Developing_Stored_Procedure.doc

Size: px

Start display at page:

Download "Microsoft Word - 430_15_Developing_Stored_Procedure.doc"

さみらはらしない
7 years ago
Views:

1 Java Oracle

2 Java 3 Java Web GUI Java Java Java Oracle Java Oracle8i Oracle / Oracle Java Virtual Machine VM CORBA Enterprise JavaBeans Oracle Java Java Java Oracle Oracle Java Virtual Machine Oracle 5 1 Java Java Oracle8i Java Virtual Machine 2 Oracle8i 3 Java Oracle8i Oracle Java Java 4 Java Structured Query Language SQL PL/SQL Java SQL PL/SQL Java 5 Java Oracle8i Java Virtual Machine Java Oracle 1

Java Java Oracle8i Java Virtual Machine 2 Oracle8i 3 Java Oracle8i Oracle Java Java 4 Java

3 Java Java Java Java Java 3 Java Java Virtual Machine Java Java JavaBeans Enterprise JavaBeans Java 1 JavaBeans Java Java Web Java / / Java Java Oracle Oracle C C++ C C C Java Java Java Oracle 2

4 Java Java Java Java Java Java Java C Oracle8i Java Virtual Machine Java Oracle8i Java JavaSoft Java VM JDK Java JavaSoft Java VM VM Java Virtual Machine VM Oracle8i Sun Java Java VM Java Oracle8i Java Virtual Machine 24 Java Runtime Java Integrated Development Environment IDE Oracle Java Oracle 3

Machine VM Oracle8i Sun Java Java VM Java Oracle8i Java Virtual Machine 24

5 Oracle8i Net8 PL/SQL SQL IIOP ORB Java VM JDBC 1 Oracle8i Java Virtual Machine Java Oracle Java VM Oracle8i 1 Oracle Oracle8 Oracle8i Java Virtual Machine RDBMS Java Oracle Multithreaded Server MTS 10,000 Oracle8i VM Oracle Java HTML XML Java Oracle8i Java Oracle Java Java Oracle 4

Virtual Machine RDBMS Java 15 40 Oracle Multithreaded Server MTS 10,000

6 Oracle 8i Java Virtual Machine 2 Oracle8i Java Virtual Machine VM Java PL/SQL SQL PL/SQL SQL Oracle8i Java Java SQL PL/SQL SQL PL/SQL Oracle8i Java Virtual Machine 1 Oracle8i Java VM Java CORBA Enterprise JavaBeans 2 Application Programming Interface API CORBA Enterprise JavaBeans Oracle8i Oracle8i Java Virtual Machine PL/SQL SQL PL/SQL Java Oracle8i Java PL/SQL Java Java SQLJ SQLJ Java SQL C SQL Pro*C SQL SQLJ SQL Java 3 1 Java Java SQL Java SQL PL/SQL SQL DML 2 Java Oracle8i Oracle 5 Java 3 Oracle8 SQL Oracle8 PL/SQL C C++ Oracle8i Java Java Oracle 5

JavaBeans Oracle8i Oracle8i Java Virtual Machine PL/SQL SQL PL/SQL Java Oracle8i Java PL/SQL Java Java SQLJ SQLJ Java SQL C SQL Pro*C SQL

7 Java Oracle Java Virtual Machine Java Oracle Java Oracle8i Java 2 Java Oracle Java JavaSoft JDK Java Java.class Java Java Java Virtual Machine Java CLASSPATH Oracle8i Java Java Oracle8i Java VM Java Java.class Java.jarJava Java Java Java PL/SQL Java Java Java Java Java Java Java Java CLASSPATH RESOLVER RESOLVER SQL RESOLVER Java PL/SQL Java RESOLVER RESOLVER ((<match string> <schema name>)...) <march string> Java Java 1 <schema name> RESOLVER (( some.particular.class FRED) ( com.oracle.employee.* SCOTT) (* PUBLIC)) Java Oracle 6

Java Java Virtual Machine Java CLASSPATH Oracle8i Java Java Oracle8i Java VM Java Java.class Java.

8 Oracle8i Java Oracle8i Java Oracle8i Java Java Java 4 Java Java Java Oracle8i Java Oracle8i Java Integrated Development Environment IDE JDeveloper Java Java 'emp' 2 Java 'approve_raise' 'sal_grade'java Oracle 'approve_raise' 'sal_grade' Java SQL PL/SQL Java SQL // File: emp.java package com.oracle.employee; public class emp { public static String sal_grade (float current_sal) { if (current_sal > 0 && current_sal < 50000) return MTS ; if (current_sal >= && current_sal < ) return SMTS ; return Salary out of Range ; public static void approve_raise (float current_sal, float raise, float new_sal [ ]) { // new_sal is an array of size one to handle OUT parameters in Java. float percent_raise = (raise / current_sal) * 100; if (percent_raise > 15 percent_raise < 5) new_sal[0] = -1; else { new_sal[0] = current_sal + raise; Java Oracle 7

$employee; public class emp { public static String sal_grade (float current_sal) { if (current_sal > 0 && current_sal < 50000) return MTS ; if (current_sal >= 50000 && current_sal < 100000) return$

9 Java Java Java Java Java Java 3 Java Java 2 Java.class.jar Java Java Java 2 Oracle8i SQL*Plus CREATE JAVA... DDL Java Oracle8i LOB 2 Java Java com.oracle.employee.emp com/oracle/employee/emp Java SQL> CREATE DIRECTORY bfile_dir as /home/user/com/oracle/employee ; - - CREATE JAVA CLASS... DDL Java - SQL> CREATE OR REPLACE JAVA CLASS USING BFILE (bfile_dir, emp.class ); - LOB - 1. Java emp.class LOB - 2. LOB Java - SQL> CREATE JAVA CLASS RESOLVER USING BLOB - > (SELECT ALOB FROM LOB_TABLE WHERE ID=123); - Oracle8i Java LOADJAVA Java LOADJAVA Java Java.class Java.jar 3 Java LOADJAVA Java create$java$lob$table Java.jar LOB BLOB CREATE JAVA... DDL BLOB Java Java Oracle 8

.. DDL Java - SQL> CREATE OR REPLACE JAVA CLASS USING BFILE (bfile_dir, emp.class ); - LOB - 1. Java emp.class LOB - 2.

10 LOADJAVA Java RESOLVER Oracle LOADJAVA Java DROPJAVA LOADJAVA Java Oracle emp.class SCOTT tiger Java SID ORCL oudelsrv-1 RESOLVER employee.* SCOTT *PUBLIC LOADJAVA Java loadjava -user -r (("employee.*" SCOTT) (* PUBLIC)) emp.class - Java LOADJAVA Oracle Java Database Connectivity JDBCJava Java LOADJAVA Thin JDBC JDBC/Oracle Call Interface Java Java SQL Java SQL SQL Java Java Java DML SQL 1 Java Java Java VM Java Java Java Java SQL Java SQL SQL Java Java SQL 3 SQL Java SQL PL/SQL Java SQL Java Java SQL SQL Java Java SQL Java BYTE SHORT INT FLOAT DOUBLE SQL NUMBER CHAR VARCHAR2 DATE Java SQL SQL Java SQL Java 2 - SQL Java SQL NUMBER Java 'int' - 2 SQL NULL Java NULL SQL NULL Java Java NULL CONSTRAINT Java Oracle 9

class - Java LOADJAVA Oracle Java Database Connectivity JDBCJava Java LOADJAVA Thin JDBC JDBC/Oracle Call Interface Java Java SQL Java SQL SQL Java Java Java DML SQL 1 Java Java Java VM

11 2 SQL NULL Java Java SQL NUMBER Java. lang. Integer Java null SQL NULL SQL NULL Java 2 Oracle8i SQL Java Oracle.SQL Java SQL Java SQL oracle.sql SQL SQL Java SQL NULL SQL oracle.sql SQL Java SQL Java oracle.sql.number SQL Java SQL IN OUT IN OUT Java IN Java SQL OUT IN OUT 1 1 IN OUT Java Java Oracle7 Oracle8 PL/SQL PL/SQL SQL EXECUTE PL/SQL Oracle8i Java T SQL A A T B B T SQL PL/SQL Java Java Java 3 CALL-spec SQL Java CALL-spec Java PL/SQL CALL-spec PL/SQL SQL Java CALL-spec SQL Java Java SQL CALL-spec JAVA Java com.oracle.employee.emp.sal_grade com.oracle.employee.emp.approve_raise SQL Java Java Oracle 10

Oracle7 Oracle8 PL/SQL PL/SQL SQL EXECUTE PL/SQL Oracle8i Java T SQL A A T B B T SQL PL/SQL Java Java Java 3 CALL-spec SQL Java CALL-spec Java PL/SQL

12 Java Java CALL-spec employee.emp sal_grade Java CALL-spec SQL> CREATE OR REPLACE FUNCTION SGRADE (SAL IN NUMBER) RETURN VARCHAR2 2> IS LANGUAGE JAVA NAME 3> 'com.oracle.employee.emp.sal_grade (float) return java.lang.string'; / Statement processed. CALL-spec for the approve_raise Java method of class employee.emp SQL> CREATE OR REPLACE PROCEDURE APPRAISE (SAL IN NUMBER, RAISE IN 2> NUMBER, NSAL OUT NUMBER) AUTHID INVOKER IS LANGUAGE JAVA NAME com.oracle.employee.emp.approve_raise(float, float, float [ ]) ; / Statement processed. Oracle SQL CALL-spec Oracle SQL Java NUMBER Oracle SQL employee.emp approve_raise Java CALL-spec SQL> CREATE OR REPLACE PROCEDURE APPRAISE (SAL IN NUMBER, RAISE IN 2> NUMBER, NSAL OUT NUMBER) AUTHID INVOKER 3> IS LANGUAGE JAVA NAME com.oracle.employee.emp.approve_raise(oracle.sql.number, oracle.sql.number, oracle.sql.number [ ]) ; / Statement processed. Java Oracle 11

emp SQL> CREATE OR REPLACE PROCEDURE APPRAISE (SAL IN NUMBER, RAISE IN 2> NUMBER, NSAL OUT NUMBER) AUTHID INVOKER IS LANGUAGE JAVA NAME com.oracle.employee.emp.approve_raise(float, float, float [ ]) ; / Statement processed.

13 Java Java SQL SQL Java SQL PL/SQL PL/SQL SQL Java 2 CALL Oracle8i SQL CALLCALL CALL SQL*PLUS EXECUTE BEGIN...END CALL Java CALLSQL Java CALL-spec CALL CALL Java CALL-spec SGRADE APPRAISE CALL SQL Java SQL> VARIABLE NEWSAL NUMBER SQL> VARIABLE CURSAL NUMBER SQL> VARIABLE RAISE NUMBER SQL> VARIABLE GRADE VARCHAR2 SQL> EXECUTE :CURSAL := 50000; Statement processed. SQL> EXECUTE :RAISE := 5000; Statement processed. SQL> CALL APPRAISE(:CURSAL, :RAISE, :NEWSAL); Statement processed. SQL> PRINT NEWSAL NEWSAL SQL> CALL SGRADE(:CURSAL) INTO :GRADE Statement processed. SQL> PRINT GRADE GRADE SMTS SQL SQL PL/SQL Java SQL Java SQL CALL-spec SQL SQL Java SQL Java SQL> SELECT ENAME, SAL, SGRADE(SAL) FROM EMP; Java Oracle 12

VARIABLE GRADE VARCHAR2 SQL> EXECUTE :CURSAL := 50000; Statement processed. SQL> EXECUTE :RAISE := 5000; Statement processed.

14 SQL PL/SQL Java PL/SQL Java Java PL/SQL PL/SQL PL/SQL CALL-spec PL/SQL Java VM Java PL/SQL Java PL/SQL Java DECLARE newsal NUMBER(7,2); cursal NUMBER(7,2); raise NUMBER(7,2); BEGIN APPRAISE(CURSAL, RAISE, NEWSAL); END; Java SQL PL/SQL Oracle8i Java Java Oracle RDBMS Java PL/SQL Java JDBC SQLJ Oracle Java Virtual Machine JDBC JDBC JDBC 1.22 JDBC API JDBC SQL PL/SQL JDBC Oracle8i SQLJ JDBC SQLJ PRO*C SQL C SQL Java SQLJ SQLJ JDBC Java Java 100% Java Java / JDBC JDBC SQLJ JDBC SQLJ SQLJ 'emp' 'sal_grade' 'approve_raise' Java Oracle 13

Oracle RDBMS Java PL/SQL Java JDBC SQLJ Oracle Java Virtual Machine JDBC JDBC JDBC 1.

15 Java package com.oracle.employee; public class emp { public static String sal_grade (String empname) { float current_sal; #sql {select salary into :current_sal from emp where ename = :empname ; if (current_sal > 0 && current_sal < 50000) return MTS ; if (current_sal >= && current_sal < ) return SMTS ; return ( Salary out of Range ); public static void approve_raise (String empname, float raise, float new_sal [ ]) { float current_sal; int hire_time; #sql {select salary into :current_sal from emp where ename = :empname ; // We are assuming that employment_period is a PL/SQL function that has been previously defined in the // the database that looks at the employees hire date and returns the number of months the employee // has been with the company. #sql hire_time = { VALUES(employment_period (:empname)) ; float percent_raise = (raise / current_sal) * 100; if (hire_time > 9) { if (percent_raise > 15 percent_raise < 5) { new_sal[0] = -1; else { new_sal[0] = current_sal + raise; else { new_sal[0] = -2; Java Oracle 14

$new_sal [ ]) { float current_sal; int hire_time; #sql {select salary into :current_sal from emp where ename = :empname ; // We are assuming that employment_period is a PL/SQL function that has been$

16 SQLJ Java SQL SQL SQLJ SQLJ JDBC Java / Java Java Java PL/SQL 2 / Java Java /Net8 Net8 Connection Manager 1 Java JDBC SQLJ Java Java SQL92 Oracle SQL PL/SQL JDBC Java Pro* Oracle Call Interface ODBC Developer/2000 FORMS Java Java JDBC SQL*Net ORACLE RDBMS OCI SQL*Net Pro* ODBC Dev 2000 [Forms] SQL*Net Net8 Connection Manager SQL*Net Java PL/SQL 2 Java Java Oracle 15

Call Interface ODBC Developer/2000 FORMS Java Java JDBC SQL*Net ORACLE RDBMS OCI SQL*Net

17 Java Java Oracle8i Java Java 2 Oracle8i Java 2 Oracle Java Oracle Oracle Lite 3 Java 2 SQLJ Oracle Oracle IBM Tandem Sybase JavaSoft Informix 2 0 Java SQL 1 Java Oracle8i Java Java SQL ANSI/ISO 4 Java PL/SQL / / PL/SQL Java Oracle Java RDBMS Oracle Java PL/SQL 2 PL/SQL Java Java PL/SQL 2 / Java Oracle 16

Java SQL 1 Java Oracle8i Java Java SQL ANSI/ISO 4 Java PL/SQL / / PL/SQL

18 Java Oracle8i Oracle Oracle8i Java Virtual Machine Java Oracle8i Java Java Java SQL Java Java JDBC SQL Java SQL PL/SQL Oracle8i Java Java SQL PL/SQL Java Oracle Oracle Oracle Lite Java Java Oracle 17

19 Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA U.S.A FAX Oracle Corporation 1998 Oracle SQL*Plus Enabling the Information Age Oracle8i PL/SQL Oracle8 Pro*C JDeveloper Oracle7 Net8 Developer/2000 # A

com/ Oracle Corporation 1998 Oracle SQL*Plus Enabling the Information Age

0315_F1_8iJDBC-SQLJ.PDF

0315_F1_8iJDBC-SQLJ.PDF Page 1 1 Page 2 2 JDBC JDBC JDBC JDBC JDBC SQLJ SQLJ SQLJ SQLJ SQLJ Page 3 3 JDBC Java JDBC RDBMS JDBC ODBC JDBC SQL JDBC SQL Java API Java JDBC Java API RDBMS Java Java JDBC JDBC ODBC Javasoft SQL99 JDBC