Xpert 基础

----------------------- 01 -------------------------------
declare
    cursor emp_cur is
        select empname 
          from company
          where name = 'asdf';
    emp_rec emp_cur%rowtype;
begin
    open emp_cur;
    loop
        fetch emp_cur into emp_rec
        exit when emp_cur%notfound;
        give_raise(emp_rec.empno);
    end loop;
    close emp_cur;
end;

-- for style, 不用open 和 close 游标
declare
    cursor emp_cur is
        select empname 
          from company
          where name = 'asdf';
begin
    for emp_rec in emp_cur
    loop
        give_raise(emp_rec.empno);
    end loop;
end;
------------------------ 02 ---------------------------------
-- cursor parameter
-- cursor parameters use the same structure as the parameter list for procedures and function.
-- 只能使用 in 模式, 不是使用OUT, IN OUT 模式
cursor company_cur(id_in company.company_id%type,
                    status_in in company.cstatus%type := 'O')
        is 
        select * from company
         where company_id = id_in
           and status = status_in;

----------------------- 03 --------------------------------------
-- cursor in package, 有两种方法, 第一种是定义cursor同时制定select, 这种跟普通的pl/sql块一样
-- 另外一种是, 在package中只定义一个header, 并指定 return, 在 packagebody 中完整定义cursor
create or replace package book_info
is
    cursor byauthor_cur( author_in IN books.author%type) 
    is
        select * from books where author = author_in;
    
    -- return type 直接使用 %rowtype
    cursor bytitle_cur( title_filter_in in books.title%type)
        return books%rowtype;
    
    -- 自己定义的行记录
    type author_summary_rt is record(
        author books.author%type,
        total_page_count pls_integer,
        total_book_count pls_integer);
    
    -- 返回自己定义的行记录类型
    cursor summary_cur( author_in in books.author%type)
        return author_summary_rt;
end book_info;

cursor

-- 01 变量名--
cursor company_cur    -- cursor
c_constant_data     -- constants
v_varibale_value    -- variable
company_id_in        -- parameter
company_rec            -- record

-- 02 尽量使用间接
-- 使用 %rowtype, %type
-- 使用 常量等, 不要直接使用数字等, 比如
min_diff constant number := 1;
max_diff constant number := 100;
if footing between min_diff and max_diff then    -- 好

if footing between 1 and 100 then -- 不好

变量

/*
 If the loop executes for a fixed number of times (by number or number of records), use the FOR loop.
 If you want to make sure the loop executes at least once, use the simple loop.
 If you want the termination condition evaluated before the body executes, use the WHILE loop.
 */

-- If the code used to initialize is similar to the body, then switch to a simple loop. For example, 
-- the following WHILE loop relies on two fetch statements:
OPEN emp_cur;
FETCH emp_cur INTO emp_rec;        -- FETCH 第一次
WHILE emp_cur%FOUND AND
      emp_rec.sal < avg_sal
LOOP
   
   FETCH emp_cur INTO emp_rec;    -- FETCH 第二次
END LOOP;

-- 所以, 以上应该使用 简单循环, 这样就只 FETCH 了一次
LOOP
   FETCH emp_cur INTO emp_rec;
   EXIT WHEN emp_cur%NOTFOUND OR
        emp_rec.sal >= avg_sal;
END LOOP;

loop

/*
 Do not use PL/SQL where you can use a SQL statement instead. 
 The SQL statement will often be much faster. You should replace PL/SQL loops 
 with single SQL statements when possible.
 */

-- lower PL/SQL Version:

FOR year_count IN 1 .. 20
LOOP
   INSERT INTO v1table1 
      SELECT * FROM v1table2
       WHERE yr_nu = year_count; 
END LOOP;

-- Faster, Simpler SQL Version:
INSERT INTO v1table1
   SELECT * FROM v1table2
    WHERE yr_nu BETWEEN 1 AND 20;

SQL FASTER THAN PL/SQL

-- Never Use a RETURN Statement Inside a Loop

-- 错误的方法
BEGIN
   the_rowcount := Get_Group_Row_Count( rg_id );
   
   FOR j IN 1..the_rowcount 
   LOOP
   
      col_val := Get_Group_Char_Cell( gc_id, j );
   
      IF UPPER(col_val) = UPPER(the_value) 
      THEN
         RETURN j;
      END IF;
   
   END LOOP;
END;

/*
 Once again, if the loop should be conditionally terminated, do not use a FOR loop. 
 Instead, use a WHILE or infinite loop and then issue the RETURN after the loop is completed. 
 The following code replaces the unstructured IF statement shown above:
 */

BEGIN
/* Initialize the loop boundary variables. */
   row_index := 0;
   the_rowcount := Get_Group_Row_Count (rg_id);
   
   /* Use a WHILE loop. */
   WHILE row_index <= the_rowcount AND 
          match_not_found 
   LOOP
         row_index := row_index + 1;
         col_val := Get_Group_Char_Cell (gc_id, row_index);
         match_not_found := UPPER (col_val) != UPPER (the_value) 
   END LOOP;
   
   /* Now issue the RETURN statement. */
   RETURN row_index;
END;

Return Vs Loop

FUNCTION func_name (...) RETURN datatype 
 IS
    /* Variable for RETURN */
    return_value datatype;
 BEGIN
    <executable statements>
   
    /* Last line always: */
    RETURN return_value;
 EXCEPTION
  
 END func_name;

Function templete

PROCEDURE calc_sales
   (company_id_in   IN     company.company_id%TYPE,
    rank_inout      IN OUT NUMBER,
    total_sales_out OUT    NUMBER);

parameter name

-- package 果然跟 objective c 很像
-- By placing data inside the package body, 
-- it is protected from direct access by any programs outside of the package.
-- recommendation:
-- Never put your variables and other data structures in the package specification.
/*
Always put them in the body.
Then build programs to change values in the data structures and retrieve the current values.
Make these “get and set” programs available in the package specification. 
The benefits include: 
Tighter control over data structures.
Flexibility to change implementation of data structure. 
 */
/*
it's recommended that you set standards for elements and their names in your "get and set"
programs. For example, if the data structure is “maximum length,” then create the following elements:

A public procedure named “set_max_length”
A public function named “max_length”
A private variable named “v_max_length”. 
 */

package

/*
     shared pool 可以重复利用 SQL 的条件:
    ~ 字母的大小写要一致.
    ~ 空格要一致(只用一个空格).
    ~ 要在同一个 schema 下的对象.
    ~ 如果table使用了别名, 并且别名不一样, 比如一个C, 另一个E, 那么也不能被重复利用.
    总之要想被重复利用, 必须完完全全一样. 一个字, 一个空格都不能差, 单纯重字面上. 
 */

/*
 Put all SQL verbs in one case. 例子中, SQL verbs 全部大写, 表,列等等全部小写
 Begin all SQL verbs on a new line. SQL 关键字重启一行
 Right- or left-align verbs with the initial SQL verb. 貌似右对齐的人比较多
 Separate all statement "words" by a single space. 一个空格分隔
*/
-- 例如, 左对齐
SELECT emp_no, emp_name, emp_salary
FROM   emp
WHERE  sal_grade > 10
AND    ( emp_salary >= 10000
OR     tot_staff > 100 )

-- 右对齐
SELECT emp_no, emp_name, emp_salary
  FROM emp
 WHERE sal_grade > 10
   AND ( emp_salary >= 10000
    OR tot_staff > 100 )

-- 这里也可以全部使用小写, 避免一些问题, 因为你要知道, 如果要想SQL被重复利用, 字符大小写是有关系的
-- 所以, 全部用小写就不会有什么问题. 这样, 有些懒程序员就可以全部使用小写.
select emp_no, emp_name, emp_salary
  from emp
 where sal_grade > 10
   and (emp_salary >= 10000 or tot_staff > 100)
-- 当然, 你使用大小写区分的好处是, 很容易分辨关键字等信息.

/*
 好的方式
Use UPPER and lower case to distinguish between reserved words and application-specific identifiers.
Use white space to improve readability.
Use a consistent commenting style that is low in maintenance and high in readability.
Comment only to add value.
Use consistent formats for different constructs of the language, including SQL statements.
    Distinguish between the SQL syntax and your application constructs. Put all keywords to the left, 
        application elements to the right.
    Separate the distinct clauses with white space.(行与行之间的空格)
    Use meaningful aliases, especially for tables.
 */
-- <wait picture 1-1.jpg>##########################################
-- 另外, 要多使用间接, 甚至连 业务逻辑都可以写到间接变量里, 例如:
DECLARE
   /* I hide my business rule behind this variable. */
   order_overdue CONSTANT BOOLEAN
  DEFAULT (shipdate < ADD_MONTHS (SYSDATE, +3) OR
       order_date >= ADD_MONTHS (SYSDATE, -2)) AND
    order_status = 'O';
   high_priority CONSTANT BOOLEAN 
  DEFAULT cust_priority_type = 'HIGH';

BEGIN
   IF order_overdue AND high_priority    -- 业务逻辑已经隐藏在上边的定义中
   THEN
      ship_order ('EXPRESS');
   ELSE
      ship_order ('GROUND'); 
   END IF;
END;

-- 行内的空格
-- Always include a space between every identifier and separator in a statement.
WHILE (total_sales < maximum_sales AND company_type = 'NEW')
LOOP
-- Use spaces to make module calls and their parameter lists more understandable.
calc_totals (company_id, LAST_DAY (end_of_year_date), total_type);
-- procedure 等名字后面要有个空格再接括号, 括号与字符之间不需要有空格.

-- 如果有大量的声明, 那么声明最好有一定顺序, 例如: by datatye, by logical relationship
-- by datatype
DECLARE
 min_value NUMBER;
 company_id NUMBER;
 company_name VARCHAR2(30);
 employee_name VARCHAR2(60);
 hire_date DATE;
 termination_date DATE;
-- by logical relationship
DECLARE
 company_name VARCHAR2(30);
 company_id INTEGER;
 employee_name VARCHAR2(60);
 hire_date DATE;
 termination_date DATE;
 min_value NUMBER;

-- 如果有多行参数时, 美观的写法是, 参数另起一行, 例如: 并且缩进
generate_company_statistics 
   (company_id, last_year_date, rollup_type, 
   total, average, variance, budgeted, next_year_plan);
FOR month_index IN
   first_month .. last_month    -- 区间另起一行, 可能区间很长, 在一行不美观
LOOP

q1_sales := 
   month1_sales + 
   month2_sales +
   month3_sales;        -- 在一行, 太长, 不美观, 也不直观.

-- 美观的 SQL 语句, 例子
-- <wait picture 1-2.jpg>#############################
-- Here are some examples of this format in use:

SELECT last_name, first_name
  FROM employee
 WHERE department_id = 15
   AND hire_date < SYSDATE;

SELECT department_id, SUM (salary) AS total_salary
  FROM employee
 GROUP BY department_id
 ORDER BY total_salary DESC;

INSERT INTO employee 
   (employee_id, ... )
VALUES (105 ... );

DELETE FROM employee
      WHERE department_id = 15;

UPDATE employee
   SET hire_date = SYSDATE
 WHERE hire_date IS NULL
   AND termination_date IS NULL;

-- we recommand
-- Placing each expression of the WHERE clause on its own line
-- Using a separate line for each expression in the select list of a SELECT statement.
-- Placing each table in the FROM clause on its own line.
-- Placing each separate assignment in a SET clause of the UPDATE statement on its own line.
-- 例如
SELECT last_name,
       C.name,
       MAX (SH.salary) best_salary_ever
  FROM employee E,
       company C,
       salary_history SH
 WHERE E.company_id = C.company_id
   AND E.employee_id = SH.employee_id
   AND E.hire_date > ADD_MONTHS (SYSDATE, -60);

UPDATE employee
   SET hire_date = SYSDATE,
       termination_date = NULL
 WHERE department_id = 105;

-- pl/sql 格式, 跟SQL类似, 只是每个块要有空格区分
FUNCTION company_name (company_id_in IN company.company_id%TYPE)  -- 参数多时可另起一行, 并缩进
   RETURN VARCHAR2
IS
   cname company.company_id%TYPE;

BEGIN        -- 前面有一行空格
   SELECT name INTO cname FROM company
    WHERE company_id = company_id_in;
   RETURN cname;

EXCEPTION    -- 前面有一行空格
   WHEN NO_DATA_FOUND 
   THEN
      RETURN NULL;
END;

SQL 好的结构

1-1.jpg

1-2.jpg

-- 如果注释另起一行, 要注意缩进要同注释的语句在同一个缩进级别
-- 多行注释, 推荐使用
/*
|| this is a multipule comments.
|| another line
*/

-- 注释接上一行时, 可以使用缩进
/*
|| Variables used to keep track of string scan:
||    atomic_count - running count of atomics scanned.
||    still_scanning - Boolean variable controls WHILE loop.
*/

注释

-- if 格式
-- <wait picture 1-3.jpg>#############

-- exception 格式
EXCEPTION
   WHEN exception1
   THEN
      executable_statements1;
 
   WHEN exception 2
   THEN
      executable_statements1;
 
   ...
   WHEN OTHERS
   THEN
      otherwise_code;
END;

/*
|| Indent each WHEN clause in from the EXCEPTION keyword that indicates the start of the exception section.
|| Place the THEN directly below the WHEN.
|| Indent all the executable statements for that handler in from the THEN keyword.
|| Place a blank line before each WHEN (except for the first)
*/

-- 在 package 中声明的内容
/*
|| 全局变量
|| Complex datatypes, such as records and tables
|| Database-related declarations, such as cursors
|| Named exceptions
|| Modules (procedures and functions)
 */

Format statement

1-3.jpg