The sixth day of learning to go to Oracle



1. Previous section Review
Introduction of 2.pl/sql
The foundation of 3.pl/sql

Understanding Oracle's PL/SQL concepts
Master PL/SQL Programming techniques (including writing processes, functions, triggers, packages ...) ）

What is PL/SQL?
PL/SQL (procedural language/sql) is an extension of Oracle in the standard language, which not only allows
embedded in the SQL language,

Database: Write stored procedures, functions, triggers, using the PL/SQL language, PL/SQL simplifies complexity
Increase the modularity of the program, reduce the cost of network transmission, improve the security, improve the efficiency of the program operation
1. Procedures, functions, triggers are written in PL/SQL
2. Procedures, functions, triggers are in Oracle
3.pl/sql is a very powerful database process language
4. Procedures, functions can be called in a Java program

The traditional way to manipulate databases is to:
Java program sql-"SQL (Database compilation)

Program to have a soul, a programmer's growth is the need for process,
A bad place for PL/sql:
1. Transplant is not good, can only use a single database, not for other databases

What does PL/SQL use to write PL/SQL
PL/SQL Development tools
PL/SQL is a tool provided by Oracle Corporation
Writing stored procedures, inserting records into them
1. Create a simple table
CREATE TABLE mytest (name varchar2, passwd varchar2 (30));

2. Create a stored procedure
Create or replace procedure Sp_pro1 is
Begin
--Executive Section
INSERT into mytest values (' Hoshiyang ', ' m123 ')
End
/
Replace: Indicates that there will be a replacement
How to view error messages:
Show error;

How do I invoke a stored procedure? Two methods
(1) Exec Sp_pro1 (parameter 1, parameter 2, ...) );
(2) Call Sp_pro1 (parameter 1, parameter 2, ...) );

developing a PL/SQL stored procedure using the PL/SQL developer tool
Create or replace procedure Sp_pro2 is
Begin
Delete from mytest where name= ' Hoshiyang ';
End

Stored procedures do not yet have a standard, and each database has its own rules for writing stored procedures

PL/SQL Programming uses variables and logical control statements to write useful functional modules

Simple classification:
|--process
|--function
Block (programming)
|--Trigger
|--Bag

Writing Specifications:
(1) Notes
Single-line Comment--
SELECT * from EMP where empno=7788;--get employee information
Multi-line comments
/*...*/to divide
(2) Naming conventions for symbols
1) When defining variables, we recommend using v_ as a prefix
2) constant, c_ as prefix
3) cursor, _cursor as suffix
4) exceptions, e_ as prefixes, such as E_error

Description of the BLOCK:
Block is the Basic program unit of PL/SQL, and writing a PL/SQL program is actually writing PL/SQL blocks

Block structure diagram:
PL/SQL blocks consist of three parts: the definition section, the execution section, the Exception handling section
As shown below:
Declear
/* Define a section-----Define constants, variables, cursors, exceptions, complex data types */
Begin
/* Execute part-----The PL/SQL statement to execute */
exception
/* Exception Handling section---various errors in handling the operation */
End

Special Note:
The definition section starts with the delete.
This section is optional
The executive section begins with begin.
This section is a must.
The exception handling section starts with exception.
This section is optional

Java Program Results

public static void Main (string[] args)
{
int a = 1;
Try
{
a++;
}
catch (Exception e)
{

}
}

The simplest block:
Set Serveroutput on--open the output process
Begin
Dbms_output.put_line (' Hello World ');
End

The above is the output of ' Hello World ' block, described below:
Dbms_output is a package provided by Oracle (Java-like development package) that contains a number of procedures
Put_Line is a process of dbms_output package.

Declear
V_ename VARCHAR2 (5);--Define string variables
V_sal number (7.2);
Begin
Select Ename to V_ename,v_sal from EMP where empno=&no;
Dbms_output.put_line (' Employee Name ' | | V_ename);
exception
When No_data_found Then
Dbms_output.put_line (' friend, you entered the wrong number! ‘);
End
/
& indicates the parameters to accept console input

Defining sections, performing partial and exception handling sections

Oracle has pre-defined some exceptions, such as No_data_found

Process:
A procedure is used to perform a specific operation, and when the process is established, you can specify either an input parameter or an output parameter, by
Using input parameters, you can pass data to the execution section,
By using the output parameters, you can pass the execution portion of the data to the application environment, and you can use the CREATE PROCEDURE command to establish the process in Sqlplus
Examples are as follows:
(1) Write a process that can enter an employee's name, a new salary, and modify an employee's salary
(2) There are two methods of invoking the procedure:
(3) Calling a stored procedure in a Java program
CREATE PROCEDURE Sp_pro3 (spname varchar2,newsal number) is
--Define the variables section
Begin
--Execute part, revise salary according to user name
Update emp Set sal=newsal where ename = spname;
End
/

Call the above stored procedure:
EXEC Sp_pro3 (' Scott ', 4789);

Calling a stored procedure in a Java program
Write a Java application program to test the invocation of an Oracle stored procedure
Import java.sql.*;
1. Load Driver
Class.forName ("Oracle.jdbc.driver.OracleDriver");
2. Get Connected
Connection ct = drivermanager.getconnection ("");
3. Create CallableStatement
CallableStatement cs = ...

How do I use the procedure return value??

Functions: Functions and procedures are a family, functions are used to return specific data, and when a function is established, the function header must contain a return statement

function Case:
Case: Enter the employee's name and return the employee's annual salary
Create function sp_fun2 (spname carchar2) return
Number is yearsal number (7,2);
Begin
--Executive Section
Select SAL*12+NVL (comm,0) *12 into yearsal from EMP where ename=spname;
return yearsal;
End
Call
1) function call in Sqlpls
Tome number
Call Sp_fun2 (' Scott ') into:abc;
Print ABC
2) Similarly we can call this function in a Java program
Select Annual_income (' SCOTT ') from dual;
The returned result can be obtained by rs.getint (1)

Trigger: A trigger is an implicit execution of a stored procedure. When you define a trigger, you must specify the event of the trigger and the action that is triggered


Package: A package is used to logically combine processes and functions, which consists of package specification and package body two parts
Create package (Declaration):
Create Package Sp_package is
Procedure Update_sal (name varchar2,newsal number);
function Annual_income (name VARCHAR2) return number;
End
/

To implement a package for a package:
Create or Replace package sp_package is
Procedure Update_sal (name varchar2,newsal number)
Is
Begin
Update emp set sal=newsal where Ename=name;
End
function Annual_income (name VARCHAR2)
Return number is
Annual_salary number;
Begin
Select SAL*12+NVL (comm,0) to annual_salary from EMP
where Ename=name;
return annual_salary;
End
End
/

How to call a package, call a procedure and function in a package, call a package name before the procedure and function
exec sp_package.update_sal (' SCOTT ', 120);


PL/SQL Foundation defines and uses variables
Scalar (scalar)-Common type
In PL/SQL
Examples of scalar definitions:
(1) Define a variable-length string
V_ename VARCHAR2 (20)

(2) Use a variable
Data assignment:: =
Declare
C_tax_rate number (3,2): = 0.03;

V_ename VARCHAR2 (5);
V_sal number (7,2);
V_tax_sal number (7,2);
Begin
-Execution
Select Ename,sal to V_ename,v_sal from emp where empno = &no;
--Calculation of income tax
V_tax_sal: = v_sal*c_tax_rate;
--Output
Dbms_output.put_line (name is: ' | | | v_ename | | ' Wages are: ' | | v_sal| | ' Tax: ' | | V_tax_sal);
End

Scalar-Using the%type type:
V_ename emp.ename%type;//represents the size of the variable v_ename defined and the field enamel the table emp is the same size

The compound variable (composite) describes:
Variables for storing multiple values, mainly including:
Structures similar to high-level languages
Type Emp_recode_type is Recode
(
Name Emp.ename%type,
Salary Emp.sal%type,
Title Emp.job%type
);
Defines a PL/SQL record type Emp_recode_type, which contains the type of data name,salary,title
Sp_recode emp_recode_type;//defines a variable sp_recode, which is of type Emp_recode_type

Specific writing:
Declare
Type Emp_recode_type is record (name Emp.ename%type,
Salary Emp.sal%type,
Title Emp.job%type);
Sp_recode Emp_recode_type;
Begin
Select Ename,sal,job to Sp_record from EMP where empno=7788;
Dbms_output.put_line (' Employee Name: ' | | sp_record.name | | ' Wages are ' | | Sp_record.salary);
End

Composite type:
is equivalent to an array in the advanced high-level language, but it is important to note that the subscript of an array in a high-level language cannot be negative, but here is a negative

PL/SQL Table instances:
Declare
--index by binary_integer means that subscripts are integers, positive and negative integers.
Type Sp_table_type is table of Emp.ename%type index by Binary_integer;
Sp_table Sp_table_type;
Begin
Select Ename to Sp_table (0) from EMP where empno=7788;
Dbms_output.put_line (' Employee Name: ' | | sp_table (0));
End

How do I return more than one data type??

Compound variable-Reference variable
Reference variables are variables used to hold numeric pointers, and by using reference variables, you can enable applications to share the same objects, thereby reducing the number of controls that are occupied, in writing
PL/SQL program, you can use a cursor over the reference variable type (ref CURSOR) and object type variable (ref Obj_type)

Reference variable-REF cursor cursor variable
When you use cursors, you do not need to define cursors when

Declare
--Define cursor type Sp_emp_cursor
Type sp_emp_cursor is ref cursor;//defines a cursor
--Define cursor variables
Test_cursor Sp_emp_cursor;
--Defining variables
V_ename Emp.ename%type;
V_sal Emp.sal%type;
Begin
-Execution
--Combine test_cursor with a select, that is, test_cursor points to the result set
Open Test_cursor for select ename,sal from EMP where deptno=&no;
Loop
Fetch test_cursor into v_ename,v_sal;
--Judging the salary level, decide whether to update

--Determine if Test_cursor is empty
Exit when Test_cursor%notfound;
Dbms_output.put_line (' Name: ' | | v_name | | ' Wages: ' | | V_sal);
End Loop;
End

Compound variable-Nested table

Compound variable-composite table

The sixth day of learning to go to Oracle