)
Oracle数据库学习PL/SQL什么是PL/SQLPL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的扩展语言;使用PL/SQL 可以编写具有很多高级功能的程序虽然通过多个SQL语句也可以实现相同的功能但对于应用程序开发过程中, 需要多次与数据库打交道,PL/SQL可以针对于某一种功能特定的实现…(类似Java的方法) 一定程度上避免了频繁于数据库的请求 (减低服务器压力)优点PL/SQL具有编程语言的特点, 它把一组SQL语句放到一个模块中执行使其更具模块化程序的特点PL/SQL可以采用过程语言的程序结构可以在其中写 逻辑判断 循环…与其它编程语言类似PL/SQL对于程序中的异常也有其响应的处理方式;PL/SQL块具有更好的可移植性; 可以移植到另一个Oracle数据库中…减少了程序网络的交互(数据了访问), 提高程序性能;PL/SQL体系结构PL/SQL块由PL/SQL引擎来编译 和 执行PL/SQL块; 该引擎存在于 Oracle服务器中PL/SQL引擎执行所有过程语句将SQL语句发送给Oracle的SQL语句执行器PL/SQL简介PL/SQL 是一种块结构的语言它把一组SQL语句放到一个模块中执行使其更具模块化程序的特点匿名块是一个未在数据库中命名的PL/SQL块, 在运行时传递到PL/SQL引擎中执行…PL/SQL中可以使用 select insert update delete…事务语句以及sql函数增删改别忘了 COMMIT 提交事务~PL/SQL中不允许直接使用create deop alter 但可以通过动态SQL来执行它们…一个PL/SQL语句由三个部分组成声明部分执行部分异常处理部分PL/SLQ结构块--PL/SQL块基本结构DECLARE-- DECLARE 可选,声明部分:--在此声明PL/SQL用到的变量,常量,类型,游标 以及局部的存储过程和函数;BEGIN-- BEGIN~end 必须项,执行部分:-- 在此编写执行的过程 及 SQL语句,即程序的主要部分;EXCEPTION-- EXCEPTION 可选,指定出现错误时需要执行的操作END;-- end: 表示Pl/sql块的结束,别忘了分号结尾;-- 上面是PL/SQL块的基本结构, BEGIN~END; 是必须的 声明部分和异常处理部分并不是必须的..-- 是PL/SQL中的单行注释 /**/ 多行注释;运算符和表达式关系运算符运算符意义运算符意义小于大于小于或等于大于或等于等于, !, ~, ^不等于一般预算符运算符运算符运算符运算符--------------------------加号-减* 乘/除:赋值号关系号. . 范围运算符︳︳字符连接符逻辑预算符运算符意义运算符意义--------------------------IS NULL空值BETWEEN AND介于两者之间iN在一列值之间AND逻辑与OR逻辑或NOT取反, 如 IS NOT NULL 或 NOT INDECLARE变量的声明语法/** 使用DECLARE 关键字,用于定义变量或者常量: PL/SQL块的可执行部分引用变量和常量前,必须先在DECLARE 对其进行声明; */DECLAREvariable_name[CONSTANT]type[(size)][NOTNULL][:value];BEGINEXCEPTIONEND;-- variable_name:变量名称-- [CONSTANT] :可选表示是否为常量-- type :表示变量的数据类型-- [(size)] :可选,数据类型的长度-- [NOT NULL] :可选,该变量是否可以为空-- [:value] :可选,使用 : 给对于对象赋值 初始值;PL/SQL变量名规范变量命名规则变量名首字母必须是英文字母其后可以是字母、数字或者特殊字符$、#和下划线变量名长度不超过30个字符变量名中不能有空格 不能是SQL保留字 不能用 - (减号)唯一比较好的就是不区分大小写了吧…实例Demo-- scott用户下emp举例select*fromemp;--PL/SQL 根据输入id,获得当前用户名 JOB-- 方式一:PL/SQL块 注意分号~declare--声明变量 id ename job, 每一个变量之间 ; 分号分隔...--id 通过 :值 进行赋默认值,并通过 xxx由Oracle工具输入参数;v_id number :myid;--还可以把: myid手动输入换位固定的 :7934; 固定的id号;v_ename varchar2(50);v_JOB varchar2(50);beginselectename,jobintov_ename,v_JOBfromempwhereempnov_id;dbms_output.put_line(用户名||v_ename||职位: ||v_JOB);end;-- 还可以通过: select into 给变量进行赋值-- select 列1,列2 into 变量1,变量2 form 表 形式进行下标自动映射赋值..因此列类型和变量类型 顺序都要尽量对应-- dbms_output.put_line(); Oracle还可以通过该语句进行数据打印输出...-- 方式二: 解决了不清楚变量与列的类型declarev_id emp.empno%type:id;v_ename emp.ename%type;v_JOB emp.job%type;v_emp emp%Rowtype;beginselectename,jobintov_ename,v_JOBfromempwhereempnov_id;dbms_output.put_line(用户名||v_ename||职位: ||v_JOB);select*intov_empfromempwhereempnov_id;dbms_output.put_line(用户名||v_emp.ename||职位: ||v_emp.job);end;-- 表.列%type: 可以在不清楚列类型时候给设置类型;-- 表%ROWTYPE: 可以在不知道表中列个数 和 数据类型时候,可以通过 表%ROWTYPE 获取一个表类型变量...注意SELECT 语句在PL/SQL中的写法稍微有改变,SELECT INTO 查询结果只能返回一条记录, 并赋值到变量中保存如果返回多条数据或没有都会报错;PL/SQL 中对于多条记录的查询, 后面会学习游标进行存储多个结果集;PL/SQL控制语句PL/SQL可以通过控制结构, 来控制命令执行的流程。标准的SQL 没有流程控制的概念, 而PL/SQL提供了丰富的流程控制语句;控制结构共有三种类型条件控制 循环控制 顺序控制条件控制条件控制用于控制条件执行一系列的语句条件控制包括 IF语句 和 CASE语句IF-THEN语句IF布尔表达式THENPL/SQL和SQL执行语句ENDIF;IF-THEN-ELSE语句IF布尔表达式THENPL/SQL和SQL执行语句1ELSEPL/SQL和SQL执行语句2ENDIF;IF-THEN-ELSIF语句IF布尔表达式1THENPL/SQL和SQL执行语句1ELSIF 布尔表达式2THEN--注意是ELSIF而不是ELSEIFPL/SQL和SQL执行语句2ELSEPL/SQL和SQL执行语句3ENDIF;CASE类似于Java的Switch在Oracle 9i 后引入依据表达式选择相应的when子句执行CASE条件表达式WHEN值1THENPL/SQL和SQL执行语句1;WHEN值2THENPL/SQL和SQL执行语句2;.....WHEN值nTHENPL/SQL和SQL执行语句n;[ELSEelse_statements;]--可选相当于default 都不符合则执行..ENDCASE;循环控制循环控制用户重复执行一系列语句循环控制包括: LOOP WHERE FOREXIT 语句用于退出循环EXIT WHERE 条件语句条件成立则退出循环…LOOP死循环LOOPPL/SQL和SQL执行语句;ENDLOOP;--实现计数器功能当计数器为10或者大于10时退出DECLAREv_countinteger:1;--声明变量BEGINLOOP--死循环开始v_count:v_count1;--变每次1IFv_count10THEN--判断变量 大于等于10EXIT;--退出循环ENDIF;--IF 结束ENDLOOP;--死循环结束END;--PL/SQL块结束WHILE-LOOP循环:类似于while循环WHILE布尔表达式LOOPPL/SQL和SQL执行语句;ENDLOOP;FOR-LOOP循环:类似与for循环FOR变量名in最小值..最大值loop循环执行的语句ENDLOOP;-- ..两点表示在其中范围~eg: 1..20 循环从1开始20结束;beginforiin1..20loopinsert表value(i,值1,值2,值3);COMMIT;--提交事务~endloop;COMMIT;--或放在最后统一提交事务~-- 后面在加一个异常处理如果出现异常 ROLLBACK; 回滚数据;end;顺序控制控制语句用户按顺序执行语句NULL个人觉得用的不多…--NULL:-- NULL语句可以是一个可执行的语句,相当于一个占位符,不会执行任何的操作 的空语句;-- 它可以使某些语句变的有意义,提高程序的可读性,保证其它语句结构的完整性 和 正确性;DECLAREv_count Number :5;--声明变量BEGINIFv_count10thennull;--IF中需要有执行,不然报错; 为了使结构成立有意义使用 NULLElsedbms_output.put_line(值太小的||v_count);ENDIF;END;动态SQL什么是动态SQL编译期间SQL 语句是不确定的并且在运行时允许发生变化动态SQL应用场合要执行一个DDL 语句时 需要增加程序的灵活性时 使用包DBMS_SQL动态执行SQL语句时;动态SQL简直是PL/SQL的一大亮点而且可以搭配条件控制…使用而产生不同的SQL语句用于执行不同的操作--动态sql--查询用户总共有多少表selectcount(1)fromdba_tablesselectcount(1)fromdba_tableswheretable_nameupper(WSM);--查询所有表select*fromdba_tables--查询用户存在WSM表吗 如果有删除/没有则创建; 注意切换一个可以创建的用户哦System用户declarev_count number;--用于判断是否存在表;v_sql varchar2(500);--执行的sql变量,varchar2类型长度500 毕竟存储的是sql长一点好~beginselectcount(1)--获取countintov_countfromdba_tableswheretable_nameupper(WSM);--upper() 方法将字符串中的小写字母转为大写字母ifv_count0then--判断是否存在v_sql :drop table WSM;--拼接SQLelse--else 拼接SQLv_sql :create table WSM(||id number,||name varchar2(20)||);endif;--执行动态sqlexecuteimmediate v_sql;end;-- 前面说PL/SQL中不可以使用 DDL语句;-- 而 execute immediate SQL; 可以解决该问题...EXECUTE IMMEDIATE语句EXECUTE IMMEDIATE dynamic_sqldynamic_sql: 表示一个SQL语句或者一个PL/SQL语句块的字符串表达式, 可以通过EXECUTE IMMEDIATE语句执行…常用语法--基本语法BEGINexecuteimmediate执行的SQL;END;-- 如果在存储过程 或 PL/SQL块执行还可以;-- 给变量进行赋值 INTO-- 对于需要参数执行的SQL(增删改查条件数据..),通过 Using 传参(根据顺序进行参数匹配注意类型也对应哦..);executeimmediateselect 列1,列2 from 表 where id:idinto变量1,变量2USING条件id;declarev_name varchar2(20);beginexecuteimmediateselect ename from emp where empno:aintov_nameUSING7369;--USING指定 7369编号;dbms_output.put_line(v_name);end;/* 注意事项 EXECUTE IMMEDIATE将不会提交一个DML事务执行应该显式提交(对于增删改需要 commit; ) 当执行SQL语句时不要用分号当执行PL/SQL块时在其尾部用分号. 对于处理动态语句,EXECUTE IMMEDIATE 比以前可能用到的更容易并且更高效. */对于execute 是真的非常好用, 尤其在存储过程~ 但是对于拼接一些特殊字符要非常注意比如引号 ‘ 这真是恶心死我了搜了很多有的是 用 ’‘’ 三个引号间隔拼接为一个 ‘ 的对于新手非常难受;可以使用ASCLL码来代替引号或是其它特殊字符~chr(39) 就表示一个单引号~ 39 ASCLL码表示一个 单引号;这是我在完成存储过程中的一个条件查询拼接时间条件范围查询v_where: v_where|| AND EXP_DATE TO_DATE(||chr(39)||v_qdate||chr(39)||,||chr(39)||yyyy-fmmm-fmdd||chr(39)||);当前时写 简直恶心死了,先将字符转换为时间进行判断, 过程还要当心引号异常处理 EXCEPTION什么是异常在运行过程中出现的错误叫做异常,发生异常后, 语句将停止执行PL/SQL引擎立即将控制权转到PL/SQL的异常处理部分注意这里说的是 执行过程中的异常,并不包含编译的异常…异常处理exception用来处理正常执行过程中未预料的事件…两种比较经典的异常预定义异常和用户自定义异常预定义异常Oracle预定义异常情况大约有 24个,对于这种异常情况的处理无须在程序中定义, 可由Oracle自动引发…当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发常见预定义异常OTHERS处理程序异常不会漏过任何异常, 它相当于Java的 Exception 无论什么异常都可以捕获所以一般声明在 最后, 进行捕获遗落的异常处理…图上代码实例异常处理之后加一个 ROLLBACK; 回滚一下效果更好…实例意思是循环新增三个商品类别(当前数据有点随便…) 如果过程中有异常进行捕获处理…则插入成功用户自定义异常用户可以在 PL/SQL 块的声明部分定义异常自定义的异常通过RAISE语句显式引发;实例Demo-- 切换Scott用户select*fromemp-- 根据输入id 查询对应用户工资declarev_empno number :myid;v_ename varchar2(50);v_sal number;e_exp exception;-- exception异常类型;begin-- 查询数据赋值;selectename,salintov_ename,v_salfromempwhereempnov_empno;-- 打印输出dbms_output.put_line(v_ename||点击量:||v_sal);ifv_sal2000then-- 如果工资小于两千raise e_exp;-- 抛出异常..endif;exceptionwhenno_data_foundthendbms_output.put_line(没有找到数据);rollback;whene_expthen-- 处理手动异常...dbms_output.put_line(工资小于两千);rollback;whenothersthen-- 处理任何异常..dbms_output.put_line(程序异常);rollback;end;扩除了以上的异常情况处理…RAISE_APPLICATION_ERROR还可以重新定义异常错误消息为应用程序提供一种与Oracle交互方法d语法RAISE_APPLICATION_ERROR(err_number,err_message);err_number: 表示为用户异常标识指定的编号, 注意范围必须是 -20999~20000之间负整数’err_message: 用户为异常指定的文本信息…长度可达 2049字节…游标游标基本原理Oracle 在执行一个包含 SELECT INSERT DELETE UPDATE …的语句时候会在内存中创建出一个缓存区将执行结果放在这个缓冲区中, 而游标是指向该区的一个指针…游标分类Oracle中提供了两种游标类型,静态游标(本次讲解)动态游标静态游标 是在编译器就知道明确的SELECT 语句的游标…又细分为 显示游标/隐式游标声明游标CURSORcursor_name[(parameter[,parameter]…)][RETURNreturn_type]ISselectsql;/* CURSOR 用于声明一个游标,一般配合PL/SQL 声明使用... cursor_name 自定义游标名; parameter 可选参数,用于指定参数类型、模式等 return_type 可选,指定游标的返回类型 selectsql: 需要处理的select语句不能含INTO子句 */打开游标opencursor_name;-- cursor_name: 指定的游标名,来打开游标...提取游标FETCHcursor_nameINTOvariable_list;-- cursor_name: 指定的游标名,来打开游标...-- variable_list 用于接收,游标中数据的变量名...注意类型匹配哦,使用时和普通的 SELECT 列1,列2 INTO 变量1,变量2 ... 类似;关闭游标close游标名;-- 使用CLOSE语句关闭一个游标-- 关闭游标后所有资源都将被释放且不能再次被打开实例Demodeclare--声明部分--创建变量Ename varchar2(20);--创建一个静态游标, IS指定SELECTCURSORAAISSELECTEnameFROMEMP;begin--执行部分OPENAA;--打开游标,一般都是第一行声明...--调用游标;FETCHAAINTOEname;--第一次调用dbms_output.put_line(Ename);FETCHAAINTOEname;--第二次调用dbms_output.put_line(Ename);CloseAA;--关闭游标,一般都是最后一行...end;-- 两次分别输出了,两个用户名...读取多行记录时可以采用循环的方式 :LOOP循环WHILE循环FOR循环显示游标的属性属性名称说明%found返回true和false表示是否有数据%isopen返回true和false表示游标是否打开%notfound返回true和false表示是否没有数据一般调用前建议判断是否有值在使用..%rowcount表示受影响的数据一共有多少行WHERE循环条件可以直接设置游标%notfound进行循环…使用显示游标 删除或更新个人看了一段时间才明白, 可能介绍不全… 而且觉得很鸡肋使用游标时候如果处理过程中需要删除 或 更新行可以借鉴下面做法…;在定义游标时候必须使用SELECT…FOR UPDATE语句在执行 DELETE 和 UPDATE时使用WHERE CURRENT OF 子句指定游标当前行详情如下声明CURSOR游标名ISSELECT查询语句FORUPDATE[ofcolumns];-- FOR UPDATE 删除/跟新关键字-- [of columns: 查询语句单表时候,可省略...对于多表的查询,需要根据 of 列;-- 锁定针对某一个表的操作,该游标 删/改只能对于该表...--使用时根据WHERE后面条件...UPDATE表SET列WHERECURRENTOF游标名;DELETEFROM表WHERECURRENTOF游标名;实例Demoselect*fromemp--下面以给工资低于2000的雇员增加100元工资为例说明使用显示游标更新数据的方法。示例如下declarecursoremp_cursorisselectename,salfromempforupdate;v_ename emp.ename%type;--定义变量v_sal emp.sal%type;beginopenemp_cursor;--打开游标loopfetchemp_cursorintov_ename,v_sal;--提取游标数据赋值给变量exitwhenemp_cursor%notfound;ifv_sal2000then--当变量v_sal 小于2000时执行更新操作updateempsetsalsal100wherecurrentofemp_cursor;endif;endloop;closeemp_cursor;--关闭游标end;存储过程将SQL或者PL/SQL代码块集中用于完成特定功能的集合过程的结构声明部分包括类型、变量、游标执行部分完成功能而编写的SQL语句或则是PL/SQL代码块异常处理部分从根本上来讲就是命名的PL/SQL块, 它可以比赋予参数并存储在数据库中可以由一个应用程序或其它PL/SQL调用使用创建存储过程CREATE[ORREPLACE]PROCEDUREprocedure_name[(parameter list)]IS|AS[localvariable declaration]--可选,局部声明;BEGINexecutable statements--可执行语句[EXCEPTIONexception handlers]--可选,异常处理执行...END[procedure_name];--procedure_name: 存储过程名称;--[(parameter list)] 可选,存储过程的参数列表--[OR REPLACE] 可选,如果不包含 OR REPLACE 则表示仅仅新建一个存储过程,存在则报错;-- 如果包含 OR REPLACE 如果已存在则覆盖..实战----------------无参存储过程 myproc1 -----------------------判断员工EMP表,每个员工在对应部门的平均薪资...评价createorreplaceproceduremyproc1as-- 定义变量v_name emp.ename%type;v_sal emp.sal%type;v_dept emp.empno%type;v_dept_avgsal number;v_sql varchar2(200);--动态SQL变量-- 定义游标cursorcur_resultisselectename,sal,deptnofromemporderbydeptno;begin-- 打开游标opencur_result;-- 死循环结束loop-- 调用游标..fetchcur_resultintov_name,v_sal,v_dept;-- 判断条件终止循环..ifcur_result%notfoundthenexit;endif;-- 定义SQLv_sql:select avg(sal) from emp where deptno :a;-- 执行SQL获取 当前部门平均薪资executeimmediate v_sqlintov_dept_avgsalusingv_dept;-- 判断平均薪资ifv_salv_dept_avgsalthendbms_output.put_line(v_name||:很优秀);elsif v_salv_dept_avgsalthendbms_output.put_line(v_name||:良好);elsedbms_output.put_line(v_name||:需要努力了);endif;endloop;--循环结束closecur_result;--关闭游标exception--异常处理whenothersthendbms_output.put_line(程序出异常了);endmyproc1;--存储过程结束;----------------有参存储过程 add_EMP --------------------- 添加员工记录CREATEORREPLACEPROCEDUREadd_EMP(--输入参数eno NUMBER,--员工编号name VARCHAR2,--名称salary varchar2,--薪水job varchar2DEFAULTCLERK,--员工部分,默认CLERKdno number)ISBEGININSERTINTOEMP(empno,ename,sal,job,deptno)VALUES(eno,name,salary,job,dno);END;存储过程创建完, 数据库定义执行完后并不会立刻执行而是在数据库中存在…和 序列 索引 一样存储在某个位置, 方便后面使用时直接调用即可…(数据库/应用程序…执行时可以调用降低数据库和程序之间交流..降低资源)过程的调用与删除调用存储过程EXEC[UTE] 存储过程名( 参数列表 );有参存储过程 参数传递的三种方式--按位置传递参数EXECadd_EMP(3,WSM,1000,CLERK,10);--按名称传递参数EXECadd_EMP(eno3,nameWSM,salary1000,jobCLERK,dno10);--混合方式传递参数前面使用顺序传参,后面使用名称传参EXECadd_EMP(3,WSM,salary1000,jobCLERK,dno10);--但,前面顺序之后使用了名称之后就不可以在使用顺序了,因为顺序有可能已经乱了--注意使用前用户要具有操作 存储过程的授权;--存储过程创建之后只有,创建者和管理员才具有调用操作,如果普通用户需要还要赋权--授权语句GRANTEXECUTEON存储过程名TO用户名;--撤销权限REVOKEEXECUTEON存储过程名FROM用户名;存储过程的参数模式IN用于接受调用程序的值 默认的参数模式OUT用于向调用程序返回值IN OUT用于接受调用程序的值并向调用程序返回更新的值IN表示向存储过程传递参数OUT表示从存储过程返回参数。而IN OUT 表示传递参数和返回参数------------------创建入参和出参的过程---------------------- 根据部门查询出对应 人员姓名和职位;createorreplaceproceduremyproc2-- 声明 有参存储过程( 参数 );(in_deptinnumber,-- in 入参,进入过程执行的参数,需要提供; 这里指对应部门;out_resultoutsys_refcursor-- out 出参,执行过程之后返回的结果参数,称为出参; 这里是一个游标,返回一个结果集;)asv_sql varchar2(500);-- 声明变量sql;beginv_sql:select ename , job from emp where deptno :a;openout_resultforv_sqlusingin_dept;-- 指向sql 给对应占位符;endmyproc2;-- 执行 myproc2 存储过程~declare-- 声明变量,用于接收 或 存储过程需要的参数;v_deptno number:20;-- 参数1v_result sys_refcursor;-- 出参 动态游标:结果v_name varchar2(50);v_job varchar2(50);beginscott.myproc2(v_deptno,v_result);-- 调用存储过程(给出对应的参数 部门no 出餐游标)~loopfetchv_resultintov_name,v_job;ifv_result%notfoundthenexit;endif;dbms_output.put_line(v_name||:||v_job);endloop;closev_result;end;
Oracle数据库学习PL/SQL什么是PL/SQLPL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的扩展语言;使用PL/SQL 可以编写具有很多高级功能的程序虽然通过多个SQL语句也可以实现相同的功能但对于应用程序开发过程中, 需要多次与数据库打交道,PL/SQL可以针对于某一种功能特定的实现…(类似Java的方法) 一定程度上避免了频繁于数据库的请求 (减低服务器压力)优点PL/SQL具有编程语言的特点, 它把一组SQL语句放到一个模块中执行使其更具模块化程序的特点PL/SQL可以采用过程语言的程序结构可以在其中写 逻辑判断 循环…与其它编程语言类似PL/SQL对于程序中的异常也有其响应的处理方式;PL/SQL块具有更好的可移植性; 可以移植到另一个Oracle数据库中…减少了程序网络的交互(数据了访问), 提高程序性能;PL/SQL体系结构PL/SQL块由PL/SQL引擎来编译 和 执行PL/SQL块; 该引擎存在于 Oracle服务器中PL/SQL引擎执行所有过程语句将SQL语句发送给Oracle的SQL语句执行器PL/SQL简介PL/SQL 是一种块结构的语言它把一组SQL语句放到一个模块中执行使其更具模块化程序的特点匿名块是一个未在数据库中命名的PL/SQL块, 在运行时传递到PL/SQL引擎中执行…PL/SQL中可以使用 select insert update delete…事务语句以及sql函数增删改别忘了 COMMIT 提交事务~PL/SQL中不允许直接使用create deop alter 但可以通过动态SQL来执行它们…一个PL/SQL语句由三个部分组成声明部分执行部分异常处理部分PL/SLQ结构块--PL/SQL块基本结构DECLARE-- DECLARE 可选,声明部分:--在此声明PL/SQL用到的变量,常量,类型,游标 以及局部的存储过程和函数;BEGIN-- BEGIN~end 必须项,执行部分:-- 在此编写执行的过程 及 SQL语句,即程序的主要部分;EXCEPTION-- EXCEPTION 可选,指定出现错误时需要执行的操作END;-- end: 表示Pl/sql块的结束,别忘了分号结尾;-- 上面是PL/SQL块的基本结构, BEGIN~END; 是必须的 声明部分和异常处理部分并不是必须的..-- 是PL/SQL中的单行注释 /**/ 多行注释;运算符和表达式关系运算符运算符意义运算符意义小于大于小于或等于大于或等于等于, !, ~, ^不等于一般预算符运算符运算符运算符运算符--------------------------加号-减* 乘/除:赋值号关系号. . 范围运算符︳︳字符连接符逻辑预算符运算符意义运算符意义--------------------------IS NULL空值BETWEEN AND介于两者之间iN在一列值之间AND逻辑与OR逻辑或NOT取反, 如 IS NOT NULL 或 NOT INDECLARE变量的声明语法/** 使用DECLARE 关键字,用于定义变量或者常量: PL/SQL块的可执行部分引用变量和常量前,必须先在DECLARE 对其进行声明; */DECLAREvariable_name[CONSTANT]type[(size)][NOTNULL][:value];BEGINEXCEPTIONEND;-- variable_name:变量名称-- [CONSTANT] :可选表示是否为常量-- type :表示变量的数据类型-- [(size)] :可选,数据类型的长度-- [NOT NULL] :可选,该变量是否可以为空-- [:value] :可选,使用 : 给对于对象赋值 初始值;PL/SQL变量名规范变量命名规则变量名首字母必须是英文字母其后可以是字母、数字或者特殊字符$、#和下划线变量名长度不超过30个字符变量名中不能有空格 不能是SQL保留字 不能用 - (减号)唯一比较好的就是不区分大小写了吧…实例Demo-- scott用户下emp举例select*fromemp;--PL/SQL 根据输入id,获得当前用户名 JOB-- 方式一:PL/SQL块 注意分号~declare--声明变量 id ename job, 每一个变量之间 ; 分号分隔...--id 通过 :值 进行赋默认值,并通过 xxx由Oracle工具输入参数;v_id number :myid;--还可以把: myid手动输入换位固定的 :7934; 固定的id号;v_ename varchar2(50);v_JOB varchar2(50);beginselectename,jobintov_ename,v_JOBfromempwhereempnov_id;dbms_output.put_line(用户名||v_ename||职位: ||v_JOB);end;-- 还可以通过: select into 给变量进行赋值-- select 列1,列2 into 变量1,变量2 form 表 形式进行下标自动映射赋值..因此列类型和变量类型 顺序都要尽量对应-- dbms_output.put_line(); Oracle还可以通过该语句进行数据打印输出...-- 方式二: 解决了不清楚变量与列的类型declarev_id emp.empno%type:id;v_ename emp.ename%type;v_JOB emp.job%type;v_emp emp%Rowtype;beginselectename,jobintov_ename,v_JOBfromempwhereempnov_id;dbms_output.put_line(用户名||v_ename||职位: ||v_JOB);select*intov_empfromempwhereempnov_id;dbms_output.put_line(用户名||v_emp.ename||职位: ||v_emp.job);end;-- 表.列%type: 可以在不清楚列类型时候给设置类型;-- 表%ROWTYPE: 可以在不知道表中列个数 和 数据类型时候,可以通过 表%ROWTYPE 获取一个表类型变量...注意SELECT 语句在PL/SQL中的写法稍微有改变,SELECT INTO 查询结果只能返回一条记录, 并赋值到变量中保存如果返回多条数据或没有都会报错;PL/SQL 中对于多条记录的查询, 后面会学习游标进行存储多个结果集;PL/SQL控制语句PL/SQL可以通过控制结构, 来控制命令执行的流程。标准的SQL 没有流程控制的概念, 而PL/SQL提供了丰富的流程控制语句;控制结构共有三种类型条件控制 循环控制 顺序控制条件控制条件控制用于控制条件执行一系列的语句条件控制包括 IF语句 和 CASE语句IF-THEN语句IF布尔表达式THENPL/SQL和SQL执行语句ENDIF;IF-THEN-ELSE语句IF布尔表达式THENPL/SQL和SQL执行语句1ELSEPL/SQL和SQL执行语句2ENDIF;IF-THEN-ELSIF语句IF布尔表达式1THENPL/SQL和SQL执行语句1ELSIF 布尔表达式2THEN--注意是ELSIF而不是ELSEIFPL/SQL和SQL执行语句2ELSEPL/SQL和SQL执行语句3ENDIF;CASE类似于Java的Switch在Oracle 9i 后引入依据表达式选择相应的when子句执行CASE条件表达式WHEN值1THENPL/SQL和SQL执行语句1;WHEN值2THENPL/SQL和SQL执行语句2;.....WHEN值nTHENPL/SQL和SQL执行语句n;[ELSEelse_statements;]--可选相当于default 都不符合则执行..ENDCASE;循环控制循环控制用户重复执行一系列语句循环控制包括: LOOP WHERE FOREXIT 语句用于退出循环EXIT WHERE 条件语句条件成立则退出循环…LOOP死循环LOOPPL/SQL和SQL执行语句;ENDLOOP;--实现计数器功能当计数器为10或者大于10时退出DECLAREv_countinteger:1;--声明变量BEGINLOOP--死循环开始v_count:v_count1;--变每次1IFv_count10THEN--判断变量 大于等于10EXIT;--退出循环ENDIF;--IF 结束ENDLOOP;--死循环结束END;--PL/SQL块结束WHILE-LOOP循环:类似于while循环WHILE布尔表达式LOOPPL/SQL和SQL执行语句;ENDLOOP;FOR-LOOP循环:类似与for循环FOR变量名in最小值..最大值loop循环执行的语句ENDLOOP;-- ..两点表示在其中范围~eg: 1..20 循环从1开始20结束;beginforiin1..20loopinsert表value(i,值1,值2,值3);COMMIT;--提交事务~endloop;COMMIT;--或放在最后统一提交事务~-- 后面在加一个异常处理如果出现异常 ROLLBACK; 回滚数据;end;顺序控制控制语句用户按顺序执行语句NULL个人觉得用的不多…--NULL:-- NULL语句可以是一个可执行的语句,相当于一个占位符,不会执行任何的操作 的空语句;-- 它可以使某些语句变的有意义,提高程序的可读性,保证其它语句结构的完整性 和 正确性;DECLAREv_count Number :5;--声明变量BEGINIFv_count10thennull;--IF中需要有执行,不然报错; 为了使结构成立有意义使用 NULLElsedbms_output.put_line(值太小的||v_count);ENDIF;END;动态SQL什么是动态SQL编译期间SQL 语句是不确定的并且在运行时允许发生变化动态SQL应用场合要执行一个DDL 语句时 需要增加程序的灵活性时 使用包DBMS_SQL动态执行SQL语句时;动态SQL简直是PL/SQL的一大亮点而且可以搭配条件控制…使用而产生不同的SQL语句用于执行不同的操作--动态sql--查询用户总共有多少表selectcount(1)fromdba_tablesselectcount(1)fromdba_tableswheretable_nameupper(WSM);--查询所有表select*fromdba_tables--查询用户存在WSM表吗 如果有删除/没有则创建; 注意切换一个可以创建的用户哦System用户declarev_count number;--用于判断是否存在表;v_sql varchar2(500);--执行的sql变量,varchar2类型长度500 毕竟存储的是sql长一点好~beginselectcount(1)--获取countintov_countfromdba_tableswheretable_nameupper(WSM);--upper() 方法将字符串中的小写字母转为大写字母ifv_count0then--判断是否存在v_sql :drop table WSM;--拼接SQLelse--else 拼接SQLv_sql :create table WSM(||id number,||name varchar2(20)||);endif;--执行动态sqlexecuteimmediate v_sql;end;-- 前面说PL/SQL中不可以使用 DDL语句;-- 而 execute immediate SQL; 可以解决该问题...EXECUTE IMMEDIATE语句EXECUTE IMMEDIATE dynamic_sqldynamic_sql: 表示一个SQL语句或者一个PL/SQL语句块的字符串表达式, 可以通过EXECUTE IMMEDIATE语句执行…常用语法--基本语法BEGINexecuteimmediate执行的SQL;END;-- 如果在存储过程 或 PL/SQL块执行还可以;-- 给变量进行赋值 INTO-- 对于需要参数执行的SQL(增删改查条件数据..),通过 Using 传参(根据顺序进行参数匹配注意类型也对应哦..);executeimmediateselect 列1,列2 from 表 where id:idinto变量1,变量2USING条件id;declarev_name varchar2(20);beginexecuteimmediateselect ename from emp where empno:aintov_nameUSING7369;--USING指定 7369编号;dbms_output.put_line(v_name);end;/* 注意事项 EXECUTE IMMEDIATE将不会提交一个DML事务执行应该显式提交(对于增删改需要 commit; ) 当执行SQL语句时不要用分号当执行PL/SQL块时在其尾部用分号. 对于处理动态语句,EXECUTE IMMEDIATE 比以前可能用到的更容易并且更高效. */对于execute 是真的非常好用, 尤其在存储过程~ 但是对于拼接一些特殊字符要非常注意比如引号 ‘ 这真是恶心死我了搜了很多有的是 用 ’‘’ 三个引号间隔拼接为一个 ‘ 的对于新手非常难受;可以使用ASCLL码来代替引号或是其它特殊字符~chr(39) 就表示一个单引号~ 39 ASCLL码表示一个 单引号;这是我在完成存储过程中的一个条件查询拼接时间条件范围查询v_where: v_where|| AND EXP_DATE TO_DATE(||chr(39)||v_qdate||chr(39)||,||chr(39)||yyyy-fmmm-fmdd||chr(39)||);当前时写 简直恶心死了,先将字符转换为时间进行判断, 过程还要当心引号异常处理 EXCEPTION什么是异常在运行过程中出现的错误叫做异常,发生异常后, 语句将停止执行PL/SQL引擎立即将控制权转到PL/SQL的异常处理部分注意这里说的是 执行过程中的异常,并不包含编译的异常…异常处理exception用来处理正常执行过程中未预料的事件…两种比较经典的异常预定义异常和用户自定义异常预定义异常Oracle预定义异常情况大约有 24个,对于这种异常情况的处理无须在程序中定义, 可由Oracle自动引发…当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发常见预定义异常OTHERS处理程序异常不会漏过任何异常, 它相当于Java的 Exception 无论什么异常都可以捕获所以一般声明在 最后, 进行捕获遗落的异常处理…图上代码实例异常处理之后加一个 ROLLBACK; 回滚一下效果更好…实例意思是循环新增三个商品类别(当前数据有点随便…) 如果过程中有异常进行捕获处理…则插入成功用户自定义异常用户可以在 PL/SQL 块的声明部分定义异常自定义的异常通过RAISE语句显式引发;实例Demo-- 切换Scott用户select*fromemp-- 根据输入id 查询对应用户工资declarev_empno number :myid;v_ename varchar2(50);v_sal number;e_exp exception;-- exception异常类型;begin-- 查询数据赋值;selectename,salintov_ename,v_salfromempwhereempnov_empno;-- 打印输出dbms_output.put_line(v_ename||点击量:||v_sal);ifv_sal2000then-- 如果工资小于两千raise e_exp;-- 抛出异常..endif;exceptionwhenno_data_foundthendbms_output.put_line(没有找到数据);rollback;whene_expthen-- 处理手动异常...dbms_output.put_line(工资小于两千);rollback;whenothersthen-- 处理任何异常..dbms_output.put_line(程序异常);rollback;end;扩除了以上的异常情况处理…RAISE_APPLICATION_ERROR还可以重新定义异常错误消息为应用程序提供一种与Oracle交互方法d语法RAISE_APPLICATION_ERROR(err_number,err_message);err_number: 表示为用户异常标识指定的编号, 注意范围必须是 -20999~20000之间负整数’err_message: 用户为异常指定的文本信息…长度可达 2049字节…游标游标基本原理Oracle 在执行一个包含 SELECT INSERT DELETE UPDATE …的语句时候会在内存中创建出一个缓存区将执行结果放在这个缓冲区中, 而游标是指向该区的一个指针…游标分类Oracle中提供了两种游标类型,静态游标(本次讲解)动态游标静态游标 是在编译器就知道明确的SELECT 语句的游标…又细分为 显示游标/隐式游标声明游标CURSORcursor_name[(parameter[,parameter]…)][RETURNreturn_type]ISselectsql;/* CURSOR 用于声明一个游标,一般配合PL/SQL 声明使用... cursor_name 自定义游标名; parameter 可选参数,用于指定参数类型、模式等 return_type 可选,指定游标的返回类型 selectsql: 需要处理的select语句不能含INTO子句 */打开游标opencursor_name;-- cursor_name: 指定的游标名,来打开游标...提取游标FETCHcursor_nameINTOvariable_list;-- cursor_name: 指定的游标名,来打开游标...-- variable_list 用于接收,游标中数据的变量名...注意类型匹配哦,使用时和普通的 SELECT 列1,列2 INTO 变量1,变量2 ... 类似;关闭游标close游标名;-- 使用CLOSE语句关闭一个游标-- 关闭游标后所有资源都将被释放且不能再次被打开实例Demodeclare--声明部分--创建变量Ename varchar2(20);--创建一个静态游标, IS指定SELECTCURSORAAISSELECTEnameFROMEMP;begin--执行部分OPENAA;--打开游标,一般都是第一行声明...--调用游标;FETCHAAINTOEname;--第一次调用dbms_output.put_line(Ename);FETCHAAINTOEname;--第二次调用dbms_output.put_line(Ename);CloseAA;--关闭游标,一般都是最后一行...end;-- 两次分别输出了,两个用户名...读取多行记录时可以采用循环的方式 :LOOP循环WHILE循环FOR循环显示游标的属性属性名称说明%found返回true和false表示是否有数据%isopen返回true和false表示游标是否打开%notfound返回true和false表示是否没有数据一般调用前建议判断是否有值在使用..%rowcount表示受影响的数据一共有多少行WHERE循环条件可以直接设置游标%notfound进行循环…使用显示游标 删除或更新个人看了一段时间才明白, 可能介绍不全… 而且觉得很鸡肋使用游标时候如果处理过程中需要删除 或 更新行可以借鉴下面做法…;在定义游标时候必须使用SELECT…FOR UPDATE语句在执行 DELETE 和 UPDATE时使用WHERE CURRENT OF 子句指定游标当前行详情如下声明CURSOR游标名ISSELECT查询语句FORUPDATE[ofcolumns];-- FOR UPDATE 删除/跟新关键字-- [of columns: 查询语句单表时候,可省略...对于多表的查询,需要根据 of 列;-- 锁定针对某一个表的操作,该游标 删/改只能对于该表...--使用时根据WHERE后面条件...UPDATE表SET列WHERECURRENTOF游标名;DELETEFROM表WHERECURRENTOF游标名;实例Demoselect*fromemp--下面以给工资低于2000的雇员增加100元工资为例说明使用显示游标更新数据的方法。示例如下declarecursoremp_cursorisselectename,salfromempforupdate;v_ename emp.ename%type;--定义变量v_sal emp.sal%type;beginopenemp_cursor;--打开游标loopfetchemp_cursorintov_ename,v_sal;--提取游标数据赋值给变量exitwhenemp_cursor%notfound;ifv_sal2000then--当变量v_sal 小于2000时执行更新操作updateempsetsalsal100wherecurrentofemp_cursor;endif;endloop;closeemp_cursor;--关闭游标end;存储过程将SQL或者PL/SQL代码块集中用于完成特定功能的集合过程的结构声明部分包括类型、变量、游标执行部分完成功能而编写的SQL语句或则是PL/SQL代码块异常处理部分从根本上来讲就是命名的PL/SQL块, 它可以比赋予参数并存储在数据库中可以由一个应用程序或其它PL/SQL调用使用创建存储过程CREATE[ORREPLACE]PROCEDUREprocedure_name[(parameter list)]IS|AS[localvariable declaration]--可选,局部声明;BEGINexecutable statements--可执行语句[EXCEPTIONexception handlers]--可选,异常处理执行...END[procedure_name];--procedure_name: 存储过程名称;--[(parameter list)] 可选,存储过程的参数列表--[OR REPLACE] 可选,如果不包含 OR REPLACE 则表示仅仅新建一个存储过程,存在则报错;-- 如果包含 OR REPLACE 如果已存在则覆盖..实战----------------无参存储过程 myproc1 -----------------------判断员工EMP表,每个员工在对应部门的平均薪资...评价createorreplaceproceduremyproc1as-- 定义变量v_name emp.ename%type;v_sal emp.sal%type;v_dept emp.empno%type;v_dept_avgsal number;v_sql varchar2(200);--动态SQL变量-- 定义游标cursorcur_resultisselectename,sal,deptnofromemporderbydeptno;begin-- 打开游标opencur_result;-- 死循环结束loop-- 调用游标..fetchcur_resultintov_name,v_sal,v_dept;-- 判断条件终止循环..ifcur_result%notfoundthenexit;endif;-- 定义SQLv_sql:select avg(sal) from emp where deptno :a;-- 执行SQL获取 当前部门平均薪资executeimmediate v_sqlintov_dept_avgsalusingv_dept;-- 判断平均薪资ifv_salv_dept_avgsalthendbms_output.put_line(v_name||:很优秀);elsif v_salv_dept_avgsalthendbms_output.put_line(v_name||:良好);elsedbms_output.put_line(v_name||:需要努力了);endif;endloop;--循环结束closecur_result;--关闭游标exception--异常处理whenothersthendbms_output.put_line(程序出异常了);endmyproc1;--存储过程结束;----------------有参存储过程 add_EMP --------------------- 添加员工记录CREATEORREPLACEPROCEDUREadd_EMP(--输入参数eno NUMBER,--员工编号name VARCHAR2,--名称salary varchar2,--薪水job varchar2DEFAULTCLERK,--员工部分,默认CLERKdno number)ISBEGININSERTINTOEMP(empno,ename,sal,job,deptno)VALUES(eno,name,salary,job,dno);END;存储过程创建完, 数据库定义执行完后并不会立刻执行而是在数据库中存在…和 序列 索引 一样存储在某个位置, 方便后面使用时直接调用即可…(数据库/应用程序…执行时可以调用降低数据库和程序之间交流..降低资源)过程的调用与删除调用存储过程EXEC[UTE] 存储过程名( 参数列表 );有参存储过程 参数传递的三种方式--按位置传递参数EXECadd_EMP(3,WSM,1000,CLERK,10);--按名称传递参数EXECadd_EMP(eno3,nameWSM,salary1000,jobCLERK,dno10);--混合方式传递参数前面使用顺序传参,后面使用名称传参EXECadd_EMP(3,WSM,salary1000,jobCLERK,dno10);--但,前面顺序之后使用了名称之后就不可以在使用顺序了,因为顺序有可能已经乱了--注意使用前用户要具有操作 存储过程的授权;--存储过程创建之后只有,创建者和管理员才具有调用操作,如果普通用户需要还要赋权--授权语句GRANTEXECUTEON存储过程名TO用户名;--撤销权限REVOKEEXECUTEON存储过程名FROM用户名;存储过程的参数模式IN用于接受调用程序的值 默认的参数模式OUT用于向调用程序返回值IN OUT用于接受调用程序的值并向调用程序返回更新的值IN表示向存储过程传递参数OUT表示从存储过程返回参数。而IN OUT 表示传递参数和返回参数------------------创建入参和出参的过程---------------------- 根据部门查询出对应 人员姓名和职位;createorreplaceproceduremyproc2-- 声明 有参存储过程( 参数 );(in_deptinnumber,-- in 入参,进入过程执行的参数,需要提供; 这里指对应部门;out_resultoutsys_refcursor-- out 出参,执行过程之后返回的结果参数,称为出参; 这里是一个游标,返回一个结果集;)asv_sql varchar2(500);-- 声明变量sql;beginv_sql:select ename , job from emp where deptno :a;openout_resultforv_sqlusingin_dept;-- 指向sql 给对应占位符;endmyproc2;-- 执行 myproc2 存储过程~declare-- 声明变量,用于接收 或 存储过程需要的参数;v_deptno number:20;-- 参数1v_result sys_refcursor;-- 出参 动态游标:结果v_name varchar2(50);v_job varchar2(50);beginscott.myproc2(v_deptno,v_result);-- 调用存储过程(给出对应的参数 部门no 出餐游标)~loopfetchv_resultintov_name,v_job;ifv_result%notfoundthenexit;endif;dbms_output.put_line(v_name||:||v_job);endloop;closev_result;end;
