SQL Server -> Oracle (1)
Support Oraclezhuzhichao 2002.4 allows Oracle-based program-based Support SQL Server, which is easy and pleasant; but so that SQL Server developed Support Oracle is not so simple, you may face a lot of incoming problems and do it for this Many work. Oracle has received many DBAs in their highly stability and safety, but its bad operability and ease of use have also made most of the Programmer deeply painful. SQL Server T- SQL is so free and flexible, Oracle's PL / SQL is so rigorous scribes, SQL Server -> Oracle will make you feel so don't happen. Do you spend two days without transferring a 300-line SP? ... Do you delay a few days of work during a difficult problem? ... If so, please continue to look at the content below. First. Basic II. How to return the result set? Third. About the temporary table four. Perform dynamic SQL 5. How to transplant it from Six. About trigger seven. Inline knot, external joint, all joint eight. What work needs to do in F2 normal open window? 9. Top N and ROWNUM Ten. Performance Optimization Eleven. Other ... II. How to return the result set? "Tian, the SP of the SP can't return the result set !!!" ... Will you send this exclaim in the work of SQL ServeràOracle? ... In any PL / SQL statement in Oracle, all SELECT statements must have an INTO clause! This is the reason for Oracle's SP can't return the result set! Anything has a solution, you don't want to this solution is discouraged following routine: create or replace package pkg_test astype cur_test is ref cursor; - defined typeend pkg_test of a cursor; / create or replace procedure p_test (v_cur out pkg_test.cur_test) asv_sql varchar2 (100) ; - beginv_sql: = 'SELECT A1, A2 from Test'; Open V_Cur for v_sql; --Exceptionwhen Others Ten DBMS_OUTPUT.PUT_LINE ('Error -------------' || sqlcode || : '|| SQlerRM; END P_TEST; / JAVA program: ... Callablestatement call = conn.preparecall ("{call p_test (?)}"); call.registeroutparameter (1, oracletypes.cursor); // Register OUT parameters SQL data type call.execute (); resultset = (resultSet) call.getObject (1); // get the data result collection while (rs.next ()) ... .... III. About temporary Table You must find a lot of places to use the temporary table in the SP of SQL Server; ... You must also find that in the Oracle's SP cannot be creatored;
... You must also find out that the temporary table in Oracle and the temporary table in SQL Server is not a concept. It can't reach the role of the temporary table in SQL Server! ... all this is why ??? How will it be solved? Holding so many questions, let's take a look at the temporary table of SQL Server. There are two types of SQL Server: local temporary table (#temp) and global temporary table (## temp) local temporary table is independent Every session, for each session, even if both Create is the same name #temp but every session is independently operates your #temp, it does not interfere with each other. Thesession exits automatically released its own #TEMP. Once the global temporary table Create, all session can be used, but only when the session exits when the global temporary table is created, it is worth mentioning that all SQL Server's temporary table is stored in the TEMPDB database, SQL Server6 .5 has the option of Tempdb in Ram, although the version after 7.0 is canceled. However, as long as your Tempdb is allocated enough hard disk space, the performance of the temporary table is not reduced during multiple users concurrently, and each After a period of SQL Server, the TEMPDB will automatically make a fragmentation to ensure performance. However, the process mechanism of the temporary table in Oracle and the process mechanism of the temporary table in SQL Server is complete. Oracle's temporary table top can only be equivalent to SQL Server Global temporary table, and it will always exist, if you don't go to Drop, oracle will not release it automatically. And the local temporary table in SQL Server cannot replace it in Oracle, so I gave up in Oracle. For temporary tables, all temporary tables are all changed to permanent tables. DDL statements cannot appear in Oracle's SP. Because all DDL statements (Create, DROP, ALTER, TRUNCATE, etc.) are explicitly commit commands, There is no existence of explicit commit in Oracle's SP. If you want to build a table or delete a table in SP, you can use dynamic SQL to complete implicit commit. For example: execute immediate "create table ..."; SQL, there will be a detailed introduction later. There will be two misunderstandings here, and improper handling will affect performance or cause an error: 1. Use dynamic SQL to build a permanent table with dynamic SQL directly in SP, then Table Operation, SP exit before deleting a permanent table with dynamic SQL. Please note that when multiple users are connected concurrently .a sessi ON calls the SP and successfully created a table, b session also calls the SP to try to create this table. Oracle will be very savage SP interruption, and then our customers will see very unfriger error box. 2. In order to make multiple users use mutual disturbance, generate sessionID from the program to generate sessionID using the Oracle function usingV ('sessionID'). Then use dynamic SQL to generate the table name sessionID table in the SP. Operation, SP is deleted when SP is exited. However, there will be a problem: Since the SP is often called to cause a constant table deletion table. The Oracle's table is stored on the table space. Such a large number of DDL statements make the table space Internal fragmentation, the table space will continue to increase, to know that Oracle's fragmentation must be manually made, it will not be automatically organized as SQL Server. "Oracle is most satisfactory is that it can be optimized, Oracle is most People are not satisfied is that it must be optimized! "After a quarter, even for a month, our users will complain about our system. It is more slow to run. I mentioned that I have said before: our absolute Most prices have not been real test. If you don't consider it, our system will have a serious problem. For the original SQL Server Transplantation Oracle, my processing is as follows: 1. Create 1-module-createTemPTable. SQL file: The temporary table used in all SQL Server is built here, but each table is more than one field sessionID INT, if the table has a primary key, then the sessionID is added to the primary key. 2. Create 0-module-droptemptable.sql file The content inside is: Beginfor R in (SELECT 'DROP TABLE'
|| Object_name as sqls from user_objects where object_type = 'table' and object_name in ('temp1', 'temp2', ...)) LOOP - All temporary tables are written in in in in in in in in in in in in in in in in in in in in in in in in in in in in in In in Loop; end; These two SQL files let our program automatically run. 3. Generate sessionID by programming or generate sessionID fields for SessionID written by Useerenv ('sessionID'). Each session only handles the data of this session. Note : Temporary Time Temporary Temporary Table Effect of SQL Server in Oracle: CREATE GLOBAL TEMPORY TABLE TEMP1 (........... sessionid int) on commit delete rows; 4. Perform dynamic SQL a dynamic SQL statement is Running instead of compiling, when compiling, if you don't understand the structure of the query or the object you want to query, you can use dynamic SQL. But in general, system resource cost of running dynamic SQL is running The same static SQL two to three times. Because it must be gramatic analysis every time you perform dynamic SQL. Due to this factor, I hope everyone uses less dynamic SQL.SQL Server with EXEC (string) Dynamic SQL can be performed, if you need to get a result value from the executed dynamic SQL, you can use the sp_executesql stored procedure. In Oracle, you can use dbms_sql package and Execute IMMediate '...' to perform dynamic SQL, but pay attention to Execute Immediate It is the new feature that Oracle8i is launched. It cannot be used in Oracle8 and previous versions. The routines are as follows: SQL Server: Declare @count intdeclare @sql nvarchar (200) set @SQL = N'select Count (* ) from sysobjects'exec sp_executesql @ SQL, N '@ i int out: set serverputprint @countoracle: set serveroutput on / declare i_count int; v_sql varchar2 (200): = 'Begin Select Count (*) INTO: 1 from User_Objects; End;'; Beginexecute Immediate V_SQL Using Out i_count; dbms_output.put_line (i_count);
/ DBMS_SQL package is more complicated and cumbersome and explicitly uses a cursor, which does not advocate it. Note: Note: One thing to pay attention to, the length of the string that Execute IMMEDITE can perform is limited, if more over This limit, then you can only use the dbms_sql package. 5. How to transplant the Identity in SQL Server gives us a great convenience, we can use it easy to sort. But in Oracle There is no such feature. Oracle only sequence concept. Serquence is unrelated, and Sequence is not attached to the table, which exists independently, and transactions are independent. But we can use Sequence to reach SQL Server The effect of Identity. The routines are as follows: SQL Server Original table structure: Create Table FTM07 - Standling Detained (ftg00c type_v_cmpid not null, - Company Don't ftg01c varchar (20) Not null, - Bill Number FTG02f Int IDENTIITY NOT NULL, - Diji FTG03D TYPE_V_DATE NULL, - Status Process Day FTG04C TYPE_V_ENUM NULL, - Status FTG06C VARCHAR (20) NULL, - Sports No. FTG07C VARCHAR (2) NULL, - Bill distant ftg08c varchar ( 20) NULL, - DOTO CONSTRAINT PK_FTM07 PRIMARY Key (ftg00c, ftg01c, ftg02f)) Migration to Oracle: Create Table FTM07 - Detained Details (ftg00c varchar2 (3) Not Null, - Company Don't ftg01c varchar2 (20) Not null, - Bill number FTG02F INT Not Null, - Water No. / * Sequence * / FTG03D VARCHAR2 (8) NULL, - Status Processing Day ftg04c varchar2 (1) null, - Status FTG06C VARCHAR2 (20) NULL, - Sports No. FTG07C VARCHAR2 (2) NULL , - ticket changes do ftg08c varchar2 (20) null, - movement in a single ticket number constraint PK_FTM07 primary key (ftg00c, ftg01c, ftg02f)); CREATE SEQUENCE ftm07_seq INCREMENT BY 1; create or replace TRIGGER Cash_ftm07_insert_before - insert before adding a Trigger Before INSERT ON FTM07FOR EACH ROWDECLAREI_ID INTEGER; beginselect ftm07_seq.nextval I_id from dual ;: new.ftg02f: = i_id; end; can be seen from above, like SQL Server, you don't need to operate FTG02F fields The trigger will help you get everything. But there is a different need to pay attention: Sequence's value cannot be manually reset in SQL Server can pass DBCC CHECKIDENT (Name, Reseed, 0) or Truncate Table Tablename (if the data is not What needs to be reset to 1, and the Oracle's sequence does not do this, and the system will automatically reset it to a predetermined minimum .________________ zhuzhichao @ itpub. Net.SQL Server -> Oracle (2)
Support Oraclezhuzhichao 2002.4 6. About triggers for trigger SQL Server are executed after triggering statements, and is the statement level. That is to say that a statement can only lead to a trigger, regardless of how many records have been changed, due to virtual Table INSERTED and DELETED existence ensures that each row can be referenced. And Oracle's trigger is divided into four ways to perform, statement level or row combination after trigger statements. The trigger is granted to Oracle, you can create a row-level trigger after trigger statements, remember, must use a row-level trigger without using a sentence level trigger. Because Oracle's trigger mode is not like SQL Server Inserted and deleted virtual tables, but only: New and ld.:new and LD cannot be calculated as a two-dimensional table, it is just the value before the record field update, the value after the update. In Oracle, the concept of the cursor The entire PL / SQL, the cursor is widely used in Oracle. Even each Update statement and the Delete statement has a built-in implicit cursor! Therefore, we can use the row-level trigger plus ld and: New to reach SQL Server . the effect of the statement-level trigger routine plus inserted and deleted as follows: SQL SERVER: create trigger Cash_frm02_deleteon dbo.frm02for deleteasif @@ rowcount = 0returndelete frm05 from frm05 f, deleted d where f.fre00c = d.frb00c and f. fre01c = d.frb01c and f.fre02c = d.frb02cif @@ error <> 0goto error_handlerreturnerror_handler: beginrollback transactionreturnendOracle: create or replace trigger Cash_frm02_deleteafter deleteon frm02for each rowbegin delete from frm05 where fre00c =: OLD.frb00c and fre01c =: OLD.frb01c And fre02c =: Old.frb02c; Exceptionwhen Others Thenr Ollback; END CASH_FRM02_DELETE; seven. Inner Join, inner join, is also our usual equality. In SQL-92 standard, internal coupling can be specified in the FROM or WHERE clause. this Is the only supported type of SQL-92 in the WHERE clause. The outer joints in the SQL-92 standard are divided into Left Outer Join and Right Outer Join. Traditional syntax is * = and = *. SQL Server also supports SQL-92 external coupling syntax and traditional syntax to specify external linkages in a manner using * = * operator in the WHERE clause. It is recommended that you use SQL-92 syntax because it does not generate the simulation of the traditional Transact-SQL external connection sometimes generated, and the MS will not be able to support * = and = * in future versions. Please pay attention to it. All joint: SQL-92 Full Outer Join operator points out that all of the data columns in the two data sheets must be included in the results, regardless of whether there is in line in the data table.
SQL Server supports full-link, but unfortunately: Oracle does not support full link, if you want to achieve the full effect, then only using Union. Oracle's PL / SQL is not very supporting SQL-92 standard, it has Many local practices are different from the standard, and its external joints are different. Oracle's outer joint symbols are ( ), and only one table is made as the primary table and other forms. Less. If you use two or more tables in your SQL Server as the primary table, then only completely rewrite. And if I use IN or OR in the outlocker statement of SQL Server, that is converted to Oracle. Voluntary. The routines are as follows: SQL Server: Select a.comp_id, a.trans_no, b.part_no from transmaster aleft outer join transdetail b on (a.comp_id = b.comp_id and a.trans_no = b.trans_no) Oracle: Select a.comp_id, a.trans_no, b.part_no from transmaster a, transdetail b where a.comp_id = b.comp_id ( ) and a.trans_no = b.trans_no ( ); // Please pay attention ( ) This symbol It should appear next to the fields from the table ... 9. Top N and ROWNUM can use top n in SQL Server to return to the specified number of records, and the corresponding is ROWNUM in Oracle, but different: SQL Server's top n is a record returned by the logical order, and Oracle's ROWNUM specifies the number of physical order, and its physical serial number has been decided before the ORDER BY. Therefore, the following statement is in Oracle error. : Error Statement 1: Select Rownum, FOM01. * From Fom01 WHERE ROWNUM <11 ORDER BY FOA02D; // This statement will never have the correct, what you want. You can run a look ./ / Because RowNum is the serial number of the physical order, it has been decided before sorting. Corrected statements: SELECT ROWNUM, SS. * From (SELECT * FOM01 ORDER BY FOA02D) SS Where Rownum <11; but pay attention to This Sample statements can only run normally after Oracle8i, because Oracle8i can use ORDER BY in child query. Error statement 2: Select Rownum, FOM01. * From fom01 where rownum = 2; // This statement will return any value, But a record can be returned to 1 to 1. Because Oracle's Rownum can only have 2 after generation, there is 2 to have 3, which is pushed. In fact, this truth is also very simple. Have you seen a house without covering a good 1st floor, you will cover it. 2nd floor hanging in half empty? ^ _ ^ Corrected statement: SELECT * FROM (SELECT ROWNUM R, FOM01. * From fom01 where rownum <= 2) WHERE R> 1; ...... 11. Some SQL Server needs to pay attention to different from Oracle.
