plsql中的sql语句
## select into 语句:用于把从中查询出的内容存入变量
declare v_hire_date employees.hire_date%type; v_salary empployees.salary%type; begin select hire_date,salary into v_hire_date,v_salary from employees where department_id = 100; ... end;
set serveroutput on declare v_sum_sal number(10,2); v_deptno number not null := 60; begin select sum(salary) --group function into v_sum_sal from employees where department_id = v_deptno; dbms_output.put_line('the sum salary is ' || to_char(v_sum_sal)); end;
注意:该语句支持单行的查询结果,如果where条件控制的不好,导致多行查询结果,则会引发to manay rows的例外
## insert,update,delete,merge语句:在plsql中执行这些语句和直接执行这些语句差不多,只不过可以在sql语句中使用plsqls声明变量。
begin insert into employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) values ((employees_seq.nextval, 'ruth', 'cores', 'rcores',sysdate, 'ad_asst', 4000) end;
declare v_sal_increase employees.salary%type; begin update employees set salary = salary + v_sal_increase; where job_id = 'st_clerk'; end;
declare v_deptno employees.department_id%type := 10; begin delete from employees where department_id = v_ deptno; end;
declare v_empno employees.employee_id%type := 100; begin merge into copy_emp c using employees e on (e.employee_id = v_empno) when matched then update set c.first_name = e.first_name, c.last_name = e.last_name, c.email = e.email, ... when not matched then insert values(e.employee_id, e.first_name, e.last_name,..., e.department_id); end;
plsql中的控制语句
和其他语言一样,控制主要包括判断和循环;
判断语句的语法与其他语言类似:
if condition then statements; [elsif condition then statements;] [else statements;] end if;
case selector when expression1 then result1; when expression2 then result2; ... when expressionn then resultn; [else resultn + 1] end;
### 循环语句的语法与其他语言类似:有基本循环、for循环、wihle循环三种 :
loop statement1; ... exit [when condition]; end loop
while condition loop statement1; statement1; ... end loop
for counter in [reverse] lower_bound .. upper_bound loop statement1; statement2; ... end loop;
举例:
declare v_country_id locations.country_id%type := 'ca'; v_location_id locations.location_id%type; v_city locations.city%type := 'montreal'; begin select max(location_id) into v_location_id from locations where country_id = v_country_id; for i in 1 .. 3 loop insert into locations(location_id, city, country_id) values((v_location_id + i), v_city, v_country_id ); end loop; end;
嵌套循环和label:
... begin <> loop v_counter := v_counter+1; exit when v_counter > 10; <> loop ... exit outer_loop when total_done = 'yes'; -- leave both loops exit when inner_done = 'yes'; -- leave inner loop only ... end loop inner_loop; ... end loop outer_loop; end; ...
## plsql中的复杂自定义数据类型
概述:
plsql中常用的自定义类型就两种: 记录类型、plsql内存表类型(根据表中的数据字段的简单和复杂 程度又可分别实现类似于简单数组和记录数组的功能)
记录类型的定义举例:
type emp_record_type is record (last_name varchar2(25), job_id varchar2(10), salary number(8,2)); emp_record emp_record_type;
%rowtype属性:在plsql中 %rowtype 表示某张表的记录类型或者是用户指定以的记录类型,使用此属性可以很方便的定义一个变量,其类型与某张表的记录或者自定义的记录类型保持一致。极大的方便了select * into ….的语句使用。
declare emp_rec employees%rowtype begin select * into emp_rec from employees where employee_id = &employee_number; insert into retired_emps(empno, ename, job, mgr, hiredate, leavedate, sal, comm, deptno) ; values (emp_rec.employee_id, emp_rec.last_name, emp_rec.job_id, emp_rec.manager_id, emp_rec.hire_date, sysdate, emp_rec.salary, emp_rec.commission_pct, emp_rec.department_id); commit; end;
plsql内存表即index by table ,这种结构类似于数组,使用主键提供类似于数组那样的元素访问。这种类必须包含两个部分:
使用binary integerl类型构成的索引主键; 另外一个简单类型 或者用户自定义类型的字段作为具体的数组元素。 这种类型可以自动增长,所以也类似于可变长数组。
... type ename_table_type is table of employees.last_name%type index by binary_integer; ename_table ename_table_type; ...
plsql内存表应用举例:
下面定义的两个内存表中的元素都是简单的数据类型,所以相当于定义了两个简单数组:
declare type ename_table_type is table of employees.last_name%type index by binary_integer; type hiredate_table type is table of date index by binary_integer; ename_table ename_table_type; hiredate_table hiredate_table_type; begin ename_table(1) := 'cameron'; hiredate_table := sysdate + 7; if ename_table.exists(1) then insert into ... ... end;
备注:对plsql内存表中某个元素的访问类似于数组,可以使用下表,因为binary_integer这种数据类型 的值在-2147483647 … 2147483647范围内,所以下表也可以在这个范围内。
plsql内存表的应用举例:
下面定义的两个内存表中的元素都是记录类型,所以相当于定义了真正的内存表:
需要了解更多数据库技术:PLSQL中的各种SQL语句实例讲解,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
declare type emp_table_type is table of employees%rowtype index by binary_integer; my_emp_table emp_table_type; v_count number(3) := 104; begin for i in 100 .. v_count loop select * into my_emp_table(i) from employees where employee_id = i; end loop; for i in my_emp_table.first .. my_emp_table.last loop dbms_output.put_line(my_emp_table(i).last_name); end loop; end;
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/625268.html