Oracle杂记

Oracle语法

建表

​ create table mytable ( num number, name nvarchar2 );

添加信息

​ insert into mytable ( num, name ) values ( 1,‘张三’ );

修改表

​ update mytable set num=3 where name=‘张三’;

删除表

​ delete from mytable where num=3;

修改表

​ 添加字段：alter table mytable add sex nvarchar(10)

​ 修改字段：alter table mytable modify( sex nvarchar(20) )

​ 删除字段: alter table mytable drop ( sex )

​ 删除表: drop table (mytable)

约束

​ 主键约束: alter table mytable add constraint c_mytable primary key ( num )

​ 唯一约束: alter table mytable add constraint c_mytable unique ( name )

​ 检查约束: alter table mytable add constraint c_mytable check ( sex=‘男’

​ or sex=‘女’ )

​ 外键约束: alter table mytable add constraint c_mytable forign key (num) or

​ reference mytable2 ( num )

视图

​ create view myview as select * from mytable m ,mytable2 m2 where

​ m.num=m2.num;

序列

​ create sequence mysequence

​ start with 1

​ increment by 1

​ nomaxvalue

​ nocycle

​ cache 10

索引

​ B树索引： create index my_index on mytable( num )

​ 位图索引: create index biemap index my_index on mytable ( num )

​ 函数索引: create index my_index on mytable( substr(1,3) )

​ 降序索引: create index my_index on mytable (num desc)

PL/SQL

​ declare

​ begin

​ exception

​ end;

​ /

存储过程

​ cteate or replace procedure my_procedure( v1 in out number )

​ as

​ begin

​ v1:=123;

​ DBMS_OUTPUT.PUT_LINE( V1);

​ end;

​ /

函数

​ create or replace function my_function ( num in mytable.num%type )

​ return number

​ is

​ v1 number;

​ v2 mytable.num%type;

​ begin

​ return v1;

​ end;

​ /

触发器

​ create trigger t_IUD

​ before

​ insert or delete or update

​ on student

​ begin

​ DBMS_OUTPUT.PUT_LINE (‘执行了操作！’);

​ end;

​ /

游标

​ declare

​ cursor c_mycursor

​ is

​ select * feom mytable;

​ c_num c_mycursor;

​ begin

​ for c_num in c_mycursor

​ loop

​ DBMS_OUTPUT.PUT_LINE( c_num.num);

​ end loop;

​ end;

​ /

事务

​ commit; --提交事务

​ insert into mytable ( num ) values ( mysequence.nextval’)–插入数据

​ savepoint spo1; --设置事务回滚点1

​ insert into mytable ( num ) values ( mysequence.nextval’)–插入数据

​ savepoint spo2; --设置事务回滚点2

​ delect from mytable;

​ rollback to spo2; --回滚到事务点2

​ rollback; --回滚到上次提交事务

组合函数

名称	作用	语法
avg	求平均数	avg(字段)
sum	求和	sum(字段)
count	数据统计	count(字段)
Max、Min	最大值、最小值	Max(字段)、Min(最小值)

---

第一篇文章，初入编程，欢迎纠错。