1.x version of hive does not provide the function of similar stored procedure. When using Hive for data development, it usually encapsulates a paragraph of HQL statements in Shell or other scripts, and then calls them by command line to complete the statistical analysis of a business or a report. The good news is that there is now a Hive stored procedure solution (HPL/SQL-Procedural SQL on hadoop) and that this module will be integrated into future Hive versions (2.0). This solution not only supports Hive, but also supports the use of Oracle PL/SQL-like functions in Spark SQL, other NoSQL, and even RDBMS. This will greatly facilitate the work of data developers. Many previously difficult functions in Hive can now be easily implemented, such as custom variables, cursors based on a result set, loops, and so on.

hive 1.x version needs to install hplsql by itself, hive 2.x version with hplsql, just need to configure the configuration file related to hplsql.
To configure
The configuration file (hplsql-site.xml) is as follows

<configuration>
<property>
  <name>hplsql.conn.default</name>
  <value>hive2conn</value>
  <description>The default connection profile</description>
</property>
<property>
  <name>hplsql.conn.hive2conn</name>
  <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://Hadoop01:10000;root;123456</value>
  <description>HiveServer2 JDBC connection</description>
</property>
<property>
  <name>hplsql.conn.init.hive2conn</name>
  <value>
    set mapred.jobqueue.name=default;
    set hive.execution.engine=mr;
    set hive.exec.mode.local.auto=true;
  </value>
  <description>Statements for execute after connection to the database</description>
</property>
<property>
  <name>hplsql.conn.convert.hive2conn</name>
  <value>true</value>
  <description>Convert SQL statements before execution</description>
</property>
<property>
  <name>hplsql.conn.db2conn</name>
  <value>com.ibm.db2.jcc.DB2Driver;jdbc:db2://Hadoop01:50001/dbname;user;password</value>
  <description>IBM DB2 connection</description>
</property>
<property>
  <name>hplsql.conn.tdconn</name>
  <value>com.teradata.jdbc.TeraDriver;jdbc:teradata://localhost/database=dbname,logmech=ldap;user;password</value>
  <description>Teradata connection</description>
</property>
<property>
  <name>hplsql.conn.mysqlconn</name>
  <value>com.mysql.jdbc.Driver;jdbc:mysql://Hadoop01:3306/hive;root;123456</value>
  <description>MySQL connection</description>
</property>
<property>
  <name>hplsql.dual.table</name>
  <value>default.dual</value>
  <description>Single row, single column table for internal operations</description>
</property>
<property>
  <name>hplsql.insert.values</name>
  <value>native</value>
  <description>How to execute INSERT VALUES statement: native (default) and select</description>
</property>
<property>
  <name>hplsql.onerror</name>
  <value>exception</value>
  <description>Error handling behavior: exception (default), seterror and stop</description>
</property>
<property>
  <name>hplsql.temp.tables</name>
  <value>native</value>
  <description>Temporary tables: native (default) and managed</description>
</property>
<property>
  <name>hplsql.temp.tables.schema</name>
  <value></value>
  <description>Schema for managed temporary tables</description>
</property>
<property>
  <name>hplsql.temp.tables.location</name>
  <value>/tmp/plhql</value>
  <description>LOcation for managed temporary tables in HDFS</description>
</property>
</configuration>

Where Hadoop01 is changed to the node name of its own node
Put the configured hplsql-site.xml in the conf directory under your own hit directory
start-up
Start the metadata service first

hive --service metastore &

Start hiveserver2 service

hive --service hiveserver2 

Start hiveserver2

hiveserver2