Plsql and TSQL have different grammatical _oracle

Insert into TestTable (recordnumber,currentdate) values (i,sysdate);
print ';
Select @i=@i+1;
End

You can tell by comparison that it's different.

Plsql inside the structure of the command
Delacre
Define a statement segment
Begin
Execute a statement segment
exception
Exception Handling Statement segment
End
This is the PLSQL program overall structure diagram

Define the difference between a variable and MSSQL
Basic methods
Variable name type identifier "Notnull": = value
Case Age Number (8): =26
More functionality to define a composite data type variable
1. More%type variables
Declare
MyDate user. Testtable.currentdate%type;
There are%rowtype types of variables that can recognize variables to obtain the data type of the field, and use%rowtype to get the data type of the entire record.
Variable name data table. Column Name%type
Variable name data table%rowtype
Declare
MyTable Testtbale%rowtype contains testtable All fields are only available when outputting the output.
Begin
Shelect * INTO MyTable
From Temuuser.tedttbale
where recordnumber=88
Dbms_output.put_line (mytable.currentdate);
End
And then there's the definition of a conforming variable
Format
Type compound variable name is record (
Variable type, can have several);
Variable name compound variable name the variable name is the same as the object in the Java class and the compound variable name is the class name so you can understand your personal opinion.
Begin
SELECT * into variable name from table name where condition
Dbms_output.put_line (variable name. Values in the table)
End

In addition, you can define a one-dimensional array
Type table type is table the type index by Binary_integer
Table variable Name table type
The index by Binary_integer clause is indexed by a signed integer,
The way to access the data in a table-type variable is to "table variable name (index symbol integer)"

Declare
Type Tabletype1 is Table of VARCHAR2 (4) index by Binary_integer;
Type tabletype2 is table the Tempuser.testtable.recordnumber%type index by
Binary_integer;
Table1 Tabletype1;
Table2 tabletype2;
Begin
Table1 (1): = ' university ';
Table1 (2): = ' College ';
Table2 (1): = 88;
Table2 (2): = 55;
Dbms_output.put_line (table1 (1) | | Table2 (1));
Dbms_output.put_line (table1 (2) | | Table2 (2));
End
A standard one-dimensional array

To define a multidimensional table type variable
Defines a multidimensional table type named Tabletype1, which is equivalent to a multidimensional array, Table1 is a multidimensional table type variable, and a datasheet tempuser.testtable
RecordNumber 60 records are extracted and stored in Table1 and displayed.

Type Tabletype1 is table of Testtable%rowtype index by Binary_integer;
Table1 Tabletype1;
Begin
SELECT * into table1 (60)
From tempuser.testtable
where recordnumber=60;
Dbms_output.put_line (table1) recordnumber| | Table1. currentdate);
End

Look at this procedure below.
Set Serveroutput on
Declare
result integer;
Begin
result:=10+3*4-20+5**2;
Dbms_output.put_line (' The result of the operation is: ' | | To_char (result));
End

|| This symbol is a connection statement
To_char (Result) dbms_output.put_line function output can only be strings, so use the To_char function to convert numeric results to character types.
To_char: Converts other types of data to character type. To_date: Converts other types of data to date type. To_number: Converts other types of data to numeric type.

Besides, what kinds of control statement combinations are there in the Plsql?

1. If.. Then.. End If condition control
If condition Then
Statement section;
End If;

2. If.. Then.. else.. End If condition control
If condition Then
statement paragraph 1;
Else
statement paragraph 2;
End If;

3. If nested condition control
If condition 1 Then
If condition 2 Then
statement paragraph 1;
Else
statement paragraph 2;
End If;
Else
statement paragraph 3;
End If;

4.loop.. Exit.. End Loop loop control
Loop
Circular sentence segment;
If conditional statement Then
Exit
Else
The processing statement segment that exits the loop
End If;
End Loop;

5. Loop ... Exit.. When.. End Loop loop control
Using loop ... Exit.. When.. End Loop loop control syntax structure with loop. Exit.. End Loop Loop controls Similar
Exit when is actually equivalent to
If condition Then
Exit
End If;

6.while.. Loop.. End Loop loop control
While condition loop
Execute a statement segment
End Loop;

7.for.. In.. Loop.. End loop control
For loop variable in [reverse] loop lower bound ... Loop Upper Loops
Circular processing of sentence segments;
End Loop;
The last one is an example
Set Serveroutput on
Declare
Number1 integer:=80;
Number2 integer:=90;
I integer:=0;
Begin
For I in 1..10 loop
number1:=number1+1; In MSSQL is sclect @number = @number +1
End Loop;
Dbms_output.put_line (' number1 value: ' | | To_char (Number1));
End
I learn Java for plsql a very simple look and Java compared to a lot simpler but Oracle command is only a part of more things I need to learn to brag about haha

More transaction orders in Plsql

Commit command
Commit TRANSACTION commits a command. In Oracle, in order to ensure data consistency, a workspace is created for each client in memory, meaning that the operations before the commit command are completed in this work group, and only after the commit command is written to the database.
There is a command to automatically commit the transaction
Set Auto on
Turn off to set auto off

Rollback command
The rollback is a transaction rollback command, which, in the absence of a commit command, can be rolled to the state of the last commit if a delete insert update is found to be restored.
Set auto off to turn off Autocommit first
SELECT * from Scott.emp;
Delete Form scott.emp;
Rollback
At this point, you can see the scott.emp or the previous change.

SavePoint command
This command saves the point command. Transactions are usually made up of multiple commands, and each transaction can be divided into sections for saving so that each savepoint is rolled back without having to roll back the entire transaction.
Create a savepoint savepoint save a roll Call
Rollback save point Rollback to save roll
Here's an example.
Insert into Scott.emp (empno,ename,sal) VALUES (9000, ' Wang ', 2500); Insert a value First
SavePoint Insertpoint; Create a restore point named Insertpoint
Rollback to Insertpoint; Restore to that restore point

Let's start with the cursor.
This thing is not in MSSQL, I don't remember.
The cursor is not a pointer in C, I think of the word as soon as I see the pointer unfortunately what C inside the pointer is not the same don't confuse the estimated no one will confuse.
Cursors can be said to be a temporary place for data storage
To use a cursor to define the first
Cursor cursor name is SELECT statement
Cursor This is the keyword of the cursor selcet query command
See an example
Set Serveroutput on
Declare
Tempsal Scott.emp.sal%type defines a variable. He's Scott.emp.sal the same type.
Cursor MyCursor is defines a cursor mycursor
SELECT * FROM Scott.emp
where sal>tempsal;
Begin
tempsal:=800;
Open mycursor; Open this cursor
End
I just forgot to open the cursor. To extract data from a cursor
Using the FETCH command
Fetch cursor name into variable 1, variable 2, .... ；
Or
Fetch cursor name into record variable name;
The procedure above should be changed.

Set Serveroutput on
Declare
Tempsal Scott.emp.sal%type defines a variable. He's Scott.emp.sal the same type.
Cursor MyCursor is defines a cursor mycursor
SELECT * FROM Scott.emp
where Sal>tempsal
New Scott.emp%rowtype; there's a variable defined.
Begin
tempsal:=800;
Open mycursor; Open this cursor
Fetch mycursor into new; Read cursor Data add him to new
Dbms_output._line (To_char (new.sal)); Show results
Close mysursor; Close closes this cursor
End

Properties of Cursors
1.%isopen Property
is to determine whether the cursor is open, if it is not open, to prompt for errors with the FETCH statement
2.%found Property
is to test whether the previous FETCH statement has a value, returns true without returning false
The 3.%notfound property is the opposite of the above
4.%rowcount property to determine the number of data rows for a cursor is how much data

The following is the concept of the process in SQL No
The complete process is structured as follows
Create or replace procedure name as
A declaration statement segment;
Begin
Execute the sentence segment;
exception
Exception handling statement segment;
End
Process is a name of the program block, as keyword instead of the declare of the unknown block

Procedure for creating an instance
Create a procedure named Tempprocdeure, create is the identifier for the creation process, and replace means that a procedure with the same name will overwrite the original procedure. Defines a variable whose type is the same as the type of the CurrentDate field in the datasheet, is a date type, testtable the currentdate field content of the RecordNumber field in the datasheet into a variable, and then outputs the result.

Set Serveroutput on
creat or replace procedure tempuser.tempprocedure as
Tempdate Tempuser.testtable.currentdate%type;

Begin
Select CurrentDate
Into Tempdate
From TestTable
where recordnumber=88;
Dbms_output.put_line (To_char (tempdate));
End
Use process
Set Serveroutput on
Begin
Tempprocedure;
End
Next, the process with parameters
1. Parameter type
In-read parameter program passes numerical value to procedure
Out readout parameter process passes numerical value to program
In-out bidirectional parametric program processes pass values to each other
To define a procedure with parameters
Set Serveroutput on
creat or replace procedure scott.tempprocedure (
Tempdeptno in scott.dept.deptno%type,/* defines an in type of variable * *
Tempdname out scott.dept.danme%type,/* defines a variable of an out type.
Temploc in Out Scott.dept.loc%type) AS/* Defines a variable of inout type * *
Loc1 Scott.dept.doc%type;
Dname1 Scott.dept.dname%type;
Begin
Select Loc into Loc1
From Scott.dept
where Deptno=tempdeptno;
Select Danme into Danme1
From Scott.dept
where Deptno=tempdeptno;
temploc:= ' Address ' | | Loc1;
tempdname:= ' Name ' | | dname1;

End

OK, here we go.
Set Serveroutput on
Declare
Myno Scott.dept.deptno%type;
Mydname Scott.dept.dname%type;
Myloc Scott.dept.loc%type;

Begin
myno:=10;
Mydname:= ";
Myloc:= ";
Scott.tempprocedure (Myno,mydname,myloc);
Dbms_output.put_line (Myno);
Dbms_output.put_line (Mydname);
Dbms_output.put_line (Myloc);
End
It's done.
That is, a variable with a redefined three variables that is passed to the parameter in the procedure defined above can accept the values of these three variables.
To explain in the Java language is that the process is a class with three parameters
These three variables are data, of course, no object. Haha, after all, is not Java haha

It's written here today, and I'm off 7.3.

Exception handling
is the procedure for dealing with special situations.

1. Define Exception handling
The syntax for defining exception handling is as follows:
Declare
Exception name exception;
2. Trigger Exception Handling
The syntax for triggering exception handling is as follows:
Raise exception name;
3. Handling Exceptions
After the exception handling is triggered, you can define the exception handling section, which is the following syntax:
Exception
When exception name 1 then
Exception handling statement segment 1;
When exception name 2 Then
Exception handling statement segment 2;

The following Pl/sql program contains the complete process of defining, triggering, and handling exception handling. Define the name Salaryerror
Exception, find the empno=7566 record in the Scott.emp datasheet, put its value in the variable tempsal, and judge
If the Tempsal value is not between 900 and 2600, there is a problem with the employee's salary, and the exception handling is activated and the message is prompted.

Set Serveroutput on
Declare
Salaryerror exception;
Tempsal Scott.emp.sal%type;
Begin
Select Sal into Tempsal
From Scott.emp
where empno=7566;
If Tempsal <900 or tempsal>2600 Then
Raise Salaryerror;
End If;
exception
When Salaryerror Then
Dbms_output.put_line (' Salary out of range ');
End