• 1. Batch Data Operation
  • 1.1. Batch Generation Data
  • 1.2. Batch insertion data
• 2. Batch generation script
• 3. Generating Data Dictionary
• 4. Common System Packages
  • 4.1,DBMS_OUTPUT
  • 4.2,DBMS_RANDOM
  • 4.3. Other System Packages and Common Methods
• 5. Summary

1. Batch Data Operation

1.1. Batch Generation Data

Generate integers between 1 and 5

SELECT ROWNUM,LEVEL,LAG(LEVEL) OVER(ORDER BY LEVEL) lag,LEAD(LEVEL) OVER(ORDER BY LEVEL) lead 
FROM DUAL CONNECT BY ROWNUM <= 5;

Generate results:

    ROWNUM      LEVEL        LAG       LEAD
---------- ---------- ---------- ----------
         1          1                     2
         2          2          1          3
         3          3          2          4
         4          4          3          5
         5          5          4 

Random Generation of Five Data

SELECT SYS_GUID() ora_guid,LEVEL inc_number,fn_now+LEVEL/24/3600 inc_date,
DBMS_RANDOM.STRING('X',8) random_string,
TRUNC(DBMS_RANDOM.VALUE(0,100000)) random_number
FROM DUAL CONNECT BY LEVEL <= 5;

Generate results:

ORA_GUID                         INC_NUMBER INC_DATE    RANDOM_STRING  RANDOM_NUMBER
-------------------------------- ---------- ----------- -------------- -------------
44F51C83A2964B1F81C60DBBA8BD7206          1 2017-01-10  ESL1LWPB               83888
6336F565364849889FBDC817B761E315          2 2017-01-10  7HNXAHUR               14726
C0B2BF9E4AAB4B95B9F1CB03A0582097          3 2017-01-10  ZZO8OAK3               82615
82C4A6F29BCA46BE89CA797D74F391C6          4 2017-01-10  K8ZNTRS8               54534
38F0552F1C724805A4D3E6AD54DEB43D          5 2017-01-10  4ZHL52OA               95298

Constructing Equivariance Sequence

SELECT LEVEL n1, LEVEL*2 n2, LEVEL*2-1 n3, fn_today+LEVEL-1 dt1, 
fn_today+NUMTOYMINTERVAL(LEVEL-1,'month') dt2,
fn_today+NUMTOYMINTERVAL(LEVEL-1,'year') dt3
FROM DUAL CONNECT BY LEVEL <= 5;

Tectonic results:

        N1         N2         N3 DT1         DT2         DT3
---------- ---------- ---------- ----------- ----------- -----------
        1          2          1 2017-01-10  2017-01-10  2017-01-10
        2          4          3 2017-01-11  2017-02-10  2018-01-10
        3          6          5 2017-01-12  2017-03-10  2019-01-10
        4          8          7 2017-01-13  2017-04-10  2020-01-10
        5         10          9 2017-01-14  2017-05-10  2021-01-10

Constructing Second Order Equivariance Sequences

SELECT LEVEL num,SUM(LEVEL) OVER(ORDER BY LEVEL) fac FROM DUAL CONNECT BY LEVEL <= 5;

Tectonic results:

    NUM        FAC
---------- ----------
        1          1
        2          3
        3          6
        4         10
        5         15

1.2. Batch insertion data

Insert 1 million pieces of data into the t3 table at a time (it takes more than 20 seconds to operate on my laptop)

BEGIN
  FOR i IN 1 .. 1000000 LOOP
    INSERT INTO t3(f1) VALUES(i);
  END LOOP;
  COMMIT;
END;

Turn the data in the t2 table into 2 ^ 5 times the current one (if there were 3 rows in the t2 table, the number of rows in the t2 table would be 3 + (3 * 2 ^ 0) + (3 * 2 ^ 1) + (3 * 2 ^ 2) + (3 * 2 ^ 3) + (3 * 2 ^ 4), or 3 * 2 ^ 5).

BEGIN
  FOR i IN 1 .. 5 LOOP
    INSERT INTO t2 SELECT t2.* FROM t2;
  END LOOP;
  COMMIT;
END;

2. Batch generation script

Generate statements that empty data in all tables of the current user

SELECT 'DELETE FROM '||LOWER(USER)||'.'||LOWER(t.table_name)||' t;' FROM USER_TABLES t;
SELECT 'TRUNCATE TABLE '||LOWER(USER)||'.'||LOWER(t.table_name)||';' FROM USER_TABLES t;

Generate statements that delete all tables of the current user

SELECT 'DROP TABLE '||LOWER(USER)||'.'||LOWER(t.table_name)||' PURGE;' FROM USER_TABLES t;

Generate statements that delete all objects of the current user

SELECT 'DROP '||t.object_type||' '||LOWER(USER)||'.'||LOWER(t.object_name)||';'
FROM USER_OBJECTS t ORDER BY t.object_type;

Generate statements that disable all triggers for the current user

SELECT 'ALTER TRIGGER '||LOWER(USER)||'.'||LOWER(t.trigger_name)||' DISABLE;'
FROM USER_TRIGGERS t WHERE t.status='ENABLED';

Generate statements that authorize fox to all tables beginning with T_for the current user

SELECT 'GRANT SELECT ON '||LOWER(USER)||'.'||LOWER(t.table_name)||' TO fox;'
FROM USER_TABLES t WHERE t.table_name LIKE 'T/_%' ESCAPE '/';

Generate statements that query data in the T_COURSE table

SELECT 'SELECT '||WM_CONCAT('t.'||LOWER(t.column_name))
  ||' FROM '||LOWER(USER)||'.'||LOWER(MAX(t.table_name))||' t WHERE 1=1;'
FROM USER_TAB_COLUMNS t WHERE t.table_name='T_COURSE';

Generate statements:

SELECT t.course_id,t.course_name,t.course_desc FROM demo.t_course t WHERE 1=1;

Generate statements that insert data into the T_COURSE table

Style 1:

SELECT 'INSERT INTO '||LOWER(USER)||'.'||LOWER(MAX(t.table_name))
  ||'('||WM_CONCAT(LOWER(t.column_name))
  ||') VALUES('||WM_CONCAT(':'||LOWER(t.column_name))||');'
FROM USER_TAB_COLUMNS t WHERE t.table_name='T_COURSE';

Generate statements:

INSERT INTO demo.t_course(course_id,course_name,course_desc)
VALUES(:course_id,:course_name,:course_desc);

Style 2:

SELECT 'INSERT INTO '||LOWER(USER)||'.'||LOWER(MAX(t.table_name))
  ||'('||WM_CONCAT(LOWER(t.column_name))
  ||') VALUES('||WM_CONCAT(':'||REPLACE(INITCAP(t.column_name),'_',''))||');'
FROM USER_TAB_COLUMNS t WHERE t.table_name='T_COURSE';

Generate statements:

INSERT INTO demo.t_course(course_id,course_name,course_desc)
VALUES(:CourseId,:CourseName,:CourseDesc);

Generate statements that modify data in the T_COURSE table

Style 1:

SELECT 'UPDATE '||LOWER(USER)||'.'||LOWER(MAX(t.table_name))
  ||' t SET '||WM_CONCAT('t.'||LOWER(t.column_name)||'=:'
  ||LOWER(t.column_name))||' WHERE 1=1;'
FROM USER_TAB_COLUMNS t WHERE t.table_name='T_COURSE';

Generate statements:

UPDATE demo.t_course t
SET t.course_id=:course_id,t.course_name=:course_name,t.course_desc=:course_desc
WHERE 1=1;

Style 2:

SELECT 'UPDATE '||LOWER(USER)||'.'||LOWER(MAX(t.table_name))
  ||' t SET '||WM_CONCAT('t.'||LOWER(t.column_name)||'=:'
  ||REPLACE(INITCAP(t.column_name),'_',''))||' WHERE 1=1;'
FROM USER_TAB_COLUMNS t WHERE t.table_name='T_COURSE';

Generate statements:

UPDATE demo.t_course t
SET t.course_id=:CourseId,t.course_name=:CourseName,t.course_desc=:CourseDesc
WHERE 1=1;

3. Generating Data Dictionary

Query statement:

SELECT t1.tablespace_name "Table space",USER "Pattern",t1.table_name "Table name",
NULL "Field serial number",NULL "Field name",NULL "data type",NULL "Field Length",
NULL "accuracy",NULL "Decimal digits",NULL "Can it be empty?",NULL "Default values",t2.comments "Notes"
FROM USER_TABLES t1
LEFT JOIN USER_TAB_COMMENTS t2 ON t1.table_name=t2.table_name
UNION ALL
SELECT t1.tablespace_name "Table space",USER "Pattern",t1.table_name "Table name",
t3.column_id "Field serial number",t3.column_name "Field name",t3.data_type "data type",
t3.data_length "Field Length",t3.data_precision "accuracy",t3.data_scale "Decimal digits",
t3.nullable "Can it be empty?",t3.data_default "Default values",t2.comments "Notes"
FROM USER_TABLES t1
LEFT JOIN USER_COL_COMMENTS t2 ON t1.table_name=t2.table_name
LEFT JOIN USER_TAB_COLUMNS t3 ON t1.table_name=t3.table_name AND t2.column_name=t3.column_name
ORDER BY "Table name","Field serial number" NULLS FIRST;

Query results (limited to length, only part of the result set is intercepted here):

Table Space Schema Table Name Segment Number Field Name Data Type Field Length Accuracy Decimal Null Default Value Annotation
------- ----- --------- -------- ------------ --------- -------- ----- ------ -------- ------ ------------
USERS   DEMO  T_COURSE                                                                        
USERS DEMO T_COURSE 1 COURSE_ID NUMBER 22 100N Course Primary Key ID
 USERS DEMO T_COURSE 2 COURSE_NAME VARCHAR2 50Y Course Name
 USERS DEMO T_COURSE 3 COURSE_DESC VARCHAR2 2000 Y Course Description
......

Note: This is a very interesting phenomenon. The default data_default field in the result set queried by PL/SQL Developer is of type LONG. After seeing this, I wanted to convert the query statement into a string. Later, I found that Oracle did not provide functions or grammars of LONG-type character conversion. If I had to do so, I had to write my own functions. All in all, it was quite tedious. Later, I found that if I export data directly to Excel through the result set window of PL/SQL Developer, the default column is automatically converted to a string. Later, I found that executing queries through command windows automatically converts default columns into strings. In a word, instead of bothering to convert the LONG type, the data dictionary can be generated directly through PL/SQL Developer.

4. Common System Packages

In order to facilitate the development of PL/SQL programs, Oracle database provides hundreds of system packages. This machine will focus on several common system packages and common methods.

4.1,DBMS_OUTPUT

DBMS_OUTPUT The main function of packages is to input or output messages in PL/SQL programs, such as sending debug messages to buffers through stored procedures and triggers.

Grammar and description of common subroutines:

DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2); -- Output data to the buffer and line breaking
 DBMS_OUTPUT.PUT(item IN VARCHAR2); -- Adding data to the buffer, but not changing lines and not displaying it. Executing NEW_LINE or PUT_LINE can display all previous data.
DBMS_OUTPUT.NEW_LINE; -- Output a newline to the buffer
 DBMS_OUTPUT.DISABLE; -- Used to close input and output while emptying the buffer
 DBMS_OUTPUT.ENABLE([buffer_size IN NUMBER]); for opening input and output

Comprehensive examples:

BEGIN
  DBMS_OUTPUT.PUT_LINE('A'); -- output A
  DBMS_OUTPUT.DISABLE;       -- Prohibit DBMS_OUTPUT And clearance A
  DBMS_OUTPUT.PUT('B');      -- Because the output is closed, no additional output will be added. B
  DBMS_OUTPUT.ENABLE;        -- Enable DBMS_OUTPUT
  DBMS_OUTPUT.PUT('C');      -- Append C
  DBMS_OUTPUT.PUT('D');      -- Append D
  DBMS_OUTPUT.NEW_LINE;      -- output CD Parallel Line Change
  DBMS_OUTPUT.PUT_LINE('E'); -- output E Parallel Line Change
  DBMS_OUTPUT.PUT('F');      -- Append F，But not in the back. NEW_LINE or PUT_LINE，So it won't show.
END;

Output results:

CD
E

4.2,DBMS_RANDOM

DBMS_RANDOM Packets provide a built-in random number generator that can be used to quickly generate random numbers and random strings.

RANDOM: Returns an integer in the range [- 2 ^ 31, 2 ^ 31].

SELECT DBMS_RANDOM.RANDOM res FROM DUAL; -- res: -699438152

NORMAL: Returns the random number in the normal distribution. The standard deviation of the normal distribution is 1 and the expected value is 0. 68% of the values returned by this function are between - 1 and + 1, 95% between - 2 and + 2, and 99% between - 3 and + 3.

SELECT DBMS_RANDOM.NORMAL res FROM DUAL; -- res: 0.763005475791809

STRING(opt IN CHAR,len IN NUMBER): Returns a random string, where opt refers to the format of the string and Len refers to the length of the string.

SELECT DBMS_RANDOM.STRING('u', 10) res FROM DUAL; - res: ADKXBWIOMI, capital letter
 SELECT DBMS_RANDOM.STRING('l', 10) res FROM DUAL; - res: mupmuqdoue, all lowercase letters
 SELECT DBMS_RANDOM.STRING('a', 10) res FROM DUAL; - res: AdOhEwGByt, mixed upper and lower case letters
 SELECT DBMS_RANDOM.STRING('x', 10) res FROM DUAL; - res: OMUBEPN3C2, capital letters or numbers
 SELECT DBMS_RANDOM.STRING('p', 10) res FROM DUAL; - res: b+[5 $ot = w |, any printable character

VALUE: Returns random numbers in the [0,1] range with a precision of 38 bits.

SELECT DBMS_RANDOM.VALUE res FROM DUAL; -- res: 0.381593460771342

VALUE(low IN NUMBER,high IN NUMBER): Returns random numbers in the [low, high] range.

SELECT DBMS_RANDOM.VALUE(10,20) res FROM DUAL; -- res: 13.650786652248

INITIALIZE (val IN BINARY_INTEGER) & SEED (seed IN BINARY_INTEGER | VARCHAR2): Sets the seed value used to initialize the DBMS_RANDOM package. The only difference between INITIALIZE and SEED is that INITIALIZE only supports numbers, while SEED supports both numbers and strings. In addition, one of the functions of SEED is to replace INITIALIZE.

By default, the DBMS_RANDOM package is initialized based on user, time, session and other information. In other words, even for the same statement, the seeds are uncertain at each generation. At this time, a fixed seed can be set through INITIALIZE or SEED to ensure that the random sequence is consistent at each generation.

BEGIN
  DBMS_RANDOM.SEED('ABC123'); -- Setting seed values ABC123
  FOR i IN 3 .. 9 LOOP
    DBMS_OUTPUT.PUT(DBMS_RANDOM.RANDOM||'|');
  END LOOP;
  DBMS_OUTPUT.NEW_LINE;
END;

Output results:

-219386465|-850200733|-240588365|-351313939|-1206831363|852217108|-1045006337|

4.3. Other System Packages and Common Methods

DBMS_METADATA The GET_DDL method in the package is used to get the object definition statement (DDL statement) stored in the data dictionary, and the return value is CLOB type.

Grammar:

DBMS_METADATA.GET_DDL(
  object_type IN VARCHAR2,
  name        IN VARCHAR2,
  schema      IN VARCHAR2 DEFAULT NULL,
  version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model       IN VARCHAR2 DEFAULT 'ORACLE',
  transform   IN VARCHAR2 DEFAULT 'DDL'
);

Examples:

-- Query Definition T_COURSE Table DDL Sentence
SELECT DBMS_METADATA.GET_DDL('TABLE','T_COURSE') FROM DUAL;
-- Query Definition V_STAFF Viewed DDL Sentence
SELECT DBMS_METADATA.GET_DDL('VIEW','V_STAFF') FROM DUAL;
-- Query Definition SP_STAFF_STATUS Stored procedure DDL Sentence
SELECT DBMS_METADATA.GET_DDL('PROCEDURE','SP_STAFF_STATUS') FROM DUAL;
-- Query Definition TRG_STAFF_ID Flip-flop DDL Sentence
SELECT DBMS_METADATA.GET_DDL('TRIGGER','TRG_STAFF_ID') FROM DUAL;

DBMS_LOB The SUBSTR method in the package is used to intercept CLOB-type values from the specified offset and return them as strings.

Grammar:

DBMS_LOB.SUBSTR(
  lob_loc IN CLOB CHARACTER SET ANY_CS,
  amount  IN INTEGER := 32767,
  offset  IN INTEGER := 1
);

Examples:

SELECT DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('TABLE','T_COURSE')) FROM DUAL;

DBMS_DDL The ALTER_COMPILE method in the package is used to compile the specified schema object in the data.

Grammar:

DBMS_DDL.ALTER_COMPILE(
  type           VARCHAR2, 
  schema         VARCHAR2, 
  name           VARCHAR2
  reuse_settings BOOLEAN := FALSE
);

Examples:

BEGIN
  DBMS_DDL.ALTER_COMPILE('PROCEDURE','DEMO','SP_STAFF_STATUS');
END;

It was mentioned in previous articles in this series of blogs. DBMS_JOB,DBMS_SCHEDULER,DBMS_XPLAN,DBMS_SQL In fact, there are many powerful system packages in Oracle. For example, you can read and write operating system text files through UTL_FILE system packages, and you can even use UTL_HTTP system packages to access data on the Internet through HTTP to extract the content of the specified web pages. If you want to find more practical system packages, you can take a look at them. <Oracle Database PL/SQL Packages and Types Reference> This is a comprehensive introduction manual on Oracle 10g system packages.

5. Summary

This paper mainly describes the techniques of batch data generation by hierarchical query, batch data insertion by loop, batch script generation by data dictionary, data dictionary generation and system package.