Oracle database Learning views, sequences, and stored procedures

Views (view)
A view is also known as a virtual table, which is a logical representation of a set of data.
The view corresponds to a SELECT statement, and the result set is given a name, which is the name of the view.
The view itself contains no data, it simply contains a query statement that maps to the base table, and the view data changes as the base table data sounds.
After the view is created, you can manipulate the view like an action table, primarily a query.

There are several types of subqueries depending on the view:
The SELECT statement is based on a single table and does not contain any function operations, expressions, or grouping functions, called simple views, where the view is a subset of the base table.
The SELECT statement is based on a single table, but contains a single-line function, an expression, a grouping function, or a GROUP BY clause called a complex view.
The SELECT statement is based on multiple tables, called connection views.

The role of the view:
If you need to perform a complex query frequently, you can build a view based on this complex query, and later query this view to simplify complex queries.
A view is essentially a SELECT statement, so when you access the view you can only access the columns involved in the corresponding SELECT statement, and the other columns in the base table are safe and confidential, restricting access to the data.

Authorization to Scott users (under SYSDBA role)
Grant CREATE view to Scott;

Create a view (the user must have permission to create a view)
Create or Replace view V_emp
As
Select Ename,sal from EMP;

View the View structure
Desc v_emp;

Delete a view
Drop View v_emp;

Query the data in the view:
SELECT * from V_emp;

--------------------------------------------------------------------------------------------
Sequence (sequence)
Where do you use it?
In the MySQL database, you can set the ID field to automatically grow to achieve the insertion of data
CREATE TABLE Mysql_tbl (
ID int primary KEY auto_increment,
Name varchar (100)
)

Automatic growth of ID fields in MySQL tables using sequences in Oracle
A sequence is an object in Oracle
Create sequence seq_user--Creating a sequence of keywords and sequence names
Increment by 1--automatically increases the step size by default 1
Start with 1--Start size value default 1
--maxvalue|minvalue num--Maximum and minimum default nomaxvalue default maximum value is 10^26
nomaxvalue--The default unrestricted maximum value
--cycle|nocycle--is the default nocycle of reincarnation
Cycle
--cache num|nocache--buffer size default 20
Cache 3

Using sequences
Select Seq_user.nextval from dual;

Select Seq_user.currval from dual;

Delete a sequence
Drop sequence Seq_user;

Build table
CREATE TABLE T_user (ID number,username varchar2, password varchar2, regtime date,constraint Pk_user primary KEY ( ID));

INSERT into T_user values (seq_user.nextval, ' Owen ', ' 123123 ', sysdate);

---------------------------------------------------------------------------------------

Need a table t_product
Number primary key auto-grow
Product name is not empty
Product Sales non-null default 0
Product inventory non-null default 0
Initial sales of products

Create a table
CREATE TABLE T_product (
P_no number,
P_name VARCHAR2 (+) NOT NULL,
P_sal_num number Default 0,
P_has_num number Default 0,
P_sal_num_start number,
Constraint Pk_product primary KEY (P_no)
);

Create a sequence
Create sequence seq_product--Creating a sequence of keywords and sequence names

Insert 5 Piece of data
INSERT into t_product values (Seq_product.nextval, ' Apple 5s ', 128,200,110);
INSERT into t_product values (Seq_product.nextval, ' Apple 6 ', 88,150,0);
INSERT into t_product values (Seq_product.nextval, ' Apple 6s ', 98,100,50);
INSERT into t_product values (Seq_product.nextval, ' Apple 6plus ', 128,200,110);
INSERT into t_product values (Seq_product.nextval, ' Apple 7 ', 99,0,99);

Build view (does not show product initialization sales)
Create or Replace view V_product
As
Select P_no,p_name,p_sal_num,p_has_num from T_product;

Query view
SELECT * from V_product;

Inserting a piece of data from a view
INSERT into v_product values (Seq_product.nextval, ' Apple 8 ', 0,0);

------------------------------------------------------------------------------------------

Stored Procedures
Stored procedures are suitable for more operations, especially for updates of large data volumes

Create a stored procedure
Create or replace procedure Proc1
As|is equivalent to the meaning of the Declare statement
ABC VARCHAR2 (100);--A variable that defines the stored procedure, scoped to this stored procedure
Begin
Update T_user set username= ' Old King ' where id=3;
Commit
End
/

For example:
Create or replace procedure Proc1
As
Begin
Update T_user set username= ' Old King ' where id=3;
Commit
End
/
Call a stored procedure
exec proc1;
----------------------------------------------------------------------------------------------
Stored procedure with input parameters
Create a stored procedure
Create or replace procedure proc2 (
param1 varchar2,param2 varchar2--The parameter type of the stored procedure does not need to specify a width (range)
)
As
Begin
INSERT into T_user values (seq_user.nextval,param1,param2,sysdate);
Commit
End
/

Perform
exec proc2 (' Owen ', ' 121212 ');
Call PROC2 (' Haha ', ' 111111 ');--In a DOS window you can

------------------------------------------------------------------------------------------------

Stored procedure with output parameters
Create or replace procedure proc3 (
Param1 in Varchar2,param2 out varchar2--in represents input parameters, out represents output parameters, Param0 in Out number
)
As
Begin
Select Password into param2 from T_user where username=param1;
Dbms_output.put_line (PARAM2);
Commit
End
/

Perform
Set serverout on;
var pp varchar2 (100);
EXEC proc3 (' Tom ',:p p);

------------------------
Query results return stored procedures for multiple fields
Create or replace procedure proc4 (
Param1 in Varchar2,param2 out varchar2,param3 out varchar2--in represents input parameters, out represents output parameters, Param0 in Out number
)
As
begin--Note: There is only one record result set that can be processed here, and if the result set contains multiple records, Oracle does not provide a way to directly handle
-the cursor itself is inefficient because of the need for indirect contact with the cursor, so the actual development is basically not used
Select Id,password into Param2,param3 from T_user where username=param1;
Dbms_output.put_line (' Query result data: id= ' | | param2| | ' Password= ' | | PARAM3);
Commit
End
/

Perform
Set serverout on;
var pp1 varchar2 (100);
var pp2 varchar2 (100);
EXEC proc4 (' Tom ',:p P1,:p p2);
---------------------------------------------------------------------
Defining variables
param1 varchar2 (100);--the type of the variable can be any valid data type of the Oracle system
PARAM2 number;

Assigning values to variables
param1: = ' Who am i! ';
PARAM2: = 123;

Judge
If T_value = 1 Then
Begin
Do ...
End
End If;
-------------------------------------------
Create or Replace procedure proc_if (pp in number)
As
Total number;
Begin
Total: = pp;
If Total <4 Then
Begin
INSERT into T_user values (seq_user.nextval, ' Su-beggar ', ' 666666 ', sysdate);
Commit
End
End If;
End
/

EXEC proc_if (5);

--------------------------------------------

While loop
While T_value = 1 loop
Begin
Do ...
End
End Loop;

Create or replace procedure Proc_while
As
I number;
Begin
I:=1;
While i<100 loop
Dbms_output.put_line (i);
i:=i+1;
End Loop;
End
/

exec proc_while;
-----------------------------------
For y in 1..100 loop
I:=x*y;
Exit when i = 300;
End Loop;

Create or Replace procedure proc_for (pp in number)
As
X number;
I number;
Begin
X:=PP;
For y in 1..100 loop
I:=x*y;
Exit when i>300;
Dbms_output.put_line (i);
End Loop;
End

EXEC proc_for (6);

Oracle database Learning views, sequences, and stored procedures