发布时间 : 星期日 文章PLSQL语言的运用更新完毕开始阅读8dd1f06f814d2b160b4e767f5acfa1c7ab00827b
v_deptno NUMBER;
v_dname VARCHAR2(100); BEGIN
DELETE FROM departments WHERE department_id = 190
RETURNING department_id, department_name INTO v_deptno, v_dname;
dbms_output.put_line(v_deptno); dbms_output.put_line(v_dname); END;
DML返回多行值 DECLARE
TYPE emp_record_type IS RECORD(
v_last_name employees.last_name%TYPE, v_salary employees.salary%TYPE);
TYPE table_type IS TABLE OF emp_record_type;
emp_table table_type; BEGIN
UPDATE employees
SET salary = salary + 1 WHERE salary <= 5000
RETURNING last_name, salary BULK COLLECT INTO emp_table;
FOR i IN 1 .. emp_table.count LOOP
dbms_output.put_line(emp_table(i).v_last_name); dbms_output.put_line(emp_table(i).v_salary); dbms_output.put_line('------------------------------'); END LOOP; END;
1 基本输出语句
BEGIN
dbms_output.put_line('hello,world'); dbms_output.put_line(127);
dbms_output.put_line(sysdate); 系统当前时间
--dbms_output.put_line(ture); 不能直接输出true/false END;
2 变量的使用
SELECT 列1，列2……..INTO 变量1 、变量2 DECLARE
i number(4):=250; j number(6);
c varchar(100):='hello,world'; d DATE := SYSDATE; --e boolean := true; BEGIN j:=1000;
dbms_output.put_line('i=' || i); dbms_output.put_line('j=' || j); dbms_output.put_line('c=' || c);
dbms_output.put_line('d=' || to_char(d,'YYYY-MM-DD')); --dbms_output.put_line('e=' || e); END;
CONSTANT 常量的使用
declare //只做声明
c_a CONSTANT NUMBER(4):=100;
v_b number(4) not null:=101; 使用not null修饰变量时，变量必须初始化； begin
--c_a:=101; 常量不能边
dbms_output.put_line(c_a); dbms_output.put_line(v_b); null； 不能为空 end;
更新100员工，工资增加1并在控制台打印输出
declare
v_empid binary_integer:=100; v_money number(10):=1; begin
update employees set salary = salary +v_money where employee_id = v_empid; commit;
dbms_output.put_line(v_empid || ','|| v_money); end;
查询某个员工的编号的姓名，工资，入职日期，部门编号
declare
v_empid binary_integer :=101; v_name varchar(50); v_salary number(8,2); v_hiredate DATE;
v_deptid binary_integer; begin
select employee_id,last_name,salary,hire_date,department_id into v_empid,v_name,v_salary,v_hiredate,v_deptid from employees
where employee_id= v_empid;
dbms_output.put_line(v_empid || ',' || v_name || ',' || v_salary || ',' || to_char(v_hiredate,'yyyY/mm/DD')||','||v_deptid ); end;
记录类型
declare
type emp_record_type is record( v_empid binary_integer :=101, v_name varchar(50), v_salary number(8,2), v_hiredate DATE,
v_deptid binary_integer );
e emp_record_type; begin
select employee_id,last_name,salary,hire_date,department_id into e
from employees
where employee_id= e.v_empid;
dbms_output.put_line(e.v_empid || ',' || e.v_name || ',' || e.v_salary || ',' || to_char(e.v_hiredate,'yyyY/mm/DD')||','||e.v_deptid ); end;
参照引用类型
未知的变量类型可以参照已知的变量类型，或者参照数据库表中的列的类型 语法%type declare i number(4); j i%type:=100;
k employees.employee_id%type; begin k:=101;
dbms_output.put_line('j=' || j); dbms_output.put_line('k=' || k); end;
查询某个员工的编号的姓名，工资，入职日期，部门编号 declare
TYPE emp_record_type is record(
empid employees.employee_id%type:=102, ename employees.last_name%type, salary employees.salary%type, hiredate employees.hire_date%type, deptid employees.department_id%type );
e emp_record_type; begin
select employee_id,last_name,salary,hire_date,department_id INTO e
from employees
where employee_id= e.empid;
dbms_output.put_line('员工编号:' || e.empid); dbms_output.put_line('员工姓名:' || e.ename); dbms_output.put_line('工资:' || e.salary);
dbms_output.put_line('入职日期:' || to_char(e.hiredate,'YYYY/MM/DD')); dbms_output.put_line('部门编号:' || e.deptid); end;
查询某个员工的编号的姓名，工资，入职日期，部门编号
declare
e employees%rowtype; //赋值没有点. begin
e.employee_id:=102; select * into e