Teacher Oracle Handout Fifth day

View

What is a view?
A view is also known as a virtual table, which is a fictitious 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 province does not contain any data, it simply contains a query statement that maps to the base table, and the view data changes as the base table data changes.

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
Select statements are 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 then query the diagram later to simplify complex queries
A view is essentially a SELECT statement, so when you access the view, you can access only 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

The user who created the view must have permission to create the view
Authorizing a specified user under the SYSDBA role
Conn sys/a as SYSDBA;
Show user;
Grant CREATE view to Scott;
Conn scott/a;
Show user;

Create a View
Create or Replace view V_emp
As
Select Empno, ename, Sal from EMP;

Desc v_emp;

Drop View v_emp;

SELECT * from V_emp;

Create or Replace view v_emp_2
As
Select Empno No, ename name, Job job, Sal Sal from EMP;

Sql> SELECT * from v_emp_2 where no = 7499;

NO NAME JOB SAL
---------- -------------------- ------------------ ----------
7499 ALLEN Salesman 1600

Sql> SELECT * from v_emp_2 where empno = 7499;
SELECT * from v_emp_2 where empno = 7499
*
An error occurred on line 1th:
ORA-00904: "EMPNO": Invalid identifier

-------------------------------------------------------------------------------------------
Sequence (sequence)
Where to use
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)
)

How to implement the automatic growth of ID fields in MySQL tables in Oracle.
Using sequence sequences is an object in Oracle
Create sequence Seq_user--Creates 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 10 26-Time Square
nomaxvalue--No Limit by default
Cycle | Nocycle--whether reincarnation default Nocycle
Cache num | NoCache--Buffer size default 20

Using sequences
Select Seq_user.nextval from dual;

Select Seq_user.currval from dual;

Build table
CREATE TABLE T_user (ID number, username varchar2, password varchar2, regtime date, constraint Pk_user Primar Y key (id));

INSERT into T_user values (Seq_user.nextval, ' Jack ', ' 123123 ', sysdate);

Drop sequence Seq_user;

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
Insert 5 Piece of data
Build a view (no product initialization sales are displayed in the view)
Query view

Inserting a piece of data from a view

-------------------------------------------------
Stored Procedures
Stored procedures are suitable for update operations, especially for large data volumes

Create a stored procedure
Create or replace procedure Proc1
As | is equivalent to the meaning of declare declaration
ABC VARCHAR2 (100); --The variable scope that defines the stored procedure is in this stored procedure
Begin
Update T_user Set username = ' Lucy ' where id = 3;
End;

For example:
Create or replace procedure Proc1
As
Begin
Update T_user Set username = ' Lucy ' where id = 4;
commit;
End;

Call a stored procedure
exec proc1;

---------------------------
Stored procedure with input parameters
Create
Create or replace procedure proc2 (
param1 varchar2, param2 varchar2 default ' 888888 '--the parameter type of the stored procedure does not need to specify the width (range)
)
As
Begin
INSERT into T_user values (Seq_user.nextval, param1, param2, sysdate);
commit;
End;

Perform
EXEC proc2 (' Tom ');
Call PROC2 (' Mary ');

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

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

---------------------------------------
A stored procedure that returns multiple field values for a query result

Create or replace procedure proc4 (
Param1 in Varchar2, param2 out varchar2, param3 out varchar2--in represents input parameter out represents output parameter Param0 in out number
)
As
param VARCHAR2 (100);
Begin-Note: Only the result set returned for a record can be processed if the result set contains multiple records, Oracle does not provide a way to directly handle
-Cursors must be used indirectly, but the cursor itself is inefficient, so the actual development of the basic does not apply
Select ID, password to param2, param3 from t_user where username = param1;
Param: = ' Who am I? ‘ ;
Dbms_output.put_line (' Query result data: id= ' | | param2 | | ', password = ' | | PARAM3);
Dbms_output.put_line (param);
End;

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 all valid data types 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;

Call PROC_IF (3);

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

I: = 1;
While I<=5 loop
Insert into Tb_zhaozhenlong (rpt_date, Dept_id,item,qty) VALUES (to_date (' 2007-01-02 ', ' yyyy-mm-dd '), ' D ' | | I, ' I ' | | I,round (i*200/3,3));
I: =i+1;
End Loop;

CREATE OR REPLACE PROCEDURE TEST
Is
I int: = 1;
BEGIN
While I < loop
Dbms_output.put_line (i);
i:=i+1;
End LOOP;
END TEST;
Note: I need to declare here, and in the loop body, I should be changed

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

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

-----------------
CREATE OR REPLACE PROCEDURE TEST
Is
BEGIN
For I in 1..100 LOOP
Dbms_output.put_line (i);
End LOOP;
END TEST;
Note: I can not be declared here, and in the loop body, I will be self-increasing

Teacher Oracle Handout Fifth day