Oracle Database (1)

Oracle Basic data types:
Oracle's basic data types are categorized by type: String type, data type, date type, LOB type, and so on.
1. String Type:
①char: fixed-length string that stores up to 2k bytes, and defaults to a length of 1 bytes without specifying a char length.
②ncahr: A fixed-length string containing Uncode format that can store up to 2k of bytes.
③varchar: variable-length string, unlike the char type, does not use Kong FEI padding to maximum length and can store up to 4k bytes of information.
④nvarchar: A variable-length string containing Unicode data, with a maximum storage of 4k bytes.
2. Data type:
①number (precision, number of significant digits).
②integer: Is the subclass of number, which is equivalent to number (38,0), which is used to store integers, which are rounded when the data is inserted or stored with a fractional portion.
③binary_float: is a 32-bit single-precision floating-point data type that can support up to 6-bit precision, and each binary_float value requires 5 bytes including length bytes.
④binary_double: A double-precision floating-point data type with a length of 64 bits, each binary_double value requires 9 bytes including the length.
⑤float (n Precision): Precision indicates the number of data that can be stored, and n can range from 1 to 126.
3. Date Type:
①date: Used to store date and time information, typically occupying 7 bytes of space.
②timestamp: Unlike the date type, timestamp can contain fractional seconds, and the right side of the decimal point can hold up to 9 bits.
③timestamp with time zone type: is a variant of the timestamp that contains the value of the timezone offset.
④timestamp with local time zone type:
⑤interval year to Moth
⑥interval Day to second
4.LOB Type:
①clob Type: Stores data of Unicode type, supports fixed width and variable character sets, Nclob object can store maximum (4 gigabytes-1) * (Database block size)
②blob type: A large object that stores unstructured binary data, which can be understood as a bit stream without character set semantics, is generally a media file.
③bfile type: Binary files, system files stored outside the database, read-only, and the database will be processed by the file as a binary file.
5.RAW & Long RAW Type:
①long Type: Stores variable-length strings, can store up to 2GB bytes of data, storage limit for long type:
Only one column in a table can be of type long.
A long column cannot be defined as a primary key or a unique primary key.
Cannot build an index.
The Long data cannot specify a regular expression.
A function or stored procedure cannot accept parameters of the Long data type.
A long column cannot appear in a WHERE clause or integrity constraint (except for NULL and NOT NULL constraints).
②long Raw type: 2GB of raw binary data can be stored.
③raw type: Used to store binary or string types, variable-length binary data types. You can store up to 2k bytes of information.

PL/sql:

/**declare x Number: = 0;  Cursor CUR1 is a SELECT * from EMP; Cursor CUR2 (ENO number) is a SELECT * from EMP where empno=eno;--parameter cursor Emp_record emp%rowtype;--represents the line type of the EMP table begin open cur1;-     -Open cursor loop--loop FETCH data in cursor fetch CUR1 into Emp_record;     Exit when Cur1%notfound;  --dbms_output.put_line (Emp_record.ename);  End Loop;    Close cur1;--closes the cursor, freeing the memory/*loop x:=x+1;   Dbms_output.put_line (x);  Exit when x>5;  End Loop;    While X<8 loop x:=x+1;  Dbms_output.put_line (x);  End Loop;  For x in reverse 1..6 loop dbms_output.put_line (x);  End Loop;  Select Sal to x from EMP where empno=1;    Exception when Too_many_rows then Dbms_output.put_line (' too many rows ');      When No_data_found and then Dbms_output.put_line (' No data found ');  When others and then Dbms_output.put_line (' others ');  Select Test (7499) from dual;  --implicit cursor--For RD in (SELECT * from emp) loop--Dbms_output.put_line (Rd.ename);  --end Loop;  --Parametric cursor: Open cur2 (7499); loop--loop extracting data from a cursor FETch cur2 into Emp_record;     Exit when Cur2%notfound;  Dbms_output.put_line (Emp_record.ename);  End Loop; Close cur2;end;*/--Stored procedures:/*syntax of the stored procedure: create [or Replace] procedure stored procedure name [(parameter [in | | out] Data type ...)]  {is | as} [Description section] begin executable part [exception error handling section] End [procedure name];beginProc1;End;

 Storage Method:
Create or Replace functionTest (ENOinch  Number)return  Number  is/*parameters of function: In parameter out parameter in out parameter*/Salary Number;begin  SelectSal intoSalary fromEmpwhereEmpno=7499; returnsalary; Exception whenToo_many_rows Then      return -1;  whenNo_data_found Then      return -2;  whenOthers Then  return -3;Endtest; Cursors: 1. Execute select in PL, SQL block,Insert, delete, and UPDATE statements, Oracle assigns it a context (buffer) in memory, which is a pointer to that area, or a workspace, or a structured data type. It provides a method for application equate to separately handle each row of data in a query result set with multiple rows of data, and is a common programming method for an application that designs embedded SQL statements. 2in each user session, multiple cursors can be opened at the same time, with the number of open_cursor parameter definitions in the database initialization parameter file. 3for different SQL statements, cursors are used differently: ① non-query statements: Cursors are implicit, ② results when single-row query statements: Cursors can be implicit or displayed, and ③ results when multiple rows of query statements: cursors are displayed. (1) Display cursor: Step one: The definition of a cursor:CURSORCursor_name[(Parameter[,parameter]...)][RETURN datatype]  isselect_statement; Step Two: Open the cursor:OPENCursor_name[([parameter=>]Value[,[parameter=>]value] ...); Step Three: Extract cursor data:FETCHCursor_name into{Variable_list|record_variable}; Step four: Close the cursor:CLOSEcursor_name; Cursor properties: cursor_name%Found:boolean, True if the last fetch cursor ashamed fetch succeeded, otherwise false; Cursor_name%Notfound:boolean, with cursor_name%found opposite; cursor_name%Isopen:boolean, returns True when the cursor is already open; Cursor_name%ROWCOUNT: A numeric Type property that returns the number of records that have been read from the cursor;

 stored procedures:
Create or Replace procedureProc1 (Enoinch  Number, Salary out Number) isbegin  SelectSal intoSalary fromEmpwhereEmpno=Eno; Dbms_output.put_line (salary);EndProc1;/*How the stored procedure is called: 1. Under the command window: EXEC produre_name (parameter value ...)       Sql> set Serverput on cannot set Serverput sql> set serveroutput on sql> exec proc1;     Procedure successfully completed Sql> 2. Under the SQL window: Begin produre_name (parameter value ...)  End  3. A stored procedure can call another stored procedure. Parameters of the stored procedure: the difference between in and out stored procedures and functions: 1. Stored procedures and functions are stored in the database as named database objects. The advantages that are stored in the database are obvious because the code is not saved locally, and the user can log on to the database on any client computer and invoke or modify the code. 2. Stored procedures and functions, in addition to the result of a difference, is actually the same, the stored procedure does not return results, function to return the results. 3. Stored procedures and functions can be swam by the database to provide security assurances, to use stored procedures and functions, need to have stored procedures and functions of the owner's authorization, only the authorized user or the creator itself can execute the stored procedure or call function. 4. Stored procedures and functions are written to the data dictionary, so stored procedures can be thought of as a common module, a user-written PL/SQL program, or other stored procedures that can call it (but stored procedures and functions cannot invoke the PL/SQL program), a reusable feature. Can be designed as a stored procedure, such as: Display a payroll table, can be designed as a stored procedure; a frequently invoked calculation can be designed as a storage function, and the employee's name can be designed as a storage function based on the employee number. */

Package:

 1. A package is an object used to store the associated program structure, which is stored in a data dictionary. 2. The package consists of two separate parts: Baotou (packages) and inclusion (Packagebody).   Baotou is the title part of the package, is the external operation interface, is visible to the application, the package is the code and implementation of the package, for the application is not visible black box.                      3. Create Package: Header: Create [or replace] package_name [Authid {Current_user|definer}] {is | as}                      [Total data type definition [common data type definition] ...]                      [Common cursor Declaration [Common Cursor declaration] ...]                      [Common variables, bright declarations [common variables, constant declarations] ...]                     [Common subroutine Declaration [Common subroutine declaration] ...] end [package_name]; body: Create Replace package body Package_name Ction Implement procedure implementation ... end package_name Delete package: 1. Remove header: Drop PA Ckage package_name 2. Delete Package body: Drop Pack Body package_name 3. RECOMPILE header: Alter package Package_name compile packages 4. Re- Build package Body: Alter packages package_name Compile pack body*/

 create  or  replace  package pkg1 is  --  Author: not if  --  CREATED:2016/8/6 12:41:11  --  Purpose: /span> function  fn2 (Eno number ) return  varchar2  ;  procedure  proc3 (Eno number ,  SENAME out varchar2  );  end  pkg1; 

Create or ReplacePackage Body PKG1 is   functionFN2 (Eno Number)return varchar2  isSenamevarchar2( -); begin       SelectEname intoSename fromEmpwhereEmpno=Eno; returnSename; Endfn2; procedurePROC3 (Eno Number, Sename outvarchar2) is      begin        SelectEname intoSename fromEmpwhereEmpno=Eno; Endproc3;EndPKG1;

Oracle Database (1)