●PLSQL控制台输出语句SET serveroutput ON; --打开控制台输出服务dbms_output.put_line('values2='||var_val); --输出语句●PLSQL动态变量var_str := '&input';●创建表空间和用户--创建表空间CREATE TABLESPACE "BCPBS"LOGGINGDATAFILE'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 UNLIMITEDEXTENT 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 REPLACEFUNCTION fun_level_value(level_value number)RETURN numberISreturn_value number:=null;BEGINCASE level_valueWHEN 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';ELSEdbms_output.put_line('函数fun_level_value:'||level_value||'入参无法匹配');END CASE;RETURN return_value;END fun_level_value;存储过程CREATE OR REPLACEPROCEDURE p_bctts_enterext_update(var_id in number,var_old_address varchar2,var_new_address varchar2,var_coperation varchar2,var_bj_manager varchar2,var_phonevarchar2,var_emailvarchar2,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);BEGINvar_errormsg := '';var_zyxt_enterprise_id := '';select zyxt_enterprise_idinto var_zyxt_enterprise_idfrom t_enterprise_infowhere id = var_id;update bctts.t_enterprise_infoset 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,bctts.t_enterprise_info.deputy_start_date = var_deputy_start_date,bctts.t_enterprise_info.deputy_end_date = var_deputy_end_date where enterprise_id = var_zyxt_enterprise_id;COMMIT;EXCEPTIONwhen others thenvar_errormsg := SUBSTR(SQLERRM, 1, 4000);INSERT INTO t_zyjcxxsync_error_info(f_tablename,t_tablename,op_type,primary_value,errormsg,operate_date,remark)VALUES('bcpbs.t_enterpriseextend_info','bctts.t_enterprise_info','update',var_id,var_errormsg,sysdate(),'p_bctts_enterext_update');ROLLBACK;RETURN;end;-------存储过程用于触发器是不能有commit和rollback行触发器CREATE OR REPLACETRIGGER "BCPBS".trg_bcpbs_agentBEFORE INSERT OR UPDATE ON t_agent_infoFOR EACH ROWDECLAREvar_errormsg varchar2(4000);var_zyxt_enterprise_id varchar2(32);var_result number(1);BEGIN--如果是备案数据,进行同步IF :_source = 0 THENvar_zyxt_enterprise_id := :new.zyxt_agent_id;--如果存在与专业系统不存在关联IDIF var_zyxt_enterprise_id is null THENselect count(*)into var_resultfrom BCTTS.t_agent_infowhere agent_name = :new.agent_name;--如果通过企业名称找到关联企业IF var_result = 1 THENselect AGENT_IDinto var_zyxt_enterprise_idfrom BCTTS.t_agent_infowhere agent_name = :new.agent_name;END IF;END IF;IF var_zyxt_enterprise_id is null THENinsert into BCTTS.t_agent_info(bctts.t_agent_info.agent_id,bctts.t_agent_info.agent_name,bctts.t_agent_info.corporation,bctts.t_agent_info.agent_level,bctts.t_agent_info.linkman,bctts.t_agent_info.link_phone,bctts.t_agent_info.status,bctts.t_agent__source)values(to_char(bctts.seq_t_agent_info.nextval),:new.agent_name,:new.representative_name,:new.qualifications_degree,:new.linkman,:new.linkman_phone,:new.status,:_source);select to_char(bctts.seq_t_agent_info.currval)into var_zyxt_enterprise_idfrom dual;:new.zyxt_agent_id := var_zyxt_enterprise_id;ELSEupdate BCTTS.t_agent_infoset bctts.t_agent_info.agent_name = :new.agent_name,bctts.t_agent_info.corporation = :new.representative_name,bctts.t_agent_info.agent_level = :new.qualifications_degree,bctts.t_agent_info.linkman = :new.linkman,bctts.t_agent_info.link_phone = :new.linkman_phone,bctts.t_agent_info.status = :new.status,bctts.t_agent__source = :_source where agent_id = var_zyxt_enterprise_id;:new.zyxt_agent_id := var_zyxt_enterprise_id;END IF;END IF;EXCEPTIONwhen others thenvar_errormsg := SUBSTR(SQLERRM, 1, 4000);INSERT INTO t_zyjcxxsync_error_info(f_tablename,t_tablename,op_type,primary_value,errormsg,operate_date,remark)VALUES('bcpbs.t_agent_info','bctts.t_agent_info','insert or update',:new.id,var_errormsg,sysdate(),'trg_bcpbs_agent');END;触发器开关alter trigger bcpbs.trg_agent_insert disable; --关闭触发器alter trigger bcpbs.trg_agent_insert enable; --打开触发器触发器操作其他用户表时的显示授权grant select on bctts.t_enterprise_info to bcpbs;grant update on bctts.t_enterprise_info to bcpbs;grant delete on bctts.t_enterprise_info to bcpbs;grant select on bctts.seq_t_enterprise_info to bcpbs;Case 语句的用法用法一:SELECTorganization_no,case enterprise_type1when 0 then '建设单位'when 1 then '施工单位'else '未登记企业'endFROM t_enterprise_info ;SELECT grade,COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1为男生,2位女生*/ELSE NULLEND) 男生数,COUNT (CASE WHEN sex = 2 THEN 1ELSE NULLEND) 女生数FROM students GROUP BY grade;用法三:SELECT T2.*, T1.*FROM T1, T2WHERE (CASE WHEN PARE_TYPE = 'A' ANDT1.SOME_TYPE LIKE 'NOTHING%'THEN 1WHEN PARE_TYPE != 'A' ANDT1.SOME_TYPE NOT LIKE 'NOTHING%'THEN 1ELSE 0END) = 1●字符串处理函数字符串截取:substr(organization_no, 1, 9) ----截取前9位字符字符串替换:replace(organize_no, '-', '')字符串拼接:concat('abc','123')----两个拼接wm_concat(organization_no)—拼接列1.LOWER(string)将输入的字符串转换成小写2.UPPER(string)将输入的字符串转换成大写3.INITCAP(string)将输入的字符串单词的首字母转换成大写。