Database objects-functions, stored procedures, indexes, synonyms

database objects--functions

1, functions (function) are divided into two, one is the Oracle database itself function, the other is the user write their own functions.

2. Use functions to turn lowercase captions into uppercase subtitles.

Select Upper (' A I love you ') from dual;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/57/68/wKioL1SZZ0KB0a83AAAxOlWpd68763.jpg "title=" Qq20141223205800.png "alt=" Wkiol1szz0kb0a83aaaxolwpd68763.jpg "/>

3. Create a function that gets its salary through the employee's ID number

Create or Replace function get_empsal (emp_no in number)

return number

is emp_sal number (7,2);

Begin

Select Empsal

Into Emp_sal

From EMP

where empno = empno;

return (emp_sal);

End

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/82/wKioL1Scuh2QqyKGAAIIhLeGX5E241.jpg "title=" Qq20141226092741.png "alt=" Wkiol1scuh2qqykgaaiihlegx5e241.jpg "/>

Create or REPLACE functions: This is a function that is created or already exists, the function is named Emp_sal, the Emp_no in parentheses is the employee number entered, and the type of input is number (numeric type)

Return Number: The return value of the function is a value of a numeric type;

is emp_sal number (7,2): Defines the parameter emp_sal, the parameter type is number (7,2)

Begin: Begins the program segment of a function

SELECT .... RETURN ... : This is the program segment of the function,

End: The SQL program segment that ends the function, corresponding to the preceding begin

4, the function of the test

Locate the function right-click, select Test function, enter the value to test

5. How to get employee salary through function

Select Get_sal (1944) from dual;

Database objects--Stored procedures

1. Stored procedure (stored procedure): A set of SQL statements created to accomplish a particular function, compiled and stored in a database. The user executes the stored procedure by making a name and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures.

2. Create a stored procedure that deletes employee information based on employee number

CREATE OR REPLACE PROCEDURE delempno (empid in number) is

BEGIN

DELETE from EMP WHERE EMP. EMPNO = EMPID;

COMMIT;

END Delempno;

Explain:

Create OR Replace PROCEDURE: Is the keyword that creates the stored procedure, and if there is a stored procedure with the same name, the stored procedure is replaced, and Delempno is the name of the stored procedure;

Empid in Number:empid is a parameter of the delempno stored procedure, the data type of the parameter is numeric

Begin...end. This is the start and end of the stored procedure keyword, in the middle of the begin...end for the stored procedure specific SQL statements;

3. Execute the stored procedure:

Open a Command window and enter:

Execute Delepmno (1994);

Explanation: Execute: Is the keyword that executes the stored procedure, followed by the name of the stored procedure,

Database Objects--index

1, Index: Is the database table in a column or a number of columns of the value of a search for a result, the equivalent of a book directory, there can speed up the role of the search. Indexes can speed up the query of a database.

2. Create a stored procedure to loop the data into the Test_index table

1) Create a table:

CREATE TABLE test_index (id number,name varchar2 (200));

2) Create a stored procedure:

CREATE OR REPLACE PROCEDURE Insert_data is

TEMP VARCHAR2 (+): = ' Insert Data ';

Begin

For I in 1..1000000 loop

Insert into Test_index (id,name) values (i,temp);

End Loop;

Commit

End

3) Execute the Insert_data stored procedure.

Execute insert_data;

4) Verify the number of data in the table, SELECT COUNT (*) from Test_index;

5) The time required to query the data numbered 9999 requires a full scan when there is no index, which takes a long time to execute.

Full Scan: Here is the computer when querying data, from the last line of 1 all scan once

Indexes are used as directories to speed up queries

6) Create the index of the table,

CREATE INDEX Id_test_index on Test_index (ID); The query time is much less;

Database objects-Synonyms

1. Synonyms (synonym)

Database objects-functions, stored procedures, indexes, synonyms