Oracle —— Sql文法總結，oraclesql文法總結

Oracle —— Sql文法總結，oraclesql文法總結

-- 語句塊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;  -- 綁定變數,執行動態sqldeclare  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 selectSqlopen 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;