oracle基础入门教材

最新推荐文章于 2022-02-19 10:53:07 发布

huihuijing

最新推荐文章于 2022-02-19 10:53:07 发布

阅读量351

点赞数

文章标签： oracle table insert user delete date

建表
create table <table_name>(
column_name1 column_type [not null] [check (expression)]
    [default value] [primary key][references <table_name>(column_name)],
column_name2 column_type [not null] [check (expression)]
    [default value] [primary key][references <table_name>(column_name)]
....
[constraint primary_name
   primary key(column_name1,column_name2...)
]
[unique(<column_name1,column_name2...>)]
                        );

create table <table_name>
as
     select * from <other_table_name> where <condition>
---------------------------------------------------------------------------------------
改表结构
1 alter table <table_name> add(column_name column_type);
2 alter table <table_name> modify(column_naem column_type);
---------------------------------------------------------------------------------------
删表
drop table <table_name>;
truncate table <table_name>;
---------------------------------------------------------------------------------------
表的重命名
rename <table_name1> to <table_name2>;
---------------------------------------------------------------------------------------
插入数据
1 insert into <table_name> (column_name1, column_name2, ....)
    values   (column_value1, column_value2,.... );
2 insert into <table_name>
    values   (column_value1, column_value2,.... );
3 insert into <table_name> (column_name1, column_name2, ....)
      select expression1,expression2, ....
      from   <table_name>
      [where <condition_expression>];
---------------------------------------------------------------------------------------
表的定义与数据插入
create table <table_name> (column_name1, column_name2, ....)
         [ as child_select];
---------------------------------------------------------------------------------------
修改数据
1 update <table_name> [other_name]
set   column_name1= expression1,column2=expression2, ....
[where <condition_expression>];
2 update <table_name> [other_name]
set   column_name1,column2, .... =child_select
[where <condition_expression>];
---------------------------------------------------------------------------------------
删除数据
delete from <table_name>
   [where <condition_expression>];
---------------------------------------------------------------------------------------
数据查询
select [all| distinct] <[table_name.*]|expression1,expression2,...>
from table_name1.* [other_name1],table_name2.* [other_name2],...
[where <condition_expression>]
[connect by <expression> [startwith] <condition_expression> ]
[group by expression1,expression2,....]
[having <condition>]
[{union|intersect|minus} select...]
[
order by {expression1|labor1} [asc|edsc],{expression2|labor2}
[asc|edsc] for update of column_name1,column_name2,...[no wait]
];
---------------------------------------------------------------------------------------
索引
   create [unique] index <index_name> on
                  <table_name> <column_name1,column_name2,...>
   tabspace tabspace_name;

   drop index <index_name>;
---------------------------------------------------------------------------------------
视图
create
or replace view <view_name> [view_column_name]
as   <child_select>
       [with check option;]
    [with read only;]

drop view <view_name>;
---------------------------------------------------------------------------------------
存储过程
   ====================存储过程主要用于处理复杂的业务，而且易于维护
   ====================创建存(储过程/函数/包)的用户必须具有 CREATE PROCEDURE
   ==================== 或 CREATE ANY PROCEDURE 的权限
   create [or replace] procedure <procedure_name>
(<var_name> in/out/inout <var_type>)
   as/is
var_name var_type
begin
    ...
    exception
    ...
end;
   execute procedure_name(value1,value2...);

其中exception中：可用   when <condition> then .....;
                      when <condition1> or <condition2> then .......;
                      when OTHERS then .......;
其中<condition>包括：
condition value sqlcode condition
CURSOR_ALREADY_OPEN (-6511) 试图打开一个已打开的光标
DUP_VAL_ON_INDEX (-1) 唯一索引中的数据重复
INVALID_CURSOR (-1001) 使用未打开的光标
INVALID_NUMBER (-1722) 字符串转换成数据时出错
LOGIN_DENIDE (-1017)
NO_DATE_FOUND (-1403) select语句基于的条件检索数据不存在
NOT_LOGGED_ON (-1012)
PROGRAM_ERROR (-6501)
STORAGE_ERROR (-6500)
STORAGE_ERROR (-0051)
TOO_MANY_ROWS (-1427) 使用隐式光标时一次检测到多行数据
TRANSACTION_BACKED_OUT (-0061)
VALUE_ERROR (-1476) 指定目标域的长度小于待放入其中的数据长度
ZERO_DIVID
OTHERS
EXCEPTION

raise_application_error (<error_code>,<error_text>) 用于返回自定义错误信息
其中error_code 的编码范围在 -20001 到 -20999 之间
    可用 grant procedure on <procedure_name> to <user_object> 赋权
    运行 execute <procedure_name>(<value1>,<value2>,...)
    重新编译：alter procedure <procedure_name> compile;
--------------------------------------------------------------------------------------------
用connect by 遍历家族树
{ 使用connect by的顺序，select ,from   ,where ,start with ,connect by ,order by }
    //where子句将从树中删掉单个节点，但保留它的后代，而connect by 的限定将删除单个节点
    //及其后代。
level 是表示接点层数的伪列（从1开始）
例：
1） select   node_value
from   t_tree
start with node_value='root_nod'
connect by parent_node=prior node_value //遍历节点root_nod 的子节点，不包括
and node_value !='no_use_nod' //node_value 等于'no_use_nod'节点及其子节点
2）
select   node_value
from   t_tree
where node_value !='no_use_nod' //遍历节child_nod的父节点，不包括
start with node_value='child_nod' //node_value 等于'no_use_nod'节点
connect by node_value=prior parent_node
-----------------------------------------------------------------------------------------------
定义函数：
create or replace function <function_name> (<var_name> in/out/inout <var_type>)
return <data_type> {as/is}
   <data_name>   <data_type>;
...
begin
....
exception
end;

---------------------------------------------------------------------------------------
包：
包是集中到单独一单元的一组过程、函数、变量、常量、数据指针、例外列表和sql语句。
dbms_output 包括三个调用函数 put , put_link ,new_link   在使用dbms_output 之前，必须
首先发出命令 set serveroutput on
建立包的定义：
create [or replace] package   <[user].package_name>
{is|as}
<some package specification;>
end <[user].package_name>
建立包的内容：
create [or replace] package body   <[user].package_name>
{is|as}
<some package body specification;>
end <[user].package_name>
或
create [or replace] package body   <[user].package_body_name>
{is|as}
<some package body specification;>
begin
       sql_command; //初始化包的命令
end <[user].package_body_name>

调用方式：<[user].package_name>.<[user].package_body_name>
---------------------------------------------------------------------------------------
数据拷贝：
copy from
[<remote usename>/<remove password>@<connect_string>]
{append | create | insert | replace }
table name
using subquery;
例：
set copycommit 1
set arraysize 1000 //每一千条记录提交一次

copy from t_branch@dhzx -     //???????It is wrong
create t_new_branch -
using -
select * from t_new_branch           //每行中的   - 表示与下一行连接
-------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------
触发器：
触发器的执行对用户来说是透明的。
建立触发器需具有表的alter 或 alter any table 的权限
合法的触发器类型有14种
BEFORE INSERT row BEFORE INSERT statement
AFTER INSERT   row AFTER INSERT   statement
BEFORE UPDATE row BEFORE UPDATE statement
AFTER   UPDATE row AFTER   UPDATE statement
BEFORE DELETE row BEFORE DELETE statement
AFTER   DELETE row AFTER   DELETE statement
INSTEAD OF row INSTEAD OF   statement
创建触发器的语法：
---------------------------------------------------------------------------------------------
   显示光标：declare cursor <cursor_name> is
<sql_command>；
       open <cursor_name>;
       fetch <cursor_name> into var1,var2...;
       close <cursor_name>;
   oracle的隐式游标是sql,所有游标都有四个属性：%NOT FOUND,%FOUND,%ROWCOUNT,%ISOPEN
   这些属性可以存取有关的insert,update,delete,insert into的语句执行信息。
例：游标名%ROWCOUNT,或sql%ISOPEN
----------------------------------------------------------------------------------------
   伪列
   rowid, rownum, user
---------------------------------------------------------------------------------------
pb 端调用：declare <procedure_logic_name> for <procedure_name>
                (:var_1,:var_2,....);
      execute <procedure_logic_name>;
      fetch <procedure_logic_name> INTO :var....;
   CLOSE <procedure_logic_name>;
---------------------------------------------------------------------------------------
同义词
   create synonym <synonym_name>
         for   <object_name>
   create public synonym <public_synonym_name>
            for <schema.object_name>

   drop [public] synonym <syonoym_name>
---------------------------------------------------------------------------------------
序列
   create sequence   [schema.]sequence_name [option] ;

   option:
INCREMENT BY, MINVALUE, NOMINVALUE ,MAXVALUE ,NOMAXVALUE ,
START WITH ,CYCLE ,NOCYCLE ,CACHE ,NOCACHE ,ORDER ,NOORDER

   access: CURRVAL ,NEXTVAL
   调用方式：[schema.]sequence_name.nextval //取下一个序列号
---------------------------------------------------------------------------------------
创建数据库连接：
create [public] database link <dbl_name>
connect to <user_id> identified by <user_password>
using <link_string>;
删除
drop [public] database link <dbl_name>;
调用方式：
<table_name>@<dbl_name>
或建立远程连接的同义词：例： create synonym <table_name> for <table_name>@<dbl_name>

应避免在使用 connect by,start with 和 prior等关键字查询中使用数据库连接，
使用数据连接进行树状结构的查询，多数回失败。
----------------------------------------------------------------------------------------

创建用户
   create user <user_name> {identified by <password>|   externally};
   alter user <user_name> identified by <password>;
---------------------------------------------------------------------------------------
授权与回收
   database:
   grant connect|resouce|dba to <user_name> identified by <password>
                      [with grant/admin option];
   revoke connect|resouce|dba from <user_name>;

   table:
   grant all|alter|delete|index|insert|select on [user_name.]
   <table_name> to <user_name1>[user_name2,..] [with grant option]
   revoke all|alter|delete|index|insert|select on
   <table_name> from <user_name>

   grant {system privilege|role} [,{system privilege|role},...]
      [ on <object>]
       to {user|role} [,{user|role},...]
      [with admin option]

   revoke {system privelege|role} [,{system pribilege|role},...]
       from {user|role} [,{user|role},...]

   privilege include: insert,update,delete,alter,references,all,
                  index,execute,select,read
   publec means:all user such as:
                            grant select on table1 to public;

   create public synonym <synonym_name> for <object>;
---------------------------------------------------------------------------------------
角色：
   create role <role_name>
[not identified| identified [by <password> | externally]];

   alter role <role_name> identified [by <password> | externally]|not identified];

   alter user <user_name> default role {[role1,role2,...]
            [all|all except role1,role2,...] [none]};

   set role <role_name> [identified by <password>];

   set role none;

   drop role <rloe_name>;
---------------------------------------------------------------------------------------
授权制定的列：
   grant <privillege> (colunm_name1,column_name2,...) on <table_name>
       to {user|role} [,{user|role},...]
---------------------------------------------------------------------------------------
块语句：
   BEGIN
   ....
   END
---------------------------------------------------------------------------------------

语法结构
--for
for <condition> loop
...
end loop;

where <condition>
...

--loop1
<command>;
loop
<command>;
if <condition> then
exit;
end if
...
end loop;
--loop2
<command>;
loop
<command>;
exit when <condition>
....
end loop;
--loop3
<command>;
while <condition> loop
....
end loop;
--loop
<command>;
for <var> in const1..constn loop
....
end loop;

--if
--if
if <condition> then
...
else
...
end if
---------------------------------------------------------------------------------------
数据类型
char(size)定长字符型，最大长度为2000，缺省为1
varchar(size)旧的oracle版本中可变长字符串型
varchar2(size)可变长字符串，size最大值为4000
number数值型包括40位空间,又作number(*) 或 smallint
number(size,d)定长数值型
date 有效日期为公元前4712年1月1日到公元后4712年12月31日
long, raw,long raw,mlslable,bolb,clob,nclob,bfile
---------------------------------------------------------------------------------------
比较运算符
   = ,!=,^=,<>,>,<,>=,<=, between..and.. ,in,
   like,is null,is not null,exist,not exist
---------------------------------------------------------------------------------------
通配符
% ,_
---------------------------------------------------------------------------------------
函数
   decode(<condition>,<state1>,<value1>,<state2>,<value2>,...,<value_else>)
   nvl(x1,x2)    //if x1 is not null then return x1 ,else return x2;
   avg,count,max,min,sum,num
   to_date(char_value,chg_type)     cc
                                  yyyy,yyy,yy,y
                                  q //季度
                                  mm,month,mon
                                  ww,w//周
                                  ddd,dd,d,day,dy
                                 am,pm
                                  hh12,hh24
                                  mi
                                  ss
   to_char(date_value,chg_type)
   sign(<number_value>)          //取符号，返回1，0，-1
   abs(n); ceil(n);//上取整   floor(n);// 下取整 mod(m,n); sign(n);
   round(n[,m]);//四舍五入   sqrt(n)
   ascII($); chr(n);//相应ascII码的字符 inicap($);//每个词头大写
   length($); lower($); upper($); instr($1,$2);//$2在$1中出现的第一个位置
   lpad($1,n[,$2]); //$1从左边用$2补至长度n,$2 默认为空格
   ltrim($1,n[,$2]);//同lpad 相反 $1||$2   //字符串连接
   user// 返回当前的用户名
   add_months(date,n);   last_date(day); monts_between(date1,date2);
   next_day(date,char);trunc(date);//截区date中的时间

   is null,is not null //例 where name is null
   decode(expression,value1,result1,value2,result2,....,default_result)
   truncate table table_name;//不可恢复的删除表中所用数据
运算符：
   +,-,*,/,**,<>,!=,^=,<,>,=

   ||,:=,--
外连接
(+)
集合运算
union (交), intersect(并),minus(差)
---------------------------------------------------------------------------------------
提交
   commit;
   set autocommit on;

   alter;audit;connect;create;disconnect;drop;exit;grant;noaudit;quit;
   revake;rename;   //以上命令在执行完后,自动执行commit 命令
回退
   rollback;
   SAVEPOINT point1;
   ROLLBACK TO SAVEPOINT point1;
---------------------------------------------------------------------------------------
使用spool的例子：
set feedback off;
set heading off;
spool yyy.txt
select 'delete from '||table_name||'@dbl_hcss'||';'
   from   user_tables where table_name like 'T_%';
select 'insert into '||table_name||'@dbl_hcss'||' select * from '||table_name||';'
   from   user_tables where table_name like 'T_%';
spool off;
--spool all_table_records.txt
--start yyy.txt
--spool off
---------------------------------------------------------------------------------------
公用视图：
user_source 用户所有对象的源代码
---------------------------------------------------------------------------------------
sqlplus 中接受变量
ACCEPT p_val PROMPT'Please input a branchname:';
用&p_val来引用该变量

huihuijing

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle基础入门教材

建表create table (column_name1 column_type [not null] [check (expression)] [default value] [primary key][references (column_name)],column_name2 column_type [not null] [check (expression)] [defaul
复制链接

扫一扫