【Oracle】DDL语言 Sql语法总结

学习Oracle时候总结了以下Oracle中DDL语言Sql语法总结，统一起来分享一下

-- 语句块  
declare  
  v_name varchar2(30) := 'Jack' -- 定义变量  
begin  
  select v_name from dual;  
exception  
  when others then  
    dbms_output.put_line('有异常');  
end;  
  
-- if 判断  
declare  
  v_num1 number;  
  v_num2 number;  
  v_result varchar2(10);  
begin  
  if v_num1 is null or v_num2 is null then  
    v_result := 'Undefined';  
  elsif v_num1 > v_num2 then  
    v_result := 'num1 is big';  
  else  
    v_result := 'num2 is big';  
  end if;  
end  
    
-- case 语句  
declare  
  grade char := 'A';  
  remark varchar2(20);  
begin  
  case grade  
    when 'A' then remark = 'is Excellent';    
    when 'B' then remark = 'is Good';  
  end case;  
end;  
  
-- for 循环  
declare  
  total integer := 0;  
begin  
  for i In 1..19 loop  
    total := total + 1;  
  end loop;  
end;  
  
-- loop 循环  
declare  
  v_count integer := 1;  
begin  
  loop  
    v_count := v_count + 1;  
    if  v_count >= 10 then  
      exit;  
    end if;  
  end loop;  
end;  
  
-- while loop 循环  
declare  
  v_num1 := 10;  
  while v_num1 >1 loop  
    v_num1 := v_num1 + 1;  
  end loop;  
end;  
    
-- 动态sql  
 -- execute immediate 语句  
    
execute immediate dynamic_sql [into {define_variable [,define_variable2]... |recode}]  
  [using [in | out | in out] bind_argument [,[in | out | in out] bind_argument2]...]  
  [{returning | return}] into bind_argument [,bind_argument2]...];  
    
     dynamic_sql: 表示一个sql语句或者pl/sql语句块字符串表达式  
 define_variable: 表示一个存储选择的列的变量值  
          recode: 表示存储在所选行的一个用户定义或%rowtype类型的记录%  
   bind_argument: 输入bind_argument参数是一个表达式，其值会被传递给动态sql语句，输出bind_argument参数，使存储动态sql语句返回值的一个变量  
            into: 在进行单行查询时，指定值被赋值给列的变量或记录，对于查询检索出来的每一个值，into子句都必须有一个与之对应的类型兼容的变量或字段  
        returing: 只能用于DML操作，returning into用于指定值被检索值的变量或记录，每个由DML语句返回值必须在returing into子句中有一个相应类型兼容的变量或字段  
           using: 使用using子句来绑定动态sql语句中的参数，指定in表示只能输入，out表示输出，in out表示参数输入和输出，默认是in  
                  对于DML而言，在returning into子句中放置一个out参数，如果是using子句和return into字句一起使用，则using子句只能包含in参数  
      
  execute immediate 语句只能使用处理单行的数据查询，而不能处理多行数据查询  
    
-- 执行 DDL  
  begin  
    execute immediate 'create table temp_table (id integer, name varchar2(20))';  
  end;  
   
  
declare   
  plsql varchar2(200);  
begin  
  plsql := 'declare systime varchar2(20); ''begin select to_char(sysdate,''dd-mm-yyyy day'') into systime from dual; dbms_output.put_line(''当前日期是：''||systime) end;';  
  execute immediate plsql;  
end;  
    
-- 绑定变量,执行动态sql  
declare  
  plsql varchar2(200);  
  t_name varchar2(20) := 'Jock';  
  t_id integer := '1002';  
begin  
  plsql := 'insert into temp_table values(:1,:2)';  
  execute immediate plsql using t_name, t_id;  
end;  
  
-- pl/sql 异常处理  
declare exception_name  // 定义异常  
raise exception_name  // 触发异常  
exception   // 处理异常  
  when exception_name then  
    statements;  
    
declare  
  temp_ex exception;  
  t_num integer;  
begin  
  select count(id) into t_num from temp_table where id = '1031';  
  if t_num >= 1 then  
    raise temp_ex;  
  end if;  
  DBMS_OUTPUT.PUT_LINE('该用户不存在');  
  exception  
    when temp_ex then  
      DBMS_OUTPUT.PUT_LINE('该用户已经存在');  
end;  
    
-- 声明游标  
cursor cursor_name [{parameter[,parameter]...}] [return return_type] is selectSql  
open cursor_name // 打开游标  
fetch cursor_name into variable_list; // 提取游标  
close cursor_name // 关闭游标  
    
-- 普通游标取值  
declare  
  fname varchar2(20);  
  lname varchar2(20);  
  cursor c_student is select firstname,lastname from student where id = '1001';  
begin  
  open c_student;  
  if c_student%NOTFOUND then  
    dbms_output.put_line('没有找到记录');  
  else  
    fetch c_student into firstname,lastname;  
    dbms_output.put_line(fname||''||lname);  
  end if;  
  close c_student;  
end;  
    
-- loop/while/for 循环取值  
declare  
  fname varchar2(20);  
  lname varchar2(20);  
  cursor t_student is select firstname,lastname from student where id < 1001;  
begin  
  for stus in t_student loop  
    fname := t_student.firstname;  
    lname := t_student.lastname;  
    dbms_output.put_line('姓名：'||fname||''||lname);  
  end loop;   
end;  
  
-- 存储过程  
create proc | procedure pro_name  
    [{@参数数据类型} [=默认值] [output],  
     {@参数数据类型} [=默认值] [output],  
     ....  
    ]  
as  
    SQL_statements   
    
-- 创建无参存储过程  
create or replace procedure showInfo  
  as    
  select * from student  
    
begin   
  showInfo('Jock'); -- 执行存储过程  
end;  
    
-- 创建带参存储过程  
create or replace procedure showInfo (Major in varchar2) as  // 声明一个输入参数  
  select * from student where major = Major;  
  
begin   
  showInfo('Jock'); -- 执行存储过程  
end;  
  
drop showInfo  -- 删除存储过程  
  
-- 函数语法  
create [or replace] function 名称  
  [(参数1 [{in|out|in out} 类型 参数[{in|out|in out} 类型...]]) return 返回类型 {is | as}]  
function _body;  
  
-- 定义函数  
create or replace function getCount(Major in varchar2)  
  return number as f_count number;  // 声明返回类型  
  begin  
    select count(*) into f_count from students where major = 'Magor'  
    return f_count; // 返回return语句  
  end;  
  
-- 使用函数  
declare  
  v_count number;  
begin  
  v_count := getCount('Music');  
  dbms_output.put_line(v_count);  
end;  
  
drop function getCount -- 删除函数  
  
-- 创建包头  
create or replace package emp_package as  
  -- 声明一个存储过程  
  procedure my_proc(  
    lend_nun varchar2;  
    lend_name varchar2;  
    ledn_sex varchar2;  
    major varchar2;  
  );  
end emp_package;  
  
-- 创建包体  
create or replace package body emp_package as  
  -- 存储过程的实现  
  procedure my_proc(  
    lend_num varchar2;  
    lend_name varchar2;  
    lend_sex varchar2;  
    major varchar2;  
  ) is  
  begin  
    insert into emp(lnum,lname,lsex,major) values(lend_num,lend_name,lend_sex,major);  
  end my_proc;  
end emp_package;  
  
-- 调用包  
package_name.type_name;  
  
begin  
  emp_package.my_proc('1001','Jock','male','music');  
end;  
  
-- 定义视图  
create or replace view v_student as select * from student;  
select * from v_student;  // 查询视图  
drop view v_student; // 删除视图  
  
-- 序列  
create sequence seq_name  
[increment by n]  
[start with n]  
[maxvalue n | nomaxvalue] // nomaxvalue：为升序指定最大值为1027，降序最大为-1  
[minvalue n | mominvalue] // nominvalue：为升序指定最小值为1，降序最小为-1026  
  
-- 修改序列  
alter sequence seq_name  
[increment by n]  
[maxvalue n | nomaxvalue]   
[minvalue n | mominvalue]  
  
-- 删除序列  
drop sequence seq_name;  
  
create sequence seq_Id  
  minvalue 1  
  maxvalue 1000  
  start with 1  
  increment by 1  
  cache 20;  
  
-- 数据库链  
create [public] datebase link link_name  
  connect to username identified by password  
  using 'servername / serverurl';  
  
select * from tablename@link_name;  
  
create database link link_goods  
  connect to scott identified scott  
  using '(description = (address_list = (address = (protocol = tcp)(host = 10.0.0.34)(port = 1521)))(connect_data = (service_name = Orcl)))';  
    
select * from goods@link_goods;  
  
-- 索引  
create [unique] index [schema.]index_name on table_name(col_name)  
  [tablespace ts]  
  [storage s]  
  [pctfree pf]  
  [nosort ns]  
  
  schema: 表示Oracle模式，缺省默认当前账户  
  tablespace: 索引存储表空间  
  storage：存储参数  
  pctfree：索引数据块空闲空间的百分比  
  nosort：不排序(存储时已经按照升序排序，无需再排序)  
  
create unique index i_id on student(id);  
  
-- 修改索引  
alter [unique] index index_name  
  [initrans n]  
  [maxtrans n]  
  rebuild  
  [storage<storage>]  
  
  initrans：一个块内同时访问的初始事务的入口数，n为十进制整数  
  maxtrans：一个块内同时访问的最大事务入口数，n为十进制整数  
  rebuild：根据原来的索引结构重新建立索引，即重新对表进行全表扫描以后创建索引数据  
  storage: 存储数据，与create index相同  
    
alter index i_id rebuild storage(initial 1M next 512k)  
  
--删除索引  
drop index schema.index_name;