网站建设项目前分析,wordpress改foot图标,网络软营销,做跨境电商的网站目录 数据查询以及伪列、伪表 Oracle连接方式 常用运算符 集合操作符 Oracle函数 日期函数#xff1a; 字符函数#xff1a; 数字函数#xff1a; 转换函数#xff1a; 其它函数#xff1a; 分组函数#xff1a; 分析函数 数据库对象 同义词 序列 视图 索引 PL/SQL块 游标… 目录 数据查询以及伪列、伪表 Oracle连接方式 常用运算符 集合操作符 Oracle函数 日期函数 字符函数 数字函数 转换函数 其它函数 分组函数 分析函数 数据库对象 同义词 序列 视图 索引 PL/SQL块 游标触发器 游标 触发器 创建触发器的语法 触发器由三部分组成 子程序和程序包 子程序 子程序的优点 过程 函数 程序包和程序包主体 程序包 程序包规范 程序包的优点 程序包中的游标 内置程序包 程序包主体 子程序和程序包总结 数据查询以及伪列、伪表 A. { 查询不重复的数据Distinct 连接运算符|| 运算符 - * / mod } B.{ 伪列rowid 是表中行的存储地址该地址可以唯一地标识数据库中的一行可以使用rowid快速地定位表中的一行 rownum 是查询返回的结果集中行的序号可以使用它来限定查询返回的行数
伪表dual } C.{ 单列排序order by deptno desc 多列排序 order by deptno desc,sal asc }
Oracle连接方式 D.{ 连接 》》》 左left join 右right join 内inner join 全full join 自连接 》》》 应用场合同一表中的字段与字段之间存在引用关系 注意自连接时需要给表指定别名 例查询员工‘SMITH’ 的上级领导的姓名 select 领导表.ename from emp 员工表,emp 领导表 where 员工表.ename‘SMITH’ and 员工表.mgr领导表.empno; }
常用运算符
E.{ 比较运算符 模糊查询like-通配符 %表示匹配任意个字符 _表示匹配单个字符 In(值列表) 表示查询在指定的值列表范围内的数据 is null判断是否为空 例查询没有上级的员工信息 Between…and 判断是否在两值之间 例查询薪水在2000-3000之间的员工信息 与not连用表示取否定 not likenot inis not nullnot between 逻辑运算符 连接多个比较运算的结果以生成一个或真或假的结果。 包括and与or或not非 例查询薪水高于2000或者岗位为CLERK的员工信息同时还要满足其姓名首字母为大写J }
集合操作符
F.{ 将两个查询的结果组合成一个结果集 Union联合 返回两个查询选定的所有的不重复的行 Union all联合所有 返回两个查询选定的所有行包括重复行 Intersect交集 返回两个查询都有的行 Minus减集 返回由第1个查询选定但是没有被第2个查询选定的行。 即在第1个结果集中减去在第2个结果集中出现的行
注意集合操作符联接的各个查询要具有相同的列数且对应列的数据类型必须相同。 }
Oracle函数 日期函数 Add_Months(d,n) 1d-日期n-月数 2返回给指定日期加上指定月数后的日期值 Months_Between(d1,d2) 返回两个日期之间的月数 Last_Day(d) 返回指定日期当月的最后一天的日期值 Round(d[,fmt]) 返回四舍五入后的日期值 Next_Day(d,day) 返回指定日期之后的星期几的日期 Extract(fmt from d) 提取日期时间类型中的特定部分 字符函数
Initcap(char)Select initcap(‘hello’) from dual;HelloLower(char)Select lower(‘FUN’) from dual;funUpper(char)Select upper(‘sun’) from dual;SUNLtrim(char,set)Select ltrim( ‘xyzadams’,’xyz’) from dual;adamsRtrim(char,set)Select rtrim(‘xyzadams’,’ams’) from dual;xyzadTranslate(char, from, to)Select translate(‘jack’,’j’ ,’b’) from dual;back (只1对1)Replace(char, searchstring,[rep string])Select replace(‘jack and jue’ ,’j’,’bl’) from dual;black and blueInstr (char, m, n)Select instr (‘worldwide’,’d’) from dual;5 (indexOf)Substr (char, m, n)Select substr(‘abcdefg’,3,2) from dual;cdConcat (expr1, expr2)Select concat (‘Hello’,’ world’) from dual;Hello world 数字函数
Abs(n)Select abs(-15) from dual;15Ceil(n)Select ceil(44.778) from dual;45Cos(n)Select cos(180) from dual;-.5984601Cosh(n)Select cosh(0) from dual;1Floor(n)Select floor(100.2) from dual;100Power(m,n)Select power(4,2) from dual;16Mod(m,n)Select mod(10,3) from dual;1Round(m,n)Select round(100.256,2) from dual;100.26Trunc(m,n)Select trunc(100.256,2) from dual;100.25Sqrt(n)Select sqrt(4) from dual;2Sign(n)Select sign(-30) from dual;-1 转换函数 TO_CHAR数值型或者日期型转化为字符型 TO_DATE字符型转换为日期型 TO_NUMBER将字符串转换为数值型的格式
select to_char(6666.3333,0000) from dual;select to_char(sysdate,YYYY年fmMM月fmDD日) from dual;select to_char(empno,C99999) from emp;select to_date(2003-10-09,yyyy-mm-dd) from dual;select to_number(100) from dual; 其它函数 处理null值和零值 Nvl(exp1,exp2) 将空值替换为指定的值 如果exp1为null则返回exp2否则返回exp1 例查询所有员工的年薪和年奖金之和 Nvl2(exp1,exp2,exp3) 如果exp1不为null则返回exp2否则返回exp3 Nullif(exp1,exp2) 如果exp1exp2则返回null否则返回exp1 分组函数 GROUP BY子句 用于将信息划分为更小的组 每一组行返回针对该组的单个结果 注意在包含Group by子句的查询中select子句中列表的项只能是聚合函数、Group by表达式 分组字段或表达式、常数或包含上述项之一的表达式。 HAVING子句 用于指定 GROUP BY 子句检索行的条件 ORDER BY 字句 ASC 升序 DESC 降序
对分组后的数据进行筛选 AVG 求平均值 MIN 取最小 MAX 取最大 SUM求和 count计数 分析函数 ROW_NUMBER 返回连续的排位不论值是否相等 RANK 具有相等值的行排位相同序数随后跳跃 DENSE_RANK 具有相等值的行排位相同序号是连续的 数据库对象 同义词
#私有同义词
CREATE SYNONYM emp FOR SCOTT.emp;#公有同义词
CREATE PUBLIC SYNONYM emp_syn FOR SCOTT.emp;#删除同义词
DROP SYNONYM emp;
DROP PUBLIC SYNONYM emp_syn; #替换现有的或创建同义词 查询同义词
CREATE OR REPLACE SYNONYM emp_syn FOR SCOTT.emp;
select * from emp_syn; 序列 创建序列
CREATE SEQUENCE toys_seqSTART WITH 10 指定第一个序号从10开始INCREMENT BY 10 指定序号之间的间隔为10MAXVALUE 2000 表示序列的最大值为2000MINVALUE 10 表示序列的最小值为10NOCYCLE 在达到最大值后停止生成下一个值CACHE 10; 指定内存中预先分配的序号数ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE; 使用ALTER SEQUENCE语句修改序列不能更改序列的START WITH参数
DROP SEQUENCE toys_seq; 删除序列 视图 CREATE [OR REPLACE] [FORCE] VIEW 创建视图view_name [(alias[, alias]...)] AS select_statement[WITH CHECK OPTION][WITH READ ONLY];在视图上也可以使用修改数据的DML语句如INSERT、UPDATE和DELETE视图上的DML语句有如下限制只能修改一个底层的基表如果修改违反了基表的约束条件则无法更新视图如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句则将无法更新视图如果视图包含伪列或表达式则将无法更新视图DROP VIEW view_name; 删除视图 索引
CREATE INDEX item_index ON itemfile (itemcode); 创建索引
ALTER INDEX item_index REBUILD; 重建索引
DROP INDEX item_index; 删除索引
CREATE INDEX index_reverse_empno ON emp(empno) REVERSE; 反向键索引
CREATE BITMAP INDEX index_bit_job ON emp(job); 位图索引
CREATE UNIQUE INDEX item_index ON itemfile (itemcode); 唯一索引
CREATE INDEX comp_index
ON itemfile(p_category, itemrate); 组合索引与索引有关的数据字典视图有
USER_INDEXES 用户创建的索引的信息
USER_IND_PARTITIONS 用户创建的分区索引的信息
USER_IND_COLUMNS 与索引相关的表列的信息
PL/SQL块
DECLAREp_empno NUMBER;p_name VARCHAR2(20);p_sal NUMBER;c_rate CONSTANT NUMBER : 0.10;
BEGIN...p_empno: 7366;SELECT ename, sal * c_rateINTO p_name, p_salFROM emp WHERE empno p_empno;...
END;PL/SQL 支持的流程控制结构条件控制 | 顺序控制IF 语句 | GOTO 语句CASE 语句 | NULL 语句循环控制 LOOP 循环WHILE 循环FOR 循环输出语句DBMS_OUTPUT.PUT_LINE(itemrate|| irate);异常语句raise_application_error(-20820,*******);条件控制A、 IF irate 200 THENUPDATE itemfile SET itemrate itemrate - 200WHERE itemcode icode;ELSEUPDATE itemfile SET itemrate itemrate - 50WHERE itemcode icode;END IF;B、 CASE ‘grade’WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(’优异’);WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (优秀’);ELSE DBMS_OUTPUT.PUT_LINE (’没有此成绩’);END CASE;循环控制A、LOOP sequence_of_statementsEND LOOP;B、WHILE condition LOOP sequence_of_statementsEND LOOP;C、FOR e_emp or (counter) IN [REVERSE] v_emp or(value1..value2) LOOP sequence_of_statementsEND LOOP;顺序控制IF qtyhand relevel THENGOTO updation;ELSEGOTO quit;END IF;updationUPDATE itemfile SET qty_hand qty_hand re_levelWHERE itemcode i201;quitNULL;动态SQLDECLAREsql_stmt VARCHAR2(200);emp_id NUMBER(4) : 7566;emp_rec emp%ROWTYPE;BEGINEXECUTE IMMEDIATE CREATE TABLE bonus1 (id NUMBER, amt NUMBER);sql_stmt : SELECT * FROM emp WHERE empno :id;EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;END;
游标触发器 游标 %NOTFOUND 如果FETCH语句失败则该属性为TRUE否则为FALSE; %FOUND 如果FETCH语句成功则该属性为TRUE否则为FALSE; %ROWCOUNT 返回游标当前的行数; %ISOPEN 如果游标是开的则返回TRUE否则为FALSE;
创建隐式游标DECLAREv_TOYID TOYS.ID%type : TOYID;v_TOYNAME TOYS.NAME%Type : TOYNAME;BEGINUPDATE TOYS SET NAME v_TOYNAMEWHERE toyidv_TOYID;IF SQL%NOTFOUND THENDBMS_OUTPUT.PUT_LINE(编号未找到。);ELSEDBMS_OUTPUT.PUT_LINE(‘表已更新);END IF;END;创建显示游标DECLAREv_sal emp.sal%TYPE; CURSOR c_cur IS SELECT sal FROM emp WHERE sal2500;BEGINOPEN c_cur; FOR toy_rec IN c_cur LOOP 循环游标DBMS_OUTPUT.PUT_LINE(‘玩具编号|| ||toy_rec.toyid|| ||‘玩具名称|| ||toy_rec.toyname|| ||‘玩具单价|| ||toy_rec.toyprice);END LOOP;LOOPFETCH c_cur INTO v_sal;EXIT WHEN c_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE (‘薪水||v_sal);END LOOP;CLOSE c_cur;END;使用有表变量执行动态SQLdeclareType salcursor is ref cursor;--声明游标类型salcurvar salcursor;--定义游标变量mysal number;beginopen salcurvar for‘select sal from myemp where deptno:dno’--动态SQLusing ‘no’;--填补占位符的参数loopfetch salcurvar into mysal;exit when salcurvar%notfound;dbms_output.put_line(mysal); end loop;end; 触发器 触发器是当特定事件出现时自动执行的存储过程 特定事件可以是执行更新的DML语句和DDL语句 触发器不能被显式调用 触发器的功能 自动生成数据 自定义复杂的安全权限 提供审计和日志记录 启用复杂的业务逻辑 创建触发器的语法
CREATE [OR REPLACE] TRIGGER trigger_nameAFTER | BEFORE | INSTEAD OF[INSERT] [[OR] UPDATE [OF column_list]] [[OR] DELETE]ON table_or_view_name[REFERENCING {OLD [AS] old / NEW [AS] new}][FOR EACH ROW][WHEN (condition)]pl/sql_block;行级触发器CREATE OR REPLACE TRIGGER BI_TEST_TRGBEFORE INSERT OR UPDATE OF IDON TEST_TRGFOR EACH ROWBEGINIF INSERTING THENSELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;ELSERAISE_APPLICATION_ERROR(-20020, 不允许更新ID值);END IF;END;
语句级触发器CREATE OR REPLACE TRIGGER BI_TEST_TRGBEFORE INSERT OR UPDATE OF IDON TEST_TRGFOR EACH ROWBEGINIF INSERTING THENSELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;ELSERAISE_APPLICATION_ERROR(-20020, 不允许更新ID值);END IF;END;
INSTEAD OF 触发器CREATE OR REPLACE TRIGGER upd_ord_viewINSTEAD OF UPDATE ON ord_viewFOR EACH ROWBEGINUPDATE order_masterSET vencode:NEW.vencode WHERE orderno :OLD.orderno;DBMS_OUTPUT.PUT_LINE(‘已激活触发器);END; 触发器由三部分组成 触发器语句事件 定义激活触发器的 DML 事件和 DDL 事件 触发器限制 执行触发器的条件该条件必须为真才能激活触发器 触发器操作主体 包含一些 SQL 语句和代码它们在发出了触发器语句且触发限制的值为真时运行
启用、禁用和删除触发器ALTER TRIGGER aiu_itemfile DISABLE;ALTER TRIGGER aiu_itemfile ENABLE;DROP TRIGGER aiu_itemfile;
触发器是当特定事件出现时自动执行的存储过程 触发器分为 DML 触发器、DDL 触发器和数据库级触发器三种类型 DML 触发器的三种类型包括行级触发器、语句级触发器和 INSTEAD OF 触发器
子程序和程序包 子程序 子程序的优点 模块化 将程序分解为逻辑模块 可重用性 可以被任意数目的程序调用 可维护性 简化维护操作 安全性 通过设置权限使数据更安全 过程 过程参数的三种模式
IN 用于接受调用程序的值 默认的参数模式 OUT 用于向调用程序返回值 IN OUT 用于接受调用程序的值并向调用程序返回更新的值 创建过程的语法CREATE [OR REPLACE] PROCEDURE 》procedure name [(parameter list)] 》 创建过程可指定运IS|AS 》 行过程需传递的参数。local variable declaration 》BEGINexecutable statements 包括在过程中要执行的语句 [EXCEPTIONexception handlers] } 处理异常END; 执行过程的语法
EXECUTE procedure_name(parameters_list);
CREATE OR REPLACE PROCEDUREitemdesc(item_code IN VARCHAR2)ISv_itemdesc VARCHAR2(5);BEGINSELECT itemdesc INTO v_itemdescFROM itemfileWHERE itemcode item_code;DBMS_OUTPUT.PUT_LINE(item_code||项目的说明为||v_itemdesc);END;/SET SERVEROUTPUT ONEXECUTE itemdesc(i201);将过程的执行权限授予其他用户本用户不受限制GRANT EXECUTE ON find_emp TO MARTIN;GRANT EXECUTE ON swap TO PUBLIC;删除过程DROP PROCEDURE find_emp;函数 函数是可以返回值的命名的 PL/SQL 子程序。 创建函数的语法
CREATE [OR REPLACE] FUNCTION function name [(param1,param2)]RETURN datatype IS|AS [local declarations]BEGINExecutable Statements;RETURN result;EXCEPTIONException handlers;END; 定义函数的限制 函数只能接受 IN 参数而不能接受 IN OUT 或 OUT 参数 可以使用伪类型%TYPE、%ROWTYPE 访问函数的两种方式 使用 PL/SQL 块 使用 SQL 语句
创建函数CREATE OR REPLACE FUNCTION fun_hello(a number,b varchar2)RETURN VARCHAR2ISBEGINRETURN 朋友您好;END;调用函数SELECT fun_hello(1,asdf) FROM DUAL;
过 程函 数作为 PL/SQL 语句执行**Execute** 执行作为 PL/SQL 语句执行**作为表达式的一部分调用**在规格说明中不包含 RETURN 子句必须在规格说明中包含 RETURN 子句**(执行出错)**不返回任何值必须返回单个值可以包含 RETURN 语句但是与函数不同它不能用于返回值必须包含至少一条 RETURN 语句
程序包和程序包主体 程序包
程序包规范
CREATE [OR REPLACE]PACKAGE package_name IS|AS[Public item declarations][Subprogram specification]END [package_name];create or replace package toyspack as--增加单价的过程procedure updateToyPrice;--计算平均值的函数function avgToyPrice return number;end toyspack;程序包的优点 模块化 更轻松的应用程序设计 信息隐藏 新增功能 性能更佳
程序包中的游标 游标的定义分为游标规范和游标主体两部分 在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型 RETURN子句指定的数据类型可以是 用 %ROWTYPE 属性引用表定义的记录类型 程序员定义的记录类型
CREATE OR REPLACE PACKAGE BODY cur_pack ASCURSOR ord_cur(vcode VARCHAR2)RETURN order_master%ROWTYPE IS SELECT * FROM order_master WHERE VENCODEvcode;PROCEDURE ord_pro(vcode VARCHAR2) ISor_rec order_master%ROWTYPE;BEGINOPEN ord_cur(vcode); LOOPFETCH ord_cur INTO or_rec;EXIT WHEN ord_cur%NOTFOUND;DBMS_OUTPUT.PUT_LIne(’返回的值为 || or_rec.orderno);END LOOP;END ord_pro;END cur_pack;
内置程序包 扩展数据库的功能 为 PL/SQL 提供对 SQL 功能的访问 用户 SYS 拥有所有程序包 是公有同义词 可以由任何用户访问
STANDARD**和DBMS_STANDARD**定义和扩展**PL/SQL语言环境**DBMS_LOB提供对 LOB**数据类型进行操作的功能**DBMS_OUTPUT处理**PL/SQL块和子程序输出调试信息**DBMS_RANDOM提供随机数生成器DBMS_SQL允许用户使用动态 SQLDBMS_XMLDOM用**DOM模型读写XML类型的数据**DBMS_XMLPARSERXML**解析处理XML文档内容和结构**DBMS_XMLQUERY提供将数据转换为 XML 类型的功能DBMS_XSLPROCESSOR提供**XSLT功能转换XML文档**UTL_FILE用 PL/SQL 程序来读写操作系统文本文件 内置程序包
DBMS_OUTPUT包显示 PL/SQL 块和子程序的调试信息。SQL BEGINDBMS_OUTPUT.PUT_LINE(打印三角形);FOR i IN 1..9 LOOPFOR j IN 1..i LOOPDBMS_OUTPUT.PUT(*);END LOOP for_j;DBMS_OUTPUT.NEW_LINE;END LOOP for_i;END;DBMS_LOB包提供用于处理大型对象的过程和函数
DBMS_XMLQUERY包用于将查询结果转换为 XML 格式SQL DECLAREresult CLOB;xmlstr VARCHAR2(32767);line VARCHAR2(2000);line_no INTEGER : 1;BEGINresult : DBMS_XMLQuery.getXml(SELECT empno, enameFROM employee);xmlstr : DBMS_LOB.SUBSTR(result,32767);LOOPEXIT WHEN xmlstr IS NULL;line : SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1);DBMS_OUTPUT.PUT_LINE(line_no || : || line);xmlstr : SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))1);line_no : line_no 1;END LOOP; END;DBMS_RANDOM 包可用来生成随机整数SQL SET SERVEROUTPUT ONSQL DECLAREl_num NUMBER;counter NUMBER;BEGINcounter:1;WHILE counter 10LOOPl_num : DBMS_RANDOM.RANDOM;DBMS_OUTPUT.PUT_LINE(l_num);counter:counter1;END LOOP;END;UTL_FILE 包用于读写操作系统文本文件
操作文件的一般过程是打开、读或写、关闭
UTL_FILE 包指定文件路径依赖于 DIRECTORY 对象SQL CREATE DIRECTORY TEST_DIR AS C:\DEVELOP;SQL GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;SQL SET SERVEROUTPUT ONSQL DECLAREinput_file UTL_FILE.FILE_TYPE;input_buffer VARCHAR2(4000);BEGINinput_file : UTL_FILE.FOPEN( TEST_DIR, employees.xml, r);LOOPUTL_FILE.GET_LINE(input_file,input_buffer);DBMS_OUTPUT.PUT_LINE(input_buffer);END LOOP;UTL_FILE.FCLOSE(input_file);EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(------------------);END;程序包主体
程序包主体规范
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS[Private item declarations][Subprogram bodies][BEGINInitialization]END [package_name];--创建包体
create or replace package body page210
asprocedure pro_page(dno number,clo varchar2,pageIndex number,pageSize number,c_out_cursor out c_ref,totalCount out number )asv_sql varchar2(500);beginv_sql:select * from ( select rownum rn,t.* from (select * from emp where deptno:1 order by ||clo|| desc) t where rownum:3 ) where rn:4;
--打开游标open c_out_cursor for v_sql using dno,(pageIndex*pageSize),((pageIndex-1)*pageSize);--查询总记录数v_sql:select count(1) from emp where deptno:1;--执行单行execute immediate v_sql into totalCount using dno;end pro_page;
end page210;
子程序和程序包总结
子程序是命名的 PL/SQL 块可带参数并可在需要时随时调用 有两种类型的PL/SQL子程序即过程和函数 过程用户执行特定的任务函数用于执行任务并返回值 程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装 程序包由两部分组成即包规范和包主体 使用程序包的优点是模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳
