Oracle PL / SQL Program Manual (SQL Daquan) (transfer)
A, SQLPLUS 1 Introduction SQL command as the beginning of the following 17 statements keywords: alterdroprevoke auditgrantrollback * commit * insertselect commentlockupdate create
SQL not in SQL * PLUS command These commands are not stored in the SQL buffer @ definepause # delquit $ describeremark / disconnectrun acceptdocumentsave append editset breakexitshow btitlegetspool changehelpsqlplus clearhoststart columninputtiming computelistttitle connectnewpageundefine copy - -------- 2 all columns in the base table created by users CATALOG SELECT select SELECT empno, ename, job fromemp; select * fromdept orderbydeptnodesc; logical operator = = or <> > > = < <= ! et slex job fromemp wherejobin ( 'clerk', 'analyst'); selectename, job fromemp wherejobnotin ( 'clerk', 'analyst') , salfromemp wheresalnotbetween2000and3000; predicate like, not like selectename, deptnofromemp whereenamelike'S% '; (beginning with a letter S) selectename, deptnofromemp whereenamelike'% K '; (ending K) selectename, deptnofromemp whereenamelike'W ___'; (beginning with W, only three letter later) selectename, jobfrom EMP aales% '; (which employee's work name is not starting with Sales) nullnull, ISNOT [NULL) No bonus (NULL) selectename, jobfromemp wherecommisnull; selectename, jobfromemp wherecommisnotnull; multi-criteria query selectename, job From Select 5% of the bonus than its salary? Select SELECT [ , Sal, Comm, Comm / Sal From Offem WHERENCOMM>
.05 * SAL Mar-87 Add ? 6-Mar-87 6-Mar-87 15 / (24 * 60 * 60) = alias 6-Mar 87and15secs-column names selectenameemployeefrom emp wheredeptno = 10; (alias: employee) selectename, sal, comm, comm / sal "C / SRATIO" fromemp wherecomm> .05 * sal order display the current SQL command, and put the fourth line as the current line, There is a * behind this line number. changeorc original first time with the new content occurs in a row replacement content SQL> c / (...) / ( 'analyst') / inputori increase in one or more rows Del Del Delete the current line in the SQL buffer running the SQL buffer in the SQL buffer Edit writes commands in the SQL buffer to text files under the operating system, and call the editor provided by the operating system to perform modifications.
------------- 3 @sert dept (dname, deptno) values ( 'accounting', 10); selected insert data from other tables insertintoemp (empno, ename, deptno) selectid, name, department FromOld_emp SQL / PLUS will be for each parameter s when the user is prompted parameters or character data corresponding to the date, the parameter may be added to the quote, without quotation marks insertintodept values can be input (& deptno, '& dname', '& loc'); inserted Null (NULL) Download Data Default Format: DD-MON-YY INSERT EMP (Empno , ename, hiredate) values (7963, 'stone', '07-APR-87'); system time: SYSDATE insertintoemp (empno, ename, hiredate) values (7600, 'kohn' , SYSDATE); data update updateemp setjob = 'manager' whereename =' martin '; updateemp setjob =' marketrep' whereename = 'salesman' ; Update EMP = 40, Job = 'market r j =' salesman '; Data Deletion DELETE Update submission Submit Method @ inesRT, Update, Delete will be submitted immediately. Update cancellation creating a base table DEPTNONUMBER (2), DName JaChar (14), LOCTCHAR (13)); Data Dictionary automatically update. A base table is up to 254 columns. Name name name naming rules: The first character must be a letter, which can be arbitrarily (including ". Name must not exceed 30 characters. The only user's base table name must be unique, can't be the reserved word of Oracle, the column names of the same base table are different. Use Double Quotes If the table name is enclosed in double quotes, the above rules can not be met; Only the use of double quotes can distinguish large, lower-write; Named double quotes, in the future operation, two quotes must also be used.
data type: CHAR (N) Number (N, D) null value processing Sometimes the value of the value Cannot be empty Create_Number (2) Add a list in the base Table ALTERTABLE dept add (headcntnumber (3)); modify an existing column attributes altertabledept modifydnamechar (20); Note: only when all the values in a column is empty, Can reduce its column value width. Only the type of column value can be changed when all values are empty. Only the list as NOT-NULL can only be defined when all values are not spaced. Example s: altertabledeptmodify (locchar (12)); altertabledeptmodifylocchar (12); altertabledeptmodify (dnamechar (13), locchar (12)); create a view createviewmanagersas selectename, job, sal fromemp wherejob = 'manager'; alias names for the view columns createviewmydept (person, title, salary) asselectename, job, sal fromemp wheredeptno = 10; withcheckoption s options use withcheckoption, To ensure that the view is inserted or updated, the data must meet the conditions specified in the select command in the view definition. Update DEPT20 occurs when doing the following setdeptno = 30 whereename = 'ward'; s copy s base table, view createtableemp2 asselect * fromemp; base table, delete the view droptable DROPVIEW ------------ SQL * Plus report function SQL * PLUS Some basic format command DEPTNO DEPTNO enameheadingname columnsalheadingsalary columnsalformat $ 99,999.00 ttitlesamplereportfor | hitechcorp btitlestrictlyconfidential breakondeptno computesum ofsalondeptno run headers and footers ttitlesamplereportfor | hitechcorp btitlerightstrictlyconfidential "|" represents a new line, the end does not have to add a semicolon options There are three types: left center using TTITLE, the system will automatically display the date and page number at the top of each page. TTITLET and BTITLE commands are valid until the header or tail is reset, or exit SQL * Plus.
title the following command to make the statement invalid TTITLEOFF BTITLEOFF column name column defined command is used to display the column names if the name is a word, do not add quotation marks columnenameheadingemployee columnename heading'employee | name' (| linefeed) columnenameformatA15 columnsalformat cancel column format defined columnenameclear column $ 9,999.99 columncommlike SAL \ Like clause makes a column format refers to another column format, it copies the column name and its format selex DEPTNO, ENAME SELECT DEPTNO, ENAME emp orderbydeptno; (ORDERBY clause for controlling BREAK) s shown as 10clark niller 20smith scott 30allen blake only one command from BREAK Role, but once can be used on multiple columns deptno deptno SELECT DEPTNO, ENAME orderbydeptno; two blank spaces clearbreak (canceled BREAK command) between each deptno breakonpage (each time starting a new page) breakonreport (each from a new report start) breakonpageonreport (joint use) breakondeptnoskip2 computesumofsalondeptno grouping calculate calculate each department The sum of salary is separated by the information between the SKIP clause to separate the information between the department MAX MINM MIN STD Once a compute is defined, it has been valid until it shuts down Compute (Clear ) SQL / PLUS environment command show option s (display current parameter settings case) showall (All values are displayed) s s setting parameter values or options set setautocommiton SET switch command comprises setautocommit {off | on | immediate} (automatic submission, OFF default) setecho {off | on} (command file execution, whether the command itself, OFF default in the terminal) setfeedback {off | on} (ON: the end of the query, the results are given, the number of information recording, by default; OFF: no query result, the number of information recording) setheading {off | on} (ON: column header Displayed on the report, default; OFF: Not displayed on the report) The maximum number of characters displayed in a row,
The default is 80.PageSize {n} The number of rows per page, the default is 14 {OFF | ON | text} (ON: When the report exceeds one page, each display screen Suspend the display, wait for the user to return the vehicle button, then display; ? Command buffer, usually, the SQL command buffer has been the current buffer. Since the SQL command buffer can only store a SQL command, it is possible to store SQL commands and SQL * PLUS commands with other buffers. Regularly used settings can be placed in the Login.sql file. The commission of no commissions in department 30 is "NO DATA". set Null is the SQL * Plus command, with it to identify null values (NULL), which can be set to any string. Save command SAVE save file name input 1selectempno, ename, job 2fromemp 3wherejob = 'analyst' saveresearch directory will add a research.sql file. Edit Command EDIT EDIT Edit the content in the current buffer. Edit a file get GET ASEARCH to transfer the file content on the disk into the buffer, and display it on the screen, the file name does not have to add .sql. START command Run the specified file START not only display the query results, but also save the result to file SPOOLFF The result is output on the printer, first deposit them in a file, then do not have to use spool FF, and use: spoolout spoolOut Close this file and outputs the production report on the system default printer \ intoemp (empno, ename, deptno) values (3333, 'samson', 20); spoolnew_emp select * fromemp wheredeptno = 20 ordeptnoisnull spool ------ 6 One letter is changed to uppercase. Jack Smith - jack
substr (job, 1,4); lower upper least other strings taken alphabetical list at the top of a string greatest removed alphabetical list of strings in the last row of a Skewers Date Function Add_MONTH (HIREDATE, 5) Month_between (Sysdate, HiRedate) Calculate the number of months between employment time and system time NEXT_DAY (HIREDATE, 'Friday') NEXT_DAY (HIREDATE, 'FRIDAY') Date of the first Friday after calculating the date of employment SELECT [SESDATE, 'Friday') to_char not function, default date format in ORACLE is' DD_MON_YY' to_char (date, datepicture) selectename, to_char (hiredate, 'DyMondd, yyyy') hired fromemp Date data format DD dy DY DDSPTHTWELFTH MM03 MON MONTHMARCH YY 'Mondd, yyyy' MAR12,1987'MONdd, yyyy' ThursdayMARCH12'DayMONTHdd' Mar1211: 00 am'Monddhh : miam' Thu, thetwelfth'Dy, "the" ddspth' arithmetic function least (v1, v2) selectename, empno, mgr, least (empno, mgr) lownum fromemp WHERE- NG value function NVL (V1, V2) NVL (V1, V2) V1 is column name, if V1 is not a null value, NVL returns its column value. V1 is null, return to V2 value.
Poly group functions minmaxcount be used for any type of data selectmin (ename) fromemp; selectmin (hiredate) fromemp; selectmin (sal) fromemp; number Is there a job? SELECT (JOB) How many different types of work? Select other agglomerations (Digital Data) Calculate average wage SELECT_EMP; STDDEV calculation of the average difference of salary Group Employee of each department SELECT DEPTNO, Count (COUNT) SELECT DEPTNO, COUNT (SAL) * Employees of each department of each department Select DEPTNO, Job, count (*) Conditional group (where is for SELECT, Having is a for groupby) Which departments have more salary and more than 9000 select deptno, sum (sal) Sum (SAL)> 9000; SELECT small junction Remove staff, which department's total salary is more than 8000 SELECT DEPTNO, SUM (SAL) group = 'clerk' groupby havingsum (sal)> 8000 orderbysum (sal); --------- 7 advanced query equivalent join selectempno, ename, job, emp.deptno , dname fromemp, dept whereemp.deptno = dept.deptno; s outer coupling selectename, dept.deptno, loc fromemp, dept whereemp.deptno ( ) = dept. DEPTNO; If there is a value in dept.deptno, there is no in Emp.Deptno (such as deptno = 40), and the result will generate an null value in the result of the results: the same base table Have to do it Connect, you can use the self-join manger.ename, manager.ename = manager.empno; manger s value which is coupled wages of employees belonging to the third level selectename, sal fromemp, salgrade wheregrade = 3 andsalbetweenlosalandhisal; (base table salgrade: grade Losal
Connection set operation s s s set operation line to two or more query results into one union-setunion Rowsoffirstqueryplusofsecondquery, lessduplicaterows intersect-setintersection Rowsbothquerieshaveincommon minus-setdifference rowsuniquetothefirstquery describes several views accountview enamesal Job returned to a query result, combined with records in the base table or view all sectors in which employees earn more than 2000 corresponding column data types must be the same selectename, sal fromaccount wheresal> 2000 union selectename, sal fromresearch wheresal > 2000 union INTERSECTOTOMIBOUNSETOUSETOUSETOUSETOUSETOUNSET The partial record of the second query result is different in the first query results.
What kind of work is in the Terrorist, but not in the sales department? selectjobfromaccount minus selectjobfromsales; subquery slectename, deptno fromemp wheredeptno = (selectdeptno fromemp whereename = 'smith'); multi-level query select select ? > (selectsal fromemp whereename = 'clark'); plurality of base tables and subqueries selectename, job, sal fromemp, dept whereloc = 'new york' andemp.deptno = dept.deptno andsal> (selectsal fromemp whereename = 'scott'); subquery using poly-function selectename, hiredate eMP Register, create a new foundation Table CONNECT, registration, and inquiry Only DBA has the right to create new users dba or user can change the user password? Grant connecttoscott identifiedbyleopard; base table permissions 1 there are two ways to obtain permission for the operation of the base table to create your own base table obtain permission to create user base table of grantselect, insert These privileges select selex SELECT * Creating synonyms Create a synonym EMPLOYEEEE CRE for users Allen Ate_Synym EMPLOYEE alLlen.emp You can make other users have such power, that is, other users can grant your base table authority to others grand scott withgrantoption; recover permissions system permissions recover only the DBA privilege base table can always withdraw revokeinsert onemp fromscott; -------- -9 9 Create About indexes (at least 50 records To establish an index, insert data Establish any number of indexes for a base table Generally, the index is established as the primary key Establish an index, it does not affect the execution of the SQL command. After the index is established, Oracle is automatically maintained and used. Index Ensure that the data uniqueness increases the execution speed, the index can also ensure that each column value in each record is not repetitive. ? If there is, arrange it according to the descending order of salary.
If there are more than two employees pay the same, they are sorted by their name. Select List the percentage of employee names and bonuses accounted for a percentage. Require display time columnnamism clearly, sorted by employee name, excluding an unknown employee. Select Work in Chicago (Department 30) in Chicago (Department 30) All employees' salary rose 10%. updateemp setsal = 1.1 * sal wheredeptno = 30; updateemp setsal = 1.1 * sal wheredeptno = (selectdeptno fromdept whereloc = ' Chicago '); To Hitech newly built a department, numbered 50, and other information are not known. insertintodept (dname, deptno) values ( 'faclities', 50); create a view, three column name, which does not include the employee information createviewemployee ( "employeename", " Employee "" Employee Job ") Make a wage report, including employee name, employment time (press Week calculation), wage and department number, one page shows the information of a department, the end of each page, the sum of the wages of the page, and the sum of the employment time, Time summary, wage calculate according to US dollars, employment time is calculated according to week, and there should be titles per page. ttitle'service' breakondeptnoonpageonreport computesumofsalondeptno computesumofsalonreport computesumof service_lengthondeptno computesumofservice_lengthonreport columnsalformat $ 99,999.00 columnservice_lengthformat9999 selectdeptno, enameemployee, (sysdate-hiredate) / 7 YYYY, the total income includes the total income of employees without bonuses, named alphabetical order.
"Hirepha -date" format "EMPLOYEE", "EMPLOYEE", (SAL NVL (COMM) "EMPLOYEE" , 0) "Compensation", "hiredate" "hirephar Date" Lists more than 7 surrounding countries Name and area. selectnation, area fromnation wherecodein (selectnation_code fromborder groupbynation_code havingcount (*)> 7); lists all equal area larger than Japan The national name and population of the island country. selectnation, population fromnation, border wherecode = nation_code ( ) andnation_codeisnull andarea> = (selectarea fromnation whereupper (nation ) = 'Japan'); List all the borders in other countries, and show their border countries.
breakonnation selectnation1.nation, nation2.nationborderin_country fromnationnation1, border, nationnation2 wherenation1.code = border.nation_code andborder.border_code = nation2. Code ----------- ----------- PL / SQL 2PL / SQL block structure and data type The characteristics of block structure Nested end; No more than 30 characters The first character must be letters Can be arranged, and unparalleled in the case. No SQL reservation word single quote s zero-length string (no characters between the two single quotes), said NULL character: 1 length string of data definition syntax s s identifier [constant> data type [NOT NULL> [: = PL / SQL expressions>; ': =' s represent variables are assigned numeric data types include s number (7,2) character char (120) date type Boolean date Boolean (value True, False or NULL, not stored in the database) notnull: = false; availableboolean; (initial value NULL) % type matching the type books_printednumber (6); books_soldbook_printed% type; manager_nameemp.ename% type; variable Assignment Variable Name: = PL / SQL Expression Numvar: = 5; BOOLV Ar: = true; Datevar: = '11-jun-87'; Null value in digital expression null
President '; eXception ); begin selectempnointov1fromemp wherejob = 'manager'; exception whentoo_many_rowsthen insertintojob_errors values ( 'morethanonemanager '); End; Exception ---- 3SQL and PL / SQL inserted declare my_salnumber (7,2): = 3040.55; my_enamechar (25): = 'wanda'; my_hiredatedate: = '08 -SEP-88 '; begin INSERT in in (Empno, Enmae, Job, HiRedate, Sal, Deptno) VALUES (2741, My_ENAME,' CABDRIVER ', MY_HIREDATE, MY_SAL, 20); End ; s delete declare bad_child_typechar (20): = 'naughty'; begin deletefromsantas_gift_listwhere kid_rating = bad_child_type; end; transaction commit [WORK>; rollback [WORK> ; (optional WORK keyword, but has no effect on command execution) savepoint tag name; (save the current point) marks the current point in the transaction rollback [WORK> to [SAVEPOINT> tag name (Return to the current save point) All the modifications of the database after the savePoint command Keyword Work and SavePoint are optional, execute the command S function has no effect PL / SQL block SQL commands can be used for all functions insertintophonebook (lastname) value (upper (my_lastname)); selectavg (sal) intoavg_salfrom EMP; For non-SQL commands, most individual functions can be used x: = SQRT (Y); LastName: = Upper (lastname); age_diff: = months_between (birthday1, birthday2) / 12; s assignment of data type conversion 4 Species assignment form: s variable name: = expression insertinto yl table values (expression 1, expression 2 ,. ..); UPDATE Number Number Transfer into Char CHAR MTE to CHAR_VAR: = NM_VAR; Digital Conversion Brand Type Date_Var: = '25-DEC-88';
Character conversion into date type Example declare num_jobsnumber (4); begin selectcount (*) intonum_jobsfromauditions whereactorid = && actor_idandcalled_back = 'yes'; ifnum_jobs> 100 then updateactorsetactor_rating = 'wordclass' whereactorid = && actor_id; elsifnum_job = 75then updateactorsetactor_rating =' daytimesoaps' whereactorid = && actor_id ; else updateactorsetactor_rating = 'waiter' whereactorid = && actor_id; endif; endif; commit; end; -------- 5 Circulation End 3): = 0; begin @Sert inToTable1Values ('TastesGreat'); INSERT filling '); CTR: = CTR 1; End ... endloop; declare my_indexchar (20): = 'fettucinialfredo'; bowlchar (20); begin formy_indexinreverse21..30loop INSERT INTOTOTEMP (COLL ) values (my_index); / * cycles from 30 to 21 * / endloop; bowl: = my_index; end; with digits after inreverse must be ascending order, must be Is integer, can not be variables or expressions ---------- notfound% found% rowcount% isopen embodiment fetchmy_curintomy_var; whilemy_cur% foundloop (data processing) fetchmy_curintomy_var; exitwhenmy_cur % rowcount = 10; endloop; % notfound s attribute values as follows: fetch operation returns no record, a record of the value returned true fetch operation, then the value is not on the cursor fetch false Take the value of NULL
Note: If there is no Fetch action,
---------- 8. Pre-defined abnormal situation No Oracle error will automatically generate an exception information some abnormal conditions have been named, such as: no_data_found When SELECT statement is not returned When you record it, it is generated to define a cursor, while the SELECT statement returns a plurality of records. WHENEVER User-defined anomalies By the user's own partial definition: declare declare declare declare x Number; User-defined anomalous case Follow the general scope rule Obtain an exception: Raise_Something_ISNT_Right Note: You can also get predefined exceptions EXCEPTION_INIT statement Allow Oracle errors s call naming format: pragmaexception_init (
product_status column values of 'shipped' and 'ordered' inventory: product_idnumber (6) product_descriptionchar (30) product_statuschar (20) std_shipping_qtynumber (3) contract_item: contract_no number (12) item_nonumber (6) arrival_datedate order: order_idnumber (6) product_idnumber (6) qtynumber (3) answer: declare i_product_idinventory.product_id % type; i_product_descriptioninventory.product_description% type; i_product_statusinventory.product_status% type; i_std_shipping_qtyinventory.std_shipping_qty% type; begin selectproduct_id, product_description, product_status, std_shipping_qty intoi_product_id, i_product_description, i_product_status, i_std_shipping_qty frominventory whereproduct_id = ( selectproduct_id fromcontract_item wherecontract_no = && contractnoanditem_no = && itemno); ifi_product_status = 'shipped'then updatecontract_item SET-ar ar = itex; c tem setarrival_date = add_months (sysdate, 1) whereitem_no = && itemnoandcontract_no = && contractno; else updatecontract_item setarrival_date = add_months (sysdate, 2) whereitem_no = && itemnoand contract_no = && contractno; insertintoorders values (100, i_product_id, i_std_shipping_qty); endif; endif; commit; end; 2: 1. identify all the specified sectors Employee 2. Use the variable with '&' to prompt the user input department number 3. Switch the employee name and wage into the PRNTTable table, the base structure is: Create_TablePlePle (7), LINE 4. Experient situation, the employee information of the bonus is not null value can be deposited in the PRNTTABLE table.
answer: declare cursoremp_curis selectename, sal, comm fromempwheredeptno = & dno; emp_recemp_cur% rowtype; null_commissionexception; begin openemp_cur; FetchEmp_Cur fetchemp_curintoemp_rec; endloop; closeemp_sur; exception whennull_commissionthen openemp_cur; fetchemp_curintoemp_rec; while (emp_cur% found) loop ifemp_rec.commisnotnullthen insertintotempvalues (emp_rec.sal, emp_rec.ename); endif; fetchemp_curintoemp_rec; end loop; closeemp_cur; commit; end; Java Research organization - Copyright 2002-2002
RE: ORACLE database objects and user management (rpm) of: UB Time: 2003-08-14 21:06:59 [modify] [Reply] [Delete] ORACLE database objects and a user management, management of ORACLE database schema objects With the main content of this section is the management and maintenance of the mode objects of the Oracle database, including: tablespace, table, view, index, sequence, synonym, aggregation, and integrity constraints. For each mode object, the definition is described, which describes its function, and finally how to manage them in the SQL language-based instance. 1.1 The tablespace is necessary to maintain it first because the table space is a logical space containing these mode objects. Create a table space sql> createphablespace @ '/ usr / usr / oracle / dbs / jxzy.dbf'> online; modified table space sql> altertablespace jxzy @tablesNormal; SQL> AltertableSpace / usr / oracle / dbs / jxzy.dbf '> to' / usr / oracle / dbs / jxzynew.dbf '> Online SQL> CREATETABLESPACE > DROPTABLESPACE JXZY> INCLUDINGTENTENTS 1. 图片 2 The table maintenance table is the basic unit of data storage in the database, and a table contains several columns, each column name, type, length, etc. The establishment of the table sql> createphable (> office_num NOT / NULL); SQL> Altertable SQL> Alter_Table cascade constraints // Delete The integrity constraints of other tables of the table are referenced 1. 3 The view maintenance view is a collection of data generated by one or several base meters, but the view does not account for storage. Create a view to protect data security (only some of the ranks that you can see), simplify the independence of the query operation and protect the data. View of the view sql> create (selectpole_path_num 安 ASPATH, WELL asSupport_Device asFrom: SQL> DROPVIEW; 1.4 sequence maintenance sequence is the unique integer generated by the sequence generator.
Establishing sequence SQL> CREATESEQUENCEjxzy.sequence_cable> STARTWITH1> INCREMENTBY1> NO_MAXVALUE; established sequence, jxzy.sequence_cable.currval returns the current value, jxzy.sequence_cable.nextval return current The new value of the value after 1 is added. SQL> alter_cable> Start_1 // The starting point cannot be modified, if you modify, you should delete, then redefine> IncTement 2> MaxValue_1000; Deletion of the Sequence SQL> DROPSQUENCE sSEquence_cable 1. 5 5 The index maintenance index is a structure associated with the table, which is established to improve the speed of the data. Therefore, in order to increase the index speed on the table, one or more indexes can be created on the table, and an index can be built on one or more columns. For the query type, establish multiple indexes, greatly improve the query speed, the updated table, if the index is too large, will increase the overhead. Index unique index and unique index index creation SQL> Create ipspace (switch_name)> TableSpace \ (office_num, switch_name> TABLESPACE jxzy; Index Delete SQL> DROPINDEX jxzy.idx_switch; 1. 6 完 完 约 约 约 管理 管理 管理 正 正 正 正 正 性 性 性 性 性 性 性 性; Data Type Check Prevents data in the database from doing semantics. Integrity constraints are defined a set of rules description methods for columns.
ORACLE provides the following integrity constraint. A.NOTNULL b.UNIQUE non-null primary key unique key c.PRIMATYKEY a table can have a non-empty outer d.FOREIGAKEY The key E.CHECK NN_DESC not NN_DESC not NN_DESC SQL> altertable SQL> altertable / witch (switch_code u ((8)> constraint NUMBER (8),> office_numnumber (3)> CONSTRAINTfk_officenum> REFERENCESoffice_organization (office_num)> ONDELETECASCADE); define inspection SQL> CREATETABLEoffice_organization (> office_numNUMBER (3) ,> CHECK 二 二 9 数据 数据 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户 用户management system is configured with a good security mechanism 2.1ORACLE database security policy to establish system-level security guarantees system-level privileges is achieved by granting user rights system-level, system-level rights (the system privileges) includes: establishing Table space, build users, modify users' rights, delete users, etc. System privileges can be granted to users, or they can be recycled at any time. Oracle system privileges 8 More than 0. Establishing an object-level security guarantee Object-level privilege is achieved by granting the user's right to operate (query, increasing, deleting), etc. in the database. Establish user-level security guarantee User-level security guarantee through user passwords and role mechanisms (a set of rights). The purpose of introducing role mechanism is to simplify authorization and management of users. The practice is to packet the user according to its function, establish a role for each user, and assign the role to the user, and the same role has the same privilege. Content 2.2 User Management ORACLE user management including user creation, modification and deletion of users to establish SQL> CREATEUSERjxzy> IDENTIFIEDBYjxzy_password> DEFAULTTABLESPACEsystem> QUATA5MONsystem; sql> create sql> DROPUSER JXZYCASCADE; simultaneously delete its established entity 2.3 System privilege management and control Oracle offer more than 80 system privileges, each of which allows users to perform one or a type of database operation.
Grant system privilege SQL> grantecreate From_JXZY_NEW / / But there is no level recipient function Display the system privilege (system-level privilege for a user) sql> select * from d d_sys_privs 2.4 d 特 特 管理 管理 管理 管理 管理 管理 管理 管理 管理The right to perform special operations on the table. These special operations include increasing, deleting, changing, viewing, executing (stored procedures), reference (other table fields as foreign bonds), index, and more. Grant Object Privileges SQL> GRANTSELECT, INSERT (office_num, office_name),> UPDATE (desc) ONoffice_organization> TOnew_adminidtrator> WITHGRANTOPTION; // cascade authorized SQL> GRANTALLONoffice_organization > TOnew_administrator recycling object privileges SQL> REVOKEUPDATEONoffice_orgaization> FROMnew_administrator // cascade recycling function SQL> REVOKEALLONoffice_organization> FROMnew_administrator show all objects have been granted privileges SQL> Select * from 5Sys.dba_tab_privs 2.5 Role Management Oracle's role is named related privilege group (including system privileges and object privileges), Oracle uses it to simplify privilege management, you can grant it to users or other roles. The Oracle database system pre-defines Connect, Resource, DBA, Connect has privileges for creating tables, views, sequences; resource has privileges, triggers, tables, sequences, etc., DBA has all system privileges; Exp_Full_Database, You can understand the rights owned for each role by query sys.dba_sys_privs. Granted to user roles SQL> GRANTDBATOnew_administractor> WITHGRANTOPTION; Author: UB Time: 2003-08-14 21:06:59
[modify]
[Reply]
[delete]
Oracle Database Objects and User Management
One,
Management and Maintenance of the Oracle Database Mode Object
The main content of this section is about
The management and maintenance of the mode object of the Oracle database, including: tablespace, table, view, index, sequence, synonym, aggregation, and integrity constraints. For each mode object, first describe its definition, indicating its function, and finally based on
An instance of the SQL language shows how to manage them in maintenance.
1.1 table space
Since the tablespace is a logical space containing these mode objects, it is necessary to maintain it first.
Create a table space
SQL> CreatephableSpace jxzy
> Datafile '/ usr /
Oracle / DBS / JXZY.DBF '
> Online;
Modify table space
SQL> Alterphablespace f
SQL> altertablespace jxzy
> Rename Datafiler '/ usr /
Oracle / DBS / JXZY.DBF '> TO ' / usr /
Oracle / DBS / JXZYNEW.DBF '
> Online
SQL> CreatephableSpace online
Delete table space
SQL> DROPTABLESPACE JXZY
> Incruding Contents
1. 图 2 maintenance
The table is the basic unit of the data store in the database, and a table contains several columns, each column name, type, length, etc.
Table establishment
SQL> CreatephableTable jxzy.switch (
> Office_num
> Switch_code Number (8, 0)
> Switch_name
Table modification
SQL> altertable jxzy.switch
> Add (desc varchar2 (30));
Table deletion
SQL> DROPTABLE jxzy.switch
> Cascade constraints
// Remove the integrity constraints of other tables that reference the table
1. 3 视 maintenance
The view is a collection of data generated by one or several base meters, but the view does not account for storage. Create a view to protect data security (only some of the ranks that you can see), simplify the independence of the query operation and protect the data.
View of view
SQL> Create_View jxzy.pole_well_view
> (Selectpole_path_num asasPath,
Pole asDevice_num FromPole
> Union
> Selectpipe_path_num ωpath,
> dervice_num from well
View replacement
SQL> Replace_Well_View ωView
> (Selectpole_path_num asasPath,
Pole asSupport_Device frompole
> Union
> Selectpipe_path_num ωpath,
Well asSupport_Device from well);
Remove of view
SQL> DROPVIEW jxzy.pole_well_view;
1.4 sequence maintenance
The sequence is the unique integer generated by the sequence generator.
Sequence establishment
SQL> Create_Sequence jxzy.sequence_cable
> Start_with
> Increment 1
> NO_MAXVALUE;
Create a sequence, jxzy.sequence_cable.currval returns the current value, jxzy.sequence_cable.nextval return the current value after 1
Sequence modification
SQL> Alter SEquerncejxzy.sequence_cable
> Start // The starting point cannot be modified, if you modify, delete first, then redefine
> Incote 2
> MaxValue1000;
Deletion of the sequence
SQL> DROP SEQUENCE JXZY.SEQUENCE_CABLE
1. 5 5 号 Index Maintenance
The index is a structure associated with the table, which is established to improve the retrieval speed of the data. Therefore, in order to increase the index speed on the table, one or more indexes can be created on the table, and an index can be built on one or more columns.
For the query type, establish multiple indexes, greatly improve the query speed, the updated table, if the index is too large, will increase the overhead.
Index unique index and unique index index
SQL> create index jxzy.idx_switch
> ON SWITCH (Switch_name)
> TABLESPACE JXZY;
Modification of index
SQL> Alter oddex jxzy.idx_switch
> ON SWITCH (Office_num, Switch_name)
> TABLESPACE JXZY;
Delete of index
SQL> DROP j.idx_switch;
1. 6 完 约 约束
The integrity of database data refers to the correctness and compatibility of data. Data Type Check Prevents data in the database from doing semantics.
Integrity constraints are defined a set of rules description methods for columns.
Oracle provides the following integrity constraints.
A. Null
B.
C.Primaty Key Key a table can only have one, non-empty
D.ForeiGa key
E.CHECK Each line of the table must be true or unknown (for null)
E.g:
A column defines non-empty constraints
SQL> altertable / office_organization
> Modify (Descvarchar2 (20)
> Constraint nn_desc NOT otNULL)
Some column definition unique keyword
SQL> altertable / office_organization
> Modify (office_name vatchar2 (20)
> Constraint qQ_officename @Unique)
Define primary key constraints, primary key requirements are not empty
SQL> CreatephableTable [Switch_code nMBER (8)
> Constraint pk_switchcode primary KEY,)
Make the primary key constraint
SQL> altertable switch
Key foreign bond
SQL> CreatephableTablepole (pole_code Number (8),
> Office_num Number (3)
> Constraint Fk_officenum
> References office_organization (office_num)
> ONELETE cascade;
Definition check
SQL> CreatephableTable / over_organization
> Office_num Number (3),
> Constraint CHECK_OFFICENUM
> Check (office_num between → 199);
two,
Oracle Database User and Permission Management
Oracle is a multi-user system that allows many users to share system resources. In order to ensure the security of the database system, the database management system is configured with a good security mechanism.
2.
Oracle Database Security Policy
Establish a system-level security guarantee
System-level privileges are implemented by granting user system-level rights, and system-level rights (system privileges) include: establishment of tablespace, establishing users, modifying users, and deleting users. System privileges can grant users or recycled at any time.
There are more than 80 privileges privileged in Oracle system.
Establish an object-level security guarantee
Object-level privileges are implemented by granting users to operate (query, increasing, and deleting) rights of specific tables, views, sequences, etc. in the database.
Establish user-level security guarantee
User-level security guarantees through user passwords and role mechanisms (a set of rights). The purpose of introducing role mechanism is to simplify authorization and management of users. The practice is to packet the user according to its function, establish a role for each user, and assign the role to the user, and the same role has the same privilege. 2.2 User Management
Oracle user management content mainly includes user establishment, modification, and deletion
User's establishment
SQL> CREATE jxzy
> Identified @jxzy_password
> DefaultTablesPace 日System
> Quata 5m
User's modification
SQL> CREATE jxzy
> Identified @ @ @ oddy_pw
> Quata 10m
Delete users and their own objects
SQL> DROP
2.3 System privilege management and control
Oracle provides more than 80 system privileges, each of which allows users to perform one or a type of database operation.
Grant system privilege
SQL> Grant Create_User, alter u u u u u
> TO JXZY_NEW
ω;
Recycling system privilege
SQL> Revoke-iterative r c u u>
> From JXZY_NEW
/ / Without cascading
Display system privileges (system-level privileges for a user)
SQL> SELECT * FROMS.DBA_SYS_PRIVS
2.4 Object Privilege Management and Control
Oracle object privileges refers to the right to conduct special operations on the specified table. These special operations include increasing, deleting, changing, viewing, executing (stored procedures), reference (other table fields as foreign bonds), index, and more.
Grant the subject privilege
SQL> grant Select, INSERT (Office_num, office_name),
> Update (DESC) ON / Office_organization
> To @New_AdminIDTrator
> With
// Cascade authorization
SQL> grant
> TO nw_administrator
Recycling object privilege
SQL> Revoke UPDATE 中 ffice_orgaization
> From nw_administrator
// Claced recipient function
SQL> Revoke ImageAll
> From nw_administrator
Display all object privileges have been granted
SQL> SELECT * FROMS.DBA_TAB_PRIVS
2.5 Role Management
Oracle's role is a naming related privilege group (including system privileges and object privileges),
Oracle uses it to simplify privilege management, grant it to users or other roles.
The Oracle database system pre-defines Connect, Resource, DBA, Connect has privileges for creating tables, views, sequences; resource has privileges, triggers, tables, sequences, etc., DBA has all system privileges; Exp_Full_Database,
You can understand the rights owned for each role by query sys.dba_sys_privs.
Grant user role
SQL> GRANT TONEW_ADMINISTRACTOR
> With If this paper involves copyright issues, 9CBS does not assume relevant responsibilities, please contact the copyright owner directly with the article Author.
Published on August 17, 2004 1:01 PM
HREF = "http://blog.9cbs.net/kenly_zhang/services/pingback.aspx" Rel = "pingback" />
