I. Basic KnowledgeFor ease of display, you can directly add the label (alias) sum (decode () after the field to count order by DESC/ASC sort distinct, without displaying duplicate. Group: Group by field, which matches the field before Select. Aggregate functions (max, Min, sum, avg) cannot appear in where. In this case, having is used to fuzzy query like a %, starting with. Table join from * join * on internal join left, right Outer Join (non-exact match) (+) non-customs subquery in not in related subquery cannot use * exists, you can use * Tables to merge union, but only display the merged intersect to return the rows that appear in both tables to insert multiple records: insert into * select result set insert into values, only one record can be inserted to create a table based on the existing table. Create Table TTT as (select * from *)
II.
PL/SQLSupplement: the front and back-end parameters are passed. PL/SQL: declare... begin .... exception... end output: dbms_output.put_line (''); | connection character, similar to + in other languages, no need to convert the type set serveroutput on size 10000 set the buffer size for output to the screen -- line comment/**/block comment if branch if... then... elsif... then... else... end if case branch when .. then .. when .. then .. elseendcase loop... end loop wihle loop while expression loop... end loop for loop goto Loop
Record:Type * is record, define record, (a table row) delcaretype myrecord is record (ID varchar2 (10), name varchar2 (10), real_record myrecord; and table field type, the structure of declaremyrec Table A % rowtype is the same as that of declaremyrec Table A % rowtype. eid % typebeginselect * into myrec from a where...
CursorCursor cursor: a PL/SQL control structure that allows you to control the display of SQL statement processing, so that you can process row data one by one. Cursor attributes: % found, % isopen, % notfound, % rowcount explicitly delcarecursor mycur isselect * From books; myrecord books % rowtype; beginopen mycur; fetch mycur into myrecord; while mycur % found loopdbms_out.put_line (myrecord. books_id); fetch mycur into myrecord; end loopclose mycur; end;/cursor parameter 1 delcarecursor mycur_para (ID varchar2) isselect books_name from books where books_id = ID; t_name books. books_name % type; begin open mycur_para ('001'); loopfetch mycur_para into t_name; exit when mycur_para % notfound; terminate (t_name); End loop close mycur_para; end; /cursor parameter 2 delcarecursor mycur_para (ID varchar2) isselect books_name from books where books_id = ID; begindbms_output.put_line ('***** result set :****'); for cur in mycur_para ('001') loop -- for loop does not need to turn off dbms_output.put_line (cur. books_name); End loop; end;/cursor isopendeclaret_name books. books_name % type; cursor mycur (ID varchar2) isselect books_name from books where books_id = ID; beginif mycur % isopen cursor ('cursor already opened '); elseopen mycur ('003 ') end iffetch mycur into t_name; close mycur; dbms_output.put_line (t_name); end; /You must add for updateupdate deptment set name = Name | '_ fujia' where current of mycur after the SELECT statement to modify data using the cursor. implicit: no declaration. Open, disable beginfor mycur in (seclect name from *) loop to print the output end loop; end;
Iii. Stored ProcedureDefinition: store common or complex tasks in advance with SQL statements and a specified name, to enable the database to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command. What is the difference between a stored procedure and a general SQL statement? Advantages of stored procedures: 1. the stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution. 2. when performing complex operations on the database (for example, performing update, insert, query, and delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database. 3. stored procedures can be reused to reduce the workload of database developers. high security. You can set only one user to have the right to use a specified stored procedure. Type of Stored Procedure: 1. system stored procedure: starts with SP _ and is used to set the system. obtain information. related management work. For example, sp_help is used to obtain information about the specified object. 2. the extended stored procedure starts with XP _ and is used to call the exec master function provided by the operating system .. xp_mongoshell 'Ping 10.8.16.1 '3. the user-defined stored procedure, which is the common format of the stored procedure, create procedure procedue_name [@ parameter data_type] [Output] [with] {recompile | encryption} As SQL _statement. Explanation: output: this parameter indicates that this parameter can be returned with {recompile | encryption} recompile: indicates that encryption is re-compiled every time this stored procedure is executed: the content of the created stored procedure is encrypted. Three values are returned for the stored procedure: 1. return an integer 2. return parameters in output format. the difference between the returned values of recordset: both output and return can be received using variables in a batch program, while recordset is passed back to create or replace procedure myproc (ID in varchar2 (10) in the client that executes the batch )) isname varchar2 (10); begin
Iv. Views, synonyms, Sequences
View(Secure, convenient, consistent, and virtual table): it is actually a query statement. It is a data display method. Create or replace view myviewasselect * From books; it is not feasible to update two base tables through the view. (Trigger) with group by, sum AVG and other Aggregate functions, or distinct, the view cannot be updated. With read only
Synonym: Abbreviation code. You can easily manipulate the object create synonym dept for Scott. Dept in different user modes; the current user's proprietary private. Drop synonym dept Delete create public synonym dept for Scott. Dept; Create public.
Sequence:?? Create sequence myseqstart with 1 increment by 1 ordernocycle;
5. triggers, tablespaces, and indexesTrigger: Automatic execution. parameters are not accepted. Transaction: the ability to ensure data integrity and concurrent processing. It regards one or more SQL statements as a logical unit to ensure that all these statements are successful/fail transactions: A (Atomicity): atomicity, consistency: consistency, consistent before and after the operation. I (isolation): isolation. It is closely integrated with concurrency and won't be submitted by itself (if not submitted, it seems to be updated under the current user, but it is not actually updated and must be submitted), commit; rollback; set the user for update. Other users can only wait and cannot perform operations. They can only perform operations after the user submits the operation. (Similar to "Lock") D (duralibility): permanent, commit and rollback, cannot be operated together
Row-level triggers: Implement Multi-Table integrity and atomic create or replace trigger del_deptidafter delete on deptmentfor each rowbegindelete from EMP where id =: Old. ID; end del_deptid;/operations not allowed in the trigger: If: Old. books_id = '2013' then raise_application_error (-0001, 'disallow delete ')
Statement-level triggers:Create or replace trigger dml_aaafter insert or delete or update on mytablebeginif inserting theninsert into mytable values ('''); elsif deleting then... else... end if; end;
Replace trigger(This trigger can only be created on a view and can be used to update multiple tables)
TablespaceCreate Table space tabsdatafile instance path/tabs. dbf' size 10 m authorization grant unlimited tablespace
Index: Increase the speed bitmap index (Bitmap index), (there are many data, such as gender, there are only a few) SQL * loader has data files and control file control files in advance may be in the form: load datainfile 'C:/loader.txt 'appendinto Table Name (M1 position () Char, -- the length is known and the same, otherwise you can use terminated ", "-- separated by commas (,) m2 position () Char) sqlldr control file path data file path
OEM: Enterprise management tools, in the browser
Vi. database backup and recoveryLogical backup exp test/test123 @ test extension name. dmp logical recovery imp test/test123 cold (offline), hot (online) physical backup cold: shutdown immediate, then copy the relevant content. Hot: view the archive log list in log archiving mode; Alter system set log_archive start = true scope = spfile; shutdown immediate; Startup Mount; Alter database archivelog; Alter database open; example: alter tablespace table begin backup; Copy things to alter tablespace table end backup; Alter system archive log current; Alter system switch logfile; shutdown immediate; after re-opening, check the error select * from V $ recover_file; Alter database datafile? Offline drop; Alter database open; last recover datafile ?; Alter database datafile? Online; backup control file alter Database Backup controlfile to trace after recovery; -- log files under udump under the admin instance are lost and then the log file alter database open resetlogs is re-generated;
