Oracle Data Processing and oracle Big Data Processing

Oracle Data Processing and oracle Big Data Processing
DML Language &: address character; (PrepareStament) Batch Processing: insert -------- insert employees of Department 10 to a new table at a time; Do not write values statements; the Value List in the subquery should correspond to the column name in the insert substatement; the difference between delete and truncate: 1; delete is deleted one by one, while truncate destroys the table first, and then creates a new table; 2: the main difference is that there are two different languages. 3. delete does not release space, but truncate does. 4: delete does not produce fragments, but truncate does not. 5: delete can flash back, however, truncate cannot flash back. set feedback off command: The Execution Language is not displayed. Execute the SQL script command on the hard disk, for example, @ c: \ SQL. SQL; test: The delete operation is fast;
Oracle in DCL Language is automatically enabled and manually submitted (display: commit; implicit: normal exit, ddl statement) rollback (display: rollback; implicit: Abnormal exit, power failure, downtime); by saving the savepoint, you can better control things; by creating the save point command: savepoint to save the name; The transaction level supported by Oracle: read commited (default), SERIALIZABLE, read only;
DDL language objects have 12 tables: permissions and space are required during table creation. You can use subqueries to quickly create tables. You can use add, modfiy, drop, and rename to modify table fields; oracle recycle bin: show recyclebin to view the recycle bin; purge recyclebin to clear the recycle bin; not every user has a recycle bin. If the Administrator does not have a recycle bin, normal users will have a recycle bin. How can I retrieve the table in the recycle bin? --- Constraints on the flashback Course table: not null, unique, primary key, foreign key, and check. Generally, the table creation and creation constraints are separated for ease of reading. Example of constraints:

Create table student

(

Pid number constraint student_PK primary key,

Sname varchar2 (20) constraint student_name_notnull not null,

Gender varchar2 (2) constraint student_gender check (gender in ('male', 'female ')),

Email varchar2 (20) constraint student_email_unique unique

Constraint student_email_notnull not null,

Deptno number constraint student_FK references dept (deptno) on delete cascade

)

View: logically related data set extracted from the table; objective: to simplify search; not recommended: Modify the table through the view;

Sequence: sequences mainly serves to provide values for the table's primary key. It has two attributes: nextval and currval. It has the following characteristics: it is an array and exists in the memory. Data is not continuous: rollback, system exception-power failure. Multiple tables use one sequence;
Index: Improves the query speed. For example, create index myindex on emp (deptno, job); synonym: alias
Oracle plsql
The fastest language for operating Oracle is plsql; variable type: basic type, reference variable (% type), record variable (% rowtype); there are two Assignment Methods: ": = "and into keywords; set serveroutput on command: Open the console to print the output; accept num prompt 'Enter the 'command: accept the content entered on the keyboard; plsql's if statement must end with if end;
Cursor: cursor is used to store multiple rows of data returned by a query. attributes: % whether isopen is enabled; % rowcount no records; % notfound no records; Use cursor to open and close first; oracle supports up to 300 cursors; show paremeters allows you to view parameter settings; Case: Use the cursor to query the employee's name and salary, and print it; raise the employee's salary to 1000, the President 800, the manager 400, and the other; it also needs to manually submit the transaction; the cursor with parameters: its definition, with parameters when the cursor is opened, other usage is the same as the usage without the cursor; Case: query the employee name of a department
Exceptions: System-provided exceptions, custom exceptions. in Java, exceptions are handled upwards, while plsql cannot be throttled. try in java .. Catch .. Finally, while plsql only has try .. Catch is written as exception when .. Then... Custom exception in declare: exception name exception; run an exception using raise, the exception will help you automatically close the cursor;

Example 1: count the number of employees enrolled each year. Example 2: Raise the employee's salary. No one raised 10% from the minimum wage, but the total salary cannot exceed 50 thousand yuan; calculate the number of people who raise the salary and the total salary after the raise, and output the number of people who raise the salary and the total salary;
========================================================== =============== Stored Procedure (No return value ), stored functions (with returned values), trigger Java cannot directly call plsql, but is written as a stored procedure, stored functions, and then called in Java; created storage Name: create [or replace] procedure process name (parameter list) as plsql subroutine body; parameter type and input/output should be indicated; Do not store functions, do not submit things during stored procedures; the method to call the stored procedure: exec process name (); in another plsql call: begin process name (); end; these two methods; create a storage function: create [or replace] procedure process name (parameter list) return value type as plsql subroutine body; output parameters apply to out, stored procedures, stored functions can return one or more parameters, there is basically no difference between the two. Example 1: querying all information of all employees in a department requires a packet header and a packet body: When the returned result is a set, you can use the cursor to achieve it;

Oracle Data Processing

You can use the row-to-column conversion function. The script is as follows:
Insert into othertable (zddm, cjsj, f1110101, f1110102, f1110103) select zddm, cjsj, sum (f1110101) f1110101, sum (f1110102) f1110102, sum (f1110103) f1110103 from (select zddm, cjsj, decode (xdm, '000000', xsj, 0) f1110101, decode (xdm, '000000', xsj, 0) f1110102, decode (xdm, '000000', xsj, 0) f1110103 from table_name) a group by. zddm, z. cjsj -- create table othertable asselect zddm, cjsj, sum (f1110101) values, sum (f1110102) f1110102, sum (f1110103) f1110103 from (select zddm, cjsj, decode (xdm, '000000', xsj, 0) f1110101, decode (xdm, '000000', xsj, 0) f1110102, decode (xdm, '000000', xsj, 0) f1110103 from table_name) a group by. zddm, z. cjsj and above can be converted in this way when the number of data item codes is fixed. If the number of data item codes is uncertain, dynamic SQL is required. It is best to write a stored procedure and execute it every day.
If you have any questions, please ask. I hope this will help you.


In oracle, how does one process big data?

I. Big Data Storage Methods:
1. BLOB, binary Big Data
2. CLOB, text-type Big Data
3. BFILE (recommended): file-based big data, stored as a connection (file location), outside the database
4. RAW and binary big data (in earlier ORACLE versions, 9i is gradually replaced by BLOB, CLOB, and BFILE)
2. built-in big data processing functions
1. BLOB/CLOB/BFILE: DBMS_LOB package
2. RAW: UTL_RAW package