Oracle System Learning Summary

For enterprise-Class large systems, the importance of Oracle databases is self-evident, and as a long-term use of SQL Server developer, it took a little time to learn a series of Oracle courses and summarize them.

1.oracle some basic commands

Sql*plus is a common tool for operating Oracle, some of the following common commands:

(1) Connection database: Conn username/password as Sysdba/sysoper

(2) Close connection: Disc

(3) Change Password: PSSSW, ALTER USER * * * Identified by * * *

(4) Show current users: Show user

(5) Creating User: Create users identified by PWD, deleting users: Drop user [cascade]

2.Oracle tables, data objects, rights management and roles, data dictionary

Oracle data type:

Character type: Char fixed length, maximum 2000 characters; VARCHAR2 variable length, maximum 4000 characters; Clob large object of character type, Max 4G.

Numeric type: Number (N,M)

Date: Date

Permissions and Roles

Schema: Before permission, there is the concept of a scenario where each user establishes a scheme that has the same name as the user name. Oracle's tables, stored procedures, functions, views, triggers, cursors, and so on, are called Data objects, and each user's scenario contains its own data object.

Permissions: The right to execute a specific SQL command or access an object. Oracle includes system permissions, object permissions, and 2 categories.

System permissions: System permissions are the right to execute a specific type of SQL command. It is used to control one or a set of database operations that a user can perform. For example, when a user has CREATE TABLE permissions, a table can be built in its schema, and a table can be built in any scenario when the user has the Create any table permission. Oracle provides more than 100 system permissions. For example, commonly used are: Create session connection Database create table, create view, create procedure build process, function, package, create trigger build trigger; Create Clust ER building clusters and so on.

Show system permissions SQL statement: SELECT * from System_privilege_map order by name;

Grant/Reclaim system permissions:

Grant create session,create table to user*** with admin option – with admin option indicates that the authorized user can continue to grant the appropriate permissions down.

Revoke create Session from user*** (System permissions are not cascade recycled, that is, A->B->C, when a revoke B's permission is, C is not recycled. )

Object permissions: The right to access objects of other scenarios. Commonly used are: Alter, delete, select, INSERT, UPDATE, execute, and so on.

To view object permissions:

Select DISTINCT privilege from Dba_tab_privs---View all object permissions

Select Grantor, owner, TABLE_NAME, privilege from DBA_TAB_PRIVS where grantee = ' AAA '; ---View object permissions for AAA users

Grant/Reclaim Object permissions

Grant Select,delete on TableA to AAA;

Revoke select on TableA from AAA; ---(Cascade recycle)

Role

A role is a collection of related permission commands that are used to simplify rights management. Roles have pre-defined roles and custom role 2 classes.

Predefined roles, for example, the Connect role contains: Alter session, create cluster, CREATE DATABASE link, create session, CREATE table, create VIEW, CRE Ate sequence permissions.

Custom role, create role name identified by password

Add permissions to the role: Grant permission to role name (with admin option).

Assign role to User: Grant role name to User name with admin option.

Data Dictionary

Data dictionary: Records static information for a database, including read-only tables and views, whose owner is a sys user. Includes: User_xxx, All_xxx, dba_xxx 3 types.

User_tables: Displays all tables for the current user, returning only the user's schema table. Select table_name from User_tables.

All_tables: Displays all tables that the user can access, including tables for other scenarios. Select table_name from All_tables.

Dba_tables: Displays all tables for all scenarios, when only the DBA role user or a user with select any table permission can query.

Dba_users: All users; Dba_sys_privs: all system permissions; Dba_tab_privs: All object permissions; Dba_col_privs: All column permissions; Dba_role_privs: All roles.

3.pl/sql programming, packages, stored procedures, functions, cursors, etc.

Package,store procedure, function template:

Package declaration:

Create or Replace package packageaaa is

Procedure Procedureaaa (par1 varchar2, par2 number);

function functionaaa (par1 varchar2);

End

Inclusion

Create or Replace package body PACKAGEAAA is

Procedure Procedureaaa (par1 varchar2, par2 number) is

Begin/* Content * /end;

function functionaaa (par1 varchar2) is

Begin/* content */end;

End

Variables and composite types in a PL/SQL program

1. Scalar type 2. Composite Type 3. Reference type 4.lob (large object)

Scalar type: Also known as common type, the definition syntax is as follows: identifier [constant] datatype [NOT NULL] [: =default value], for example: V_sal2 number (6,2): = 5.4;

Type, which assigns a value of 5.4 by default. Use the%type type, such as V_name Tableaaa.col1%type, to define the V_name variable, which is the same type as TABLEAAA.

Composite types: There are PL/SQL records, PL/SQL tables, nested tables, varray, etc.

type Emp_record_type is record (name Emp.ename%type, salary Emp.sal%type, title emp.job% Type); -- Define PL/SQL record types

Reference variable: A variable used to hold a numeric pointer. By using reference variables, you can make your application share the same objects, thereby reducing the space consumed. When you write a PL/SQL program, you can use both the cursor variable (ref CURSOR) and the object type variable (ref Obj_type) for the reference variable type.

     --Define cursor sp_emp_cursor
        type sp_aaa_cursor is REF CURSOR;
     --Define a cursor variable
        Test_cursor sp_aaa_cursor;
     --Define variables
        V_PAR1 AAA. Col1%type;
        V_PAR2 AAA. Col2%type;
        begin
       --execute
        Open Test_curso R for select Col1,col2 from AAA where col3=&?;
       --loop out
       loop
            FE Tch test_cursor into V_par1,v_par2;
           --Determine if Test_cursor is empty
            exit when Test_cursor%notfound;
            Dbms_output.put_line (' Column1: ' | | v_par1| | ' Column2: ' | | V_PAR2);
       end loop;

PL/SQL Paging stored procedures:

Create or replace package testpackage as
TYPE test_cursor is REF CURSOR;
End Testpackage;

#NAME?
Create or Replace procedure paging
(

TableName in Varchar2,
Pagesize in number,--a page of records
Pagenow in number,
Myrows out number,--total records
Mypagecount out number,--total pages
P_cursor out testpackage.test_cursor--the recordset returned
) is
--Define SQL statement parameters
V_sql VARCHAR2 (1000);
#定义开始和结尾数
V_begin number:= (pageNow-1) *pagesize+1;
V_end number:=pagenow*pagesize;
Begin
--Main logic
V_sql:= ' SELECT * FROM (select t1.*, rownum rn from (SELECT * from ' | | tablename| | ') T1 where rownum<= ' | | v_end| | ') where rn>= ' | | V_begin;
#关联sql和游标
Open p_cursor for V_sql;
--Calculate myrows and Mypagecount
--Organization SQL
V_sql:= ' SELECT COUNT (*) from ' | | TableName;
--Executes the SQL and assigns the returned value to the myrows;
Execute inmediate v_sql into myrows;
--Calculation Mypagecount
If mod (myrows,pagesize) =0 Then
Mypagecount:=myrows/pagesize;
Else
Mypagecount:=myrows/pagesize+1
End If;
--Close cursor
Close p_cursor;
End

Oracle System Learning Summary