Oracle PL/SQL

Sequence

1. What is sequence?
   In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.
2. How to use.

-- Syntax
CREATE SEQUENCE sequence  --创建序列名称

       [INCREMENT BY n]  --递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1

       [START WITH n]    --开始的值,递增默认是minvalue 递减是maxvalue

       [{MAXVALUE n | NOMAXVALUE}] --最大值

       [{MINVALUE n | NOMINVALUE}] --最小值

       [{CYCLE | NOCYCLE}] --循环/不循环

       [{CACHE n | NOCACHE}]; --分配并存入到内存中,

什么是cache?

-- sqquence 的属性
  NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用

  CURRVAL 中存放序列的当前值

  NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效

-- 举个栗子
	Create sequence seqEmp increment by 1 start with 1 maxvalue 3 minvalue 1 Cycle cache 2;
	
	-- 先nextval 后 currval
	
	Select seqEmp.nextval  from dual; --[什么是doal](https://www.cnblogs.com/shoshana-kong/p/8697131.html)
	
	Select seqEmp.currval  from dual;

Cursor

1. What is Cursors?
   In Oracle, a cursor is a mechanism by which you can assign a name to a SELECT statement and manipulate the information within that SQL statement.
2. How to use?

	Declare
	   cnumber number;
	   -- 1.Declare a Cursor
	   CURSOR courses IS
	     SELECT course_number
	     FROM courses_tbl
	     WHERE course_name = name_in;
	BEGIN
	   OPEN courses; -- 2.OPEN Statement
	   -- loop traversal
	   loop 
		   FETCH courses INTO course; -- 3.FETCH Statement
		   EXIT WHEN courses%notfound;
		   dbms_output.put_line('course name:'||course.name||'course score:'||course.score);
	   end loop;
	   -- for traversal
	   for course in courses loop
	   	   dbms_output.put_line('course name:'||course.name||'course score:'||course.score);
	   end loop;
	   CLOSE courses;-- 4.CLOSE Statement
	END;

Exception

1. What is a named system exception in Oracle?
   Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.Oracle has a standard set of exceptions already named as follows:

Oracle Exception Name	Oracle Error	Explanation
TIMEOUT_ON_RESOURCE	ORA-00051	You were waiting for a resource and you timed out.
TOO_MANY_ROWS	ORA-01422	You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE	ORA-01476	You tried to divide a number by zero.
VALUE_ERROR	ORA-06502	You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
NO_DATA_FOUND	ORA-01403	You tried one of the following: 1.You executed a SELECT INTO statement and no rows were returned. 2.You referenced an uninitialized row in a table. 3. You read past the end of file with the UTL_FILE package.

more…

2. How to use?

	declare
		n number;
		vrow emp%rowtype;
	begin
		-- n := 5/0;
		select * into vrow from emp
	exception
		when zero_divide then 
			dbms_output.put_line("zero_divide");
		when too_many_rows then
			dbms_output.put_line("too_many_rows");
		when others then
			dbms_output.put_line("others exception : " || sqlerrm);-- sqlerrm : sql error message
	end

--user-defined exception
	declare
	  cursor vrows is select * from vagt_pin where agt_key = '4585111';
	  crow vagt_pin%rowtype;
	  is_emply exception; 
	begin
	  open vrows;
	  fetch vrows into crow;
	  if vrows%notfound then 
	    raise is_emply;
	  end if;
	exception
	  when is_emply then 
	    dbms_output.put_line('aaa');
	end;

Procedure

1. Symtax
   
2. Example

	create or replace procedure select_dept(
	  num_deptno in number,
	  var_dname out dept.dname%type,
	  var_loc out dept.loc%type) is
	begin
	  select dname,loc
	  into var_dname,var_loc
	  from dept
	  where deptno = num_deptno;
	exception
	  when no_data_found then 
	    dbms_output.put_line('This department doesnt exist');
	end;

	DECLARE
	    var_dname dept.dname%type;
	    var_loc dept.loc%type;
	BEGIN
	    select_dept(1,var_dname,var_loc);
	    DBMS_OUTPUT.PUT_LINE('dept name is:'||var_dname||',location is:'||var_loc);
	END;

1). What are NOCOPY, IN, OUT? reference

	-- For Example 1
	DECLARE
		n NUMBER := 10;
		PROCEDURE do_something (n1 OUT NUMBER) IS
	BEGIN
		dbms_output.put_line('before assign: ' || n1); -- prints none <<1>>
		n1:=20; 
		dbms_output.put_line('before return: ' || n); -- prints 10 <<2>>
	END;

	BEGIN
		do_something(n);
		dbms_output.put_line('after return: ' || n); -- prints 20
	END;
	/*
	print result :
		before assign: 
		before return: 5
		after return: 20 
	*/
	
	-- For Example 2
	DECLARE
		n NUMBER := 10;
		PROCEDURE do_something (
		n1 IN NUMBER,
		n2 IN OUT NUMBER,
		n3 IN OUT NOCOPY NUMBER) IS
	BEGIN
		n2 := 20;
		dbms_output.put_line(n1); -- prints 10<<1>>
		n3 := 30;
		dbms_output.put_line(n1); -- prints 30 <<2>>
	END;
	
	BEGIN
		do_something(n, n, n);
		dbms_output.put_line(n); -- prints 20 <<3>>
	END;

2). What are @ROWTYPE and @TYPE?

How to Debugger?

DECLARE   
  V_ORG_NAME SF_ORG.ORG_NAME%TYPE;
  V_PARENT_ID SF_ORG.PARENT_ID%TYPE;
BEGIN  
  SELECT ORG_NAME,PARENT_ID INTO V_ORG_NAME,V_PARENT_ID  
  FROM SF_ORG SO  
  WHERE SO.ORG_ID=&ORG_ID;  
  DBMS_OUTPUT.PUT_LINE('部门名称：' || V_ORG_NAME);  
  DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_PARENT_ID));  
END;  

Reference

1. https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm
2. https://www.cnblogs.com/yjhlsbnf/p/7749127.html
3. https://blog.csdn.net/kkdelta/article/details/4698399