jparsesql 格式化sql plsql格式化sql语句

文章目录

• 1 PL/SQL可用的SQL语句
• 2 PL/SQL块
• 3 建议的命名方法
• 4 复合类型

• 4.1 使用记录类型
• 4.2 使用%ROWTYPE

1 PL/SQL可用的SQL语句

PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：

INSERT, UPDATE, DELETE, SELECT ... INTO, COMMIT, ROLLBACK, SAVEPOINT。

提示：在PL/SQL中只能用SQL语句中的DML部分，不能用DDL部分，如果要在PL/SQL中使用DDL（如CREATE table等）的话，只能以动态的方式来使用。

2 PL/SQL块

PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分

PL/SQL块的结构如下：

DECLARE
/*声明部分：在此声明PL/SQL用到的变量，类型及游标，以及局部的存储过程和函数*/
BEGIN
/*执行部分：过程及SQL语句，即程序的主要部分*/
EXCEPTION
/*执行异常部分：错误处理*/
END;

其中，执行部分是必须的

例如：（在PL/SQL中执行前需运行set serveroutput on语句）

--declare
  --声明的变量、类型、游标
begin
  --程序的执行部分（类似于java里的main()方法）
  dbms_output.put_line('helloworld');
--exception
  --针对begin块中出现的异常，提供处理的机制
  --when ... then ...
  --when ... then ...
end;

declare
  --变量、记录类型等的声明
  v_sal number(8,2) := 0;
begin
  --程序的执行部分
  select salary into v_sal
  from employees
  where employee_id = 123;
  dbms_output.put_line('salary: '||v_sal);
--exception
  --异常的处理
end;

在 PL/SQL 编程中，变量赋值是一个值得注意的地方，它的语法如下：
variable := expression;
variable 是一个 PL/SQL 变量，expression 是一个 PL/SQL 表达式

--查询编号为100号员工的工资
declare
  --声明变量
  v_sal varchar2(20);
begin
  --sql语句的操作：select ... into ... from ... where ...
  select salary into v_sal from employees where employee_id = 100;
  --打印
  dbms_output.put_line(v_sal);
end;

--查询编号为100号员工的工资，邮箱，入职时间
declare
  --声明变量
  v_sal number(10,2);
  v_email varchar2(20);
  v_hire_date date;
begin
  --sql语句的操作：select ... into ... from ... where ...
  select salary, email, hire_date into v_sal, v_email, v_hire_date from employees where employee_id = 100;
  --打印
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;

使用%TYPE
定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库的某个列的数据类型相同，这时可以使用%TYPE
使用%TYPE特性的优点在于：

• 所引用的数据库列的数据类型可以不知道
• 所引用的数据库列的数据类型可以实时改变

declare
  --声明变量
  v_sal employees.salary%type;
  v_email employees.email%type;
  v_hire_date employees.hire_date%type;
begin
  --sql语句的操作：select ... into ... from ... where ...
  select salary, email, hire_date into v_sal, v_email, v_hire_date from employees where employee_id = 100;
  --打印
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;

3 建议的命名方法

标识符	命名规则	例子
程序变量	v_name	v_name
程序常量	c_name	c_company_name
游标变量	name_cursor	emp_cursor
异常标识	e_name	e_too_many
表类型	name_table_type	emp_record_type
表	name_table	emp_table
记录类型	name_record	emp_record
SQL*Plus替代变量	p_name	p_sal
绑定变量	g_name	g_year_sal

4 复合类型

4.1 使用记录类型

ORACLE在PL/SQL中除了提供基础的变量类型外，还提供一种称为复合类型的类型——记录

记录类型是把来逻辑相关的数据作为一个单元存储起来，称作PL/SQL RECORD的域（FIELD），其作用是存放互不相同但逻辑相关的信息。
例如：

declare
  --声明一个记录类型
  type emp_record is record(
       v_sal employees.salary%type,
       v_email employees.email%type,
       v_hire_date employees.hire_date%type
   );
   --定义一个记录类型的成员变量
   v_emp_record emp_record;
begin
  --sql语句的操作：select ... into ... from ... where ...
  select salary, email, hire_date 
  into v_emp_record 
  from employees
  where employee_id = 100;
  --打印
  dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hire_date);
end;

declare
  type salary_record is record(
       v_name varchar2(20),
       v_salary number(10,2)
  );
  v_salary_record salary_record;
begin
  v_salary_record.v_name := '刘德华';
  v_salary_record.v_salary := 100000; 
  dbms_output.put_line('name: '||v_salary_record.v_name||' salary: '||v_salary_record.v_salary);
end;

4.2 使用%ROWTYPE

PL/SQL 提供%ROWTYPE操作符，返回一个记录类型，其数据类型和数据库表的数据结构相一致
使用%ROWTYPE特性的优点在于：

• 所引用的数据库中列的个数和数据类型可以不必知道
• 所引用的数据库中列的个数和数据类型可以实时改变

declare
  v_emp_record employees%rowtype;
begin
  select * into v_emp_record from employees where employee_id = 123;
  dbms_output.put_line('employeeid: '||v_emp_record.employee_id);
end;