PLSQL语言的运用

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