Oracle Study Notes (3)

In Oracle databases, there is a concept: tablespace. Table spaces are logical components of databases. Physically, database data is stored in data files. Logically, databases are stored inIn a tablespace, a tablespace is composed of one or more data files. The logical structure includes tablespaces, segments, partitions, and blocks. Why is the database so detailed? I think it is mainly to improve database efficiency.Why do you think about it yourself!

Creating a tablespace has the following benefits:
1. Control the disk space occupied by the database (as if the maximum is 500 mb );
2. DBA can deploy different data types to different locations, which improves I/O performance and facilitates management operations such as backup and recovery;

Of course, if you do not want to create a tablespace, you must have the create tablespace permission. Otherwise, you cannot create a tablespace. It is understood that after a good DBA builds a database, it will immediately create a tablespace for ease of management. And now I am justProgramPersonnel, not considering this, but understand a little better. The command line is as follows:

Create tablespace name datafile 'd: \ sp001.dbf '(path name) size (size) 5 m uniform size (minimum region size) 128 K;

If you want to place the created table under a tablespace, you only need to create the table name (... field name tablespace table space name.

 
AlterTablespace name offline -- tablespace offline, so that others cannot access the database
AlterTablespace name online -- online
AlterTablespace nameRead Only -- set tablespace read-only
AlterTablespace nameReadWrite -- set readable and writable tablespaces
 

Next, some very important knowledge, for personal understanding!

Block: as mentioned earlier, it is a logical structure of the database. What is a block ?? To cite others:

Data Block overview Oracle manages the storage space in a database data file in data block ). A data block is the smallest (logical) data unit in a database. The minimum physical storage unit of all data at the operating system level is byte ). Each operating system has a parameter called block size. Each time Oracle acquires data, it always accesses the entire data block (Oracle), instead of accessing data according to the capacity of the operating system block. Link: http://soft.chinabyte.com/database/56/12199056.shtml

In Oracle, we often use it to define a block as allProgramming LanguageOutput hello. World is the first lesson, which is also available in Oracle. The command is as follows:

 
Begin -- indicates that execution starts.
Dbms_output.put_line ('Hello, world!'); -- ExecutedCode
End; -- End execution
 

If you don't see hello, world !, The command is set serveroutput on.

The following example defines a block that reads data from Scott users. The Code is as follows:

 Declare 
 V_enameVarchar2(5); -- Defines two variables for transmitting two values 
 V_salNumber(7,2); 
 Begin 
SelectEname, SalIntoV_ename, v_salFromEMPWhereEmpno= &Empno; -- indicates the employee ID entered by the user. 
 Dbms_output.put_line ('The user name is:'|V_ename| 'Salary:' |V_sal ); 
 End; 

Generally, there is no problem. In fact, this block is very problematic. For example, if the number of employee numbers entered by the user is different in size, the type of the variables defined is different from the data type in the table, these are all problems. See the following statement.

 Declare 
 V_ename EMP. ename%Type; 
 V_sal EMP. Sal%Type; 
 Begin 
 SelectEname, SalIntoV_ename, v_salFromEMPWhereEmpno= &Empno; 
Dbms_output.put_line (v_ename|'Salary:' |V_sal ); 
 Exception -- if an exception exists 
 WhenNo_data_found -- when no data is found 
 ThenDbms_output.put_line ('Error'); 
 End; 

From this command, I did not explicitly define the type of the variable v_ename and v_sal, but used % type, which makes sense, so that the two values have the same type. Second, I have defined how to handle exceptions. WhenIf no data exists, execute the following statement. I will discuss several exceptions later.

Function: Used to return specific data. When a function is created, the return statement must be included in the function header, and the return statement must be included in the function body.It indicates that we have created a function. For example, we write a function or a user's annual salary. Of course, the example returns only one value, which is unique to the famous user. The statement is as follows.

   Create   function  sp_fun1 (spname  varchar2 ) -- defines the function name and function parameters  
   return   Number   is  yearsal  Number  ( 7 ,  2  );  

-- Defines that the type of the returned value is number and the returned value is named yearsal. The types of the two values are the same.
Begin
SelectSal*12 IntoYearsalFromEMPWhereEname=Spname;
ReturnYearsal;
End;
 

How can I call this function ??

   var  yearsal  Number ;  --   define a variable and accept the returned value   
  call sp_fun1 ( '  Scott   ')  into : yearsal;  --   call a stored procedure   
  method called  
  yearsal  
   --   -------   
   36000   --  -returned results  

I have summarized this knowledge today!