Oracle中常见的一些sql

字段添加注释

comment on table 表名 is 'xxxxxx';
comment on column 表名.字段名 is 'xxx';

唯一索引约束

create unique index 索引名 on 表名(列);

主键约束

alter table 表名 add constraint 约束名 primary key (列) using index 索引名；

创建索引

create index 索引名 on 表名(列);

创建同义词

create public synonym 名 for 表名;

授权

grant select , insert, update, delete on 表名 to 用户;

递归sql

select * from T where 条件1 connect by 条件2 start with 条件3;

其中connect by 与 start with 语句的先后顺序不影响查询的结果， 条件1是根据条件2、条件3选择出来的数据进行过滤，条件2是指定构造树条件，条件3作为搜索起点的条件；

plsql脚本里运用数组

type example_type is record(
c_1 user.T.c1%type,
c_2 user.T.c2%type,
c_3 user.T.c3%type);--创建记录类型
type T_example_table is table of example_type  index by binary_integer;
example_array  example_type ;--后面使用
example_array2  example_type ;--用于初始化

--数组
type example_type is table of user.T.columen%type index by binary_integer;
example_array  example_type ;--后面使用
example_array2  example_type ;--用于初始化

select t.column bulk collect into example_array from T t where .... 

其中bulk collect into 可以将结果一次性地加载到collections中，而不是通过cursor逐条处理，所有into的变量必须是collections，可以在select into，fetch into， returning into语句中使用；

sql里的with .. as

with temp_table as (select * from t1 )select * from temp_table ;

是在内存中建了一张虚表，会话结束，临时表会被清除

行转列

原表：                                                             sql实现 ：

                                 

select p_id as pid ,
       sum(case s_id when 01 then p_num end) as s1_id ,
       sum(case s_id when 02 then p_num end) as s2_id ,
       sum(case s_id when 03 then p_num end) as s3_id 
from T group by p_id order by p_id；

DBMS包的DBMS_SQL

DBMS_SQL系统提供了很多函数及过程，介绍其中使用频率较高的几种：

function  open_cursor：打开一个动态游标，并返回一个整型；

procedure close_cursor(c in out integer)：关闭一个动态游标，参数为open_cursor所打开的游标；

procedure parse(c in integer, statement  in  varchar2 , language_flag in  integer)：对动态游标所提供的sql语句进行解析，参数c表示游标，statement为sql语句，language-flag为解析sql语句所用的oracle版本，一般有V6，V7跟native（在不明白所连database版本时，使用native）；

procedure define_column(c  in integer, position in integer, column any datatype, [column_size in integer]) :定义动态游标所能得到的对应值，其中c为动态游标，position为对应动态sql中的位置（从1开始），column为该值所对应的变量，可以为任何类型，column_size只有在column为定义长度的类型中使用如varchar2，char等；

function execute (c  in  integer) ： 执行游标，并返回处理一个整型，1表示成功，0表示失败，代表处理结束（对insert，delete ，update才有意义，对select语句而言可以忽略）；

function fetch_rows(c  in  integer)：对游标进行循环取数据，并返回一个整数，为0时表示已经取到游标末端；

procedure column_value(c in integer, position in integer , value)：将所取得的游标数据赋值到相应的变量，c为游标，position为位置，value则为对应的变量；

procedure bind_variable(c  in integer, name in varchar2 , value)：定义动态sql语句（DML）中所对应字段的值，c为游标，name为字段名称，value为字段的值；

对于一般的select操作，如果使用动态的sql语句则需要进行以下步骤：

open cursor --> parse  -->define column --> execute --> fetch  rows/column value  --> close cursor;

对于dml操作则需要进行以下步骤：

open cursor --> parse  -->bind_variable--> execute -->  close cursor;

对于ddl操作需要进行以下步骤：

open cursor --> parse  -->  close cursor;