Summary of PL/SQL knowledge points

Concept

• PL/SQL (procedural language/sql) is an Oracle-specific language that is extensible on SQL, not only for writing SQL languages, but also for defining variables and constants.
• The stored procedures, functions, etc., written by PL/SQL, can improve efficiency and eliminate compilation time when traditional programs interact with the database. (Example: A traditional Java program accesses a database with SQL statement Access, SQL statements need to be compiled to be recognized by the database)

Writing stored Procedures

• Sql>

  Create or replace procedure Sp_pro1 is

  2 begin

  3 INSERT INTO test values (2, ' wangxin ', ' Male ', to_date (' 1994-03-19 ', ' yyyy-mm-dd '));

  4 End;

  5/

  Procedure created

• Call: Exec procedure name (parameter 1, parameter 2, ...) ）

• Invocation: Call Procedure Name (parameter 1, parameter 2, ...)

Variable

• The variable is defined with a V prefix, for example: v_sal;
• Define constants with C prefixes;
• Defining cursors with _cursor as suffixes
• Prefix with E when defining exceptions

PL/SQL block

• Three parts:

  Declear

  Definition section

  Begin

  Processing part

  exception

  Exception Handling Section

  End

• Set Serveroutput on/off: Sets whether the console output is on or off, and the console does not display data.

Sample program:

declarev_userid int;//定义一个变量v_useridbegin select userid into v_userid from test where username=&name;//选择userid列，并将得到的值放入到v_userid中，&name：代表用户手动输入一个值，保存到name中，并替代name。dbms_output.put_line(‘用户名‘|| v_userid);// “||” 代表字符串连接符的作用，dbms_output是系统自带的包，类似java的包名.类名。put_line是系统自带的存储过程。end;/如果出现异常情况：例如，没有符合条件的记录，那么，就会发生异常，此时要对异常进行捕获。beginxxx；exceptionwhen 异常名字 then 进行何种操作；end；

Function

create function func1(usid int) return varchar2 is uname varchar2(20);//usid为传入参数名字，return varchar2表示返回值类型，is是关键字，uname varchar2（20），代表返回值具体接收变量beginselect username into uname from test where userid=usid;return uname;//执行后需要有的返回值，跟java语法类似。end;/

Package

create package 包名 isprocedure  过程名（参数1，参数2,...）；....function  函数名（参数1,参数2,...）return number（这里指表明返回值，不是一定写number）;....create package body 包名 isprocedure 过程名（参数） isbeginxxxxxend;...function 函数名（参数）return 类型 is 变量名 类型;beginxxxxreturn 变量;end;end;

PL/SQL record type (similar to struct)

declare type 类型名 is record（name 类型，xxx 类型，...）;//；这里定义一个新类型，就像结构体中定义一个//新的类型一样。新变量名 类型名//定义一个新的变量，类型是自己设定的这个类型。beginxxx操作end；

PL/SQL table (equivalent to array)

declaretype 新类型名 is table of 类型名 index by binary_integer；//因为table相当于数组，数组中类型固定，所以，of 类型名将统一设定一个类型，后面的index by xxxx是对下标的限定，这里表示下标为整数，可正可负。变量名 新类型名；beginxxx操作end；

PL/SQL Reference variable cursor variable REF CURSOR

declaretype 新类型名 is ref cursor;新变量 新类型名接收变量1 emp.ename%type;接收变量2 emp.sal%type;begin//这里需要将游标和select结合open 新变量 for select ename，sal from emp where deptno=&no;loop //循环执行语句fetch 新变量 into 接收变量1，接收变量2；//fetch是从游标变量取，此时游标变量相当于resultset这个类了，开始指向第一个元组。xxxxx//对数据进行其他处理 exit when test_cursor%notfound;//结束循环条件end loop;end;

Special east table name. Column name%type: Sometimes the value of the column inside the length is not fixed, in the outside can not be judged, directly write VARCHAR2 (num) Such words may also be wrong, so, write this way, you can assign a value number: = is the assignment number bound variable

Sql>var variable name type;
SQL>EXEC: Variable Name: = value;//variable name in front of: not less, this means that he is bound variable
Sql>success (This is a hint message, omit shorthand);

Can also be written like this

• Sql>var variable name type;
• Sql>begin
• SQL>: variable name: = value;
• sql>end;
• sql>/;
• Sql>succcess

The above mentioned is only the assignment success, want to see value, need this

• Sql>print variable name;

Conditional statements

1.编写一个过程，可以输入一个雇员名，如果雇员工资低于2000，就给该雇员工资增加10%；create procedure sp_pro1(empname varchar2) issalary emp.sal%type;beginselect sal into salary from emp where ename=epname;if salary<2000 then update empset salary=salary*1.1where ename=epname;end if;end;2.编写一个存储过程，可以输入一个雇员名，如果该雇员的补助不是0，就在原来的基础上增加100，如果补助是0，那么久把补助设置为200.create procedure sp_pro2(epname varchar2) isnewbuzhu emp.buzhu%type;beginselect buzhu into newbuzhu from empwhere ename=epname;if newbuzhu!=0then update emp set buzhu=buzhu+100where ename=epname;else update emp set buzhu=200where enmae=epname; end if;end;3.如果有多个条件的话，else if 应该写成 elsif，这样才对，然后接着是then。

Looping statements

1. loop   end loop;编写一个过程，可输入用户名，并循环添加10个用户到users表里面，用户编号从1开始增加。create procedure sp_pro3(epname varchar2) isno number:=1;beginloopinsert into users values(no,epname);no：=no+1；exit when no=11;end loop;end;2. while 条件 loop  xxxx操作 end loop；create procedure sp_pro4(epname varchar2) isno number:=1;beginwhile no<=10 loopinsert into users values(no,epname);no：=no+1；end loop;end;3. for循环；create procedure sp_pro5(epname varchar2) isno number:=1;beginfor i in 1..10 loopxxxxend loop;end;

Sequential control statements

1.null,主要是起到增强可读性的作用。例如java中这样if（xxx）{    xxx；}else{}else里面没有任何东西，显得可读性差，于是，这plsql里面if xxx thenelse null；end if；增强可读性

To return a stored procedure for a result set

1.首先创建一个包（包用来存放定义的游标类型，因为这个类型需要被存储起来，因此包起到了一个存储的作用）Create or replace package testpackage is    type test_cursor is ref cursorend；2.创建过程create procedure sp_pro6(spno in number,p_cursor out testpackage.test_cursor) isbeginopen p_cursor for select * from emp where deptno=spno;end;

Writing a paging process

1.编写一个存储过程，要求输入表名，每页显示记录数，当前页，返回总记录数，总页数，和返回的结果集。create procedure sp_pro7(tableName varchar2,pageCount number,currentPage number,totalCount out number,totalPage out number,total_cursor out testpackage.test_cursor) isbegin open total_cursor for select * from (select t1.*,rownum from (select * from emp)t1 where rownum>((currentPage-1)*pageCount))t2 where t2.rownum<=(currentPage* pageCount);select count(*) into totalCount from emp;if mod(totalCOunt,pageCount)=0then totalPage:=totalCount/pageCount;else totalPage:=totalCount/pageCount+1;end if;close total_cursor;end;//说明：open游标后要记得关闭，否则在其他地方使用相同游标会报错，原因是你打开它就相当于获取了使用权，有点类似于synchronized一样，一次只能一个在使用，所以要记得关闭。

Cursor, this cursor is not the type of cursor just

为什么说预编译的sql会效率高，因为预编译的结构会注册hash-bucket，这叫做父游标，存储sql的文本，然而即使有时sql文本相同，但是仍然还有一些其他因素不同，导致sql执行重新编译，于是这父游标下面会有子游标记录着其他那些表信息，执行计划等等。如果找到父，子游标都相同的sql执行，那么就不必重新编译了。

Execution plan

执行计划是查询优化器根据sql语句和当前表结构以及内部数据多少等信息自动生成的一条查询计划。

Exception Handling (Exception)

1.预定义例外：系统自带,在编写plsql语句时的例外2.非预定义例外：处理预定义例外不能处理的，例如用户登录时出错，一般不是plsql语法级别的的。3.自定义例外：处理与oracle系统错误无关的错误如何自定义一个例外？在声明部分定义例外名 exception，例如：myexc exception；这样就定义了一个例外。例外需要使用条件判断语句进行判断，才能知道是否满足抛出例外的条件。抛出语句是：raise myexc;然后在后面声明exception when exc then xxxxx;举例：create procedure xxx() ismyexc exception;beginxxxif sql%notfound then raise exc;else null;end if;exception when exc then xxx;end;

Four cursor parameters

一般只要进行Dml语句，即增删改查，那么就会语句的属性值就会保存在四个游标中，这四个游标分别是：sql%found，当增删改查都正确执行时，即增加语句执行，有一行被改变，删除语句执行，有一行被改变，etc，它得罪值是true，反之，值为falsesql%notfound，它的值相反。sql%rowcount，可以理解为返回元组行数，增删改返回值都是0，只有select会不止为0.sql%isopen,只有上面提到的那种显示的声明游标打开之后才会为true，隐式游标只有上面三个参数。默认的sql%isopen为false，所以并不怎么使用，因为值固定，无意义。

View

1.视图是由表产生的，是虚拟的，2.视图不能创建索引。3.视图增加效率4.提高安全性下面来创建视图create (or replace)view viewname is select * from emp where salry>10000;使用视图与表操作是一样的，也可以进行增删改查（crud）;

Summary of PL/SQL knowledge points