1.每句后面需要加 ;
2.获取时间
当前时间 : GETDATE() now() LOCALTIMESTAMP
提前几天时间: now()::DATE -3+ now()::TIME
推迟几天时间:now()+1
获取第几周: date_part('week',@Today) day month
设定每周的第一天为周一 SET datestyle TO ISO, DMY;
convert(datetime, TmpJITSuppPart_UTime)不支持第三个参数
3.数据转换
SELECT to_number(23,99);
SELECT to_char('12', '999');
4.调用存储过程
call add_Log(1,1,1,1);
5.声明变量且赋值 判断
CREATE OR REPLACE PROCEDURE add_Log(@Param1 varchar(100), @Param2 varchar(100), @Param3 varchar(100), @Param4 varchar(30)-- 操作用户ID
)
AS DECLARE @Today DATETIME:= LOCALTIMESTAMP;@CurrWeek varchar(50);@Type varchar(20);@Result varchar(20);
BEGIN --直接赋值@CurrWeek := to_char(LOCALTIMESTAMP,'YYYY')+ '/' + 'WK' + to_char(date_part('week',@Today),'99');--查询赋值select Style into @Typefrom student;if 1=1thenbegin@Result := 'Normal';end;elsebegin@Result := 'Exception';end; end if;RAISE NOTICE 'The value of my_variable is %', @Result; --打印数据savepoint 1;ROLLBACK to savepoint 1;COMMIT; --提交
--抛出异常
EXCEPTIONWHEN others THEN RAISE EXCEPTION '%', SQLERRM; --抛出异常ROLLBACK; --回滚
END;
–查询PGSQL存储过程数量
SELECT COUNT(*)
FROM pg_proc
WHERE prokind IN ('p') AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'student');
—查询PGSQL数据库特定scema中存储过程内容中的字符串
SELECT p.proname
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
AND p.prokind = 'p'
AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'wlms_br')
and p.prosrc like '%Add_Log%'
—设置按照mssql语法下执行存储过程脚本
SELECT * FROM pg_settings where NAME='enable_set_variable_mssql_format'
SET enable_set_variable_mssql_format ON;
–连接到数据库
Environment bean = ToolSpring.getBean(Environment.class);String url = bean.getProperty("spring.datasource.url");String user = bean.getProperty("spring.datasource.username");String password = bean.getProperty("spring.datasource.password");Connection conn = DriverManager.getConnection(url, user, password);// 创建存储过程调用的语句String sql = "{perform ADD_Log(?,?,?,?)}";PreparedStatement stmt = conn.prepareStatement(sql);// 设置表值参数stmt.setArray(1, param1);stmt.setLong(2, param2);stmt.setString(3, param3);stmt.setString(4, param4);// 执行存储过程stmt.execute();// 关闭连接stmt.close();conn.close();