Oracle-PL/SQLベース-DL
21282 ワード
SQL(Structured Query Language)とPLSQL(Procedural Language/SQL)の違い:SQLは構造化クエリー言語であり、非プロセス化である.PL/SQLは、プロセス化SQL言語と呼ばれるプログラム言語であり、OracleデータベースによるSQL文の拡張です.
PL/SQLベース構文を説明する前に、Oracleにどのようなデータベース・オブジェクトがあるかを理解し、データベース・オブジェクトの使用によってPL/SQLの構文を導入する必要があります.
DDL(データ定義言語)とDML(データ操作言語)の理解:簡単に言えば、データベースオブジェクトを作成するのはすべてデータベース定義言語であり、データベースオブジェクトを呼び出したり使用したりするのはデータベース操作言語である.
Oracleデータベース・オブジェクトは、次のように分類されます.
1.TABLE表
公式説明:A table is the basic unit of data organization in an Oracle database.A table describes an entity, which is something of significance about which information must be recorded. For example, an employee could be an entity.
2.INDEXインデックス
公式説明:An index is an optional structure,associated with a table or table cluster,that can sometimes speed data access.By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.
3.CONTRAINT制約
公式説明:Use a constraint to define an integrity constraint-a rule that restricts the values in a database.
4.VIEWビュー
公式説明:A view is a logical representation of one or more tables.In essence, a view is a stored query. A view derives its data from the tables on which it is based, called base tables. Base tables can be tables or other views.
5.SYNONYM同義語
公式説明:A synonym is an alias for a schema object.For example, you can create a synonym for a table or view, sequence, PL/SQL program unit, user-defined object type, or another synonym. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.
6.SEQENCEシーケンス
公式説明:A sequence is a schema object from which multiple users can generate unique integers.A sequence generator provides a highly scalable and well-performing method to generate surrogate keys for a number data type.
7.PROCEDUREストレージプロセス
公式説明:A procedure is a type of PL/SQL subprogram,which is a programming object that can be stored and executed in the database server,and called from other programming objects or applications.
8.FUNCTION関数
公式説明:The CREATE FUNCTION statement creates or replaces a standalone function or a call specification.A standalone function is a function (a subprogram that returns a single value) that is stored in the database.
9. DBMS_SCHEDULERスケジューリングタスク
公式説明:The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures that can be called from any PL/SQL program.
10.PACKAGEパッケージ
公式説明:The CREATE PACKAGE statement creates or replaces the specification for a stored package,which is an encapsulated collection of related procedures,functions,and other program objects stored as a unit in the database. 11. TRIGGERトリガ
公式説明:Triggers are defined using PL/SQL.Therefore, this section provides some general information but refers to Oracle Database PL/SQL Language Reference for details of syntax and semantics.
12.DBLINKデータベース接続
公式説明:Use the CREATE DATABASE LINK statement to create a database link.A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
13.SUBPROGRAMESサブルーチン
公式説明:A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly.If the subprogram has parameters, their values can differ for each invocation.
14.SCHEDULES Jobスケジューリングタスク
公式説明:Oracle Database includes Oracle Scheduler,an enterprise job scheduler to help you simplify the scheduling of hundreds or even thousands of tasks.Oracle Scheduler (the Scheduler) is implemented by the procedures and functions in the DBMS_SCHEDULER PL/SQL package.
以上、Oracleデータベース・オブジェクトについて説明しましたが、PL/SQLを使用して上記のオブジェクトの作成と使用方法について説明します.
PL/SQLベース構文を説明する前に、Oracleにどのようなデータベース・オブジェクトがあるかを理解し、データベース・オブジェクトの使用によってPL/SQLの構文を導入する必要があります.
DDL(データ定義言語)とDML(データ操作言語)の理解:簡単に言えば、データベースオブジェクトを作成するのはすべてデータベース定義言語であり、データベースオブジェクトを呼び出したり使用したりするのはデータベース操作言語である.
/* */
SELECT object_type FROM DBA_OBJECTS GROUP BY object_type;Oracleデータベース・オブジェクトは、次のように分類されます.
1.TABLE表
公式説明:A table is the basic unit of data organization in an Oracle database.A table describes an entity, which is something of significance about which information must be recorded. For example, an employee could be an entity.
/* */
CREATE TABLE [schema.]table_name [ORGANIZATION HEAP](column_name datatype [DEFAULT expreession]);
CREATE TABLE [schema.]table_name AS subquery；
ALTER TABLE [schema.]table_name ADD(column_name datatype,….);
ALTER TABLE [schema.]table_name DROP CLOUMN column_name;
ALTER TABLE [schema.]table_name RENAME CLOUMN A TO B;
DROP TABLE [schema.]table_name cascade constraints;
CREATE GLOBAL TEMPORARY TABLE [schema.]table_name [ORGANIZATION HEAP](column_name datatype [DEFAULT expreession]) [ON COMMIT { DELETE | PRESERVE } ROWS];2.INDEXインデックス
公式説明:An index is an optional structure,associated with a table or table cluster,that can sometimes speed data access.By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.
/* */
CREATE [ UNIQUE | BITMAP] INDEX [schema.] indexname ON [schema.] tablename(column,….);
DROP INDEX indexname;3.CONTRAINT制約
公式説明:Use a constraint to define an integrity constraint-a rule that restricts the values in a database.
/* */
CREATE TABLE table_name ( column1 datatype null/not null,column2 datatype null/not null, ... CONSTRAINT constraint_name [ UNIQUE | PRIMARY KEY ](column1, column2,...) );
ALTER TABLE table_name ADD CONSTRANT constraint_name [ UNIQUE | PRIMARY KEY ] (columu1,column2..);
ALTER TABLE constraint_name [ENABLE | DISABLE] constraint_name;
ALTER TABLE DROP CONSTRAINT constraint_name ;
ALTER TABLE table_name MODIFY column_name [ NOT ] NULL;
CREATE TABLE table_name1 ( column1 datatype null/not null,column2 datatype null/not null, ... CONSTRAINT constraint_name FOREIGN KEY(column1) REFERENCES table_name2(column1) );
ALTER TABLE table_name ADD CONSTRANT constraint_name FOREIGN KEY(column1) REFERENCES table_name2(column1) );
ALTER TABLE DROP FOREIGN KEY constraint_name ;4.VIEWビュー
公式説明:A view is a logical representation of one or more tables.In essence, a view is a stored query. A view derives its data from the tables on which it is based, called base tables. Base tables can be tables or other views.
/* */
CREATE TABLE [ OR REPLACE ] [ FORCE | NOFORCE] VIEW [ schema.] viewname [(alias1,alias2,….)] AS subquery [WITH CHECK OPTION [CONSTRAINT] constraintname] [WITH READ ONLY [CONTRAINT constraintname]];
DROP VIEW [schema.]viewname;5.SYNONYM同義語
公式説明:A synonym is an alias for a schema object.For example, you can create a synonym for a table or view, sequence, PL/SQL program unit, user-defined object type, or another synonym. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.
/* */
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.] synonym_name FOR [schema.] object_name [@dblink];
DROP [PUBLIC] SYSNONYM synonym;
ALTER SYNONYM sysnonym COMPILE;6.SEQENCEシーケンス
公式説明:A sequence is a schema object from which multiple users can generate unique integers.A sequence generator provides a highly scalable and well-performing method to generate surrogate keys for a number data type.
/* */
CREATE SEQUENCE [schema.]sequencename[INCREMENT BY number][START WITH number][MAXVALUE number | NOMAXVALUE][MINVALUE number | NOMINVALUE][CYCLE | NOCYCLE][CACHE number | NOCACHE][ORDERR | NOORDER];
--ALTER CREATE ， ALTER 。
DROP SEQUENCE sequencename;7.PROCEDUREストレージプロセス
公式説明:A procedure is a type of PL/SQL subprogram,which is a programming object that can be stored and executed in the database server,and called from other programming objects or applications.
/* */
CREATE OR REPLACE PROCEDURE ( IN/OUT ,...) AS
--
BEGIN
--
END;
8.FUNCTION関数
公式説明:The CREATE FUNCTION statement creates or replaces a standalone function or a call specification.A standalone function is a function (a subprogram that returns a single value) that is stored in the database.
/* */
CREATE OR REPLACE FUNCTION ( IN/OUT ,...) RETURN AS
--
BEGIN
--
RETURN ;
END;
9. DBMS_SCHEDULERスケジューリングタスク
公式説明:The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures that can be called from any PL/SQL program.
10.PACKAGEパッケージ
公式説明:The CREATE PACKAGE statement creates or replaces the specification for a stored package,which is an encapsulated collection of related procedures,functions,and other program objects stored as a unit in the database. 11. TRIGGERトリガ
公式説明:Triggers are defined using PL/SQL.Therefore, this section provides some general information but refers to Oracle Database PL/SQL Language Reference for details of syntax and semantics.
12.DBLINKデータベース接続
公式説明:Use the CREATE DATABASE LINK statement to create a database link.A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
/* */
--
DROP PUBLIC DATABASE LINK dblink ;
--
create database link dblink
connect to identified by
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = )
)
)';
--
select * from table@dblink ;13.SUBPROGRAMESサブルーチン
公式説明:A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly.If the subprogram has parameters, their values can differ for each invocation.
14.SCHEDULES Jobスケジューリングタスク
公式説明:Oracle Database includes Oracle Scheduler,an enterprise job scheduler to help you simplify the scheduling of hundreds or even thousands of tasks.Oracle Scheduler (the Scheduler) is implemented by the procedures and functions in the DBMS_SCHEDULER PL/SQL package.
以上、Oracleデータベース・オブジェクトについて説明しましたが、PL/SQLを使用して上記のオブジェクトの作成と使用方法について説明します.
/*PL/SQL */
HEADER ---
DECLARE ---
BEGIN ---
EXCEPTION ---
END; --- /* 、 、 */
-- Create table
create table AA
(
aaa VARCHAR2(10) not null,
bbb VARCHAR2(1),
cc TIMESTAMP(6),
dd NUMBER(12,2)
) ;
-- Create/Recreate indexes
create index BBBB on AA (BBB) ;
-- Create/Recreate primary, unique and foreign key constraints
alter table AA add constraint AAAA primary key (AAA) ;
-- AA
-- BBBB
-- AAAA