Dynamic SQL;
Declaresql varchar2 (100); ... SQL: = 'INSERT INTO' || Tablename || '(ID, Name) Vaules (: 1,: 2)'; Execute Immediate SQL Using: New.ID,: New.Name ; ================================================== ============================================================================================================== How to determine the difference between the two dates between two dates? SELECT TO_DATE ('2002-9-1', 'YYYY-MM-DD') - TO_DATE ('2002-8-19', 'YYYY-MM-DD') from DUAL
============================================================================================================================================================================================================= ==============================================
Alternative triggers provide a way to resolve problems that cannot modify the view. Alternative triggers can only be created on the view, which performs a PLSQL SQL code block instead of a DML statement. create [or replace] trigger trigger_name INSTEAD of xxx on view_name for each row DECLARE declarationsBEGIN plsql codeEND; / The following is an example: create or replace trigger emp_name_changeINSTEAD OF UPDATE ON emp_public_dateBEGIN if (: new.emp_name <>: old.emp_name) or ( : Old.emp_name is null and: new.emp_name is not null) THEN UPDATE EMPLOYEE SET EMP_NAME =: new.emp_name where EMP_ID =: new.emp_id; end if; end; / =========== ============================================================================================================================================================================================================= ================================================================================================================================================================================================
Text / Fan Sheng
I carefully prepare and test the following procedures. If you can type and test in SQLPlus, you can have a more comprehensive mastery of the process and PLSQL, and I will find more complex examples. First, the PLSQL table index can use any integer, can be EMP (0) .ename or EMP (-2) .ename II, I just tried a process of using the cursor type as parameters, please debug in SQLPLUS And experience its format.
create package pkgTest istype curTest is ref cursor; procedure sp_SelectData (s out curTest); end; / CREATE or replace package BODY pkgTest ASprocedure sp_SelectData (s out curTest) ismyname varchar2 (90); myno number; beginopen s for select ename, empno from EMP; loopFetch s Into MyName, MyNO; DBMS_OUTPUT.PUT_LINE (MyName); EXIT WHEN S% NOTFOUND; End Loop; Close S; End;
DECLAREMYNAME VARCHAR2 (22); TYPE CURTEST IS Ref Cursor; c_name curtest; beginpkgtest.sp_selectddata (c_name); - If only one cursor is declared in the package, then this is equivalent to taken a result set. END; / 3, understand the process, from shallow into the depth.
create or replace procedure abcisbegindbms_output.put_line ( 'Hello'); end abc; / beginabc; end; after / Review: create or replace procedure abcisvjob varchar (9); v_count number: = 0; vtotal date: = sysdate 7; c_tax Constant Number (3, 2): = 8.25; v_valid boolean not null: = true; begindbms_output.put_line ('hello' || to_char (vtotal)); end abc; / - Here, add the variables required inside the process, It seems that there is a process name that is an anonymous block. Beginabc; End; / - Execute. After modification, add an in parameter: create or replace procedure abc (testin in varcha2) isvjob varchar (9); v_count number: = 0; vtotal date: = sysdate 7; c_tax constant number (3, 2): = 8.25; v_valid boolean not null: = true; begindbms_output.put_line ('Hello' || to_CHAR (VTOTIN); DBMS_OUTPUT.PUT_LINE (TESTIN); End Abc; SQL> Execute ABC ('MyHome') Hello30- January -02Myhome
The PL / SQL process has been successfully completed. Remodel, add an OUT parameter: Create or Replace Procedure Abc (Testin in varcha2, testout out number isvjob varchar (9); v_count number: = 7; vtotal date: = sysdate 7; c_tax constant number (3, 2) : = 8.25; v_valid boolean not null: = true; begintestout: = v_count; dbms_output.put_line ('Hello' || to_CHAR (VTOTIN); DBMS_OUTPUT.PUT_LINE (TESTIN); End ABC; / This will calculate during the process The result passed a parameter to the process of calling variables outside of the process: DECLAREVARID VARCHAR (22): = 'Your Home'; Numbid Number; BeginAbc (Varid, NumbID); dbms_output.put_line (number); end; / Result: Hello30- January -02your home7
The PL / SQL process has been successfully completed. If it is inout, it is not an input at the same time.
============================================================================================================================================================================================================= ============================================== 回 段And practice / Fan Sheng
Since it is called segment, this is a logical name that is occupied by physical space. We believe that the segment is between the TABLESPACE and the Extent scale, and the rollback segment is also a type of segment. The rollback segment is Rollback Segment. Rollback Segment is a unit used in the database to keep the old value in the modified process, which stores the position and data of the original data before the editing. A Transaction can only use a rollback segment, but the incoming thing can share a Rollback Segment. The role of Rollback Segment is generally: things rollback, things recovery, providing read consistency. Type of Rollback Segment: System Rollback Segment: Used for objects for System tablespace. Non-System Rollback Segment: Two branches of the public and private. Deferred Rollback Segment: The Take Offline is used only in the table space. When Oracle is started at a certain instance, you will specify a few backlinks online. If the user does not specify a roll band, Oracle Server specifies the minimum returns to this matter. But sometimes there is too small or ORA-01555 Snapshot TOO OLD Error. This is a very common error. It is often encountered when programming and manipulating big data. There are two reasons for generating: 1, return The Chinese resource is reused. 2, the old data in the rollback segment is rewritten by another thing. The solution can expand the rollback segment, but the results may not be successful, you can try to specify a separate return segment for a mestination: In the block of PLSQL: SET Transaction Use Rollback Segment Rollback_SEGMENT can use a separately created rollback segment. Create a rollback statement: CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs STORAGE (INITIAL 100K NEXT 100K MINEXTENTS 20 MAXEXTENTS 100 OPTIMAL 2000K); Note that when you create: PCTINCREASE is always 0, INITIAL = NEXT, try not to make MAXEXTENTS to UNLIMITED, an error The program may cause a space unlimited. Alter rollback segment rbs01 online; put a rollback segment online. You can also modify the maximum expansion of Rollback Segment in INITSID.ORA: ALTER ROLLBACK Segment RBS01 Storage (MAXEXTENTS 200); Delete: Drop Rollback Segment RBS01; We can learn about the related rollover segment by data dictionary Information: SQL> Select Segment_name, TableSpace_name, Owner, Status from DBA_ROLLBACK_SEGS; if you need to optimize database performance, you should determine how much and size of the rollback segment according to the direction developed by your database. For example, in OLTP, there is a lot of small rollback segments, and each returning segment is taken about 4 things.
In the DSS system, because fewer things are small, only a small amount of rollback segments are required, and the average of each thing applies a rollover segment. In summary, for the master of rollback segments, these can cope with most applications, and in-depth content should look at Oracle documents, and practice a lot. For DBA requirements: I hope that DBA can reach the point of knowledge of concepts and regular knowledge points. ============================================================================================================================================================================================================= ====================================================== SQL - JOIN Full usage
Outline. The outer joint may be left outwardly, and the right outwardly or completely external connection. When an external connection is specified in the FROM clause, you can specify a set of groups in the following groups: Left Join or Left Outer Join. The result set of left outwardly links includes all rows of the left table specified in the Left Outer clause, not just the lines matching the joint column. If a row of the left table does not match the line in the right, all the selected list columns of the right table in the associated result collection are null. Right Join or Right Outer Join. Right outwardly is the reverse connection of left abutment. Will return all rows of the right table. If a row of the right table is not matched in the left table, the null value will be returned for the left table. Full Join or Full Outer Join. Complete external join returns all rows in the left table and the right table. When a row does not match the line in another table, the selection list column of another table contains a null value. If there is a match between the table, the entire result is a list of data values for the base table. The inner linkage returns only when there is at least one of the row of lines belonging to the two tables. Inner connected eliminates the rows that do not match the other table. The outer joint returns all the lines of at least one table or view mentioned in the FROM clause, as long as these rows meet any WHERE or HAVING search criteria. All rows of the left table referenced by the left outward joint reference will be retrieved, and all rows of the right to the right of the right outwardly. All rows of the two tables in the complete external join will return. Microsoft? SQL Server? 2000 Use the following SQL-92 keywords specified in the FROM clause: Left Outer Join or LEFT JOINRight Outer Join or Right Joinfeal Outer Join or Full Join SQL Server supports SQL-92 outslon joint syntax, And using the * = and = * operators in the WHERE clause specify the old grammatical syntax of the outer join. Since SQL-92 syntax is not easy to generate ambiguity, and old TRANSACT-SQL external joints sometimes generate ambiguity, so it is recommended to use SQL-92 syntax. Use the left outwardly assumed assumption to join the Authors table and the Publishers table on the City column. The results are only displayed in the city where the publisher is located (this example is Abraham Bennet and CHERYL CARSON). To include all authors in the results, regardless of whether the publisher live in the same city, use SQL-92 left outwardly.
The following is queries and results of Transact-SQL left outwardly: Use pubsselect a.au_fname, a.au_lname, puB_namefrom authors a left outer Join Publishers Pon a.city = p.cityorder by p.PUB_NAME ASC, A.AU_LNAME ASC, A.AU_FNAME ASC The following is the result set: AU_FNAME AU_LNAME PUB_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------- ----------------- Reginald Blotchet-Halls Nullmichel Defrance Null INNES DEL CASTILLO NULLANN DULL NULL MARJORE GREEN NULL MORNINGSTAR GREENE NULL Burt Gringlesby NULL Sheryl Hunter NULL Livia Karsen NULL Charlene Locksley NULL Stearns MacFeather NULL Heather McBadden NULL Michael O'Leary NULL Sylvia Panteley NULL Albert Ringer NULL Anne Ringer NULL Meander Smith NULL Dean Straight NULL Dirk Stringer NULL Johnson White NULL Akiko Yokomoto NULL Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems (23 Row (s) Affected No matter whether it matches the city column in the Publishers table, Left Outer Join contains all rows of the Authors table in the results. Note: Most authors listed in the results do not match data, so the PUB_NAME columns of these rows contain null values. Use the right outwardly assume that the AUTHORS table and the Publishers table are coupled to the City column. The results are only displayed in the city where the publisher is located (this example is Abraham Bennet and CHERYL CARSON). SQL-92 Right Out Outer Dip Operator Right Outer Join Indicates: Regardless of whether there is a match in the first table, the result will contain all rows in the second table. To include all publishers in the results, regardless of whether there are publishers in the city living in the city, please use SQL-92-to-right outwardly.
Below is the query and result of Transact-SQL right outwardly: use pubsselect a.au_fname, a.au_lname, puB_namefrom authors as a right = p.cityorder by p.city = p.cityorder by P.PUB_NAME ASC, A .au_lname ASC, A.au_fname ASC below is the result set: AU_FNAME AU_LNAME PUB_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- -------------------- Abraham Bennet Algodata Infosystemschel Carson Algodata Infosystemsnull Null Binnet & Hardley Null Null Five Lakes Publish Null Null GGG & g Null NULL Lucerne Publishing Null Null New Moon Books Null Null Ramona Publishers Null Null Scootney Books (9 row (s) Affected) Use predicates (such as comparing coupling and constant) to further limit the outer join. The following example contains the same right outwards, but eliminates a book name below 50 books: Use Pubsselect S.Stor_ID, S.Qty, T.TitleFrom Sales S Right Outer Join Titles T ====== ============================================================================================================================================================================================================= ======================================== only return the first 3 records SQL> SELECT ROWNUM , Month, Sell From Sale Where Rownum <4;
Rownum Month Sell -------------- --------- 1 200001 1000 2 200002 1100 3 200003 1200
How to achieve greater than logic with ROWNUM? (Returns the data between ROWNUMs at 4-10) (Minus operation, speed will be affected) SQL> Select Rownum, Month, Sell from sale where rownum <10 2 minus 3 Select Rownum, Month, Sell From Sale Where Rownum <5 ;
Rownum Month Sell -------------- --------- 5 200005 1400 6 200006 1500 7 200007 1600 8 200101 1100 9 200202 1200 ======== ============================================================================================================================================================================================================= ============================================= - Auto number trigger Create or Replace Trigger Trigger1 Before INSERT ON AABB1 for Each RowBegInselectr Max (ORDERID) 1 INTO: New.Orderid from Dual; End; ============================ ============================================================================================================================================================================================================= ================== - Exceptions when there is no data found
Declarei Number; S1 VARCHAR (20); Begin Select * INTO I, S1 from Aabb1; Exception When No_Data_found the dbms_output.put_line ('NO Data Found.');
============================================================================================================================================================================================================= ============================================== exception handle :: ::::
1. PLSQL abnormal processing exception is generated by Oracle errors or explicitly throws an error. How to deal with: use a handler to capture it; pass it to calling environment II, anomaly type: Oracle Server predefined error Non-Oracle Server predefined error, but also Oracle Server standard error User custom unusual three, capture abnormal Key points: when odser Clause is placed behind all capture conditions. Error handles up to one when the Others Clause. The exception segment begins with Exception. Keyword. We can define different EXCEPTION handles to capture exceptions. When processing an exception, only one statement can be processed. EXCEPTION WHEN exception1 [... OR exception2] THEN statement1; Fourth, conventional error or exception: NO_DATA_FOUND ORA-01403 TOO_MANY_ROWS ORA-01422 INVALID_CURSOR ORA-01001 ZERO_DIVIDE ORA-01476 DUP_VAL_ON_INDEX ORA-00001 V. Examples PROCEDURE elim_inventory (v_product_id IN s_product. id% TYPE) IS v_id s_product.id% TYPE; BEGIN SELECT id INTO v_id FROM s_product WHERE id = v_product_id; DELETE FROM s_inventory WHERE product_id = v_product_id; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; TEXT_IO.PUT_LINE (TO_CHAR (v_product_id) || 'is invalid.'); WHEN TOO_MANY_ROWS THEN ROLLBACK; TEXT_IO.PUT_LINE ( 'Data corruption in S_PRODUCT.'); WHEN OTHERS THEN ROLLBACK; TEXT_IO.PUT_LINE ( 'Other error occurred.'); END elim_inventory; excerpt from oracle plsql guide DECLAREERR_NUM NUMBER; Err_Msg Varchar2 (100); recomsti; err_msg: = SUBSTR (SQlerRM, 1, 100); in SERT INTO ERRORS VALUES (ERR_NUM, ERR_MSG);
Use in the Scott environment to be used to change change ========================================= ====================================================== in Form, What are the exception (exceptions), how to write it? Software Environment: 1. Server: Windows NT4.0 Oracle 8.0.4 Oracle Installation Path is: C: / ORANT2, Client: Windows 98, Developer / 2000 Client Installation (Version 2.1) Implementation Method: Begin "PL / SQL block "; Exception When no_data_found dam - Data" Response Command "is not found; when h __many_rows then - Return Multi-line, implicit cursor can only retrieve a line of data" Response Command "each time; when invalid_number dam Conversion failed "Response Command"; when Zero_divide THEN - is zero "Response Command"; when inserts the repeating data "Response Command" to the unique index; when invalid_cursor the - illegal cursor operation "Response Command"; WHEN value_ERROR THEN - digital, data conversion, cross-string, or mandatory error "Response command"; When Others the - happens any error null; - Select one: Nothing, just when the error does not occur Raise form_trigger_failure; - Select 2: Suspend the current program END;
============================================================================================================================================================================================================= =======================================
Decode () function use skills
Meaning Explanation: decode (condition, value 1, translation value 1, value 2, translation value 2, ... value n, translation value N, default)
The function of this function is as follows: IF condition = value 1 THEN RETURN (translation value 1) ELSIF condition = value 2 Then return (translation value 2) ... Elsif Condition = value n Then return (translation value n) Else Return (Default) End if ============================================ ============================================== 用户 用户 用户 SELECT * From v $ session where lower (terminal) = 'wangliang'; alter system kill session 'sID, serial #';
It should be Alter System Kill Session 'SID, Serial #' or use Tools such as Toad, PL / SQL Developer. In addition, SELECT VP.SPIDFROM V $ Session VS, V $ process vpwhere vs.paddr = vp.addr - Not clear and vs.machine = 'Your Machine Name'and Other Conditions
The SPID is the process number under UNIX, and Kill is off. ============================================================================================================================================================================================================= ======================================================================================================================================================================================================================================================================
CREATE TABLE DUP_TABLE AS (SELECT * from ORG_TABLEWHERE 1 = 0);
============================================================================================================================================================================================================= ======================================= 表 copy: Use Copy from usage: Copy from < DB> to
