Aurora笔记
1.bm文件介绍
<?xml version="1.0" encoding="UTF-8"?>
<!--
$Author: 10910
$Date: 2019-12-25 上午10:26:52
$Revision: 1.0
$Purpose:
-->
<bm:model xmlns:o="aurora.database.local.oracle" xmlns:f="aurora.database.features" xmlns:bm="http://www.aurora-framework.org/schema/bm" alias="t1" baseTable="TEST26820" defaultOrderBy="ID">
<!--数据库字段对应,类型于mybatis中的字段对应-->
<bm:fields>
<bm:field name="id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="ID" prompt="TEST26820.ID"/>
<bm:field name="name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NAME" prompt="TEST26820.NAME"/>
<bm:field name="age" databaseType="NUMBER" datatype="java.lang.Long" physicalName="AGE" prompt="TEST26820.AGE"/>
<bm:field name="sex" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SEX" prompt="TEST26820.SEX"/>
<bm:field name="enabled_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="ENABLED_FLAG" prompt="TEST26820.ENABLED_FLAG"/>
<bm:field name="teacher_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TEACHER_ID" prompt="TEST26820.TEACHER_ID"/>
<bm:field name="teacher_name" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false" physicalName="TEACHER_NAME" prompt="TEST26820.TEACHER_NAME"/>
<bm:field name="birthday" databaseType="DATE" datatype="java.util.Date" physicalName="BIRTHDAY" prompt="TEST26820.BIRTHDAY"/>
<bm:field name="sal" databaseType="NUMBER" datatype="java.lang.Double" physicalName="SAL" prompt="TEST26820.SAL"/>
</bm:fields>
<bm:features>
<f:standard-who/>
<o:sequence-pk/>
</bm:features>
<!--主键-->
<bm:primary-key>
<bm:pk-field name="id"/>
</bm:primary-key>
<!--填写where条件,这里就不写了-->
<bm:data-filters>
<bm:data-filter enforceOperations="query" expression="t1.teacher_id=t2.id"/>
</bm:data-filters>
<!--根据这个前端会生成模糊查询-->
<bm:query-fields>
<bm:query-field name="id" queryExpression="t1.id=${@id}"/>
<bm:query-field name="name" queryExpression="t1.name=${@name}"/>
<bm:query-field name="sex" queryExpression="t1.sex=${@sex}"/>
<bm:query-field name="teacher_id" queryExpression="t1.teacher_id=${@teacher_id}"/>
</bm:query-fields>
<bm:operations>
<!--修改默认查询语句-->
<bm:operation name="query">
<!--#WHERE_CLAUSE#-->
<bm:query-sql><![CDATA[
SELECT
t1.ID,
t1.NAME,
t1.AGE,
t1.SEX,
t1.ENABLED_FLAG,
t1.TEACHER_ID,
t1.SAL,
t1.BIRTHDAY,
t2.TEACHER_NAME
FROM
TEST26820 t1,TEST_TEACHER t2
#WHERE_CLAUSE#
#ORDER_BY_CLAUSE#
]]></bm:query-sql>
</bm:operation>
<!--修改默认插入语句-->
<bm:operation name="insert">
<bm:update-sql><![CDATA[
begin
test26820_pkg.exception_test
(
USER_NAME=>${@name},
CREATED_BY=>${/session/@user_id},
AGE=>${@age},
SEX=>${@sex},
ENABLED_FLAG=>${@enabled_flag},
TEACHER_ID=>${@teacher_id},
BIRTHDAY=>${@birthday},
SAL =>${@sal}
);
end;
]]>
</bm:update-sql>
</bm:operation>
</bm:operations>
</bm:model>
使用前先创建队列
-- Create sequence
create sequence TEST26820_S
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
2.新建SCREEN文件
modules目录底下新建screen文件,注意要和bm文件目录层级保持一致
3.文件在前端显示步骤
1..上传对应文件至服务器.
2.页面注册(云平台管理=》开发维护=》页面注册)注册screen、 svc文件
3.功能定义(云平台管理=》系统管理云级=》功能定义)通过脚本注册功能 分配页面 分配BM
4.为角色分配功能(云平台管理=》系统管理云级=》系统角色管理) 5.缓存数据重载(云平台管理=》开发维护=》缓存数据重载)
4.语言环境极其显示的中文名称对应的表
select * from SYS_PROMPTS where DESCRIPTION like '%id'
#查询语言环境#
select userenv('lang') from dual;
#改变语言环境#
alter session set NLS_LANGUAGE = 'simplified chinese'
5.几个比较重要的表
select * from SYS_CODE_VALUES_V WHERE CODE = 'SEX'--编码
SELECT * from SYS_CODES_VL;--父类
sys_ prompts; --前台页面多语言表
fnd_ descriptions; -后台业务数据多语言表
sys_ messages; -后台异常多语言表
sys_code_pkg;--某个code的表,在里面有大佬写的插入语句,可以直接copy修改
select * from sys_raise_app_errors order by 1 desc;--报错记录表
select * from sys_messages where message like '%姓名%';--EXCEPTION异常处理报错名称表
--写入上面那个表的异常名称
begin
sys_message_pkg.insert_message('TEST26820_USER_IS_NOT_NULL','错误','姓名不能为空!','ZHS');
sys_message_pkg.insert_message('TEST26820_USER_IS_NOT_NULL','ERROR','username is null!','US');
end;
commit
6.前端SCREEN文件
<?xml version="1.0" encoding="UTF-8"?>
<!--
$Author: 10910
$Date: 2019-12-25 上午11:23:04
$Revision: 1.0
$Purpose:
-->
<a:screen xmlns:a="http://www.aurora-framework.org/application">
<a:init-procedure/>
<a:view>
<script><![CDATA[
function query_test26820_result_ds(){
$('test26820_result_ds').query();
}
function reset_test26820_query_ds(){
$('test26820_query_ds').reset();
}
function on_enterdown(field,e){
$('test26820_result_ds').query();
}
function on_update(dataSet, record, name, value, oldvalue ){
if(name == 'age'){
record.set('sal',record.get('age')*200);
}
}
]]></script>
<a:dataSets>
<!-- lookupCode="SEX",这里因为没有数据库里,自定义的-->
<a:dataSet id="test26820_sex_list_ds">
<a:datas>
<a:record name="男" code="M"/>
<a:record name="女" code="W"/>
<a:record name="未选择" code="U"/>
</a:datas>
</a:dataSet>
<a:dataSet id="test26820_query_ds" autoCreate="true">
<a:fields>
<a:field name="id"/>
<a:field name="name"/>
<a:field name="sex"/>
<a:field name="sex3" displayField="name" options="test26820_sex_list_ds" returnField="sex" valueField="code"/>
<a:field name="teacher_name" lovAutoQuery="true" lovGridHeight="800" lovHeight="1000" lovLabelWidth="80" lovService="test.test26820.test_teacher_lov" lovWidth="800" title="教师查询">
<a:mapping>
<a:map from="id" to="teacher_id"/>
<a:map from="teacher_name" to="teacher_name"/>
</a:mapping>
</a:field>
<a:field name="teacher_id"/>
</a:fields>
</a:dataSet>
<a:dataSet id="test26820_result_ds" autoPageSize="true" autoQuery="true" model="test.test26820.test26820" queryDataSet="test26820_query_ds" selectable="true">
<a:fields>
<a:field name="id"/>
<a:field name="name"/>
<a:field name="age"/>
<a:field name="sex2" displayField="name" options="test26820_sex_list_ds" returnField="sex" valueField="code"/>
<a:field name="sex"/>
<a:field name="sal"/>
<a:field name="birthday"/>
<!--<a:field name="created_date"/>
<a:field name="last_updated_date"/>-->
<a:field name="enabled_flag" checkedValue="Y" defaultValue="N" uncheckedValue="N"/>
<a:field name="teacher_id"/>
<a:field name="teacher_name" lovAutoQuery="true" lovGridHeight="800" lovHeight="1000" lovLabelWidth="80" lovService="test.test26820.test_teacher_lov" lovWidth="800" title="教师查询">
<a:mapping>
<a:map from="id" to="teacher_id"/>
<a:map from="teacher_name" to="teacher_name"/>
</a:mapping>
</a:field>
</a:fields>
<a:events>
<a:event name="update" handler="on_update" />
</a:events>
</a:dataSet>
</a:dataSets>
<a:form column="4" marginWidth="80" title="查询内容">
<a:textField name="id" bindTarget="test26820_query_ds" prompt="编号">
<a:events>
<a:event name="enterdown" handler="on_enterdown"/>
</a:events>
</a:textField>
<a:textField name="name" bindTarget="test26820_query_ds" prompt="姓名">
<a:events>
<a:event name="enterdown" handler="on_enterdown"/>
</a:events>
</a:textField>
<a:comboBox name="sex3" bindTarget="test26820_query_ds" prompt="性别">
<a:events>
<a:event name="enterdown" handler="on_enterdown"/>
</a:events>
</a:comboBox>
<a:lov name="teacher_name" bindTarget="test26820_query_ds" prompt="教师姓名">
<a:events>
<a:event name="enterdown" handler="on_enterdown"/>
</a:events>
</a:lov>
</a:form>
<a:hBox>
<a:button click="query_test26820_result_ds" text="查询"/>
<a:button click="reset_test26820_query_ds" text="重置"/>
</a:hBox>
<a:grid id="grid_id" bindTarget="test26820_result_ds" marginHeight="80" marginWidth="80" navBar="true">
<a:toolBar>
<a:button type="add"/>
<a:button type="save"/>
<a:button type="delete"/>
<a:button type="excel"/>
<a:button type="clear"/>
</a:toolBar>
<a:columns>
<a:column name="id" editor="grid_id_numberField_id" prompt="编号" width="100"/>
<a:column name="name" editor="grid_id_textField_id" prompt="姓名" width="100"/>
<a:column name="age" align="center" editor="grid_id_numberField_id" prompt="年龄" width="100"/>
<a:column name="sex2" editor="grid_id_comboBox_id" prompt="性别" width="100"/>
<a:column name="sal" editor="grid_id_numberField_id" prompt="薪资" renderer="Aurora.formatMoney" width="100"/>
<a:column name="birthday" editor="grid_id_dateField_id" prompt="出生年月" renderer="Aurora.formatDate" width="100"/>
<a:column name="teacher_name" editor="grid_id_lov_id" prompt="教师姓名" width="100"/>
<!-- <a:column name="created_date" editor="grid_id_textField_id" prompt="创建日期" renderer="Aurora.formatDateTime" width="150"/>
<a:column name="last_updated_date" editor="grid_id_textField_id" renderer="Aurora.formatDateTime" prompt="最后更新日期" width="150"/>
-->
<a:column name="enabled_flag" editor="grid_id_checkBox_id" prompt="是否启用" width="150"/>
</a:columns>
<a:editors>
<a:numberField id="grid_id_numberField_id"/>
<a:textField id="grid_id_textField_id"/>
<a:datePicker id="grid_id_dateField_id"/>
<a:checkBox id="grid_id_checkBox_id"/>
<a:comboBox id="grid_id_comboBox_id"/>
<a:lov id="grid_id_lov_id"/>
</a:editors>
</a:grid>
<a:hBox/>
</a:view>
</a:screen>
7.PLSQL在更新时写的包的事例
HEAD:
create or replace package test26820_pkg is
-- Author : 10910
-- Created : 2019/12/26 9:32:23
-- Purpose :
PROCEDURE exception_test(USER_NAME VARCHAR2,CREATED_BY NUMBER,AGE NUMBER,SEX VARCHAR2,ENABLED_FLAG VARCHAR2,
TEACHER_ID NUMBER,BIRTHDAY VARCHAR2,SAL NUMBER);
end test26820_pkg;
BODY:
create or replace package body test26820_pkg is
PROCEDURE exception_test(USER_NAME VARCHAR2,CREATED_BY NUMBER,AGE NUMBER,SEX VARCHAR2,ENABLED_FLAG VARCHAR2,
TEACHER_ID NUMBER,BIRTHDAY VARCHAR2,SAL NUMBER) AS
t_user_name_is_null EXCEPTION;
BEGIN
IF user_name IS NULL THEN
RAISE t_user_name_is_null;
ELSE
INSERT
INTO
TEST26820
(
ID,
NAME,
AGE,
SEX,
ENABLED_FLAG,
TEACHER_ID,
BIRTHDAY,
SAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES
(
TEST26820_S.NEXTVAL,
USER_NAME,
AGE,
SEX,
ENABLED_FLAG,
TEACHER_ID,
to_date(BIRTHDAY,'yyyy-mm-dd hh24:mi:ss') ,
SAL,
CREATED_BY,
sysdate,
CREATED_BY,
sysdate
);
END IF;
exception
when t_user_name_is_null then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'TEST26820_USER_IS_NOT_NULL', --syscode_value不能重复
p_created_by => CREATED_BY,
p_package_name => 'test26820_pkg',
p_procedure_function_name => 'exception_test');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
END;
end test26820_pkg;
测试脚本:
begin
-- Call the procedure
test26820_pkg.exception_test(USER_NAME => :USER_NAME,
CREATED_BY => :CREATED_BY,
AGE => :AGE,
SEX => :SEX,
ENABLED_FLAG => :ENABLED_FLAG,
TEACHER_ID => :TEACHER_ID,
BIRTHDAY => :BIRTHDAY,
SAL => :SAL);
end;
8.LOV的bm文件定义(然后前端使用参考前端那个页面,相信你可以找到)
<?xml version="1.0" encoding="UTF-8"?>
<!--
$Author: 10910
$Date: 2019-12-25 下午6:59:27
$Revision: 1.0
$Purpose:
-->
<bm:model xmlns:bm="http://www.aurora-framework.org/schema/bm">
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[select id,teacher_name from test_teacher #WHERE_CLAUSE# ]]></bm:query-sql>
</bm:operation>
</bm:operations>
<bm:fields>
<bm:field name="id" databaseType="NUMBER" datatype="java.lang.Long" forDisplay="true" forQuery="true" physicalName="ID" prompt="编号"/>
<bm:field name="teacher_name" databaseType="VARCHAR2" datatype="java.lang.String" forDisplay="true" forQuery="true" physicalName="TEACHER_NAME" prompt="姓名"/>
</bm:fields>
<bm:query-fields>
<bm:query-field name="id" queryExpression="id like ${@id}"/>
<bm:query-field name="teacher_name" queryExpression="teacher_name like ${@teacher_name}"/>
</bm:query-fields>
</bm:model>