oracle学习笔记

●PLSQL控制台输出语句

SET serveroutput ON; --打开控制台输出服务

dbms_output.put_line('values2='||var_val); --输出语句

●PLSQL动态变量

var_str := '&input';

●创建表空间和用户

--创建表空间

CREATE TABLESPACE "BCPBS"

LOGGING

DATAFILE

'D:\app\E430\oradata\orcl\BCPBS_01.ora'

SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,

'D:\app\E430\oradata\orcl\BCPBS_02.ora'

SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

--建立用户

CREATE USER "BCPBS" PROFILE "DEFAULT"

IDENTIFIED BY "bcpbs123" DEFAULT TABLESPACE "BCPBS"

TEMPORARY TABLESPACE "TEMP"

ACCOUNT UNLOCK;

GRANT "CONNECT" TO "BCPBS";

GRANT "DBA" TO "BCPBS";

GRANT "RESOURCE" TO "BCPBS";

●删除表空间和用户

drop user bcpbs cascade ;

drop tablespace BCPBS including contents and datafiles cascade constraints ;

●自定义函数

CREATE OR REPLACE

FUNCTION fun_level_value(level_value number)

RETURN number

IS

return_value number:=null;

BEGIN

CASE level_value

WHEN 0 THEN return_value:='0';

WHEN 1 THEN return_value:='1';

WHEN 2 THEN return_value:='2';

WHEN 3 THEN return_value:='3';

WHEN 6 THEN return_value:='8';

ELSE

dbms_output.put_line('函数fun_level_value:'||level_value||'入参无法匹配');

END CASE;

RETURN return_value;

END fun_level_value;

存储过程

CREATE OR REPLACE

PROCEDURE p_bctts_enterext_update(var_id in number,

var_old_address varchar2,

var_new_address varchar2,

var_coperation varchar2,

var_bj_manager varchar2,

var_phone

varchar2,

var_email

varchar2,

var_consigner varchar2,

var_deputy varchar2,

var_accredit_content varchar2,

var_deputy_start_date date,

var_deputy_end_date date) IS var_errormsg varchar2(4000);

var_zyxt_enterprise_id varchar2(16);

BEGIN

var_errormsg := '';

var_zyxt_enterprise_id := '';

select zyxt_enterprise_id

into var_zyxt_enterprise_id

from t_enterprise_info

where id = var_id;

update bctts.t_enterprise_info

set bctts.t_enterprise_info.old_address = var_old_address,

bctts.t_enterprise_info.new_address = var_new_address,

bctts.t_enterprise_info.coperation = var_coperation,

bctts.t_enterprise_info.bj_manager = var_bj_manager,

bctts.t_enterprise_info.phone = var_phone,

bctts.t_enterprise_info.email = var_email,

bctts.t_enterprise_info.consigner = var_consigner,

bctts.t_enterprise_info.deputy = var_deputy,

bctts.t_enterprise_info.accredit_content = var_accredit_content,