/* 授權命令 grant 權限 to 用戶 授權可以授予多個權限 grant connect,resource to baidu 識訓權限 revoke 權限 from 用戶 revoke dba from baidu 創建用戶 分配表空間--指定用戶的默認表空間 create table p(..) 建表存在默認表空間 --建表時候指定表空間 create table p(...) talebspace 表空間名 */ /* PlSql編程語言 procedure language 程序語言 是在sql陳述句中加入一些處理程序的陳述句 常見的條件運算式 if else 還有回圈結構 基本結構 declare --宣告部分 理解為定義 --宣告使用的變數 begin --處理邏輯的代碼塊 end; */ --psSql簡單示例 declare v_n number := 1; --宣告數值變數 賦值使用符號:= v_s varchar2(4) :='s'; --宣告字符型別變數 emp_ename emp.ename%type ;-- 參考型別變數 emp_row emp%rowtype ;-- 記錄型別變數 begin dbms_output.put_line('v_n====='||v_n); --輸出陳述句相當于sys out dbms_output.put_line('v_s====='||v_s); select ename into emp_ename from emp where empno=7499; --使用into關鍵字賦值 dbms_output.put_line('emp_ename====='||emp_ename); select * into emp_row from emp where empno = 7499; --賦值記錄型別變數 dbms_output.put_line('員工編號=='||emp_row.empno||'員工姓名'||emp_row.ename); end; /* plsql 的條件運算式判斷 if .. 處理陳述句 else if ..處理陳述句 ------------------------- if .. then elsif .. then else end if; */ ---使用條件運算式判斷員工的工資 使用漢字輸出 declare emp_row emp%rowtype ;-- 記錄型別變數 begin select * into emp_row from emp where empno = 7499; --賦值記錄型別變數 --使用運算式判斷工資 if emp_row.sal > 3000 then dbms_output.put_line('員工工資大于3000=='||emp_row.sal); elsif emp_row.sal < 1000 then dbms_output.put_line('員工工資小于1000=='||emp_row.sal); else dbms_output.put_line('員工工資位于1000到3000之間=='||emp_row.sal); end if; end; /* 回圈結構 第一種----- loop exit when 條件 end loop; 第二種 --- while 條件 loop end loop; 第三種 --- for 變數 in 范圍 loop end loop; */ -------使用回圈輸出數字 1-----10 /* 第一種 loop exit when 條件 end loop; */ declare v_n number :=1; begin loop --只是用來判斷退出使用的,并不是相當于if()else{} exit when v_n>10 ; --退出條件 dbms_output.put_line(v_n); v_n:=v_n+1; --自增 end loop; end; /* 第二種 while 條件 loop end loop; */ declare v_n number :=1; begin while v_n<11 loop dbms_output.put_line(v_n); v_n:=v_n+1; --自增 end loop; end; /* 第三種 for 變數 in 范圍 loop 變數的宣告和范圍的控制是由for回圈自動執行 end loop; */ declare begin for i in 1..10 loop dbms_output.put_line(i); end loop; end; /* 游標 游標 是用于接收查詢的記錄結果集 ResultSet 提示記錄使用.next() 游標的使用步驟 宣告游標 cursor 游標名 is select 陳述句 指定游標的記錄結果集 打開游標 open 游標名 提取游標 fetch 游標名 into 記錄型別變數 關閉游標 close cursor 游標的兩個屬性 游標名%found : 判斷它有找到 游標名%notfound : 判斷它沒有找到 if emp_cursor%found then dbms_output.put_line('found'); elsif emp_cursor%notfound then dbms_output.put_line('notfound'); elsif emp_cursor%notfound is null then dbms_output.put_line('null'); end if; */ --使用while回圈結構演示游標 declare --宣告游標 cursor emp_cursor is select * from emp; --宣告記錄型別變數 用于接收游標提取的記錄 emp_row emp%rowtype; begin --打開游標 open emp_cursor; --提取游標(判斷下一個是否有值) fetch emp_cursor into emp_row ; --有值就執行while回圈 while emp_cursor%found loop dbms_output.put_line(emp_row.empno||'員工姓名'||emp_row.ename); --繼續提取游標(并判斷下一個是否有值) fetch emp_cursor into emp_row ; end loop; close emp_cursor; end; /* loop exit when 游標提取不到 end loop */ declare --宣告游標 cursor emp_cursor is select * from emp; --宣告記錄型別變數 用于接收游標提取的記錄 emp_row emp%rowtype; begin --打開游標 open emp_cursor; loop fetch emp_cursor into emp_row; exit when emp_cursor%notfound; dbms_output.put_line(emp_row.empno||'員工姓名'||emp_row.ename); end loop; close emp_cursor; end; --使用游標提取某個部門的員工資訊 --宣告帶引數的游標資訊 declare --宣告游標 cursor emp_cursor(dno number) is select * from emp where deptno = dno ; --宣告記錄型別變數 用于接收游標提取的記錄 emp_row emp%rowtype; begin --打開游標 時候傳入引數 open emp_cursor(10); loop fetch emp_cursor into emp_row; exit when emp_cursor%notfound; dbms_output.put_line(emp_row.empno||'員工姓名'||emp_row.ename); end loop; close emp_cursor; end; /* 錯誤資訊開發中的例外 資料庫中叫做 例外 例外的分類 1.系統例外 系統定義好的例外 2.自定義的例外 new 自定義類繼承Exception 自定義傳值（錯誤代碼，提示資訊） 使用場景 不滿足某些特定業務場景，拋出自定義例外 例外的處理 java try{}catche(IndexOutOfBoundException e){}catche(Exception e){} java try{}catche(Exception e){} catche(IndexOutOfBoundException e){}--報錯 資料庫可以捕捉處理例外 exception 關鍵字捕捉例外 when 例外型別 then 處理陳述句 判斷例外型別 處理例外 */ --例外的簡單示例 /* --除0的例外 除數為0 --賦值錯誤 */ declare v_n number :=0; v_m number :=1; begin v_m:='s'; --將字串賦值給數值變數 v_m:= v_m/v_n; exception when zero_divide then dbms_output.put_line('除數不能為0'); when value_error then dbms_output.put_line('賦值有誤'); end; ---處理太多記錄數例外 declare emp_row emp%rowtype ;-- 記錄型別變數 begin select * into emp_row from emp ; --賦值記錄型別 exception when too_many_rows then dbms_output.put_line('太多記錄數'); when others then --others是最大范圍的例外 相當于java 的 Exception dbms_output.put_line('其他例外'); end; /* 需求 ：使用游標查詢部門下的員工資訊 如果部門下沒有員工 報錯提示 需要自定義例外 變數名 exception --宣告自定義例外 */ declare cursor emp_cursor is select * from emp where deptno= 40; --游標結果集不存在 emp_row emp%rowtype ;-- 記錄型別變數 no_dept_emp exception ; --宣告自定義例外 begin open emp_cursor; --打開游標 fetch emp_cursor into emp_row; if emp_cursor%notfound then --沒有員工 拋出錯誤例外 raise no_dept_emp; end if; close emp_cursor; exception when no_dept_emp then dbms_output.put_line('部門下面沒人，快招人吧'); end; /* 存盤程序 是一段封裝好的代碼塊，程序是編譯好放在服務器提供開發人員呼叫 封裝的代碼塊意義： 提升開發效率 可以復用 誰用直接呼叫 提升運行效率 一呼叫直接運行 語法：create [or repalce] procedure 程序名稱(引數名 out|in 引數型別) as|is --宣告變數的部分 begin --處理程序陳述句代碼塊 end; 呼叫存盤程序 在begin和end之間使用 程序名傳參呼叫 */ --存盤程序的簡單示例 使用存盤程序給某個員工增加工資100 create or replace procedure add_sal(eno in number ) as emp_sal number :=0; begin select sal into emp_sal from emp where empno = eno ; dbms_output.put_line('漲工資之前是===='||emp_sal); update emp set sal=sal+100 where empno = eno; select sal into emp_sal from emp where empno = eno ; dbms_output.put_line('漲工資之后是===='||emp_sal); commit; end; --------呼叫存盤程序 declare begin add_sal(7499); end; /* 使用存盤程序統計某個員工的年薪，年薪需要回傳輸出列印 in 型別輸入引數可以 省略 默認就是輸入引數 */ create or replace procedure count_sal(eno number,year_sal out number) as begin select sal*12+nvl(comm,0) into year_sal from emp where empno=eno; --使用into賦值給輸出引數 end; ----呼叫存盤程序計算年薪 declare v_emp_sal number :=0; begin count_sal(7499,v_emp_sal); dbms_output.put_line('年薪為=='||v_emp_sal); end; /* 使用存盤程序 查詢出某個部門的員工資訊 某個部門應該接受一個in型別的輸入引數 查詢到的部門員工多條記錄回傳應該使用結果集 宣告游標 cursor 游標名 is select 陳述句指定結果集 系統參考游標 sys_refcursor 宣告系統參考游標 變數名 sys_refcursor; --不需要指定結果集 打開游標 open 系統參考游標 for select 陳述句 --使用for關鍵字裝入資料 */ create or replace procedure dept_emp(dno number,cusor_emp out sys_refcursor) as begin --根據傳進來的部門編號給游標裝入結果集資料 open cusor_emp for select * from emp where deptno = dno; end; ----呼叫存盤程序查詢部門下的員工 declare cursor_emp sys_refcursor; --宣告系統參考游標傳參使用 emp_row emp%rowtype ;--記錄型別變數 begin dept_emp(10,cursor_emp); --提取游標中的資料 loop fetch cursor_emp into emp_row; exit when cursor_emp%notfound; dbms_output.put_line('編號'||emp_row.empno||'姓名'||emp_row.ename); end loop; close cursor_emp; end; /* 存盤函式 是一段封裝好的代碼塊，是編譯好放在服務器提供開發人員呼叫 封裝的代碼塊意義： 提升開發效率 可以復用 誰用直接呼叫 提升運行效率 一呼叫直接運行 語法：create [or repalce] function 函式名稱(引數名 out|in 引數型別) return 資料型別 in 代表傳入引數,out 代表傳出引數 as|is --宣告變數的部分 begin --處理程序陳述句代碼塊 --return 變數 end; 呼叫存盤函式 在begin和end之間使用 函式名傳參呼叫 函式必須使用變數接收 回傳值 */ --使用存盤函式統計某個員工的年薪 create or replace function count_emp_sal(eno number,year_sal out number) return number as v_sal number :=0; begin select sal*12+nvl(comm,0) into year_sal from emp where empno=eno; --使用into賦值給輸出引數 return v_sal; end; --不帶out型別輸出引數統計年薪 create or replace function count_sal_noout(eno number) return number as v_sal number :=0; begin select sal*12+nvl(comm,0) into v_sal from emp where empno=eno; --使用into賦值給輸出引數 return v_sal; end; --呼叫函式統計年薪 declare emp_sal number:=0; total_sal number :=0; begin --total_sal := count_emp_sal(7499,emp_sal); total_sal := count_sal_noout(7499); dbms_output.put_line(emp_sal);--0 dbms_output.put_line(total_sal); --統計后年薪 end; /* 存盤函式和程序的區別 1.創建的關鍵字 procedure funciton 2.創建函式 必須使用return 宣告函式的回傳變數資料型別 3.在函式的方法體內 必須使用return 回傳一個變數 4.函式的呼叫 必須有變數接識訓傳值 5.函式可以用在select 查詢陳述句中 select emp.*,count_sal_noout(empno) from emp; 存盤函式和程序使用場景 開發規范 java代碼待用程序 程序是用來處理業務邏輯代碼 如果邏輯中需要用到一些功能性的封裝，可以呼叫函式 90%情況下 函式和程序通用 程序可以呼叫函式，函式同樣可以呼叫程序 */ /* 觸發器 是一個監視器，監視對表中資料的操作 如果對資料的操作滿足觸發器的執行條件， 觸發器會自動運行 觸發器語法： create or repalce trigger 觸發器名稱 after|before --觸發器執行時機 insert|update|delete --監視的動作 on 表名 --表級觸發器 declare begin end; 行級觸發器 insert update delete :new 動作之后的記錄 要插入的記錄 修改后的記錄 空 :old 動作之前的記錄 空 原始的記錄 原始的記錄 */ --創建觸發器監視表，如果表中有資料插入，輸出一個歡迎陳述句 create or replace trigger insert_trigger after insert on p declare begin dbms_output.put_line('歡迎加入！'); end; ----插入資料測驗效果 insert into p values(1,'zs'); commit; --插入資料不能在休息日插入資料 --休息日 周六和周日 /* raise_application_error(v1,v2) v1錯誤代碼 v2是提示陳述句 -20000 -20999 */ create or replace trigger insert_no_work before insert on p declare v_day varchar2(10) ; begin --獲取到當前星期 select to_char(sysdate,'day') into v_day from dual; --判斷星期是否在休息日 if trim(v_day) in ('saturday','sunday') then --如果休息 錯誤提示 raise_application_error(-20001,'不能休息日插入資料'); end if; end; ----插入資料測驗效果 insert into p values(1,'zs'); commit; --使用觸發器監視表中資料修改，不能做降低工資的操作 create or replace trigger can_not_low before update on emp for each row --行級觸發器 declare begin --獲取到原始記錄的工資 --獲取修改后的工資 if :old.sal > :new.sal then --談錯誤框提示 raise_application_error(-20002,'不能降低工資'); end if; end; --修改員工的工資測驗觸發器 update emp set sal=sal-1 where empno=7499; /* 觸發器實際應用 使用觸發器實作 插入資料的id 自增長 面試題 **/ create or replace trigger auto_increment_id before insert on test_trigger for each row declare begin --補全將要插入記錄的id --補全的id 是自增長的數值 如果沒有提前創建序列,需要提前創建序列 --創建序列 create sequence order_sequence select order_sequence.nextval into :new.pid from dual; end; insert into test_trigger(pname,phone) values('zs','1234566'); commit; package baidu;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import org.junit.Test;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;public class TestJdbc { String driverClass = "oracle.jdbc.driver.OracleDriver"; String url ="jdbc:oracle:thin:@192.168.17.128:1521:orcl"; String user= "baidu_03"; String password = "baidu_03"; /* *測驗jdbc連接資料庫 * * */ @Test public void querEmp(){ try{ //加載驅動 Class.forName(driverClass); //獲取鏈接 Connection con = DriverManager.getConnection(url, user,password); //獲取預編譯的statement PreparedStatement pst= con.prepareStatement("select * from emp"); //執行查詢 ResultSet rs = pst.executeQuery(); //處理結果 while(rs.next()){ System.out.println(rs.getInt(1)+"員工姓名"+rs.getString("ename")); } rs.close(); con.close(); //關閉連接 }catch(Exception e){ e.printStackTrace(); } } /*存盤程序的呼叫 * {call <procedure-name>[(<arg1>,<arg2>, ...)]} add_sal(eno number,addsal number) * */ @Test public void callAddSal(){ try{ //加載驅動 Class.forName(driverClass); //獲取鏈接 Connection con = DriverManager.getConnection(url, user,password); //獲取預編譯的statement CallableStatement pst= con.prepareCall("{call add_sal(?,?)}"); pst.setInt(1, 7499); pst.setInt(2, 1000); //執行查詢 pst.execute(); con.close(); //關閉連接 }catch(Exception e){ e.printStackTrace(); } } /*存盤程序的呼叫 * {call <procedure-name>[(<arg1>,<arg2>, ...)]} count_yearsal(eno number,total_year_sal out number) * */ @Test public void callCountSal(){ try{ //加載驅動 Class.forName(driverClass); //獲取鏈接 Connection con = DriverManager.getConnection(url, user,password); //獲取預編譯的statement CallableStatement pst= con.prepareCall("{call count_yearsal(?,?)}"); pst.setInt(1, 7499); //注冊輸出引數 pst.registerOutParameter(2, OracleTypes.NUMBER); //執行查詢 pst.execute(); int total = pst.getInt(2); System.out.println(total); con.close(); //關閉連接 }catch(Exception e){ e.printStackTrace(); } } /* * pro_dept_emp(dno number,dept_emp out sys_refcursor) * */ @Test public void callProEmp(){ try{ //加載驅動 Class.forName(driverClass); //獲取鏈接 Connection con = DriverManager.getConnection(url, user,password); //獲取預編譯的statement CallableStatement pst= con.prepareCall("{call pro_dept_emp(?,?)}"); pst.setInt(1, 10); //注冊輸出引數 pst.registerOutParameter(2, OracleTypes.CURSOR); //執行查詢 pst.execute(); OracleCallableStatement ocs = (OracleCallableStatement)pst; ResultSet rs = ocs.getCursor(2); while(rs.next()){ System.out.println(rs.getInt(1)+"員工姓名"+rs.getString("ename")); } rs.close(); ocs.close(); pst.close(); con.close(); //關閉連接 }catch(Exception e){ e.printStackTrace(); } }}public void show4(){ try { Class.forName(driverClass); Connection con = DriverManager.getConnection(url, user,password); CallableStatement pst= con.prepareCall("{?= call count_sal_noout(?)}"); //給第二個引數賦值 pst.setLong(2, 7499); // stat2.setLong(2, empno); //宣告第一個引數的型別 pst.registerOutParameter(1, OracleTypes.NUMBER); pst.execute(); OracleCallableStatement ocs = (OracleCallableStatement)pst; NUMBER num = ocs.getNUMBER(1); System.out.println(num);// long i = pst.getLong(1);// System.out.println(i); con.close(); } catch (Exception e) { e.printStackTrace(); } }/*
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/39218.html
標籤：Oracle
上一篇：lob 按ROWID磁區并行匯出