I don't know where to get this document, put it online - GWB
Database end SQL syntax migration
The following is a commonly used SQL syntax migration, including data types, ID columns to sequence migration, table (primary key, foreign key, check, unique, default, fact, ", cursor, stored procedure, function, trigger, common SQL syntax and functions Aspects, considering the actual situation of SQL Server, there is no relating to Oracle's unique package, exception, etc. In the following description, the Transact-SQL of SQL Server is referred to as T-SQL. In Oracle, its grammatical set is called PL / SQL.
<1> Migration of data types
<1>, Oracle Dide Syntax Description
In Oracle, analyze its data type, which can be roughly divided into numbers, characters, datetime, and special four categories. Among them, the digital type has Number; the character type has Char and VARCHAR2; the datetime type is only Date; in addition, the data types such as Long, RAW, long RAW, BLOB, Clob, and BFILE can be considered a special data type.
<2>, SQL Server Dide Syntax Description
In SQL Server, refer to Oracle's division, data types can be roughly divided into numbers, characters, datetime, and special four categories. Digital types can be divided into exact values, approximate values, integends, binary numbers, currency, etc. The value has float [(n)]; integer has int, smallint, tinyint; binary number has binary [(n)], varbinary [(n)]; currency has Money, SmallMoney. Character type has char [(n)] and varchar [(n)]. Date Time Type has DateTime, SmallDateTime. In addition, data types such as Bit, TimeStamp, Text, and Image, Binary VARING can be considered a special data type.
<3>, from SQL Server to Oracle Migration Scheme
Comparing Oracle and SQL Server different on the data type, you can do the following adjustments when migrating from SQL Server to Oracle:
SQL Server Oracle Digital Type Decimal [(P [S])] NUMBER [(P [S])] Numeric [(p [p [s])] Number [(p [p [p [s])] float [(n) ] Number [(n)] int Number Smallint Number Tinyint Number Money Number [19,4] SmallMoney Number [19,4] Character Type Char [(N)] Char [(N)] varchar [(n)] varchar2 [ N)] Date Time Type DateTime Date SmallDatetime Date Other Text Clob Image Blob Bit Number (1)
method:
Money in the original system of the company is used for the amount of Number (14, 2); use Number (10, 4) for unit prices;
<2> ID column migration to SEQUENCE
<1>, SQL Server Syntax Description
In SQL Server, you can define a field in the database as an Identity column to make primary key identification, such as:
JLBH NUMERIC (12, 0) Identity (1, 1) / * Record Number field * /
Constraint PK_TBL_EXAMPLE PRIMARY Key Nonclustered (jlbh) / * Primary key constraint * / Here, JLBH is an ID column, when inserting records to tables with the column, the system will automatically automatically automatically automatically using the value of JLBH from 1. maintain.
<2>, Oracle Dide Syntax Description
But in Oracle, there is no such ID column definition, but use another method to create sequence.
Such as:
/ * - 1, create various area coding tables - * /
Drop Table LT_Area;
Create Table LT_Area
(
Area_id Number (5,0) Not NULL, / * Region code * /
Area_name varchar2 (20) Not NULL, / * Region Name * /
ConsTRAINT PK_LT_AREA PRIMARY Key (Area_ID)
);
/ * - 2, create sequence, will list the area_id class ID - * /
DROP SEQUENCE SEQ_LT_AREA;
Create sequence seq_lt_Area increment by 1 / * This sequence increased in 1 step size * /
Start with 1 maxValue 99999; / * starting from 1, maximum growth to 9999 * /
/ * - 3, reference sequence's next value when actual operation - * /
INSERT INTO LT_AREA (Area_ID, Area_name) Values (seq_lt_area.nextval, 'Shenzhen');
INSERT INTO LT_AREA (Area_ID, Area_name) Values (seq_lt_area.nextval, 'Guangzhou');
INSERT INTO LT_AREA (Area_ID, Area_name) Values (seq_lt_area.nextval, 'Beijing');
/ * - 4, after the newly inserted continuous record, the next statement runs, the 'Shanghai' area of Area_ID is 4 - * /
INSERT INTO LT_AREA (Area_ID, Area_Name) Values (SEQ_LT_AREA.NEXTVAL, 'Shanghai');
<3>, from SQL Server to Oracle Migration Scheme
According to the above analysis, when migrating from SQL Server to Oracle, you can do the following adjustment:
1. Remove the Identity declaration keyword for the id column in the table statement;
2. Create sequence, the column of this sequence and the column of the class ID;
3. References to the corresponding columns in the INSERT statement: SequenceName.NextVal
In fact, the method used in Oracle in Oracle is added to an insertion front trigger for a table with an automatic growth field (the specific information is asked after the "trigger" section), as follows:
Create or Replace Trigger GenaeraReAid
Before Insert On LT_Area
For Each Row
SELECT SEQ_LT_AREA.NEXTVAL INTO: New.ID
From Dual;
Begin
End genarateareAreAid;
GenaerateAreAid actually modified the pseudo record: New of the area_id value. : New most useful features ---- When the statement is actually executed, the storage content in the New will be used. So the system can automatically generate new numbers each time.
There is a SQL Server statement:
/ * ------------------------ Create an Employee Table --------------------- --- * /
IF exists (SELECT 1 from sysobjects where name = 'employee "
And type = 'u')
Drop Table Employee
Go
CREATE TABLE EMPLOYEEEE
(
EMP_ID EMPID / * EMPID is a user-defined data type * /
/ * Create a self-name primary key constraint * /
ConsTRAINT PK_EMPLOYEE PRIMARY Key Nonclustered
/ * Create a self-name Check constraint * /
ConsTRAINT CK_EMP_ID CHECK (EMP_ID LIKE
'[A-Z] [A-Z] [1-9] [0-9] [0-9] [FM]' OR
EMP_ID LIKE '[A-Z] - [A-Z] [1-9] [0-9] [0-9] [fm]'),
/ * Check constraints: Each Employee ID Consists of Three Characters That
REPRESENT The Employee's Initials, Followed by a Five
Digit Number Ranging from 10000 to 99999 and THE
Employee's Gnder (M or F). A (HYPHEN) - IS ACCEPTABLE
For the middle initial. * /
FName Varchar (20) Not Null,
Minit char (1) NULL,
Lname varchar (30) Not null,
SS_ID VARCHAR (9) Unique, / * Create a unique bound * /
Job_ID Smallint Not NULL
DEFAULT 1, / * Set the default value * /
Job_lvl Tinyint
DEFAULT 10, / * Set the default value * /
/ * Entry Job_LVL for new hires. * /
Pub_id char (4) Not null
DEFAULT ('9952') / * Set the default value * /
References publishers (PUB_ID), / * Create a system named foreign key constraint * /
/ * By Default, THE Parent Company Publisher Is The Company
TO whom Each Employee Reports. * /
HIRE_DATE DATETIME NOT NULL
Default (getDate ()), / * Set the default value * /
/ * By default, the current system date will be entered. * /
ConsTRAINT FK_EMPLOYE_JOB FOREIGN Key (JOB_ID)
References jobs (JOB_ID) / * Create a self-name foreign key constraint * /
)
Go
/ * --------------------- Create the INDEX on the Employee table ------------------- * /
IF exists (SELECT 1 from sysindexes
Where name = 'EMP_PUB_ID_IND')
DROP INDEX Employee. EMP_PUB_ID_IND
Go
CREATE INDEX EMP_PUB_ID_IND
ON Employee (Pub_ID)
Go
<2>, Oracle Dide Syntax Description
The syntax at the Oracle side is as follows:
/ * ---------------------- Create an Employee Table --------------------- * /
Drop Table Employee;
CREATE TABLE EMPLOYEEEE
(
EMP_ID VARCHAR2 (9) / * Adjustments to VARCHAR2 (9) * / by user-defined data type
/ * Create a self-name primary key constraint * /
ConsTRAINT PK_EMPLOYEE PRIMARY Key Nonclustered
/ * Create a self-name Check constraint * /
ConsTRAINT CK_EMP_ID CHECK (EMP_ID LIKE
'[A-Z] [A-Z] [1-9] [0-9] [0-9] [FM]' OR
EMP_ID LIKE '[A-Z] - [A-Z] [1-9] [0-9] [0-9] [fm]'),
/ * Check constraints: Each Employee ID Consists of Three Characters That
REPRESENT The Employee's Initials, Followed by a Five
Digit Number Ranging from 10000 to 99999 and THE
Employee's Gnder (M or F). A (HYPHEN) - IS ACCEPTABLE
For the middle initial. * /
FName Varchar2 (20) Not Null,
Minit varchar2 (1) NULL,
Lname varchar2 (30) Not null,
SS_ID VARCHAR2 (9) UNIQUE, / * Create a unique bound * /
Job_id Number (5,0) Not Null
/ * Here you consider the length of Smallint, or adjust to number * /
DEFAULT 1, / * Set the default value * /
Job_lvl Number (3,0)
/ * Here, considering the length of Tinyint, or adjust to number * /
DEFAULT 10, / * Set the default value * /
/ * Entry Job_LVL for new hires. * /
Pub_id varchar2 (4) Not null
DEFAULT ('9952') / * Set the default value * /
References publishers (PUB_ID), / * Create a system named foreign key constraint * /
/ * By Default, THE Parent Company Publisher Is The Company
TO whom Each Employee Reports. * /
HIRE_DATE DATE NOT NULL
Default sysdate, / * Set the default value * /
/ * Here, SQL Server's getDate () is adjusted to Oracle's sysdate * /
/ * By Default, The Current System Date Will Be Entered. * / ConsTRAINT FK_EMPLOYE_JOB FOREIGN Key (JOB_ID)
References jobs (JOB_ID) / * Create a self-name foreign key constraint * /
);
/ * -------------------- Create the INDEX on the Employee table ------------------ * /
Drop Index Employee. EMP_PUB_ID_IND;
CREATE INDEX EMP_PUB_ID_IND ON EMPLOYEE (PUB_ID);
<3>, from SQL Server to Oracle Migration Scheme
Comparing these two SQL code, you can see that when you create a table and its primary key, both the key, both, the SQL Server is approximately the same as the oracle syntax, but when migrates: pay attention to the following:
(1) Oracle Definition The default property of the table field is followed by the field type, as follows:
CREATE TABLE MZ_GHXX
(GHLXH NUMBER PRIMAY KEY,
Rq Date Default Sysdate Not Null,
.
)
Can't write
CREATE TABLE MZ_GHXX
(GHLXH NUMBER PRIMAY KEY,
RQ Date Not Null Default Sysdate,
.
)
(2) When the T-SQL defines the table structure, if it involves the default time and the default modification, all modifications are as follows:
Zhxgrq Date Default Sysdate Null,
Zhxgr char (8) Default 'Futian' Null,
(3) If the table has an Identity Different Section, you must first record it. After the table is completed, the corresponding sequence and table trigger will be established immediately, and as a record.
<1>, SQL Server Syntax Description
1, DECLARE CURSOR statement
grammar:
Declare cursor_name [INSENSITIVE] [Scroll] Cursor
For select_statement
[For {read only | update [of coluMn_list]}]
example:
Declare Authors_Cursor Cursor for
SELECT AU_LNAME, AU_FNAME
From authors
WHERE AU_LNAME LIKE 'B%'
Order by au_lname, au_fname
2, Open statement
grammar:
Open cursor_name
example:
Open authors_cursor
3, FETCH statement
grammar:
Fetch
[[Next | PRIOR | First | Last | Absolute N | relative n]
From cursor_name
[INTO @ variable_name1, @ variable_name2, ...]
example:
Fetch next from authors_cursor
INTO @AU_LNAME, @au_fname
4, close statement
grammar:
Close Cursor_name
example:
Close Authors_Cursor5, DEAllocate statement
grammar:
DEAALLOCATE CURSOR_NAME
example:
DEAALLOCATE AUTHORS_CURSOR
6. The standard cycle and cycle termination conditions in the cursor
(1) Fetch next from authors_cursor @au_lname, @au_fname
(2) - Check @@ fetch_status to see if the are any more rows to fetch.
While @@ fetch_status = 0
Begin
- Concatenate and Display The Current Values in The Variables.
Print "Author:" @AU_FNAME "" @au_lname
- this is executed as long as the previous fetch succeeds.
Fetch next from authors_cursor @au_lname, @au_fname
End
(3) Close Authors_Cursor
7, implicit cursor
MSSQLServer is affected by the number of rows of data manipulation speech, there is a global variable: @@ rowcount, in fact it is an implicit cursor, which records the number of rows affected by upper data manipulation, when @@ rowcount is less than 1 At the table, the relevant record did not find last time, as follows:
Update students set lastname = 'john' where student_id = '301'
IF @@ rowcount <1 THEN
Insert Into Students Values ('301', 'Stdiv', 'John', '996-03-02')
Indicates that if there is a record number "301" in the data table, modify its name "john". If the corresponding record is not found, the record of "John" is inserted into the database.
8. Example:
- Declare the variables to store the values returned by fetch.
Declare @au_lname varchar (40), @AU_FNAME VARCHAR (20)
Declare Authors_Cursor Cursor for
SELECT AU_LNAME, AU_FNAME
From authors
WHERE AU_LNAME LIKE 'B%'
Order by au_lname, au_fname
Open authors_cursor
- Perform The First Fetch and Store The VALUES IN VARIABLES.
- NOTE: The Variables Are In The Same Order As the Columns
- in the select statement.
Fetch next from authors_cursor @au_lname, @au_fname
- Check @@ fetch_status to see letter there is a any more rows to fetch.
While @@ fetch_status = 0begin
- Concatenate and Display The Current Values in The Variables.
Print "Author:" @AU_FNAME "" @au_lname
- this is executed as long as the previous fetch succeeds.
Fetch next from authors_cursor @au_lname, @au_fname
End
Close Authors_Cursor
DEAALLOCATE AUTHORS_CURSOR
<2>, Oracle Dide Syntax Description
1, DECLARE CURSOR statement
grammar:
Cursor cursor_name is select_statement;
example:
Cursor Authors_Cursor IS
SELECT AU_LNAME, AU_FNAME
From authors
WHERE AU_LNAME LIKE 'B%'
Order by au_lname, au_fname;
2, Open statement
grammar:
Open cursor_name
example:
Open authors_cursor;
3, FETCH statement
grammar:
Fetch Cursor_name Into Variable_name1 [, Variable_name2, ...];
example:
Fetch authors_cursor INTO AU_LNAME, AU_FNAME;
4, close statement
grammar:
Close Cursor_name
example:
Close authors_cursor;
5. Simple cursor extraction cycle structure and cycle termination condition judgment
1> WHILE cycles for cyclic judgment conditions with% Found
(1) Fetch authors_cursor INTO AU_LNAME, AU_FNAME;
(2) While Authors_Cursor% Found Loop
- Concatenate and Display The Current Values in The Variables.
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE ('Author:' || au_fname || '' || au_lname);
Fetch authors_cursor INTO AU_LNAME, AU_FNAME;
End loop;
(3) Close authors_cursor;
2> Simple Loop ... End loop cycle with% notfound
(1) Open authors_cursor;
(2) loop
Fetch authors_cursor INTO AU_LNAME, AU_FNAME;
- exit loop when there is no more rows to fetch.
Exit when authors_cursor% NOTFOUND;
- Concatenate and Display The Current Values in The Variables.
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE ('Author:' || au_fname || '' || AU_LNAME); end loop;
(3) Close authors_cursor;
3> Use the game style for loop, as follows:
Declare
Cursor C_HistoryStudents IS
SELECT ID, First_NAME, LAST_NAME
From students
Where major = 'history'
Begin
For v_studentdata in c_historystudents loop
INSERT INTO registered_students
(Student_ID, First_Name, Last_name, Department, Course)
VALUES (v_studentdata.id, v_studentdata.first_name, v_studentdata.last_name, 'his', 301);
End loop;
COMMIT;
END;
First, the record v_studentdata is not declared in the declaration section of the block. The type of variable is c_historystudents% ROWTYPE, the scope of v_studentdata is limited to this for loop itself; shut down.
6, implicit cursor SQL% Found and SQL% Notfound
Like MSSQL Server, Oracle also has an implicit cursor, which is used to handle INSERT, DELETE, and a single-line SELECT...into statement. Because the SQL cursor is turned on and off through the PL / SQL engine, the Open, FETCH and Close commands are independent. But the cursor attribute can be applied to the SQL cursor, as follows:
Begin
Update rooms
Set number_seats = 100
WHERE ROOM_ID = 9990;
- Insert a new record if you find a record
IF SQL% Notfound Then
INSERT INTO Rooms (room_id, number_seats)
VALUES (9990, 100)
END IF
END;
7. Example:
- Declare the variables to store the values returned by fetch.
- DECLARE The CURSOR Authors_Cursor.
Declare
AU_LNAME VARCHAR2 (40);
AU_FNAME VARCHAR2 (20);
Cursor Authors_Cursor IS
SELECT AU_LNAME, AU_FNAME
From authors
WHERE AU_LNAME LIKE 'B%'
Order by au_lname, au_fname;
Begin
Open authors_cursor;
Fetch authors_cursor INTO AU_LNAME, AU_FNAME;
While authors_cursor% Found Loop
- Concatenate and Display The Current Values in The Variables.
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE ('Author:' || au_fname || '' || au_lname);
Fetch authors_cursor INTO AU_LNAME, AU_FNAME;
End loop;
Close authors_cursor;
<3>, from SQL Server to Oracle Migration Scheme
Compare the above SQL code, do the following adjustments during the migration:
(1) T-SQL's declaration of CURSOR in the main code, and the declaration and change of Cursor in PL / SQL
Quantity declaration synchronization, you must declare before the main code (Begin Keyword), so you want to migrate
Put the cursor statement in advance, the parameters after the MSSQL Server's CURSOR definition is saved;
(2) PL / SQL does not have T-SQL in the syrust part of DEALLOCATE CURSOR in the syntax of Cuosor.
Remove this part of the statement when migration.
(3) PL / SQL and T-SQL have nothing to do with the loop and cycle termination conditions in the cursor.
According to the previous discussion and reference the two grammar sets, the two grammar sets is described in the narrative part of the analysis.
It is recommended to adjust the cursor extraction cycle in the T-SQL to the WHILE cursor extraction cycle junction in PL / SQL.
Structure, this can maintain the basic structure of the cycle, which is approximately constant, and when the cycle termination condition is determined
Be careful to adjust the judgment of @@ fetch_status global variables in T-SQL
CURSOR_NAME% FOUND statement is judged.
(4) For T-SQL, the statement end flag is not defined, and the PL / SQL is ";"; "end statement.
(5) For the original MSSQL Server type, if the value taken out of the cursor does not participate in the calculation, all use for loop mode; and other calculations for the removed value, can be used directly to define variable position definition variables .
(6) MSSQL Repeatedly defined several times in Oracle can be solved by the cursor variables in Oracle. As follows:
MSSQL Server:
Declare Cur_YPDM CURSOR FOR
SELECT * FROM YP
Open CUR_YP
Fetch cur_yp INTO @ YP, @ mc ...
While @@ fetch_status <> -1
Begin
IF @@ fetch_status <> -2
Begin
.
End
Fetch cur_yp INTO @ YP, @ mc ...
End
Close Cur_YPDM
Deallocate Cur_YPDM
.
Declare Cur_YPDM CURSOR FOR
Select * from YP Where Condition 1
Open CUR_YP
Fetch cur_yp INTO @ YP, @ mc ...
While @@ fetch_status <> -1
Begin
IF @@ fetch_status <> -2
Begin
.
End
Fetch cur_yp INTO @ YP, @ mc ...
End
Close Cur_YPDM
Deallocate Cur_YPDM
.
Declare Cur_YPDM CURSOR FOR
Select * from YP Where Condition 2
Open CUR_YP
Fetch cur_yp INTO @ YP, @ mc ...
While @@ fetch_status <> -1
Begin
IF @@ fetch_status <> -2
Begin
.
End
FETCH CUR_YP INTO @ YP, @ mc ... End
Close Cur_YPDM
Deallocate Cur_YPDM
.
In the program, the same cursor CUR_YP three times
During the migration, it is best to define a ribbon variable first, open it with open in the program, as follows:
Declare
TYPE CUR_TYPE IS REF CUR_TYPE;
CUR_YP CUR_TYPE;
...
Begin
Open cur_yp for select * from yp;
loop
Fetch Cur_YP INTO YP, MC ...
EXIT WHEN CUR_YP% NOTFOUND;
.
End loop;
Close Cur_YP;
Open cur_yp for select * from yp where condition1;
loop
Fetch Cur_YP INTO YP, MC ...
EXIT WHEN CUR_YP% NOTFOUND;
.
End loop;
Close Cur_YP;
Open cur_yp for select * from yp where condition2;
loop
Fetch Cur_YP INTO YP, MC ...
EXIT WHEN CUR_YP% NOTFOUND;
.
End loop;
Close Cur_YP;
END;
(7) Please note that in the cursor cycle must exit the name, or the dead cycle will occur when executed.
<5> Store Process / Function
<1>, SQL Server Syntax Description
1, syntax:
Create Proc [edure] [owner.] Procedure_name [; Number]
(Parameter1 [, Parameter2] ... [, parameter255]]])]
[{For replication} | {with recompile}
[{[With] | [,]} encryption]]]
AS
SQL_STATEMENT [... N]
Among them, parameter = @Parameter_name Datatype [= default] [OUTPUT]
Explanation: The structure of the storage process in T-SQL is roughly as follows
CREATE Procedure Procedure_name
/ * Nurse of the input and output parameters * /
AS
Declare
/ * Declaration of local variables * /
Begin
/ * Sub-body SQL statement part * /
/ * Cursor statement, use statements in this section * /
End
2. Example:
IF EXISTS (SELECT 1 from sysoads
Where name = 'titles_sum' and type = 'p')
Drop Procedure Titles_sum
Go
Create Procedure Titles_sum
@Title varchar (40) = '%', @Sum Money Output
AS
Begin
SELECT 'TITLE NAME' = Title
From titles
Where title Like @title
SELECT @SUM = SUM (price)
From titles
Where title Like @title
End
<2>, Oracle-end Procedure Syntax Description
1, syntax:
Create [or replace] Procedure Procedure_name
(Parameter1 [{IN | OUT | in out}] Type, ...
Parametern [{IN | OUT | in out}] type)]]
{IS | AS}
[Begin]
SQL_STATEMENT [... N];
[End];
Explanation: The structure of the storage process in PL / SQL is roughly as follows
Create or Replace Procedure Procedure_name
(/ * Input, output parameter declaration part * /)
AS
/ * Declaration part of local variables, cursors, etc. * /
Begin
/ * Sub-body SQL statement part * /
/ * Cursor usage statement in this section * /
EXCEPTION
/ * Abnormal processing section * /
END;
2. Example:
Create or Replace Procedure Drop_class
(arg_student_id in varchar2,
Arg_class_id in varchar2,
Status Out Number
AS
Counter Number;
Begin
STATUS: = 0;
- Verify That this class really is part of the student's schedule.
SELECT Count (*) Into Counter
From student_schedule
Where student_id = arg_student_id
And class_id = arg_class_id;
If counter = 1 THEN
Delete from student_schedule
Where student_id = arg_student_id
And class_id = arg_class_id;
STATUS: = -1;
END IF;
END;
<3> Oracle side Function syntax
(1) Syntax
Create [or replace] function function_name
[(Argument [{IN | OUT | in out}]) TYPE,
...
(argument [{in | out | in out}]) TYPE
Return Return_Type {IS | AS}
Begin
...
END;
Keyword return specifies the data type of the function return value. It can be any legal PL / SQL data type. Each function must have a RETURN clause because a function must return a value to the calling environment on the defined function.
(2) Example
Create Or Replace Function BLANACE_CHECK (Person_name in varchar2)
Return Number
IS
Balance Number (10, 2);
Begin
SELECT SUM (Decode (Acton, 'Bought', Amount, 0))
INTO Balance
From Ledger
WHERE PERSON = Person_name;
Return (Balance);
END;
(3) The difference between the process and function
The function can return a value to the calling environment; and the process is not, the process can only pass the data by returning parameters ("OUT" or "in out").
<4> Migration Scheme from SQL Server to Oracle
By comparing the differences in the above SQL syntax, you must pay attention to the following points when migration:
1. For the MSSQL stored procedure with returning single value, it is best to convert to ORALCE in the database shift value; for the process of mass data for MSSQL, there is no need to return the value of the stored procedure to Oracle 2, in T- In SQL, input, output parameter definition part is between "Create ..." and "AS", before and after
There is no parentheses; and there must be "(" and ")" in PL / SQL.
3, in T-SQL, when the local variable is declared, there must be a Declare keyword in front;
And do not have a Declare keyword in PL / SQL.
4. In T-SQL, the first character of the parameter name must be "@" and meet the identifier;
In PL / SQL, there is no special instructions outside the parameter name, which is in accordance with the identifier, T-SQL, for the parameter, the data type and its length and precision; but in PL / SQL, in addition to the reference% Type and% RowType In addition, the length and accuracy cannot be given when defining the parameter data type, as follows:
Create or Replace Procedure Proc_SELE_YS
(YSDM CHAR (6), GZ Number (14, 4))
AS
Begin
...
END;
It is wrong, it should be defined below
Create or Replace Procedure Proc_SELE_YS
(YSDM Char, GZ Number)
AS
Begin
...
END;
or
Create or Replace Procedure Proc_SELE_YS
(YSDM YSDMB.YSDM% TYPE, GZ YSDMB.GZ% TYPE)
AS
Begin
...
END;
5. For T-SQL, the cursor statement is synchronized with the use of statements in the main SQL statement;
In PL / SQL, the cursor statement is synchronized with the local variable before the main SQL statement.
6. For T-SQL, the local variable is assigned (initial value or) with the following statement in the main SQL statement (initial value or
Field values or expressions of database tables):
"SELECT local variable name = assigned (field value or expression of the initial value or database table);
In PL / SQL, when the initial value is assured to the local variable, the following statement is used:
"Local variable name: = assigned (initial value or expression);",
When the search, the search value is assigned to the local variable, the following statement is used:
"SELECT database table field value INTO local variable name ...".
7. In PL / SQL, you can use% TYPE to define the data type of local variables. described as follows:
For example, the first_name column of the Students table has type varchar2 (20), based on this point.
We can declare a variable according to the following manner:
V_firstname varchar2 (20);
But if the data type of the first_name column must be modified, the declaration statement must be modified, so you can
Variable data type declaration with% TYPE:
V_firstname students.first_name% type;
Thus, this variable will automatically determine its corresponding data type by the system when compiling.
8. For T-SQL, the statement end flag is not defined, and the PL / SQL uses "end
9, the memory process should be Note: The format in the MSSQLServer is "exec procedure_name {arg1, arg2, ...}, but directly reference the process name in Oracle, if the stored procedure DEFALTNO, the parameters are" 9 " Then, execute the DEFAULT ("9"). 10, the stored procedure of the Oracle database does not support the use of the SELECT clause to return to a data set, and there are two options to pass the generation of a recordset:
Solution 1: Using packet and cursor variables
The first step, create a package to define a cursor variable
Create package p_nameistype cursoor_name is ref curd;
Step 2, create a process, but the base returns the cursor type Create Procedure procedure_name (s in out p_name.cursor_name) ISBEGINOPEN S for SELECT * from Table_Name ...;
In this way, by the stored procedure, you can return a data set, but in this case, only the cursor parameters of the return result can be with the keyword "OUT" in the parameter of the process, and the system will not take "out", otherwise, the system will There is a shortcomings.
Solution 2: Through the intermediate table, build a middle table, and the list of the table is plus a sequence field. Process processing To insert data into the intermediate table, passe
Select Userenv ('sessionid') from DUAL The required data can be retrieved by the serial number parameters.
<1>, SQL Server Syntax Description
1, syntax:
Create Trigger [ooner.] Trigger_name
On [owner.] TABLE_NAME
For {INSERT, UPDATE, DELETE}
[With encryption]
AS
SQL_STATEMENT [... N]
Or use the IF Update clause:
Create Trigger [ooner.] Trigger_name
On [owner.] TABLE_NAME
For {INSERT, UPDATE}
[With encryption]
AS
IF update (column_name)
[{And | OR} Update (colorn_name) ...]
SQL_STATEMENT [... N]
2. Example:
IF EXISTS (SELECT 1 from sysoads
Where name = 'reminder' and type = 'tr')
Drop Trigger Reminder
Go
Create Trigger Employee_insUpd
On Employee
For Insert, Update
AS
/ * Get The Range of level for this Job Type from the jobs table. * /
Declare @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@Job_ID Smallint
SELECT @min_lvl = min_lvl,
@MAX_LVL = Max_LVL,
@EMP_LVL = I.Job_LVL,
@JOB_ID = I.Job_idfrom Employee E, Jobs J, Inserted I
Where e.emp_id = i.emp_id and i.job = j.job_id
IF (@JOB_ID = 1) and (@emp_lvl <> 10)
Begin
Raiserror ('Job ID 1 Expects The Default Level of 10.', 16, 1)
Rollback Transaction
End
Else
IF not (@EMP_LVL BETWEEN @min_lvl and @max_lvl)
Begin
Raiserror ('the level for job_id:% D shop becom_id% D and% d.',
16, 1, @JOB_ID, @MIN_LVL, @MAX_LVL)
Rollback Transaction
End
Go
<2>, Oracle Dide Syntax Description
1, syntax:
Create [or replace] Trigger Trigger_name
{Before | After} Triggering_event on table_name
[For Each Row]
[Hen Trigger_Condition]
Trigger_body;
2, instructions and examples:
(1), in the above method, Trigger_Event is three statements corresponding to DML INSERT, UPDATE,
Delete; table_name is a table name related to the trigger; for Each Row is optional
The clause, when used, the corresponding row will cause trigger trigger; condition is optional
Oracle Boolean conditions, when the condition is true, trigger trigger; Trigger_Body is trigger
The PL / SQL block that is executed when triggered.
(2) The Oracle trigger has the following two categories:
1> Statement-Level trigger, not in the Create Trigger statement
Contains for Each ROW clause. Statement level triggers can only trigger once for triggering events,
And you cannot access the column values of each row affected by the trigger. Generally use the sentence level trigger processing
Information about the SQL statement triggered by the trigger -, for example, who is executed and what?
Execution.
2> Row-Level trigger, in the Create Trigger statement
Contains for Each ROW clause. Row-level triggers can touch each line affected by the trigger
Send, and can access the original column value and the new column value processed by the SQL statement. Row-level trigger
A typical application is to execute a transaction rule when the column value needs to be known.
(3) In the trigger body, the row-level trigger can reference the column value of the row that exists when the trigger is triggered.
The value relying on the SQL statement that causes the trigger trigger.
1> For the INSERT statement, the value to be inserted is included in new.column_name, here
Column_name is a column in the table.
2> For the UPDATE statement, the original value of the column is included in Old.Column_name, the new data column
The value is in new.column_name.
3> For the DELETE statement, the column value of the row to be deleted is placed in Old.Column_Name.
Trigger statement: OLD: New INSERT Non-defined - All fields are null When the statement is completed, the value update to be inserted in the update of the original value of the previous line is updated when the statement is completed. DELETE is deleted. The original value of the previous line is undefined - all fields are null4> In the triggers main body, ":" in front of NEW and OLD "is required. In the trigger
In the WHEN clause,: New and: OLD record can also be inside the WHEN clause
Quote, but do not need to use a colon. For example, the main body of the checkcredits trigger below is only
When the current score of the student exceeds 20, it will be executed:
Create Or Replace Trigger Checkcredits
Before Insert or Update of current_credits on students
For Each Row
When (new.current_credits> 20)
Begin
/ * Trigger Body Goes here. * /
END;
But Checkcredits can also be rewritten as follows:
Create Or Replace Trigger Checkcredits
Before Insert or Update of current_credits on students
For Each Row
Begin
IF: new.current_credits> 20 THEN
/ * Trigger Body Goes here. * /
END IF;
END;
Note that when the WHEN clause can only be used for a row-level trigger, if it is used, the trigger main body
Treatment only for rows that meet the conditions specified by the WHEN clause.
(4) The main body of the trigger is a PL / SQL block, all of the statements that can be used in the PL / SQL block touch
There are legal in the main body of the hairman, but it is subject to the following limit:
1> The trigger cannot use transaction control statements, including commit, rollback or
Savepoint. Oracle maintains this restriction reason: if the trigger encounters an error,
All database transforms caused by triggers can be rolled back; but if
Trigger confirmation (commit) is part transformed for the database, Oracle can't be completely
Roll back the entire transaction.
2> Transaction Control Words cannot be used in the implementation statement of the stored procedures called by the trigger body.
sentence.
3> The trigger body cannot declare any long or long RAW variable. And,: New and: OLD
You cannot point to the long or long RAW column in the table of the trigger.
4> When declaring foreign key constraints in the table of the trigger, if the table and needs of the trigger are defined as
DELETE CASCADE Reference Integrity Restriction The result is updated to the change table,
The table associated with the foreign key is called a restriction table, the SQL statement in this trigger body is not allowed
Read or modify any variation table of the trigger statement, nor does it allow for reading or modify the Limit
Key, unique value column or foreign key columns.
(5) The following is an example of establishing an insertion trigger:
Create Or Replace Trigger Credit_Charge_log_ins_before
Before Insert On Credit_CHARGE_LOG
For Each Row
Declare
Total_for_past_3days number;
Begin
- Check The Credit Charges for the Past 3 Days .-- i t Total More $ 1000.00, Log this entry
- int the credit_charge_attempt_log for further handling.
SELECT SUM (AMOUNT) INTO TOTAL_FOR_PAST_3DAYS
From credit_charge_log
WHERE CARD_NUMBER =: new.card_number
AND transaction_date> = sysdate - 3;
If Total_for_past_3days> 1000.00 THEN
INSERT INTO CREDIT_CHARGE_ATTEMP_LOG
(Card_Number, Amount, Vendor_ID, Transaction_Date)
Values
(: new.card_number,: new.amount,
: new.vendor_id,: new.transaction_date;
END IF;
END;
<3>, from SQL Server to Oracle Migration Scheme
1. By comparing the different SQL syntax, consider the actual programming style of the existing SQL Server, from
When the T-SQL migrates to the PL / SQL, follow the rules:
1> Adopt after the Create Trigger definition, that is, adjusted to the event trigger.
2> In the Create Trigger definition, the for Each ROW keyword is used, that is, adjust to a row trigger
.
3> Adjust "insert" in the trigger body to ": New", adjust "deleted" to ": OLD".
4> Disable Cursor operation in the trigger body: New and: OLD.
5> Disable commit, rollback, savepoint and other transaction control statements in the trigger body.
2. Solve the ID column to Sequence Migration with triggers:
The generatestudentID trigger below is used: New. This is a Before INSERT touch
The initiator is to fill in the numerical value generated by the Student_Sequence sequence.
The ID field of the Students table.
example:
Create or Replace Trigger GenerateStudentID
Before Insert On Students
For Each Row
Begin
Select student_sequence.nextval
INTO: New.ID
From Dual;
END;
In the above trigger body, GenerateStudentId actually modified: New.id's value. This
Yes: New most useful features - When the statement is actually executed, the storage content in New is
Will be used. With this trigger, we can use the following INSERT statement, not
Will generate errors:
INSERT INTO Students (first_name, last_name)
VALUES ('Luo', 'Tao');
Although we don't have the main key column ID (this is required) specified value, the trigger will provide the required needs.
Take the value. In fact, if we specify a value for the id, it will also be ignored because touch
The hairman modified it. If we use the following statement:
INSERT INTO Students (ID, First_Name, Last_name)
VALUES (-789, 'luo', 'tao');
Its processing results are still the same. In any case, Student_sequence.nextVal will be used as the ID column value.
From this discussion, this method can be used to handle the ID column of the ID of SQL Server to Oracle's sequence.
The problem of conversion.
In addition, due to the above reasons, we cannot modify in the AFTER row trigger: New because
The statement has been processed. Typically,: New is only modified in the Before row trigger, and: OLD
Never be modified, only you can read data from it.
In addition,: New and: OLD records are only valid inside the row-level trigger. If you try to want to be from the statement
The level trigger will be referenced to get a compilation error. Because the statement level trigger only executes once
- Although the statement should handle a lot of rows - so: new and: OLD is meaningless, because
Which line will it be referenced?
<7> Common SQL syntax and functions
<1>, SQL Server end common syntax description
1, use local variables:
1> Variable definition:
Declare @variable_name datatype [, ...]
example:
Declare
@name varchar (30),
@Type Int
2> Assignment to variables:
method one:
example:
Declare @int_var int
SELECT @Int_var = 12
Method Two:
example:
Declare
@Single_Auth Varchar (40),
@Curdate DateTime
SELECT @Single_Auth = au_lname,
@curdate = getdate ()
From authors
WHERE AU_ID = '123-45-6789'
2, use the T-SQL standard control structure:
1> Definition sentence block
grammar:
Begin
Statements
End
2> IF ... ELSE statement
grammar:
IF Boolean_Expression
{Statement | Statement_Block}
Else
{Statement | Statement_Block}
Example:
IF (SELECT AVG (Price) from titles where type = 'business')> $ 19.95
Print 'The average price is Greater THEN $ 19.95'
Else
Print 'The average price is less $ 19.95'
3> if exissx statement
grammar:
IF [NOT] EXISTS (SELECT_STATEMENT)
{Statement | Statement_Block}
[ELSE
{Statement | Statement_Block}]
Example:
Declare
@lname varchar (40),
@msg varchar (255)
SELECT @LName = 'Smith'
IF exists (Select * from titles where au_lname = @lname)
Begin
SELECT @msg = 'there is authors named' @lname
Print @MSG
end
Else
Begin
SELECT @msg = 'there is all no authors named' @lname
Print @MSG
end
4> Circular statement:
While
grammar:
While boolean_condition
[{statement | statement_block}]
[Break]
[Condition]
Example:
Declare
@AVG_PRICE MONEY,
@max_price money,
@Count_Rows Int,
@Times_thru_THE_LOOP INT
SELECT @avg_price = avg (price),
@MAX_PRICE = Max (Price),
@count_rows = count (*),
@Times_thru_THE_LOOP = 0
From titles
While @AVG_PRICE <$ 25 and (@count_rows <10 or @max_price <$ 50)
Begin
SELECT @AVG_PRICE = AVG (Price) * 1.05,
@MAX_PRICE = Max (Price) * 1.05,
@Time_thru_THE_LOOP = @time_thru_THE_LOOP 1
end
IF @Time_thru_THE_LOOP = 0
SELECT @time_thru_the_loop = 1
Update Titles
Set price = price * Power (1.05, @time_thru_the_loop)
4> GOTO statement
grammar:
Goto label
...
Label:
Example:
Begin Transaction
INSERT TINY (C1) Values (1)
IF @@ error! = 0 goto error_handler
Commit transaction
Return
Error_Handler:
Rollback Transaction
Return
5> RETURN statement
grammar:
Return
(1) Used for unconditional exit a batch, stored procedures, or trigger.
Example:
IF not exists (SELECT 1 from Inventory
Where it item_num = @item_num)
Begin
RaiseError 51345 'Not Found'
Return
end
Print 'No Error Found'
Return
(2) The status value is returned during the stored procedure.
Example:
CREATE Procedure Titles_for_a_pub
(@PUB_NAME VARCHAR (40) = NULL)
AS
IF @PUB_NAME IS NULL
Return 15
IF not exists (SELECT 1 from Publishers
Where pub_name = @Pub_name)
Return -101
SELECT T.TILE FROM PUBLISHERS P, TITLES Twhere P.PUB_ID = T.PUB_ID
And pub_name = @PUB_NAME
Return 0
3, the cursor extraction cycle statement in T-SQL:
(1) Fetch [Next from] CURSOR_NAME INTO @ Variable_1, ... @ variable_n
(2) While @@ fetch_status = 0
Begin
Other_Statements
Fetch [Next from] CURSOR_NAME INTO @ Variable_1, ... @ variable_n
End
(3) Close Cursor_name
4, transaction processing statements in T-SQL:
1> Start a transaction:
Begin TRAN [SACTION [Transaction_name]]]]
2> Submit a transaction:
Commit TRAN [Transaction_name]]]]]
3> Roll up a transaction:
Rollback TRAN [SACTION]]]
4> Using transaction save points:
Begin TRAN [SACTION [Transaction_name]]]]
Save TRAN [SACTION] SavePoint_name
Rollback TRAN [SACTION] savepoint_name
Commit TRAN [Transaction_name]]]]]
5, T-SQL can be used for erroneous judgment or other processing global variables:
1> @@ rowcount: The number of rows that the previous command processing
2> @@ error: the error number reported before a SQL statement report
3> @@ TRANCOUNT: Level of transaction nested
4> @@TranState: The current state of the transaction
5> @@Tranchained: The mode of the current transaction (chained) or non-linked)
6> @@ servername: Name of local SQL Server
7> @@ Version: SQL Server and O / S version level
8> @@ SPID: ID of the current process
9> @@ identity: Identity value used in the last INSERT operation
10> @@nestlevel: Nested layer in the stored procedure / trigger
11> @@ fetch_status: Status of the last FETCH statement in the cursor
6, use standard built-in error message send functions:
Function Description:
Raiserror ({msg_id | msg_str}, severity, state
[, argument1 [, argument2] [, ...])
[With log]
Among them, MSG_ID represents the error number, the user-defined error message error number is 50001 to 2147483647
Room, specific messages cause an error 5000. Msg_str is an error message body, up to 255 characters. Severity describes the severity level defined with this message, and the value includes 0 and 10
Any integer between 25. State describes the "call status" of the error, which is between 1 and 127
Numerical value. Argument Defines the messages used to replace the variables defined in the msg_str or correspond to the Message with MSG_ID
parameter. With log indicates that you want to log errors in the server error log and event log.
example 1:
Raiseerror ('Invalid Customer ID in Order.', 16, 1)
Returns:
MSG 50000, Level 16, State 1
INVALID Customer ID in ORDER.
Example 2:
SP_ADDMESSAGE 52000, 16, 'Invalid Customer ID% S in ORDER'
Raiseerror (52000, 16, 1, 'ID52436')
Returns:
MSG 52000, Level 16, State 1
INVALID Customer ID ID52436 in Order.
<2>, Oracle end common grammatical description
1, use local variables:
1> Define variables:
Variable_name data type [: = initial value];
Example: Defining variables
v_num number;
v_string varchar2 (50);
Example: Define variables and assign initial values
v_num number: = 1;
v_string varchar2 (50): = 'Hello World!';
2> Assignment to variables:
method one:
example:
v_num: = 1;
v_string: = 'Hello World!';
Method Two:
example:
SELECT First_Name Into v_string
From students
WHERE ID = v_num;
2, use the PL / SQL standard control structure:
1> Definition sentence block
grammar:
Begin
STATEMENTS;
END;
2> if ... Then ... ELSE statement
grammar:
If Boolean_Expression Then
{Statement | statement_block};
[Elsif Boolean_expression Then / * Note the Writing here - ELSIF * /
{Statement | Statement_block} ;;]
...
[ELSE
{Statement | Statement_block} ;;]
END IF;
Example:
v_numberseats rooms.number_seats% type;
v_comment varchar2 (35);
Begin
/ * Retrieve the number of seats in the room identified by ID 99999.
Store the result in v_numberseats. * /
Select Number_Seats
INTO V_NUMBERSEATS
From rooms
WHERE ROOM_ID = 99999;
IF v_numberseats <50 kil
v_comment: = 'Fairly Small'; Elsif v_numberseats <100 THEN
v_comment: = 'a little bigger';
Else
v_comment: = 'LOTS of room';
END IF;
END;
3> Circular statement:
(1) Simple loop statement:
grammar:
Loop
{Statement | statement_block};
[EXIT [WHEN CONDition];]
End loop;
Among them, statement EXIT [WHEN CONDition]; equivalent
IF condition the
EXIT;
END IF;
Example 1
v_counter binary_integer: = 1;
Begin
Loop
- INSERT A ROW INTO TEMP_TABLE with The Current Value of The Current Value of To
- loop counter.
INSERT INTO TEMP_TABLE
Values (v_counter, 'loop index');
v_counter: = v_counter 1;
- EXIT CONDITION - WHEN The loop counter> 50 We Will
- Break out of the loop.
IF v_counter> 50 THEN
EXIT;
END IF;
End loop;
END;
Example 2:
v_counter binary_integer: = 1;
Begin
Loop
- INSERT A ROW INTO TEMP_TABLE with The Current Value of The Current Value of To
- loop counter.
INSERT INTO TEMP_TABLE
Values (v_counter, 'loop index');
v_counter: = v_counter 1;
- EXIT CONDITION - WHEN The loop counter> 50 We Will
- Break out of the loop.
EXIT WHEN V_COUNTER> 50;
End loop;
END;
(2) While loop statement:
grammar:
While Condition Loop
{Statement | statement_block};
End loop;
Example 1
v_counter binary_integer: = 1;
Begin
- Test The loop counter before own loop ity t
- Insure That It is still less Than 50.
While v_counter <= 50 loop
INSERT INTO TEMP_TABLE
Values (v_counter, 'loop index');
v_counter: = v_counter 1;
End loop;
END;
Example 2:
v_counter binary_integer;
Begin
- this Condition Will Evaluate To Null, Since V_Counter - Is Initialized to Null by Default.
While v_counter <= 50 loop
INSERT INTO TEMP_TABLE
Values (v_counter, 'loop index');
v_counter: = v_counter 1;
End loop;
END;
(3) Digital For loop statement:
grammar:
For loop_counter in [reverse] low_bound..high_bound loop
{Statement | statement_block};
End loop;
Here, loop_counter is an index variable that implicitly declared.
Example 1
The loop index of the for loop is implicitly declared as binary_integer. No in front of the cycle
It is necessary to declare it, if it is declared, the loop index will block the outer layer statement.
As follows
v_counter number: = 7;
Begin
- Inserts The Value 7 INTO TEMP_TABLE.
INSERT INTO TEMP_TABLE (NUM_COL)
VALUES (V_COUNTER);
- this loop redeclares v_counter as a binary_integer, Which
- HIDES The NUMBER DECLATION OF V_COUNTER.
For v_counter in 20..30 loop
- Inside the loop, V_counter Ranges from 20 to 30.
INSERT INTO TEMP_TABLE (NUM_COL)
VALUES (V_COUNTER);
End loop;
- INSERTS ANOTHER 7 INTO TEMP_TABLE.
INSERT INTO TEMP_TABLE (NUM_COL)
VALUES (V_COUNTER);
END;
Example 2:
If there is a REVERSE keyword in the for loop, the loop index will be from the maximum
Small values are loop. Please pay attention to the syntax is the same - still writing the minimum,
As follows
Begin
For v_counter in reverse 10..50 loop
- v_counter will start with 50, and will be decremented
- by 1 Each Time Through the loop.
NULL;
End loop;
END;
Example 3:
The maximum and minimum value in the for loop is not necessary to be digital text, they can
Is any expression that can be converted to a digital value, as shown below
v_lowvalue number: = 10;
v_highvalue number: = 40;
Begin
For v_counter in reverse v_lowvalue..v_highvalue loop
INSER INTO TEMP_TABLE
VALUES (V_Counter, 'Dynamically SQECified loop Range');
End loop;
END;
4> GOTO statement
grammar:
Goto label;
...
<< label >>
...
Example:
v_counter binary_integer: = 1; Begin
Loop
INSERT INTO TEMP_TABLE
Values (v_counter, 'loop count');
v_counter: = v_counter 1;
IF v_counter> 50 THEN
Goto L_ENDOFLOOP;
END IF;
End loop;
<< l_ndofloop >>
INSERT INTO TEMP_TABLE (Char_col)
VALUES ('DONE!');
END;
5> EXIT statement
grammar:
EXIT;
See the circulating representation of the PL / SQL standard control structure above.
3, the cursor extraction cycle statement in PL / SQL:
1> Simple loop
This cycle uses a simple cycle syntax (LOOP..end loop) as follows
- Declare Variables To Hold Information About The Students
. - Majoring in history.
v_studentid students.id% type;
v_firstname students.first_name% type;
v_lastname students.last_name% type;
- Cursor to Retrieve The Information About History Students
Cursor C_HistoryStudents IS
SELECT ID, First_NAME, LAST_NAME
From students
WHERE MAJOR = 'History';
Begin
- Open the cursor and initialize the Active Set
Open c_historystudents;
Loop
- Retrieve Information for the Next Student
Fetch c_historystudents INTO V_STUDENTID, V_Firstname, v_lastname
- EXIT loop When there is are no more rows to fetch
EXIT WHEN C_HISTORYSTUDENTS% NOTFOUND;
- Process the fetched rows. In this case signal Up Each
- Student for History 301 by Inserting Them Into The
- Registered_students Table. Record The First and Last
- Names in temp_table as well.
INSERT INTO registered_students (student_id, divartment, course)
VALUES (V_StudentID, 'His', 301);
INSERT INTO TEMP_TABLE (NUM_COL, CHAR_COL)
VALUES (v_studentid, v_firstname || '' || v_lastname);
End loop;
- Free Resources Used by The Cursor
Close C_HistoryStudents;
- Commit Our Work
COMMIT;
END;
Note that the location of the Exit When statement is followed by behind the FETCH statement. In the final one
After the row, c_historystudents% notfound changes to TRUE, which exits. The location of the Exit WHEN statement is also in front of the data processing section, which is to ensure that the cycle process does not process
Where is the row.
2> While cycle
This cycle uses while .. loop cycle syntax, as shown below
- Declare Cursor to Retrieve The Information About History Students
Cursor C_HistoryStudents IS
SELECT ID, First_NAME, LAST_NAME
From students
WHERE MAJOR = 'History';
- Declare a replaord to hold the fetched information.
v_studentdata c_historystudents% rowtype;
Begin
- Open the cursor and initialize the Active Set
Open c_historystudents;
- Retrieve The First Row, To Set Up for the While Loop
Fetch c_historystudents INTO V_STUDENTDATA;
- Continue Looping While There more rows to fetch
While c_historystudents% Found Loop
- Process The Fetched Rows, In this case signal Up Each
- Student for History 301 by Inserting Them Into The
- Registered_students Table. Record The First and Last
- Names in temp_table as well.
INSERT INTO registered_students (student_id, divartment, course)
VALUES (V_StudentData.ID, 'His', 301);
INSERT INTO TEMP_TABLE (NUM_COL, CHAR_COL)
VALUES (v_studentdata.id,
v_studentdata.first_name || ''
|| v_studentdata.last_name);
- Retrieve the next row. The% Found Condition Will Be Checked
- Before the loopundues again.
Fetch c_historystudents INTO V_STUDENTDATA;
End loop;
- Free Resources Used by The Cursor
Close C_HistoryStudents;
- Commit Our Work
COMMIT;
END;
Please note that the FETCH statement has two times - once in front of the loop, the other is at the loop
Behind the reason, this is to make the circulatory conditions (c_historystudents% FOUND) each time
The surrounds are evaluated to ensure that the cycle process does not deal with any duplication.
3> Teller FOR cycle
This is slightly due to a small relationship with the migration.
4, transaction processing statement in PL / SQL:
In PL / SQL, the start position of the transaction is the first SQL statement executed after the end of the previous transaction,
Alternatively, the first SQL statement executed after the database is connected. The end of the transaction is to use commits
Or the Rollback statement identifies. 1> Commit syntax is:
Commit [work];
An optional keyword work is used to improve readability.
2> The syntax of Rollback is:
Rollback [work];
An optional keyword work is used to improve readability.
3> ROLLBACK statement will undo the entire transaction, if you use the savepoint command, only part of things
The need to be revoked, and its syntax is:
SavePoint name;
Here Name is the name of the save point.
4> Example:
v_numiterates number;
Begin
- Loop from 1 to 500, Inserting these Values Into temp_table.
- Commit every 50 rows.
For v_loopcounter in 1..500 loop
INSERT INTO TEMP_TABLE (NUM_COL) VALUES (V_LoopCounter);
v_numiterates: = v_numiterates 1;
IF v_numiterates = 50 THEN
COMMIT;
v_numiterates: = 0;
END IF;
End loop;
END;
5. Use standard built-in error messaging functions:
There is a built-in function in the PL / SQL in PL / SQL in PL / SQL in PL / SQL.
Function Description:
RAISE_APPLICATION_ERROR (Error_Number, Error_Message, [Keep_ERRORS]);
Here, Error_Number is a parameter between -20,000 to -20, 999; error_message is here
Errors related text, error_message must not be more than 512 bytes; keep_errors are one
An optional Boolean value parameter, which is True, new error will be added to the incorrect list (eg
If there is anything), it is false (this is the default setting), the new error will replace the error's current list.
example:
RAISE_APPLICATION_ERROR (-20000, 'can't find any record.');
<3>, T-SQL and PL / SQL Comparative Function Comparison (The following Express Expression Abbreviation)
T-SQL
PL / SQL
Character class function
Ascii (char_exp) Ascii (str_exp) Char (int_exp) Chr (int_exp) Datalength (char_exp) Length (str_exp) Substring (exp, start, length) Substr (exp, start, length) Upper (char_exp) Upper (str_exp) Lower ( char_exp) Lower (str_exp) Stuff (char_exp1, start, length, Char_exp2) Translate (str_exp, from_str, to_str) Ltrim (char_exp) Ltrim (str_exp1 [, str_exp2]) Rtrim (char_exp) Rtrim (str_exp1 [, str_exp2]) date class function
Getdate () sysdate mathematics class function
Abs (numeric_exp) Abs (number_exp) Ceiling (numeric_exp) Ceil (number_exp) Exp (float_exp) Exp (number_exp) Floor (numeric_exp) Floor (number_exp) Power (numeric_exp, int_exp) Power (number_exp1, number_exp2) Round (numeric_exp, int_exp) Round (number_exp1 [, number_exp2]) Sign (int_exp) Sign (number_exp) Sqrt (float_exp) Sqrt (number_exp) conversion function convert (datatype [(length)], exp, format) To_char (datatype, str_format) convert (datatype [( Length)], exp, format) s to_date (STR_EXP, DATE_FORMAT) Convert (DataType [(Length)], Exp, Format) TO_NUMBER (STR_EXP, NUM_FORMAT) Other functions
AVG ([All | Distinct] COL) AVG ([All | Distinct] COL) Count ({[All | Distinct] col] | *}) count ({[all | distinct] col} |) Max ([all | DISTINCT] COL) MAX ([All | Distinct] COL) min ([All | Distinct] Col) SUM ([All | Distinct] COL) SUM ([All | Distinct] COL) STDEV (COL) STDDEV (COL) VAR (COL) Variance (COL) ISNULL (Check_exp, Replace_Value) NVL (Check_exp, Replace_Value) Case Decode
<4> MSSQL and Oracle pay attention to several syntax conversion
(1) ISNULL and NVL
In MSSQL, in order to replace null values commonly used by ISNULL functions, such as ISNULL (@DNO, "00") indicates that when the value of the variable @DNO is empty, use "00" to replace its value; in Oracle, the same function is available NVL Implementation, as described above can be replaced with NVL (DNO, "00").
(2) Case and Deccode
Case can be used in MSSQL to process multiple judgment branches to a certain value, simplifying the code as follows:
Update Student Set Class = (Case Inyear When "1993" THEN "8" WHEN "1994" THEN "7" WHEN "1995 Then" 6 "Else" 0 ")
The same features can be implemented in Oracle, such as in the above example, should be processed as follows:
Update Student Set Class = Deccode (Inyeare, '1993', '8', '1994', '7', '1995', '6', '0');
(3) Date operation
In MSSQL's processing, the process cannot be numerically available. The main function of its main implementation has dateadd, datediff; and processes the date as a value in Oracle, its main process function has Add_MOTH, MONTH_BETWEEN, D1 ( - * /) D2, etc., the following is listed below
Dateadd (Year, 1, Pubdate)
Add_MONTHS (D1, 12)
Dateadd (Month, 3, Pubdate)
Add_MONTHS (D1, 3)
Dateadd (day, 13, pubdate)
D1 13
Dateadd (day, -3, pubdate)
D1 - 3
Dateadd (Hour, 6, Pubdate)
D1 6/24
Dateadd (Minutes, 24, Pubdate)
D1 24/1440
Datediff (Minute, D1, D2)
(D2-D1) * 1440
Datediff (Hour, D1, D2)
(D2-D1) * 24
Datediff (Month, D1, D2)
MONTHS_BETWEEN (D1, D2)
Datename (Month, getDate ())
To_char (sysdate, 'month')
Datename (Year, getDate ())
To_char (sysdate, 'year')
Datename (day, getdate ())
To_char (sysdate, 'day')
Datedart (Month, getDate ())
To_char (sysdate, 'mm')
Datedart (Year, getDate ())
To_char (sysdate, 'yyyy')
Datedart (day, getdate ())
TO_CHAR (SYSDATE, 'DD')
Getdate ()
sysdate
<4>, from T-SQL to PL / SQL Migration Scheme
By the above discussion, when migrating from T-SQL to PL / SQL, it is meticulous to compare the syntax and function.
After adjustment, pay special attention to how the common function is replaced and the main control structure is adjusted.
(1) Convert all GetDate to sysdate;
(2) Change all of the SELCT @var = column from table where condition
Select Column Into Var from Table Where Condition;
Change all SELCT @ var1 = @ var2 to
Var1: = var2;
(3) Convert all Convert to be to_char or trunc
Example 1:
Declare RQ1 DateTime, RQ2 DateTime
...
Select Je from Sr Where RQ> = Convert (Char (10), RQ1, 111)
And RQ Should be modified: Date RQ1; Date RQ2; SELECT SR INTO JE Where RQ> = Trunc (RQ1) AND RQ Example 2: Declare RQ1 DateTime, RQ2 DateTime ... Select Je from Sr Where Convert (10), RQ, 111)> = Convert (Char (10), RQ1, 111) And RQ Should be modified: Date RQ1; Date RQ2; SELECT SR INTO JE WHERE TRUNC (RQ)> = Trunc (RQ1) AND TRUNC (RQ) or: Date RQ1; Date RQ2; SELECT SR INTO JE where to_CHAR (RQ, 'YYYY / MM / DD')> = TO_CHAR (RQ1, 'YYYY / MM / DD') And to_Char (RQ, 'YYYYY / MM / DD') (3) Direct ratio of PL / SQL does not pay the time field and rule string As in T-SQL SELECT @JE = Sr where rq> '2001.01.01' is ok. In PL / SQL SELECT SR INTO JE WHERE RQ> '2001.01.01'; It is not good. If the proportion is to be achieved, it should be changed; SELECT SR INTO JE WHERE RQ> To_Date ('2001.01.01', 'YYYY.MM.DD'); or SELECT SR INTO JE where to_CHAR (RQ, 'YYYY.MM.DD')> '2001.01.01'; (4) Change all Datediff in T-SQL to Trunc (D1-D2), Months_Between Such as select @Ts = datediff (days, date1, date2), should be changed in PL / SQL: Ts = trunc (date2 - date1); Such as select @ys = datediff (Month, Date1, Date2), should be changed in PL / SQL: Ts = months_between (Date1 - Date2); (5) DATEADD is changed to D N or Add_MONTHS If select date2 = dateadd (days, date1, 3) should be changed in PL / SQL: Date2: = DATE1 3; If select date2 = dateadd (Month, Date1, 6), it should be changed in PL / SQL: Date2: = add_months (date1, 6); (6) The version of Oralce8i is supported to support the temporary table. It creates a syntax for: Create Global Temporary Table Table_name (CLOMN1 TYPE, COLUMN2 TYPE); for the version below Oralce8i does not support, comprehensive consideration, when migrating from SQL Server to Oracle, the following scenarios: 1. A temporary table in the T-SQL statement is actually generated in the background - the forthcoming is created as a formal table, adding a column in its own column to the sequence number to identify different operations. 2. Plus the "TMP_" prefix on all such a table when the temporary table is actually activated. <1>, DUAL use In Oracle, you can use such syntax from the background server to extract the time value: SELECT SYSDATE INTO: VARIBLE from DUAL <2>, Oracle cannot be built in the stored procedure <10> Connect remote databases (including different servers) Database links are closely linked to distributed database functions. Database link allows users to process remote databases without knowing where data is. When a database link is established, login information for remote data is provided. Whenever a database link is used, a dialog is initialized on a distributed network to resolve references to remote database objects. (1) Create an alias for a remote database, with Oracle8 Net Easy Config, and add the following format in the file tnsnames.ora: Alias .world = (Description = (Address = (protocol = nmp) (Server = remote data server name) (PIPE = ORAPIPE)) (Connect_Data = (SID = remote database name))))))))))) (2) Create a database link Syntax: create or replace [public] Database Link Connect_name Connect To Username Identified by Password Using 'connection_string'; The syntax used in the case of creating a database link depends on the following two conditions: n Database link "public" or "private" status; n Use the default or explicitly log in to the remote database. If the established database link is used, the keyword public should be specified when creating; no such parameter system defaults to private; You can specify the user and password link to the database, as follows: Create or Replace Database Link DB_LINK_1 Connect to 'Scott' Identified by 'Tiger' Using 'HQ' Then, a link to the remote database "HQ" is created in the current database, the username and password used to connect to "Tiger"; If you do not specify a user and password, you will use the database link, the system uses the user attempts to connect, the creation process is as follows: Create or Replace Database Link DB_LINK_1 Using 'HQ' (3) References of remote database objects After creating a database link, you can access objects with access permissions in the current database, and the referenced methods are to add the database link name to any table or view that can access the remote account. When adding a database link name to a table or view name, you must use a "@" before the database link name, such as: Select * from worker @ remote_connect; (3) Use synonyms to access remote objects For remote data in the database link, you can create synonyms on the local database, reaching the access to the logically quite local database object, make the grammar simple, as follows: Create synonym worker_syn For worker @ remote_connect; After creating, access to the Worker table for remote database Remote_Connect is as follows: Select * from worker_syn;
