plsql常见命令笔记

-- ###################### pl/sql编程(SQL*Plus中无法执行时在末尾加"\") ######################;
-- 打开输出选项
set serveroutput on;
-- 显示错误信息
show error;
-- 变量赋值 :=

-- pl/sql编程的基本示例:
-- 定义部分
declare
    v_ename varchar2(20);
    v_sal number;

-- 执行部分
begin
    -- 执行语句给变量赋值
    select ename, sal into v_ename, v_sal from emp where empno=&no;
    -- 在控制台打印变量
    dbms_output.put_line('雇员名：' || v_ename || '薪水：' || v_sal);
    
-- 例外处理
exception
    when no_data_found then
    dnms_output.put_line('未找该员工');
end;

-- ############# 存储过程 ###############

-- 创建存储过程(or replace: 存在时替换)
create [or replace] procedure 过程名_pro[(参数)] is
-- 定义变量
begin
执行语句
end;
-- 调用存储过程
exec 过程名[(参数)];

-- ############# 函数 ###############
-- 创建函数
create function 函数名_func(参数)
return 返回值类型 is 
返回变量名 返回变量具体类型;-- 例如: 返回值类型为number，返回变量具体类型为number(7, 2)
begin
    select 需要返回的字段 into 返回变量名 from 表名 where 字段 = 参数;
    return 返回变量名;
end;

-- SQL*Plus中调用函数
var 变量名 变量类型;
call 函数名(参数) into:变量名;
print 变量名;

-- ############# 包 ###############
-- 创建包
create package 包名 is
procedure 储存过程名(参数);-- 存储过程声明
function 函数名(参数) return 返回值类型;-- 函数声明
end;

-- 创建包体
create or replace package body 包名 is
	-- 储存过程实现
	procedure 储存过程名(参数) is
	begin
		insert into test1 values(v_id, v_name);
	end;
	-- 函数实现
	function 函数名(参数)
	return 返回值类型 is 
	返回变量 返回变量具体类型;
	begin
		select 需要返回的字段 into 返回变量名 from 表名 where 字段 = 参数;
		return 返回变量;
	end;
end;

-- 调用包
call 包名.储存过程名(3, '使用包插入的值');
select 包名.函数名('SCOTT') from dual;
select * from test1;


-- 记录类型(类似结构体)
declare
    -- type 记录变量_type is record (记录成员); 
	type emp_record_type is record(name emp.ename%type, salary emp.sal%type, title emp.job%type);
    emp_record emp_record_type;
begin
    select ename, sal, job into emp_record from emp where empno = 7654;
    -- 记录变量.记录成员
    dbms_output.put_line('姓名：' || emp_record.name || ', 薪水：' || emp_record.salary || ', 工作：' || emp_record.title);
end;

-- 表类型(类似数组)
declare
    -- type 表类型_type is table of 字段类型 index by binary_integer;
    type sp_table_type is table of emp.ename%type index by binary_integer;
    sp_table sp_table_type;
begin
    select ename into sp_table(-1) from emp where empno = 7654;
    -- 记录变量.记录成员
    dbms_output.put_line('姓名：' || sp_table(-1));
end;

-- 游标类型(输入部门号，并显示该部门所有员工姓名和工资)
declare
    -- 定义游标  
    type sp_emp_cursor is ref cursor;
    test_cursor sp_emp_cursor;
    -- 定义接受结果的变量
    v_ename emp.ename%type;
    v_sal emp.sal%type;
begin
    -- 使游标指向指定结果集
    open test_cursor for select ename, sal from emp;
    -- 循环取出结果
    loop
        fetch test_cursor into v_ename, v_sal;
        -- 判断游标是否为空
        exit when test_cursor%notfound;
        dbms_output.put_line('姓名：' || v_ename ||', 薪水: ' || v_sal);
    end loop;
    -- 关闭游标
    close test_cursor;
end;