最近准备学习一下SQL编程,通过ORACLE10g pl/sql 编程,参照其写的一些实例,欢迎大家一起学习
准备工作:oracle10g,pl/sql developer
创建表 emp 和 dept
CREATE OR REPLACE TABLE emp(
empno VARCHAR2(32),
ename VARCHAR2(32),
sal NUMBER(7,2),
deptno VARCHAR2(32),
constraint emp_key primary key(empno)
);
那么就开始了 直接上例子了:
--pl/sql实例 DECLARE--声明变量 v_ename VARCHAR(5); BEGIN SELECT ename INTO v_ename FROM emp--INTO为游标,必须要加 WHERE empno='&no';--一个输入no dbms_output.put_line('输出'||v_ename);--dbms_output为系统表,\\为连接符 EXCEPTION --抛出例外 WHEN NO_DATA_FOUND THEN dbms_output.put_line('输入有误'); END; --命名块 跟匿名块差不多,多一个<<>> <<outer>> DECLARE v_deptno VARCHAR2(5); v_name VARCHAR2(10); BEGIN <<inner>> BEGIN SELECT dno INTO v_deptno FROM emp WHERE lower(ename)=lower('&name') END; SELECT dname INTO v_name FROM emp WHERE deptno = v_deptno; dbms_output.put_line('名称'||v_name); END; --创建过程 CREATE OR REPLACE PROCEDURE update_sals(name varchar2, newsal number) AS BEGIN UPDATE emp SET sal = newsal WHERE lower(ename) = lower(name); END;
--调用过程 call update_sals('fei',7000); --创建函数 必须有一个RETURN 语句在头部 CREATE OR REPLACE FUNCTION annual_income(name varchar2) RETURN NUMBER AS annual_salary NUMBER(7,2); BEGIN SELECT sal*12 + nvl(comm,0) INTO annual_salary from emp WHERE lower(ename) = lower(name); RETURN annual_salary; END; --调用函数 --SQL命令行中 SQL->var sal NUMBER SQL->call annual_income('fei') INTO :sal; SQL->print sal; --创建包 逻辑组合相关的函数和过程 --包声明 CREATE PACKAGE pkg AS PROCEDURE updatesals(name VARCHAR2(32),sal NUMBER); FUNCTION annual_income(name Varchar2(32)) RETURN NUMBER; --包体定义 CREATE PACKAGE BODY pkg AS PROCEDURE update_sals(name varchar2, sal number) AS BEGIN UPDATE emp SET sal = newsal WHERE lower(ename) = lower(name); END; FUNCTION annual_income(name varchar2) RETURN NUMBER AS annual_salary NUMBER(7,2); BEGIN SELECT sal*12 + nvl(comm,0) INTO annual_salary from emp WHERE lower(ename) = lower(name); RETURN annual_salary; END; END; --包调用 要加上包名 SQL->call pkg.updatesals('fei',10000); --创建触发器 通过某些特定条件隐含的执行存储过程 CREATE TRIGGER update_emp AFTER UPDATE OF sal ON sal FOR EACH ROW BEGIN UPDATE emp set sal =:new.sal--new 现在的值 WHERE sal =:old.sal;--old 以前的值 END;
--简单类型 DECLARE v_ename emp.ename%TYPE;--在不知道ename什么类型的状况下,可以匹配该类型 v_sal emp.sal%TYPE; v_sal_tax Constant NUMBER(6,2):=1.5;--\ default expr 定义常量 v_sal_af v_sal%TYPE; BEGIN SELECT ename , sal INTO v_ename,v_sal FROM emp WHERE empno=&no; v_sal_af := v_sal * v_sal_tax; dbms_output.put_line('工资'||v_sal_af); END;
--复合类型之记录 类似于结构体 DECLARE 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='3'; dbms_output.put_line('员工名'||emp_record.name); END; --复合类型之表 类似于数组,但下标没有上限和下限 DECLARE TYPE ename_table IS TABLE OF emp.ename%type INDEX BY BINARY_INTEGER; tabled ename_table ; BEGIN SELECT ename INTO tabled(-1) FROM emp WHERE empno = '2'; dbms_output.put_line('雇员:'||tabled(-1)); END; --复合类型之嵌套表 下标不能为负,可以作为列属性 CREATE OR REPLACE TYPE emp_type AS OBJECT( name VARCHAR2(10),salary NUMBER(6,2)); CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type; CREATE TABLE unit( uno VARCHAR2(32),uname VARCHAR2(32),employee emp_array) NESTED TABLE employee STORE AS employee;--必须定义一个存储表 --复合类型之VARRAY 它要限定元素个数 CREATE TYPE art_type AS OBJECT( title VARCHAR2(30),publish DATE ); CREATE TYPE art_array IS VARRAY(20) OF art_type; CREATE TABLE composition ( id NUMBER(10),title VARCHAR2(32),art art_array );
持续更新中~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~