select months_between(to_date('20200901', 'YYYYMMDD'), TO_DATE('20200722', 'YYYYMMDD')) as VAL1, round(months_between(to_date('20200901', 'YYYYMMDD'), TO_DATE('20200722', 'YYYYMMDD'))) as VAL2, ceil(months_between(to_date('20200901', 'YYYYMMDD'), TO_DATE('20200722', 'YYYYMMDD'))) as VAL3 from dual;
SELECT ORDER_DATE, PRODUCT_ID, QTY FROM PRODUCT WHERE PRODUCT_ID = '1000' ORDER BY ORDER_DATE;
CREATE OR REPLACE PACKAGE PG_PRODUCT AS PROCEDURE SP_PRC_GET_PRODUCT(i_product_id IN VARCHAR2); END PG_PRODUCT; / CREATE OR REPLACE PACKAGE BODY PG_PRODUCT AS PROCEDURE SP_PRC_GET_PRODUCT(i_product_id IN VARCHAR2) AS CURSOR cur_product IS SELECT ORDER_DATE, PRODUCT_ID, QTY FROM PRODUCT WHERE PRODUCT_ID = i_product_id ORDER BY ORDER_DATE; cur_product_rec cur_product%rowtype; BEGIN dbms_output.put_line('i_product_id = ' || i_product_id); FOR cur_product_rec IN cur_product LOOP dbms_output.put_line('ORDER_DATE = ' || cur_product_rec.ORDER_DATE || ', PRODUCT_ID = ' || cur_product_rec.PRODUCT_ID || ', QTY = ' || cur_product_rec.QTY); END LOOP; END SP_PRC_GET_PRODUCT; END PG_PRODUCT; /
SELECT ORDER_DATE, PRODUCT_ID, QTY FROM PRODUCT WHERE ORDER_DATE = '20200501' ORDER BY ORDER_DATE, PRODUCT_ID;
SELECT ORDER_DATE, PRODUCT_ID, QTY, row_number() over (ORDER BY ORDER_DATE, PRODUCT_ID) AS ROW_NUMBER FROM PRODUCT WHERE ORDER_DATE = '20200501' ORDER BY ORDER_DATE, PRODUCT_ID;
SELECT ORDER_DATE, PRODUCT_ID, QTY, ROW_NUMBER FROM ( SELECT ORDER_DATE, PRODUCT_ID, QTY, row_number() over (ORDER BY ORDER_DATE, PRODUCT_ID) AS ROW_NUMBER FROM PRODUCT WHERE ORDER_DATE = '20200501' ORDER BY ORDER_DATE, PRODUCT_ID ) WHERE ROW_NUMBER = 2;
SELECT ORDER_DATE, PRODUCT_ID, LAG(ORDER_DATE) over (ORDER BY ORDER_DATE) AS PREVIOUS_RECORD, LEAD(ORDER_DATE) over (ORDER BY ORDER_DATE) AS NEXT_RECORD FROM PRODUCT WHERE PRODUCT_ID = 1000;
SELECT to_char(last_day(to_date('20200905', 'YYYYMMDD')), 'YYYYMMDD') as last_day FROM DUAL ;
SELECT (last_day(to_date('20200905', 'YYYYMMDD')) - to_date('20200905', 'YYYYMMDD')) as Days_Left FROM DUAL ;
PROCEDURE SP_RESET_SEQUENCE AS seq_exists VARCHAR2(1) := 'T'; BEGIN SELECT CASE WHEN COUNT(*) > 0 THEN 'T' ELSE 'F' END INTO seq_exists FROM all_sequences WHERE sequence_name = 'SEQ_TEST' AND sequence_owner = 'TEST' ; IF seq_exists = 'T' THEN execute immediate 'DROP SEQUENCE TEST.SEQ_TEST'; END IF; execute immediate 'create sequence TEST.SEQ_TEST start with 1 increment by 1 MAXVALUE 9999 cycle'; END SP_RESET_SEQUENCE;
FUNCTION FN_GET_BIZ_COD (i_biz_cod IN VARCHAR2)
RETURN VARCHAR2 IS o_result VARCHAR2(2) := '';
BEGIN
CASE i_biz_cod
WHEN '01' THEN o_result := ' P';
WHEN '02' THEN o_result := ' S';
WHEN '03' THEN o_result := ' 8';
WHEN '04' THEN o_result := ' 3';
WHEN '05' THEN o_result := ' 8';
WHEN '06' THEN o_result := '11';
WHEN '07' THEN o_result := ' 6';
END CASE;
RETURN o_result;
END FN_GET_BIZ_COD;
FUNCTION FN_GET_BIZ_COD (i_biz_cod IN VARCHAR2)
RETURN VARCHAR2 IS o_result VARCHAR2(2) := '';
BEGIN
CASE i_biz_cod
WHEN '01' THEN o_result := ' P';
WHEN '02' THEN o_result := ' S';
WHEN '03' THEN o_result := ' 8';
WHEN '04' THEN o_result := ' 3';
WHEN '05' THEN o_result := ' 8';
WHEN '06' THEN o_result := '11';
WHEN '07' THEN o_result := ' 6';
ELSE o_result := '';
END CASE;
RETURN o_result;
END FN_GET_BIZ_COD;
tr.odd td.detail { background-color: #eaeaea; }
java -jar "C:\schemaspy\schemaSpy_5.0.0.jar" ^ -dp "C:\schemaspy\ojdbc6.jar" ^ -t orathin ^ -db TEST_DB ^ -s ap_tax ^ -host localhost ^ -port 1521 ^ -u system ^ -p password ^ -schemas "ODS,TS" ^ -charset UTF-8 ^ -o "C:\schemaspy\schema_doc" ^ -norows -hq -css "C:\schemaspy\schemaSpy.css" ^
case 幣別when 'NTD' then '01'when 'USD' then '51'when 'HKD' then '52'when 'JPY' then '53'
FUNCTION FN_GET_CCY_CODE (i_ccy IN VARCHAR2) RETURN VARCHAR2 IS o_ccy_code VARCHAR2(2) := ''; BEGIN CASE i_ccy WHEN 'NTD' THEN o_ccy_code := '01'; WHEN 'USD' THEN o_ccy_code := '51'; WHEN 'HKD' THEN o_ccy_code := '52'; WHEN 'JPY' THEN o_ccy_code := '53'; END CASE; RETURN o_ccy_code; END FN_GET_CCY_CODE;
create sequence seq_test start with 1 increment by 1;
select (select lpad(to_char(seq_test.NEXTVAL), 4, '0') from dual) as seq, ......... from ............ where .........
FUNCTION FN_GET_NEXT_SEQ_VAL RETURN VARCHAR2 IS v_next_seq_val VARCHAR2(4) := ''; BEGIN select lpad(to_char(seq_test.NEXTVAL), 4, '0') into v_next_seq_val from dual; return v_next_seq_val; END FN_GET_NEXT_SEQ_VAL;
select FN_GET_NEXT_SEQ_VAL() as seq, ......... from ............ where .........
CURRVAL and NEXTVAL can be used in the following places:VALUES clause of INSERT statementsSELECT list of a SELECT statementSET clause of an UPDATE statementCURRVAL and NEXTVAL cannot be used in these places:SELECT statement with the DISTINCT operatorSELECT statement with a GROUP BY or ORDER BY clauseSELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operatorWHERE clause of a SELECT statementDEFAULT value of a column in a CREATE TABLE or ALTER TABLE statementCHECK constraintCatalog (-cat) was not provided and unable to deduce catalog, wildcard catalog can be used -cat %
-cat "%" in your command line.CREATE OR REPLACE PACKAGE PG_TEST_001 AS v_last2Month VARCHAR2 := ''; PROCEDURE SP_Get_LAST2MONTH(i_dataId IN VARCHAR2, o_last2Month OUT VARCHAR2); END PG_TEST_001; /
PLS-00215: String length constraints must be in range
CREATE OR REPLACE PACKAGE PG_TEST_001 AS v_last2Month VARCHAR2(8) := ''; PROCEDURE SP_Get_LAST2MONTH(i_dataId IN VARCHAR2, o_last2Month OUT VARCHAR2); END PG_TEST_001; /
{
"test_no":"9999",
"year":"0108",
"registerDate":"20191031"
}
SELECT JSON_VALUE(JSON_COLUMN, '$.test_no' RETURNING VARCHAR2) FROM test_table
C:\Users\user01\Desktop λ impdp test_user/oracle@orcl directory=C:\Users\user01\Desktop dumpfile=data.dmp; 連線至: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-39002: 無效的作業 ORA-39070: 無法開啟日誌檔. ORA-39087: 目錄名稱 C:\USERS\USER01\DESKTOP 無效
CREATE OR REPLACE DIRECTORY DATA_DUMP_DIR AS 'C:\data'; impdp test_user/oracle@orcl directory=DATA_DUMP_DIR dumpfile=data.dmp LOGFILE=dump.log;
SQL> CREATE USER test_user IDENTIFIED BY oracle;
CREATE USER test_user IDENTIFIED BY oracle
*
ERROR 在行 1:
ORA-65096: 無效的通用使用者或角色名稱
SQL> alter session set "_ORACLE_SCRIPT"=true; 已更改階段作業. SQL> CREATE USER test_user IDENTIFIED BY oracle; 已建立使用者.
create or replace PACKAGE PACKAGE1 AS PROCEDURE TEST_EMPLOYEE ( I_GENDER IN VARCHAR2 DEFAULT 'M', O_RESULT OUT SYS_REFCURSOR, o_return_code OUT INT ); END PACKAGE1; create or replace PACKAGE BODY PACKAGE1 AS PROCEDURE TEST_EMPLOYEE ( I_GENDER IN VARCHAR2 DEFAULT 'M', O_RESULT OUT SYS_REFCURSOR, o_return_code OUT INT ) AS BEGIN OPEN O_RESULT FOR SELECT ID, NAME, PHONE, ADDRESS, GENDER FROM EMPLOYEE WHERE GENDER = I_GENDER; o_return_code := 0; END TEST_EMPLOYEE; END PACKAGE1;
package com.example.jpa.repository.custom; import java.util.Map; public interface EmployeeRepositoryCustom { Map<String, Object> findEmployee(String gender); }
package com.example.jpa.repository.impl; import com.example.jpa.entity.Employee; import com.example.jpa.repository.custom.EmployeeRepositoryCustom; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcCall; import java.util.Map; @Slf4j public class EmployeeRepositoryImpl implements EmployeeRepositoryCustom { @Autowired private NamedParameterJdbcTemplate jdbcTemplate; @Override public Map<String, Object> findEmployee(String gender) { SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate.getJdbcTemplate().getDataSource()) .withCatalogName("PACKAGE1") .withProcedureName("TEST_EMPLOYEE") .returningResultSet("O_RESULT", BeanPropertyRowMapper.newInstance(Employee.class)); SqlParameterSource input = new MapSqlParameterSource().addValue("I_GENDER", gender); Map<String, Object> output = jdbcCall.execute(input); output.entrySet().forEach(entry -> log.debug("key = {}, value = {}", entry.getKey(), entry.getValue())); return output; } }
package com.example.jpa.repository; import com.example.jpa.entity.Employee; import com.example.jpa.repository.custom.EmployeeRepositoryCustom; import org.springframework.data.repository.CrudRepository; import org.springframework.stereotype.Repository; @Repository public interface EmployeeRepository extends CrudRepository<Employee, String>, EmployeeRepositoryCustom { }
package com.example.jpa; import com.example.jpa.entity.Employee; import com.example.jpa.repository.EmployeeRepository; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.builder.ToStringBuilder; import org.junit.Assert; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import java.math.BigDecimal; import java.util.List; import java.util.Map; @SpringBootTest @Slf4j public class TestEmployeeRepository { @Autowired private EmployeeRepository employeeRepository; @Test public void testFindEmployee() { Map<String, Object> output = employeeRepository.findEmployee("F"); List<Employee> employees = (List<Employee>) output.get("O_RESULT"); for (int i = 0; i < employees.size(); i++) { log.debug("employee = {}", ToStringBuilder.reflectionToString(employees.get(i))); } int returnCode = ((BigDecimal) output.get("O_RETURN_CODE")).intValue(); log.debug("returnCode = {}", returnCode); } }
[ main] c.e.j.r.impl.EmployeeRepositoryImpl : key = O_RESULT, value = [com.example.jpa.entity.Employee@2adbd899, com.example.jpa.entity.Employee@d6831df] [ main] c.e.j.r.impl.EmployeeRepositoryImpl : key = O_RETURN_CODE, value = 0 [ main] com.example.jpa.TestEmployeeRepository : employee = com.example.jpa.entity.Employee@77ccded4[id=2,name=Mandy,phone=0911111111,address=Taipei,gender=F] [ main] com.example.jpa.TestEmployeeRepository : employee = com.example.jpa.entity.Employee@2bb717d7[id=3,name=Gina,phone=0911111111,address=ChiaYi,gender=F] [ main] com.example.jpa.TestEmployeeRepository : returnCode = 0