Oracle——Sql語法總結

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;