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 中完整定义cursorcreate or replace package book_infois    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    -- cursorc_constant_data     -- constantsv_varibale_value    -- variablecompany_id_in        -- parametercompany_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_salLOOP      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 .. 20LOOP   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_salaryFROM   empWHERE  sal_grade > 10AND    ( emp_salary >= 10000OR     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 datatypeDECLARE min_value NUMBER; company_id NUMBER; company_name VARCHAR2(30); employee_name VARCHAR2(60); hire_date DATE; termination_date DATE;-- by logical relationshipDECLARE 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    -- 区间另起一行, 可能区间很长, 在一行不美观LOOPq1_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 VARCHAR2IS   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