Oracle-4-: Super Beginner's entry-level notes: Plsql, basic syntax, record types, loops, cursors, exception handling, stored procedures, storage functions, triggers

Beginners can from the query to the current PL/SQL content can be found in my notes here, I hope to help everyone, video resources in resources,

My own full set of notes in notes

The SQL keywords that you can continue to use in PL $ are: UPDATE Delete insert Select--into commit rollback savepoint, it is important to note that the query is not the same as before.

Plsql consists of three blocks: Declaration part, execution part, exception handling part

Declare: This declares the variables, types and cursors used in PL/SQL, as well as the local stored procedure's and functions

Begin: Execution part: Process and SQL statement, which is the main part of the program

Exception: Execute exception section, error handling

End

Where the execution part is necessary

HelloWorld of PL/SQL

Set SERVEROUTPUT on-this statement must be executed first, otherwise there is no output
Begin
Dbms_output.put_line (' Hello World ');
End --The first three are the results of execution without executing the SET SERVEROUTPUT on statement to have output after executing this statement

There is only the Begin section, and the ending end, because this statement eliminates the need for a variable declaration and omits the declare part, and the exception error part

　　　　　　

A naming convention for variable constants, such as the following red is the beginning of the recommended naming

　　　　　　

Example: using PL/SQL to query out the name of the teacher TNO as t001 and output

The following SELECT statement puts the query results in the variable v_name and then outputs

Declare
V_name TEACHER. Tname%type; --Here is a dynamic get the type of the Tname field in the teacher table

V_tno VARCHAR2 (10);
Begin
--General Query statement: SELECT * from teacher where tno = ' t001 ';
Select Tno,tname into V_tno,v_name from teacher where tno = ' t001 ';
SYS. Dbms_output. Put_Line (v_name| | ', ' | | V_TNO);
End

Record type: Is the logical correlation of data stored as a unit, its role is to store different but logically related information, similar to the concept of a class in Java

Note You cannot assign a column in a SELECT statement to a Boolean variable

Declare

--in the format of assigning a value to a variable: V_tno number (10): = 10; From here ": =" is an assignment, judged as =
--type Custom Name 1 is record
Type Teacher_mas is record (
V_name TEACHER. Tname%type,--comma
V_tno VARCHAR2 (10)--No punctuation
);
--Define a member variable of a record type
--Custom 2 custom Name 1 in this is equivalent to creating an object
V_teacher_mas Teacher_mas;

--If the field is particularly numerous we can use: V_teacher_mas teacher%rowtype; Indicates that all types in the teacher table have been, and the following can be queried directly * into V_teacher_mas
Begin
--General Query statement: SELECT * from teacher where tno = ' t001 ';
Select Tno,tname into V_teacher_mas from teacher where tno = ' t001 ';
SYS. Dbms_output. Put_Line (v_teacher_mas.v_name| | ', ' | | V_TEACHER_MAS.V_TNO);
End

Process Control Statements:

If statement structure: If conditional expression then----end if; equivalent in Java if () {}

If "conditional expression" then--else--end if; equivalent to if () {} else {} in Java

If conditional expression then--ELS if conditional expression--endif; here is elsif not elseif equivalent to multiple judgments in Java: if () {} elseif () {} eles{}

Example:--Query sno for s001 Student's c001 course results, if the output is greater than 60 pass less than 60 output fail other output general

Each time the If or elsif must be followed by a delimiter with the end if as the closing flag, of course the same if after can be added and
Declare
V_score SC. Score%type;
Begin
Select score into V_score from SC where sc.sno= ' s001 ' and cno= ' c001 ';
If V_score<60 then SYS. Dbms_output. Put_Line (' fail ');
elsif V_score >=60 then SYS. Dbms_output. Put_Line (' Pass ');
Else SYS. Dbms_output. Put_Line (' General ');
End If;
End

Case Statement structure: case value

When expression then

When expression then

Else

End

Because case is disgusting, Sno for s001 of Student's c001 course achievement is 78.9, look under query SQL, here is like Java, the switch in the same

Case a value, when after only with a constant, and then cannot be assigned, output, etc., can only return the results

Declare
V_score SC. Score%type;
V_mas VARCHAR2 (30);
Begin
Select score into V_score from SC where sc.sno= ' s001 ' and cno= ' c001 ';
V_mas: =
Case V_score While 78.9 then ' pass '
When "Failed"
Else ' General '
End

SYS. Dbms_output. Put_Line (V_mas);
End

Loop structure: Example output 1--100 with cyclic structure

1. Loop...exit...where. End Loop

　　　　　　　　　　　
Declare
V_min Number (3): = 1;
Begin
Loop
SYS. Dbms_output. Put_Line (v_min);
Exit when V_min >= 100;
V_min: = V_min +1;
End Loop;
End

2. while< Boolean expression > Loop to execute the statement end loop;

Declare
V_i Number (3): = 1;
Begin
While V_i <=100 loop
SYS. Dbms_output. Put_Line (v_i);
V_i: = V_i +1;
End Loop;
End

3. For loop counter in "reverse" upper limit: Lower loop the statement to be executed end loop; Don't forget that there are two points in the middle of the limit

The variable is automatically +1 per loop, using the keyword reverse auto-1, the number following in reverse must be in order from small to large, and must be an integer, cannot be a variable or an expression, you can use Exit to exit the loop

Begin
For C in 1..100 Loop
SYS. Dbms_output. Put_Line (c);
End Loop;
End

Marking with Goto: The meaning of the unconditional jump to the specified label

Example: Print 1 to 100, print end loop when printing to 50, and end the entire loop

Declare
V_i Number (3): = 1;
Begin
While v_i<=100 loop
If v_i=50
then Goto label;
End If;
SYS. Dbms_output. Put_Line (v_i);
V_i: = v_i+1;
End Loop;
<<label>>
SYS. Dbms_output. Put_Line (' End Cycle ');
End

Use of cursors: like iterator iterator in Java, a cursor is a handle or pointer to a context that can handle multiple rows of records through a cursor

　　　　　　　　　　

1. Show cursor Handling

Show cursor handling Four steps

1. Define cursors: Cursor--is--Cannot use length constraints when specifying data types

2. Opening the cursor: open--: The program cannot open a cursor repeatedly by opening a statement

3. Extract cursor: fetch--into--

4. Close the cursor: close--

Cursor instance: Print out information for all employees in department 80th
Declare
--Record type
Type Emp_mas is record (
V_empid Employees.employee_id%type,
V_name Employees.last_name%type,
V_sal Employees.salary%type
);
--Record type Object
Emp_mas_record Emp_mas;
--Defining cursors
Cursor Emp_ens_mas is a select employee_id,last_name,salary from employees where department_id=80;
Begin
--Open cursor
Open Emp_ens_mas;
--Extracting cursors
Fetch emp_ens_mas into Emp_mas_record;
--emp_ens_mas%found equivalent to Hashnext in Java
While Emp_ens_mas%found loop
SYS. Dbms_output. Put_Line (emp_mas_record.v_empid| | ', ' | | emp_mas_record.v_name| | ', ' | | Emp_mas_record.v_sal);
Fetch emp_ens_mas into Emp_mas_record;
End Loop;
--Close cursor
Close Emp_ens_mas;
End

2. Cursor FOR loop: PL/SQL provides a cursor for loop, automatically executes the cursor's open,fetch,close statement and the function of the loop statement, and when entering the loop, the cursor for loop automatically opens the cursor and extracts

The first row of cursor data, when the program finishes processing the currently extracted data and enters the next loop, the cursor for Loop statement Huidong the next row of data for the program to process, when the results are extracted

Centralize all data rows after the end loop, and automatic cursors

Format: For variable in cursor loop-----end loop;

Print out the information for all employees in department 80th, as in the previous question

Declare
--Defining cursors
Cursor Emp_ens_mas is a select employee_id,last_name,salary from employees where department_id=80;
Begin
For C in Emp_ens_mas Loop
SYS. Dbms_output. Put_Line (c.employee_id| | ', ' | | c.last_name| | ', ' | | C.salary);
End Loop;
End

3. Exception capture and handling

1. Predefined exceptions are some of the system-defined exceptions that are automatically thrown by the system, as follows

　　　　　　　　　　　　

　　　　　　　　　　　　　　

Declare
V_i number (30);
Begin
Select salary to V_i from employees where employee_id >=100;
SYS. Dbms_output. Put_Line (v_i);
End

Because employee_id >= 100 of employees pay more than one result, there will be too many return values here.

　　　　　　　　　　　　　　　　

The exception can be caught in the exception and processed, if not processed, the system error and the overall termination of the program

Declare
V_i number (30);
Begin
Select salary to V_i from employees where employee_id >=100;
SYS. Dbms_output. Put_Line (v_i);
　　　　　　　　　　　　　　exception
When Too_many_rows and then SYS. Dbms_output. Put_Line (' The return value is too much!! ‘);

　　　　　　　　　　　　　　　　　When others and then SYS. Dbms_output. Put_Line (' Other error!! ‘);
End

The above captures the system pre-defined exception Too_many_rows , and if any other unknown exception is generated, it can be captured and processed using others

2. Handling of non-pre-defined exceptions

For non-scheduled exception handling, you must first define a non-defined Oracle error, step

1. Define exception conditions in the definition section of the PL/SQL block:< anomalies >exception;

2. Connect its defined exception to the standard Oracle error, using the pragma exception_init statement: pragma exception_init (< exception >,< exception code >);

3. Handling exception cases in the exception handling section of PL/SQL blocks

Let's remove employee_id = 100 users

Declare
Begin
Delete FROM employees where employee_id = 100;
End

At this time because employee 100th has a child record, employee_id equals the manager_id of this watch, so can not delete

　　　　　　　　　　

The above-mentioned error code 2292 does not have a predefined exception in Oracle, and we can only define our own error name associated with this error number.

Declare
my_exception exception;
pragma exception_init (my_exception,-2292);
Begin
Delete FROM employees where employee_id = 100;
exception
When My_exception and then SYS. Dbms_output. Put_Line (' Violation of constraint non-predefined exception!! ‘);
End

　　　　　　　　　　　　

3. User-defined exception handling

User-defined exceptions are triggered by the display using the Raise statement, and when an exception is thrown, the control shifts to the exception block exception error section

The steps for handling this type of exception are as follows

1. Define exceptions < anomalies in the definition section of the PL/SQL block >exception;

2. Raise < abnormal situation >

3. In the Exception handling section of the PL/SQL block, the exception is dealt with accordingly.

Example: Query employee_id for the salary of employee number 100th, if the salary >1w is thrown abnormally "high salary"

Declare
My_exception exception;
V_i number (5);
Begin
Select salary to V_i from employees where employee_id = 100;
If v_i>10000 Then
Raise My_exception; --Departure Custom exception
End If;
exception
When My_exception and then SYS. Dbms_output. Put_Line (' High salary!! ‘);
End

　　　　　　　　　　　　

stored functions and stored procedures

Oracle provides the ability to store PL/SQL programs in a database and can run them anywhere, so it's called a stored procedure or function

The only difference between a procedure and a function is that the function always returns the data to the caller, and the procedure does not return

Create a function: When you create a function, overwrite the creation directly

1. Create an inline function

Syntax: Create or Replace function name (ID number, name varchar2)

return number

IS--a variable cursor that needs to be used, etc. can be defined here

Begin--Function body

Exception--Exception handling

End

instance, write a function that can return HelloWorld (no parameter function)

Create or Replace function Get_helloworld
return VARCHAR2
Is
Begin
Return ' Hello world ';
End

Function creation Complete Call this function: 1. Select Get_helloworld from dual;

2. Begin
SYS. Dbms_output. Put_Line (get_helloworld);
End

(parameter function): Create or Replace function Get_helloworld (name varchar2)--No length required
return VARCHAR2
Is
Begin
Return ' Hello World ' | | Name
End

Call: Select Get_helloworld (' Pure Rookie ') from dual;

2. About out functions: PL/SQL programs can have multiple return values through out-type parameter implementations

The in parameter token indicates that the value passed to the function does not change in the execution of the function; The out token indicates that a value is evaluated in the function and passed to the calling statement by that parameter, and the value passed to the function in the out tag can vary

and passed to the calling statement. If the tag is omitted, the parameter is implied in. Return contains the data type of the returned result

Example: Define a function that gets the sum of the payroll for a given department and the total number of employees in that department (defined as an out type parameter)

Requirement: The department number is defined as a parameter and the payroll is defined as the return value

Creating a function: Create or Replace function get_salary (Empid number,empnum out number)
return number
Is
V_sal Number (6): = 0;
Cursor My_emp_cur is a select salary from employees where department_id = Empid;
Begin
Empnum: = 0; --The parameter can only be assigned in the function body, if not correct please
For C in My_emp_cur Loop
V_sal: = C.salary + v_sal; --Wages
Empnum: = empnum+1;
End Loop;
return v_sal;
End

Calling function: Declare
V_count_people_number number (3);--the variable that stores the population
Begin
SYS. Dbms_output. Put_Line ( get_salary (80,v_count_people_number));
SYS. Dbms_output. Put_Line (V_count_people_number);
End

--From the calling function, it can be seen that the return number is not shown in the Out function above, but when called, Oracle brings back parameters to its own defined variables, the output will only output the results returned by the function, and the output parameters

Stored procedure creation: Gets the sum of wages (out) for a given department, requiring that the department number and the payroll be defined as parameters

Create or Replace procedure Get_sal (Empid number,sum_sal out number)
Is
Cursor My_emp_cur is a select salary from employees where department_id = Empid;
Begin
Sum_sal: = 0;
For C in My_emp_cur Loop
Sum_sal: = sum_sal+c.salary;
End Loop;
End

We find that the syntax format of the stored procedure is only the difference between the syntax format of the stored function and the procedure no return, and the function has a return

Call: Declare
V_count_people_number number (7);
Begin
Get_sal (80,v_count_people_number);
Sys.dbms_output.put_line (V_count_people_number);
End

　　　　　　　　　　　　

Triggers: Similar to procedures and functions, there are PL/SQL blocks for declaring, executing, and exception handling, and the stored procedure is called by the program, and the trigger is triggered by the event, the trigger cannot accept the parameter, and the Oracle event refers to the additions and deletions to the table or view.

You can trigger on each line or statement operation before adding or deleting the operation or after the operation.

The composition of the trigger:

1. Triggering events: adding and deleting changes

2. Trigger time: Before after

3. The trigger itself:

4. Trigger frequency: Statement-level (statement) triggers and row-level triggers: For example, changing a table's payroll, if a person's trigger is a row level, if the trigger is a statement-level before or after the entire table change

Syntax for creating triggers

create [or replace] trigger name

Before | After

Insert | Update | Delete [of column]

On table

[For each row]--row-level or statement-level, write the words are line-level, not write is the statement-level

WHERE---

Add trigger on Tname on teacher table: output When updating update Tname: Tname is changed

Create or Replace Trigger TEA_TNAME_UP
After--event is triggered before
Update of Tname on teacher--action on the Tname column of the teacher table, or directly on the table, remove the row on the row directly on table

--Do not write is statement-level, write for each row is row-level
Begin-what is triggered and done
SYS. Dbms_output. Put_Line (' tname is changed ');
End

When updated: Update teacher Set tname = ' Pure rookie ' where tid=1; When

　　　　　　　　　　

: New and: Old modifiers: such as changing the data in a table, you can see both pre-and post-update data

Modify the above trigger so that it tname changes before the output changes and after the change

Create or Replace Trigger TEA_TNAME_UP
After
Update of Tname on teacher
For each row--functions with each row, using new and old must add this
Begin
SYS. Dbms_output. Put_Line (' Pre-modified: ' | |:o   ld.tname| | ' Modified: ' | |:new.tname);
End

Change Tname:update teacher Set tname = ' lazy egg ' where tid=1;

　　　　　　　　　　　　

Example: When the data in the teacher table is deleted, the deleted data is backed up to Teacher_bak;

Data in the teacher

　　　　　　　　　　　　

Data in the Teacher_bak

　　　　　　　　　　　　

Trigger creation: Create or replace Trigger Teacher_two_bak
After
Delete on teacher
For each row
Begin
INSERT into Teacher_bak values (: old.tid,:old.tname);
End

Test: Delete from teacher where tid = 1;

After execution, the data in teacher and Teacher_bak are

......

Oracle-4-: Super Beginner's entry-level notes: Plsql, basic syntax, record types, loops, cursors, exception handling, stored procedures, storage functions, triggers