PL_SQL基础
1.数据类型
VARCHAR2--文本
NUMBER--数字、金额
DATE--日期
2.增删改查
3.主键
4.块
declare--声明
begin--程序体的开始
exception--异常捕获
end;--程序体的结束
DECLARE
v_att1 NUMBER;
v_att2 NUMBER;
v_result NUMBER;
BEGIN
v_att1 := 100;
v_att2 := 0;
v_result := v_att1 / v_att2;
dbms_output.put_line(v_result);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR!');
END;
5.view、select into、if、for、exception
--视图
--创建 或 覆盖 对象类型 对象名称 as
--查询语句
create or replace view sys_user_v as
select * from sys_user;
--select into 取某一行数据的多列（返回多行或无数据都会报错）
DECLARE
v_user_id NUMBER;
v_user_name sys_user.user_name%TYPE;
v_description sys_user.description%TYPE;
BEGIN
v_user_id := 1;
SELECT u.user_name,
u.description
INTO v_user_name,
v_description
FROM sys_user u
WHERE u.user_id = v_user_id;
dbms_output.put_line(v_user_name || v_description);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR!');
END;
--if
DECLARE
v_att1 NUMBER;
BEGIN
v_att1 := 3;
IF v_att1 = 1 THEN
dbms_output.put_line('one');
ELSIF v_att1 = 2 THEN
dbms_output.put_line('two');
ELSE
dbms_output.put_line('other');
END IF;
END;
--for 数据遍历
--循环固定次数
DECLARE
v_count NUMBER;
BEGIN
v_count := 4;
FOR cr IN 0 .. v_count
LOOP
dbms_output.put_line(cr);
END LOOP;
END;
--遍历数据集
BEGIN
FOR cr IN (SELECT *
FROM sys_codes_vl
WHERE rownum < 11)
LOOP
dbms_output.put_line(cr.code || cr.code_name);
END LOOP;
END;
--exception 异常处理
--常见预定义异常
NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的元素时
TOO_MANY_ROWS 执行 select into 时，结果集超过一行
ZERO_DIVIDE 除数为 0
DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值
begin
.....
exception
when NO_DATA_FOUND then
...
when TOO_MANY_ROWS then
...
when ZERO_DIVIDE then
...
when DUP_VAL_ON_INDEX then
...
when others then
....
end;
--自定义异常
DECLARE
v_num NUMBER;
e_error1 EXCEPTION; --异常定义
e_error2 EXCEPTION;
e_others EXCEPTION;
BEGIN
v_num := 3;
IF v_num = 1 THEN
RAISE e_error1; --抛出异常
ELSIF v_num = 2 THEN
RAISE e_error2; --抛出异常
ELSE
RAISE e_others; --抛出异常
END IF;
EXCEPTION
WHEN e_error1 THEN
dbms_output.put_line('error1');
WHEN e_error2 THEN
dbms_output.put_line('error2');
WHEN OTHERS THEN
dbms_output.put_line('others');
END;
6.function、procedure、package
--function 函数
--创建 或 覆盖 对象类型 对象名称(参数1 数据类型，参数2 数据类型,.......) 返回 数据类型 as
CREATE OR REPLACE FUNCTION test_fun(p_att1 NUMBER,
p_att2 NUMBER) RETURN NUMBER AS
v_result NUMBER;
BEGIN
v_result := p_att1 * p_att2;
RETURN v_result; --返回参数
END;
DECLARE
v_result NUMBER;
BEGIN
v_result := test_fun(p_att1 => 100, p_att2 => 200);
dbms_output.put_line(v_result);
END;
SELECT u.*,
test_fun(u.user_id, u.created_by) result_num
FROM sys_user u
--procedure 存储过程
--创建 或 覆盖 对象类型 对象名称(参数1 传入/传出 数据类型，参数2 传入/传出 数据类型,.......) as
CREATE OR REPLACE PROCEDURE test_proc(p_att1 NUMBER,
p_att2 IN NUMBER,
o_result OUT NUMBER,
o_result1 out number) AS
BEGIN
o_result := p_att1 * p_att2;
o_result1 := p_att1 + p_att2
END;
DECLARE
v_result NUMBER;
v_result1 NUMBER;
BEGIN
test_proc(p_att1 => 100, p_att2 => 50, o_result => v_result, o_result1 => v_result1);
dbms_output.put_line(v_result || '/' || v_result1);
END;
--package 包头 --声明公共方法
--package body 包体 --私有的存储过程、函数
--打包程序
--package
--创建 或 覆盖 对象类型 对象名称 as
CREATE OR REPLACE PACKAGE test_pkg AS
FUNCTION test_fun(p_att1 NUMBER,
p_att2 NUMBER) RETURN NUMBER;
PROCEDURE test_proc(p_att1 NUMBER,
p_att2 IN NUMBER,
o_result OUT NUMBER,
o_result1 OUT NUMBER);
END;
--package body
--创建 或 覆盖 对象类型 对象名称 as
create or replace package body test_pkg as
FUNCTION test_fun(p_att1 NUMBER,
p_att2 NUMBER) RETURN NUMBER AS
v_result NUMBER;
BEGIN
v_result := p_att1 * p_att2;
RETURN v_result; --返回参数
END;
PROCEDURE test_proc(p_att1 NUMBER,
p_att2 IN NUMBER,
o_result OUT NUMBER,
o_result1 out number) AS
BEGIN
o_result := p_att1 * p_att2;
o_result1 := p_att1 + p_att2
END;
end;
DECLARE
v_result NUMBER;
v_result1 NUMBER;
BEGIN
test_pkg.test_proc(p_att1 => 100, p_att2 => 50, o_result => v_result, o_result1 => v_result1);
--test_proc(p_att1 => 100, p_att2 => 50, o_result => v_result, o_result1 => v_result1);
dbms_output.put_line(v_result || '/' || v_result1);
END;
select * from cux_employee;
create or replace package cux_employee_pkg as
procedure insert_cux_employee(p_employee_no varchar2,p_employee_name varchar2,p_job varchar2,p_mgr number,p_sal number,p_hiredate date,p_user_id number ) ;
end;
create or replace package body cux_employee_pkg as
procedure insert_cux_employee(p_employee_no varchar2,p_employee_name varchar2,p_job varchar2,p_mgr number,p_sal number,p_hiredate date,p_user_id number ) as
begin
insert into cux_employee(employee_id ,employee_no ,employee_name ,job ,mgr ,sal ,dept_no ,hiredate,created_by ,creation_date,last_updated_by ,last_update_date)
values(cux_employee_s.nextval,p_employee_no,p_employee_name,p_job,p_mgr,p_sal,'',p_hiredate,p_user_id,sysdate,p_user_id,sysdate);
end;
end;
