Oracle Rdb: SQL Update

Size: px

Start display at page:

Download "Oracle Rdb: SQL Update"

ぜんぺいいさやま
6 years ago
Views:

1 Day1-7 SQL Oracle Rdb , Oracle Corporation

2 RMU Extract SQL DDL SQL 2

4 SQL V SQL 4

5 7.2 Rdb RMU Oracle Rdb Rdb Installation and Configuration Guide SQL/Services Configuration Guide Guide to Distributed Transactions RMU Reference Manual CDD Trace ROR DDAL DBMS SQL

6 RMU Extract

7 RMU Extract V7.1.3 VIEW Extract UNION EXCEPT INTERSECT V7.1 RMU Extract V7.0.8 ( ) MATCH GROUP_TABLE 7

8 RMU Extract /DEFAULTS QUOTING RULES SQL92 DEFAULT DATE FORMAT SQL92 /DEFAULT=(DATE_FORMAT= VMS, LANGUAGE=SWEDISH,NOALLOCATI ON) 8

10 [ ] V4.1 RDMS$BIND_QG_TIMEOUT RDMS$BIND_QG_REC_LIMIT V4.2 RDMS$BIND_QG_CPU_TIMEOUT 10

11 [ ] V RDMS$BIND_QG_EXEC_CPU_TIMEOUT RDMS$BIND_QG_EXEC_ELAPSED_TIMEOUT exec 11

12 QG Interactive SQL SET Dynamic SQL SET RMU Show Statistics (exec ) Rdb SQL.INIT OCI Services 12

13 1 SQL> set query execution cont> limit elapsed time 5 minutes;... SQL> delete from EMPLOYEES; %RDB-E-EXQUOTA, Oracle Rdb runtime quota exceeded -RDMS-E-MAXTIMLIM, query governor maximum timeout has been reached SQL> 13

14 2 SQL> set query execution limit elapsed time 2 seconds; SQL> delete from EMPLOYEES; %RDB-E-EXQUOTA, Oracle Rdb runtime quota exceeded -RDMS-E-MAXTIMLIM, query governor maximum timeout has been reached SQL> show query limit; Query limit Time is OFF Query limit Row count is OFF Query limit CPU time is OFF Execution limit CPU time is OFF Execution limit Elapsed time 0 00:00:02.00 (2 seconds) Execution limit Row count is OFF SQL> set query execution nolimit elapsed time; 14

15 QG exec I/O CPU SQL RMU Show Statistics 15

16 SQL

17 ROUND TRUNC ROUND TRUNC Rdb V7.0 V7.1.3 SQL$FUNCTIONS DOUBLE PRECISION 17

18 ROUND TRUNC TINYINT SMALLINT INTEGER BIGINT REAL DOUBLE PRECISION DATE Rdb Server OCI Services for Rdb DATE ANSI TIMESTAMP DATE VMS 18

19 RDB$$IS_ROW_FRAGMENTED DECLARE FUNCTION Interactive SQL DBKEY 0( ) 1( ) 19

20 3 SQL> declare function RDB$$IS_ROW_FRAGMENTED cont> (in :dbk char(8) character set unspecified) cont> returns integer; SQL> SQL> select dbkey, cont> RDB$$IS_ROW_FRAGMENTED (dbkey) cont> from work_status; DBKEY 99:10: :10: :10: rows selected 20

21 DDL

22 Computed Columns ALTER TABLE ALTER COLUMN COMPUTED BY AUTOMATIC AS DROP/CREATE 22

23 Computed Columns class Computed by Automatic as Automatic Insert as Automatic Update as AUTOMATIC 23

24 DECLARE UNDECLARE Interactive SQL UNDECLARE SQL SQL$PRE 24

25 Declare SQL> declare :employee_id ID_NUMBER = ; SQL> select last_name from employees cont> where :employee_id = employee_id; LAST_NAME Toliver SQL> undeclare :employee_id; : DECLARE 25

26 Alter Sequence SQL:2003 RESTART WITH MINVALUE MAXVALUE TRUNCATE TABLE IDENTITY IDENTITY 1 26

27 Alter View COMMENT IS COMMENT ON VIEW RENAME TO RENAME VIEW WITH CHECK OPTION WHERE WITH NO CHECK OPTION 27

28 Alter View AS SELECT FROM WHERE SELECT 28

29 Alter View SQL> create view DEPARTMENTS_SUMMARY cont> as cont> select department_code, d.department_name, cont> d.manager_id, jh.employee_count cont> from departments d inner join cont> (select department_code, count (*) cont> from job_history cont> where job_end is null cont> group by department_code) cont> as jh (department_code, employee_count) cont> using (department_code); SQL> 29

30 Alter View( ) DEPARTMENTS_SUMMARY DROP VIEW CASCADE CREATE VIEW( DBA EMPLOYEE_COUNT 30

31 Alter View SQL> alter view DEPARTMENTS_SUMMARY cont> as cont> select department_code, d.department_name, cont> d.manager_id, d.employee_count cont> from departments d; SQL> - 31

32 Alter View 32

33 Storage Maps Create Storage Map Alter Storage Map Rdb$Storage_Maps (USING ) ALTER STORAGE MAP COMPILE 33

34 4 SQL> show system module Modules in database with filename mf_personnel RDB$STORAGE_MAPS SQL> show system function Functions in database with filename mf_personnel CANDIDATES_MAP COLLEGES_MAP DEGREES_MAP DEPARTMENTS_MAP EMPLOYEES_MAP 34

35 5 SQL> show function EMPLOYEES_MAP Information for function EMPLOYEES_MAP Function ID is: -2 Source: return case when (:EMPLOYEE_ID <= '00200') then 1 when (:EMPLOYEE_ID <= '00400') then 2 else 3 end case; Comment: Return value for select partition - range

36 5( ) Module name is: RDB$STORAGE_MAPS Module ID is: -1 Number of parameters is: 1 Parameter Name Data Type Domain or Type INTEGER Function result datatype Return value is passed by value EMPLOYEE_ID CHAR(5) Parameter position is 1 Parameter is IN (read) Parameter is passed by reference 36

38 /PROTOTYPES /C_PROTOTYPES C Pascal Bliss Basic C ANSI C C++ 38

39 Prototypes SQL RMU Rdb LANGUAGE 39

40 LANGUAGE C SQL$MOD/PROTOTYPE.SQLMOD C 40

41 module SAMPLE language C parameter COLONS declare alias filename SQL$DATABASE procedure START_TRANSACTION (sqlcode); start transaction; procedure COMMIT_TRANSACTION (sqlstate); commit work; 41

42 .h /* Generated by Oracle Rdb SQL X at 10-FEB :47:52.70 */ /* Source file is USER2:[TESTER.RDB$TEST_SYSTEM]XX.SQLMOD;1 */ #ifndef _SAMPLE_H_ #define _SAMPLE_H_ #ifdef cplusplus extern "C" { #endif /* cplusplus */ extern void START_TRANSACTION ( long *SQLCODE /* out */ ); extern void COMMIT_TRANSACTION ( void *SQLSTATE /* out */ ); #ifdef cplusplus } #endif /* cplusplus */ #endif /* _SAMPLE_H_ */ 42

44 GET DIAGNOSTICS LIMIT_CPU_TIME LIMIT_ROWS_FETCHED LIMIT_ELAPSED_TIME TRACE_ENABLED (7.1.4) GB18030(GB ) ( ) Dynamic EXECUTE INTO SQLDA 44

45 TRACE_ENABLED SQL> declare :x integer; SQL> begin get diagnostics :x = TRACE_ENABLED; end; SQL> print :x; X 0 SQL> set flags 'trace'; SQL> begin get diagnostics :x = TRACE_ENABLED; end; SQL> print :x; X 1 45

46 V DCL SET UIC SUBMIT/USERNAME CMKRNL UIC SQL/Services OCI Services impersonation) 46

47 SET UIC $ set uic jain $ sql$ SQL> attach 'file sql$database'; SQL> show privileges on database rdb$dbhandle; Privileges on Alias RDB$DBHANDLE (IDENTIFIER=[RDB,JAIN],ACCESS=SELECT+INSERT+ UPDATE+DELETE+SHOW+CREATE+ALTER+DROP+ DBCTRL+OPERATOR+DBADM+SECURITY+ DISTRIBTRAN) 47

48 LIMIT TO 7.2 LIMIT TO LIMIT TO n SKIP m ROWS SELECT m 48

49 LIMIT TO SKIP SQL>! Return employee with highest employee ID SQL> select last_name, first_name, employee_id cont> from employees cont> order by employee_id cont> limit to 1 cont> skip (select count(*)-1 from employees) rows; LAST_NAME FIRST_NAME EMPLOYEE_ID Herbener James row selected 49

50 metalink.oracle.com 50

Oracle Rdb: PowerPoint Presentation

Oracle Rdb: PowerPoint Presentation Day2-3 Itanium: T S Oracle Rdb 2006 4 4 2006 4 6 2005-2006, Oracle Corporation VAX/Alpha IEEE Rdb IEEE SQL SQL SQL 2 : 12340000 = 1.234 x 10 7 ( ) -1.234 x 10 7-1.234 x 10 7-1.234 x 10 7 (10-2 = 1/100)