The syntax of plsql is different from that of tsql. For more information, see.
The syntax of plsql is different from that of tsql. For more information, see.
Insert into testtable (recordnumber, currentdate) values (I, sysdate );
Print '';
Select @ I = @ I + 1;
End;
After comparison, we can see that it is different.
The command structure in plsql is
Delacre
Define statement segments
Begin
Execution statement segment
Exception
Exception Handling statement segment
End
This is the overall structure of the plsql program.
Defining variables is different from mssql
Basic Method
Variable name type identifier [notnull]: = Value
Example age number (8): = 26
Added the ability to define Variable Composite data types.
1.% type Variable added
Declare
Mydate user. Testtable. currentdate % type;
Another variable of the % rowtype type can identify the data type of the Field Obtained by the variable, and % rowtype can be used to identify the variable to obtain the data type of the entire record.
Variable name data table. Column name % type
Variable name data table % rowtype
Declare
Mytable testtbale % rowtype contains all testtable fields, but you can select
Begin
Shelect * into mytable
From temuuser. tedttbale
Where recordnumber = 88
Dbms_output.put_line (mytable. currentdate );
End;
There is also a defined variable
Format
Type Composite variable name is record (
Variable type, which can have several );
Variable name composite variable name this variable name is like a Class Object in java, and compound variable name is the class name.
Begin
Select * into variable name from table name where Condition
Dbms_output.put_line (variable name. Value in table)
End
You can also define a one-dimensional array.
Type table type is table of type index by binary_integer
Table variable name table Type
The index by binary_integer clause indicates that the index is a signed integer,
In this way, the data method used to access table-type variables is "Table variable name (index symbol integer )"
Declare
Type tabletype1 is table of varchar2 (4) index by binary_integer;
Type tabletype2 is table of tempuser. testtable. recordnumber % type index
Binary_integer;
Table1 tabletype1;
Table2 tabletype2;
Begin
Table1 (1): = '';
Table1 (2): = 'Junior 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
Define a multi-dimensional table Type Variable
Defines a multi-dimensional table Type named tabletype1, which is equivalent to a multi-dimensional array. table1 is a variable of the multi-dimensional table type.
Records with a recordnumber of 60 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 (60). recordnumber | table1 (60). currentdate );
End;
Let's look at the following program.
Set serveroutput on
Declare
Result integer;
Begin
Result: = 10 + 3*4-20 + 5 ** 2;
Dbms_output.put_line ('Operation result: '| to_char (result ));
End;
| This symbol is a connection statement.
To_char (result) the output of the dbms_output.put_line function can only be a string. Therefore, the to_char function is used to convert the numeric result to the numeric type.
To_char: convert data of other types to character type. To_date: convert other types of data to the date type. To_number: convert other types of data to numeric type.
What are the control statement combinations in plsql?
1. if... then... end if condition Control
If condition then
Statement segment;
End if;
2. if... then... else... end if condition Control
If condition then
Statement segment 1;
Else
Statement Segment 2;
End if;
3. if nested condition Control
If condition 1 then
If condition 2 then
Statement segment 1;
Else
Statement Segment 2;
End if;
Else
Statement segment 3;
End if;
4. loop... exit... end loop control
Loop
Loop statement segment;
If condition statement then
Exit;
Else
Processing statement segment for loop exit
End if;
End loop;
5. loop... exit... when... end loop control
The syntax structure of loop... exit... when... end loop control is similar to loop... exit... end loop control.
Exit when is actually equivalent
If condition then
Exit;
End if;
6. while... loop... end loop control
While condition loop
Execution statement segment
End loop;
7. for... in... loop... end loop Control
For loop Variable in [reverse] lower loop threshold... loop
Loop processing statement segment;
End loop;
Last 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, sclect @ number = @ number + 1
End loop;
Dbms_output.put_line ('number1 value: '| to_char (number1 ));
End;
I learned java. I think plsql is much simpler than java. But oracle commands are only a part of more things that I need to learn. Haha
Additional transaction processing commands in plsql
Commit command
Commit transaction commit command. In oracle, to ensure data consistency, a workspace will be created for each client in the memory. That is to say, all the operations before using the commit command are completed in this work group, only after the commit command is used will the commands you write be written into the database.
There is an automatic transaction commit command
Set auto on
Set auto off
Rollback command
Rollback is a transaction rollback command. If you find that the delete insert update and other operations need to be restored, you can use the rollback command to roll back to the status of the previous commit.
Set auto off: Disable auto submit first.
Select * from scott. emp;
Delete form scott. emp;
Rollback
At this time, we can see that scott. emp remains unchanged.
Savepoint command
This command saves the dot command. A transaction is usually composed of multiple commands. You can divide each transaction into several parts for saving. In this way, you do not have to roll back the entire transaction because each save point is rolled back.
Create a save point savepoint
Rollback save point rollback to save the name
Here is 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 the Restore Point
The cursor
I'm not impressed with this in mssql.
Cursor Is Not A pointer in c. When I see this word, I think of a pointer. Unfortunately, the pointer in c is very different. Don't confuse it. No one will confuse it.
A cursor is a place where temporary data is stored.
To use a cursor, you must first define
Cursor name is select statement
Cursor
Let's look at an example.
Set serveroutput on
Declare
Tempsal scott. emp. sal % type defines a variable of the same type as scott. emp. sal.
Cursor mycursor is defines a cursor mycursor
Select * from scott. emp
Where sal> tempsal;
Begin
Tempsal: = 800;
Open mycursor; open this cursor
End;
I forgot. I just opened the cursor and didn't need to extract the cursor data.
Run the fetch Command
Fetch cursor name into variable 1, variable 2 ,....;
Or
Fetch cursor name into record variable name;
The above program needs to be modified.
Set serveroutput on
Declare
Tempsal scott. emp. sal % type defines a variable of the same type as scott. emp. sal.
Cursor mycursor is defines a cursor mycursor
Select * from scott. emp
Where sal> tempsal
New scott. emp % rowtype; a new variable is defined.
Begin
Tempsal: = 800;
Open mycursor; open this cursor
Fetch mycursor into new; read cursor data and add it to new
Dbms_output. _ line (to_char (new. sal); Display Results
Close mysursor; close the cursor
End;
Cursor attributes
1.% isopen attributes
It is to determine whether the cursor is opened. If it is not opened, it is to use the fetch statement to prompt an error.
2.% found attributes
Is to test whether the previous fetch statement has a value. If yes, true is returned. If no value is returned, false is returned.
3.% notfound property is opposite to the above
4. The % rowcount attribute determines the number of rows of data in the cursor.
The concept of the process is not found in SQL.
The complete process structure is as follows:
Create or replace process name
Declaration statement segment;
Begin
Execution statement segment;
Exception
Exception Handling statement segment;
End;
The process is a program block with a name. The as keyword replaces the declare of the unknown block.
Instance creation process
Create a process named tempprocdeure. create is the identifier of the creation process. replace indicates that a process with the same name will overwrite the original process. Defines a variable. Which of the following types is the same as the currentdate field in the testtable data table? It is a date type. The currentdate field with the recordnumber field 88 in the data table is sent to the variable and the result is output.
Set serveroutput on
Creat or replace procedure tempuser. tempprocedure
Tempdate tempuser. testtable. currentdate % type;
Begin
Select currentdate
Into tempdate
From testtable
Where recordnumber = 88;
Dbms_output.put_line (to_char (tempdate ));
End;
Procedure
Set serveroutput on
Begin
Tempprocedure;
End;
The following describes the process of parameters.
1. Parameter type
The in reading parameter program transmits a value to the process
The out reading parameter process transmits a value to the program.
In out two-way parameter program process transmits values to each other
Define a process with Parameters
Set serveroutput on
Creat or replace procedure scott. tempprocedure (
Tempdeptno in scott. dept. deptno % type,/* defines a variable of the in type */
Tempdname out scott. dept. danme % type,/* defines an out type variable */
Temploc in out scott. dept. loc % type) as/* defines an inout variable */
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;
After the definition is completed, we will start using
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;
Done.
That is to say, when the parameter is passed to the above-defined process, the variables with parameters can accept the values of the three variables.
In java, the process is that the class contains three parameters.
These three variables are data, and of course there are no objects. Haha, isn't it java? haha
I wrote it here today, and I'm off duty 7.3.
Exception Handling
Is the method to deal with special circumstances in the program
1. Define Exception Handling
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 exception handling is triggered, you can define the exception handling part. The syntax is as follows:
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 definition, trigger, and process of exception handling. Define salaryerror
In the scott. emp data table, find the record with empno = 7566, put the value in the variable tempsal, and Judge
If the tempsal value is not between 900 and 2600, it indicates that the employee's salary is incorrect. An exception is triggered and a message is displayed.
Set serveroutput on
Declare
Salaryerror exception;
Tempsal scott. emp. sal % type;
Begin
Select sal into tempsal
From scott. emp
Where e-mapreduce = 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;
