Oracle PL/SQL基础知识

编码规则规范

/*
|| 在注释开始的首行只放斜线星号，标志注释开始，
|| 然后注释块的每一行以双垂直线开头，突出注释后面
|| 的内容是注释部分，最后，将星号斜线单置于一行
*/
DECLARE
   v_ename VARCHAR2(20);
   v_rate NUMBER(7,2);
   c_rate_incr CONSTANT NUMBER(7,2):=1.10;
BEGIN
   --方法一：通过SELECT INTO给变量赋值
   SELECT  ename, sal* c_rate_incr  
     INTO  v_ename, v_rate
     FROM  employee 
    WHERE  empno='7788';
   --方法二：通过赋值操作符“:=”给变量赋值
   v_ename:='SCOTT';
END;

 

标示符示例

/*
===========================================================
|        标示符示例
============================================================
*/
DECLARE
   ename varchar2(20) :='FORD';
BEGIN
  DELETE FROM employee WHERE ename=ename;
END;

SELECT * FROM employee;

ROLLBACK;

 

格式化单独语句

/*
|| 格式化单独的语句
*/

new_id := 15; calc_total (new_id); max_dollars := 105 * sales_adj;
--可改写为：
new_id := 15;
calc_total (new_id);
max_dollars := 105 * sales_adj;

/*
|| 格式化多行语句
*/
--第一条语句
IF total_sales < maximum_sales AND company_type = 'NEW' AND (override
= 'Y' OR total_company_revenue < planned_revenue (SYSDATE))
THEN
   accept_order;
END IF;
--可改写为：
IF total_sales < maximum_sales 
   AND company_type = 'NEW' 
   AND (override= 'Y' 
        OR total_company_revenue < planned_revenue (SYSDATE))
THEN
   accept_order;
END IF;

--第二条语句
generate_company_statistics (company_id, last_year_date
, rollup_type, total, average, variance, budgeted, next_year_plan);
--改写为第一种：
generate_company_statistics (company_id, last_year_date,
   rollup_type, total, average, variance, budgeted, next_year_plan);
--改写为第二种：
generate_company_statistics
   (company_id, last_year_date, rollup_type,
    total, average, variance, budgeted, next_year_plan);
 
   
--第三条语句
total_sales := product_sales (company_id) + admin_cutbacks *
.5 - overhead - golden_parachutes;
--可改写为：
total_sales := 
   product_sales(company_id)+
   admin_cutbacks *0.5-
   overhead-
   golden_parachutes;