1 /* 2 *====================================== basic sql ========================================== 3 */ 4 5 -- Merge -- 6 merge into copy_emp as ac 7 using employees e 8 on (c.employee_id = employee_id) 9 when matched then 10 update set 11 c.first_name = e.first_name 12 c.last_name = e.last_name 13 c.department_id = e.department_id 14 when not matched then 15 insert values(e.employee_id, e.first_name, e.last_name, e.department_id) 16 17 -- alter table -- 18 alter table dep80 19 add(job_id varchar(8)) 20 21 alter table dep80 22 modify(job_id varchar(30)) 23 24 alter table dep80 25 drop(job_id) 26 27 alter table dep80 28 set unused(job_id) 29 30 -- reaname -- 31 rename dept to detail_dept 32 33 -- truncate -- 34 truncat table detial_dept 35 36 -- comment -- 37 comment on table dept IS 'department information' 38 comment on dept.deptno IS 'department number' 39 40 -- constraint , not null, unique, primary key, foreign key, check -- 41 create table employees ( 42 employee_id NUMBER(6), 43 job_id VARCHAR2(10) NOT NULL, 44 45 constraint emp_id_pk PRIMARY KEY(employee_id) 46 ) 47 48 alter table employees 49 add constraint emp_manager_fk FOREIGN KEY(manager_id) 50 REFERENCES employee(employee_id) 51 52 alter table employees 53 drop constraint emp_manager_fk [cascade] 54 55 alter table employees 56 disable constraint emp_manager_fk[cascade] 57 58 alter table employees 59 enable constraint emp_manager_fk 60 61 alter table employees 62 drop (column1, column2) [cascade constraints] 63 64 -- view -- 65 create or replace view emp_view as select deptno, dname, loc from dept 66 drop view emp_view 67 68 -- sequence -- 69 create sequence seq_emp 70 increment by 1 71 start with 0 72 maxvalue 20000000 73 minvalue 0 74 nocycle 75 nocache 76 77 -- index -- 78 create index ind_emp on dept(deptno, dname) 79 80 -- synonym -- 81 create public synonym dept for scott.dept 82 -- 2种方法: 例如 object 在 hr 下, scott 想要访问 hr 下的 table department. 83 -- 1. 在 scott 模式下, create synonym department for hr.department 84 -- 2. 在 hr 模式下, create synonym scott.department for department , 注意这种含义是 hr 用户代替 scott 85 -- 用户在 scott 模式下创建了别名, scott.department 这个表示是在 scott模式下, 所以在 scott 模式下就直接 86 -- 有了这样一个别名. 87 88 -- create user -- 89 create user leon_master identified by leon 90 alter user leon_master identified by master ( 修改密码 ) 91 92 -- grant privilege -- 93 grant dba to leon_master 94 grant create session, create table, create sequence to scott ( to role ) 95 grant manager(role) to leon_master 96 grant update(deptname, location_id) on departments to scott, manager 97 98 -- role -- 99 create role manager;100 grant create session, create table, create sequence to manager;101 102 -- revoke --103 revoke create session from scott104 revoke update(deptname, location_id) on department from scott, manager105 106 -- database link --107 create public database link mes20108 connect to <被链接数据库用户名> identified by <被链接数据库密码> 109 using 'ASDF'110 111 /*112 tnsnames >>>113 ASDF, ASDF2 =114 (DESCRIPTION =115 (ADDRESS = (PROTOCOL = TCP)(HOST = 109.106.13.25)(PORT = 1521))116 (ADDRESS = (PROTOCOL = TCP)(HOST = 109.106.13.22)(PORT = 1521))117 (ADDRESS = (PROTOCOL = TCP)(HOST = 109.106.13.26)(PORT = 1521))118 (CONNECT_DATA =119 (SERVER = DEDICATED)120 (SERVICE_NAME = ASDF)121 )122 )123 */124 -- relation sub query --125 select column1, column2126 FORM table1 outer127 where column1 operator ( select column1 from table2 where expr1 = outer.exp2);128 129 select column1, column2130 from table1 outer131 where exists ( select column1 from table2 where expr1 = outer.exp2);132 133 update table1 als_1134 set column = ( select expression from table2 als_2 where als_1.column = als_2.column);135 136 delete from table1 als_1137 where column operator ( select expression from table2 als_2 where als_1.column = als_2.column);138 139 -- Hierarchical retrieve --140 141 select substr(sys_connect_by_path(ENAME,'->'), 3) EMPLOYEE from EMP142 connect by prior EMPNO = MGR143 start with ename = '小董'144 145 -- 首先这个查询有一个 connect by prior EMPNO=MGR, 这是一个连接条件, 从 start with 开始找到第一行数据,146 -- 然后根据连接条件来进行. 向后遍历. 翻译过来就是, 先从小董开始, 然后小董的 empno 作为父层次 = manager147 -- 向后依次遍历.148 149 /*150 * =========================================== plsql =========================================151 */152 153 -- 系统绑定变量 可以直接在 sqlplus 环境中定义154 variable g_salary NUMBER 155 -- 注意此变量不同于一般意义的plsql变量, 这个是在环境中定义的类似环境变量156 157 -- PL/SQL 定义变量158 declare v_depno number(2) not null := 100;159 declare c_comn constant number := 1400;160 declare v_location varchar(20) := 'hello world';161 declare v_name employees.lastname%TYPE;162 declare v_balance number(10,2);163 declare v_min_balance v_balance%TYPE :=10; -- 参照上一个定义 v_balance164 -- declare v_home = q'[tom's home]' -- 注意是以双引号内的形式, 实际环境中, 不需要双引号165 declare l_right_now DATE NOT NULL DEFAULT SYSDATE;166 167 168 -- 在 PL/SQL 中 sql 部分不需要冒号 :169 declare v_bonus NUMBER(6);170 begin171 select salary * 0.01172 INTO v_bouns173 FROM employees174 WHERE emp_id = '200912';175 end;176 177 -- 游标 --178 declare cursor cursor_name IS179 select_statement;180 open cursor_name181 fetch cursor_name into variable1, varibal2182 close cursor_name 183 -- 以上4步骤, 分别为定义游标, 打开游标, 取出游标, 关闭游标184 185 -- 游标属性 --186 cursor_name%isopen187 cursor_name%notfound188 cursor_name%found189 cursor_name%rowcount190 191 -- 隐式游标举例 --192 Function book_title (isbn_in IN books.isbn%TYPE)193 return books.title%type194 IS195 return_value book.title%type;196 BEGIN197 SELECT title198 into return_value199 from books200 where isbn = isbn_in;201 202 return return_value;203 204 exception205 when no_date_found206 then207 return null;208 when too_many_rows209 then210 errpkg.record_and_stop ('Data integrity error for:' || isbn_in);211 raise; 212 END;213 214 -- 显示游标举例 --215 Function jealousy_level (216 NAME_IN IN friends.NAME%TYPE) RETURN NUMBER217 AS218 cursor jealousy_cur219 IS220 select location from friends221 where name = UPPER(name_in);222 223 jealousy_rec jealousy_cur%ROWTYPE; 224 retval NUMBER;225 BEGIN226 OPEN jealousy_cur;227 228 fetch jealousy_cur into jealousy_rec;229 230 IF jealousy_cur%found then231 if jealousy_rec.location = 'PUERTO RICO' THEN232 retval := 10;233 elsif jealousy_rec.location = 'CHICAGO' THEN234 retval := 1;235 end if;236 end if;237 238 close jealousy_cur;239 240 return retval;241 EXCEPTION242 when others then243 if jealousy_cur%isopen then244 close jealousy_cur;245 end if; 246 END;247 248 -- 游标变量 --249 TYPE cursor_type_name IS REF CURSOR [RETURN return type];250 251 TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE; -- 强类型252 253 TYPE generic_curtype IS REF CURSOR; -- 弱类型254 255 -- 从 oracle 9i 开始, 数据库已经替我们定义好了一个弱类型, 直接用就可以了256 declare my_cursor SYS_RECURSOR;257 258 -- 声明游标变量的方法259 cursor_name cursor_type_name;260 261 -- 打开游标变量262 open cursor_name FOR select_statement;263 -- 从游标变量获取数据, 同静态游标一样264 FETCH cursor_variable_name INTO record_name;265 FETCH cursor_variable_name INTO varibale_name, variable_name...;266 267 268 -- 游标表达式269 CURSOR( subquery ) 270 271 -- 参数化 游标 -- 参数的类型只能是 in 类型272 declare273 v_department classes.department%type;274 v_course classes.course%type;275 276 cursor c_classes is277 select * from classes278 where department = v_department279 and course = v_course;280 281 declare282 cursor c_classes(p_department classes.department%type, p_course classes.course%type) is283 select * from classes284 where department = v_department285 and course = v_course;286 open c_classes('HIS', 101);287 288 -- 游标中的 for update 语句, 加锁, 默认的游标是不会加锁的 --289 -- 如果此时该表已经被加锁, 那么这个游标将无限期的被挂起, 直到该表所已经解开, 而 NOWAIT 语句表示不等挂起290 -- 如果发现这个表已经被加锁, 直接会提示一个错误, 个人感觉 NOWAIT 好一点291 292 declare293 v_NumCredits classes.num_credites%type;294 295 cursor c_RegisteredStudents IS296 select * from students297 where id in ( select student_id298 from registered_students299 where department = ’HIS‘300 and course = 101)301 for update of current_credits; -- one column302 303 begin304 for v_studentInfo in c_RegisteredStudents305 loop306 select num_credits307 into v_NumCredits308 from classes309 where department = 'HIS'310 and course = 101;311 312 update students313 set current_credits = current_credits + v_NumCredits314 where current of c_RegisteredStudents -- 修改当前行的 column 值315 end loop;316 end;317 318 -- 注意 commit; 操作会释放锁, 所以如果你将 commit 写在 for 循环里的话, 那么再没有完成的情况下就释放锁,319 -- 会出现错误提示 ORA-1002： fetch out of sequence320 321 declare322 cursor c_AllStudents IS323 select * from students324 for update; -- 注意, 这只有 for update, 所以所有的列都被加锁325 326 v_StudentInfo c_AllStudents%ROWTYPE;327 begin328 open c_AllStudents;329 FETCH c_allStudents INTO v_StudentInfo;330 -- 如果这时候使用 commit; 那么后边的记录就没有办法进行了, 所以推荐 close cursor 以后再 commit;331 end;332 333 -- 当然, 如果你非想在 for 循环里使用 commit, 做法是, 现将游标全部提取出来, 然后通过 for 循环进行修改334 declare335 v_NumCredits classes.num_credites%type;336 337 cursor c_RegisteredStudents IS338 select * from students339 where id in ( select student_id340 from registered_students341 where department = ’HIS‘342 and course = 101)343 for update of current_credits; -- one column344 345 begin346 for v_studentInfo in c_RegisteredStudents347 loop348 select num_credits349 into v_NumCredits350 from classes351 where department = 'HIS'352 and course = 101;353 354 update students355 set current_credits = current_credits + v_NumCredits356 where current of c_RegisteredStudents -- 修改当前行的 column 值357 commit; -- 注意此时已经通过 for 循环将所有游标提取到 v_studentInfo里, 并且不能使用358 -- where current of359 end loop;360 end;361 362 -- If statement --363 IF salary >= 10000 AND salary <=20000364 THEN365 give_bonus(employee_id, 1500);366 ELSIF salary > 20000 AND salary <= 40000367 THEN368 give_bonus(employee_id, 10000);369 ELSIF salary > 40000370 THEN371 give_bouns(employee_id, 400);372 END IF;373 374 -- case statement --375 CASE employee_type376 WHEN 'S' THEN377 award_salary_bonus(employee_id);378 WHEN 'H' THEN379 award_hourly_bonus(employee_id);380 WHEN 'C' THEN381 award_commissioned_bonus(employee_id);382 ELSE383 RAISE invalid_employee_type;384 END CASE;385 386 CASE TRUE387 WHEN salary >= 10000 AND salary <= 20000 THEN388 give_bouns(employee_id, 1500);389 WHEN salary > 20000 AND salary < 40000 THEN390 give_bouns(employee_id, 1000);391 ELSE392 give_bouns(employee_id, 0);393 END CASE;394 395 -- loop --396 397 -- basic loop --398 PROCEDURE display_multiple_years (399 start_year_in IN PLS_INTEGER,400 end_year_in IN PLS_INTEGER401 )402 IS403 l_current_year PLS_INTEGER := start_year_in;404 BEGIN405 LOOP406 EXIT WHEN l_current_year > end_year_in;407 display_total_sales(l_current_year);408 l_current_year := l_current_year + 1;409 END LOOP;410 END display_muliple_years;411 412 -- for loop --413 procedure display_multiple_years( start_year_in IN PLS_INTEGER, end_year_in IN PLS_INTEGER)414 IS415 BEGIN416 FOR l_current_year IN start_year_in .. end_year_in417 LOOP418 display_total_sales(l_current_year);419 END LOOP;420 END display_multiple_years;421 422 procedure display_multiple_years( start_year_in IN PLS_INTEGER, end_year_in IN PLS_INTEGER)423 IS424 BEGIN425 FOR l_current_year IN (426 select * from sales_date427 where year between start_year_in and end_year_in)428 LOOP429 display_total_sales(l_current_year);430 END LOOP;431 END display_multiple_years;432 433 -- 游标参考 for loop --434 435 -- 一般形式的游标 --436 declare 437 cursor occupancy_cur IS438 select pet_id, room_number439 from occupancy where occupid_dt = trunc(sysdate);440 441 occupancy_rec occupancy_cur%rowtype;442 443 begin444 open occupancy_cur;445 loop446 fetch occupancy_cur into occupancy_rec;447 exit when occupancy_cur%notfound;448 update_bill(occupancy_rec.pet_id, occupancy_rec.room_id);449 end loop;450 close occupancy_cur;451 end;452 453 -- For loop 形式游标 --454 declare 455 cursor occupancy_cur IS456 select pet_id, room_number457 from occupancy where occupid_dt = trunc(sysdate);458 begin459 for occupancy_rac in occupacy_cur -- occupancy 不用定义460 loop461 update_bill(occupancy_rec.pet_id, occupancy_rec.room_id);462 end loop;463 end;464 465 -- while loop --466 procedure display_multiple_years( start_year_in IN PLS_INTEGER, end_year_in IN PLS_INTEGER)467 IS468 l_current_year PLS_INTEGER := start_year_in;469 BEGIN470 while (l_current_year <= end_year_in)471 LOOP472 display_total_sales(l_current_year);473 l_current_year := l_current_year + 1;474 END LOOP;475 END display_multiple_years;476 477 -- --------------------------Exception ------------------------------------------------------------478 -- EXCEPTION --479 -- 1.抛出异常, 用户错误, 系统错误, 内存泄露等等问题都会抛出异常480 -- 抛出方式, raise exception_name, raise package_name.exception_name, raise;481 -- raise_application_error 也可以抛出异常, 并且可以写自己bussiness的逻辑信息482 procedure raise_by_language(code_in IN PLS_INTEGER)483 IS484 l_message error_table.error_string%TYPE;485 BEGIN486 SELECT error_string487 INTO l_message488 FROM error_table489 where error_number = code_in490 and string_language = USERNV('LANG');491 492 RAISE_APPLICATION_ERROR(code_in, l_message); -- 抛出异常493 END;494 -- 2. 捕获异常495 -- 异常种类, oracle定义好有名字的异常, 一共20个, 没名字的异常, 还有就是用户定义异常496 -- 捕获异常后, 程序不会返回到抛出异常处, 执行完捕获异常, 看是向下还是停止.497 -- 当处理没有命名的异常时, 可以通过以下语句来定义名称498 declare499 e_emps_remaining EXCEPTION500 PRAGMA EXCEPTION_INIT(e_emps_remaining, -2292);501 begin502 -- do something503 exception504 when e_emps_remaining then505 statement1506 end;507 -- 3. 内置函数508 SQLCODE -- 返回一个错误代码, 如果没有错误, 返回 0509 SQLERRM -- 返回错误代码对应的官方错误信息510 DBMS_UTILITY.FORMAT_ERROR_BACKTRACK -- 10g 以后开始饮用, 返回一个格式化文本串(可以返回抛出异常行号)511 -- ---------------------------------End Exception -------------------------------------------------512 513 -- --------------------- 动态 sql -----------------------------------------------------------------514 515 516 517 -- ---------------------- End 动态 sql ------------------------------------------------------------518 519