Oracle —— 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;

-- 语句块
declare
  v_name varchar2(30) := "Jack" --