Oracle Class Learning 2

1.1. Training Description

use cursors to print information about all employees in the EMP department

Action Steps Answer

Declare

Cursor C_emp is a SELECT * from EMP where deptno=10;

V_row Emp%rowtype;

Begin

Open c_emp;

Loop

Fetch c_emp into V_row;

Exit when C_emp%notfound;

Dbms_output.put_line (v_row.empno| | ': ' | | V_row.ename);

End Loop;

Close c_emp;

End

1.2. Training Description

Enter an age data in the pop-up box, and if the number is greater than the value , throw a custom exception and display a word "this person is too old"

Action Steps Answer

Declare

V_age number:=&nums;

V_exception exception; --Custom Exception V_exception

Begin

If v_age>150 Then

--Throwing exceptions

Raise V_exception;

End If;

exception

When V_exception Then

Dbms_output.put_line (' This person is too old ');

End

 1.3. Training Description

Create a stored procedure with out parameters, calculate the annual salary for the specified employee, and test with the 7788 number of employees

Operation Steps

1. create a stored procedure pro_yearsal
2. Use stored procedures.

Action Steps Answer

1. create a stored procedure pro_yearsal

Create or Replace procedure Pro_emp_sal (V_no number,v_year_sal out number)

Is

Begin

Select sal*12+ NVL (comm,0) to v_year_sal from EMP where empno=v_no;

End

1. Use stored procedures.

Declare

V_sal number (8,2);

Begin

Pro_emp_sal (7369,v_sal);

Dbms_output.put_line (v_sal);

End

describe the relationships between table spaces, users, and tables?

A table space is equivalent to the concept of a MySQL database,Oracle is the allocation table space, and MySQL is the new database, table data, is a table space in which the user is placed, and the table space randomly places the table data in one or more data files.

because Oracle 's database is not a common concept,Oracle manages and stores data by users and tablespaces. But the table is not by the table space to query, but by the user to check . because different users can create tables of the same name in the same table space, they are differentiated by different users.

an Oracle database system can create multiple databases, each of which is called an instance, in which multiple table spaces can be allocated, multiple tables can be created in each table space, and table data in each table space is stored in a data file. To query table data in a table space, you need to specify a user for the table space under this instance, and the user accesses the table in the table space.

4. View

4.1. describe what a view is

A view is a virtual table that does not hold data, and the data source is the original table

Views make complex SQL queries simple, and each view wraps a complex SQL statement

4.2. Create a View

CREATE VIEW name as SELECT statement

Example:CREATE View VIEW_EMP20 as select Empno,ename,job,mgr,hiredate from EMP where deptno=20;

4.3. Working with views

SELECT * FROM view name

Cannot modify the table data in the view, because it is not a real entity table, if you change the data will be disorderly data, resulting in inconsistencies, we created the view as a reference to read the view

CREATE View VIEW_EMP20 as select Empno,ename,job,mgr,hiredate from EMP where deptno=20 with Read only;

5. sequence

5.1. brief description of what sequence

in many data tables there is an operation called an autogrow column, but the Among Oracle, there is no such automatic growth column, which requires manual control by the user, which is primarily for ease of development, and the sequence is provided by the Oracle database to implement the ID self-growth feature.

5.2. Creating a sequence

Syntax:

CREATE SEQUENCE SEQUENCE

[INCREMENT by N]--Each growth n

[Start with N]--initial value, starting with n

For example:create sequence PERSON_SQC;

When the sequence is created, its autogrow requires the user to manually trigger

Nextval: Get the next content of the sequence

Currval: Gets the current value of the sequence

5.3. use of sequences

Insert into person values (person_sqc.nextval, "Zhangsan", 1.null, "fix")

6. Index

6.1.Overview index

An index is a data object used to speed up data access, and the index can be seen as a directory of books that can quickly find what is needed. The index in the database is to raise the query speed (in case of large amount of data).

6.2. creating a single-line index

Create index name on table name (column)

6.3. Creating complex indexes

Create index name on table name (column, column)

6.4. Testing of Indexes

Insert The data in the employee table ,

Declare

Begin

For I in 1..5000000 loop

Insert into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO)

VALUES (Emp_seq.nextval, Emp_seq.currval, ' clerk ', 7902, Sysdate,

Dbms_random.value (800,3000), NULL, 10);

End Loop;

Commit

End

Check all employee information for salary equal to

Without an index we query:

SELECT * from EMP where sal=1500;

Indexing in the Query criteria payroll field

CREATE INDEX Emp_sal_index on EMP (SAL);

to query in the case of an index :

Select * from emp where sal = 1500;

Oracle Class Learning 2