SQL编程(一)
最近准备学习一下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);BEGINSELECT ename INTO v_ename FROM emp--INTO为游标，必须要加WHERE empno='&no';--一个输入nodbms_output.put_line('输出'||v_ename);--dbms_output为系统表，\\为连接符EXCEPTION --抛出例外 ?WHEN NO_DATA_FOUND THEN??? dbms_output.put_line('输入有误');END;--命名块??? 跟匿名块差不多，多一个<<>><<outer>>DECLAREv_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)ASBEGIN? 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 NUMBERSQL->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 ASPROCEDURE update_sals(name varchar2, sal number)ASBEGIN? 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?);?持续更新中~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~????