The sixth day of oracle learning and the sixth day of oracle Learning

The sixth day of oracle learning and the sixth day of oracle Learning
Zookeeper

1. Review in the previous section
2. PL/SQL Introduction
3. Basic PL/SQL

Understand oracle pl/SQL concepts
Master PL/SQL programming technology (including programming process, functions, triggers, packages ...)

What is PL/SQL?
PL/SQL (procedural language/SQL) is an extension of oracle in the standard SQL language, PL/SQL not only allow
Embedded SQL language,

Database: Write stored procedures, functions, and triggers. the PL/SQL language is used. PL/SQL simplifies the complexity.
Increase program modularization, reduce network transmission overhead, improve security, and improve program running efficiency
1. Procedures, functions, and triggers are written in PL/SQL.
2. process, function, and trigger are in oracle
3. PL/SQL is a very powerful database process Language
4. process. functions can be called in java programs.

The traditional database operation method is as follows:
Java program SQL-"SQL (database compilation)

A program must have a soul. The growth of a programmer requires a process,
Poor PL/SQL:
1. Poor portability. You can only use a single database, but not other databases.

What is PL/SQL used to compile PL/SQL?
PL/SQL development tools
PL/SQL is a tool provided by oracle.
Write a stored procedure and insert records into it
1. Create a simple table
Create table mytest (name varchar2 (20), passwd varchar2 (30 ));
 
2. Create a stored procedure
Create or replace procedure sp_pro1 is
Begin
-- Execution part
Insert into mytest values ('He Shiyang ', 'm123 ')
End;
/
Replace: replace if any.
How to view error information:
Show error;
 
How do I call a stored procedure? Two methods
(1) exec sp_pro1 (parameter 1, parameter 2 ,...);
(2) call sp_pro1 (parameter 1, parameter 2 ,...);
 
Use the PL/SQL developer tool to develop PL/SQL stored procedures
Create or replace procedure sp_pro2 is
Begin
Delete from mytest where name = 'he Shiyang ';
End;
 
There is no standard for stored procedures. Each database has its own Stored Procedure writing rules.
 
PL/SQL programming uses variables and logical control statements to write useful functional modules
 
Simple classification:
| -- Process
| -- Function
Block (programming)
| -- Trigger
| -- Package

Writing specifications:
(1) Notes
Single line comment --
Select * from emp where empno = 7788; -- get employee information
Multi-line comment
/*... */
(2) symbol naming rules
1) when defining a variable, we recommend that you use v _ as the prefix.
2) constant, c _ as the prefix
3) cursor, _ cursor as the suffix
4) Exceptions. e _ is used as the prefix, for example, e_error.
 
Block introduction:
Blocks are the basic program units of PL/SQL. Compiling PL/SQL programs is actually writing PL/SQL blocks.
 
Block Structure:
A pl/SQL block consists of three parts: the definition part, the execution part, and the exception handling part.
As follows:
Declear
/* Definition section ----- define constants, variables, cursors, exceptions, and complex data types */
Begin
/* Execution part ----- PL/SQL statement to be executed and SQL statement */
Exception
/* Exception Handling part --- handling various running errors */
End;
 
Note:
The definition starts with delete.
This part is optional
The execution part starts from begin.
This part is required
Exception Handling starts with exception.
This part is optional
 
Java program result
 
Public static void main (String [] args)
{
Int a = 1;
Try
{
A ++;
}
Catch (Exception e)
{

}
}
 
Simplest block:
Set serveroutput on -- open the output process
Begin
Dbms_output.put_line ('Hello World ');
End;
 
The above is the block that outputs 'Hello world', which is described as follows:
Dbms_output is a java-like package provided by oracle. This package contains some processes,
Put_line is a process of the dbms_output package.
 
Declear
V_ename varchar2 (5); -- defines string variables
V_sal number (7.2 );
Begin
Select ename into 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, your number is incorrect! ');
End;
/
& Indicates that you want to accept parameters entered in the console
 
Definition, execution, and exception
 
Oracle predefines some exceptions, such as no_data_found
 
Process:
A process is used to perform a specific operation. When a process is created, you can specify either an input parameter or an output parameter.
You can use input parameters to pass data to the execution part,
You can use the output parameters to transfer the executed data to the application environment. In sqlplus, you can use the create procedure command to create a process.
Example:
(1) Write a process where you can enter the employee name, new salary, and modify the employee's salary.
(2) There are two methods to call the process:
(3) call a stored procedure in a java program
Create procedure sp_pro3 (spName varchar2, newSal number) is
-- Define variable
Begin
-- Run the command to modify the salary according to the user name.
Update emp set sal = newSal where ename = spName;
End;
/
 
Call the above stored procedure:
Exec sp_pro3 ('Scott, 4789 );
 
Java program calls a stored procedure
// Compile a java application program to test the oracle stored procedure call
Import java. SQL .*;
1. Load the driver
Class. forName ("oracle. jdbc. driver. OracleDriver ");
2. Get the connection
Connection ct = DriverManager. getConnection ("");
3. Create CallableStatement
CallableStatement cs =...
 
? How to use the process return value ??
 
Function: a function and a process are used to return specific data. When a function is created, the return statement must be included in the function header.
 
Function case:
Case: Enter the employee name and return the employee's annual salary.
Create function sp_fun2 (spName carchar2) return
Number is yearSal number (7, 2 );
Begin
-- Execution part
Select sal * 12 + nvl (comm, 0) * 12 into yearSal from emp where ename = spName;
Return yearSal;
End;
Call
1) Call functions 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;
You can use rs. getInt (1) to obtain the returned result.
 
Trigger: A trigger is a stored procedure that is implicitly executed. When defining a trigger, you must specify the trigger event and trigger operation.
 
 
Package: a package is used to logically combine processes and functions. It consists of the package specification and package body.
Create a package (Declaration ):
Create package sp_package is
Procedure update_sal (name varchar2, newsal number );
Function annual_income (name varchar2) return number;
End;
/
 
Implement the package body for the 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) into annual_salary from emp
Where ename = name;
Return annual_salary;
End;
End;
/
 
How to call a package, call the process and function in the package, and add the package name in front of the process and function when calling the package
Exec sp_package.update_sal ('Scott, 120 );
 
 
PL/SQL basic definition and use variables
Scalar
In PL/SQL
Scalar definition case:
(1) define a variable-length string
V_ename varchar2 (20)
 
(2) Use a variable
Data assignment: =
Declare
C_tax_rate number (0.03): =;
 
V_ename varchar2 (5 );
V_sal number (7,2 );
V_tax_sal number (7,2 );
Begin
-- Execute
Select ename, sal into v_ename, v_sal from emp where empno = & no;
-- Calculate income tax
V_tax_sal: = v_sal * c_tax_rate;
-- Output
Dbms_output.put_line (Name: ''| v_ename | 'salary: '| v_sal | 'tax:' | v_tax_sal );
End;
 
Scalar-Use % type:
V_ename emp. ename % type; // indicates that the value defined by the variable v_ename is the same as the value of enamel in the table emp.
 
Composite:
Variables used to store multiple values include:
Structures similar to advanced 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, such as emp_recode_type, which contains the data name, salary, and title.
Sp_recode emp_recode_type; // defines the sp_recode variable. Its type is emp_recode_type.
 
Specific Compilation:
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 into sp_record from emp where empno = 7788;
Dbms_output.put_line ('employee name: '| sp_record.name | 'salary is' | sp_record.salary );
End;
 
Composite Type:
It is equivalent to an array in advanced languages, but note that in advanced languages, the subscript of an array cannot be negative, but it can be negative.
 
PL/SQL table instance:
Declare
-- Index by binary_integer indicates that the subscript is an integer. Both positive integers and negative integers are allowed.
Type sp_table_type is table of emp. ename % type index by binary_integer;
Sp_table sp_table_type;
Begin
Select nameinto sp_table (0) from emp where empno = 7788;
Dbms_output.put_line ('employee name: '| sp_table (0 ));
End;
 
? How to return multiple data types ??
 
Composite variable-reference variable
A reference variable is a variable used to store numeric pointers. By using a reference variable, the application can share the same object, thus reducing the usage of the control.
For PL/SQL programs, you can use the cursor type (ref cursor) and object type variable (ref obj_type ).
 
Reference variable-ref cursor variable
When using a cursor, you do not need to define the cursor
 
Declare
-- Define the cursor type sp_emp_cursor
Type sp_emp_cursor is ref cursor; // defines a cursor
-- Define the cursor variable
Test_cursor sp_emp_cursor;
-- Define variables
V_ename emp. ename % type;
V_sal emp. sal % type;
Begin
-- Execute
-- Combines test_cursor with a select statement, 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;
-- Judge the salary level and decide whether to update
 
-- Determine whether test_cursor is null
Exit when test_cursor % notfound;
Dbms_output.put_line ('name: '| v_name | 'salary:' | v_sal );
End loop;
End;
 
Composite variable-nested table
 
Composite variable-composite table