ORACLE講解內容.docx

第一部分：ORACLE 基礎一、oracle體系結構要了解oracle體系結構必須先了解兩個基本的概念: 數(shù)據(jù)庫和實例.1、數(shù)據(jù)庫：數(shù)據(jù)庫(database)是一個數(shù)據(jù)集合.oracle數(shù)據(jù)庫都將其數(shù)據(jù)存放在數(shù)據(jù)文件中. 在其內部,構數(shù)據(jù)庫結數(shù)據(jù)對文件的邏輯映射, 使不同的數(shù)據(jù)分開存儲, 這些邏輯劃分稱為表空間。 表空間表空間(tablespace)是數(shù)據(jù)庫的邏輯劃分, 每個數(shù)據(jù)庫至少有一個表空間,叫做系統(tǒng)表空間(system 表空間). 一個表空間只能屬于一個數(shù)據(jù)庫。每個表空間由同一個磁盤上的一個或多個文件組成, 這些文件稱為數(shù)據(jù)文件。創(chuàng)建表空間的語句：create tablespace oraro datafile D:OracleDataoraro.dat size 512M autoextend on;增加數(shù)據(jù)文件：alter tablespase oraro add datafile D:OracleDataoraro1.dat size 512M; 刪除表空間：drop tablespace oraro including contents and datafiles; 數(shù)據(jù)文件每個表空間由同一個磁盤上的一個或多個文件組成, 這些文件叫做數(shù)據(jù)文件(datafile),數(shù)據(jù)文件只能屬于一個表空間. 數(shù)據(jù)文件創(chuàng)建后可以改變大小. 創(chuàng)建新的表空間需要創(chuàng)建新的數(shù)據(jù)文件. 數(shù)據(jù)文件一旦加入到表空間中, 就不能從表空間中移走, 也不能與其他表空間發(fā)生聯(lián)系.數(shù)據(jù)庫必須的三類文件是 data file, control file, redolog file. 其他文件 prameter file, password file, archived log files并不是數(shù)據(jù)庫必須的, 他們只是輔助數(shù)據(jù)庫的.查看數(shù)據(jù)庫的物理文件組成: 1)查看數(shù)據(jù)文件: SQL select * from v$datafile; 2)查看控制文件: SQL select * from v$controlfile; 3)查看日志文件: SQL select * from v$logfile; 控制文件控制文件是一個很小的二進制文件，用于記錄數(shù)據(jù)庫的物理結構。一個控制文件只屬于一個數(shù)據(jù)庫。創(chuàng)建數(shù)據(jù)庫時，創(chuàng)建控制文件。當數(shù)據(jù)庫的物理結構改變的時候，Oracle會更新控制文件。用戶不能編輯控制文件，控制文件的修改由Oracle完成。 數(shù)據(jù)庫的啟動和正常運行都離不開控制文件。啟動數(shù)據(jù)庫時，Oracle從初始化參數(shù)文件中獲得控制文件的名字及位置，打開控制文件，然后從控制文件中讀取數(shù)據(jù)文件和聯(lián)機日志文件的信息，最后打開數(shù)據(jù)庫。數(shù)據(jù)庫運行時，Oracle會修改控制文件，所以，一旦控制文件損壞，數(shù)據(jù)庫將不能正常運行。 控制文件記錄的數(shù)據(jù)庫信息如下： 數(shù)據(jù)庫名稱（Database Name） 創(chuàng)建數(shù)據(jù)庫的時間戳 數(shù)據(jù)文件的名字及位置 聯(lián)機日志文件的名字及位置 表空間信息 日志歷史記錄（Log History） 歸檔日志的信息 備份信息 當前的日志序列號（Log Sequence Number） 校驗點信息（Checkpoint） 控制文件的大小由下面的幾個參數(shù)決定： MAXLOGFILES MAXLOGMEMBERS MAXLOGHISTORY MAXDATAFILES MAXINSTANCES 當增加、重命名、刪除一個數(shù)據(jù)文件或者一個聯(lián)機日志文件時，Oracle服務器進程（Server Process）會立即更新控制文件以反映數(shù)據(jù)庫結構的這種變化。所以，Oracle總是告誡我們，在數(shù)據(jù)庫的結構發(fā)生變化后，要備份控制文件。日志寫進程LGWR負責把當前日志序列號記錄到控制文件中。校驗點進程CKPT負責把校驗點的信息記錄到控制文件中。歸檔進程負責把歸檔日志的信息記錄到控制文件中。 初始化參數(shù)CONTROL_FILES的值記錄控制文件的位置。通常，DBA應該鏡像控制文件，把每個控制文件分布到不同的物理磁盤，發(fā)生災難時，即使其中一個控制文件損壞，數(shù)據(jù)不會丟失，也不會使整個數(shù)據(jù)庫陷于癱瘓。2、實例通俗的講實例就是操作oracle數(shù)據(jù)庫的一種手段.數(shù)據(jù)庫實例也稱作服務器, 是用來訪問數(shù)據(jù)庫文件集的存儲結構及后臺進程的集合.一個數(shù)據(jù)庫可以被多個實例訪問(稱為真正的應用群集選項).決定實例的大小及組成的各種參數(shù)或者存儲在名稱init.ora的初始化文件中, 或者隱藏在數(shù)據(jù)庫內部的服務器參數(shù)文件中. 通過spfile引用該文件, spfile存儲在spfile.ora文件中.實例啟動時讀取初始化文件, 數(shù)據(jù)庫系統(tǒng)管理員可以修改該文件, 對初始化文件的修改只有在下次啟動時才有效.3、監(jiān)聽程序監(jiān)聽器是Oracle基于服務器端的一種網(wǎng)絡服務，主要用于監(jiān)聽客戶端向數(shù)據(jù)庫服務器端提出的連接請求。既然是基于服務器端的服務，那么它也只存在于數(shù)據(jù)庫服務器端，進行監(jiān)聽器的設置也是在數(shù)據(jù)庫服務器端完成的。二、PL/SQLPL/SQL也是一種程序語言，叫做過程化SQL語言（Procedural Language/SQL）。PL/SQL是Oracle數(shù)據(jù)庫對SQL語句的擴展。在普通SQL語句的使用上增加了編程語言的特點，所以PL/SQL就是把數(shù)據(jù)操作和查詢語句組織在PL/SQL代碼的過程性單元中，通過邏輯判斷、循環(huán)等操作實現(xiàn)復雜的功能或者計算的程序語言。1 PL/SQL基本概念1.1 PL/SQL的作用 使用PL/SQL可以編寫具有很多高級功能的程序，雖然通過多個SQL語句可能也能實現(xiàn)同樣的功能，但是相比而言，PL/SQL具有更為明顯的一些優(yōu)點： 能夠使一組SQL語句的功能更具模塊化程序特點； 采用了過程性語言控制程序的結構； 可以對程序中的錯誤進行自動處理，使程序能夠在遇到錯誤的時候不會被中斷； 具有較好的可移植性，可以移植到另一個Oracle數(shù)據(jù)庫中； 集成在數(shù)據(jù)庫中，調用更快； 減少了網(wǎng)絡的交互，有助于提高程序性能。 通過多條SQL語句實現(xiàn)功能時，每條語句都需要在客戶端和服務端傳遞，而且每條語句的執(zhí)行結果也需要在網(wǎng)絡中進行交互，占用了大量的網(wǎng)絡帶寬，消耗了大量網(wǎng)絡傳遞的時間，而在網(wǎng)絡中傳輸?shù)哪切┙Y果，往往都是中間結果，而不是我們所關心的。 而使用PL/SQL程序是因為程序代碼存儲在數(shù)據(jù)庫中，程序的分析和執(zhí)行完全在數(shù)據(jù)庫內部進行，用戶所需要做的就是在客戶端發(fā)出調用PL/SQL的執(zhí)行命令，數(shù)據(jù)庫接收到執(zhí)行命令后，在數(shù)據(jù)庫內部完成整個PL/SQL程序的執(zhí)行，并將最終的執(zhí)行結果返饋給用戶。在整個過程中網(wǎng)絡里只傳輸了很少的數(shù)據(jù)，減少了網(wǎng)絡傳輸占用的時間，所以整體程序的執(zhí)行性能會有明顯的提高。 1.2 PL/SQL分類1) DDL語句(數(shù)據(jù)定義語言) Data Define Language CREATE TABLE 創(chuàng)建表 ALTER TABLE 更改表 DROP TABLE 刪除表 TRUNCATE TABLE 清空表 RENAME table1 TO table2 改表名 建立表 create table class(-班級表 classid number(2) primary key, cname varchar2(20) not null); create table student( -學生表 sid number(4) primary key, -學號 name varchar2(10) not null, -姓名 sex char(2) check (sex in (M,F),-性別 birthday date,-生日 classid number(2) references class(classid) -班級 );外鍵引用的列一定是主鍵或有unique約束的列 alter table student add (age number(3);2) DML語句(數(shù)據(jù)操作語言) Data Manupilate Language UPDATE 更新存儲在表中的數(shù)據(jù) DELETE 刪除行 SELECT 查詢表 INSERT 插入數(shù)據(jù) 特點: 對數(shù)據(jù)起作用的這些語句的修改是在內存中發(fā)生的 要想改動存入庫中必須要commit語句 delete from student： 只刪除數(shù)據(jù)，速度慢，數(shù)據(jù)在commit之前可以恢復 truncate table student; 刪除 的時候不檢查表中現(xiàn)有記錄，速度快，數(shù)據(jù)不能恢復，可以自動釋放所清空記錄之前所占的表空間3) TCL(事務控制語句) Transaction Control LanguageCOMMIT 提交 修改保存到數(shù)據(jù)庫中 ROLLBACK 回滾 取消內存中的改動 SAVEPOINT 保存點 分解事務的 把事務變小4) DCL(數(shù)據(jù)控制語句)Data Control Language GRANT 賦予權限-創(chuàng)建用戶create user zhangsan identified by oraro;-授權grant create table,create tablespace to zhangsan;grant select,delete,insert on tb_bulletin to zhangsan;grant connect to zhangsan;-刪除drop user zhangsan; REVOKE 收回權限revoke select on tb_bulletin from zhangsan;1.3 PL/SQL程序的基本結構 PL/SQL塊由四個基本部分組成：聲明、執(zhí)行體開始、異常處理、執(zhí)行體結束。 下面是四個部分的基本結構： DECLARE可選部分 變量、常量、游標、用戶定義異常的聲明 BEGIN必要部分 SQL語句和PL/SQL語句構成的執(zhí)行程序 EXCEPTION可選部分 程序出現(xiàn)異常時，捕捉異常并處理異常 END；必須部分 在數(shù)據(jù)庫執(zhí)行PL/SQL程序時，PL/SQL語句和SQL語句是分別進行解析和執(zhí)行的。PL/SQL塊被數(shù)據(jù)庫內部的PL/SQL引擎提取，將SQL語句取出送給Oracle的SQL引擎處理，兩種語句分別在兩種引擎中分析處理，在數(shù)據(jù)庫內部完成數(shù)據(jù)交互、處理過程。declare v_goods_type varchar2(100):=杯具;-商品類別 v_row int;-數(shù)據(jù)行數(shù)begin select count(*) into v_row from tb_goods_type where type_name=v_goods_type; if v_row0 then dbms_output.put_line(商品類型已存在); else insert into tb_goods_type (type_id, type_name) values (seq_goods_id.nextval, v_goods_type); commit; dbms_output.put_line(商品類型添加成功); end if;end; 2 PL/SQL的變量就像其他的程序語言一樣，變量是在程序中出現(xiàn)最頻繁的名詞，在PL/SQL中的學習中首先需要了解變量的一些基本概念和使用方法。 PL/SQL程序包括了四個部分，在四個部分中，聲明部分主要用來聲明變量并且初始化變量，在執(zhí)行部分可以為變量賦新值，或者在表達式中引用變量的值，在異常處理部分同樣可以按執(zhí)行部分的方法使用變量。另外，在PL/SQL程序使用時可以通過參數(shù)變量把值傳遞到PL/SQL塊中，也可以通過輸出變量或者參數(shù)變量將值傳出PL/SQL塊。 在定義變量、常量標識符時需要注意下面的一些基本規(guī)則： 1) 定義的標識符名稱應該遵循命名規(guī)則，在后面將會提到主要的命名規(guī)則； 2) 在聲明常量和變量的時候可以為其設置初始化值，也可以強制設置not null； 3) 可以使用賦值運算符（：=）或DEFAULT保留字來初始化標識符，為標識符賦初始值； 4) 在聲明標識符時，每行只能聲明一個標識符。 在PL/SQL中主要使用下面三種類型的變量（或者常量）： 1) 簡單變量； 2) 復合（組合）變量； 3) 外部變量。 三種變量分別用于存放不同特性的數(shù)據(jù)。 3 PL/SQL的基本語法在寫PL/SQL語句時，必須遵循一些基本的語法，下面是PL/SQL程序代碼的基本語法要求： 1.語句可以寫在多行，就像SQL語句一樣； 2.各個關鍵字、字段名稱等等，通過空格分隔； 3.每條語句必須以分號結束，包括PL/SQL結束部分的END關鍵字后面也需要分號； 4.標識符需要遵循相應的命名規(guī)定； （1）名稱最多可以包含30個字符； （2）不能直接使用保留字，如果需要，需要使用雙引號括起來； （3）第一個字符必須以字母開始； （4）不要用數(shù)據(jù)庫的表或者科學計數(shù)法表示； 還有一些語法相關的規(guī)則： 1.在PL/SQL程序中出現(xiàn)的字符值和日期值必須用單引號括起； 2.數(shù)字值可以使用簡單數(shù)字或者科學計數(shù)法表示； 3.在程序中最好養(yǎng)成添加注釋的習慣，使用注釋可以使程序更清晰，使開發(fā)者或者其他人員能夠很快的理解程序的含義和思路。在程序中添加注釋可以采用： （1）/*和*/之間的多行注釋； （2）以-開始的單行注釋。三、約束在數(shù)據(jù)庫中使用約束(constraints)是為了在該數(shù)據(jù)庫中實施所謂的業(yè)務規(guī)則其實就是防止非法信息進入數(shù)據(jù)庫,滿足管理員和應用開發(fā)人員所定義的規(guī)則集.ORACLE 使用完整性約束(integrity constraints)防止不合法的數(shù)據(jù)寫入數(shù)據(jù)庫,管理員和開發(fā)人員可以定義完整性規(guī)則,增強商業(yè)規(guī)則,限制數(shù)據(jù)表中的數(shù)據(jù).如果一個DML語句執(zhí)行的任何結果破壞了完整性約束,ORACLE就會回滾語句,返回錯誤信息.約束是通過使用CREATE TABLE或ALTER TABLE語句生成的.(建立表時或者表建立后修改都可)如果相關的約束定義在單列上,可以在列這一級指定約束的定義;多列約束必須定義在數(shù)據(jù)表級,相關的列要在括號中指定,用逗號分隔.如果沒有為約束提供一個名字,那么ORACLE會分配一個系統(tǒng)生成的唯一名字,以SYS_開頭,你可以使用關鍵字 CONSTRAINTS后面跟隨相關的約束名字來為約束指定名字.ORACLE支持五種類型的完整性約束NOT NULL (非空)-防止NULL值進入指定的列,在單列基礎上定義,默認情況下,ORACLE允許在任何列中有NULL值.CHECK (檢查)-檢查在約束中指定的條件是否得到了滿足.UNIQUE (唯一)-保證在指定的列中沒有重復值.在該表中每一個值或者每一組值都將是唯一的.PRIMARY KEY (主鍵)-用來唯一的標識出表的每一行,并且防止出現(xiàn)NULL值,一個表只能有一個主鍵約束.POREIGN KEY (外部鍵)-通過使用公共列在表之間建立一種父子(parent-child)關系,在表上定義的外部鍵可以指向主鍵或者其他表的唯一鍵.約束定義存儲在數(shù)據(jù)字典中,查詢USER_CONSTRAINTS可以獲得相關信息.定義約束CREATE TABLE schema.table(column datatype DEFAULT exprcolumn_constraint,.table_constraint,.);e.g.CREATE TABLE employees(employee_id NUMBER(6),first_name VARCHAR2(20),.job_id VARCHAR2(10) NOT NULL,CONSTRAINTS emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID);列級的約束定義column CONSTRAINT constraint_name constraint_type,表級約束的定義column,.CONSTRAINT constraint_name constraint_type (column,.)NOT NULL約束只能定義在列級,不能定義在表級.e.g.CREATE TABLE employees(employee_id NUMBER(6),last_name VARCHAR2(25) NOT NULL, /沒有指定名字,將用系統(tǒng)SYS_命名salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL); /用戶定義了名字UNIQUE約束用來保護一個表中的一個或者多個列沒有任何兩行在收到保護的列中具有重復的數(shù)據(jù).ORACLE在唯一鍵列上自動生成一個唯一索引以實現(xiàn)唯一性e.g.CREATE TABLE employees(employee_id NUMBER(6),last_name VARCHAR2(25) NOT NULL,salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_date DATE NOT NULL,CONSTRAINT emp_email_uk UNIQUE(email);PRIMARY KEY約束唯一鍵的所有特征都適用于主鍵約束,只是在主鍵列中不允許有NULL值.一個表只能有一個主鍵.e.g.CREATE TABLE departments(department_id NUMBER(4),department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL,manager_id NUMBER(6),location_id NUMBER(4),CONSTRAINT dept_id_pk PRIMARY KEY(department_id);FOREIGN KEY 約束用來保護一個表中的一個或者多個列,它會通過一個主鍵主鍵或者唯一鍵保證對于每個非NULL值在數(shù)據(jù)庫的其他地方都有一個數(shù)據(jù)可用.這個外部鍵就是在生成此約束的表(子表)中的一個或多個列,在父級表和子表中,相關列的數(shù)據(jù)類型必須匹配.外部鍵列和引用鍵(reference key)列可以位于相同的表中(自引用完整性約束).e.g.CREATE TABLE employees(employee_id NUMBER(6),last_name VARCHAR2(25) NOT NULL,salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_date DATE NOT NULL,deparment_id NUMBER(4),CONSTRAINT emp_dept_fk FOREIGN KEY(department_id) REFERENCES departments(department_id),CONSTRAINT emp_email_uk UNIQUE(email);上例中是在表級定義外部鍵約束,如果在列級定義,不同的是:CREATE TABLE employees(.,department_id NUMBER(4) CONSTRAINT emp_deptid_fk REFERENCES departments(department_id),.);/沒有關鍵字FOREIGN KEYFOREIGN KEY約束還有兩個關鍵字是ON DELETE CASCADE -當刪除所引用的父表記錄時,刪除子表中相關的記錄ON DELETE SET NULL-與上面不同的是刪除時,轉換子表中相關記錄為NULL值默認情況下,如果沒有指定以上兩個中任一,則父表中被引用的記錄將不能被刪除.CHECK 約束CONSTRAINT CHECK ( )這里CHECK子句中的CONDITION應該求值為一個布爾值結果,并且可以引用相同行中其他列的值;不能包含子查詢,序列,環(huán)境函數(shù)(SYSDATE, UID,USER,USERENV)和偽列(ROWNUM,LEVEL,CURRVAL,NEXTVAL),一個列上可以定義多個CHECK約束,如果所定義的條件為FALSE,則語句將回滾.CREATE TABLE employees(.,salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary0),.);添加約束ALTER TABLE employeesADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);刪除約束ALTER TABLE employeesDROP CONSTRAINT emp_manager_fk;ALTER TABLE departmentsDROP PRIMARY KEY CASCADE; /由于departments被employees.department_id引用了對于NOT NULL約束,用ALTER TABLE MODIFY子句來刪除ALTER TABLE employees MODIFY last_name NULL;關閉約束ALTER TABLE employeesDISABLE CONSTRAINT emp_emp_id_pk CASCADE; /如果沒有被引用則不需CASCADE關鍵字當你生成一個約束時,約束自動打開(除非你指定了DISABLE子句0,當用DISABLE關閉UNIQUE或者PRIMARY KEY約束時,ORACLE會自動刪除相關的唯一索引,再次打開時,ORACLE又會自動建立的.打開約束ALTER TABLE employeesENABLE CONSTRAINT emp_emp_id_pk; /注意,打開一個先前關閉的被引用的主鍵約束,并不能自動打開相關的外部鍵約束可以從USER_CONSTRAINTS表和USER_CONS_COLUMNS視圖中查詢約束的信息SELECT constraint_name,constraint_type,search_conditionFROM user_constraintsWHERE table_name=EMPLOYEES;四、序列序列是一數(shù)據(jù)庫對象，利用它可生成唯一的整數(shù)。一般使用序列自動地生成主鍵值。一個序列的值是由特別的Oracle程序自動生成，因而序列避免了在運用層實現(xiàn)序列而引起的性能瓶頸。Oracle序列允許同時生成多個序列號，而每一個序列號是唯一的。 當一個序列號生成時，序列是遞增，獨立于事務的提交或回滾。容許設計缺省序列，不需指定任何子句。該序列為上升序列，由1開始，增量為1，沒有上限。 1） 建立序列命令CREATE SEQUENCE user.sequence_name increment by n start with n maxvalue n | nomaxvalue minvalue n | nominvalue; INCREMENT BY： 指定序列號之間的間隔，該值可為正的或負的整數(shù)，但不可為0。序列為升序。忽略該子句時，缺省值為1。 START WITH：指定生成的第一個序列號。在升序時，序列可從比最小值大的值開始，缺省值為序列的最小值。對于降序，序列可由比最大值小的值開始，缺省值為序列的最大值。 MAXVALUE：指定序列可生成的最大值。 NOMAXVALUE：為升序指定最大值為1027，為降序指定最大值為-1。 MINVALUE：指定序列的最小值。 NOMINVALUE：為升序指定最小值為1。為降序指定最小值為-1026。2） 更改序列命令ALTER SEQUENCE user.sequence_nameINCREMENT BY nMAXVALUE n| NOMAXVALUE MINVALUE n | NOMINVALUE；修改序列可以：修改未來序列值的增量。設置或撤消最小值或最大值。改變緩沖序列的數(shù)目。指定序列號是否是有序。注意：1，第一次NEXTVAL返回的是初始值2，可以alter除start至以外的所有sequence參數(shù)。如果想要改變start值，必須 drop sequence 再 re-create .3） 刪除序列命令DROP SEQUENCE user.sequence_name；用于從數(shù)據(jù)庫中刪除一序列。4）牛刀小試4.1）創(chuàng)建一個序列號的語句：- Create sequencecreate sequence NCME_QUESTION_SEQminvalue 1maxvalue 999999999999start with 1increment by 1cache 20;/4.2）SQL中取序列號的用法：SELECT NCME_QUESTION_SEQ.nextval FROM dualSELECT NCME_QUESTION_SEQ.CURRVAL FROM dualSELECT NCME_QUESTION_SEQ.nextval FROM dual SELECT NCME_QUESTION_SEQ.CURRVAL FROM dual注意：在使用序列的時候，有時需要有用戶名，就像這樣：insert into system.CONSERVATOR(CONSERVATORNAME,CONPASS,CONTRUENAME,CONSEX,CONID)values(JG,123456,000,0, system.CONID.nextval);第二部分：查詢、函數(shù)、游標一、 查詢?yōu)槭裁词褂米釉?？查詢成績高于平均分的學生的信息l 第一種方式declare v_avg_score number;begin select avg(score) into v_avg_score from tb_mark; select stuname from tb_student stu inner join tb_mark mk on stu.stuno=mk.stuno where scorev_avg_score;end;l 第二種方式select stuname from tb_student stu inner join tb_mark mk on stu.stuno=mk.stuno where score(select avg(score) from tb_mark);子查詢在WHERE語句中的一般用法：SELECT FROM 表1 WHERE 字段1 (子查詢)外面的查詢稱為父查詢，括號中嵌入的查詢稱為子查詢 UPDATE、INSERT、DELETE一起使用，語法類似于SELECT語句 將子查詢和比較運算符聯(lián)合使用，必須保證子查詢返回的值不能多于一個 l Rownum,rowidRownum 查詢結果中的行號， ROWID是數(shù)據(jù)的物理地址二、 函數(shù)函數(shù)是數(shù)據(jù)庫中常用對象之一，與其他編程語言的函數(shù)一樣，ORACLE中的函數(shù)也必須返回一個值，這也是函數(shù)區(qū)別于存儲過程的重要特征(函數(shù)類似于JAVA中的方法)。簡介：1、 函數(shù)與功能的劃分：最大化函數(shù)復用；2、 函數(shù)的參數(shù)：函數(shù)可以沒有傳入?yún)?shù)，但是如果有的話必須明確數(shù)據(jù)類型；3、 函數(shù)的返回值：必須有返回值，必須在結尾處用return返回，類型可以是字符串，字符型，數(shù)組或對象類型；創(chuàng)建函數(shù)：1、 創(chuàng)建函數(shù)：create or replace function get_hello_msgreturn varchar2 asbegin return hello world;end get_hello_msg;2、 在數(shù)據(jù)字典中查看函數(shù)的信息：select object_name,object_type,status from user_objects where lower(object_name)=get_hello_msg3、 查看函數(shù)的返回值：declare msg varchar2(20);beginmsg:=get_hello_msg;dbms_output.put_line(msg);end;注：ORACLE中函數(shù)名后的括號在無參數(shù)情況下可以省略；帶參數(shù)的函數(shù)：-*- 版權所有: (C)201, 南京橙紅信息科技有限公司1。- 數(shù)據(jù)庫版本: Oracle 10G R2- 內容摘要: 判斷指定表指定列的值是否存在- 創(chuàng) 建 人: 里斯- 創(chuàng)建日期: 2011.9.5- 備注:-*create or replace function func_Exists(v_table_name varchar2,v_column_name varchar2,v_value varchar2) return booleanas v_sql varchar2(1000);-保存SQL命令 v_rows int;-行數(shù) v_exists boolean:=false;begin v_sql:=select count(*) r from | v_table_name | where | v_column_name | = | v_value | ; execute immediate v_sql into v_rows; if(v_rows0) then v_exists:=true; end if; return v_exists; exception when others then dbms_output.put_line(執(zhí)行SQL出錯); dbms_output.put_line(v_sql);end;三、 游標游標可以增強SQL語句的功能 ，它可以對SQL語句的處理進行顯式的控制，可以非常方便地底幫助我們從數(shù)據(jù)庫中提取多行數(shù)據(jù)，然后對每一行數(shù)據(jù)進行單獨處理。游標是一種PL/SQL的控制結構，它可以命名一個工作區(qū)來存取該工作區(qū)的存儲信息。游標分為顯式游標和隱式游標， PL/SQL會為所有的DML 句創(chuàng)建一個游標，包含那些只返回一條語句的查詢。對于返回多條記錄的查詢語句，我們應該顯式地聲明一個游標來逐個處理這些數(shù)據(jù)。DECLAREv_ename scott.EMP.ENAME%TYPE;-定義v_name為列ename同一種類型v_salary scott.EMP.sal%TYPE; -定義v_name為列sal同一種類型CURSOR c_emp IS SELECT ename,sal FROM scott.emp;BEGIN OPEN c_emp; LOOP FETCH c_emp INTO v_ename,v_salary; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(Salary of Employee | v_ename | is | v_salary);end loop;END;關于SQL編程中的特殊變量類型（type和rowtype）declare v_row scott.emp%rowtype;begin select empno, ename, job, mgr, hiredate,sal, comm, deptno into v_row from scott.emp where empno=7521; dbms_output.put_line(v_row.ename);end;第三部分 高級查詢、存儲過程Case when 用法select gi.goods_name,ogi.price,ogi.quantity,case gi.status when 0 then 未處理 when 1 then 缺貨 when 2 then 已發(fā)貨 when 3 then 收貨 when 4 then 退貨 end as st,order_time,order_accountfrom tb_order_goods_info ogi,tb_goods_info gi,tb_order_info oiwhere ogi.goods_id=gi.goods_id and oi.order_id=ogi.order_idand customer_id=11、復雜查詢班級表：tb_classClass_idNumber(4)班級ID主鍵Class_nameVarchar2(30)班級名稱NOT NULLGrade_tpye_codeChar(1)歸屬年級編碼NOT NULL 約定用1-6年級學生表：tb_studentStudent_idNumber(8)學號主鍵Student_nameVarchar2(50)姓名NOT_NULLClass_idNumber(4)歸屬班級NOT_NULLSex Char(1)性別NOT_NULL 0：男 1：女科目表 tb_subjectSubject_idNumber(8)科目ID主鍵 如：1000100Subject_nameVarchar2(50)科目名稱NOT_NULL,語文，數(shù)學Extend_tagChar(1)是否必選NOT_NULL 0：必選 1：否考試結果信息表 t_scoreStudent_idNumber(8)學號NOT_NULLSubject_idNumber(8)科目IDNOT_NULLScoreNumber(5,2)分數(shù)NOT_NULLExam_noNumber(4)考試批次NOT_NULLRelease_timedate發(fā)布時間NOT_NULL注：每個批次的考試，都是按各年級統(tǒng)一參加考試，各年級必選科目固定且相同問題1：假如在t_score表中存在重復的記錄，請用一條SQL語句刪除重復的記錄。第一步：查詢重復的記錄select subject_id, student_id, score from t_scoregroup by subject_id, student_id, score having count(subject_id)1第二步：通過rowid列找出兩列中的一列select max(rowid) id, subject_id, student_id, score from t_scoregroup by subject id, student_id, score having count(subject_id)1第三步：根據(jù)rowid刪除重復的列delete from t_score where rowid in(select max(rowid) id from t_scoregroup by subject_id, student_id, scorehaving count(subject_id)1)2：寫一個語句刪除t_score非必選科目的記錄delete from t_socre where exists(select 1 from t_score sc,t_subject sb where sc.subject_id=sb.subject_id and Extend_tag=0);3：找出在相同批次考試中，相同年級，相同科目，成績相同的記錄select grade_tpye_code,subject_name,score from t_socre sc,t_student st,t_subject sb,t_class cswhere cs.class_id=st.class_id and st.student_id=sc.student_id and sb.subjecct_id=st.sbubject_idgroup by grade_tpye_code,subject_name,score,student_name,exam_nohaving count(grade_tpye_code)14：統(tǒng)計以下信息；每個必選科在各考試批次中的年級最高分，年級平均分，各班級最高分-年級最高分平均分select Grade_tpye_code,max(score) grade_max_score,avg(score) grade_avg_score from t_score sc inner join t_student st on sc.student_id=st.student_id inner join t_class cs.class_id=t_student.class_idgroup by cs.Grade_tpye_code,exam_no -班級最高分平均分select cs.class_name,max(score) class_max_score from t_score sc inner join t_student st on sc.student_id=st.student_id inner join t_class cs.class_id=t_student.class_idgroup by cs.class_id,cs.class_name,exam2、存儲過程存儲過程是PL/SQL語句的集合，類似于方法，存儲過程不必返回值，但可以有參數(shù)；存儲過程有三種參數(shù)：輸入?yún)?shù) 輸出參數(shù) 輸入輸出參數(shù)存儲過程的優(yōu)點： 1、 提高數(shù)據(jù)庫執(zhí)行效率在編程語言中，使用SQL接口更新數(shù)據(jù)就意味著要頻繁的連接數(shù)據(jù)庫，但連接數(shù)據(jù)庫是一個很耗時且消耗資源的的操作，這樣一來對于數(shù)據(jù)庫的性能就完全得不到保證，如果使用存儲過程來更新，則可大大的提高效率；2、 提高安全性存儲過程是以對象形式保存在數(shù)據(jù)庫中，這樣一來就可以對存儲過程分配權限來保證安全性；而且這樣一來就使操作從編程語言轉移到了數(shù)據(jù)庫，只要數(shù)據(jù)庫不被破壞，那么這些操作就能夠保存下來；3、 可復用4、節(jié)省網(wǎng)絡流