"PL/SQL Programming Basics"

"PL/SQL Programming Basics"
Grammar:
Declare
Declaration parts, such as defining variables, constants, cursors
Begin
program writing, SQL statements
exception
Handling Exceptions
End
/forward slash indicates fast execution of the program
Example
--Created on 2016/8/22 by VITAS
Declare defining variables
V_num number;
V_eno number;
V_ename VARCHAR2 (10);
Begin
v_num:=234;
v_eno:=&empno; Receive input variables
Select Ename to V_ename from EMP where Empno=v_eno;
Dbms_output.put_line (V_ename);
End
/

Declaration and use of variables

Plsql is a strongly typed programming language, with the variable name plus v representing the variable
Grammar:
All variables are required to be in the Declare section and can be assigned a default value at the time of definition, and the variable declaration syntax is as follows:
variable name [constant] type [not Null][:=value]
Declare
V_resulta number;
V_RESULTB number: = 100;
V_RESULTC constant number:=14;
Begin
v_resulta:=10;
The value of dbms_output.put_line (' result ' is: ' | | (V_RESULTB+V_RESULTA+V_RESULTC));

End
/
Defining variables using%type
You can define the type of a row of records in a table by using the%rowtype declaration variable
Operator:
Mastering the Declaration and use of variables
Mastering the use of%type and%rowtype

PL/SQL Branch statements
If/else
Cycle:
Loop
Grammar:
Loop
Exit when Loop End condition
End Loop
while () loop

End Loop;
For
For loop index in loop area downline: Loop area on-line loop
End Loop;
Goto statement:
Goto Endpoint
<<endpoint>>

Exception handling:
1, compile-time exception, unable to process
2. Runtime exception: Only run-time exceptions that the user can handle
Exception handling is handled with the exception clause, and the exception is handled by the When clause
When exception type | User Custom Exception | exception code |other Then
Exception handling
Continue executing other code after an exception is generated

Record type:
Define the record type syntax:
Type name is record (
Member name data type [[not null][:= default] expression],
......
Member name data type [[not null][:= default] expression]

）

Nested record types
Employee contains departmental information
Inserting a composite data type
INSERT INTO Dept Values v_dept

Update a composite data type
Update Dept set row=v_dept where Deptno=v_dept_deptno;

The concept of an indexed table (array)
An index table is similar to an array in a program language and can hold multiple data
Difference: no initialization required
Index: Numeric or string, subscript can be set to negative
Grammar:
Type name is table of data type "not NULL"
index by [PLS_INTEGER|BINARY_INTERGER|VARCHAR2]
Access to an index that is not defined will result in an exception where the data cannot be found, but the index's subscript is not sequential, so the index value may not exist, providing exists (index) to determine if there is
Example: Using a string as a character index
Example: Using the RowType array
Example: Using an array of record types
Nested tables: (skip) Start
1. Define a simple type of nested table
CREATE [OR REPLACE] Type name As|is TABLE of data type [not null];/
Create or Replace type Project as Table of VARCHAR2 (20);
Set operators:

Aggregate functions: Count, First Last
Collection Exception Handling:
Skip End
Batch processing using ForAll:
Use for in: (Update n times)
For XX in collection first: Last loop
End Loop
2. Using ForAll: (Batch update data)
Grammar:
ForAll x in collection first. Last
Update .....

Receive data in bulk:
Bulk collect into

Sub-Program:

Goal
1, Master sub-Program classification
Contains stored procedures and functions
Define the process:
Specifically defines a set of SQL statements
Grammar:
procedure = Declaration +plsql Fast
CREATE [OR REPLACE] PROCEDURE procedure name (parameter name [pattern in/out/in out] nocopy data type, ...)
As|is
[Pragma autonomous_transaction]
Declaration section:
Begin
Exception when others then
Rollback
End Procedure Name

Procedure does not return a value
Create or Replace procedure ChangePassword (UserName in Out varchar2,
Password in off varchar2) is
pragma autonomous_transaction;
V_result Boolean;
Begin

V_result: = login (userName, password);
End ChangePassword;

Example: Defining a Process
Process is executed through the exec procedure name after the process is defined
Example:
Definition process Find name and salary based on employee number

Function:
A user-defined SQL statement or plssql is called directly, and the biggest difference between a function and a procedure is that the function can return a value, while the process intelligence returns the data through in and out.
The syntax is as follows: You do not need to define a length when the type is numnber or varchar otherwise the compilation cannot pass
CREATE [OR REPLACE] FUNCTION procedure name (parameter name [pattern] nocopy data type, ...)

Return return value type
As|is
[pragma autonomous_transaction]
Declaration part
Begin
return value;
Exception when Others then
Rollback
End Function name
Create or Replace function login (userName in out varchar2,
Password in varchar2) Return Boolean is
V_islogin boolean;
--Define ROWTYPE
Userrow t_user%rowtype;
--Define a collection of ROWTYPE types
Type Us Erinfo is table of T_user%rowtype index by Binary_integer;
--Define type variables after defining the type variable
users UserInfo;
--Define record type
Type Userrecord is record (
ID number),
UserName Varc HAR2 (+),
Password varchar2 (+),
Roleid number),
Userr Userrecord;
--Define custom transactions, The rollback and commits of this transaction do not affect the primary transaction
pragma autonomous_transaction;
Begin

--use bulk collect into batch query assignment
SELECT * into Userr from t_user where userName = ' ywp ';
Userr.username: = ' ZP ';
Insert into T_user VALUES userr;
Commit
SELECT *
BULK COLLECT
into users
From T_user
WHERE username = Username
and password = password;
If Users.count! = 0 Then
Dbms_output.put_line (' Welcome login my system! ');
Else
Dbms_output.put_line (' User name or password error ');
End If;
exception
When others then
Dbms_output.put_line (' 313 ');
/*--use for in to iterate through the collection
For I in Users.first. Users.last Loop
--to determine if the subscript exists when outputting a variable
If Users.exists (i) Then
Dbms_output.put_line (' Welcome login my system! ');
End If;
End loop;*/
UserName: = ' ywp ';
return (V_islogin);
End LOGIN;

2. Master the definition and difference of sub-program
The parameter mode of the sub-program
There are three types of formal parameter definitions
In: (default), changes made in the subroutine do not affect the original parameter (delivery of the base data type)

Out: In a subroutine with no numeric value, the subroutine can pass the value to the call (the passing of the reference data type) through this variable
InOut, the value can be passed to the subroutine, and the sub-program changes the variables to return to the call place

Nocopy Sub-options: Change parameters to reference pass, in and out for copy pass parameters
Autonomous transactions:
Each session in Oracle has a separate transaction,
Starting child transactions
Calling subroutines with Java

"PL/SQL Programming Basics"